mysql索引知识点总结

MySQL 8.0 之索引跳跃扫描Index Skip Scan

前言

MySQL 8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况,优化器依然能组使用组合索引。

talk is cheap ,show me the code

实践

使用官方文档的例子,构造数据

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 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版本中会是怎样呢?我们看看执行计划

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 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 全表扫描。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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 语法

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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。

    举个例子:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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 索引跳跃扫描的资料请关注开心学习网其它相关文章!

    原文链接:https://cloud.tencent.com/developer/article/1641469