MySQL 開發核心必讀：存儲引擎抉擇、索引設計精要與批量寫入優化策略

存儲引擎：MyISAM 與 InnoDB 的深度抉擇

實戰避坑：MyISAM 高頻批量寫入易損壞

實測：並發批量 INSERT 達到一定量級時，MyISAM 表極易損壞，需 REPAIR TABLE 甚至數據丟失。核心業務嚴禁使用 MyISAM。

索引設計：從原理到落地的六大精要

1. 索引服務於查詢條件，而非 SELECT 列

WHERE、JOIN ON、ORDER BY、GROUP BY 後的列才需要索引。SELECT 列只需考慮覆蓋索引。

2. 高選擇性列優先，低選擇性拒絕單列索引

• 主鍵、手機、身份證、Email → 選擇性 ≈ 1.0 → 必建
• 性別、狀態、布爾、類型碼 → 選擇性 < 0.01 → 單列索引無效
• 複合索引把高選擇性列放最左：(user_id, status) ≫ (status, user_id)

3. 短索引：前綴長度覆蓋業務區分度即可

-- VARCHAR(100) 實際前 10 字符已區分 99%+
ALTER TABLE articles ADD INDEX idx_title_prefix (title(10));
-- 空間節省 90%，插入更快，緩存更多鍵值

4. 最左前綴原理：一個聯合索引頂多個單列索引

索引 (a, b, c) 等價於擁有：(a)、(a, b)、(a, b, c) 三個索引。
查詢 WHERE a=1 AND c=3 只能用到 (a) 部分，跳過 b 導致 c 失效。

5. 覆蓋索引：只讀索引不回表，性能提升數量級

-- 索引 (user_id, status, email)
SELECT user_id, status, email FROM users WHERE user_id = 100;
-- Extra: Using index（零回表）

6. 索引克制：每個索引都是寫入成本

• 單表 ≤ 5 個索引
• 定期清理：sys.schema_unused_indexes、pt-index-usage
• 低基數索引（性別、狀態）改用複合索引或不建

大批量插入：四大方案性能梯隊

LOAD DATA INFILE 生產級模板

-- 1. 目標表暫時無索引（或用 MyISAM）
CREATE TABLE users_staging LIKE users;
ALTER TABLE users_staging ENGINE=MyISAM;  -- 或 InnoDB 但無輔助索引

-- 2. 關閉檢查，調大緩衝
SET SESSION unique_checks=0, foreign_key_checks=0;
SET GLOBAL innodb_buffer_pool_size=4G;  -- 視機器調整
SET GLOBAL innodb_flush_log_at_trx_commit=2;

-- 3. 高速導入
LOAD DATA INFILE '/data/users.csv'
INTO TABLE users_staging
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, username, email, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');

-- 4. 建索引、切換表
ALTER TABLE users_staging ADD PRIMARY KEY (id), ADD INDEX idx_email (email);
RENAME TABLE users TO users_old, users_staging TO users;
DROP TABLE users_old;

索引結構實例解析

mysql> SHOW INDEX FROM user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user  | 0          | PRIMARY  | 1            | user_id     | A         | 2           | NULL     | NULL   |      | BTREE      |         |
| user  | 1          | user     | 1            | username    | A         | NULL        | NULL     | NULL   |      | BTREE      |         |
| user  | 1          | user     | 2            | `order`     | A         | NULL        | NULL     | NULL   |      | BTREE      |         |
| user  | 1          | user     | 3            | email       | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

解讀：

• PRIMARY：聚簇索引，user_id 遞增，Cardinality=2（樣本統計）
• user：聯合索引 (username, `order`, email)，Seq_in_index 1/2/3 體現最左前綴
• Sub_part=NULL：全列索引，非前綴索引
• Null=YES：email 允許 NULL

總結：開發者行動指南

1. 新表默認 ENGINE=InnoDB，主鍵 BIGINT UNSIGNED AUTO_INCREMENT
2. 索引按「查詢條件 → 高選擇性左 → 短前綴 → 覆蓋查詢」設計
3. 批量寫入：<100K 用多值 INSERT，>100K 用 LOAD DATA INFILE（無索引導入→建索引）
4. 上線前 EXPLAIN 核心查詢，消除 type=ALL、Using filesort
5. 定期：ANALYZE TABLE、清理未用索引、慢查詢分析