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