您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 臨時表機制與 dstat 監控插件開發實戰

編輯：MySQL 綜合教學

臨時表是 MySQL 執行複雜查詢（GROUP BY、ORDER BY、UNION、子查詢）時的關鍵暫存結構。若記憶體不足會自動轉為磁碟 MyISAM/InnoDB 表，嚴重拖慢效能。本文解析臨時表參數、狀態監控、優化策略，並提供可直接部署的 dstat Python 監控插件。

核心參數


SHOW GLOBAL VARIABLES LIKE '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |  -- MEMORY 表/臨時表記憶體上限 (16MB)
| tmp_table_size      | 16777216 |  -- 內部臨時表記憶體上限 (16MB)
+---------------------+----------+

實際上限 = MIN(tmp_table_size, max_heap_table_size)
超過上限 → 自動轉換為磁碟表（Created_tmp_disk_tables 計數 +1）

關鍵狀態變數


SHOW GLOBAL STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7     |  -- 磁碟臨時表累計
| Created_tmp_tables      | 90    |  -- 總臨時表累計 (含記憶體)
+-------------------------+-------+

磁碟臨時表比率 = Created_tmp_disk_tables / Created_tmp_tables，建議 < 10%。

Performance Schema 進階視圖 (MySQL 5.6+)


SELECT query, exec_count, memory_tmp_tables, disk_tmp_tables,
       avg_tmp_tables_per_query, tmp_tables_to_disk_pct
FROM sys.statements_with_temp_tables
ORDER BY tmp_tables_to_disk_pct DESC LIMIT 5;

可精準定位「哪條 SQL 產生最多磁碟臨時表」。

常見導致磁碟臨時表的 SQL 模式

模式	原因	優化方向
GROUP BY + ORDER BY 欄位不一致	需建立兩組排序結構	對齊 GROUP BY / ORDER BY 欄位順序
SELECT * / 大型 TEXT/BLOB	行大小超過記憶體表限制	只 SELECT 必要欄位，避免大欄位
UNION / DISTINCT	需去重暫存	改用 UNION ALL + 應用層去重
子查詢未優化	衍生表物化	MySQL 8.0+ CTE / JOIN 改寫

優化清單

調大 tmp_table_size / max_heap_table_size（如 64M~256M），視記憶體而定
加入覆蓋索引，避免「Using temporary; Using filesort」
拆分大查詢，減少單次臨時表大小
升級 MySQL 8.0，利用 CTE、Window Function 減少衍生表

dstat MySQL 臨時表監控插件 (Python)

將以下程式碼存為 /usr/share/dstat/dstat_mysql5_tmp.py，並確保環境變數 DSTAT_MYSQL_USER/PWD/HOST/DB 已設定。


### Author: linwaterbin@gmail.com
### UPDATE: 2014-2-24
### FUNCTION: analyze mysql temp table use
import os, MySQLdb

mysql_user = os.getenv('DSTAT_MYSQL_USER')
mysql_pwd  = os.getenv('DSTAT_MYSQL_PWD')
mysql_host = os.getenv('DSTAT_MYSQL_HOST')
mysql_db   = os.getenv('DSTAT_MYSQL_DB')

class dstat_plugin(dstat):
    """ Plugin for MySQL 5 Temp Table Usage. """
    def __init__(self):
        self.name = 'mysql5 tmp usage'
        self.nick = ('mem', 'disk', 'mem-disk-pct')
        self.vars = ('memory_tmp_tables', 'disk_tmp_tables', 'avg_mem_to_disk_pct')
        self.type = 's'
        self.width = 12
        self.scale = 50

    def check(self):
        global MySQLdb
        try:
            self.db = MySQLdb.connect(user=mysql_user, passwd=mysql_pwd,
                                      host=mysql_host, db=mysql_db,
                                      cursorclass=MySQLdb.cursors.DictCursor)
        except Exception as e:
            raise Exception('Cannot interface with MySQL server: ' + str(e))

    def extract(self):
        try:
            query = """
                SELECT SUM(memory_tmp_tables) AS memory_tmp_tables,
                       SUM(disk_tmp_tables) AS disk_tmp_tables,
                       AVG(tmp_tables_to_disk_pct) AS avg_mem_to_disk_pct
                FROM sys.statements_with_temp_tables;
            """
            cur = self.db.cursor()
            cur.execute(query)
            row = cur.fetchone()
            if row:
                self.val['memory_tmp_tables'] = row['memory_tmp_tables'] or 0
                self.val['disk_tmp_tables'] = row['disk_tmp_tables'] or 0
                self.val['avg_mem_to_disk_pct'] = row['avg_mem_to_disk_pct'] or 0
            else:
                for v in self.vars: self.val[v] = 0
        except Exception as e:
            for v in self.vars: self.val[v] = -1

啟用方式


export DSTAT_MYSQL_USER=monitor
export DSTAT_MYSQL_PWD=secret
export DSTAT_MYSQL_HOST=127.0.0.1
export DSTAT_MYSQL_DB=sys
dstat --mysql5-tmp-usage 10

輸出欄位：mem (記憶體臨時表累計)、disk (磁碟臨時表累計)、mem-disk-pct (磁碟比例平均值)。

總結：臨時表不可避免，但「磁碟臨時表」可透過參數調大、SQL 改寫、索引覆蓋大幅降低。配合 dstat 插件持續監控，將磁碟臨時表比率納入日常巡檢指標。