# 函数索引公众号关注 “程序员解析”
设为“星标”，重磅干货，第一时间送达
在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: 0root@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>500unionselect * from tab where a=1 and b>500unionselect * 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: 0root@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: 0root@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';更多精彩内容，关注我们
▼▼
