隐藏索引和降序索引

MySQL8.0中的隐藏索引

隐藏索引的概述

隐藏索引即为将索引隐藏，在进行查询、排序等操作的时候系统会直接忽略该索引，隐藏索引可以理解为索引的软删除，也就是索引设置为失效，但是实际上并没有删除对应的索引，避免了索引误删除之后，系统重构索引需要消耗大量的时间和系统资源，在需要的时候将隐藏索引设置为可见，索引又恢复原来的功能

为什么要有隐藏索引？

简而言之：避免了索引误删除之后，系统重构索引需要消耗大量的时间和系统资源（可通过下面两个具体情境深入体会），在MySQL5.7只能重建索引，在MySQL8.0就可以使用隐藏索引来解决

情景分析1：
当我们删除了某个索引，但是发现删除后严重影响性能或者发生了一些错误，那么我们就需要重建索引，但是在数据量比较大的情况下，重建索引是非常耗时的，所以如果能够在不删除索引的情况下使得索引暂时失效，那么在发现错误的时候就不需要重建索引而是将索引恢复有效即可。
情景分析2：
当我们不知道自己建立的索引是否有效的时候，那么通过删除索引，对比前后的操作是否有差别就可以判断索引是否失效。如果前后没有差别，那么删除就是正确的（因为维护索引是需要耗费资源的），如果发现删除索引后严重影响查询性能，那么就需要将删除的索引进行重建。但是删除索引、重建索引是需要耗费系统资源的，所以如果能通过让索引暂时失效的方式来进行实现，那么就可以避免上述问题。

隐藏索引的使用场景

• 当需要删除索引的时候，首先可以设置为隐藏索引，这时候索引就失效了，然后就可以进行测试，如果设置为隐藏索引前后没有任何影响，那么就可以使用硬删除，否则就可以设置为非隐藏。
• 如果想要验证某个索引删除之后的查询性能的影响，就可以暂时将该索引设置为隐藏。

隐藏索引的注意事项

• 主键不能设置为隐藏索引
• 索引设置为隐藏的时候索引就不可用了(失效)，但是其内容依旧是会进行跟新的
• 隐藏索引对于优化器是不可见的，但是通过参数的设置也可以对优化器可见

1. 由于InnoDB存储引擎是以主键构建B+树进行数据的存储的，所以不能将主键设置为失效，否则存储引擎就会重新选择一个字段作为主键，对整个表重新构建B+树进行数据的存储
2. 索引虽然设置为隐藏，但是其内容依旧是更新的，只是在进行查询等操作的时候是不会使用索引的，所以如果一个索引要被长期的隐藏，那么最好将其删除，因为会影响数据的更新、删除等操作的效率

隐藏的使用代码演示

• 建表的时候设置隐藏索引

CREATE TABLE xiaoyuanhao(
	id INT,
	username INT,
	PASSWORD INT,
	INDEX password_index(PASSWORD) invisible
)
-- 默认情况下就是visible，如果使用的是invisible那么就是不可见

   
   

   
   
1
2
3
4
5
6
7

• 将表中的索引设置为隐藏

ALTER TABLE xiaoyuanhao ALTER INDEX password_index invisible;

   
   

   
   
1

---

MySQL8.0中的降序索引

降序索引的概述

在MySQL4.0中虽然在语法上就支持创建降序索引，但是InnoDB在底层的实现中依旧依旧是通过升序的方式进行索引的构架（无论是升序索引还是降序索引都是按照升序进行索引构建），在具体的实现上通过反向扫描的方式进行实现，但是这样就会大大降低数据库效率，在MySQL8.0中支持了降序索引的构建，即底层的索引结构就是按照索引列的降序进行排列的

降序索引的使用及测试

• 创建升序索引，查询的时候降序查询

CREATE TABLE xiaoyuanhao (id INT,PASSWORD INT,username VARCHAR(20));
-- 创建一个表
CREATE INDEX username_index ON xiaoyuanhao(username);
-- 按照升序在username创建索引
EXPLAIN SELECT username FROM xiaoyuanhao ORDER BY username DESC;
-- 按照降序进行查询

   
   

   
   
1
2
3
4
5
6

可以看出这里进行了反向扫描，效率低下

• 创建降序索引，同时降序查询

DROP INDEX username_index ON xiaoyuanhao;
-- 删除之前的升序所用
CREATE INDEX username_index ON xiaoyuanhao(username DESC);
-- 创建降序索引
EXPLAIN SELECT username FROM xiaoyuanhao ORDER BY username DESC;
-- 按照降序查询

   
   

   
   
1
2
3
4
5
6

这里可以看出是直接使用索引，并没有进行反向扫描，可以看出Mysql8.0的底层是支持降序索引的创建的，提升了降序查询的效率