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)
AI 代码解读
查看表结构以及数据量
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)
AI 代码解读
新增索引first_name
mysql>alter table employees add index idx_firstname(first_name);执行成功,花费 1034 ms.
AI 代码解读
测试：
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)
AI 代码解读
查看表结构以及使用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
AI 代码解读
使用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)
AI 代码解读
可以从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)
AI 代码解读
对于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
AI 代码解读
