MySQL 笔记整理(10) --MySQL为什么有时会选错索引?
笔记记录自林晓斌(丁奇)老师的《mysql实战45讲》
(本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除)
10) --mysql为什么有时会选错索引?
mysql中的一张表上可以支持多个索引的,但是,往往你写sql语句的时候不会去主动指定使用哪个索引。也就是说,使用哪个索引是由mysql来确定的。而mysql有时会选择不恰当的索引,我们举一个例子来说明这种情况。
create table `t` ( `id` int(11) not null, `a` int(11) default null, `b` int(11) default null, primary key (`id`), key `a` (`a`), key `b` (`b`) ) engine=innodb;
然后向表中插入10万行记录,取值按整数递增,即(1,1,1),(2,2,2),(3,3,3)直到(100000,100000,100000)。我们来分析一条sql语句:
mysql> select * from t where a between 10000 and 20000;
这条语句很简单,想必你也想到了这条语句会使用索引a,事实上也确实使用了索引a。不过别急,这个例子没有这么简单,我们继续来看:
其中 call idata()是执行mysql的存储过程,用来插入数据。需要注意的是,这里session b就不会再使用索引a了。为了对比结果,可以使用force index(a)来让优化器强制使用索引a,下面三条语句就是实验过程:
set long_query_time=0; select * from t where a between 10000 and 20000; /*q1*/ select * from t force index(a) where a between 10000 and 20000;/*q2*/
- 首先把慢查询日志的阈值设为0,表示这个线程接下来的语句都会进入慢查询日志中。
- q1是session b原来的查询;
- q2是seesion b 强制使用索引a的查询。
对比结果如下:
很容易看出第一行查询了10w行,并没有利用到索引a。为什么会这样的,我们从优化器的逻辑谈起
优化器的逻辑:
优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库中,扫描行数是影响执行代价的因素之一。我们的优化器就是在判断扫描行数的时候出了问题。那么问题就是,扫描行数是怎么判断的呢?而在真正的执行语句之前,并不能精确地知道满足这个条件的记录有多少条。而只能根据统计信息来进行估算。这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好,而一个索引上不同值的个数,我们也称之为“基数”(cardinlity)。mysql是通过采样统计的方式来获得区分度的,统计时时会选择n个数据页来统计。因此这个值是不精确的,当数据表持续变化时,当变更的数据行数超过1/m的时候,会自动触发重新做一次统计。在mysql中有两种存储索引统计的方式,可以通过参数innodb_stats_persistent的值来进行选择 。
- 设置为on的时候,表示统计信息会持久化存储,默认的m是10,n是20
- 设置为off的时候,表示统计信息值存储在内存中,此时,默认的m是16,n是8.
mysql选择错误的索引就是因为这个统计信息不准造成的。你可以通过analyze table t命令来进行修正 。
索引的选择异常和处理:
其实大多数时候mysql的优化器都会选择到正确的索引,但一旦真的发生这种情况,你可以有别的方式来修正。一是刚才提到的,使用force index强行选择一个索引。一旦使用了force index命令,优化器就不会再去评估其他的索引了。但这个方式一来代码不够优雅,二来一旦有索引的改动还需再额外修改代码。第二种方式呢,可以考虑修改语句,引导mysql使用我们期望的索引。例如在order by相关的语句中,适当调整order by后面跟的条件,可以引导优化器找到正确的索引。三是,在某些场景下,我们可以新建一个更合适的索引。
上期问题:
change buffer一开始是写内存的,那么如果这个时候及其掉电重启,会不会导致change buffer丢失呢?change buffer丢失可不是小事,因为丢失以后就无法再进行merge了,等于是数据丢失了,会不会出现这种情况呢?
答案是不会丢失,虽然只是更新内存,但在事务提交的时候,我们把change buffer的操作也记录到redo log里面去了,所以崩溃的时候change buffer也能找回来。
问题:
本篇前面的例子中,如果没有session a的配合,只是单独执行 delete from t; call idata(); explain这三条语句,会看到explain结果中rows字段其实还是再10000左右,即使用了索引,这是为什么呢?
推荐阅读
-
MySQL 笔记整理(10) --MySQL为什么有时会选错索引?
-
MySQL 笔记整理(12) --为什么我的MySQL会“抖”一下?
-
MySQL 笔记整理(19) --为什么我只查一行的语句,也执行这么慢?
-
Mysql学习笔记整理之索引
-
MySQL 笔记整理(18) --为什么这些SQL语句逻辑相同,性能却差异巨大?
-
MySQL 笔记整理(3) --事务隔离,为什么你改了我还看不见?
-
MySQL 笔记整理(4) --深入浅出索引(上)
-
MySQL 笔记整理(21) --为什么我只改一行语句,锁这么多?
-
MySQL 笔记整理(9) --普通索引和唯一索引,应该怎么选择?
-
MySQL 笔记整理(5) --深入浅出索引(下)