0%

MySQL8.0新特性:索引新特性

函数索引

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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中推出了函数索引的特性,其是通过虚拟列来实现的,接着就来通过函数索引实现相同的需求,看看有什么不同

1
2
3
4
5
6
7
8
9
10
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就是用于优化这种场景。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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会将查询转换为

1
2
3
4
5
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。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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中的倒序索引

1
2
3
4
5
6
7
8
9
10
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也无法使用。当我们在做优化时需要评估索引的影响,就可以通过隐藏索引来进行。

1
2
3
4
5
6
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优化器选项后,就可以使用隐藏索引

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