联合索引下Order By和范围匹配SQL优化介绍-京东云开发者社区

### 1 前言

我们日常开发或者维护系统中总会遇到一些MySql的慢SQL，那么在日常解决慢SQL中，有几种慢SQL稍微难解决一些。本文主要围绕联合索引的范围查询和Order By场景的慢SQL进行分析，尽量让你知其然也知其所以然，才能避免开发过程再引入新的慢SQL，毕竟解决慢SQL成本最低的时候是在开发的过程中。在阅读本文之前先问以下2个问题，如果不能准确回答也许本文对你有帮助：

1. 联合索引idx_create_time_type ,查询条件where create_time > now() and type = 1 能全部匹配索引吗？为什么？如何优化？
2. 联合索引idx_create_time_code_type，查询条件where create_time > now() and code = 1 and type = 1 order by type能按索引排序吗？为什么？如何优化？

### 2 知识回顾

#### 2.1 MySql Innodb索引结构

详细请参考：https://joyspace.jd.com/page/9hisNz3BFOXLssGtkMg3
在分析之前先回顾下Innodb的索引结构。经常使用到的索引有三种：聚集索引（主键索引）、辅助索引、联合索引的结构。

##### 2.2.1 聚集索引

![](//img1.jcloudcs.com/developer.jdcloud.com/f7c139f6-6228-42c1-831b-4d1e1f27365920220426142240.png)

特点：

- 文件由索引页和数据页组成
- key是主键，有固定顺序，从左到右未从小到大
- 叶子节点上有所有的key和数据
- 数据页之间使用双向链表连接

##### 2.1.2 辅助索引

是在表里普通字段上创建的索引，结构与聚集索引类似，最大的区别是子节点上不包含数据是主键。

特点：

- key是非主键，有固定顺序，从左到右未从小到大
- 叶子节点上有所有的key和主键
- 辅助索引太多会占空间和影响插入修改性能
- 子节点单向链表连接

##### 2.1.3 联合索引(辅助索引的一种)

是辅助索引的一种，由2个以上的自动组成。是单列辅助索引的基础上多了几列。

下图是由字段code和time组成的联合索引，其实相当于创建了（code）单列索引、（code，time）联合索引。存储上它首先是根据第一列单调递增排序，如叶子节点上code列10、10、11，12、15、18。如果第一列相等再根据第二列单调递增排序如叶子节点上code=10的time列2018，2019。也就是说第二列不是完全有序的，第一列相等的情况下才会有序，第一个叶子节点上2018、2019、2018可以看出。

![](//img1.jcloudcs.com/developer.jdcloud.com/62ffe573-6d44-4abf-ae10-d32aa6efd69720220426143952.png)

特点：

- 包含辅助索引的全部特点
- 每个节点有2个以上的字段内容，子节点包括所有的字段内容
- 排序先按第一个字段排序，第一个字段一样再按第二个字段排序。如果字段都一样就按主键排序
- 有利于防止回表

#### 2.2 索引匹配规则

###### 1）全值匹配

是指联合索引中的字段能全部被匹配上。比如上面”联合索引“是由code和time字段组成，SQL中的where条件中要带有code=xxx 和time =xxx。

###### 2）匹配最左前缀

是指联合索引中必须从前面列开始匹配，跳过前面的列后面的列是无法使用索引的。为什么要有这个原则呢？比如”联合索引“由a,b,c 三列组成如下图，只列出了叶子节点。

![](//img1.jcloudcs.com/developer.jdcloud.com/2aeb1dcd-2bde-4e8e-b5db-6d3f482f1fc120220426144038.png)

上面联合索引已经说过，索引中第一列是单调递增，后面的列并不是递增的，只有在前面的列相等的情况下才会单调递增。这里a列是递增的，b、c列不是的。由于联合索引是这样的存储结构，所以联合索引只能从第一列开始找，使用a列能直接使用二分查找。比如跳过了第一列使用where b=xxx，因为b列是无序的无法在索引上很快定位位置，只能采用index类型方式对整个索引树全量扫描。再比如使用where a=xxx and c=xxx。这种能使用a列，c列无法使用，因为b列是无法定位的，只能在满足a列条件下的数据做全量扫描。

###### 3）匹配列前缀

也可以匹配列的内容的开头部分。比如联合索引idx_code_time, 查询条件where code like 'JD%'。这样能使用第一列code。

###### 4）匹配范围值

比如联合索引idx_code_time，查询条件where code > xxxx。这样能使用第一列code做范围匹配。

###### 5）精确匹配+范围匹配

比如联合索引idx_code_time，查询条件where code =xxx and time > xxx。这样code列能全部匹配，time列做范围匹配。下面会再讨论此场景的SQL优化。

###### 6）索引覆盖

在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。是常用的慢SQL优化手段。

### 3 场景分析

#### 3.1 范围查询和等值匹配组合使用慢SQL优化

上面索引匹配规则里说了一些规则，这里做举例解释并列一些优化方案。有表结构如下：

```
CREATE TABLE `waybill2` (
  `waybill_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `waybill_code` varchar(32) NOT NULL COMMENT '运单编号',
  `site_code` bigint(13) unsigned DEFAULT '0' COMMENT '站点编号',
  `site_name` varchar(62) DEFAULT '' COMMENT '站点名称',
  `weight` decimal(18,4) unsigned DEFAULT '0.0000' COMMENT '重量',
  `quantity` int(8) unsigned DEFAULT '0' COMMENT '数量',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `yn` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:不可用;1:可用;',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_siteCode_weight_createTime` (`site_code`,`weight`,`create_time`)
)ENGINE=InnoDB DEFAULT COMMENT='运单信息';
```

###### 1）联合索引范围匹配问题

例如waybill2表有索引idx_create_time_site_code：

```
 ALTER TABLE `waybill2` ADD INDEX 
`idx_create_time_site_code` (`create_time`, `site_code`);
```

现有查询条件where create_time > xxx and site_code = xxx，能完全走索引吗？先使用explain 查看一下：

![](//img1.jcloudcs.com/developer.jdcloud.com/ba6cb9b7-f922-4886-a1a3-cb19cba5d34620220426144350.png)

从explain看是走了索引，从使用索引长度看key_len值是6，代表只走了create_time列索引，site_code列没有走。这是因为前导列使用了范围匹配，对于条件中有以下范围查询的运算符>, <, between, like都会停止匹配后面的列，原理在”匹配最左前缀“里已经解释。

优化方案：尽可能把范围查询的列放在联合索引后面。这里可以把site_code列放在索引前面，create_time放在后面。当然这种优化不能一概而论，也得看场景。需要考虑列区分度的高低，通常是建议区分度高的列放在索引前面。另外也的具体分析场景。比如package_tag 表有近5亿数据，每天都要结转失效的数据，结转逻辑是先查询出id再根据id删除。查询SQL：select * from package_tag where create_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY) and yn = 0。因为失效数据毕竟是少数导致yn=1条件下有99.99%的数据，yn=0只有0.01%的数据5万。这种场景yn列放前面SQL就能完全匹配索引，相比只匹配了create_time列收益是非常大的。

###### 2）范围匹配转换等值匹配

例如waybill2表有索引idx_org_id_create_time：

```
ALTER TABLE `waybill2` ADD INDEX `idx_org_id_create_time` (`org_id`, `create_time`);
```

现有SQL如下：

```sql
select * from waybill2 where    create_time > '2019-02-02' limit 10
```

![](//img1.jcloudcs.com/developer.jdcloud.com/48c81dc0-ed52-4f2a-8a6b-fd0943dd6d3e20220426144447.png)

explain 没有走索引，因为条件只有联合索引的第二列不满足最左前缀。

**优化方案：**
把范围匹配转换成等值匹配。这里可以把联合索引的第一列org_id使用in来匹配就能使用上联合索引。如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/32c3bbeb-e4bc-4b19-95a9-9c7d811333c820220426144508.png)

有时候有的字段无法转换成等值匹配，如果我们无法把类似org_id字段转换成in列表怎么办呢？

比如有个登录表，要查询7天内登录过并且小于20岁的用户，查询条件where last_online_time > xxx and age < 20，如果两个字段加了联合索引（last_online_time,age），虽然能使用上索引，但是两个都是范围查询是无法按顺序使用两个列，key_len只能是一个字段的长度。也就是只能先按第一列匹配，再使用索引页数据做第二列做全量匹配。

业务场景又要求更快的查询速度。又无法直接对两个字段做in条件转换。这里没有什么好办法，只能新增一个字段把其中的字段转换成等值匹配。这里可以新增一个字段active，每次登录设置为1，超过7天未登录的设置为0这个逻辑可以由定时任务完成。这样我们可以创建索引idx_active_age ，查询条件 active =1 and age <20。如果任务查询不够精确也可以在查询条件里加上last_online_time > xxx。

#### 3.2 Order By 场景如何做索引优化

MySql有两种方式可以生成有序的结果：1.通过排序。2.按索引顺序扫描。扫描索引本身很快，索引本身有序，索引页之间也有链表连接。如果索引不能覆盖查询和排序所需的全部列，就得没扫描一条索引记录就得回表查询一次对应的行。这基本上就是随机io，因此按索引顺序读取数据的速度，可能比顺序的全表扫描慢。

最好的情况是使用同一个索引即满足排序又满足查询匹配，我们也尽可能这样设计这样的索引。另外使用索引Order By还是有些限制，要求创建索引时列的顺序要和Order By的顺序要一致，并且排序方向(asc、desc)也要一样。Order By子句和查询型限制一样，要满足最左前缀原则。

说到Order By慢SQL优化，就绕不开最影响耗时的using filesort，using filesort表示没有使用索引的排序，并不是一定是文件排序。对于filesort有两种方式，使用的算法是QuickSort。优化的主要目的也是防止filesort。这先做下解释，详细请参考连接：https://mp.weixin.qq.com/s/_Cnfy9uKvBg2IWUchbrSNg

###### 1）全字段排序

MySql会把匹配到的数据放到内存中(sort_buffer)根据指定的字段做排序，排序完成后如果有limit 根据指定的数量返回，这会占一定的内存。如果数据量太大，超过阈值sort_buffer_size（默认1M）就会使用磁盘临时文件做辅助排序。此排序方式弊端是占内存，优势是数据量小于阈值会减少一次io因为数据都已经加载。

###### 2）rowid排序

对于上一种排序有个问题，要把所有的数据都加载到内存中。rowid的实现策略是把要排序的字段和主键id加载到内存里，待排序完成再根据主键回表查询数据。max_length_for_sort_data是决定是否走此策略的参数，意思是一行数据的长度，默认是1024。此排序方式弊端是多一次回表，优势是减少内存使用。

**下面我们根据例子来详细说明下Order By SQL的优化，waybill2表有索引idx_siteCode_weight_createTime。**

###### 1）Order By最左前缀原则

![](//img1.jcloudcs.com/developer.jdcloud.com/2e0764cd-6980-428b-acb1-7ac9ac65802a20220426144628.png)

上图看到使用了using index 覆盖索引，没有回表和filsort。这是因为Order By子句字段顺序和联合索引列顺序完全一致。如果是order by site_code,create_time 是不能按索引排序的，没有满足“最左前缀”原则。

![](//img1.jcloudcs.com/developer.jdcloud.com/487eca85-a72f-4bf9-a789-d337741f428d20220426144720.png)

上图SQL Order By子句并不满足“最左前缀”原则，extra列并没有出现filesort，using where;using index的意思是使用到了覆盖索引，不用回表查询就能得到结果。这是因为索引的第一列site_code被指定了常数，排序使用了第二列，这两列组合在一起也形成了“最左前缀”原则。

###### 2）范围查询问题

![](//img1.jcloudcs.com/developer.jdcloud.com/c20829e4-54e8-48d7-97af-237bc622713a20220426144741.png)

如上图出现了filsort，前导列使用了范围查询，不满足Order By最左前缀原则。因为site_code 是范围查询(>,<,>=,<=,like)，联合索引中weight列局部有序的。
优化方案：Order By 后加上site_code 就能使用索引排序。如下图：

![](//img1.jcloudcs.com/developer.jdcloud.com/1366c8f6-1448-4fc1-9aea-8e4aa536a05920220426144756.png)

另外如果索引的中间字段使用了多个等于条件，也不能使用索引排序。如下图：

![](//img1.jcloudcs.com/developer.jdcloud.com/32ee6d2b-2871-4749-915d-fba61460848420220426144809.png)

###### 3）排序方向不一致

![](//img1.jcloudcs.com/developer.jdcloud.com/0751be00-5c8b-49f9-b978-78a70bcf918920220426144831.png)

从上图看到使用了filesort，这是因为使用两种不同的排序方向导致的。在排序的时候两个字段必须按同样的方向排序。

**优化方案：**
现实中如果需要按不同顺序排序，可以使用新增一列存储排序字段的相反数或者反转串来解决。虽然MySql索引存储的顺序是正序(MySql8.0支持指定顺序)，但是只要按同样的顺序排序都可以使用到索引排序。比如Order By site_code desc,weight desc 这也能按索引排序，和asc的区别就是在索引树上向左遍历和向右遍历的区别。如图：

![](//img1.jcloudcs.com/developer.jdcloud.com/d8f519e2-ed4c-4ae7-9aac-2c5841a0208720220426144859.png)

###### 4)Order By 深度翻页

```sql
select * from  waybill2 where site_code = xxx  limit 5000000,10
```

这类查询limit偏移量小的时候查询是非常快的，随着limit偏移量变大查询会越来越慢。像上面个SQL，MySql需要扫描出5000010条数据，然后丢弃前面的5000000条返回后面的10条，是非常浪费资源的。
优化方案：
这种优化方式可以使用书签的方式，下次查询带着上次返回的最大主键id。如：

```sql
select * from  waybill2 where site_code = xxx  and id > 5000000 
limit 10
```

书签的优化方式有限制：1.不允许跳者翻页。2.是按主键排序，并且主键是递增的。如果不满足限制可以使用延迟关联：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据，如下：

```sql
SELECT * FROM waybill2 way1
INNER JOIN
(SELECT waybill_id FROM waybill2 WHERE site_code > 39 
ORDER BY site_code ASC LIMIT 5000000 , 10)  way2 
ON way1.waybill_id = way2.waybill_id
```

### 4 最后

本文主要围绕范围匹配和Order By场景的慢SQL优化介绍一点小经验。这只是慢SQL优化的冰山一角，如果想更深入的掌握慢SQL如何优化，还得多去了解MySql搜索引擎的原理。 在写本文前也学习参考了不少文章和相关书籍，越发感觉相关知识的匮乏，最后感谢阅读，欢迎指正。

#### 参考资料

- 《高性能MySQL(第3版)》
- 《极客时间：MySQL实战45讲》
- Order By 详解：https://mp.weixin.qq.com/s/_Cnfy9uKvBg2IWUchbrSNg

------------

###### 自猿其说Tech-JDL京东物流技术与数据智能部
###### 作者：徐迷根