函数索引
在 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 会将查询转换为
plaintext
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 中的倒序索引
plaintext
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 优化器选项后，就可以使用隐藏索引
plaintext
root@employees 15:58: set @@optimizer\_switch='use\_invisible\_indexes=on'; 作者: LuHengXing
来源: http://dbapub.cn/2020/08/17/MySQL8.0%E7%B4%A2%E5%BC%95%E6%96%B0%E7%89%B9%E6%80%A7/
