• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MYSQL千万级连表查询优化案例

MySQL SEian.G 来源:Tim_PHPer 5年前 (2019-05-23) 2306次浏览 已收录 0个评论
文章目录[隐藏]

问题背景

随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此需要对SQL进行优化。

这个SQL查询关联两个数据表,一个是攻击IP用户表主要是记录IP的信息,如第一次攻击时间,地址,IP等等,一个是IP攻击次数表主要是记录每天IP攻击次数。而需求是获取某天攻击IP信息和次数。

(以下SQL语句测试均在测试服务器上上,正式服务器的性能好,查询时间快不少。)

环境准备

查看表的行数:

表名
行数
blacklist_attack_ip
35074971
blacklist_ip_count_date
15442993

未优化前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分析一下:

MYSQL千万级连表查询优化案例

这里看到索引是有的,但是IP攻击次数表blacklist_ip_count_data也用上了临时表。那么这SQL不优化直接第一次执行需要多久(这里强调第一次是因为MYSQL带有缓存功能,执行过一次的同样SQL,第二次会快很多。)

MYSQL千万级连表查询优化案例

实际查询时间为344.997s,这完全不能接受呀,这还是没有其他搜索条件下的。

那么针对该问题,我们怎么优化呢?

优化方案(一)

索引既然走了,我们尝试一下避免临时表,这时我们先了解一下临时表跟group by的使联系:

查找了网上一些博客分析GROUP BY 与临时表的关系 :

1. 如果GROUP BY 的列没有索引,产生临时表.

2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.

3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.

4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.

5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.

6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

仔细按照上面分析一下,这SQL可能是因为第二条导致的,blacklist_ip_count_date这个表的确主键不是IP,SELECT是多列的,那么我们试试单独提出单表测试能不能避免临时表:

MYSQL千万级连表查询优化案例

很遗憾,并不能避免,但是我们仔细看看这EXPLAIN 里面的KEY 分析,用的索引是date单字段的索引。这好像就是导致了第一条的问题了,相当于GROUP BY没有用索引。那么我们试试强制使用IP单字段的索引呢?

MYSQL千万级连表查询优化案例

这里看来的确是索引的问题,导致了临时表啊,然而再看看ROWS的数量,原来的9W变成了1552W,这不是不是捡了芝麻掉了西瓜吗?

这里单列索引 避免了临时表可是联系的行数又增加了,那么我们再试试复合索引呢?

于是创建attack_count、date、ip的复合索引index_Acount_date_ip

MYSQL千万级连表查询优化案例

ROWS的行数770W而且还是有临时表,看来这复合索引也是不可取。

到此,避免临时表方法失败了,我们得从其他角度想想如何优化。

其实,9W的临时表并不算多,那么为什么导致会这么久的查询呢?我们想想这没优化的SQL的执行过程是怎么样的呢?

网上搜索得知内联表查询一般的执行过程是:

1、执行FROM语句

2、执行ON过滤

3、添加外部行

4、执行where条件过滤

5、执行group by分组语句

6、执行having

7、select列表

8、执行distinct去重复数据

9、执行order by字句

10、执行limit字句

这里得知,Mysql 是先执行内联表然后再进行条件查询的最后再分组,那么想想这SQL的条件查询和分组都只是一个表的,内联后数据就变得臃肿了,这时候再进行条件查询和分组是否太吃亏了,我们可以尝试一下提前进行分组和条件查询,实现方法就是子查询联合内联查询。

MYSQL千万级连表查询优化案例

这里EXPLAIN看来,只是多了子查询,ROWS和临时表都没有变化。那么我们看看实际的效果呢?

MYSQL千万级连表查询优化案例

可见,取出来的数据完全一模一样,可是优化后效率从原来的344.997s变成了0.289s,这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。

小结

整个过程中我们得知,其实EXPLAIN有时候并不能指出你的SQL的所有问题,有一些隐藏问题必须要你自己思考,正如我们这个例子,看起来临时表是最大效率低的源头,但是实际上9W的临时表对MYSQL来说不足以挂齿的。我们进行内联查询前,最好能限制连的表大小的条件都先用上了,同时尽量让条件查询和分组执行的表尽量小。

估计到这里,你猜这里就是全部的优化方案?不不不,整个优化过程怎么可能只是发现一个优化方案。下面看另外一种优化方案。

优化方案(二)

这里用的是内联表查询,大家都是知道子查询完全是可以代替内联表查询的,只不过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分组:

MYSQL千万级连表查询优化案例

2、攻击IP用户表blacklist_attack_ip指定具体的IP获取信息:

MYSQL千万级连表查询优化案例

然后结合在一起:

MYSQL千万级连表查询优化案例

可见,取出来的数据完全一模一样,可是优化后效率从原来的344.997s变成了0.283s,这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。

我们EXPLAIN了解一下情况:

MYSQL千万级连表查询优化案例

小结

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

 

文章链接

https://blog.csdn.net/Tim_phper/article/details/78344444

https://blog.csdn.net/Tim_phper/article/details/78357016

MYSQL千万级连表查询优化案例


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MYSQL千万级连表查询优化案例
喜欢 (5)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址