MySQL 進階優化技巧：參數調優、碎片整理、索引緩存與 I/O 優化

一、核心參數調優策略

二、InnoDB 專屬核心參數

三、表碎片整理與空間回收

-- 檢查碎片
SELECT TABLE_NAME, Data_free/1024/1024 AS Frag_MB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mydb' AND Data_free > 100*1024*1024;

-- 整理（InnoDB 重建表）
OPTIMIZE TABLE tbl_name;
-- 等效於
ALTER TABLE tbl_name ENGINE=InnoDB;

觸發場景：大量 DELETE/UPDATE 導致頁分裂、空洞；Data_free 超過表大小 10% 時建議整理。

四、慢查詢與索引優化

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

分析流程

1. mysqldumpslow -s t -t 20 slow.log 定位 Top SQL
2. EXPLAIN SELECT ... 檢查 type、key、rows、Extra
3. 補充索引：
   — WHERE/JOIN/ORDER BY 列
   — 聯合索引遵循最左前綴
   — 字符串列用前綴索引 idx_name(name(10))
4. 避免索引失效：無前導通配符、無隱式轉換、無函數包裹列

五、索引預熱：將熱點索引鎖入內存 (5.1+)

-- 1. 創建專用緩存池
SET GLOBAL hot_index_pool = 20*1024*1024;  -- 20 MB
-- my.cnf 永久：hot_index_pool=20M

-- 2. 綁定表索引到池
CACHE INDEX wp_posts IN hot_index_pool;

-- 3. 預熱載入
LOAD INDEX INTO CACHE wp_posts;

適用於：核心業務表、只讀報表表、啟動後需秒級響應的場景。

六、文件系統層面 I/O 優化

關閉 atime 更新

# /etc/fstab
/dev/sda6  /var  ext4  defaults,noatime  0  2

# 立即生效
mount -o remount,noatime /var

消除每次讀取觸發的 inode 寫入，SSD/HDD 均有收益。

七、查詢緩存狀態深度解讀 (5.7 參考)

SHOW STATUS LIKE 'Qcache%';

• Qcache_free_blocks：碎片塊數，FLUSH QUERY CACHE 整理
• Qcache_lowmem_prunes：因內存不足淘汰次數，持續增長 → 擴容或關閉
• Qcache_not_cached：不可緩存查詢（含 NOW()、RAND()、非 SELECT）
• 碎片率 = Qcache_free_blocks / Qcache_total_blocks > 20% 需整理
• 利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size 25%-80% 區間健康

八、調優執行清單

1. 建立基線：SHOW GLOBAL STATUS/VARIABLES 導出快照
2. 識別瓶頸：CPU/IO/鎖/網絡/內存
3. 單參數調整：修改一項，觀察 30 分鐘核心指標
4. 壓測驗證：sysbench oltp_read_write 模擬真實負載
5. 固化配置：寫入 /etc/my.cnf，滾動重啟生效