本文整理自實戰筆記,涵蓋 InnoDB/MyISAM 索引底層結構、索引設計原則、SQL 優化實戰技巧、EXPLAIN 執行計劃深度解讀、覆蓋索引應用等核心知識點,助你構建系統化的 MySQL 優化知識體系。
| 原則 | 說明 | 反例 |
|---|---|---|
| 業務無關自增主鍵 | 避免業務鍵導致頁分裂、主鍵變更連鎖更新二級索引 | UUID、業務編碼作主鍵 |
| 區分度優先 | 選擇性 = 唯一值/總行數,越接近 1 越好;基數極低(如性別)不建索引 | WHERE sex='M' 加索引反而全表掃描 |
| 最左前綴原則 | 聯合索引 (a,b,c) 支援 a / a,b / a,b,c 查詢 | WHERE b=1 AND c=2 用不到索引 |
| 覆蓋索引 | SELECT 列全在索引中,避免回表 | SELECT * 破壞覆蓋索引 |
| 拒絕 SELECT * | 讀取無用列、破壞覆蓋、增加網絡 IO | 習慣性 SELECT * |
| 字符串前綴匹配 | LIKE 'abc%' 走索引,LIKE '%abc' 全掃 | 模糊查詢加 % 在前 |
| 大批量導入 | 先導數據、後建索引、批量提交 | 邊導邊建索引、單條提交 |
| 排序字段加索引 | ORDER BY 高頻列加索引避免 Filesort | 大表 ORDER BY 無索引列 |
| 慎用 LIMIT offset,rows | 深分頁掃描 offset+rows 行,改用 ID 游標:WHERE id > last_id LIMIT rows | LIMIT 1000000,10 |
EXPLAIN SELECT id,author,date,title,status,name FROM wp_posts;
| 字段 | 值 | 解讀 |
|---|---|---|
| id | 1 | 單查詢無子查詢 |
| select_type | SIMPLE | 簡單查詢 |
| table | wp_posts | 訪問表 |
| type | ALL | 全表掃描! 無索引可用 |
| possible_keys | NULL | 無候選索引 |
| key | NULL | 未使用索引 |
| rows | 56 | 預估掃描 56 行 |
| Extra | NULL | 無額外優化信息 |
優化動作:根據 WHERE/JOIN/ORDER BY 列建立適當索引,將 type 從 ALL 提升至 ref/range/eq_ref。
system > const > eq_ref > ref > range > index > ALL
Using index:覆蓋索引,極佳Using where:Server 層過濾,正常Using temporary:需臨時表(GROUP BY/DISTINCT),優化空間大Using filesort:無法用索引排序,需優化定義:索引包含查詢所需所有列,EXPLAIN 顯示 Using index,避免回表讀取數據文件。
-- 優化前
SELECT * FROM orders WHERE user_id=100 AND status=1;
-- 優化後:建立聯合索引 (user_id, status, order_id, amount, create_time)
SELECT order_id, amount, create_time FROM orders WHERE user_id=100 AND status=1;
-- EXPLAIN 顯示 Using index,性能提升數量級
禁忌:覆蓋索引切勿 SELECT *,否則索引膨脹、查詢退化。
EXPLAIN 看 type、key、rows、Extra 四大金剛innodb_buffer_pool_size 佔物理內存 60-80%,innodb_flush_log_at_trx_commit=1 保安全優化無終點,唯有持續觀測、假設、驗證、沉澱,才能在 MySQL 性能調優之路上行穩致遠。