Mysql优化 -- group by

背景

由于整体的历史原因，导致部分设备存在COMMON类型无法进行区分，需要根据对应的通道数进行区分，导致需要device 和camera的两个表需要级联查询，进行分页操作。

未优化前SQL语句为：

SELECT a.serial_uuid, a.user_id, a.device_name
FROM device a
         LEFT JOIN camera b
                   USING (serial_uuid)
WHERE a.user_id = 1
GROUP BY b.serial_uuid
HAVING count(b.serial_uuid) > 1
LIMIT 0, 10;
;

device表的索引

  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_device_serial` (`serial_uuid`),
  KEY `idx_user_id` (`user_id`) 

camera表的索引

  UNIQUE KEY `uidx_serial_channel` (`serial_uuid`,`channel_no`),
  KEY `idx_user_id` (`user_id`)

我们来explain一下这个sql语句

image.png

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

查询时间达到1s中左右
那么我们怎么优化呢，索引既然走了，我尝试一下避免临时表，这时我们先了解一下临时表跟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的列没有索引,产生临时表.

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的条件查询和分组都只是一个表的，内联后数据就变得臃肿了，这时候再进行条件查询和分组是否太吃亏了，我们可以尝试一下提前进行分组和条件查询，实现方法就是子查询联合内联查询。

group的也是 索引,根据之前group by的第一条如果GROUP BY 的列没有索引,产生临时表.
这里子查询可以使用group利用serial_uuid的索引（不太清楚为什么级联查询没有使用上），可以使用mysql默认的b+tree 来避免排序分组，避免产生临时表。

image.png

camera表的索引

  UNIQUE KEY `uidx_serial_channel` (`serial_uuid`,`channel_no`),
  KEY `idx_user_id` (`user_id`,`serial_uuid`)

增加索引后的explain

image.png

优化后的sql语句为：

SELECT a.serial_uuid
FROM
    (SELECT
         b.id,
         b.dvc_device_id,
         b.serial_uuid
     FROM camera b
     WHERE b.user_id = 1
     GROUP BY b.serial_uuid
     HAVING COUNT(b.serial_uuid) > 1
    ) c
    LEFT JOIN device a
        ON c.serial_uuid = a.serial_uuid
WHERE a.user_id = 1
LIMIT 0, 10;

这样查询速度提升了到了97ms，提升了10倍。
要是有其它更好的方案希望指出进行交流，来增加对mysql的学习和了解

总结：

由于级联查询的中间表会非常大，最好能限制连的表大小的条件都先用上了，同时尽量让条件查询和分组执行的表尽量小。

参考：https://blog.csdn.net/Tim_phper/article/details/78344444