慢查询的场景

一个业务上线一段时候后出现了慢查询的情况，排除掉业务相关的数据兼容相关的处理，简化一下，慢查询的语句基本分为两种：

# case one
select * from a join b on a.col_n = b.id
join c on c.id = b.col_m
where (c.col_j like '%search%' or a.col_k like '%search%') and a.col_const = const_n
order by a.col_x desc limit 50
or
order by a.col_y desc, c.col_x desc limit 50;

# case two 
select * from a join b on a.col_n = b.id
join c on c.id = b.col_m
where c.id in list_c and a.col_count = const_n

order by a.col_x desc limit 50;
or
order by a.col_y desc, c.col_x desc limit 50;

目前在a表上有的索引: idx_const_n_col_x, idx_const_n_col_y, idx_col_n。其中 a, b表都是百万级别，c表是万的级别。

第一版方案

首先是最明显的 a_col_k like %search% , mysql的索引是按最左匹配原则进行的， 所以如果处理 search% 效率是没有问题，但是 %search 就不行的。这种情况需要建立倒序索引来解决问题，对于这种全文搜索的问题，可以使用ElasticSearch来解决，加上已有方案也已经接入了ES, 就采用了这个方案。 简单来说就是维护一个 a.col_k , a.id对应的表，先通过ES的全文收索功能，在es_col_k_id_map表中快速查找到 id列表， 将原sql转换为:

select * from a join b on a.col_n = b.id
join c on c.id = b.col_m
where (c.col_j like '%search%' or a.id in a_id_lists) and a.col_const = const_n
order by a.col_x desc , a.col_y desc limit 50;

c表只有几万行，且以后随着业务的增长也基本维持在这个量级，c_col_j like %search%不用优化也可以，不过由于业务经常使用类似的逻辑，这里已经在redis中实现了简化版的倒序查询的方案，就又复用了已有代码进一步优化了一下。

对于 case two 采用的解决方案是先把 关于join c 表的操作给去掉, 原语句就变成了：

list_c = some_sql
select * from a join b on a.col_n = b.id
where (b.col_m in list_c or a.id in a_id_lists) and a.col_const = const_n
sorted by a.col_x desc , a.col_y desc limit 50;

然后给b的col_m加上了索引。简单测试后，第一版方案就上线了。

第二版方案

上线后，发现慢查询的确减少了很多，但是并没有完全消失。认真查看了一下ELK慢查询日志，发现还有下面的两种种慢查询:

# case one
select * from a join b on a.col_n = b.id
where a.col_const = const_n and b.col_m in list_b
order by a.col_y desc, a.col_x desc limit 50;

# case two
select * from a join b on a.col_n = b.id
where a.col_const = const_n and b.col_m in list_b order by a.col_x desc limit 50;

对于第一种比较好解决，加上索引idx_col_const_col_y_const_x即可以解决问题(同时可以删除之前的索引idx_col_const_col_y)。 对于第二种 explain sql以后会发现， 第二种情况有时候 MySQL的优化器会先走 idx_col_m, 因为这样扫描出来的行数比走idx_const_col_x索引的行数少，但是这样的话，由于idx_col_m并没有 col_x字段，所以后面还需要fliter sorting, 这样就会变得非常慢。所以使用 Focus use idx_col_const_col_y_const_x直接强迫走 idx_col_const_col_y_const_x 即可。这里也有一个小的细节：两种order by都会出现，这里要根据order by的方式选择idx_const_n_x 或 idx_col_const_col_y_const_x索引。