MySQL 內建 PROCEDURE ANALYSE() 可對查詢結果集進行統計分析，自動推薦每列的**最優數據類型**、**是否可 NOT NULL**、**ENUM 適用性**等優化建議。雖 MySQL 8.0 已標記棄用（改用 ANALYZE TABLE 統計信息），但在 5.7/舊版本快速審計表結構、發現過度設計類型（如 BIGINT 存狀態碼）仍極具價值。本文詳解語法、輸出解讀、實戰案例與替代方案。
語法與參數
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements, [max_memory]]);
-- 參數說明
max_elements (默認 256): 分析每列不重複值上限，超過則不建議 ENUM
max_memory (默認 8192): 分析過程最大內存分配(字節)
實戰演示
建立測試表
CREATE TABLE user_account (
USERID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
USERNAME VARCHAR(10) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
GROUPNAME VARCHAR(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO user_account (USERNAME, PASSWORD, GROUPNAME) VALUES
('admin', 'hash1', 'admins'),
('user1', 'hash2', 'users'),
('user2', 'hash3', 'users'),
('guest', 'hash4', 'guests');
執行分析
SELECT * FROM user_account PROCEDURE ANALYSE(16, 256)\G
輸出字段解讀
| 輸出字段 | 含義 | 決策參考 |
|---|
Field_name | 庫.表.列 | 定位對象 |
Min_value / Max_value | 樣本最小/最大值 | 判斷範圍、是否需 UNSIGNED |
Min_length / Max_length | 值字符串長度範圍 | 決定 VARCHAR/CHAR 長度 |
Empties_or_zeros | 空值/零值計數 | 決定 NOT NULL |
Nulls | NULL 值計數 | 決定 NOT NULL |
Avg_value_or_avg_length | 平均值/平均長度 | 評估類型大小 |
Std | 標準差 | 分布離散度 |
Optimal_fieldtype | 推薦最優類型 | 核心輸出，直接可用於 ALTER TABLE |
範例輸出解析
*************************** 1. row ***************************
Field_name: test.user_account.USERID
Min_value: 1
Max_value: 4
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2.5000
Std: 1.2909
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
→ 當前 INT(10) 過大，實際僅 4 行，建議縮小為 TINYINT UNSIGNED
*************************** 2. row ***************************
Field_name: test.user_account.USERNAME
Min_value: admin
Max_value: user2
Min_length: 4
Max_length: 5
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 4.5000
Std: 0.5773
Optimal_fieldtype: VARCHAR(5) NOT NULL
→ 當前 VARCHAR(10) 可縮為 VARCHAR(5)
*************************** 3. row ***************************
Field_name: test.user_account.GROUPNAME
Min_value: admins
Max_value: users
Min_length: 5
Max_length: 6
Empties_or_zeros: 0
Nulls: 1
Optimal_fieldtype: ENUM('admins','guests','users') NOT NULL
→ 僅 3 個不同值，且無 NULL（樣本中有 1 NULL 但建議 NOT NULL），極適合 ENUM
適用場景與局限
| 適用場景 | 不適用/局限 |
|---|
| 繼承舊系統，快速審計數百張表類型過度設計 | MySQL 8.0 棄用，未來版本移除 |
| 開發期樣本數據較全，輔助定型 | 樣本不全（如缺極值）會給出錯誤建議 |
| 發現 BIGINT 存狀態碼、VARCHAR(255) 存性別等明顯浪費 | 不分析索引、外鍵、分區等結構 |
| 配合 pt-online-schema-change 安全變更類型 | 大表執行慢、佔用內存、可能鎖表 |
MySQL 8.0 現代替代方案
1. ANALYZE TABLE 更新統計信息
ANALYZE TABLE user_account; -- 更新索引基數統計，優化器受益
2. INFORMATION_SCHEMA.COLUMNS + 統計查詢自建分析
SELECT
c.COLUMN_NAME, c.COLUMN_TYPE, c.IS_NULLABLE,
(SELECT COUNT(DISTINCT c.COLUMN_NAME) FROM user_account) AS distinct_cnt,
(SELECT MAX(CHAR_LENGTH(c.COLUMN_NAME)) FROM user_account) AS max_len,
(SELECT MIN(CHAR_LENGTH(c.COLUMN_NAME)) FROM user_account) AS min_len
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'test' AND c.TABLE_NAME = 'user_account';
3. 結合 performance_schema 觀察實際查詢模式
SELECT
DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%user_account%'
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;
安全變更類型檢查清單
- ✅ 完整備份或快照
- ✅ 驗證推薦類型能容納**未來增長**（預留 10x 空間）
- ✅ 檢查應用層代碼：ORM 實體類型、DTO 驗證、序列化邏輯
- ✅ 使用
pt-online-schema-change 或 gh-ost 在線變更（無鎖）
- ✅ 灰度發布：先測試環境、再預發布、最後生產
- ✅ 回滾預案：保留舊表結構腳本、數據導出腳本
一鍵批量審計腳本（5.7 適用）
#!/bin/bash
# analyse_all_tables.sh
DB="your_db"
mysql -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB'" | \
while read table; do
echo "=== $table ==="
mysql -e "SELECT * FROM \`$DB\`.\`$table\` PROCEDURE ANALYSE(100, 1024)\G" 2>/dev/null | \
grep -E 'Field_name|Optimal_fieldtype'
done
