关于一次sql的优化

业务背景：

在常见的营销系统中，选品系统必不可少的。所谓的选品系统，就是通过在界面配置一些规则，进而从海量的数据中筛选出一些需要的数据。例如通过 商品上架状态=上架 筛选出所有上架的商品，通过商品库存>100 筛选出所有商品库存大于100的数据。

那么在选品系统中，一个选品id就对应一组规则，这组规则将对应一组数据。因为在业务系统中，一个选品id将对应几十万的数据。

我们拿商品为例， 选品集id=123，将有10w个商品id与之对应。因此在mysql的存储上，我们我们需要将 选品id和商品id进行联合索引，才能提高索引效率。

首先看一下表的索引：

 PRIMARY KEY (`id`),
 KEY `idx_id` (`select_id`,`version_id`,`poi_id`)

可见，针对选品+版本+poi进行了联合索引

慢sql产生

系统上线一段时间之后，发现经常有一些慢sql。

慢sql 一

select id, 
select_id as selectId,
version_id as versionId, 
poi_id as poiId, 
info 
from result_table_xxx 
where select_id = 123 
and version_id = 456 
and id > 0 
order by id limit 500

从上面的sql可以看出，业务上是想通过某一个选品id和版本id全表扫描所有的result数据。

让我们看一下这个sql执行的分析

1、type: range

2、key: primary

由此可见并没有走 idx_id这个索引，而是全表扫描了

但是如果我们将sql改成以下：

select id, 
select_id as selectId,
version_id as versionId, 
poi_id as poiId 
from result_table_xxx 
where select_id = 123 
and version_id = 456 
and id > 0 
order by id limit 500

即把info字段去掉了，再次分析却发现：

1、type: ref

2、key: idx_id

这说明本次sql走了 二级索引，由此可见索引使用的变化与是否查询info字段有关，那么这是为什么呢？

答案就是：回表。当我们查询info字段的时候，二级索引是需要回表的，因此mysql选择了直接走主键扫描。

但是业务上我们又需要使用这个info字段，也就是说回表是必须的，那么我们怎么才能让mysql走二级索引呢？如果我们order by不用id，而是使用poi呢。这样是不是就可以了？

select id, 
select_id as selectId,
version_id as versionId, 
poi_id as poiId, 
info
from result_table_xxx 
where select_id = 123 
and version_id = 456 
and poi_id > 0 
order by poi_id limit 500

继续查看上述sql的explain：

1、type: ref

2、key: idx_id

因此我们看到当where条件都是二级索引字段，那么就会先走二级索引，然后再回表。

我们对比两个sql的耗时：

sql	耗时
order by id	2.3s
order by poi_id	60ms

慢sql 二

select id,
select_id as selectId,
version_id as versionId,
info 
from 
result_table_xxx 
where version_id = 123 and select_id = 456 and city = 810100 
order by id desc limit 200,100

上述sql的业务含义是想通过选品id和版本以及对应的城市进行查询，然后根据id排序之后进行 limit截断。这很明显会造成分页的效果。

我们explain一下这个sql：

1、type : index

2、key: primary

由此可见，上述sql并没有使用idx_id索引，其根本原因还是因为回表。除此之外，还按照id进行了排序。因此，mysql选择了直接扫描主键，且using where。当然这种扫描将会产生深度分页。因此，为了解决这个问题，一般使用子查询

优化sql如下，explain一下

select * from result_table_xxx as a 
join 
(
select id from result_table_xxx 
where version_id = 123 
and select_id = 456 
and city = 110000 
order by id desc limit 0,500
)
 as b 
on a.id = b.id

上述代码，我们先从二级索引中获取到id，这个查询type=ref，key=idx_id 可见走了索引，然后再外层再去 join一次，这层的type=eq_ref, key=primary。

对比两个sql的耗时

sql	耗时
直接查询	2s
连接查询	37ms