MySQL 笔记整理(10) --MySQL为什么有时会选错索引?

时间:2022-03-20 14:42:30

笔记记录自林晓斌(丁奇)老师的《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。不过别急,这个例子没有这么简单,我们继续来看:

  MySQL 笔记整理(10) --MySQL为什么有时会选错索引?

  其中 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的查询。

  对比结果如下:

  MySQL 笔记整理(10) --MySQL为什么有时会选错索引?

  很容易看出第一行查询了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左右,即使用了索引,这是为什么呢?