一、索引是什么？能干嘛？

類比理解：索引就像書的目錄。比如你想查《哈利波特》中 “伏地魔” 出現(xiàn)的頁數(shù)，不用逐頁翻書，直接看目錄找關鍵詞就行。數(shù)據(jù)庫里的索引就是幫你快速找到數(shù)據(jù)的 “目錄”。

核心作用：

• 提速查詢：把 “全表掃描”（逐行找數(shù)據(jù)）變成 “精準定位”，查詢速度從 “翻完整本書” 變成 “查目錄找頁碼”。
• 約束數(shù)據(jù)：比如主鍵索引能保證數(shù)據(jù)不重復（像身份證號唯一）。
• 加速排序：索引本身是有序的，排序時不用臨時整理數(shù)據(jù)。

二、索引的 4 種主要類型（附通俗例子）

1. BTree 索引（最常用的 “萬能索引”）

• 原理：像字典的拼音目錄，按順序排列（如 a-b-c...），支持范圍查詢（如查 “年齡> 18”）和精準查詢。

適用場景：90% 的場景都能用，比如：

CREATE INDEX idx_age ON users(age);  -- 給年齡字段建索引

• 類比：查字典時，知道 “張三” 的拼音，直接按字母順序翻到對應頁。

2. Hash 索引（“快速匹配器”）

• 原理：把數(shù)據(jù)變成 “哈希值”（類似快遞單號），查數(shù)據(jù)時直接 “對單號”，速度極快（O (1)）。
• 限制：只能精準匹配（如WHERE id=1），不能查范圍（如id>100），且僅內(nèi)存表（MEMORY 引擎）可用。
• 類比：快遞柜取件，輸入單號直接開門，無法 “找所有單號大于 100 的快遞”。

3. 全文索引（“文本搜索神器”）

• 原理：專門針對文章、評論等長文本，把關鍵詞拆分成 “詞條” 存儲（類似搜索引擎的關鍵詞索引）。
• 適用場景：查 “包含‘MySQL’的文章”，用MATCH AGAINST語句：

CREATE FULLTEXT INDEX idx_article ON articles(content);

• 注意：MySQL 5.7 + 優(yōu)化后性能更好，別用 LIKE '% 關鍵詞 %'（太慢）。

4. 空間索引（“地圖專用索引”）

• 原理：存儲地理坐標（如經(jīng)緯度），支持 “查找附近 5 公里的咖啡店” 這類查詢。
• 適用場景：外賣 APP 找附近商家、地圖軟件標地點。
• 限制：字段必須是 GEOMETRY 類型（如點、線、面），InnoDB 引擎從 5.7 開始支持。

三、啥時候該建索引？啥時候別建？

? 建議建索引的情況：

• 經(jīng)常用來查詢的字段：比如WHERE name='張三'中的 name 字段。
• 表關聯(lián)字段：多表 JOIN 時的關聯(lián)字段（如訂單表的 user_id 關聯(lián)用戶表）。
• 唯一性字段：主鍵（id）、郵箱（唯一不重復）。
• 頻繁排序的字段：如ORDER BY create_time，索引自帶順序，不用額外排序。

? 不建議建索引的情況：

• 數(shù)據(jù)重復率高的字段：比如 “性別”（只有男 / 女），建索引還不如直接全表掃描快。
• 頻繁更新的字段：比如 “在線狀態(tài)”，每次修改都要更新索引，影響性能。
• 小表數(shù)據(jù)：表只有 100 行數(shù)據(jù)，全表掃描比查索引更快（索引本身也占空間）。
• 不參與查詢的字段：建了索引也用不上，純屬浪費空間。

四、復合索引：多個字段 “組隊” 加速查詢

1. 什么是復合索引？

• 給多個字段一起建索引，比如(name, age)，相當于 “組合目錄”。
• 語法：

CREATE INDEX idx_name_age ON users(name, age);

2. 最左前綴原則（必須掌握！）

• 規(guī)則：查詢條件必須從左到右使用索引中的字段，不能跳過。
• 示例：索引是(name, age)，支持：

WHERE name='張三' AND age=18;  -- 正確，用全索引
WHERE name='張三';  -- 正確，用name部分

不支持：

WHERE age=18;  -- 錯誤，跳過了name，索引失效
WHERE name='張三' AND age=18 AND address='北京';  -- 正確，address不影響，前兩個字段用上索引

• 類比：索引像 “省 - 市 - 區(qū)” 的地址，你必須先指定 “省”，才能用索引快速定位，直接查 “區(qū)” 無法用索引。

五、索引優(yōu)化：讓查詢飛起來的技巧

1. 覆蓋索引：“不回表” 的高效查詢

• 定義：查詢的所有字段都在索引里，不用再回表查數(shù)據(jù)（類似查目錄時直接拿到所有需要的信息，不用翻書）。
• 示例：

-- 表結構：users(id, name, age)
CREATE INDEX idx_name_age ON users(name, age);  -- 索引包含name和age
SELECT name, age FROM users WHERE name='張三';  -- 直接從索引取數(shù)據(jù)，不用回表

2. 索引失效場景（避坑指南）

• 用了函數(shù)或表達式：

WHERE UPPER(name)='ZHANGSAN';  -- 對name做了大寫轉換，索引失效

類型不匹配：

WHERE id='123';  -- id是數(shù)字類型，傳字符串可能導致索引失效

模糊查詢以通配符開頭：

WHERE name LIKE '%張三';  -- 無法用索引（不知道從哪開始查）

OR 條件分隔無關聯(lián)字段：

WHERE id=1 OR name='張三';  -- 若id和name沒有共同索引，可能失效

3. 索引管理命令（常用）

創(chuàng)建索引：

CREATE INDEX idx_name ON users(name);  -- 普通索引
CREATE UNIQUE INDEX idx_email ON users(email);  -- 唯一索引

刪除索引：

DROP INDEX idx_name ON users;

查看索引：

SHOW INDEX FROM users;

分析查詢是否用索引：

EXPLAIN SELECT * FROM users WHERE name='張三';  -- 看執(zhí)行計劃中的Key列

六、不同引擎的索引差異（簡單了解）

七、實戰(zhàn)案例：電商訂單表索引優(yōu)化

場景：

查詢 “近 30 天內(nèi)，已支付（status=2）且金額> 1000 的訂單”，按時間倒序。

表結構：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  order_time DATETIME,
  status TINYINT,
  amount DECIMAL(10,2)
);

優(yōu)化方案：

創(chuàng)建聯(lián)合覆蓋索引：

CREATE INDEX idx_status_time_amount ON orders(status, order_time, amount);

查詢語句：

SELECT id, user_id, amount FROM orders 
WHERE status=2 AND order_time >= NOW() - INTERVAL 30 DAY AND amount > 1000
ORDER BY order_time DESC;

優(yōu)化原理：

• 聯(lián)合索引(status, order_time, amount)滿足最左前綴原則，先按狀態(tài)篩選，再按時間和金額過濾。
• order_time在索引中是有序的，查詢時直接按倒序取，不用額外排序。
• 查詢字段id, user_id, amount都在索引中（id是主鍵，默認在索引里），實現(xiàn)覆蓋索引，不回表。

八、索引使用的核心原則（必記！）

• 少而精：單表索引不超過 5 個，避免過度索引（每個索引都增加寫入開銷）。
• 聯(lián)合索引優(yōu)先：多個字段頻繁一起查詢時，建聯(lián)合索引比多個單列索引更高效。
• 覆蓋索引優(yōu)先：讓查詢字段盡量在索引中，減少 “回表” 操作。
• 定期維護：用ANALYZE TABLE更新索引統(tǒng)計信息，用EXPLAIN分析慢查詢是否用了索引。
• 避免坑點：不用函數(shù)處理字段、不寫SELECT *（只查需要的字段）、模糊查詢用LIKE '關鍵詞%'（別以通配符開頭）。

通過合理設計索引，MySQL 查詢性能能提升 10-100 倍！但記?。核饕皇窃蕉嘣胶?，要在 “查詢速度” 和 “寫入速度” 之間找平衡哦～

到此這篇關于一文掌握MySQL 數(shù)據(jù)庫索引詳解的文章就介紹到這了,更多相關mysql數(shù)據(jù)庫索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論