MySQL8.0新特性-invisible indexes

田帅萌

发布于 2019-06-15 17:26:00

6030

发布于 2019-06-15 17:26:00

文章被收录于专栏：「3306 Pai」社区「3306 Pai」社区

作者李春·沃趣科技首席架构师

出品沃趣科技

作者简介：

曾就职于阿里巴巴，全程参与阿里数据架构从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_1\G
*************************** 1. row ***************************
   Table: employees_1
Create 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_ci
1 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_1\G *************************** 1. row ***************************
   Table: employees_1
Create 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_ci
1 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_1\G
*************************** 1. row ***************************
   Table: employees_1
Create 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_ci
1 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_nam
e, 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 departments\G
*************************** 1. row ***************************
   Table: departments
Create 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_bin
1 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