MySQL 8.0 之索引跳跃扫描(Index Skip Scan)
前言
MySQL8.0.13开始支持indexskipscan也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况，优化器依然能组使用组合索引。
talkischeap，showmethecode
实践
使用官方文档的例子，构造数据
mysql>CREATETABLEt1(f1INTNOTNULL,f2INTNOTNULL,PRIMARYKEY(f1,f2)); QueryOK,0rowsaffected(0.21sec) mysql>INSERTINTOt1VALUES(1,1),(1,2),(1,3),(1,4),(1,5),(2,1),(2,2),(2,3),(2,4),(2,5); QueryOK,10rowsaffected(0.07sec) Records:10Duplicates:0Warnings:0 mysql> mysql>INSERTINTOt1SELECTf1,f2+5FROMt1; QueryOK,10rowsaffected(0.06sec) Records:10Duplicates:0Warnings:0 mysql>INSERTINTOt1SELECTf1,f2+10FROMt1; QueryOK,20rowsaffected(0.03sec) Records:20Duplicates:0Warnings:0 mysql>INSERTINTOt1SELECTf1,f2+20FROMt1; QueryOK,40rowsaffected(0.03sec) Records:40Duplicates:0Warnings:0 mysql>INSERTINTOt1SELECTf1,f2+40FROMt1; QueryOK,80rowsaffected(0.05sec) Records:80Duplicates:0Warnings:0
注意t1表的主键是组合索引(f1,f2)，如果sql的where条件不包含最左前缀f1在之前的版本中会走FULLTABLESCAN,在MySQL8.0.20版本中会是怎样呢？我们看看执行计划
mysql>EXPLAINSELECTf1,f2FROMt1WHEREf2=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:Usingwhere;Usingindexforskipscan 1rowinset,1warning(0.01sec) mysql>EXPLAINSELECTf1,f2FROMt1WHEREf2>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:Usingwhere;Usingindexforskipscan 1rowinset,1warning(0.00sec)
两个sql的where条件f2>40和f2=40的执行计划中都包含了Usingindexforskipscan并且type是range。
整个执行计划大概如下:
第一次从Indexleftside开始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,extreusingwhere全表扫描。
mysql>setsessionoptimizer_switch='skip_scan=off'; QueryOK,0rowsaffected(0.01sec) mysql>EXPLAINSELECT*FROMt1WHEREf2=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:Usingwhere 1rowinset,1warning(0.00sec)
限制条件
1.select选择的字段不能包含非索引字段
比如c1字段在组合索引里面，select*的sql就走不了skipscan
mysql>EXPLAINSELECT*FROMt1WHEREf2=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:Usingwhere 1rowinset,1warning(0.00sec)
2.sql中不能带groupby或者distinct语法
mysql>EXPLAINSELECTdistinctf1FROMt1WHEREf2=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:Usingwhere;Usingindexforgroup-by 1rowinset,1warning(0.01sec)
3.Skipscan仅支持单表查询，多表关联是无法使用该特性。
4.对于组合索引([A_1,…,A_k,]B_1,…,B_m,C[,D_1,…,D_n])，A,D可以为空，但是B,C字段不能为空。
需要强调的是数据库优化没有银弹。MySQL的优化器是基于成本来选择合适的执行计划，并不是所有的忽略最左前缀的条件查询，都能利用到indexskipscan。
举个例子：
mysql>CREATETABLE`t3` (idintnotnullauto_incrementPRIMARYKEY, `f1`intNOTNULL, `f2`intNOTNULL, `c1`intDEFAULT'0', keyidx_f12(`f1`,`f2`,c1)) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4; QueryOK,0rowsaffected(0.24sec) mysql>insertintot3(f1,f2,c1)selectf1,f2,c1fromt1; QueryOK,320rowsaffected(0.07sec) Records:320Duplicates:0Warnings:0
数据量增加一倍到320行记录，此时查询f2=40也没有利用indexskipscan
mysql>explainselectf2fromt3wheref2=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:Usingwhere;Usingindex 1rowinset,1warning(0.00sec)
-TheEnd-
以上就是MySQL8.0之索引跳跃扫描(IndexSkipScan)的详细内容，更多关于MySQL8.0索引跳跃扫描的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。