MySQL 8.0 索引新特性

MySQL8

函数索引

在MySQL8.0之前对条件字段做函数操作、或者做运算都将不会使用字段上的索引，例如下面的例子

root@employees 14:09:  show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      299232 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | inx_date |            1 | birth_date  | A         |        4739 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
root@employees 14:10:  explain select * from employees where month(birth_date)=9;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299232 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

可以看到SQL执行计划的type为ALL，并没有利用索引。在MySQL8.0中推出了函数索引的特性，其是通过虚拟列来实现的，接着就来通过函数索引实现相同的需求，看看有什么不同

root@employees 14:35:  alter table employees add index idx_birth_date((month(birth_date)));
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@employees 14:36:  explain select * from employees where month(birth_date)=9;
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys  | key            | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_birth_date | idx_birth_date | 5       | const | 47370 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+

需要注意的是函数索引写法是固定时，在使用函数索引时必须依照定义时的语法进行使用，否则优化器无法识别

Index Skip Scan

在存在一个联合索引的情况下，如果查询条件中不包含联合索引的最左字段，则无法使用联合索引。例如存在Index(a,b)，现在执行select * from tab where b=1，这时需要针对b字段建立一个单独的索引。MySQL8.0中引入了Index Skip Scan就是用于优化这种场景。

root@employees 15:16:  select * from t1;
+------+--------+
| id   | score  |
+------+--------+
|    0 |   100  |
|    0 |   100  |
|    0 |   200  |
|    0 |   300  |
|    0 |   400  |
|    0 |   500  |
|    0 |   600  |
|    0 |   700  |
|    0 |   800  |
|    1 |   900  |
|    1 |   1000 |
|    1 |   1100 |
|    1 |   1200 |
|    1 |   1300 |
|    1 |   1400 |
|    1 |   1500 |
|    1 |   1600 |
|    2 |   1700 |
|    2 |   1800 |
|    2 |   1900 |
+------+--------+
root@employees 15:16:  select * from t1 where score>500;

Index Skip Scan会将查询转换为

seect * from tab where a=0 and b>500 
union 
select * from tab where a=1 and b>500
union 
select * from tab where a=2 and b>500

可以看出实际上它是将id字段做了distinct然后作为条件再union拼接起来，这种优化只适用于左边字段唯一性较差的情况，例如性别，状态之类的值，否则优化器则不会使用Index Skip Scan来进行优化

倒序索引

MySQL8.0之前创建索引只支持ASC正向索引，对于一些desc排序的查询并不是很友好，执行计划通常会出现using filesort。

root@employees 15:40:  explain select salary from salaries group by salary order by salary desc;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+-----------------------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                     |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | salaries | NULL       | range | idx_salary    | idx_salary | 4       | NULL | 81274 |   100.00 | Using index for group-by; Using temporary; Using filesort |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)
root@employees 15:41:  explain select salary from salaries group by salary  order by salary asc;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | salaries | NULL       | range | idx_salary    | idx_salary | 4       | NULL | 81274 |   100.00 | Using index for group-by |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+

可以看到倒序排序时，执行计划extra中相对正序多了Using temporary; Using filesort，现在看看8.0中的倒序索引

root@employees 15:43:  create index idx_salary on salaries(salary desc);
Query OK, 0 rows affected (7.39 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@employees 15:47:  explain select salary from salaries group by salary order by salary desc;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | salaries | NULL       | range | idx_salary    | idx_salary | 4       | NULL | 72950 |   100.00 | Using index for group-by |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+-------+----------+--------------------------+

不可见索引

MySQL8.0中引入了隐藏索引，即该索引对优化器不可见，优化器也不会选择该索引，即使使用force index也无法使用。当在做优化时需要评估索引的影响，就可以通过隐藏索引来进行。

root@employees 15:47:  create index idx_emp on salaries(emp_no) invisible;
Query OK, 0 rows affected (4.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@employees 15:58:  explain select * from salaries force index(idx_emp) where emp_no=10001;
ERROR 1176 (42000): Key 'idx_emp' doesn't exist in table 'salaries'

开启use_invisible_indexes优化器选项后，就可以使用隐藏索引

root@employees 15:58:  set @@optimizer_switch='use_invisible_indexes=on';