Loading

MySQL45讲之优化器选错索引

前言#

本文简要介绍优化器选择索引的依据有哪些,有什么办法人为引导优化器选择更优的执行方案。

为什么会出现选错索引#

可能是统计索引基数信息错误,导致优化器错选索引,也可能是MySQLbug

选择依据#

优化器选择索引的依据有扫描行记录数、回表次数、是否创建临时表和是否排序。

索引扫描行数。通过取样的方式统计索引列上不同的值数量,取N张数据页,统计页面上不同值的数量,然后估算总的不同数量(基数cardinality),这也称之为索引的“区分度”。可以通过SHOW INDEX FROM table_name来查看每个索引的基数。

对于这条执行语句 SELECT * FROM t WHERE a BETWEEN (1, 10000) AND b BETWEEN (50000, 100000) ORDER BY b LIMIT 1,从扫描行数上考虑,应该选择a上的索引,但通过MySQL执行计划发现,实际选择了b索引。因为优化器考虑了需要根据b排序,选择b索引扫描获取记录可以避免再排序。但是,使用a索引的查询耗时远比使用b索引耗时低。

如何避免选错索引#

1、强制使用索引

使用force Index(a)强制SQL执行时采用某个索引,比如SELECT * FROM t force Index(a) WHERE a BETWEEN (1, 10000) AND b BETWEEN (50000, 100000) ORDER BY b LIMIT 1

2、重新计算基数

因为MySQL取样估计基数可能存在比较大的误差,导致优化器选择低效的执行方案。为了避免这个统计信息的问题,可以使用Analyze TABLE t来重新统计信息。

3、选择更合适的索引或者删除误用的索引

提问#

# 表结构
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;
# 创建过程,插入 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();

执行两个事务

如果在执行事务B之前不开启事务A,那么语句explain select * from t where a between 10000 and 20000;扫描行数约为10000行;当开启事务A,则扫描行数约为37000行,这是为什么呢?

解释:
不开启事务A扫描行数为10000行,因为MySQL采用的是标记删除的方法,在purge线程还未执行之前,索引树和表数据并没有清除。当新插入100000行数据时,因为主键和已经删除的相同,所以会直接复用之前删除的空间,所以优化器抽样判断扫描的行数是10000行。

开启事务A扫描行数为37000行,因为事务A开启了一致性读,于是新插入数据时,不能复用已经删除的空间,必须开辟新的空间存储,使得索引数据页的数据更加密集,从而优化器抽样判断扫描行数是37000行。

posted @   flowers-bloom  阅读(163)  评论(0编辑  收藏  举报
编辑推荐:
· 在.NET Core中使用异步多线程高效率的处理大量数据
· 聊一聊 C#前台线程 如何阻塞程序退出
· 几种数据库优化技巧
· 聊一聊坑人的 C# MySql.Data SDK
· 使用 .NET Core 实现一个自定义日志记录器
阅读排行:
· 字节豆包,来园广告
· 为什么推荐在 .NET 中使用 YAML 配置文件
· 在 .NET Core 中使用 Channel 实现生产者消费者模式
· C#字符串拼接的几种方式及其性能分析对比
· 干掉EasyExcel!FastExcel初体验
点击右上角即可分享
微信分享提示
主题色彩