mysql千万级表关联优化

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

概述：

准备：

查看表的行数： 

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分析一下： 

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

网上搜索得知内联表查询一般的执行过程是：
1、执行FROM语句
2、执行ON过滤
3、添加外部行 4、执行where条件过滤 5、执行group by分组语句 6、执行having 7、select列表 8、执行distinct去重复数据 9、执行order by字句 10、执行limit字句

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

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

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

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