mysql 在8.0的时候支持了不可见索引,称为隐式索引
索引默认是可以的，控制索引的可见性可以使用Invisible,visible关键字作为create table，create index,alter table 来进行定义。

RDS 5.6 Invisible Indexes

也是最近刚刚上线的功能。新购买实例目前已经支持，老版本实例，需要进行升级。
那么接下来测试一下。

查看数据库版本：

mysql>select version();
+-----------+
| version() |
+-----------+
| 5.6.16-log    |
+-----------+
1 row in set (0.00 sec)

查看表结构以及数据量

  CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `idx_a1` (`birth_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql>select count(*) from employees;+----------+| count(*) |+----------+|   300024 |+----------+1 row in set (0.33 sec)

新增索引first_name

mysql>alter table employees add index idx_firstname(first_name);执行成功,花费 1034 ms.

测试：

a.显式索引测试：

b.将first_name 设置成隐藏索引
mysql>alter table employees alter index idx_firstname INVISIBLE;
执行成功,花费 6 ms.

c.将隐藏索引修改为显示索引
mysql>alter table employees alter index idx_firstname VISIBLE;
执行成功,花费 6 ms.

MySQL 8.0 Invisible Indexes

查看版本号：

root@my3308.sock-8.0.11>[employees]>select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

查看表结构以及使用create index alter index 创建INVISIBLE index

root@my3308.sock-8.0.11>[employees]>create index  idx_first_name on  employees(first_name) INVISIBLE;
Query OK, 0 rows affected (1.75 sec)
Records: 0  Duplicates: 0  Warnings: 0root@my3308.sock-8.0.11>[employees]>alter table employees add index idx_last_name(last_name) INVISIBLE;
Query OK, 0 rows affected (1.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用alter index ... 命令修改 索引的可见性：

  root@my3308.sock-8.0.11>[employees]>explain  select * from employees where first_name='Shahab';+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299290 |    10.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+1 row in set, 1 warning (0.00 sec)root@my3308.sock-8.0.11>[employees]>alter table employees alter index idx_first_name VISIBLE;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0root@my3308.sock-8.0.11>[employees]>explain  select * from employees where first_name='Shahab';+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+| id | select_type | table     | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | employees | NULL       | ref  | idx_first_name | idx_first_name | 44      | const |  295 |   100.00 | NULL  |+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)root@my3308.sock-8.0.11>[employees]>alter table employees alter index idx_first_name INVISIBLE;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0root@my3308.sock-8.0.11>[employees]>explain  select * from employees where first_name='Shahab';+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299290 |    10.00 | Using where |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+1 row in set, 1 warning (0.00 sec)

可以从INFORMATION_SCHEMA.STATISTICS表中获取索引的属性

root@my3308.sock-8.0.11>[employees]>SELECT INDEX_NAME, IS_VISIBLE        FROM INFORMATION_SCHEMA.STATISTICS        WHERE TABLE_SCHEMA = 'employees' and TABLE_NAME='employees';
+----------------+------------+
| INDEX_NAME     | IS_VISIBLE |
+----------------+------------+
| idx_first_name | NO         |
| idx_last_name  | NO         |
| PRIMARY        | YES        |
+----------------+------------+
3 rows in set (0.00 sec)

对于NOT NULL UNIQUE 的约束索引没有显示的主键时，是不可以直接设置成INVISIBLE 属性，

root@my3308.sock-8.0.11>[employees]>CREATE TABLE t2 (->   i INT NOT NULL,->   j INT NOT NULL,->   UNIQUE j_idx (j)-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.03 sec)root@my3308.sock-8.0.11>[employees]>ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisibleroot@my3308.sock-8.0.11>[employees]>ALTER TABLE t2 ADD PRIMARY KEY (i);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0root@my3308.sock-8.0.11>[employees]>ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0