MySQL內部臨時表策略的示例分析

144次閱讀

共計 3264 個字符，預計需要花費 9 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 MySQL 內部臨時表策略的示例分析，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

MySQL 內部臨時表策略

通過對 MySQL 數據庫的跟蹤和調試，以及參考 MySQL 官方文檔，對 MySQL 內部臨時表使用策略進行整理，以便于更加深入的理解。
使用內部臨時表條件
MySQL 內部臨時表的使用有一定的策略，從源碼中關于 SQL 查詢是否需要內部臨時表。可以總結如下：
1、DISTINCT 查詢，但是簡單的 DISTINCT 查詢，比如對 primary key、unique key 等 DISTINCT 查詢時，查詢優化器會將 DISTINCT 條件優化，去除 DISTINCT 條件，也不會創建臨時表；
2、不是第一個表的字段使用 ORDER BY 或者 GROUP BY；
3、ORDER BY 和 GROUP BY 使用不同的順序；
4、用戶需要緩存結果； www.2cto.com
5、ROLLUP 查詢。

源碼如下所示
代碼地址：sql_select.cc:854，函數：JOIN::optimize()，位置：sql_select.cc:1399
/*
Check if we need to create a temporary table.
This has to be done if all tables are not already read (const tables)
and one of the following conditions holds:
– We are using DISTINCT (simple distinct s are already optimized away)
– We are using an ORDER BY or GROUP BY on fields not in the first table
– We are using different ORDER BY and GROUP BY orders
– The user wants us to buffer the result.
When the WITH ROLLUP modifier is present, we cannot skip temporary table
creation for the DISTINCT clause just because there are only const tables.
*/ www.2cto.com
need_tmp= ((const_tables != tables
((select_distinct || !simple_order || !simple_group) ||
(group_list order) ||
test(select_options OPTION_BUFFER_RESULT))) ||
(rollup.state != ROLLUP:: STATE_NONE select_distinct));

內部臨時表使用原則
但是使用了內部臨時表，那么他是怎么存儲的呢？原則是這樣的：
1、當查詢結果較小的情況下，使用 heap 存儲引擎進行存儲。也就是說在內存中存儲查詢結果。
2、當查詢結果較大的情況下，使用 myisam 存儲引擎進行存儲。
3、當查詢結果最初較小，但是不斷增大的情況下，將會有從 heap 存儲引擎轉化為 myisam 存儲引擎存儲查詢結果。

什么情況算是查詢結果較小呢？從源碼中 if 的幾個參數可以看出：
1、有 blob 字段的情況；
2、使用唯一限制的情況；
3、當前表定義為大表的情況；
4、查詢結果的選項為小結果集的情況；
5、查詢結果的選項為強制使用 myisam 的情況。
www.2cto.com
源碼如下所示
代碼地址：sql_select.cc:10229，函數：create_tmp_table()，位置：sql_select.cc:10557
/* If result table is small; use a heap */
/* future: storage engine selection can be made dynamic? */
if (blob_count || using_unique_constraint
|| (thd- variables .big_tables !( select_options SELECT_SMALL_RESULT))
|| (select_options TMP_TABLE_FORCE_MYISAM))
{
share- db_plugin = ha_lock_engine(0, myisam_hton);
table- file = get_new_handler(share, table – mem_root,
share- db_type ());
if (group
(param- group_parts table- file- max_key_parts () ||
param- group_length table- file- max_key_length ()))
using_unique_constraint=1;
}
else
{
share- db_plugin = ha_lock_engine(0, heap_hton);
table- file = get_new_handler(share, table – mem_root,
share- db_type ());
}
www.2cto.com
代碼地址：sql_select.cc:11224，函數：create_myisam_from_heap()，位置：sql_select.cc:11287
/*
copy all old rows from heap table to MyISAM table
This is the only code that uses record[1] to read/write but this
is safe as this is a temporary MyISAM table without timestamp/autoincrement
or partitioning.
*/
while (! table- file – rnd_next( new_table.record [1]))
{
write_err= new_table .file- ha_write_row(new_table .record[1]);
DBUG_EXECUTE_IF(raise_error , write_err= HA_ERR_FOUND_DUPP_KEY ;);
if (write_err)
goto err ;
}
官方文檔相關內容
以上內容只是源碼表面的問題，通過查詢 MySQL 的官方文檔，得到了更為權威的官方信息。
臨時表創建的條件：
1、如果 order by 條件和 group by 的條件不一樣，或者 order by 或 group by 的不是 join 隊列中的第一個表的字段。
2、DISTINCT 聯合 order by 條件的查詢。
3、如果使用了 SQL_SMALL_RESULT 選項，MySQL 使用 memory 臨時表，否則，查詢詢結果需要存儲到磁盤。
臨時表不使用內存表的原則：
1、表中有 BLOB 或 TEXT 類型。
2、group by 或 distinct 條件中的字段大于 512 個字節。
3、如果使用了 UNION 或 UNION ALL，任何查詢列表中的字段大于 512 個字節。
此外，使用內存表最大為 tmp_table_size 和 max_heap_table_size 的最小值。如果超過該值，轉化為 myisam 存儲引擎存儲到磁盤。