MYSQL一次千万级连表查询优化
概述:
交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。
这个SQL查询关联两个数据表,一个是攻击IP用户表主要是记录IP的信息,如第一次攻击时间,地址,IP等等,一个是IP攻击次数表主要是记录每天IP攻击次数。而需求是获取某天攻击IP信息和次数。(以下SQL语句测试均在测试服务器上上,正式服务器的性能好,查询时间快不少。)
准备:
查看表的行数:
未优化前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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
先EXPLAIN分析一下:
这里看到索引是有的,但是IP攻击次数表blacklist_ip_count_data也用上了临时表。那么这SQL不优化直接第一次执行需要多久(这里强调第一次是因为MYSQL带有缓存功能,执行过一次的同样SQL,第二次会快很多。)
实际查询时间为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分组:
2、攻击IP用户表blacklist_attack_ip指定具体的IP获取信息:
然后结合在一起:
可见,取出来的数据完全一模一样,可是优化后效率从原来的330秒变成了0.28秒,这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。
我们EXPLAIN了解一下情况:
总结:
其实这个优化方案跟我上一篇文章MYSQL一次千万级连表查询优化(一)解决原理一样,都是解决了内联表后数据就变得臃肿了,这时候再进行条件查询和分组就太吃亏了,于是我们可以先对单表进行条件处理,再进行连表查询,只不过这个方案只是用了子查询而没有内联查询了,而两者效率对比之下,有内联的方案带其他查询条件的效率更高。感谢您们的阅读,如果有更好的方案,欢迎留言交流!!!