原创

mysql8不需要前缀即可走索引?

温馨提示:
本文最后更新于 2023年02月03日,已超过 437 天没有更新。若文章内的图片失效(无法正常加载),请留言反馈或直接联系我

前言

今天在群里看到一个这样的文章: 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的好):
file

总结

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

正文到此结束
本文目录