欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-01-03 00:03:10
笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》 (本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除) 10) --MySQL为什么有时会选错索引? MySQL中的一张表上可以支持多个索引的,但是,往往你写SQL语句的时候不会去主动指定使用哪个索引。也就是说,使用哪个索引是由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。不过别急,这个例子没有这么简单,我们继续来看:

  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左右,即使用了索引,这是为什么呢?

上一篇:

下一篇: