背景
索引是把雙刃劍，在提升查詢速度的同時(shí)會(huì)減慢DML的操作。畢竟，索引的維護(hù)需要一定的成本。所以，對(duì)于索引，要加上該加的，刪除無(wú)用的。前者是加法，后者是減法。但在實(shí)際工作中，大家似乎更熱衷于前者，而很少進(jìn)行后者。究其原因，在于后者，難。難的不是操作本身，而是如何確認(rèn)一個(gè)索引是無(wú)用的。
如何確認(rèn)無(wú)用索引
在不可見(jiàn)索引出現(xiàn)之前，大家可以通過(guò)sys.schema_unused_indexes來(lái)確定無(wú)用索引。在MySQL 5.6中，即使沒(méi)有sys庫(kù)，也可通過(guò)該視圖的基表來(lái)進(jìn)行查詢。
mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
View: schema_unused_indexes
Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (
`object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`STATISTICS` `s` on(((`t`.`OBJECT_SCHEMA` = convert(`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert(`s`.`TABLE_NAME` using utf8mb4)) and (convert(`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` > 'mysql') and (`t`.`INDEX_NAME` > 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.00 sec)
但這種方式也有不足，
1. 如果實(shí)例發(fā)生重啟，performance_schema中的數(shù)據(jù)就會(huì)清零。
2. 如果基于上面的查詢刪除了索引，查詢性能突然變差，怎么辦？
不可見(jiàn)索引的出現(xiàn)，可有效彌補(bǔ)上述不足。將index設(shè)置為invisible，會(huì)導(dǎo)致優(yōu)化器在選擇執(zhí)行計(jì)劃時(shí)，自動(dòng)忽略該索引，即便使用了FORCE INDEX。
當(dāng)然，這個(gè)是由optimizer_switch變量中use_invisible_indexes選項(xiàng)決定的，默認(rèn)為off。如果想看一個(gè)查詢?cè)谒饕{(diào)整前后執(zhí)行計(jì)劃的差別，可在會(huì)話級(jí)別調(diào)整use_invisible_indexes的值，如，
mysql> show create table slowtech.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
不可見(jiàn)索引的常見(jiàn)操作
create table t1(id int primary key,name varchar(10),index idx_name (name) invisible);
alter table t1 alter index idx_name visible;
alter table t1 alter index idx_name invisible;
如何查看哪些索引不可見(jiàn)
mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';
+--------------+------------+------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+--------------+------------+------------+-------------+------------+
| slowtech | t1 | idx_name | name | NO |
+--------------+------------+------------+-------------+------------+
1 row in set (0.00 sec)
注意
1. 主鍵索引不可被設(shè)置為invisible。
總結(jié)
以上所述是小編給大家介紹的MySQL 8 新特性之Invisible Indexes ，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)歡迎給我留言，小編會(huì)及時(shí)回復(fù)大家的！
您可能感興趣的文章:- MySQL8.0新特性之支持原子DDL語(yǔ)句
- MySQL8新特性之降序索引底層實(shí)現(xiàn)詳解
- MySQL 8.0 新特性之哈希連接（Hash Join）
- 淺談mysql8.0新特性的坑和解決辦法(小結(jié))
- MySQL8新特性：降序索引詳解
- MySQL8新特性：持久化全局變量的修改方法
- MySQL8新特性：自增主鍵的持久化詳解
- 解析MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL
