Monty 談 MySQL 優化（三）：維護策略、SQL 優化原則、跨資料庫效能對比與啟動參數

十一、維護策略

• 大量更新變長表定期 OPTIMIZE TABLE
• 偶爾 myisamchk -a 更新鍵值分布統計（先關 MySQL）
• 碎片嚴重時複製檔案到新磁碟、清空原盤、複製回
• 遇問題用 myisamchk 或 CHECK TABLE 檢查
• mysqladmin -i10 processlist extended-status 監控狀態
• GUI 客戶端多視窗監控進程列表與狀態
• mysqladmin debug 獲取鎖定與效能資訊

十二、優化 SQL 原則

揚長用 SQL 做： 基於 WHERE 找行、JOIN 表、GROUP BY、ORDER BY、DISTINCT

避短不讓 SQL 做： 驗證資料(如日期)、當計算機

技巧

• 明智用鍵值：適合搜尋，不適合索引列插入/更新
• 保持第三範式，但需求速度時建總結表（冗餘可接受）
• 大表避免 GROUP BY，改查總結表
• UPDATE table SET count=count+1 WHERE key_column=const 極快
• 大表或許定期生成總結表優於即時維護
• 充分利用 INSERT 預設值

十三、不同 SQL 服務器速度對比（秒）

鍵值讀取 2,000,000 行：

插入 350,768 行：

測試環境：MySQL 配 8MB 快取，其他資料庫預設安裝。

十四、重要啟動參數

• back_log：大量新連線時調整
• thread_cache_size：大量新連線時調整
• key_buffer_size：索引頁池，可設很大
• bdb_cache_size：BDB 表記錄與鍵值快取
• table_cache：大量表與併發連線時調整
• delay_key_write：需快取所有鍵值寫入時設定
• log_slow_queries：找出耗時查詢
• max_heap_table_size：用於 GROUP BY
• sort_buffer：用於 ORDER BY/GROUP BY
• myisam_sort_buffer_size：用於 REPAIR TABLE
• join_buffer_size：無鍵聯結時使用

十五、優化表結構

• 對每列嘗試用最有效類型，PROCEDURE ANALYSE() 可協助
• 不存 NULL 列用 NOT NULL，索引列尤重要
• ISAM → MyISAM
• 可能時用固定表格式
• 不索引不用的東西
• 利用索引前綴查詢特性：有 INDEX(a,b) 時無需單獨 INDEX(a)
• 長 CHAR/VARCHAR 列只索引前綴節省空間：INDEX(hostname(10))
• 每表用最有效表格式
• 不同表同義列同定義同名