MySQL8.0新特性~索引新增特性

隐藏索引

使用 invisible 创建表 或者 进行 表变更中设置索引为 隐藏索引。这时,索引隐藏不可见, 但是数据库会在后台继续维护隐藏。
这个隐藏索引, 在查询时,甚至 force index 都不会使用。
这个隐藏索引的主要作用是: 一些索引不确定是否有需要, 如果进行删除,后续又需要恢复这个索引, 假如数据量很大,那么就会耗费大量时间。 这时就可以将索引设置为隐藏索引, 等需要时 直接恢复即可。

隐藏索引使用案例

创建一张表（students），在name字段上创建一个索引，将name字段的索引设置为不可见INVISIBLE的时候，可以看到在执行计划中，原本应该走name的索引，并没有按照预想索引扫描方式查询。而通过alter 修改name字段索引为visible后，可以走name索引查询。

 CREATE TABLE `students` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |


mysql> alter table students alter index idx_name invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show create table students; 
| students | CREATE TABLE `students` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc select * from students where name ='1';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)

mysql> alter table students alter index idx_name visible;  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from students where name ='1';
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ref  | idx_name      | idx_name | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

降序索引

降序索引使用案例

索引默认是升序排列

mysql> select * from students;
+----+------+------+---------+-------+
| id | name | age  | class   | score |
+----+------+------+---------+-------+
|  1 | aa   |   13 | english |    99 |
|  2 | bb   |   12 | math    |    88 |
|  3 | cc   |   12 | chinese |    91 |
|  4 | aa   |   13 | chinese |    78 |
|  5 | aa   |   13 | math    |    74 |
|  6 | bb   |   12 | english |    67 |
|  7 | bb   |   12 | chinese |    97 |
|  8 | cc   |   12 | math    |    56 |
|  9 | cc   |   12 | english |    89 |
+----+------+------+---------+-------+
9 rows in set (0.00 sec)

| students | CREATE TABLE `students` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `class` varchar(11) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_cs` (`class`,`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

创建一个联合索引，其中class和socre默认升序排列

mysql> create index idx_cs on students(class, score);

mysql> desc select class,score from students order by class,score;  
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | index | NULL          | idx_cs | 52      | NULL |    9 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

执行这条SQL，因为class和score在索引中是有序排列的，而且默认升序，可以看到用到了这个索引，不需要回表。

如果执行这条，即按照class和score的降序检索，同样用到了索引。

mysql> select class,score from students order by class desc ,score desc ;
+---------+-------+
| class   | score |
+---------+-------+
| math    |    88 |
| math    |    74 |
| math    |    56 |
| english |    99 |
| english |    89 |
| english |    67 |
| chinese |    97 |
| chinese |    91 |
| chinese |    78 |
+---------+-------+
9 rows in set (0.00 sec)

mysql> desc select class,score from students order by class desc ,score desc ;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | students | NULL       | index | NULL          | idx_cs | 52      | NULL |    9 |   100.00 | Backward index scan; Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

对于普通的升序索引，从根节点到叶子节点是升序排列的，所有索引节点从左到右也是升序排列的，但是如果要得到升序索引排在后面的数据(例如主键id字段，默认升序，select * from test where id < 1000 order by id desc)，就需要对这个索引逆向查找，就使用了backward index scan，这就是基于双向链表的机制。

再进一步，如果执行这条SQL，class按照升序，score按照降序，此时除了用到索引外，还用到了filesort，原因就是复合索引中class和score都是升序排列，但是执行的SQL中class按照升序，score按照降序，两者相悖，因此，会多了对score做降序排列的操作。

mysql> desc select class,score from students order by class ,score desc ;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | students | NULL       | index | NULL          | idx_cs | 52      | NULL |    9 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

重点来了，如果创建一个class按照升序，score按照降序的复合索引，

mysql> create index idx_cs_2 on students(class, score desc);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select class,score from students order by class ,score desc ;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | index | NULL          | idx_cs_2 | 52      | NULL |    9 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此时执行这条SQL，是可以用到这个索引的，因为从根节点到叶子节点，索引节点从左到右，class都是按照升序排列，score 都是按照降序排列，和检索条件的顺序是完全一致的，
由于支持了索引中的降序存储，就可以完全利用索引的有序性，避免额外的排序，达到快速检索数据，提升检索效率的目的。

但是需要注意的是，由于引入了降序索引，MySQL 8.0之前支持的group by子句默认隐式排序的功能，不再支持了，因此对结果集有排序的需求，就需要显式执行order by。

函数索引

8.0函数作用在索引字段上，索引有效

MySQL 5.7：函数作用在索引字段上，会使索引失效

MySQL 8.0：引入函数索引，支持在索引中使用函数。函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引

函数索引使用案例

mysql>  create index func_idx on students((UPPER(name))); 
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from students\G

*************************** 7. row ***************************
        Table: students
   Non_unique: 1
     Key_name: func_idx
 Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: upper(`name`)
7 rows in set (0.06 sec)

mysql> desc select * from students where upper(name)='AA'; 
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ref  | func_idx      | func_idx | 403     | const |    3 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到在name上使用函数，依然走了name字段的索引扫描