MySQL8

函数索引

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

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

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

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

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

Index Skip Scan

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

  1. root@employees 15:16: select * from t1;
  2. +------+--------+
  3. | id | score |
  4. +------+--------+
  5. | 0 | 100 |
  6. | 0 | 100 |
  7. | 0 | 200 |
  8. | 0 | 300 |
  9. | 0 | 400 |
  10. | 0 | 500 |
  11. | 0 | 600 |
  12. | 0 | 700 |
  13. | 0 | 800 |
  14. | 1 | 900 |
  15. | 1 | 1000 |
  16. | 1 | 1100 |
  17. | 1 | 1200 |
  18. | 1 | 1300 |
  19. | 1 | 1400 |
  20. | 1 | 1500 |
  21. | 1 | 1600 |
  22. | 2 | 1700 |
  23. | 2 | 1800 |
  24. | 2 | 1900 |
  25. +------+--------+
  26. root@employees 15:16: select * from t1 where score>500;

Index Skip Scan会将查询转换为

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

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

倒序索引

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

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

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

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

不可见索引

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

  1. root@employees 15:47: create index idx_emp on salaries(emp_no) invisible;
  2. Query OK, 0 rows affected (4.12 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. root@employees 15:58: explain select * from salaries force index(idx_emp) where emp_no=10001;
  5. 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';