MySQL索引选择规划

shysh95

发布于 2022-02-16 21:31:04

9830

发布于 2022-02-16 21:31:04

文章被收录于专栏：shysh95shysh95

-- 创建测试表
CREATE TABLE `t` (
  `id` int primary key auto_increment,
  `a` int default null,
  `b` int default null,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

-- 插入10w行测试数据
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();

mysql全表扫描

explain select * from t where a between 10000 and 20000;

通过explain的执行结果我们可以看出，上面的SQL语句并没有走我们的索引a，而是直接使用了全表扫描。

-- 强制走索引a
explain select * from t force index(a) where a between 10000 and 20000;

通过explain的执行结果我们可以看出，上面的SQL语句我们通过force index(a)以后，确实使用了索引。

-- 开启慢日志
set global slow_query_log  = true;
set long_query_time = 0;
-- 分别执行不走索引和走索引的SQL
select * from t where a between 10000 and 20000;
select * from t force index(a) where a between 10000 and 20000;

可以看出走索引的查询比不走索引的查询快了将近10ms。

优化器如何选择索引？

优化器会根据扫描行数、是否使用临时表、是否排序等因素进行综合判断。

扫描行数如何判断？

MySQL优化器只能根据统计信息来估算实际的记录数，该统计信息称为区分度。一个索引上不同的值越多，区分度越高。一个索引上的不同值的个数称之为基数。

-- 查看表t上的索引基数
show index from t;

该索引基数不是准确的，是采样获得。

索引基数如何获取？

索引基数如果真的基于表的所有数据精准计算，代价过高，因此基数在获取的时候还是基于采样获得。

采样统计时，InnoDB会默认选择N个数据页，统计这样写页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，结果即索引的基数。

随着数据表的不断更新，索引基数也需要更新，当变更的行数超过1/M的时候，会自动重新做一次索引基数计算。

MySQL中有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent 的值来选择：

设置为on，表示统计信息会持久化存储，N为20，M为10
设置为off，表示统计信息只存储在内存中，N为8，M为16

-- 重新修正统计信息
analyze table t;

优化器为什么选择了扫描行数多查询？

通过刚开始的explain语句，不走索引需要扫描98811行，而走索引a只需要10001行，但优化器最终选择了全表扫描。

原因是全表扫描是在主键索引上扫描，没有回表操作的额外代价，优化器会自己估算全表扫描和索引a的代价，从上述结果来看，优化器认为全表扫描更加快捷，但是我们通过慢日志可以看出优化器的这个选择并不是最优解。

如何处理索引选择异常？

可以使用force index强制走某个索引，但该方法弊端过于明显，索引名称变更受影响，如果迁移到别的数据库语法不兼容
修改SQL语句，引导MySQL优化器选择正确的索引
新建一个更合适的索引，删除误用的索引，来给优化器选择

-- 修改SQL语句，引导MySQL优化器选择正确的索引
explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

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

本文参与?腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2022-02-08，如有侵权请联系?cloudcommunity@tencent.com 删除

sql