前言
MySQL 8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况,优化器依然能组使用组合索引。
talk is cheap ,show me the code
实践
使用官方文档的例子,构造数据
mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); Query OK, 0 rows affected (0.21 sec) mysql> INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5),(2,1), (2,2), (2,3), (2,4), (2,5); Query OK, 10 rows affected (0.07 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; Query OK, 10 rows affected (0.06 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; Query OK, 20 rows affected (0.03 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; Query OK, 40 rows affected (0.03 sec) Records: 40 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; Query OK, 80 rows affected (0.05 sec) Records: 80 Duplicates: 0 Warnings: 0
注意t1表的主键是组合索引(f1,f2),如果sql的where条件不包含 最左前缀f1 在之前的版本中会 走 FULL TABLE SCAN,在MySQL 8.0.20版本中会是怎样呢?我们看看执行计划
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 = 40\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 16 filtered: 100.00 Extra: Using where; Using index for skip scan 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 53 filtered: 100.00 Extra: Using where; Using index for skip scan 1 row in set, 1 warning (0.00 sec)
两个sql 的where条件 f2>40 和 f2=40 的执行计划中都包含了Using index for skip scan 并且 type 是range 。
整个执行计划大概如下:
第一次从Index left side开始scan
第二次使用key(1,40) 扫描index,直到第一个range结束
使用key(1), find_flag =HA_READ_AFTER_KEY, 找到下一个Key值2
使用key(2,40),扫描Index, 直到range结束
使用Key(2),去找大于2的key值,上例中没有,因此结束扫描
从上述描述可以看到使用skip-scan的方式避免了全索引扫描,从而提升了性能
如果关闭 skip_scan
特性,执行计划则变为type=all, extre using where 全表扫描。
mysql> set session optimizer_switch='skip_scan=off'; Query OK, 0 rows affected (0.01 sec) mysql> EXPLAIN SELECT * FROM t1 WHERE f2 = 40\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
限制条件
1.select 选择的字段不能包含非索引字段
比如c1 字段在组合索引里面 ,select * 的sql 就走不了skip scan
mysql> EXPLAIN SELECT * FROM t1 WHERE f2 = 40\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
2.sql 中不能带 group by或者distinct 语法
mysql> EXPLAIN SELECT distinct f1 FROM t1 WHERE f2 = 40\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 3 filtered: 100.00 Extra: Using where; Using index for group-by 1 row in set, 1 warning (0.01 sec)
3.Skip scan仅支持单表查询,多表关联是无法使用该特性。
4.对于组合索引 ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n]),A,D 可以为空,但是B ,C 字段不能为空。
需要强调的是数据库优化没有银弹。MySQL的优化器是基于成本来选择合适的执行计划,并不是所有的忽略最左前缀的条件查询,都能利用到 index skip scan。
举个例子:
mysql> CREATE TABLE `t3` ( id int not null auto_increment PRIMARY KEY, `f1` int NOT NULL, `f2` int NOT NULL, `c1` int DEFAULT '0', key idx_f12(`f1`,`f2`,c1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.24 sec) mysql> insert into t3(f1,f2,c1) select f1,f2,c1 from t1; Query OK, 320 rows affected (0.07 sec) Records: 320 Duplicates: 0 Warnings: 0
数据量增加一倍到320行记录,此时查询 f2=40 也没有利用index skip scan
mysql> explain select f2 from t3 where f2=40 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: index possible_keys: idx_f12 key: idx_f12 key_len: 13 ref: NULL rows: 320 filtered: 10.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)
-The End-
以上就是MySQL 8.0 之索引跳跃扫描(Index Skip Scan)的详细内容,更多关于MySQL 8.0 索引跳跃扫描的资料请关注其它相关文章!
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]