mysql隐藏的配置文件 mysql隐藏索引

导读：本文详细介绍 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`)) ;##隐藏和可见操作```sqlmysql>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 ='[e-mail]';

好的方面的optimizer_switch=‘use_invisible_indexes=on’ session级别，特定的时间可以通过参数进行使用。

总结：

不可见索引特性可以用于测试删除某个索引对于查询性能的影响，同时又不需要真正删除索引，也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时，而将其设置为不可见或可见将会非常简单快捷。

如果某个设置为隐藏的索引实际上仍然需要或者被优化器所使用，可以通过以下多种方法发现缺少该索引带来的影响：

• 索引提示中使用了该索引的查询将会产生错误；
• 性能模式（Performance Schema）中的数据显示受影响查询的负载升高；
• EXPLAIN 语句显示了不同的查询执行计划；
• 慢查询日志中出现了新的查询语句。

需要思考的问题：
增删改操作，本身会进行索引的维护，隐藏索引是否有必要创建。

降序索引

---

MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序索引，且必须是BTREE降序索引，MySQL8.0不在对group by操作进行隐式排序。

查看官方：
MySQL支持降序索引:索引定义中的DESC不再被忽略，而是按降序存储键值。以前，可以以相反的顺序扫描索引，但是会导致性能损失。下行索引可以按前向顺序扫描，效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时，降序索引也使优化器能够使用多列索引。

注意：

• Ddescending索引只支持InnoDB存储引擎
• 具有降序关键部分的索引不用于对调用聚合函数但没有GROUP BY子句的查询进行MIN()/MAX()优化
• 降序索引:BTREE支持，但不支持哈希索引,全文索引或空间索引
• DISTINCT可以使用包含匹配列(包括降序关键部分)的任何索引。
• 对于所有可用升序索引的数据类型，都支持降序索引。

语法如下，用法简单，需要考虑索引维护的成本和实际使用场景。对于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_indexWHERE  month(create_time) =8

mysql>ALTER TABLE tb_index  ADD INDEX idx_created_fun((month(create_time)));

• 函数索引支持UNIQUE选项。但是，主键不能包含函数列。主键只能使用存储的计算列，但是函数索引使用虚拟计算列实现，而不是存储计算列。
• 空间SPATIAL 索引和 全文FULLTEXT 索引不支持函数索引。
• 如果某个表中没有主键，InnoDB 存储引擎自动将第一个 UNIQUE NOT NULL 索引提升为主键。但是对于包含函数列的 UNIQUE NOT NULL 索引不会进行提升。
• 如果要删除的字段上有函数索引，必须先删除函数索引，才能删除字段。

总结：

---

以上介绍内容虽然给带来了多样性的功能，但怎样更有效的使用，才是问题关键。特别是隐藏索引，多一个索引就是多出一份维护成本。