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

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

核心作用：

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

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

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

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

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

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

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

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

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

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

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

CREATE FULLTEXT INDEX idx_article ON articles(content);

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

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

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

三、啥時(shí)候該建索引？啥時(shí)候別建？

? 建議建索引的情況：

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

? 不建議建索引的情況：

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

四、復(fù)合索引：多個(gè)字段 “組隊(duì)” 加速查詢

1. 什么是復(fù)合索引？

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

CREATE INDEX idx_name_age ON users(name, age);

2. 最左前綴原則（必須掌握?。?/h3>

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

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

不支持：

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

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

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

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

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

-- 表結(jié)構(gòu)：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ù)或表達(dá)式：

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

類型不匹配：

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

模糊查詢以通配符開頭：

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

OR 條件分隔無關(guān)聯(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í)行計(jì)劃中的Key列

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

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

場景：

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

表結(jié)構(gòu)：

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)篩選，再按時(shí)間和金額過濾。
• order_time在索引中是有序的，查詢時(shí)直接按倒序取，不用額外排序。
• 查詢字段id, user_id, amount都在索引中（id是主鍵，默認(rèn)在索引里），實(shí)現(xiàn)覆蓋索引，不回表。

八、索引使用的核心原則（必記?。?/h2>

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

通過合理設(shè)計(jì)索引，MySQL 查詢性能能提升 10-100 倍！但記住：索引不是越多越好，要在 “查詢速度” 和 “寫入速度” 之間找平衡哦～

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

您可能感興趣的文章:

• MySQL數(shù)據(jù)庫給表添加索引的實(shí)現(xiàn)
• MySQL數(shù)據(jù)庫的索引原理與慢SQL優(yōu)化的5大原則
• 為什么Mysql?數(shù)據(jù)庫表中有索引還是查詢慢
• MySQL數(shù)據(jù)庫之索引詳解
• MySQL數(shù)據(jù)庫索引order?by排序精講
• MySQL數(shù)據(jù)庫索引的最左匹配原則
• MySQL中有哪些情況下數(shù)據(jù)庫索引會(huì)失效詳析
• Mysql數(shù)據(jù)庫之索引優(yōu)化
• mysql數(shù)據(jù)庫索引損壞及修復(fù)經(jīng)驗(yàn)分享
• MySQL 聯(lián)合索引與Where子句的優(yōu)化 提高數(shù)據(jù)庫運(yùn)行效率

最新評論