group by 慢sql查询怎么优化

单独执行下列数据很快0.01s
select * from sale；
结果：18 000 000 条数据
但是group by 后巨慢需要20s
select note from sale group by note;
note是索引字段。
有知道怎么回事嘛？

因为你数据分组了啊，当然会比原本查表慢，优化下索引，之前是非聚簇索引？

如果你的非聚簇索引是这样：
CREATE INDEX idx_note ON sale (note);
改成
CREATE INDEX idx_note ON sale (note) INCLUDE (other_column);

看一眼走索引了吗

执行计划贴下

这是由MySQL的查询优化造成的。当没有GROUP BY时,MySQL可以使用索引快速检索全部数据,所以查询速度很快。
但是当加入GROUP BY后,MySQL无法使用索引,需要做全表扫描来完成分组,所以查询速度变慢。
这是MySQL indexing和查询优化的一个很经典的例子。原因如下:

INDEX只适合用于过滤,定位到具体数据页。而GROUP BY是在内存中进行分组聚合,无法直接使用INDEX。
MySQL无法在索引中同时存储note的值和聚合结果,所以无法直接从索引中完成GROUP BY。
MySQL没有利用索引来过滤后再聚合,而是直接做了全表扫描。这降低了索引的用处,大大减慢了查询速度。
要优化这个查询,可以使用以下方法:
Using SQL_CACHE。这个可以缓存查询结果,对重复查询有效。
使用临时表。可以使用索引过滤后,将结果导入临时表,然后在临时表上做GROUP BY。
使用SQL hint。可以添加SQL hint /!63000 TEMPORARY / 来创建临时表。
优化数据库结构,添加索引。如果有其它字段也用于GROUP BY,可以为其添加索引。
分解查询。可以不使用GROUP BY,先使用索引过滤进行部分聚合,然后再在结果上 completing 聚合。
举例来说,你可以这样优化:
sql
/* 使用临时表 */
CREATE TEMPORARY TABLE tmp AS
SELECT * FROM sale WHERE note = 'some_note';

SELECT note FROM tmp GROUP BY note;

/* 使用SQL hint创建临时表 /
SELECT /!63000 TEMPORARY */ note FROM sale WHERE note = 'some_note' GROUP BY note;

/* 分解查询 /
SELECT note, SUM(col1) AS sum_col1 FROM sale WHERE note = 'some_note';
SELECT note, MAX(col2) AS max_col2 FROM sale WHERE note = 'some_note';
/ 然后再合并结果 */

以下回答由chatgpt基于相关博客总结生成:
针对该问题的解决方案如下：
1. 确保group by后面的列有索引，尽量让group by过程使用索引，可以通过执行explain命令查看是否使用了索引，确认方法是explain结果里没有Using temporary 和 Using filesort。如果索引是前缀索引，则需要检查前缀长度是否适当，并根据需要进行调整。
2. 避免在group by之后进行having过滤，改用where子句提前过滤需要查询的数据，以减少排序分组的开销。
3. 可以考虑冗余一个字段来解决排序开销的问题，例如参考资料中的stats_date字段来统计某月每天的新增用户量。
4. 如果数据量实在太大，可以使用SQL_BIG_RESULT提示来直接使用排序算法（直接用磁盘临时表）得到group by的结果。
具体示例代码如下：
1. 确保group by使用了索引
```
explain select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;
```
1. 使用where子句替换having过滤
```
select * from sale where sale_time between '2021-01-01 00:00:00' and '2021-01-31 23:59:59' and status = 1 group by customer_id;
```
替换为
```
select * from (select * from sale where sale_time between '2021-01-01 00:00:00' and '2021-01-31 23:59:59' and status = 1) t group by customer_id;
```
1. 冗余一个字段来避免排序
```
alter table users add stats_date date;
update users set stats_date = date(create_time);  // 冗余stats_date字段
create index idx_stats_date on users(stats_date);

select stats_date, count(*) from users where create_time between '2021-01-01 00:00:00' and '2021-01-31 23:59:59' group by stats_date;
```
1. 使用SQL_BIG_RESULT提示
```
select SQL_BIG_RESULT * from sales group by customer_id;
```
注意，使用SQL_BIG_RESULT要谨慎，只有在数据量过大的情况下使用，否则会造成性能损失。

group by 慢sql查询 怎么优化

group by 慢sql查询怎么优化