作者 李春·沃趣科技首席架構師

出品 沃趣科技

作者簡介：

曾就職于阿里巴巴，全程參與阿里數據架構從Oracle遷移到MySQL過程，參與分布式中間件Cobar設計。

| 導語

MySQL 8.0版本中新增了invisible indexes(不可見索引)特性，索引被invisible后，MySQL優化器就會忽略該索引(無此特性時需要刪除索引才能達到相同效果)，由此能夠驗證在該索引被刪除的情況下對性能的影響程度。

| 怎么創建一個invisible indexes或者修改索引為invisible

在新建表，新建索引時，可以顯式聲明某索引為invisible。

示例-新建表&新建索引時聲明invisible indexes

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:(none) 14:59:15]>use employees;Database changed[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:05:22]> CREATE TABLE `employees_1` (-> `emp_no` int(11) NOT NULL,-> `birth_date` date NOT NULL,-> `first_name` varchar(14) COLLATE utf8_bin NOT NULL,-> `last_name` varchar(16) COLLATE utf8_bin NOT NULL,-> `gender` enum('M','F') COLLATE utf8_bin NOT NULL,-> `hire_date` date NOT NULL,-> PRIMARY KEY (`emp_no`),-> KEY `idx_hire_date` (`hire_date`) INVISIBLE-> ) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;Query OK, 0 rows affected (1.34 sec)[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:05:25]>ALTER TABLE employees_1 ADD INDEX idx_birth_date (birth_date) INVISIBLE;Query OK, 0 rows affected (0.71 sec)Records: 0 Duplicates: 0 Warnings: 0[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:06:39]>show create table employees_1G*************************** 1. row *************************** Table: employees_1Create Table: CREATE TABLE `employees_1` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `idx_hire_date` (`hire_date`) /*!80000 INVISIBLE */,KEY `idx_birth_date` (`birth_date`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

對于已有索引，可以修改它為invisible的。對于在線系統，修改索引為invisible能夠驗證在該索引被刪除的情況下對性能的影響程度。

修改某個索引為invisible

示例-修改索引為invisble/visible

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:08:41]>ALTER TABLE employees_1 ALTER INDEX idx_birth_date VISIBLE;Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0 Warnings: 0[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:10:30]>ALTER TABLE employees_1 ALTER INDEX idx_birthd_hired INVISIBLE;Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:10:36]>show create table employees_1G *************************** 1. row *************************** Table: employees_1Create Table: CREATE TABLE `employees_1` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `idx_hire_date` (`hire_date`) /*!80000 INVISIBLE */,KEY `idx_birth_date` (`birth_date`),KEY `idx_birthd_hired` (`birth_date`,`hire_date`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

| 驗證invisible indexes效果

我們可以通過執行計劃來簡單驗證索引invisible 后的效果

示例-visible indexes：

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:25:59]>explain select emp_no,first_name,last_name,birth_date, hire_date from employees_1 where birth_date='1953-09-02' order by hire_date limit 5;+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+| 1 | SIMPLE | employees_1 | NULL | ref | idx_birth_date | idx_birth_date | 3 | const | 63 | 100.00 | Using index condition; Using filesort |+----+-------------+-------------+------------+------+----------------+----------------+---------+-------+------+----------+---------------------------------------+1 row in set, 1 warning (0.00 sec)Note (Code 1003): /* select#1 */ select `employees`.`employees_1`.`emp_no` AS `emp_no`,`employees`.`employees_1`.`first_name` AS `first_name`,`employees`.`employees_1`.`last_name` AS `last_name`,`employees`.`employees_1`.`birth_date` AS `birth_date`,`employees`.`employees_1`.`hire_date` AS `hire_date` from `employees`.`employees_1` where (`employees`.`employees_1`.`birth_date` = '1953-09-02') order by `employees`.`employees_1`.`hire_date` limit 5

修改為invisible indexes后的效果

示例-修改索引為invisible后explain效果：

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:27:09]>ALTER TABLE employees_1 ALTER INDEX idx_birth_date INVISIBLE;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:27:14]>show create table employees_1G*************************** 1. row *************************** Table: employees_1Create Table: CREATE TABLE `employees_1` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`last_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`gender` enum('M','F') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `idx_hire_date` (`hire_date`) /*!80000 INVISIBLE */,KEY `idx_birth_date` (`birth_date`) /*!80000 INVISIBLE */,KEY `idx_birthd_hired` (`birth_date`,`hire_date`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:27:19]>explain select emp_no,first_name,last_name,birth_date, hire_date from employees_1 where birth_date='1953-09-02' order by hire_date limit 5;+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+| 1 | SIMPLE | employees_1 | NULL | ALL | NULL | NULL | NULL | NULL | 283562 | 0.02 | Using where; Using filesort |+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)Note (Code 1003): /* select#1 */ select `employees`.`employees_1`.`emp_no` AS `emp_no`,`employees`.`employees_1`.`first_name` AS `first_name`,`employees`.`employees_1`.`last_name` AS `last_name`,`employees`.`employees_1`.`birth_date` AS `birth_date`,`employees`.`employees_1`.`hire_date` AS `hire_date` from `employees`.`employees_1` where (`employees`.`employees_1`.`birth_date` = '1953-09-02') order by `employees`.`employees_1`.`hire_date` limit 5

可以看到，索引被invisible以后， MySQL優化器就看不到這個索引，從而走上了主鍵索引掃描。

| invisible indexes原理和讓invisible索引優化器可見的黑科技

其實invisible indexes和普通的可見索引是一樣維護的，唯一性約束要檢查還是得檢查。

示例-invisible indexes唯一約束仍然有效：

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:29:18]>ALTER TABLE employees_1 ADD UNIQUE KEY `idx_fn_ln` (first_name, last_name) INVISIBLE;ERROR 1062 (23000): Duplicate entry 'Erez-Ritzmann' for key 'idx_fn_ln'[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:33:04]>ALTER TABLE departments ALTER INDEX dept_name INVISIBLE;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:33:54]>show create table departmentsG*************************** 1. row *************************** Table: departmentsCreate Table: CREATE TABLE `departments` (`dept_no` char(4) COLLATE utf8_bin NOT NULL,`dept_name` varchar(40) COLLATE utf8_bin NOT NULL,PRIMARY KEY (`dept_no`),UNIQUE KEY `dept_name` (`dept_name`) /*!80000 INVISIBLE */) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin1 row in set (0.00 sec)[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:34:44]>insert into departments (dept_no,dept_name) values ('d010', 'Sales');ERROR 1062 (23000): Duplicate entry 'Sales' for key 'dept_name'

但是invisible indexes讓MySQL的優化器忽略這個索引，僅此而已。

是否能讓優化器不忽略invisible indexes列，MySQL也提供了這個選項 (雖然比較奇怪)- 在 optimizer_switch中設置use_invisible_indexes選項就可以讓優化器使用invisible indexes。

繼續剛才的例子：

示例-optimizer_switch中設置use_invisible_indexes的explain效果

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:19]>show variables like '%optimizer_switch%'G *************************** 1. row ***************************Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:38]>set optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on';Query OK, 0 rows affected (0.00 sec)[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:49]>show variables like '%optimizer_switch%'G *************************** 1. row ***************************Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:38:51]>explain select emp_no,first_name,last_name,birth_date, hire_date from employees_1 where birth_date='1953-09-02' order by hire_date limit 5; +----+-------------+-------------+------------+------+---------------------------------+------------------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------+------------+------+---------------------------------+------------------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | employees_1 | NULL | ref | idx_birth_date,idx_birthd_hired | idx_birthd_hired | 3 | const | 63 | 100.00 | Using index condition |+----+-------------+-------------+------------+------+---------------------------------+------------------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)Note (Code 1003): /* select#1 */ select `employees`.`employees_1`.`emp_no` AS `emp_no`,`employees`.`employees_1`.`first_name` AS `first_name`,`employees`.`employees_1`.`last_name` AS `last_name`,`employees`.`employees_1`.`birth_date` AS `birth_date`,`employees`.`employees_1`.`hire_date` AS `hire_date` from `employees`.`employees_1` where (`employees`.`employees_1`.`birth_date` = '1953-09-02') order by `employees`.`employees_1`.`hire_date` limit 5

可以看到，設置了use_invisible_indexes=off以后，INVISIBLE的索引優化器都可以用到了。

| 主鍵索引無法invisible

invisible indexes對主鍵索引無效。對InnoDB來說，數據都存放在主鍵索引中，主鍵索引都看不到，優化器沒法做全表掃描了。

有一種特殊的場景：隱性主鍵。表沒有定義主鍵的情況下，會把第一個非空唯一索引當成主鍵(UNIQUE & NOT NULL)，此時這個索引作為隱性主鍵也無法設置為invisible。

示例-隱性主鍵無法修改為invisible：

[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:46:46]>CREATE TABLE `departments_1` (-> `dept_no` char(4) COLLATE utf8_bin NOT NULL,-> `dept_name` varchar(40) COLLATE utf8_bin NOT NULL,-> UNIQUE KEY `dept_no` (`dept_no`),-> UNIQUE KEY `dept_name` (`dept_name`)-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.12 sec)[root@localhost.localdomain:/usr/local/mysql 8.0.11_Instance1 root@localhost:employees 15:46:50]>ALTER TABLE departments_1 ALTER INDEX dept_no INVISIBLE;ERROR 3522 (HY000): A primary key index cannot be invisible

思考：

• 是否可以設置某些列為invisiable？
• 修改列為invisiable/visiable會鎖表嗎？

參考：

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

https://mysqlserverteam.com/mysql-8-0-invisible-indexes/

關于「3306π」技術大會活動預告

會 議 報 名

Hello，伙伴們長按二維碼即可報名哦！

“閱讀原文”或戳鏈接：https://www.bagevent.com/event/1460675均可輕松報名！

總結

以上是生活随笔為你收集整理的f分布表完整图a=0.05_MySQL8.0新特性-invisible indexes的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。