一次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较量吻合。

（编辑：湖南网）

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