前言
本文简要介绍了优化器选择索引的依据,以及如何人为地引导优化器选择较好的执行方案。
[En]
This paper briefly introduces the basis for the optimizer to select the index and the ways to artificially guide the optimizer to choose a better execution scheme.
为什么会出现选错索引
可能是统计索引基数信息错误,导致优化器错选索引,也可能是 MySQL
的 bug
。
选择依据
优化器根据扫描的行记录的数量、返回表的次数、是否创建临时表以及是否对其进行排序来选择索引。
[En]
The optimizer selects the index based on the number of scanned row records, the number of times the table is returned, whether to create a temporary table, and whether to sort it.
索引扫描行数。通过取样的方式统计索引列上不同的值数量,取 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
如果在执行事务 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
行。
Original: https://www.cnblogs.com/flowers-bloom/p/select-error-index.html
Author: flowers-bloom
Title: MySQL45讲之优化器选错索引
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/508167/
转载文章受原作者版权保护。转载请注明原作者出处!