阅读前,请关注如下几个问题:
-
mysql如何判断查询需要扫描的行数?
-
索引基数如何计算?通过哪个参数可以设置索引统计的存储方式?
-
可以重新统计索引信息的命令是什么?
-
索引选择异常的问题可以有哪几种处理方式?
问题分析1
创建一个表,结构如下
CREATE TABLE `t` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
然后往表 t 中插入10万行记录,取值按整数递增,即:(1,1,1),(2,2,2),…,(100000,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();
首先分析一条sql语句:select * from t where a between 10000 and 20000;
使用的索引应该是 a。
如果是下面这种情况,开启一个事务A啥也不做,事务B将表数据删除,然后调用idata重新插入了10万行记录。
session A | session B |
---|---|
start transaction with consistent snapshot; | |
delete from t; call idata(); | |
explain select * from t where a between 10000 and 20000; | |
commit; |
观察结果可发现,这次查询没有索引a,通过慢查询日志查看具体执行情况:
set long_query_time=0;
select * from t where a between 10000 and 20000;
select * from t force index(a) where a between 10000 and 20000;
查看结果发现,Q1(select * from t where a between 10000 and 20000;
) 比Q2(select * from t force index(a) where a between 10000 and 20000;
) 慢了很多,也就是说Q1用错了索引。
首先明确,选择索引是优化器的工作
优化器选择索引是为了找到一个最优的执行方案,并用最小的代价去执行语句。
在数据库里,扫描行数是影响执行代价的因素之一。扫描行数越少,访问磁盘次数越少,消耗的cpu资源越少。
除了扫描行数,优化器还会解和是否使用临时表、是否排序等因素判断
那么扫描行数是如何判断的?
MySql真正执行语句前并不能精确的直到满足这个条件的记录有多少(不然还查个锤子),只能根据统计信息来估计。
那要如何估计呢?
这个统计信息就是索引的“区分度”。显然,一个索引上不同值越多,区分度越好,这个不同值被称为基数(Cardinality)。可以通过 show index from t;
查看表 t 的基数。
MySql是如何得到索引的基数呢?
使用采样统计,过程如下:
InnoDB会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了该索引的基数。
当变更的数据行超过 1/M 时,会触发重新统计。
MySql有两种存储索引统计的方式,可通过 innodb_stats_persistent
的值来选择:
- 设置为
on
时,统计信息会被持久化,此时默认N=20,M=10 - 设置为
off
时,统计信息只存储在内存,默认N=8,M=16
除了上述的索引统计,对于一个具体语句来说,优化器还要判断执行这个语句本身要扫描多少行
explain
执行计划中的 rows
字段表示预计扫描行数。
回到开始的问题,查看执行计划发现,Q2的扫描行数要远小于Q1,那么优化器为甚么还要选择Q1呢?
因为使用索引a,每次都要回表,优化器将这个代价算进去。在Q1中直接全表扫描是在主键索引上扫描的,没有Q2回表的代价。所以优化器觉得使用主键索引全表扫描更优。
优化器的逻辑没问题,问题还是在于扫描行数的判断不准确。
那么为甚么会得出错误的扫描行数呢?(思考题)
delete 删掉了所有语句,然后通过 call idata() 插入了10w行数据,看上去是覆盖了原来的10w行。
为甚么开启session A,扫描行数成了3W
因为session A开启了事务并未提交,所以之前插入的10w行数据不能删除。这样,之前的数据每一行都有两个版本,旧版本是delete 之前的数据,新版本是标记为deleted 的数据。
这样索引a上的数据其实就有两份。
为甚么开启session A但没使用force index(a),即使用了主键索引查询,使用explain查看到的扫描行数还是10w左右?即为甚么主键上的数据没有翻倍?
因为主键直接按照表的行数来估计的,而表的行数,优化器直接使用 show table status的值(10W多行).
如果统计信息不对,要如何修正呢?
使用 analyze table t;
重新统计
问题分析2
基于问题1中的表,执行另外一个查询语句,显然结果是空集。
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
先分析应该选择哪个索引
选择索引a,需要扫描索引a 的前1000个值,然后回表取到每条记录,根据b过滤。需要扫描1000行。
选择索引b,和a过程相同,需要扫描50001行。
很明显,用索引a效率更高,然而经explain 查看后,实际选择了索引b,索引选择异常,而且扫描行数也不准
方法一:可以使用force index(a)
解决这个问题。
方法二:修改语句,引导MySql使用我们期望的索引。比如:select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
,因为之前选择索引b是为了避免排序,现在索引a、b都要排序,优化器就选了a。但是这里语义被改了,而且使用了limit 1
,order by b limit 1
和 order by b,a limit 1
都会返回b最小的第一个结果,所以不是通用方法。
方法2.1:select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100) alias limit 1;
这里使用limit 100
让优化器意识到使用b索引代价很高,也不具备通用性。
方法三:在有些场景下,可以新建一个更合适的索引,来提供给优化器做选择,或者删掉误用的索引。