MYSQL一次千万级连表查询优化（二）

Myth丶恋晨 2022-04-02 04:28 303阅读 0赞

转载， https://blog.csdn.net/Tim\_phper/article/details/78357016

## **概述：** ##

交代一下背景，这算是一次项目经验吧，属于公司一个已上线平台的功能，这算是离职人员挖下的坑，随着数据越来越多，原本的SQL查询变得越来越慢，用户体验特别差，因此SQL优化任务交到了我手上。  
这个SQL查询关联两个数据表，一个是攻击IP用户表主要是记录IP的信息，如第一次攻击时间，地址，IP等等，一个是IP攻击次数表主要是记录每天IP攻击次数。而需求是获取某天攻击IP信息和次数。（以下SQL语句测试均在测试服务器上上，正式服务器的性能好，查询时间快不少。）

## **准备：** ##

查看表的行数：  
![这里写图片描述][SouthEast]  
![这里写图片描述][SouthEast 1]  
未优化前SQL语句为：

SELECT attack_ip, country, province, city, line, info_update_time AS attack_time, sum( attack_count ) AS attack_times FROM `blacklist_attack_ip` INNER JOIN `blacklist_ip_count_date` ON `blacklist_attack_ip`.`attack_ip` = `blacklist_ip_count_date`.`ip` WHERE `attack_count` > 0 AND `date` BETWEEN '2017-10-13 00:00:00' AND '2017-10-13 23:59:59' GROUP BY `ip` LIMIT 10 OFFSET 1000

先EXPLAIN分析一下：  
![这里写图片描述][SouthEast 2]  
这里看到索引是有的，但是IP攻击次数表blacklist\_ip\_count\_data也用上了临时表。那么这SQL不优化直接第一次执行需要多久（这里强调第一次是因为MYSQL带有缓存功能，执行过一次的同样SQL，第二次会快很多。）  
![这里写图片描述][SouthEast 3]  
实际查询时间为300+秒，这完全不能接受呀，这还是没有其他搜索条件下的。  
那么我们怎么优化呢，这里用的是内联表查询，大家都是知道子查询完全是可以代替内联表查询的，只不过SQL语句复杂了不少，那么我们分析一下这SQL，两个表分表提供了什么？  
1、IP攻击次数表blacklist\_ip\_count\_data主要提供的指定时间条件查询，攻击次数条件查询后的IP和每个IP符合条件下的具体攻击次数。  
2、攻击IP用户表blacklist\_attack\_ip主要是具体IP的信息，如第一次攻击时间，地址，IP等等。  
那么我们一步步来：  
1、IP攻击次数表blacklist\_ip\_count\_data获取符合时间条件和攻击次数的IP并且以IP分组：  
![这里写图片描述][SouthEast 4]  
2、攻击IP用户表blacklist\_attack\_ip指定具体的IP获取信息：  
![这里写图片描述][SouthEast 5]  
然后结合在一起：  
![这里写图片描述][SouthEast 6]  
可见，取出来的数据完全一模一样，可是优化后效率从原来的330秒变成了0.28秒，这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。  
我们EXPLAIN了解一下情况：  
![这里写图片描述][SouthEast 7]

## **总结：** ##

其实这个优化方案跟我上一篇文章[MYSQL一次千万级连表查询优化（一）][MYSQL]解决原理一样，都是解决了内联表后数据就变得臃肿了，这时候再进行条件查询和分组就太吃亏了，于是我们可以先对单表进行条件处理，再进行连表查询，只不过这个方案只是用了子查询而没有内联查询了，而两者效率对比之下，有内联的方案带其他查询条件的效率更高。感谢您们的阅读，如果有更好的方案，欢迎留言交流！！！

[SouthEast]: /images/20220402/8ecfe3521ec34977be61d8592be13c0c.png
[SouthEast 1]: /images/20220402/5c0b5da2b6f44602b02a5b1ffaed0a6b.png
[SouthEast 2]: /images/20220402/e3b95ccaaa3149e39878fa4519b4a89f.png
[SouthEast 3]: /images/20220402/9c9a4b263ca94b86a5f77ede407be227.png
[SouthEast 4]: /images/20220402/a66716f4c45c47d1bdf2f0428f60bc16.png
[SouthEast 5]: /images/20220402/625c2a78e85f454a80db4df472c071d9.png
[SouthEast 6]: /images/20220402/f2cdc6e6f3b4435eb7fdb07970f4e4f2.png
[SouthEast 7]: /images/20220402/6dfaa70f20b747f0ad6a28bea38739ad.png
[MYSQL]: http://blog.csdn.net/Tim_phper/article/details/78344444