MySQL学习之影响优化器选择索引元素

白砂 2021-04-12

564

我们知道，在正常的开发中，我们都是使用过索引的，也知道MySQL是支持多索引的。在MySQL中，索引选择是属于优化器所做的工作。

但是我有的时候，自己觉得明明应该选择 a 索引，优化器却偏偏给我选择 b 索引，这是什么原因哦？

优化器选择索引的目的，是为了找到一个最优的执行方案，并且用最小的代价去执行语句。影响MySQL执行代价的因素有，扫描行数、临时表、排序、回表等。

扫描行数是怎么算出来的？

扫描行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。在MySQL中，每次在真正开始执行语句之前，MySQL也不能精确的知道满足条件的记录有多少条，只能进行估算。

那MySQL到底是以什么标准进行估算的呢？这里就是以索引的区分度。一个索引上不同的值越多，这个索引的区分度就越好。而索引上不同值的个数，称之为“基数（cardinality）”。也就是说这个基数越大，索引的区分度越好。

查看区分度可以使用 show index from table，可以看到每个索引上的基数。

mysql> show index from table;

索引基数是怎么得到的？

MySQL得到这个基数是采用采样统计的方法。把整张表取出来进行一行行的进行统计，这简直是不现实的，代价太高了。

MySQL 采样统计其实就是随机取样进行统计。InnoDB默认会选择 N 个数据页，统计这些页面上不同的值，得到一个平均值，然后乘以这个索引的页面数，就是这个索引的基数。

换成白话文的说法就是：取N=3个的数据页进行统计，假设共有十个索引数据页。page 1：10个不同值；page 2：20个不同值；page 3：15个不同值；索引基数就是（10 + 20 + 15）/ 3 * 10 = 150；即N个数据页的平均不同值乘以总的页面数得到基数。这个 N（表示取多少个数据页）在 innnodb 中是有默认值的。

统计索引储存的方式有两种，都是通过设置参数 innodb_stats_persistent 的值来选择，这个参数的含义是：是否启用持久化统计信息，默认值为 on。下面看下两种情况：

设置为 on 的时候，表示统计信息会持久化存储。持久化统计信息功能通过将统计信息存储到磁盘并使其在服务器重新启动期间保持不变来提高执行计划的稳定性，以便优化器更有可能每次为给定查询做出一致的选择。持久化更新统计信息时候索引页的取样页数,默认值是20
设置为 off 的时候，表示信息只存在内存当中。非持久化更新统计信息时候索引页的取样页数,默认值是8

数据表是会持续更新的，索引统计信息也不会一直固定不变。在持久化信息中，数据行变更十分之一时，也就是10%；在瞬态统计时，距离上一次更新，表中至少修改了十六分之一行或者超过20亿行时，统计信息会进行变更。

这里要注意的是，由于 MVCC 多版本并发控制影响，不同事务看到的总行数可能是不一样。

举个书本上的例子，先创建一张表：

CREATE TABLE `t` (
  `id` int(11) auto_increment,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

然后通过存储过程创建数据：

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