MySQL PROCEDURE ANALYSE() 智能優化表結構實戰

函數簡介

PROCEDURE ANALYSE() 是 MySQL 內置分析工具，基於現有數據分佈，推薦最優字段類型與屬性。注意：MySQL 8.0 已移除，5.7 仍可用。

語法

SELECT * FROM table_name PROCEDURE ANALYSE([max_elements[, max_memory]]);

• max_elements：每列非重複值最大統計數，超過則不建議 ENUM（默認 256）
• max_memory：分析過程最大內存使用（默認 8192 字節）

實戰演示

CREATE TABLE t_demo (
    id INT,
    name VARCHAR(16),
    score INT
) ENGINE=InnoDB;

-- 插入測試數據
INSERT INTO t_demo VALUES 
(1, 'user001', 95),
(2, 'user002', 87),
(3, 'user003', 92),
(200000, 'user999', 78);

-- 執行分析
SELECT * FROM t_demo PROCEDURE ANALYSE(4);

輸出字段解讀

推薦結果示例

+---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+
| Field_name    | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std         | Optimal_fieldtype              |
+---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+
| test.t_demo.id     | 1         | 200000    | 1          | 6          | 0                | 0     | 100000.5000             | 116099.2790 | MEDIUMINT(6) UNSIGNED NOT NULL |
| test.t_demo.name   | user001   | user999   | 8          | 8          | 0                | 0     | 8.0000                  | NULL        | CHAR(8) NOT NULL               |
| test.t_demo.score  | 78        | 95        | 2          | 2          | 0                | 0     | 88.0000                 | 7.4833      | TINYINT(3) UNSIGNED NOT NULL   |
+---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+

優化建議解讀

1. id → MEDIUMINT(6) UNSIGNED NOT NULL：原 INT(11) 4 字節，優化為 3 字節，範圍 0-16777215 滿足 20 萬數據
2. name → CHAR(8) NOT NULL：固定長度 8 字符，VARCHAR(16) 有 1-2 字節長度前綴開銷，定長更緊湊
3. score → TINYINT(3) UNSIGNED NOT NULL：原 INT 4 字節，優化為 1 字節，範圍 0-255 覆蓋 0-100 分

應用建議

1. 數據充分後再分析：新建表無數據分析無意義，需業務運行一段時間
2. 結合業務語義判斷：推薦類型僅基於數值分佈，業務擴展性需人工評估（如 score 未來可能超過 255）
3. 避免盲目應用 ENUM：max_elements 限制防止過多枚舉值，但 ENUM 修改結構昂貴
4. NOT NULL 建議謹慎：業務確實無 NULL 才加，否則影響數據導入容錯

替代方案（MySQL 8.0+）

MySQL 8.0 移除 PROCEDURE ANALYSE()，可用：

• information_schema.COLUMNS 統計字段分佈自行分析
• 第三方工具：MySQL sys schema、Percona Toolkit pt-table-checksum/pt-online-schema-change
• 應用層收集數據分佈統計，定期評估結構優化