测试数据 :

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;


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();

测试是否命中索引 :

select * from t where a between 10000 and 20000;

explain 执行计划 :

• 命中索引 a

复现选错索引 :

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

-- 慢查询日志的阈值 = 0: 该线程一下的语句都记录到慢查询日志中
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*/

慢查询日志 ：

• Q1 扫描 10 万行，走了全表扫描，执行时间 : 40 毫秒
• Q2 扫描 10001 行，执行时间 : 21 毫秒

优化器逻辑

优化器选择索引的目的 : 找到最优的执行方案，并用最小的代价去执行语句

• 其中扫描的行数越少，访问磁盘数据的次数越少，消耗的 CPU 资源越少

扫描行数是根据统计信息来估算记录数

• 统计信息就是 : 索引的区分度
• 基数 (cardinality) : 一个索引上不同的值的个数
• 基数越大，索引的区分度越好

查看该索引的基数 :

show index from t;

MySQL 采样统计的方法 :

• InnoDB 默认选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，并乘以该索引的页面数，就得到该索引的基数
• 数据表会持续更新，索引统计信息也会变化。当变更的数据行数超过 1/M 时，会自动触发一次索引统计

两种存储索引统计的方式 :

• innodb_stats_persistent = on ： 统计信息持久化存储。默认 N : 20，M : 10
• innodb_stats_persistent = off ：统计信息存储在内存中。默认的 N : 8，M : 16

explain 结果 :

• rows : 预计扫描行数
• Q1 : rows = 104620
• Q2 : rows = 37116，偏差较大

重新统计索引信息 :

analyze table t;

例子 :

seelct * from t 
where (a between 1 and 1000) and (b bwtween 50000 and 100000) 
order by b limit 1;

a、b 索引的结构图 :

• 用索引 a 查询，扫描索引 a 的前 1000 个值，就取到对应 id，再到主键索引上查出每一行，再根据字段 b 过滤。要扫描 1000 行
• 用索引 b 查询，扫描索引 b 的最后 50001 个值，要回到主键索引上取值并判断，要扫描 50001 行
• 用索引 a ，执行速度会更快些

执行 explain :

• key : 优化器选择索引 b
• rows : 要扫描行数 : 50198

处理选错索引

方法1 ：force index 强行索引

select * from t force index(a) 
where a between 1 and 1000 and b between 50000 and 100000 
order by b limit 1;

差别 ：

方法2 ：修改语句，引导 MySQL 用期望的索引 :

select * from t 
where a between 1 and 1000 and b between 50000 and 100000 
order by b, a limit 1;

执行 explain :

• 当 a, b 都要排序时 , 就会认为扫描行数为主条件 , 并选 a 索引

方法3 ：改写 :

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;

执行 explain :

• limit 100 , 让优化器意识到 b 索引代价较高

方法4 ：与业务开发沟通，删除错误选择的索引，让优化器选择正确的索引