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=utf8

  mysql>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: 0

 root@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: 0

  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       | 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: 0

  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)

可以从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 invisible

root@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: 0

root@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