前言
本文来自于网络的拼凑,目的在于面试时可以让大家 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
字段索引数据):
其中 代表一整行的值, 则表示:
从图中不难看出,主键索引和非主键索引的区别是:非主键索引的叶子节点存放的是主键的值,而主键索引的叶子节点存放的是整行数据,其中非主键索引也被称为二级索引,而主键索引也被称为聚簇索引。
根据这两种索引结构我们来看看它们的查询过程有什么区别:
- 如果查询语句是
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
)!