SQL慢查询优化2

浅浅的花香味﹌ 2023-10-12 09:39 9阅读 0赞

### SQL慢查询优化2 ###

*  背景
 *  案例
 *   *  慢SQL
     *  表结构
     *  执行计划
     *  疑问
     *  执行计划
     *  思考
     *  验证
     *  执行计划
     *  分析
     *   *  主责
         *  次责
     *  建索引
     *  表结构
     *  执行计划
     *  实时慢SQL统计
 *  总结

# 背景 #

场景：慢查询报警，每分钟60条  
目标：每分钟慢查询报警小于10次  
方案：分析慢SQL原因，指定方案  
结果：每分钟慢查询报警小于10次

通过1个案例，来说明慢SQL优化。

# 案例 #

![在这里插入图片描述][6dea56d12bcc42fc911b1d4bd2baa24f.png]

## 慢SQL ##

慢查询当中，最多的一条SQL语句，就是SELECT \* FROM user\_message WHERE **user\_id** = 1234567890 and **msg\_type** = 2 ORDER BY **CREATE\_TIME** DESC LIMIT 880, 10;

## 表结构 ##

![在这里插入图片描述][73d6ce6555a44e34a058258981a4e37e.png]

## 执行计划 ##

第一个看的字段是possible\_keys，含义就是理论上用到的索引，现在为idx\_user\_pushtime, idx\_user\_state, idx\_msg\_type\_user\_id\_CREATE\_TIME。  
![在这里插入图片描述][73a6409dcd2643d1a570698e0add9206.png]  
第二个看的字段是key，含义就是实际上用到的索引，现在为idx\_user\_pushtime。

第三个看的字段是extra，含义就是额外的动作，我们特别关注一下using filesort，这是一个危险的信号。

## 疑问 ##

key实际上用到的索引竟然是idx\_user\_pustime?  
![在这里插入图片描述][79f91098059d43ec8f4b7a65a8822de3.png]  
这个SQL，难道不是应该使用索引`idx_msg_type_user_id_CREATE_TIME`(`msg_type`,`user_id`,`create_time`)更合适吗？

这里可能有网友会说：因为SQL语句中where条件为：use\_id = 1234567890 and msg\_type = 2 ORDER BY CRATE\_TIME DESC LIMIT DESC LIMIT 880, 10;

如果msg\_type和user\_id的位置置换一下，比如这样子：  
![在这里插入图片描述][220fdeb4ee1f48d6ae6884966eaf1ecd.png]  
会不会用到索引：  
idx\_msg\_type\_user\_id\_CREATE\_TIME

## 执行计划 ##

我们再次看一下SQL的执行计划：  
![在这里插入图片描述][d554c158ba8c4a6aa897eaf1d484a6f8.png]  
发现是一样的。都是用了索引：idx\_user\_pushtime

## 思考 ##

可能是因为msg\_type的区分度太小了。msg\_type只有2种值，放在索引的第一位，实在不合适。因此，mysql的优化器，用了区分度大的idx\_user\_pushtime。但用了这个idx\_user\_pushtime索引，因为order by，造成了using filesort。

## 验证 ##

强制使用一下索引：  
idx\_msg\_type\_user\_id\_CREATE\_TIME  
![在这里插入图片描述][b09e7546b597475db6167728852eff18.png]

## 执行计划 ##

首先，用的索引key，的确为：  
idx\_msg\_type\_user\_id\_CREATE\_TIME。且extra已经没using filesort。

消耗的时间：  
![在这里插入图片描述][9a9c5471aeac41b793672ac625b93f24.png]

## 分析 ##

### 主责 ###

主要责任还是人建错了索引。

idx\_msg\_type\_id\_CREATE\_TIME索引，这个联合索引，第一个索引竟然是msg\_type，区分度太小了，几乎为0。

msg\_type只有2个取值，这个和sex性别字段上建索引是一样的。建索引应该遵守：(where 条件顺序可互换的前提下）区分度从大到小。比如这索引应该建为：idx\_user\_id\_msg\_type\_create\_time。

### 次责 ###

Mysql用错索引了。

补充一下知识，mysqk用哪个索引，是通过样本筛选来选的。

样本筛选，就说明有误差，这个误差往往会导致用错索引。现在呢，我们已经清楚问题所在了。mysql用错索引，这个是我们没法改的。我们能做的就是“建对索引”。

因此，我们建一个idx\_user\_id\_msg\_type\_create\_time的索引。

## 建索引 ##

alter table user\_message add index **idx\_user\_id\_msg\_type\_create\_time**(user\_id, msg\_type, create\_time);

## 表结构 ##

![在这里插入图片描述][d048470c8b2e455bbbb783f6b6f4e8fb.png]

## 执行计划 ##

![在这里插入图片描述][126f105fc224457f82abeb37f6ce5cc0.png]  
这个时候，已经没using filesort，也没满查询了。同时也验证了猜想是正确的。

## 实时慢SQL统计 ##

![在这里插入图片描述][79446654ad6949fea93cb6bea3e67793.png]

# 总结 #

其实就是人为建的索引，建错了，这个错。不是指字段，是指字段的顺序建错了。可见，键索引，学问是很多的。

强制使用索引force index(索引名)

**参考资料**：[《大厂面试题》为什么SQL使用了索引，却还是慢查询(续集)][SQL]

[6dea56d12bcc42fc911b1d4bd2baa24f.png]: https://img-blog.csdnimg.cn/6dea56d12bcc42fc911b1d4bd2baa24f.png
[73d6ce6555a44e34a058258981a4e37e.png]: https://img-blog.csdnimg.cn/73d6ce6555a44e34a058258981a4e37e.png
[73a6409dcd2643d1a570698e0add9206.png]: https://img-blog.csdnimg.cn/73a6409dcd2643d1a570698e0add9206.png
[79f91098059d43ec8f4b7a65a8822de3.png]: https://img-blog.csdnimg.cn/79f91098059d43ec8f4b7a65a8822de3.png
[220fdeb4ee1f48d6ae6884966eaf1ecd.png]: https://img-blog.csdnimg.cn/220fdeb4ee1f48d6ae6884966eaf1ecd.png
[d554c158ba8c4a6aa897eaf1d484a6f8.png]: https://img-blog.csdnimg.cn/d554c158ba8c4a6aa897eaf1d484a6f8.png
[b09e7546b597475db6167728852eff18.png]: https://img-blog.csdnimg.cn/b09e7546b597475db6167728852eff18.png
[9a9c5471aeac41b793672ac625b93f24.png]: https://img-blog.csdnimg.cn/9a9c5471aeac41b793672ac625b93f24.png
[d048470c8b2e455bbbb783f6b6f4e8fb.png]: https://img-blog.csdnimg.cn/d048470c8b2e455bbbb783f6b6f4e8fb.png
[126f105fc224457f82abeb37f6ce5cc0.png]: https://img-blog.csdnimg.cn/126f105fc224457f82abeb37f6ce5cc0.png
[79446654ad6949fea93cb6bea3e67793.png]: https://img-blog.csdnimg.cn/79446654ad6949fea93cb6bea3e67793.png
[SQL]: https://www.bilibili.com/video/BV1ZB4y1v75U/?spm_id_from=333.337.search-card.all.click&vd_source=a1af903e57688df870625021f7f3a7eb