开发者社区> 优惠券发放> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: MYSQL一次千万级连表查询优化 原文地址 https://blog.csdn.net/Tim_phper/article/details/78357016 概述: 交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。
+关注继续查看

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

原文地址 https://blog.csdn.net/Tim_phper/article/details/78357016

概述:

交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的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一次千万级连表查询优化(一)解决原理一样,都是解决了内联表后数据就变得臃肿了,这时候再进行条件查询和分组就太吃亏了,于是我们可以先对单表进行条件处理,再进行连表查询,只不过这个方案只是用了子查询而没有内联查询了,而两者效率对比之下,有内联的方案带其他查询条件的效率更高。感谢您们的阅读,如果有更好的方案,欢迎留言交流!!!

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
+关注
优惠券发放
阿里云优惠码阿里云推荐券bieryun.com
文章
问答
文章排行榜
最热
最新
相关电子书
更多
HTAP能力持续增强 HybridDB for MySQL分析性能提升
立即下载
MySQL表和索引优化实战
立即下载
MySQL 5.7优化不求人
立即下载