前言

今天在群里看到一个这样的文章: MySQL遵循最左前缀匹配原则！面试官：回去等通知吧

所以特意看了下,无非就是2个名词,1个是索引跳跃扫描(INDEX SKIP SCAN),一个是mysql复合索引,所以今天我们来讲讲这个东西.

索引跳跃扫描(INDEX SKIP SCAN)

索引跳跃扫描其实是Oracle数据库支持的其中一种索引扫描类型,它的扫描类型有

• 1.索引唯一扫描(INDEX UNIQUE SCAN)
• 2.索引范围扫描(INDEX RANGE SCAN)
• 3.索引全扫描(INDEX FULL SCAN)
• 4.索引跳跃扫描(INDEX SKIP SCAN)
• 5.索引快速全扫描(INDEX FAST FULL SCAN)

索引跳跃扫描是oracle 9i之后提供的新功能,适用于所有的复合B树索引,包含了唯一索引和非唯一索引

当where语句的查询条件不存在索引的前导列(即前缀),也可以适用该索引

例如:test表中存在b树索引列(t1,t2)
正常来说:

select * from test where t1=1 and t2=1;# 可以走索引
select * from test where t2=1;# 不能走索引

但是在索引跳跃扫描中,该语句会优化成:

select * from test where t1=1 and t2=1
union all
select * from test where t1=2 and t2=1
...
...

可以看出,它将t2=1分为了t1的所有值+and t2=1 union all的结果,使得它支持了索引

例如,当t1的索引列存在 1-100时,会使用t1=1,t1=2,t1=3直到100,and t2=1 之后获得结果

当然,优化器会根据预估数进行优化,如果t1数据量够大,也可能不走索引

mysql

回到mysql中,mysql中,在8.0.13版本开始,也提供了类似的跳跃扫描,但是和oracle的跳跃扫描不一样,局限性非常大,可查看官方文档:https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan

限制条件如下:

• 表 T 至少有一个复合索引，其关键部分的形式为 ([A1, ..., A k,] B1, ..., B m, C [, D1, ..., D n])。关键部分 A 和 D 可以为空，但 B 和 C 必须为非空。

• 该查询仅引用一个表。

• 查询不使用GROUP BYor DISTINCT。

• 查询仅引用索引中的列。

• A1, ..., A 上的k谓词必须是等式谓词并且它们必须是常量。这包括 IN()运营商。

• 查询必须是联合查询；也就是说，一个 AND条件OR ： (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

• C 上必须有范围条件。

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

这里面有个比较重要的点,查询时仅能使用索引中的列,也就是说,不能select * ,只能select 索引列+主键

同时,在我的测试中,只要是select 索引列,不管是5.0还是8.0,都可以走到索引(但这并不能说明5.0的索引优化比8.0的好):

总结

总之,在mysql8中,实现了跳跃扫描,但是局限性大,用处不大,不如oracle
如果有查询索引的需求,还是自己想办法优化,避免没有前缀条件的情况吧