mysql索引知识点总结(MySQL 8.0 之索引跳跃扫描Index Skip Scan)
mysql索引知识点总结
MySQL 8.0 之索引跳跃扫描Index Skip Scan前言
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 索引跳跃扫描的资料请关注开心学习网其它相关文章!
原文链接:https://cloud.tencent.com/developer/article/1641469
- phpstudy怎么修改mysql版本(PhpStudy集成环境升级MySQL数据库版本的方法)
- navicat创建MySql定时任务的方法详解(navicat创建MySql定时任务的方法详解)
- navicat premium连接mysql报错(Navicat连接MySQL错误描述分析)
- 如何用mysql建立图书管理系统(图书管理系统的sqlserver数据库设计示例)
- 命令行如何使用xampp中的mysql(解决xampp自启动和mysql.sock问题)
- binlog怎么恢复mysql数据库(mysql5.7使用binlog 恢复数据的方法)
- mysql8.0.16安装步骤图解(mysql 8.0.22 安装配置图文教程)
- mysql中timestamp类型的CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP属性
- mysql索引的类型和方法(MySQL覆盖索引的使用示例)
- MySql存储过程
- mysql server安装图解教程(mysql容器之间的replication配置实例详解)
- mysql并发查询优化(详解MySQL 联合查询优化机制)
- 最新版mysql 安装教程(新手如何安装Mysql亲测有效)
- mysql详细学习笔记(Mysql常用命令 详细整理版)
- idea向数据库中插入中文报错(Idea连接MySQL数据库出现中文乱码的问题)
- docker搭建mysql服务(Docker部署Mysql集群的实现)
- 荣耀手表 GS 3 真机亮相 不支持无线充电(荣耀手表GS3)
- 通过体温就能为智能手表充电 原来是用NASA在空间站用的黑科技(通过体温就能为智能手表充电)
- 智能手表兼容Windows和Android 无需充电挑战苹果(智能手表兼容Windows和Android)
- 一天一冲也算表 麦步,一款待机 21 天的智能手表体验评测(一天一冲也算表)
- 魅族智能手表充电座曝光 Type-C 接口,线座分离设计(魅族智能手表充电座曝光)
- 华为 Watch GT2 Pro 智能手表曝光,新增支持无线充电(华为WatchGT2)
热门推荐
- dedecms缩略图优化教程(织梦DedeCMS V5.7缩略图站内上传修正)
- 在docker中运行springboot(Linux Docker运行springboot项目的步骤详解)
- axios并发请求数据(项目中如何使用axios过滤多次重复请求详解)
- MYSQL中GROUP BY不包含所有的非聚合字段时的注意事项
- node中使用token(浅谈node使用jwt生成的token应该存在哪里)
- nodejs请求页面(node.js+postman实现模拟HTTP服务器与客户端交互)
- mysql8.0详解(MySQL 8.0 的 5 个新特性,太实用了!)
- php提供哪些函数来避免sql的注入(PHP与SQL语句写一句话木马总结)
- css中width:auto和width:100%的区别
- web服务器架构(常用的WEB服务器简介)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9