MySQL索引設計的藝術：你的表該建多少個索引才健康
作者：禿頭老W
作為程序員，你一定聽過這樣的矛盾：DBA總想刪索引提升寫性能，開發總想加索引優化查詢速度。一張表到底該建多少個索引？這個讓無數團隊頭疼的問題，今天我們用「空間換時間」的底層邏輯來破解。
作為程序員，你一定聽過這樣的矛盾：DBA總想刪索引提升寫性能，開發總想加索引優化查詢速度。一張表到底該建多少個索引？這個讓無數團隊頭疼的問題，今天我們用「空間換時間」的底層邏輯來破解。
1.索引的雙面性：天使與魔鬼的共生體
索引的價值證明
- 查詢速度提升10-100倍（B+樹時間復雜度O(log n)）
- 覆蓋索引避免回表（Extra: Using index）
- 排序優化（避免filesort）
看不見的成本賬單
- 寫操作代價：每個INSERT/UPDATE/DELETE需要更新所有相關索引
- 空間開銷：每個二級索引約占用表數據的20%-30%
- 內存壓力：InnoDB緩沖池需要緩存熱索引頁
- 維護成本：索引碎片、統計信息更新
2.索引數量的黃金分割線
危險警戒線
- ? 超過10個索引：寫入性能可能下降50%+
- ? 單個索引超過5個字段：聯合索引邊際效益銳減
- ? 重復索引：(a,b)與(a)同時存在
最佳實踐區間
- ? OLTP系統推薦3-5個精選索引
- ? 數據倉庫可放寬至7-10個
- ? 每個索引不超過3個字段
3.索引設計的六大軍規
高頻查詢優先法則
-- 查詢頻率統計示例
SELECT query_pattern, COUNT(*)
FROM slow_query_log
WHERE table_name='orders'
GROUP BY query_pattern
ORDER BY COUNT(*) DESC LIMIT 5;聯合索引左前綴原則
- 正確案例：WHERE a=1 AND b>2 ORDER BY c → INDEX(a,b,c)
- 錯誤案例：WHERE b=2 AND c=3 → 無法命中上述索引
區分度計算公式
# 字段區分度評估
selectivity = COUNT(DISTINCT column)/COUNT(*)
# 值>30%適合單獨建索引熱點數據隔離策略
- 大字段單獨存儲（如JSON/text）
- 冷熱數據分離（按時間分表）
索引復用藝術
- 排序復用：WHERE a=? ORDER BY b → INDEX(a,b)
- 覆蓋查詢：SELECT a,b WHERE c=? → INDEX(c,a,b)
動態調整機制
- 季度索引健康檢查
- 使用ALTER TABLE ... ALGORITHM=INPLACE在線變更
4.實戰案例：電商訂單表索引優化
原始結構
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id INT,
product_id INT,
status TINYINT,
price DECIMAL(10,2),
created_at DATETIME,
INDEX idx_user (user_id),
INDEX idx_product (product_id),
INDEX idx_status (status),
INDEX idx_created (created_at)
);優化方案
-- 刪除單列索引
DROP INDEX idx_user, idx_product, idx_status, idx_created;
-- 創建復合索引
ADD INDEX idx_main_query (user_id, status, created_at);
ADD INDEX idx_product_query (product_id, status);
ADD INDEX idx_time_cover (created_at, price);優化效果
- 索引數量從4→3
- 查詢性能提升20%
- 寫入速度提高40%
5.索引監控工具箱
索引利用率分析
SELECT OBJECT_NAME, INDEX_NAME, ROWS_READ
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA='your_db';冗余索引檢測
pt-duplicate-key-checker --user=root --password=xxx --database=your_db索引健康度檢查
SELECT TABLE_NAME, INDEX_NAME,
ROUND(STAT_VALUE*@@innodb_page_size/1024/1024,2) AS MB
FROM mysql.innodb_index_stats
WHERE stat_name='size';6.新時代的索引哲學
當遇到索引抉擇困境時，請記住
- 數據訪問模式決定索引形態（而不是表結構）
- 索引是活的有機體，需要隨業務進化
- 有時候不加索引才是最優解（如極低頻查詢）
最后送大家一個決策樹
是否需要排序？ → 是否高頻查詢？ → 字段區分度如何？
↓ ↓ ↓
建聯合索引 監控觀察 拒絕索引責任編輯：武曉燕
來源：
JAVA充電
