MySQL慢查询优化经验

谁借莪１个温暖的怀抱￠ 2021-11-11 01:16 418阅读 0赞

# MySQL慢查询优化经验 #

## 引言 ##

由于工作中一直在做稳定性相关的工作，在一点一点梳理线上的MySQL慢查询，发现目前线上各个服务都存在很多的慢查询。有些慢查询情况很严重，已经严重影响了线上服务的性能。

## 什么是慢查询？ ##

慢查询是什么？在理想状态下，MySQL查询的执行耗时都在毫秒以下，如果执行超过1毫秒，那么出现这样长耗时的SQL就需要值得高度注意了。  
最近有一个团队的线上报了一条执行耗时长达15秒的SQL，十分值得注意。此外，在最近增加了SQL执行耗时的监控之后，发现各服务有很多的SQL执行耗时都在毫秒以上，个别业务场景下耗时超过100毫秒。所以特别将最近遇到的case和优化方案拿出来分享一下。

## 如何优化慢查询？ ##

### 1.利用好索引 ###

最近在跟一个实习生聊最近线上报的一些极端的慢查询case的时候，小伙子很惊奇的看着我，这些索引相关的问题在面试的时候不是经常会考察吗？为什么咱们线上还有有这么多的因为使用索引不合理导致的慢查询？虽然面试中会经常考察MySQL索引使用的一些问题，但是实际开发过程中，由于开发者的水平不一、业务开发排期紧张、框架封装等各种各样的问题，会导致实际写出的SQL效率让人感到惊吓。  
废话说的有点多，说重点。目前在线上遇到几个因为索引使用不合理导致的慢查询，主要是因为在使用联合索引的问题，主要是以下两个case。

#### 1.1 忽略最左匹配原则 ####

大家都知道，我们在创建一个联合索引，比如`ALTER TABLE order ADD INDEX idx_phone_company_ctime (phone, company, create_time)`这样的一个索引，MySQL实际会创建出phone, phone+company, phone+company+create\_time三个索引，而就在实际使用中，在进行拼装SQL的时候就写成了类似

SELECT id, phone, status
    FROME order
    WHERE
    `phone` = 18800001111
    AND `status` = 1 
    AND `create_time` > 1564815738
    LIMIT 5 \G;

然后后果就是，最终导致了只命中了phone的索引，在order表中由于phone这个区分度不是特别大，存在个别手机号的身上有大量订单的情况，最终执行耗时飙升到了200毫秒。  
这个问题是实际开发中看似是很低级的问题，但是确实存在很多这样的问题，开发人员在开发中为了赶工期，认为需求开发完成就可以了，没有回头review写出来的SQL的耗时，尤其是遇到订单系统这样复杂的业务场景的时候。

#### 1.2 使用范围查询导致索引失效 ####

这个也是使用联合索引经常会遇到的问题，比如线上订单表中一个索引是类似`ALTER TABLE order ADD INDEX idx_company_status_ctime (company, status, create_time)`这种的（其实这个索引本身就有问题，company和status这样前缀的索引，区分度不大，导致查询效率就不是很高）。但是有些开发在使用这个索引的时候，写出来了类似这样的SQL:

SELECT id, phone, status
    FROME order
    WHERE
    `company` = 1001
    AND `status` IN (0, 1, 2) 
    AND `create_time` > 1564815738
    LIMIT 20 \G;

很明显，在这种情况下，由于第二列status使用了范围查询，导致这个索引只命中了company+status，同时由于索引创建前两列在整个表中的区分度并不大，导致查询效率低下。  
在此特别说一下，还有以下的一些情况也会导致索引失效：

*  使用范围查询，例如IN，BETWEEN AND, (>, < ,>=, <=)
 *  查询包含NULL
 *  查询包含OR
 *  使用!=或者是<>

#### 简单总结一下 ####

在使用索引方面，其实主要是目前很多数据库的查询操作由各种ORM框架来做了，在写数据查询的代码的时候，很多开发者忽略了最后由ORM框架最终生成的SQL的执行效率究竟如何。这个问题在初级开发者中十分常见，希望各位引以为戒！

### 2. 业务优化 ###

这个问题也比较有意思，简单描述一下：我们在做业务迭代的时候，把用户的常用地址收敛到了一个单独的地址服务中，但是有些服务端仍然保留了保存和查询地址的业务逻辑。结果这部分数据实际上之后非常古老的客户端版本在使用，但是新版本的请求进来的时候，还是会把地址数据存储起来。（地址服务收敛之后，创建了单独的数据存储结构，并且客户端直接请求地址服务）  
然后突然某一天，线上报了一连串的慢查询，都是查询地址导致的。是因为老的数据库中地址表数据由于数据积累，已经达到了千万级，同时由于后序维护的开发者不清楚这段逻辑的调用关系，导致这个逻辑一直维持在线上，最终在执行的过程中出现了慢查询。感觉这个问题也是比较低级的错误，但是因为迭代的工期紧张和人员轮转，这段逻辑就一直在跑，产生了极大的影响。优化的方案也很简单粗暴，针对新版本直接删除这段逻辑就可以了。

### 3. 缓存 ###

其实缓存并不完全是为了优化慢查询，更多是因为热点数据请求直接打到数据库会给数据库带来极大的负载，影响数据库的性能，最终的结果可能不只是慢查询这么简单的问题了。  
我们设置缓存更多的时候都是为了查询热点数据，但是在使用缓存的时候要注意使用缓存对缓存服务器带来的负载和开销，还要保证好数据一致性。还有就是要注意缓存击穿和缓存雪崩带来的问题，避免因为缓存使用不合理和无法保证一致性带来其他的业务问题和稳定性问题。  
使用缓存会在很大程度上提升查询效率，但是使用缓存要结合实际的场景和实际的请求量做出评估，不要为了使用缓存而使用缓存。毕竟缓存和数据库一样都是*成本*。

### 4. 拆库拆表 ###

MySQL的单表在百万级别数据量的情况还可以保证比较好的查询效率，但是当数据膨胀到千万级别的时候就要考虑拆分数据库或者是拆分表了。

#### 4.1 拆分数据库 ####

数据库拆分更多是出现在服务化进行服务拆解，为了维护好系统边界而做的调整。如果多个服务共用一个数据库，由于开发流程不规范很容易导致数据污染。同时也可能由于其他服务清洗数据或是流量激增的时候，导致整个数据库的负载增加，影响其他使用该数据库的其他服务的性能，在严重的情况下可能会导致使用这个数据库的所有相关服务全部宕掉。  
不完全的服务化或者是不清晰的系统边界，会对相关服务的稳定性在某些极限情况下带来灾难性的影响。

#### 4.2 拆表 ####

当数据库单表数据过于膨胀的时候，会对数据库的查询性能带来极大的影响。常见的拆表方案包括一下几种：

*  hash拆分（常见的解决方案）：根据hash规则将单表拆分为多个表，将一个表的数据相对平均的分配到多个表中。
 *  冷热隔离（百度贴吧解决方案）：在百度贴吧中经常会出现很多很多热点数据，这时候经常会为热点话题创建单独的表来存储热点话题数据，而针对相对冷门的话题，可以多种类型的冷门话题共同使用同一个数据库。这样既保证了热点数据单表查询效率，也降低了冷门数据的存储成本。
 *  归档：在一些查询场景，会存在一些按月份对数据进行归档。比如对账服务中，可能会对固定账期的公司将数据按月份进行归档，同一个月份的订单数据存储到一张单独的表中，便于数据查询和存储。

### 5. 迁移Elastic Search ###

我们根据数据的查询时效性可将查询分为实时查询、近实时查询、离线查询。针对近实时查询和离线查询，可以将数据迁移到Elastic Search服务中，在对大量的数据聚合计算或者是大范围的数据查询时，Elastic Search依然可以保持极高的查询效率，是相比于数据库查询更好的解决方案。

## 总结 ##

慢查询就像“小强”一样，是一件需要持续治理的事情除了需要对MySQL查询原理有比较深刻的之外还有根据实际的业务场景选择合适的优化方式。