老斯基带你解锁MySQL 8.0索引新姿势

xiaoxiao2021-02-28  10

导读

MySQL 8.0开始支持倒序索引和不可见索引,和叶师傅一起来耍耍。

提醒:下列内容,建议采用横屏模式阅读。

MySQL版本号

Server version:     8.0.1-dmr-log MySQL Community Server (GPL)

测试表DDL

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下利用倒序索引消除排序

case2、MySQL 5.7下不支持倒序索引,有额外排序

case3、MySQL 5.7下不支持倒序索引,但强制指定索引

关于 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 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学习嘛,哈哈。

延伸阅读1、Descending Indexes https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html2、invisible index https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html3、【重磅】MySQL 8.0 pre release看点

知识无界限,不再加原创

喜欢就转走,铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com

转载请注明原文地址: https://www.6miu.com/read-1250195.html

最新回复(0)