10 mysql选错索引

10 mysql选错索引

在mysql表中可以支持多个索引，有的sql不指定使用哪个索引，由mysql自己来决定，但是有时候mysql选错了索引，导致执行很慢。

例子

CREATE TABLE `t10` (
  `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;

往表中插入10w记录

过程

delimiter ;;
create procedure idata_t11()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t10 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata_t11();

分析sql语句

mysql> select * from t where a between 10000 and 20000;

SESSION A	SESSION B
start transaction with consistent snapshot;
	delete from t; call idata_t11()
	explain  select * from t10 where a between 10000 and 20000;
commit;

Session B的查询语句explain  select * from t10 where a between 10000 and 20000;就不会在选择所有a了，可以通过慢查询日志

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

优化器的逻辑

选择索引是优化器的工作，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的cpu资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素综合判断。

扫描行数是怎么判断的？

MySQL在真正开始语句执行之前，并不能精确地知道满足这个条件的记录是多少，只能根据统计信息来估算记录数。

这个统计信息就是索引的”区分度”，显然，一个索引上不同的值越多，这个索引的区分度也就越高，而一个索引上不同的值的个数，称为”基数”，这个基数越大越好。

Mysql采样统计的方法，采样统计的时候，innodb默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变，所以，当变更的行数超过1/M的时候，会自动触发重新做一次索引统计。

在mysql中，有两种存储索引统计的方式，可以通过参数innodb_stats_persitent

(system@127.0.0.1:3306) [test]> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+

--设置为on，表示统计信息会持久化到存储，默认N=20，M=10

--设置为off，表示统计信息只存储在内存中，默认N=8，M=16

其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句要扫描多少行

统计信息不准确，使用analyze table t10;

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

索引选择异常和处理

一种方法是，采用force index(t)强行选择一个索引。

第二种方式就是，可以考虑修改语句，引导mysql使用我们期望的索引。

之前优化器选择使用索引b，因为它认为使用索引b可以避免排序（b本身是索引，已经是有序的，如果选择索引b的话，不需要在做排序只需要遍历），所以即使扫描行数更多，也判定为代价更小。

现在使用了 order by b,a，要求按照b,a排序，就意味着使用这两个索引都需要排序，因此扫描行数变成了影响决策的主要条件，此时就选择了a索引。

当然，这种修改并不是通用的优化手段，只是刚好在这个语句有limit 1，如果都有满足条件的行，要逻辑结果一致才可以这么修改。

第三种是在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或者删掉误用的索引。