索引选择与查询语句优化

#索引选择

MySQl在一张表中有多个索引，在我们书写查询语句的时候没有指定使用哪个索引来进行查询，这部分操作是由MySQL的查询优化器来决定的。但是查询优化器有选择错误的情况，常见的几种如下（丁奇大佬列举的列子我只是做了归纳整理）

优化器的逻辑

选择索引是优化器的工作，优化器的目的是找到最优的执行方案主要影响索引选择的因素如下几个

• 扫描行数
• 是否临时表
• 是否排序
• 是否回表

基础表

表结构

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

扫描行数判断

MySQL在执行语句时不能精确的知道满足记录的条数(影响行)，而是进行统计估算的方式。统计信息就是索引的区分度，在索引上不同的值越多区分度越好，索引不同值的个数叫基数。也就是说基数越大索引区分度越好。

索引基数查看

show index from t;

索引基数采用采样统计的方法，InnoDB默认选择N个数据页，统计这些页上的不同值，得到平均值然后乘索引的页面数。

案例一

这个案例讲到是错误的影响行数诱导索引优化器进行全表扫描，表结构和数据如上述。我们先执行下查询语句然后进行如下操作

EXPLAIN SELECT *FROM t WHERE a BETWEEN 10000 AND 20000;

可以注意到我们查询t表的 1W - 2W 区间 用到索引a 并且影响 1W行。

下面我们按需进行如下操作

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;

start transaction with consistent snapshot; 会在语句执行时就启动事务,这个案例中我反复试验得出的结果如下

虽然我在本机没有试出想要的未提交事务干扰查询优化器进行影响行数判断，但是这种情况的确存在，初步猜想是试验中的MySQL版本对这块查询优化器做了升级MySQL5.6.43。对于这种cardinality大于或者小于实际数值的情况我们一般用 analyze table t 来进行优化

ps:理论上来说cardinality应该大于实际值，这边反而小于。猜想这是试验不成功的原因

analyze 后

案例二

这个案例讲的是优化器误判的情况，表结构和数据依然如上。

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

上述语句我们要查询 a 1-1K 范围内的数据 和 b 在 5W-10W范围内的数据 并且按照b 升序排序取第一条。人肉分析一波在这个SQL下去后执行的过程

• 如果使用索引a，会先查到前1000个满足条件的数据，然后回表到主键上找到相应的b值查看是否满足条件。

• 如果使用索引b，会找到50000个满足条件的数据，然后回表到主键上找到相应的a值是否满足条件。

我们认为索引选择a是最优的，但是在实际的操作过程中会出现如下情况

为什么会这样呢？因为后面还有个 order by b limit 1 这样选择b就可以避免排序（b本身是索引已经有排序了）。如果 limit 1 大于了 a between 1 and 1000 优化器会怎么做呢？

优化

• 强制指定索引 force index(a)

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

• 把a加入排序

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

• 删除索引b

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