索引10連問，你能抗住第幾問？詳情 - 字段,索引優化,聯合索引,代碼人生程序員小2 博客

前言

今天我們來聊聊讓無數開發者又愛又恨的——數據庫索引。

相信不少小夥伴在工作中都遇到過這樣的場景：

明明已經加了索引，為什麼查詢還是慢？
為什麼有時候索引反而導致性能下降？
聯合索引到底該怎麼設計才合理？

別急，今天我就通過10個問題，帶你徹底搞懂索引的奧秘！

希望對你會有所幫助。

一、什麼是索引？為什麼需要索引？

1.1 索引的本質

簡單來説，索引就是數據的目錄。

就像一本書的目錄能幫你快速找到內容一樣，數據庫索引能幫你快速定位數據。

-- 沒有索引的查詢（全表掃描）
SELECT * FROM users WHERE name = '蘇三'; -- 需要遍歷所有記錄

-- 有索引的查詢（索引掃描）
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '蘇三'; -- 通過索引快速定位

1.2 索引的工作原理

索引10連問，你能抗住第幾問？_索引優化

索引的底層結構（B+樹）：

索引10連問，你能抗住第幾問？_聯合索引_02

二、索引的10個常見問題

1.為什麼我加了索引，查詢還是慢？

場景還原：

CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name LIKE '%蘇三%'; -- 還是很慢！

原因分析：

前導通配符：LIKE '%蘇三% 導致索引失效
索引選擇性差：如果name字段大量重複，索引效果不佳
回表代價高：索引覆蓋不全，需要回表查詢

解決方案：

-- 方案1：避免前導通配符
SELECT * FROM users WHERE name LIKE'蘇三%';

-- 方案2：使用覆蓋索引
CREATE INDEX idx_name_covering ON users(name, id, email);
SELECT name, id, email FROM users WHERE name LIKE'蘇三%'; -- 不需要回表

-- 方案3：使用全文索引（對於文本搜索）
CREATE FULLTEXT INDEX ft_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('蘇三');

2.索引是不是越多越好？

絕對不是！ 索引需要維護代價：

-- 每個索引都會影響寫性能
INSERT INTO users (name, email, age) VALUES ('蘇三', 'susan@example.com', 30);
-- 需要更新: 
-- 1. 主鍵索引
-- 2. idx_name索引（如果存在）
-- 3. idx_email索引（如果存在）
-- 4. idx_age索引（如果存在）

索引的代價：

存儲空間：每個索引都需要額外的磁盤空間
寫操作變慢：INSERT/UPDATE/DELETE需要維護所有索引
優化器負擔：索引太多會增加查詢優化器的選擇難度

黃金法則：一般建議表的索引數量不超過5-7個

3.聯合索引的最左前綴原則是什麼？

最左前綴原則：聯合索引只能從最左邊的列開始使用

-- 創建聯合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 能使用索引的查詢
SELECT * FROM users WHERE name = '蘇三'; -- √ 使用索引
SELECT * FROM users WHERE name = '蘇三'AND age = 30; -- √ 使用索引
SELECT * FROM users WHERE age = 30 AND name = '蘇三'; -- √ 優化器會調整順序

-- 不能使用索引的查詢
SELECT * FROM users WHERE age = 30; -- × 不符合最左前綴

聯合索引結構：

索引10連問，你能抗住第幾問？_字段_03

4.如何選擇索引字段的順序？

選擇原則：

高選擇性字段在前：選擇性高的字段能更快過濾數據
經常查詢的字段在前：優先滿足常用查詢場景
等值查詢在前，範圍查詢在後

-- 計算字段選擇性
SELECT 
    COUNT(DISTINCT name) / COUNT(*) as name_selectivity,
    COUNT(DISTINCT age) / COUNT(*) as age_selectivity,
    COUNT(DISTINCT city) / COUNT(*) as city_selectivity
FROM users;

-- 根據選擇性決定索引順序
CREATE INDEX idx_name_city_age ON users(name, city, age); -- name選擇性最高

5.什麼是覆蓋索引？為什麼重要？

覆蓋索引：索引包含了查詢需要的所有字段，不需要回表查詢

-- 不是覆蓋索引（需要回表）
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '蘇三'; -- 需要回表查詢其他字段

-- 覆蓋索引（不需要回表）
CREATE INDEX idx_name_covering ON users(name, email, age);
SELECT name, email, age FROM users WHERE name = '蘇三'; -- 所有字段都在索引中

覆蓋索引的優勢：

避免回表：減少磁盤IO
減少內存佔用：只需要讀取索引頁
提升性能：查詢速度更快

6.NULL值對索引有什麼影響？

NULL值的問題：

-- 創建索引
CREATE INDEX idx_email ON users(email);

-- 查詢NULL值
SELECT * FROM users WHERE email IS NULL; -- 可能不使用索引
SELECT * FROM users WHERE email IS NOT NULL; -- 可能不使用索引

NULL值可能不使用索引。

解決方案：

避免NULL值：設置默認值
使用函數索引（MySQL 8.0+）

-- 使用函數索引處理NULL值
CREATE INDEX idx_email_null ON users((COALESCE(email, '')));
SELECT * FROM users WHERE COALESCE(email, '') = '';

7.索引對排序和分組有什麼影響？

索引優化排序和分組：

-- 創建索引
CREATE INDEX idx_age_name ON users(age, name);

-- 索引優化排序
SELECT * FROM users ORDER BY age, name; -- √ 使用索引避免排序

-- 索引優化分組
SELECT age, COUNT(*) FROMusersGROUPBY age; -- √ 使用索引優化分組

-- 無法使用索引排序的情況
SELECT * FROM users ORDER BY name, age; -- × 不符合最左前綴
SELECT * FROM users ORDER BY age DESC, name ASC; -- × 排序方向不一致

8.如何發現索引失效的場景？

常見索引失效場景：

函數操作：WHERE YEAR(create_time) = 2023
類型轉換：WHERE phone = 13800138000（phone是varchar）
數學運算：WHERE age + 1 > 30
前導通配符：WHERE name LIKE '%蘇三'

使用EXPLAIN分析：

EXPLAIN SELECT * FROM users WHERE name = '蘇三';

-- 查看關鍵指標：
-- type: const|ref|range|index|ALL（性能從好到壞）
-- key: 實際使用的索引
-- rows: 預估掃描行數
-- Extra: Using index（覆蓋索引）| Using filesort（需要排序）| Using temporary（需要臨時表）

9.如何維護和優化索引？

定期索引維護：

-- 查看索引使用情況（MySQL）
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_database' AND table_name = 'users';

-- 重建索引（優化索引碎片）
ALTER TABLE users REBUILD INDEX idx_name;

-- 分析索引使用情況
ANALYZE TABLE users;

索引監控：

-- 開啓索引監控（Oracle）
ALTER INDEX idx_name MONITORING USAGE;

-- 查看索引使用情況
SELECT * FROM v$object_usage WHERE index_name = 'IDX_NAME';

10.不同數據庫的索引有什麼差異？

MySQL vs PostgreSQL索引差異：

特性	MySQL	PostgreSQL
索引類型	B+Tree, Hash, Fulltext	B+Tree, Hash, GiST, SP-GiST
覆蓋索引	支持	支持（使用INCLUDE）
函數索引	8.0+支持	支持
部分索引	支持	支持
索引組織表	聚簇索引	堆表

PostgreSQL示例：

-- 創建包含索引（Covering Index）
CREATE INDEX idx_users_covering ON users (name) INCLUDE (email, age);

-- 創建部分索引（Partial Index）
CREATE INDEX idx_active_users ON users (name) WHERE is_active = true;

-- 創建表達式索引（Expression Index）
CREATE INDEX idx_name_lower ON users (LOWER(name));

三、索引設計最佳實踐

3.1 索引設計原則

按需創建：只為經常查詢的字段創建索引
選擇合適類型：根據場景選擇B-Tree、Hash、全文索引等
考慮複合索引：使用複合索引減少索引數量
避免過度索引：每個索引都有維護成本
定期維護：重建索引，優化索引碎片

3.2 索引設計檢查清單

總結

理解原理：掌握B+樹索引的工作原理和特性。
合理設計：遵循最左前綴原則，選擇合適的索引順序。
避免失效：注意索引失效的常見場景。
覆蓋索引：儘可能使用覆蓋索引減少回表。
定期維護：監控索引使用情況，定期優化重建。
權衡利弊：索引不是越多越好，要權衡查詢性能和寫成本。

程序員小2 博客

程序員小2 博客

博客 / 詳情