导读
MySQL 8.0开始支持倒序索引和不可见索引,和叶师傅一起来耍耍。
MySQL版本号
Server version: 8.0.1-dmr-log MyS QL Community Server (GPL)
测试表结构
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`u1` int(10) unsigned NOT NULL DEFAULT '0',
`u2` int(10) unsigned NOT NULL DEFAULT '0',
`u3` varchar(20) NOT NULL DEFAULT '',
`u4` varchar(35) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `u1` (`u1` DESC,`u2`)
) ENGINE=InnoDB AUTO_INCREMENT=131054 DEFAULT CHARSET=utf8mb4
表统计信息:
yejr@imysql.com[yejr]> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 101970
Avg_row_length: 87
Data_length: 8929280
Max_data_length: 0
Index_length: 3686400
Data_free: 4194304
Auto_increment: 131054
Create_time: 2017-05-31 11:57:48
Update_time: 2017-06-10 12:08:09
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1、倒序索引(Descending Indexes)
case1、MySQL 8.0下利用倒序索引消除排序
# 查看执行计划
yejr@imysql.com[yejr]> desc select * from t1 where u1 <= 70 order by u1 desc, u2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: u1
key: u1
key_len: 4
ref: NULL
rows: 27742
filtered: 100.00
Extra: Using index condition
# 清除status,方便后面看SQL执行代价
yejr@imysql.com[yejr]> flush status;
# 临时修改pager,将查询结果重定向到 /dev/null,而不是发送到标准输出设备
yejr@imysql.com[test]> pager cat - > /dev/null
PAGER set to 'cat - > /dev/null'
# 执行SQL
yejr@imysql.com[yejr]> select * from t1 where u1 <= 70 order by u1 desc, u2;
16384 rows in set (0.05 sec)
# 查看SQL代价
yejr@imysql.com[yejr]> show status like 'handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 16384 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
case2、MySQL 5.7下不支持倒序索引,有额外排序
# 执行计划中有 **Using filesort**
yejr@imysql.com[yejr]> desc select * from t1 where u1 <= 70 order by u1 desc, u2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: u1
key: NULL
key_len: NULL
ref: NULL
rows: 102176
filtered: 33.29
Extra: Using where; Using filesort
yejr@imysql.com[yejr]> select * from t1 where u1 <= 70 order by u1 desc, u2;
16383 rows in set (0.05 sec)
# 可以看到有大量的 read_rnd_next
yejr@zhishutang.com[yejr]> show status like 'handler%read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 102401 |
+-----------------------+--------+
case3、MySQL 5.7下不支持倒序索引,但强制指定索引
# 还是有 **Using filesort** 啊
yejr@imysql.com[yejr]> desc select * from t1 force index(u1) where u1 <= 70 order by u1 desc, u2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: u1
key: u1
key_len: 4
ref: NULL
rows: 34014
filtered: 100.00
Extra: Using index condition; Using filesort
yejr@imysql.com[yejr]> select * from t1 force index(u1) where u1 <= 70 order by u1 desc, u2;
16383 rows in set (0.08 sec)
# status瓶颈从 read_rnd_next 变成了 read_next
yejr@imysql.com[yejr]> show status like 'handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 16384 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
关于 Handler_read_rnd、Handler_read_rnd_next 的解释 ,请看文档说明。
Handler_read_rnd
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.
Handler_read_rnd_next
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
关于倒序索引有几点说明:
• 只支持InnoDB引擎;
• 只支持B+TREE,不支持HASH、FULLTEXT、SPATIAL索引;
• change buffer无法支持有倒序的二级索引(secondary index);
• 正常正序索引支持的数据类型,倒序索引也都支持;
• 除了原生列之外,还支持虚拟列(VIRTUAL、STORED都支持);
• InnoDB全文索引的表中,FTS_DOC_ID列也不能设定为倒序;
2、不可见索引(INVISIBLE INDEX)
当我们发现冗余索引或者个别不再需要的索引时,首先想到的是直接删除。
但是直接删除索引肯定是有风险的,难免什么时候某个老业务又需要用到这个索引了,这时候invisible index就非常实用了。
这时候就可以把这个索引设置为 visible/invisible,修改索引的 visible 属性是可以in-place的,非常快。这相比删除、再次创建索引要快得多了。
一起来看看 invisible index的玩法:
# 设置 invisible
yejr@imysql.com[yejr]> alter table t1 alter index u1 INVISIBLE;
# 查看DDL
yejr@imysql.com[yejr]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
...
KEY `u1` (`u1` DESC,`u2`) /*!80000 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=131054 DEFAULT CHARSET=utf8mb4
# 查看索引状态
yejr@imysql.com[yejr]> desc select * from t1 force index(u1) where u1 <= 70 order by u1 desc, u2\G
ERROR 1176 (42000): Key 'u1' doesn't exist in table 't1'
yejr@imysql.com[yejr]> SELECT INDEX_NAME, IS_VISIBLE
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE table_name = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| PRIMARY | YES |
| u1 | NO |
| u1 | NO |
+------------+------------+
yejr@imysql.com[yejr]> show index from t1;
+-------+------------+----------+-------------+-------------+---------+
| Table | Non_unique | Key_name | Column_name | Cardinality | Visible |
+-------+------------+----------+-------------+-------------+---------+
| t1 | 0 | PRIMARY | id | 101970 | YES |
| t1 | 1 | u1 | u1 | 24 | NO |
| t1 | 1 | u1 | u2 | 24 | NO |
+-------+------------+----------+-------------+-------------+---------+
# 执行SQL时即便force index也不可用
yejr@imysql.com[yejr]>select * from t1 force index(u1) where u1 <= 70 order by u1 desc, u2\G
ERROR 1176 (42000): Key 'u1' doesn't exist in table 't1'
关于invisible index的几点补充:
• 即便是 invisible,也还要保持索引的更新;
• 主键或被选中作为聚集索引的唯一索引(这种称为隐含的聚集索引),都不能 invisible;
• 任何存储引擎都支持,不仅限于InnoDB;
最后,我要再次安利下MySQL 8.0版本,在这个版本中,有几个重大(激动人心的)新特性:
• 新增User Roles特性;
• 数据字典采用InnoDB表存储,不再使用frm文件了(MyISAM退出历史舞台进入倒计时~);
• 在线执行SET GLOBAL xx修改某个 option 后,可以实现该修改持久化(太实用了,拯救健忘症重度患者);
• InnoDB表的 AUTO_INCREMENT 值也可以实现持久化(重启后不再发生变化);
• InnoDB的REDO、UNDO LOG也支持加密;
• InnoDB表支持NOWAIT、SKIP LOCKED语法;
• 支持倒序索引、不可见索引;
• Optimizer、P_S、JSON、GIS功能持续增强;
• 可想而知的是,对Group Replicatioin也会重点增强;
其他新特性不一一列出,我粗略的看了下,这些不都是在向Oracle学习嘛,哈哈。