您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 表優化完整指南：參數調校、索引策略、架構優化實戰

編輯：MySQL 資料庫相關知識

核心配置參數 (my.cnf/[mysqld])

參數	建議值 (1GB 記憶體)	說明
key_buffer_size	128M	MyISAM 索引緩衝，比例 Key_reads/Key_read_requests < 1:100 為佳
query_cache_size	32M	查詢快取，觀察 Qcache_lowmem_prunes、Qcache_hits 調整
table_cache	256	表快取，Open_tables=table_cache 且 Opened_tables 持續增長時調大
sort_buffer_size	1M	排序緩衝
read_buffer_size	1M	順序讀取緩衝
join_buffer_size	512K	JOIN 緩衝
max_connections	120	最大連線數
thread_cache	32	執行緒快取，減少建立/銷毀開銷
thread_concurrency	CPU核心數 x 2	並發提示
wait_timeout / interactive_timeout	120	閒置斷線時間，防止連線洩漏
back_log	100	連線等待隊列
skip-innodb / skip-bdb	啟用	不需 InnoDB/BDB 時關閉省資源
skip-name-resolve	啟用	跳過 DNS 反解，加快連線
log_slow_queries	/path/slow_query.log	慢查詢日志，long_query_time=10
query_cache_type	1	啟用查詢快取

關鍵參數深度解析

key_buffer_size (僅對 MyISAM 生效)

狀態指標：Key_read_requests、Key_reads。比例 Key_reads / Key_read_requests 應 ≤ 1:100，理想 1:1000。
估算法：所有 MyISAM 表索引總大小為參考下限。

query_cache_size

Qcache_lowmem_prunes 大 → 快取不足，需調大。
Qcache_hits 大 → 重複查詢多，快取有效。
Qcache_free_blocks 大 → 碎片多，可考慮 FLUSH QUERY CACHE。

table_cache

狀態：Open_tables、Opened_tables。若 Open_tables = table_cache 且 Opened_tables 快速增長 → 調大。
過大可能導致「檔案描述符不足」，需配合 ulimit -n。

架構層面優化策略

1. 關閉不必要的二進制日志

若不需時間點恢復或主從複制，註解 log-bin 可減少 I/O。但失去災難恢復能力，需自行備份。

2. 啟用慢查詢日志

log_slow_queries、long_query_time=10、log-queries-not-using-indexes，定期分析優化。

3. 垂直分割 (Vertical Partitioning)

將低頻/大欄位 (如文章內容、最後登入時間) 拆至獨立表。
主表變小 → 快取命中率↑、掃描速度↑。
避免頻繁 JOIN 拆分表，否則效能反降。

4. 大量 DML 分批執行

while (1) {
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) break;
    usleep(50000);
}

5. 選擇適當資料型別

越小越好：TINYINT、SMALLINT、MEDIUMINT、DATE 替代 INT、DATETIME。
預留擴充空間，避免未來 ALTER TABLE 鎖表數小時。

6. 儲存引擎選型

MyISAM：讀多寫少、需 COUNT(*) 極快、全文索引。
InnoDB：寫多、需交易/行鎖/外鍵、高併發。

7. 固定長度表優勢

無 VARCHAR/TEXT/BLOB 的表為固定長度，MySQL 可直接計算行偏移，讀取更快。可透過垂直分割實現「核心表固定長、擴展表變長」。

實際案例分析

健康：key_buffer=384M，Key_reads/Key_read_requests = 1:10000。
警報：key_buffer=16M，比例 1:11，磁碟 I/O 極高。
table_cache 過大：512 但 Open_tables=103，浪費記憶體。
table_cache 過小：64，Opened_tables=22423 (6小時)，頻繁開表關表。

完整範本 (medium 系統擴充版)

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
myisam_sort_buffer_size = 1M
max_connections = 120
wait_timeout = 120
back_log = 100
read_buffer_size = 1M
thread_cache = 32
skip-innodb
skip-bdb
skip-name-resolve
join_buffer_size = 512K
query_cache_size = 32M
interactive_timeout = 120
long_query_time = 10
log_slow_queries = /usr/local/mysql4/logs/slow_query.log
query_cache_type = 1
thread_concurrency = 4
server-id = 1
# log-bin  # 視需求啟用

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

根據實際硬體 (CPU、記憶體、磁碟)、業務讀寫比例、資料量持續監控調整，方能發揮 MySQL 最大效能。