导致MySQL的查询语句效率低下的可能原因

前言

本文来自于网络的拼凑，目的在于面试时可以让大家 pretend to be something (zhuang bi) 。

一、执行语句执行效率低下的原因

① SQL语句本身没那么慢，但是在某些特殊情况下会出现执行效率低下的问题。

② SQL语句本身就很慢。

二、解决方法

1、针对第一种情况

① 数据库正在刷新脏页：
当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是先把这些更新的记录写入到 redo log 日志中去，等到空闲的时候，再通过 redo log 里的日志把最新的数据同步到磁盘中去。

不过，redo log 的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的 SQL 语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的 SQL 语句执行的很慢。

② 获取不到锁：
这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加了锁，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某一行被加锁了，这个时候，同样只能等待。

如果要判断是否真的在等待锁，我们可以用 show processlist 这个命令来查看当前的状态，这里我要提醒一下，有些命令最好记录一下，以防不时之需。

2、针对第二种情况

导致第二种情况发生的原因，很大一部分是由于 SQL 语句写的不到位，所以接下来给大家分析一下哪些 SQL 的书写情况会导致语句执行效率低下：

我们先来假设我们有一个表，表里有下面两个字段，分别是主键 id ，和两个普通字段 c 和 d 。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;  

① 没有使用索引：
例如下面这条 SQL 语句：

select * from t where 100 < c and c < 100000;  

如果刚好你的 c 字段上没有索引，那么抱歉， MySQL 只能走全表扫描了，你就体验不到索引带来的乐趣了，所以，查询数据时不能更好的利用索引会导致查询语句执行效率低下。

② 字段有索引，但是没有正确使用：
好吧，这个时候你知道了索引的重要性，并立即给 c 这个字段添加了索引，然后又执行了如下的查询语句：

select * from t where c - 1 = 1000;  

试问这样的查询语句会使用到你刚才在 c 字段上建立的索引吗？

当然不会！

如果我们在等式的左边对字段做了运算，那么很抱歉，在查询的时候， MySQL 并不会使用索引，所以，大家要注意这种字段上有索引，但由于自己的疏忽，导致 MySQL 没有使用索引的情况了。

正确使用索引的查询语句应该是这样的：

select * from t where c = 1000 + 1;  

有人可能会说，等式右边有运算就能用上索引？难道数据库就不会自动帮我们优化一下，自动把 c - 1 = 1000 自动转换为 c = 1000 + 1 ?

不好意思， MySQL 确实不会帮你，所以，你要注意了。

③ 函数操作导致没有使用索引：
如果我们在查询的时候，对字段进行了函数操作，也是会导致没有用上索引的，例如：

select * from t where pow(c,2) = 1000;  

假设函数 pow 是求 c 的 n 次方，实际上可能并没有 pow(c,2) 这个函数。这个其实和上面在等式左边做运算也是很类似的问题。

所以，一条语句执行都很慢的时候，可能是该语句没有用上索引，不过具体是啥原因导致没有用上索引，你就要会分析了，我上面列举的三个原因，应该是出现的比较多的吧。

④ 数据库自己选错了索引：
我们在进行查询操作的时候，例如：

select * from t where 100 < c and c < 100000;  

我们知道，主键索引 和 非主键索引 是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而是存放主键字段的值。

在这里简单给大家解释一下主键索引和非主键索引的区别：

假设我们现在要对如下这张表做查询：

其中 ID 是主键，使用主键索引和非主键索引的示意图如下（分别使用 ID 字段和 k 字段索引数据）：

其中 $R$ 代表一整行的值，$R_1$ 则表示：

从图中不难看出，主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

根据这两种索引结构我们来看看它们的查询过程有什么区别：

• 如果查询语句是 select * from table where ID = 100 ，即主键查询的方式，则只需要搜索 ID 这棵 B+树 。

• 如果查询语句是 select * from table where k = 1 ，即非主键的查询方式，则先搜索 k 索引树，得到 ID = 100 ，再到 ID 索引树搜索一次，这个过程也被称为回表。

回到我们的主题，也就是说，我们如果走 c 这个字段的索引的话，最后会查询到对应主键的值，然后，再根据主键的值走主键索引，查询到整行数据返回。

其实，就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描全表，找出所有符合 100 < c and c < 100000 的数据。

为什么会出现这样的情况？

因为系统在执行这条语句的时候，会进行预测：究竟是走 c 索引扫描的行数少，还是直接扫描全表扫描的行数少。显然，扫描行数越少当然越好了，因为扫描行数越少，意味着 I/O 操作的次数越少。

如果是扫描全表的话，那么扫描的次数就是这个表的总行数了，假设为 n ；而如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找数据，也就是说，需要走两次索引。而且，我们也不知道符合 100 < c and c < 10000 条件的数据有多少行，万一这个表的数据全部都符合条件呢？这个时候意味着，走 c 索引不仅扫描的行数是 n，同时查询每行数据的代价是走两次索引。

所以，MySQL是有可能走全表扫描而不走索引的。那系统是如何进行判断呢？

MySQL 是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。

所以呢，一个索引的基数越大，意味着走索引查询越有优势。

那么问题来了，MySQL 是如何知道某个索引的基数大小的？

MySQL 当然是不会遍历全部数据来获得一个索引的基数，它是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。

当然了，采样的结果并不能完全反映总体的情况，很有可能 c 这个索引的基数实际上是很大的，但是采样的时候，把这个索引的基数预测的很小。在这种情况下， MySQL 就不会使用 c 索引，而是使用全表扫描。

PS：系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

那么如何解决这种系统造成的问题？

通过显式的方式强制使用索引：

select * from t force index(a) where c < 100 and c < 100000;  

还可以通过下面的语句来查询索引的基数和实际是否符合：

show index from t;  

如果和实际很不符合的话，我们可以重新来统计索引的基数，使用如下语句：

analyze table t;  

三、总结

如果一条 SQL 语句的执行效率非常低下，分两种情况去讨论：

• 大多数情况下很正常，偶尔很慢，原因可能如下：

  ① 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

  ② 执行的时候，遇到锁，如表锁、行锁，需要等待。

• 这条 SQL 语句一直很慢，则原因可能如下：

  ① 没有用上索引：例如该字段没有索引；有索引，但是对字段进行了运算、函数操作而导致无法使用索引。

  ② 数据库选错了索引。

上面给大家介绍到的是用于面试中问到 SQL语句执行效率低下原因 的笼统分析，只是简单涉及到 SQL层面的优化 ，并不涉及到 数据库层面的优化 ，如果想知道如何去优化数据库，请移步数据库优化之读写分离与数据库Sharding。在实际工作当中，我相信大家还是比较感兴趣这条 SQL 语句到底是如何执行的，转而去直接查看 SQL 语句的 执行计划 。当然了，面试过程中你这样简单的回答可能往往得不到面试官的认可，面试官的态度一般都是 show me what you get ，所以，请开始你的表演（ Zhuang Bi ）！