一次MySQL查询优化实战

在公司短信平台架构升级中，我对数据库表结构做了相应的优化调整，过程中遇到一些莫名其妙的问题：几乎完全相同的SQL语句，执行计划也完全相同，查询却有时快，有时慢；SQL语句中带LIMIT慢，不带反而快；ORDER BY id慢，ORDER BY insert_time快，在这些看似不可思议的现象背后，究竟暗藏着什么玄机？本文为你一一道来。

 

短信平台原先一直使用分区表存储短信相关信息，分区键使用短信发送时间字段insert_time。每个月分一个区，一直分到2017年。假如2018年还在使用该分区表来存储短信内容，数据库就会报这个错误：ERROR 1526 (HY000): Table has no partition for value 1517500800。为了避免一年多后出现灾难性的错误，作为系统的负责人必须做到未雨绸缪，防患于未然。

 

除此之外，分区表在业界的应用也不多，主要是因为坑比较多，容易出现全局锁表，内存消耗大等问题。分区表的好处是比较适合用来存储比较大量的数据，物理上分开存储，查询起来相对比较快，而且SQL操作跟普通单表一模一样，一般被用来存储日志之类的数据。MySQL存储比较海量的数据的话，比较常用的方式是使用分实例分库分表。

 

在这次的短信平台架构升级中，经过再三考虑，决定不再使用分区表存储短信内容，而是使用普通的单表存储【只是去除原表的分区部分】。不幸的是，这样的改动遇到了好些坑。

 

在测试过程中，我发现前端查询某个时间范围的短信发送列表巨慢【10秒多】。相应的SQL语句大致如下：

SELECT * FROM boyaa_sms a LEFT JOIN appid_key b ON a.app_id=b.appid WHERE app_id IN (100009,100010,100018,100021,100023,100025) AND dept1_id=328 AND dept2_id=194 AND project='报警' AND insert_time >= 1477411200 AND insert_time <= 1477497599 ORDER BY a.id DESC LIMIT 0,20;

 

使用EXPLAIN查看该语句的执行计划，发现查询时没有使用字段insert_time上面的索引，而是用了app_id【应用id】这个字段上的索引,用户可以查看他拥有权限的app_id,如果是管理员，就可以查看所有的应用，换句话说，MySQL查询时需要扫描全表。这就奇怪了，我们建立新表只是去掉了分区表的分区部分，表结构的其他部分还是一样的。

 

在原有分区表上使用EXPLAIN得到的结果是一样的，但是在分区表上该SQL语句实际执行虽然也比较慢【2秒多】， 但是这个结果比在普通单表执行要快好多，这样我们几乎可以推断出带时间范围的查询会在某个特定的分区上操作，所以即便全表扫描也不会耗时太多。如果我们在月初的时候查询月初时间范围的数据会非常快，事实证明，我们的推断是对的，而实际上这也正是分区表的强项。因此，该SQL语句查询时全表扫描的问题一直都存在，只是被分区表的特性隐藏起来了，在每个月数据量不大【100多万】的时候，问题没有明显地暴露出来。后台类的数据查询，2-3秒返回结果，似乎同事们也勉强能够接受。

 

短信平台的查询都是会限定时间范围的，一般还附带有app_id和isp_id【供应商id】，因此让DBA同事建立了一个联合索引union_index_1(insert_time, app_id, isp_id)。然而，在测试中发现，查询短信列表时还是得等待5秒多。EXPLAIN查询语句的结果如下：

很不幸，查询时还是使用了app_id字段上的索引。虽然加上了联合索引，但是没有生效。由于业务没有专门针对app_id的查询，所以果断地把这个索引去掉，无效的索引不仅占用磁盘空间，而且干扰查询效率，数据库建索引的时候特别需要充分考虑业务场景。果然如此，去掉key_app_id索引后查询就快起来了，前端页面几乎是秒回。到这一步，自然就以为用上了联合索引union_index_1。

 

作为负责任的开发，系统的各种组合查询条件都有必要测试一遍，奇怪的事情发生了，当某个组合条件没有数据返回时【以上SQL语句的project=’通知中心’】，查询耗时7秒多。按常理来说，没有结果集时MySQL返回应该更快才对，毕竟没有数据传输时间，所以其中必有蹊跷。EXPLAIN没有结果集的查询语句的结果如下： 

仔细一看，查询时没有使用联合索引union_index_1，而是用了主键PRIMARY(自增id),这就奇怪了，难道有结果集时就使用联合索引union_index_1，没有结果集时就不使用吗？EXPLAIN有结果集的查询语句，发现执行计划跟没有结果集的查询语句的执行计划是一样的。

 

MySQL不仅可能会针对WHERE条件里面的字段使用索引，还可能会对ORDER BY的字段使用索引，而且同时只能使用一个索引。我们的SQL语句中包含WHERE条件，而且还加了ORDER BY，很不幸地MySQL就使用了主键索引。

 

根据执行计划中的key，rows，extra信息和查询语句可以知道，MySQL优化器机智地按id的降序查询出特定条记录，然后过滤出满足条件的20条记录就完成了查询。在有足够结果集的时候，这种取巧方式是很有效的，但是在没有结果集或者结果集不够20条记录的情况下就得扫描全表了，导致查询非常慢，真是聪明反被聪明误，机器也是如此。实际上，如果查询语句不加LIMIT，或者LIMIT的偏移值稍大一些的话，执行计划显示会使用联合索引union_index_1。以下是不加LIMIT的执行计划，另外，LIMIT 10000，20的结果是一样的。

EXPLAIN SELECT * FROM boyaa_sms a LEFT JOIN appid_key b ON a.app_id=b.appid WHERE app_id IN (100009,100010,100018,100021,100023,100025) AND dept1_id=328 AND dept2_id=194 AND project='通知中心' AND insert_time > 1477411200 AND insert_time <= 1477497599 ORDER BY a.id DESC;  

知道了慢查询的原因之后，我们就比较容易写出高效的查询语句了。把ORDER BY id改成ORDER BY insert_time，id是自增的，insert_time也是自增的，对业务没有任何影响，而且WHERE条件和ORDER BY同时用上了联合索引，减少了filesort的过程，查询起来更快。

EXPLAIN SELECT * FROM boyaa_sms a LEFT JOIN appid_key b ON a.app_id=b.appid WHERE app_id IN (100009,100010,100018,100021,100023,100025) AND dept1_id=328 AND dept2_id=194 AND project='通知中心' AND insert_time > 1477411200 AND insert_time <= 1477497599 ORDER BY a.insert_time DESC LIMIT 0, 20;

作为开发人员，在业务中遇到问题时，应当从最基本的技术理论出发，善于借助一些工具进行定位、分析、解决，提升自身解决问题的能力。同时，任何的技术架构都是为业务提供服务的，任何脱离了业务场景去大谈技术，或者随意应用技术，不注重实际效果的做法都是不可取的。