导读:本文详细介绍 MySQL 8.0.19 三大索引新功能,隐藏索引,降序索引,函数索引,结合其他同仁的技术应用案例,进一步进行验证改编,最后总结心得,希望对大家有帮助。
MySQL 8.0 版本带来了3大索引新功能:隐藏索引,降序索引,函数索引,看字面意义,大致也都能猜到那些功能,下面测试实际了解一下。
隐藏索引
MySQL 8.0 支持隐藏索引(invisible index),也称为不可见索引。隐藏索引不会被优化器使用。主键不能设置为隐藏(包括显式设置或隐式设置)
CREATE TABLE `tb_index` (
`id` bigint NOT NULL ,
`name` varchar(255) DEFAULT NULL,
`age` tinyint DEFAULT '10',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`addr` varchar(30) DEFAULT NULL,
`sex` enum('M','F') DEFAULT NULL,
PRIMARY KEY(id),
UNIQUE uni_age (age),
INDEX idx_addr (addr) ,
INDEX idx_sex (sex) INVISIBLE,
FULLTEXT KEY `fullindex_name` (`name`)
) ;
##两种方式验证
mysql>SHOW INDEX FROM `tb_index`;
mysql>SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'tb_index'
##隐藏和可见操作
mysql>ALTER TABLE tb_index ALTER INDEX idx_addr INVISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX idx_addr VISIBLE;
除了主键,其他索引都可以。
对于唯一建:例外情况: 没有主键的情况下,第一个唯一建 不可隐藏,第二个唯一建可隐藏。 从这里可以了解到 MySQL在没有主键的情况下 是把第一个唯一建做为主键。
CREATE TABLE `tb_index2` (
`id` bigint NOT NULL ,
`name` varchar(255) NOT NULL,
`age` tinyint DEFAULT '10' NOT NULL ,
`create_time` datetime NOT NULL,
`update_time` datetime NOT NULL,
`addr` varchar(30) NOT NULL,
`sex` enum('M','F') NOT NULL,
UNIQUE uni_age (age),
UNIQUE uni_id(id),
INDEX idx_addr (addr) ,
INDEX idx_sex (sex) INVISIBLE,
FULLTEXT KEY `fullindex_name` (`name`)
) ;
##隐藏和可见操作
```sql
mysql>ALTER TABLE tb_index ALTER INDEX uni_age INVISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX uni_age VISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX uni_id INVISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX uni_id VISIBLE;
执行计划: 系统变量 optimizer_switch 的 use_invisible_indexes 值控制了优化器构建执行计划时是否使用隐藏索引。如果设置为 off (默认值),优化器将会忽略隐藏索引(与引入该属性之前的行为相同)。如果设置为 on,隐藏索引仍然不可见,但是优化器在构建执行计划时将会考虑这些索引。
mysql> SHOW VARIABLES LIKE '%optimizer_switch%';
##当name索引隐藏的时候
mysql>ALTER TABLE tb_index ALTER INDEX idx_name INVISIBLE;
#当name索引显示的时候
mysql>ALTER TABLE tb_index ALTER INDEX idx_name VISIBLE;
#optimizer_switch进行控制
mysql>ALTER TABLE tb_index ALTER INDEX idx_name INVISIBLE;
mysql>set optimizer_switch='use_invisible_indexes=on';
mysql> explain select * from tb_index where age =12 and name ='9961139@qq.com';
好的方面的optimizer_switch=‘use_invisible_indexes=on’ session级别,特定的时间可以通过参数进行使用。
总结:
不可见索引特性可以用于测试删除某个索引对于查询性能的影响,同时又不需要真正删除索引,也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时,而将其设置为不可见或可见将会非常简单快捷。
如果某个设置为隐藏的索引实际上仍然需要或者被优化器所使用,可以通过以下多种方法发现缺少该索引带来的影响:
需要思考的问题: 增删改操作,本身会进行索引的维护,隐藏索引是否有必要创建。
降序索引
MySQL8.0开始真正支持降序索引,只有InnoDB引擎支持降序索引,且必须是BTREE降序索引,MySQL8.0不在对group by操作进行隐式排序。
查看官方: MySQL支持降序索引:索引定义中的DESC不再被忽略,而是按降序存储键值。以前,可以以相反的顺序扫描索引,但是会导致性能损失。下行索引可以按前向顺序扫描,效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时,降序索引也使优化器能够使用多列索引。
注意:
语法如下,用法简单,需要考虑索引维护的成本和实际使用场景。对于MAX,MIN,DISTINCT 降序索引进行优化。
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
函数索引
MySQL版本需要是5.7及以上版本才支持建立函数索引(虚拟列方式),MySQL 8.0.13 以及更高版本支持函数索引(functional key parts),也就是将表达式的值作为索引的内容,而不是列值或列值前缀。将函数作为索引键可以用于索引那些没有在表中直接存储的内容。 ##引用上面的表tb_index
mysql>ALTER TABLE tb_index ADD INDEX idx_created (create_time);
Mysql>EXPLAIN SELECT id ,name ,age ,create_time,update_time ,month(create_time)
FROM tb_index
WHERE month(create_time) =8
mysql>ALTER TABLE tb_index ADD INDEX idx_created_fun((month(create_time)));
总结:
以上介绍内容虽然给带来了多样性的功能,但怎样更有效的使用,才是问题关键。特别是隐藏索引,多一个索引就是多出一份维护成本。
墨天轮原文链接:https://www.modb.pro/db/22619