MySQL索引选择

10 | 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；

插入数据:

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();

分析语句：

select * from t where a between 1000 and 2000;

当explain命令如上，key是a，即优化器选择了索引a，但在做如下操作

seession A开启一个事务，session B把数据都删除，又调用idata存储过程，插入10万行数据。
这时候，session B的查询语句就不会选择索引a（在mysql8.0后没有这个问题）。

优化器的逻辑

• 选择索引是优化器的工作
• 优化器选择索引是找到最优方案，在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，以为这访问磁盘数据的次数越少，消耗的cpu越好啊。
• 优化器会结合是否使用临时表，是否排序等进行综合判断执行代价

1. 扫描行数的判断

• Mysql在执行语句之前，不会精确的直到满足这个条件的记录又多少，只能根据统计信息来估算记录。
• 统计信息 就是索引的区分度。索引不同值（基数）越多，区分度越好，例如：索引值只有0，1和索引值有0-10，后者区分度明显好于前者。
• 使用show index 可以看到一个索引基数
  
• 如图所示，三个索引的基数值不同，且都不准确。

2. Mysql怎么得到索引的基数

• Mysql的采样统计

1. 整张表取出来进行统计代价很高，所以使用采样统计
2. 采样统计，InnoDB会选择N个数据页，统计这些页面上的不同值，得到平均，再乘以索引页面数，就得到索引基数。
3. 当变更的数据行数超过1/M的时候，会再触发索引统计

---

• Mysql中两种储存索引统计，通过参数innodb_stats_persistent来设置

1. 设置为on，统计信息持久化存储，默认的N是20，M是10.
2. 设置off，统计信息存储再内存中，默认N是8，M是1

• 优化器判断 扫描行数是通过索引统计的值来判断的，而索引统计的值不是通过全局的统计，而是采用一种抽样统计的方式，用抽样的页面索引平均值乘以页面数估算出来的索引基数，这样如果采样的页面刚好索引分布的很密集的话，误差就会很大
• 使用普通索引回表，优化器会将这个时间成本算入，全表扫描直接在主键索引下扫描
• 
• 
• analyze table t重新统计索引信息

3.另一种情况

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

• 执行上述sql语句，这个语句会返回空集合
• 优化器会选择索引：

1. 使用a进行查询，就是扫描a的前1000个值，渠道对应id，再到主键索引上取出每一行根据B过滤，扫描1000行
2. 使用b进行查询，找到最后值，遇上相同，需要扫描50001行

• 
• 这次优化器选择了索引B，rows显示了49164

1. 扫描行数的估计值不准确。
2. Mysql又选错了索引。

• 为什么

1. 因为又order by b,优化器认为走索引b可以避免排序。
2. limit 1 让优化器认为只要找到一条满足记录的条件就可以提前终止，即使要扫描500001条记录，但是这是值得冒险的，所以走了b

4. 索引选择异常和处理

• 大部分情况优化器可以找到正确索引，但是出现例外时：

1. 采用force index 强行选择索引，MySQL会根据语法解析选择索引，然后判断，采用force index 会让优化器直接选择这个索引，不会评估其他索引代价。
2. 修改语句，引导Mysql使用我们期望的索引，比如将order by b limit 1 改为 order by b,a limit 1,如前言，order by b 导致优化器认为使用索引b可以避免排序，改成order by b,a 意味这都需要排序，因此扫描行数成为主要决策条件。
3. 新建合适索引，提供给优化器做选择，或删除误用的索引。