MySQL8.0优化（一）松散索引扫描/索引跳跃扫描ISS

(今天阅读官网，无意间发现一个小细节，分享一下，接收任何质疑)

关于多列索引（复合索引），有句话是：带头大哥不能死，中间兄弟不能丢。要使多列索引能够有效，需满足“最佳左前缀原则”。但有时候，没有带头大哥怎么办？

本地测试表 ：user1表中有210102条数据

表中建立一个多列索引：

create index idx_province_city_area on user1(user_province,user_city,user_area);

当我需要查询时，以下SQL都可以走索引：

1、select * from user1 where user_province = '山西省';
2、select * from user1 where user_province = '山西省' and user_city like '三%';
3、select * from user1 where user_province = '山西省' and user_city = '三亚市' and user_area = '乌马河区';

要使SQL能走索引，需要“满足最佳左前缀”：

1. 对于idx_sex_province_city_area 这个索引来说，user_province 是 user_city 的前缀，user_province 和 user_city 是 user_area 的前缀。

2. user_province 是 “带头大哥”，user_city 是 “中间兄弟”。

3. 以下SQL不会走索引

   -- 1、缺少带头大哥
   select * from user1 where user_city = '三亚市';
   -- 2、缺少前面所有前缀，即缺少带头大哥，中间兄弟
   select * from user1 where user_area = '乌马河区';
   

4. 缺少中间兄弟/范围条件之后，索引失效

   -- 1、缺少中间兄弟，索引只会使用能匹配到的前缀列，该例子只会使用 user_province
   select * from user1 where user_province = '山西省' and user_area = '乌马河区';
   -- 2、范围条件之后，索引失效，该例子只会使用 user_province 和 user_city
   select * from user1 where user_province = '山西省' and user_city like '三%' and user_area = '乌马河区';
   

假设我没有user_province ,但我想查询所有，以‘三’开头的市，怎么办？

select user_cityfrom user1 where user_city like '三%';

1. 在5.7版本中，根据EXPLAIN推断，使用了索引，但全表扫描，rows与全表数据量基本一致。（rows为估算值，所以不太准。rows具体怎么来的会单独讨论）

2. 在MySQL5.6版本和MySQL5.7版本中测试结果基本一致

   

   

3. 在MySQL8.0中，解决了带头大哥丢失，但依旧能走索引的问题
   官网所在位置：

   

   Using this strategy decreases the number of accessed rows because MySQL skips the rows that do not qualify for each constructed range. This Skip Scan access method is applicable under the following conditions:

   使用此策略可减少访问的行数，因为MySQL会跳过不符合每个构造范围的行。
   **此跳过扫描访问方法适用于以下情况：（8个条件）**

   • ① Table T has at least one compound index with key parts of the form ([A_1, ..., A_k]B_1, ..., B_m, C [, D_1, ..., D_n]). Key parts A and D may be empty, but B and C must be nonempty.

     表T具有**至少一个复合索引**，其关键部分的形式为（[A_1，...，A_k]B_1, ..., B_m, C [, D_1, ..., D_n])关键部分A和D**可以为空**，但B和C**必须为非空**。

   • ② The query references only one table.
     该查询仅引用一个表。

   • ③ The query does not use GROUP BY or DISTINCT.

     **查询不使用GROUP BY 或 DISTINCT.**

   • ④ The query references only columns in the index.
     该查询仅引用索引中的列。

   • ⑤ The predicates on A_1, ..., A_k must be equality predicates and they must be constants. This includes the IN() operator.

     A_1，...，A_k上的谓词必须是**相等**谓词，并且它们必须是常量。这包括

     in()操作

   • ⑥ The query must be a conjunctive query; that is, an AND of OR conditions: (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

     该查询必须是一个联合查询。即，AND或OR条件：

     (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

   • ⑦ There must be a range condition on C.
     C上必须有范围条件。

   • ⑧ Conditions on D columns are permitted. Conditions on D must be in conjunction with the range condition on C.

     D列上的条件是允许的。D上的条件必须与C上的范围条件结合使用。

   Use of Skip Scan is indicated in EXPLAIN output as follows:

   跳过扫描的使用在EXPLAIN输出中指示如下：

   • Using index for skip scan in the Extra column indicates that the loose index Skip Scan access method is used.
     在Extra列会显示 Using index for skip scan 表示所使用的松散索引跳跃扫描访问方法。

   • If the index can be used for Skip Scan, the index should be visible in the possible_keys column.

     如果索引可以用于跳跃扫描，则该索引应在possible_keys列中可见。

注：官网举例中的多列索引A_B_C_D，分三部分，A 是第一部分，为带头大哥，B_C可看作一个整体**，**为中间兄弟，D代表第三部分，可有可无

根据官网的描述，如何能够实行跳跃索引，进行以下测试：(测试表中省市区都建立了多列索引:idx_province_city_area)

1. 测试带头大哥没了,是否可以走索引

   explain select * from user1 where user_city = '三亚市' and user_area = '乌马河区';

• mysql8.0版本下该sql不走索引，为什么？ type:ALL ,rows:209046。
  
  （回看官网描述8个条件中，第4个，”该查询仅引用索引中的列“，所以需要修改sql）

  explain select user_province,user_city,user_area from user1 where user_city = '三亚市' and user_area = '乌马河区';
  

  mysql8.0只返回索引列，explain信息如下：
  
  对比上图可以发现，type:range，Extra:Using where; Using index for skip scan, rows:20904,该sql走了索引，而且是利用索引进行范围查询，预计只需要扫描20904条索引行数据

• MySQL5.7执行同样的sql，explain信息如下：
  
  通过explain信息可发现，type:index,rows:208611 使用了索引，未回表，但扫描索引的行数 = 全表数据量。由此可见，MySQL8.0支持带头大哥丢失。

   2.带头大哥丢失，挨着大哥的二弟没了，就剩三弟的时候

explain select user_province,user_city,user_area from user1 where user_area = '乌马河区';

    只有三弟的时候，还是可以的，也执行了索引跳跃扫描
3.测试 distinct 和group by是否可以索引跳跃扫描

explain select DISTINCT user_province,user_city,user_area from user1 where user_city = '三亚市' and user_area = '乌马河区';
explain select user_province,user_city,user_area from user1 where user_city = '三亚市' and user_area = '乌马河区' GROUP BY user_province,user_city,user_area;

    由explain信息可得知：用了索引全扫描

   4.测试一个重点：范围查询之后，索引是否失效

explain select user_province,user_city,user_area from user1 where user_city like '三%';explain select user_province,user_city,user_area from user1 where user_city like '三%' and user_area = '乌马河区';
explain select user_province,user_city,user_area from user1 where user_city like '三%' and user_area like '乌%';

• sql1的explain信息如下，请留意key_len，rows，filtered：

• sql2的explain信息如下：

  
  key_len，rows与sql1一模一样，但filtered变为10，代表最终结果只占rows的10%

• sql3的explain信息如下：

  
  key_len，rows与sql1一模一样，但filtered变为11.11，代表最终结果只占rows的11.11%

  由此证明，范围查询之后，依旧还是不走索引，合理。

   5.索引跳跃扫描，可以不用带头大哥，甚至不需要二弟的情况下，走索引。相当于单独建立了一个索引，但，缺点是，只能返回索引列，只能引用一张表，算内存临时表的话，是两张表，不走跳跃索引扫描，如下所示：

explain select user_id from user1 where user_city = '三亚市' and user_area = '乌马河区';
explain select user_id from user1 where user_id in (select user_id from user1 where user_city = '三亚市' and user_area = '乌马河区');
explain select * from user1 a inner join (select user_id from user1 where user_city = '三亚市' and user_area = '乌马河区') b where a.user_id = b.user_id;

• sql1需理解，多列索引为非聚簇索引，叶子节点中，包含了主键id

  
  可以走索引跳跃扫描，合理

• sql2中，利用返回的主键id进行in查询，按道理来说，是可以加快执行的

  
  结果发现并没有索引跳跃扫描，可回看8个条件中的第2个。原因暂未得知。

• sql3是sql2的变种

  
  执行结果一模一样。

   6、在大头大哥中，用 != 这个操作符

explain select user_province,user_area from user1 where user_province != '安徽省';
explain select user_province,user_area from user1 where user_province != '安徽省' and user_city = '三亚市' and user_area = '乌马河区';

• sql1的explain信息如下：

  
  rows与总数据量已相差无几，判不等本来就不走索引，合理

• sql2的explain信息如下

  
  一摸一样，没啥差异

总结：

1. MySQL8.0解决了带头大哥，二弟丢失的问题

2. 有一些限制条件，如只能返回索引列、单表查询、不能使用去重 分组，

3. 索引跳跃扫描可适用于只返回索引列的情况，或者先查出主键id，在用程序用主键id去查询数据库，增加了一次查询，但速度却会提升很多。

4. 关于索引跳跃扫描原理，官网有一个例子可解释

   CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));

   INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; ANALYZE TABLE t1;

   EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;