您的当前位置:首页>新品 > 正文

mysql索引选择错误的几种情况 mysql之优化器选择索引|天天热门

来源:CSDN 时间:2023-02-24 08:16:45

文章目录


(相关资料图)

前言索引选择错误的几种情况统计异常排序导致的异常 扫描行数数的统计总结

前言

之前文章,把索引和日志串起来介绍了一下,介绍了什么时候时候用普通索引,什么时候用唯一索引。还说明了,为什么要用普通索引,为什么要用唯一索引。和使用的原因。今天我们把索引和第一章介绍的mysql大致组成串联起来,来介绍下优化器的索引选择。

索引选择错误的几种情况

统计异常

首先创建一个表

CREATE TABLE `t` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `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)到(100000,100000,100000),然后执行下面这个sql

select * from t where a between 10000 and 20000;

这个时候呢,优化器会选择那个索引呢。结果看下面 他会选择a索引。 但是同样的查询,如果是下面的这个查询过程 他会是什么结果呢。它会走主键索引进行全表扫描。那为什么放着索引a不走,非得走主键索引呢。有两个原因 1.主键索引和普通索引扫描行数相差不大得情况下,他会选择主键索引,这是因为走普通索引需要回表(这个之前得章节有介绍,这里不再介绍了)。优化器会认为,回表的时间会大于多扫描的那几行。 2.索引统计错误,就是比如现在这个,主键索引是100000多行,普通索引是30000多行,相差这么大。它为什么还要走主键索引呢,这个就是它在统计索引时出现了异常,可能统计普通索引a可能需要扫描9万多行,或者10万多行。这个时候就是第一种的情况了。 统计索引出现异常怎么办呢。有两种方案 1.analyze table t 命令,可以用来重新统计索引信息。 2.force index(a) 强行指定索引

排序导致的异常

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

看上面这个语句,走a的话只要扫面1000多行,走b的话需要扫面5000多行,正常来说应该是走a快,事实也是走a快,但是优化器却选择了走b,这个是为什么呢。首先这个表已经矫正过索引的信息了,不会出现统计异常的情况了。那是什么原因呢。 那是因为order by b,结果需要根据b排序,走索引b 的话不需要排序了,b直接是有序的。优化器认为排序时间会大于多扫描那些行数的时间。所以会选择走b。那有什么解决方案呢。 1.将sql的order by b改为 order by a,b,这样的话系统会认为a和b都需要排序,这样它就会选择扫描行数少的。 2.创建一个更好的索引,或者删除一些索引。比如上面这个就可以直接删除索引b,这样它就会走索引a了。 3.force index(a) 强行指定索引,上面统计异常也可以这么干。

select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

扫描行数数的统计

上面说了那么多,基本都是基于扫描行数为基准的,那么扫描行数是怎么统计的。首先,在开始扫描之前,优化器是不会知道,具体的扫描行数的。而是根据统计信息估算出扫描行数。统计信息又是什么呢。统计信息是一个索引的区分度,也就是一个索引上不同值得多少。而一个索引上不同值得个数,被称为统计基数。所以统计基数越大,也就是区分度越好。 那么怎么得到统计基数呢,首先一行一行扫描不现实,虽然精确,但是太耗费时间。所以mysql采用了采样统计得方式,因为mysql数据在磁盘中是以数据页得形式存储的。mysql会选择n个数据页,算出每个数据也的基数平均值,最后乘以这个索引的数据页数,得到这个索引的基数。而优化器就是根据这个基数进行索引选择的,虽然有一定的偏差,但是基本不会影响正确索引的选择。

总结

今天我们介绍了,一些常见的索引选择错误的情况,大致就两种。然后还介绍了每种的情况所对应的解决方案有哪些,方案很多,大家可以自信选择。比较通用的是指定索引,但是不见建议大家这么干。因为指标不治本,不太好。最后就是介绍优化器选择的关键,扫描行数的怕判定。

标签:

最新新闻:

新闻放送
Top