MYSQL 多表连接查询优化

时间:2024-03-31 21:13:45

概述:

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

准备:

查看表的行数: 
MYSQL 多表连接查询优化 
MYSQL 多表连接查询优化 
未优化前SQL语句为:

 
  1. SELECT

  2. attack_ip,

  3. country,

  4. province,

  5. city,

  6. line,

  7. info_update_time AS attack_time,

  8. sum( attack_count ) AS attack_times

  9. FROM

  10. `blacklist_attack_ip`

  11. INNER JOIN `blacklist_ip_count_date` ON `blacklist_attack_ip`.`attack_ip` = `blacklist_ip_count_date`.`ip`

  12. WHERE

  13. `attack_count` > 0

  14. AND `date` BETWEEN '2017-10-13 00:00:00'

  15. AND '2017-10-13 23:59:59'

  16. GROUP BY

  17. `ip`

  18. LIMIT 10 OFFSET 1000

先EXPLAIN分析一下: 
MYSQL 多表连接查询优化
这里看到索引是有的,但是IP攻击次数表blacklist_ip_count_data也用上了临时表。那么这SQL不优化直接第一次执行需要多久(这里强调第一次是因为MYSQL带有缓存功能,执行过一次的同样SQL,第二次会快很多。) 
MYSQL 多表连接查询优化
实际查询时间为300+秒,这完全不能接受呀,这还是没有其他搜索条件下的。 
那么我们怎么优化呢,索引既然走了,我尝试一下避免临时表,这时我们先了解一下临时表跟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的列没有索引,产生临时表.

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

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

  2. 1、执行FROM语句

  3. 2、执行ON过滤

  4. 3、添加外部行

  5. 4、执行where条件过滤

  6. 5、执行group by分组语句

  7. 6、执行having

  8. 7、select列表

  9. 8、执行distinct去重复数据

  10. 9、执行order by字句

  11. 10、执行limit字句

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

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

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

 

 第二种优化:这里用的是内联表查询,大家都是知道子查询完全是可以代替内联表查询的,只不过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 多表连接查询优化
可见,取出来的数据完全一模一样,可是优化后效率从原来的330秒变成了0.28秒,这里足足提升了1000多倍的速度。这也基本满足了我们的优化需求。 
我们EXPLAIN了解一下情况: 
MYSQL 多表连接查询优化