Mysql查询变慢了？- 慢查询分析的基本思路(一)

Mysql为什么查询变慢了？

查询为什么变慢了，最简单的解释就是数据多了，找起来当然就变慢了。

衣柜里只有几件衣服，你找到想要的一件衣服，看一眼就能拿到；但是当你有一整柜的衣服时，你要找到想要的衣服，这时就需要花一定时间翻找了。

对于mysql来说它就是用最简单的办法，一条一条数据查，然后匹配出结果，最后输出给客户端(当然这是在不借助任何索引技术的情况下)。

索引技术

一条一条从头开始找，确实能找到。但是这种办法太笨了，虽然计算机的速度足够快，但是随着数据量级的增加，这种方式对处理时间还是有影响的，至少是随着数据量线性增加的。

索引(Index)就是能快速找到要匹配的数据，这里最根本的就是利用“树”这种数据结构来提升查找性能,Mysql目前默认的是Innodb存储引擎。本篇文章暂时不对Innodb做过多说明，只需要知道它是一颗“树”。

在mysql中分Server层与存储引擎层

大致结构如下

(上图来自互联网)

也就是说去查找数据都是“存储引擎”层干的事情。

---

如果一个表：

CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
 `f1` varchar(128) NOT NULL COMMENT 'f1',
 `f2` varchar(128) NOT NULL COMMENT 'f2',
 PRIMARY KEY (`id`),
 KEY `idx_f1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

那么它在存储引擎里有两颗“树”的数据结构，第一个是以主键索引（PRIMARY KEY）构建的“树”结构，其叶子结点最终对应的是一行完整的数据；另外一个是以普通索引（idx_f1）构建的“树”结构，其叶子结点只有索引的字段和对应的主键字段id.

如果你的sql是：

SELECT * FROM t1 WHERE id=2;

那么Mysql会直接去PRIMARY KEY索引“树”中查到id=2的这条记录，然后将所有字段返回。这也是为什么说主键字段查找为什么快的原因。
如果你的sql是：

SELECT * FROM t1 WHERE f1="1";

这时候需要用到idx_f1索引“树”，先找到对应匹配f1="1"叶子结点，但是这时候需要所有列数据“*”，所以再通过已经匹配的叶子结点中对应的id:2 继续去PRIMARY KEY索引“树”中查找到最终所有字段数据返回。这个需要二次查找的过程叫做“回表”。

如果你的sql是：

SELECT id,f1 FROM t1 WHERE f1="1";

这时候需要用到idx_f1索引“树”，先找到对应匹配f1="1"叶子结点,不过叶子结点中已经包含了所有需要的字段，可以不用回表，直接返回，从而提升效率，这在sql优化中是一个很重要的操作，叫作“索引覆盖”（在mysql执行计划Extra字段中出现Using index）。

如果你的sql是：

SELECT * FROM t1 WHERE f2="a";

这时没有任何索引可以用，只能依次对每条数据进行扫描，这个过程就是“全表扫描”, 而且从存储引擎层扫描的数据要全部发送到Server层在该层进行f2="a"的数据过滤后再返回。这个性能可想而知是非常差的（在mysql执行计划type字段为ALL, Extra字段包含Using where）。

如果你的sql是：

SELECT * FROM t1 WHERE f1="1" and f2="a";

这时候需要用到idx_f1索引“树”，先找到对应匹配f1="1"叶子结点, 再回表去PRIMARY KEY索引查找所有字段并返回Server层，再由Server层对所有数据进行f2="a"过滤后返回。这样就使用上了索引，最终在server层过滤的数据会少很多（在mysql执行计划type字段为ref, Extra字段包含Using where）。

如果你的sql是：

SELECT * FROM t1 WHERE f1="1" and id>10;

这时候也是同样用到idx_f1索引“树”，现找到f1="1"的叶子结点，单此时因为叶子结点上存在id字段，可以在直接过滤 id>10的结果，再回表到PRIMARY KEY找到记录，最终返回server层，此时也没有其他where条件，则可直接返回结果。这其实就是ICP索引下推（Index Condition Pushdown）

---

从以上的一些规则和机制可以看出，提高Mysql查询效率的一些基本思路有：

• 使用索引技术，避免全表扫描
• 尽可能的让查询在存储引擎层完成过滤，不要到server层进行过滤，因为可以减少不必要的数据的传输与处理，充分利用索引
• 尽量减少回表次数

---

如何查看慢查询呢？

在mysql的配置文件中有这么几个配置项：

slow_query_log = 1
slow_query_log_file = /path/to/your/slow_query.log
long_query_time = 2

slow_query_log：启用慢查询日志（1表示启用，0表示禁用）。
slow_query_log_file：指定慢查询日志文件的位置。
long_query_time：设置查询超过多少秒被认为是慢查询。这里设置为2秒，可以根据需要调整。

启用慢查询日志后，MySQL会将执行时间超过 long_query_time 的查询记录到指定的慢查询日志文件中。

使用 mysqldumpslow 工具查看慢sql

MySQL提供了一个名为 mysqldumpslow 的实用工具，用于分析和汇总慢查询日志。使用这个工具可以更方便地查看和分析慢查询。

一些常用的命令示例如下：

显示最频繁的10个查询：

mysqldumpslow -s c -t 10 /path/to/your/slow_query.log

显示执行时间最长的10个查询：

mysqldumpslow -s t -t 10 /path/to/your/slow_query.log

显示扫描行数最多的10个查询：

mysqldumpslow -s r -t 10 /path/to/your/slow_query.log

显示平均锁等待时间最长的10个查询：

mysqldumpslow -s l -t 10 /path/to/your/slow_query.log

当然你也可以使用一些其他的第三方工具去对慢日志监控以及告警。

---

导航：Mysql查询变慢了？- 慢查询分析的基本思路(二)