范围查询优化：索引跳跃扫描
如果是联合索引的话，在构造B+树的时候，会先按照左边的 key进行排序，左边的 key 相同时再依次按照右边的 key 排序。在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。
索引跳跃扫描
MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，但是在MySQL 8.0出现了索引跳跃扫描。
考虑以下场景：
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;
为了执行这个查询，需要进行一次全表扫面才能查出f2 > 40的结果集。
范围扫描比完整索引扫描更有效，但在这种情况下不能使用，因为没有f1的参与。然而，从 MySQL 8.0.13 开始使用一种称为 索引跳跃扫描（Index Skip Scan） 的方法，优化器可以执行多个范围扫描，对于每一个 f1 字段的值，进行 f2 范围扫描。
最终执行的SQL语句是像下面这样的：
SELECT f1, f2 FROM t1 WHERE f1 = 1 AND f2 > 40;
union all
SELECT f1, f2 FROM t1 WHERE f1 = 2 AND f2 > 40;
索引跳跃扫描的使用条件
使用这种策略可以减少访问的行数，因为 MySQL 跳过了不符合范围的行。在以下条件下，可以应用索引跳跃扫描：
- 表 T 至少有一个复合索引，其键部分的形式为 ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])。键部分 A 和 D 可能为空，但 B 和 C 必须非空。
- 查询只引用一个表。
- 查询不使用 GROUP BY 或 DISTINCT。
- 查询只引用索引中的列。
- 关于 A_1, …, A_k 的谓词必须是等值谓词，并且它们必须是常量。这包括 IN() 操作符。
- 查询必须是合取查询；也就是说，是 OR 条件的 AND：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR …) AND …
- 必须对 C 有一个范围条件。
- 允许对 D 列的条件。D 上的条件必须与 C 的范围条件一起使用。
EXPLAIN输出中使用索引跳跃扫描的指示是Using index for skip scan。
索引跳跃扫描的开关
MySQL是否使用索引跳跃扫描取决于optimizer_switch系统变量的skip_scan的值。默认情况下，此标志为开。要禁用它，将 skip_scan 设置为 off 即可。
使用下面语句，可以查询到skip_scan的值：
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@@optimizer_switch, 'skip_scan=', -1), ',', 1) AS skip_scan_status;
参考链接
https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html
https://blog.csdn.net/yy139926/article/details/128544678
原文地址:https://blog.csdn.net/kaka_buka/article/details/135869979
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_63445.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
