一次Group By+Order By性能优化分析

默认的临时表空间大小是16MB

mysql> show global variables like '%table_size';  
+---------------------+----------+  
| Variable_name       | Value    |  
+---------------------+----------+  
| max_heap_table_size | 16777216 |  
| tmp_table_size      | 16777216 |  
+---------------------+----------+  

https://dev.mysql.com/doc/ref...

https://dev.mysql.com/doc/ref...

max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

tmp_table_size

The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.

The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table. The internal_tmp_disk_storage_engine option defines the storage engine used for on-disk temporary tables.

也就是说这里临时表的限制是16M，max_heap_table_size大小也受tmp_table_size大小的限制。

所以我们这里调整为32MB，然后执行原始的SQL

set tmp_table_size=33554432;  
set max_heap_table_size=33554432;  

# Query_time: 5.910553  Lock_time: 0.000210 Rows_sent: 10  Rows_examined: 1337315  
SET timestamp=1552803869;  
select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  

方案3 使用 SQL_BIG_RESULT 优化

告诉优化器，查询结果比较多，临时表直接走磁盘存储。

# Query_time: 6.144315  Lock_time: 0.000183 Rows_sent: 10  Rows_examined: 2122417  
SET timestamp=1552802804;  
select SQL_BIG_RESULT aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;  

扫描行数是 2x满足条件的总行数（785102）+group by 之后的总行数（552203）+limit 的值。

顺便值得一提的是： 当我把数据量翻倍之后，使用该方式，查询时间基本没变。因为扫描的行数还是不变的。实际测试耗时6.197484

总结

方案1优化效果不稳定，当总表数据量与查询范围的总数相同时，且不超出内存临时表大小限制时，性能达到最佳。当查询数据量占据总表数据量越大，优化效果越不明显；

方案2需要调整临时表内存的大小，可行；不过当数据库超过32MB时，如果使用该方式，还需要继续提升临时表大小；

方案3直接声明使用磁盘来放临时表，虽然扫描行数多了一次符合条件的总行数的扫描。但是整体响应时间比方案2就慢了0.1秒。因为我们这里数据量比较，我觉得这个时间差还能接受。

所以最后对比，选择方案3比较合适。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!