Mysql

為什麼 MySQL 使用這麼多臨時表?

  • May 10, 2012

任何配置錯誤是否會導致 mysql..mysql 調諧器顯示創建太多臨時表

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk


table_open_cache = 125 tables
table_definition_cache = 256 tables

You have a total of 97 tables

You have 125 open tables.

Current table_cache hit rate is 3%

早期的臨時表是,of the 23725 temp tables 38% were created on disk但我從 16m 更改max_heaptmp_table200m,它降低到 30%..

engine myisam 
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M

max_connections = 800

另一個具有預設配置的系統正在顯示 of 23725 temp tables, 1% were created on disk

但是我嘗試在有這個問題的機器上更改為預設值,它仍然顯示Of 580 temp tables, 16% were created on disk

我正在使用帶有 48 GB 記憶體的 Ubuntu 11.4 64 位…有人可以提出解決方案嗎?

使用“group by”將數據庫引擎從 myisam 更改為表上的記憶體可以解決這個問題嗎?

根據正在執行的查詢,根據需要創建和刪除臨時表。您看到的數字是自上次啟動 MySQL 以來創建的臨時表的總和,而不是同時存在的數量。

當無法一次性計算查詢時,MySQL 使用臨時表。切換儲存引擎不會改變這一點。問題在於查詢,而不是配置。

增加該tmp_table_size值只會阻止其中一些寫入磁碟,它們仍將在記憶體中創建並填充數據。這些數據可能首先來自磁碟,儘管使用 48GB 的​​ RAM,您可能已經記憶體了相當多的數據。即使是記憶體的,由於這些臨時表中有 30% 大於 200MB,因此在 RAM 中複製這麼多數據仍然需要時間。

EXPLAIN您甚至可以在執行查詢之前使用語法確定它是否將使用臨時表。只需放在EXPLAIN您的查詢之前,它就會輸出一堆關於執行計劃和查詢效率的資訊,而無需實際執行它。

您可能會找到導致這些臨時表的查詢,因為它們很可能是慢查詢,因此最終會出現在您的慢查詢日誌中。

如果您需要幫助調整特定查詢,DBA.SE是一個不錯的選擇。

TL; 博士

調整您的查詢。

引用自:https://serverfault.com/questions/387838