Mysql实战09：Mysql为什么有时候会选错索引

最新推荐文章于 2024-09-10 10:54:55 发布

lamdaxu

最新推荐文章于 2024-09-10 10:54:55 发布

阅读量109

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_45119323/article/details/114383741

版权

数据库专栏收录该内容

19 篇文章 0 订阅

订阅专栏

我们知道Mysql中的一张表其实可以支持多个索引的，但是，你写SQL语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由Mysql来确定的。

有时候，一条本来可以执行很快的语句，却由于Mysql选错了索引，而导致速度变得很慢？

先创建一个简单的表，表里面有a、b两个字段，并分别建上索引：

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；

然后，我们往表t中插入10万行记录，取值按整数递增，即：(1,1,1),(2,2,2),(3,3,3)直到(100000,100000,100000)。
这里采用了存储过程来插入

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

现在来分析一条SQL语句：

mysql> select * from t where a between 10000 and 20000;

使用explain命令来看这条语句的执行情况
在这里插入图片描述
key这个字段是’a’，表示优化器选择了索引a。没有什么问题，我们现在来看另外一种情况。

在这里插入图片描述
这里session A开启了一个事务，随后，session B把数据都删除后，又调用了idata这个存储过程，插入了10万行数据。

这时候，session B的查询语句select * from where a between 10000 and 20000就不会再选择索引a了。我们可以通过慢查询日志(slow log)来查看具体的执行情况。

为了说明优化器选择的结果是否正确，我增加了一个对照，即使用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是加了force index(a)来和session B原来的查询语句执行情况对比

下图是三条SQL语句执行完后的慢查询日志。
在这里插入图片描述
从日志中可以看到，Q1扫描了10万行，显然是走了全表扫描，执行的时间是40毫秒。Q2扫描了10001行，执行了21毫秒。也就是说，我们在没有使用force index的时候，Mysql用错了索引，导致了更长的执行时间。

优化器逻辑

我们知道，选择索引是优化器的工作。而优化器选择索引的目的，是找到要给最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘的数据次数越少，消耗的CPU资源越少。

但扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。但是我们这个简单的查询语句并没有涉及到临时表和排序，所以Mysql选错索引肯定是判断扫描行数的时候出问题了。

Mysql在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同观点值越多，这个索引的区分度就越好。而一个索引上不同值的个数，我们称之为“基数”。也就是说，这个基数越大，索引的区分度越好。

我们可以使用show index方法，看到一个索引的基数。就是表t的show index的结果。虽然这个表的每一行的三个字段都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。
在这里插入图片描述
那么Mysql是怎样得到索引的基数呢？
Mysql使用的是采样统计法。为什么要采样统计？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高。所以选择采样统计。