MySQL8.0的索引搞出来新特性，请面试官注意啦，之前的规则要改啦~

ゝ一世哀愁。 2022-09-05 00:16 172阅读 0赞

![0D5F937FE12312312D81F17F.jpg][]

程序员的成长之路

互联网/程序员/技术/资料共享

[关注][Link 1]

阅读本文大概需要 8.5 分钟。

作者：LuHengXing

来源：dbapub.cn

###     ###

### **\# 函数索引** ###

在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中推出了函数索引的特性，其是通过虚拟列来实现的，接着就来通过函数索引实现相同的需求，看看有什么不同

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

### **\# 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>500union select * from tab where a=2 and b>500

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

### **\# 倒序索引** ###

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

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

### **\# 不可见索引** ###

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';

<END>

**推荐阅读：**

[再见了，谷歌！][Link 2]

[真赞！IDEA中这么玩MyBatis，让编码速度飞起！][IDEA_MyBatis]

**互联网初中高级大厂面试题(9个G)**

内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper、数据结构、限流熔断降级......等技术栈！

⬇戳阅读原文领取！                                       朕已阅 ![762ee878d9f7a011b7d6e2f33b5d3b6f.gif][]

[0D5F937FE12312312D81F17F.jpg]: /images/20220829/ab4d84bd7b7f4df49c0b85e7227820ba.png
[Link 1]: https://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa24f0bacd5379ac93d74ffebac826d2401fd494f82e900e5102a8bf968f108e4ec72a4ad548&idx=3&lang=zh_CN&mid=2247487442&scene=21&sn=61e758d4bf9f15a3419fd028146469a3&token=1854383306#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa2756fecd50dfe8e98090657fd20e4dd836a280632a06ca976b41c807c1dde8d4ab7506fbef&idx=1&mid=2247510422&scene=21&sn=e7fd7f6bf8a45d4639f5c1d715954081#wechat_redirect
[IDEA_MyBatis]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa2756fecd50dfe86ff1e2b9b07a9b90bc43fc94caa1fe0f3f5d9eeb6d0f4b626ace7469149d&idx=2&mid=2247510422&scene=21&sn=49e274bf84466b4973bc142940fb1dc9#wechat_redirect
[762ee878d9f7a011b7d6e2f33b5d3b6f.gif]: /images/20220829/181bd474119e43ad842d9cd9ab48386f.png