【MySQL】十四，MySQL 8.0的隐藏索引

在MySQL 8.0之前的版本中，索引只能直接删除。如果删除后发现引起了系统故障，又必须进行创建。当表的数据量比较大的时候，这样做的代价就会非常高。

在MySQL 8.0中，提供了隐藏索引。如果想删除某个索引，那么在实际删除之前，可以将其设置为隐藏，此时查询优化器就不会再使用此索引。确认对系统不会产生影响后，再实际删除。

演示

• 创建表时创建隐藏索引

CREATE TABLE `ts2` (
  `id` int not NULL,
  `name` int DEFAULT NULL,
  primary key(id),
  KEY `idx_name` (`name`) invisible
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• 切换成非隐藏

ALTER TABLE ts2 ALTER INDEX idx_name VISIBLE;

• 再次切换成隐藏

ALTER TABLE ts2 ALTER INDEX idx_name invisible;

• 在已经存在的表上，创建新索引

CREATE TABLE `ts3` (
  `id` int not NULL,
  `name` int DEFAULT NULL,
  primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在MySQL 8.0中创建新索引时，默认就是可见的

create index idx_name on ts3(name);

在MySQL 8.0中创建新索引时，默认就是可见的，也可以显示指定索引隐藏

create index idx_name_2 on ts3(name) invisible;

使隐藏索引对查询优化器可见

select @@optimizer_switch;

use_invisible_indexes=off说明隐藏索引默认对查询优化器不可见
设置为on可以让隐藏索引对查询优化器可见

set session optimizer_switch="use_invisible_indexes=on";