MySQL45讲之优化器选错索引
前言#
本文简要介绍优化器选择索引的依据有哪些,有什么办法人为引导优化器选择更优的执行方案。
为什么会出现选错索引#
可能是统计索引基数信息错误,导致优化器错选索引,也可能是MySQL
的bug
。
选择依据#
优化器选择索引的依据有扫描行记录数、回表次数、是否创建临时表和是否排序。
索引扫描行数。通过取样的方式统计索引列上不同的值数量,取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
行。
【推荐】中国电信天翼云云端翼购节,2核2G云服务器一口价38元/年
【推荐】博客园携手 AI 驱动开发工具商 Chat2DB 推出联合终身会员
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在.NET Core中使用异步多线程高效率的处理大量数据
· 聊一聊 C#前台线程 如何阻塞程序退出
· 几种数据库优化技巧
· 聊一聊坑人的 C# MySql.Data SDK
· 使用 .NET Core 实现一个自定义日志记录器
· 字节豆包,来园广告
· 为什么推荐在 .NET 中使用 YAML 配置文件
· 在 .NET Core 中使用 Channel 实现生产者消费者模式
· C#字符串拼接的几种方式及其性能分析对比
· 干掉EasyExcel!FastExcel初体验