MySQL慢查询那点事

----这个世界需要更多充满激情的疯子。

（一）MySQL查询对资源的消耗

                    为什么会慢？

    先从磁盘的IO开始分析,首先机械式磁盘,每次搜寻数据的时候都需要寻道，然后读取数据,根据比较可以看出内存的速度是硬盘的10万倍！

我们要把数据从硬盘读取到内存的过程就需要进行IO操作，这个过程单次IO可能需要耗费近10ms的时间能完成读取,同时操作系统又是以块来区分，所以可以认为一次IO操作从硬盘读取n块数据。通过分析可以知道只要IO操作越少，速度就越快！。                                       

            那么怎么减少IO操作呢？

  接下来看看数据库中的数据存储结构，数据结构一般是B+树，B+树的特点是所有的数据都是存放在叶子节点,同时是按照顺序进行存放的！下面是B+树的结构

比如我们要找数据4，那么首先磁盘第一次IO是定位到P1，通过二分查找到下一块的地址P3.这时候2次的IO读取到了数据项4,可以看出读取数据IO的次数就是B+树的高度！

假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。

是否豁然开朗！这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。主要是为了减少树的高度！合适的字段类型和大小会对效率和速度产生一定的影响！

                          总结

一定要选择合适的类型和大小来约束字段,可以降低B+树的高度,从而减少IO次数。

同时建立索引的也是一种B+树结构,通过索引来查找数据可以更少次数的IO实现数据的定位。

所以慢查询的优化就是减少IO操作,那么接下来我们通过慢查询日志来分析如何优化！

---

（二）MySQL日志类型

首先我们来看看MySQL的日志构成：

    MySQL日志文件系统的组成

    a、错误日志：记录启动、运行或停止mysqld时出现的问题。

    b、通用日志：记录建立的客户端连接和执行的语句。

    c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。

    d、二进制日志：记录所有更改数据的语句。还用于复制。

e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

    f、Innodb日志：innodb redo log

    好了我们捕获到了我们需要的东西,那就是慢查询日志！我们来看看一般的慢查寻日志是否有在MySQL中开启：

在命令行模式下执行:show variables like 'slow%';

slow_query_log：OFF表示没有开启慢查询,需要先开启来哦~

有了慢查询日志接下来就是我们要针对性的对消耗时间超过设定的sql语句进行针对性的优化，

到了这一步我们就可以知道到底什么语句影响了数据库的查询性能！

---

（三）MySQL语句分析

有了日志，我们才能快速定位问题。首先我们来看看系统给出的一个慢查询日志

# Time: 180112 16:50:45

# User@Host: a8591[a8591] @  [192.168.1.132]

# Thread_id: 26880039011  Schema: a8591  Last_errno: 0  Killed: 0

# Query_time: 1.336462  Lock_time: 0.000089  Rows_sent: 0  Rows_examined: 3499535  Rows_affected: 0  Rows_read: 0

# Bytes_sent: 733  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

SET timestamp=1515747045;

SELECT * FROM `serviceRecord` WHERE ( `tag` = 120 ) AND ( `theId` = 2741372 ) LIMIT 1;

我们来解析一下日志各个参数的含义：

Query_time:1.336462                      查询消耗的时间

Lock_time:0.000089                        锁表时间

Rows_sent: 0                                    发送或返回的行数

Rows_examined:3499535                查询行数   

解决方法：

1.先 执行DESC SELECT * FROM `serviceRecord` WHERE ( `tag` = 120 ) AND ( `theId` = 2741372 ) LIMIT 1;进行分析

这里的字段解析：

主要是看key是否用到了索引,这里我们发现key是NULL，所以这条语句没有经过任何索引.

然后分析慢日志：

这条是非常简单的查询语句,但是却查了300多万行的数据,非常明显没有通过索引,查看数据库的表结构

分析一下,索引有两个，一个是id的主键索引,一个是(type,theId,recTime)的联合索引。

我们的查询语句：SELECT * FROM `serviceRecord` WHERE ( `tag` = 120 ) AND ( `theId` = 2741372 ) LIMIT 1;

很明显没有走任何索引导致了全表扫描！

根据业务是关于一些消息记录的，theId是非常常用的一个字段,区分度也非常高，因此我们需要对theId进行索引的建立,而tag标签的区分度并不算高,所以暂时没有必要进行索引的建立。

参考：http://blog.csdn.net/gent__chen/article/details/51159451