MYSQL一次千万级连表查询优化(一)_千万级外连接查询-程序员宅基地

技术标签: 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

先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了解一下情况:
这里写图片描述

总结:

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

--------------------- 本文来自 Tim_PHPer 的CSDN 博客 ,全文地址请点击:https://blog.csdn.net/Tim_phper/article/details/78357016?utm_source=copy

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/insis_mo/article/details/82897665

智能推荐

NUll的作用和意义_null指针的地址-程序员宅基地

文章浏览阅读2.9k次,点赞2次,收藏16次。空地址NULL意义所在深入核心技术与架构,分享典型创新之道,全景展现全栈式分析服务主题演讲和6大分会场,40+前沿技术主题,尽在亚马逊云科技数据驱动在线峰会NULL其地址值为0,而由于任何进程的0地址开始存储的都是系统关键地址,比如进程的退出,堆栈维护,键盘处理等系统控制程序的地址。因此0地址是不允许用户代码中直接读写访问的(hacking除外),如果某指针被赋予NULL,之后该指针被用来操作对象或内存,要么在编译时报错,要么运行时程序崩溃。指针被赋值为NULL的意义在于,将NULL作为唯一无效指针_null指针的地址

NLP/Transformer/BERT/Attention面试问题与答案_attention面试题-程序员宅基地

文章浏览阅读2.4k次。主要聚焦目前处于NLP舞台中央的Transformer/BERT/后BERT 和 Self Attention。筛选的问题会深入到上述算法/模型更细节的地方,而尽量避免大而泛的问题。本文希望能帮助你对Transformer/BERT的理解再深一层,而这也要求你对上面的算法/模型有基本的认识,主要包括这两部分(后BERT的模型可以自行查找):1、论文:论文是最一手的资源,没有各方解读的杂音Transformer:Attention Is All You NeedBERT:Pre-train.._attention面试题

服务器装系统驱动程序,服务器装系统驱动-程序员宅基地

文章浏览阅读2.2k次。服务器装系统驱动 内容精选换一换Windows Server 2012 R2操作系统弹性云服务器,本地使用远程桌面连接功能连接云服务器并启用redirected drive功能时,云服务器出现蓝屏。远程桌面连接启用了redirected drive功能,同时加载对应rdpdr.sys驱动,该驱动可能会导致云服务器操作系统崩溃,无法正常运作(例如错误码:0x18, 0x5该任务以“Windows S..._服务器驱动

Manjaro 使用 pacman 安装软件出现 错误:无法从 mirrors.ustc.edu.cn : XXX 错误:无法升级 archlinuxcn (下载数据库出错) 信息_archlinuxcn.db下载失败-程序员宅基地

文章浏览阅读2.4w次,点赞6次,收藏18次。系统:Manjaro 20.0.3桌面:Gnome安装软件时出现错误:无法从 mirrors.ustc.edu.cn : Operation timed out after 10001 milliseconds with 0 out of 0 bytes received 获取文件 'archlinuxcn.db'错误:无法升级 archlinuxcn (下载数据库出错)错误:无法从 lonewolf-builder.duckdns.org : Recv failure: 连接被对方重设 获取._archlinuxcn.db下载失败

技术面试时这样介绍自己的项目经验,等于成功了一大半-程序员宅基地

文章浏览阅读2.9k次,点赞4次,收藏35次。源/头条 文 /程序员界的彭于晏面试时7分靠能力,3分靠技能,而刚开始时的介绍项目又是技能中的重中之重,决定一次面试的成败,那么面试时如果要介绍自己的项目该..._技术面如何将自己做的项目

git生成ssh密钥详细步骤 git如何生成ssh密钥_gitssh密钥生成-程序员宅基地

文章浏览阅读3.7w次,点赞16次,收藏100次。git生成ssh密钥详细步骤 git如何生成ssh密钥_gitssh密钥生成

随便推点

angular生命周期函数_angularjs生命周期函数-程序员宅基地

文章浏览阅读1.2k次。对于单页面应用来说,组件的生命周期在开发中至关重要。了解生命周期,在适当的时机处理不同的逻辑,从而使应用更加合理与健壮。(原文阅读)定义:当 Angular实例化组件类并渲染组件视图及其子视图时,组件实例的生命周期就开始了。生命周期一直伴随着变更检测,Angular会检查数据绑定属性何时发生变化,并按需更新视图和组件实例。当 Angular销毁组件实例并从 DOM中移除它渲染的模板时,生命周期就结束了。tips:演示将在life-cycle组件中进行。生命周期顺序Angular有以下8个生命周期._angularjs生命周期函数

模型预测控制MPC_模型预测控制(mpc)-程序员宅基地

文章浏览阅读2.1w次,点赞9次,收藏85次。模型预测控制MPC因本人刚开始写博客,学识经验有限,如有不正之处望读者指正,不胜感激;也望借此平台留下学习笔记以温故而知新。这一篇文章介绍的是控制理论中的模型预测控制算法。模型预测控制简介模型预测控制MPC是指:在每一个采样时刻通过求解一个有限时域的开环最优控制策略,是一种通过将过程的当前状态作为最优控制问题的初始状态,解得的最优控制序列只实施于下一时刻的控制方法。模型预测控..._模型预测控制(mpc)

高一计算机课期中考试总结反思,2017高一数学期中考试反思总结-程序员宅基地

文章浏览阅读73次。引导语:数学新课改的基本理念是:学有价值的数学,反映出学生实践能力和创新意识方面的不足,应引起我们的高度重视,学生的动手能力还有待提高。以下是百分网小编分享给大家的2017高一数学期中考试反思总结,欢迎阅读!过去的一学期里,我班在学校领导的统一组织下,在任课教师的大力支持和配合下,各项工作顺利开展,学习、生活等方面都取得较突出的成绩。现将本学期期中考试前的工作总结如下:一、 加强对学生的思想政治工...

CSS 分组 和 嵌套 选择器-程序员宅基地

文章浏览阅读336次,点赞3次,收藏8次。CSS 分组选择器允许你将多个选择器组合到一起,以便为它们应用相同的样式。CSS 嵌套选择器允许你选择嵌套在其他元素内部的元素,并仅为这些特定的嵌套元素应用样式。

为什么linux安装程序 都要放到/usr/local目录下_linux 的软件为什么要安装到/usr/local 下-程序员宅基地

文章浏览阅读4.9k次,点赞2次,收藏5次。Linux 的软件安装目录是也是有讲究的,理解这一点,在对系统管理是有益的/usr:系统级的目录,可以理解为C:/Windows/,/usr/lib理解为C:/Windows/System32。/usr/local:用户级的程序目录,可以理解为C:/Progrem File..._linux 的软件为什么要安装到/usr/local 下

【哈士奇赠书活动 - 24期】-〖前端工程化:基于Vue.js 3.0的设计与实践〗_程沛权 博客-程序员宅基地

文章浏览阅读3.6k次,点赞132次,收藏98次。【哈士奇赠书活动 - 24期】-〖前端工程化:基于Vue.js 3.0的设计与实践〗_程沛权 博客

推荐文章

热门文章

相关标签