MySQL 作為主流關(guān)系型數(shù)據(jù)庫(kù)，索引是提升查詢(xún)性能的核心手段。多數(shù)開(kāi)發(fā)者僅會(huì)創(chuàng)建基礎(chǔ)索引，但對(duì)索引原理、創(chuàng)建原則、失效場(chǎng)景、慢查詢(xún)優(yōu)化理解不足，導(dǎo)致索引 “形同虛設(shè)”，甚至因索引設(shè)計(jì)不當(dāng)引發(fā)性能問(wèn)題（如索引冗余、寫(xiě)入變慢）。

本文從索引核心原理出發(fā)，講解索引類(lèi)型、創(chuàng)建原則、失效場(chǎng)景、慢查詢(xún)分析、分場(chǎng)景優(yōu)化方案，結(jié)合 SQL 示例與執(zhí)行計(jì)劃分析，幫你避開(kāi)索引坑，設(shè)計(jì)出高效索引，將慢查詢(xún)優(yōu)化為毫秒級(jí)響應(yīng)。

一、核心認(rèn)知：索引的價(jià)值與底層原理

1. 核心價(jià)值

• 加速查詢(xún)：將全表掃描（O (n)）轉(zhuǎn)化為索引有序查找（O (log n)），大幅減少數(shù)據(jù)掃描量；
• 優(yōu)化排序：索引本身是有序的，若查詢(xún)包含排序字段，可直接利用索引排序，避免額外排序操作；
• 優(yōu)化連接：多表聯(lián)查時(shí)，索引可加速關(guān)聯(lián)字段的匹配，減少表關(guān)聯(lián)時(shí)的掃描次數(shù)。

2. 底層原理（B + 樹(shù)索引）

MySQL 中最常用的索引類(lèi)型是 B + 樹(shù)索引（主鍵索引、二級(jí)索引均基于 B + 樹(shù)實(shí)現(xiàn)），其結(jié)構(gòu)特點(diǎn)決定了高效查詢(xún)：

• B + 樹(shù)是平衡多路查找樹(shù)，高度低（一般 3-4 層），無(wú)論數(shù)據(jù)量多大，單次查詢(xún)都只需 3-4 次磁盤(pán) IO；
• 葉子節(jié)點(diǎn)存儲(chǔ)完整數(shù)據(jù)（主鍵索引）或主鍵值（二級(jí)索引），葉子節(jié)點(diǎn)間用鏈表連接，支持范圍查詢(xún)；
• 非葉子節(jié)點(diǎn)僅存儲(chǔ)索引鍵，不存儲(chǔ)數(shù)據(jù)，減少磁盤(pán)占用，提升查詢(xún)效率。

3. 索引類(lèi)型與適用場(chǎng)景

（1）主鍵索引（聚簇索引，Primary Key）

• 特點(diǎn)：默認(rèn)自動(dòng)創(chuàng)建，唯一且非空，葉子節(jié)點(diǎn)存儲(chǔ)整行數(shù)據(jù)；
• 適用場(chǎng)景：按主鍵查詢(xún)（如SELECT * FROM user WHERE id = 123）；
• 注意：一張表僅能有一個(gè)主鍵索引，建議用自增 ID 作為主鍵（避免主鍵無(wú)序?qū)е?B + 樹(shù)分裂，影響性能）。

（2）二級(jí)索引（非聚簇索引，Secondary Index）

• 特點(diǎn)：基于非主鍵字段創(chuàng)建，葉子節(jié)點(diǎn)存儲(chǔ)主鍵值，查詢(xún)時(shí)需通過(guò)主鍵值回表查詢(xún)完整數(shù)據(jù)（回表操作）；
• 分類(lèi)：
• 唯一索引（Unique）：索引鍵唯一（允許空值），避免重復(fù)數(shù)據(jù)（如user_phone唯一索引）；
• 普通索引（Normal）：無(wú)唯一性約束，適用于高頻查詢(xún)字段（如user_name普通索引）；
• 聯(lián)合索引（Composite）：基于多個(gè)字段創(chuàng)建，遵循 “最左前綴原則”，適用于多字段查詢(xún)（如(order_no, create_time)聯(lián)合索引）。

（3）其他索引類(lèi)型

• 全文索引（Fulltext）：適用于文本字段（如content）的模糊查詢(xún)，不適合精確匹配；
• 哈希索引（Hash）：基于哈希表實(shí)現(xiàn)，僅支持等值查詢(xún)，不支持范圍查詢(xún)、排序，MySQL 中 InnoDB 不支持手動(dòng)創(chuàng)建哈希索引（僅自適應(yīng)使用）。

二、實(shí)戰(zhàn)：索引創(chuàng)建原則與最佳實(shí)踐

1. 核心創(chuàng)建原則（避免無(wú)效索引）

（1）高頻查詢(xún)字段優(yōu)先創(chuàng)建索引

• 優(yōu)先為WHERE條件、JOIN關(guān)聯(lián)字段、ORDER BY/GROUP BY字段創(chuàng)建索引；
• 示例：高頻查詢(xún)SELECT * FROM order WHERE order_no = 'OD20240520'，為order_no創(chuàng)建普通索引。

（2）聯(lián)合索引遵循 “最左前綴原則”

• 聯(lián)合索引(a, b, c)的有效查詢(xún)場(chǎng)景：a、a+b、a+b+c，無(wú)效場(chǎng)景：b、b+c、c；
• 設(shè)計(jì)技巧：將區(qū)分度高的字段放在前面（區(qū)分度 = 不重復(fù)值數(shù)量 / 總記錄數(shù)，如身份證號(hào)區(qū)分度高于性別）；
• 示例：查詢(xún)SELECT * FROM order WHERE user_id = 123 AND create_time BETWEEN '2024-05-01' AND '2024-05-31'，創(chuàng)建聯(lián)合索引(user_id, create_time)（user_id區(qū)分度更高）。

（3）避免創(chuàng)建冗余索引

• 冗余索引：索引 A 包含索引 B 的所有字段，且順序一致（如(a)與(a, b)，(a, b)與(a, b, c)）；
• 危害：增加寫(xiě)入成本（INSERT/UPDATE/DELETE 時(shí)需同步維護(hù)索引），占用磁盤(pán)空間；
• 示例：已創(chuàng)建聯(lián)合索引(user_id, create_time)，無(wú)需再創(chuàng)建user_id單獨(dú)索引。

（4）低頻查詢(xún)、低區(qū)分度字段不創(chuàng)建索引

• 低區(qū)分度字段（如性別、狀態(tài)，值僅 2-3 種）：索引無(wú)法有效過(guò)濾數(shù)據(jù)，查詢(xún)效率甚至低于全表掃描；
• 低頻查詢(xún)字段：索引維護(hù)成本高于查詢(xún)收益，得不償失。

（5）避免對(duì)大字段創(chuàng)建索引

• 大字段（如TEXT、VARCHAR(2000)）：索引占用磁盤(pán)空間大，查詢(xún)時(shí) IO 成本高；
• 替代方案：僅對(duì)大字段的前綴創(chuàng)建索引（如INDEX idx_content (content(32))），或用全文索引。

2. 分場(chǎng)景索引設(shè)計(jì)示例

（1）單表查詢(xún)優(yōu)化

（2）多表聯(lián)查優(yōu)化

• 場(chǎng)景：查詢(xún)用戶及其訂單列表（user與order表聯(lián)查）；
• 原始 SQL：

SELECT u.id, u.user_name, o.order_no, o.create_time
FROM user u
LEFT JOIN order o ON u.id = o.user_id
WHERE u.id = 123
ORDER BY o.create_time DESC;

• 索引設(shè)計(jì)：

1. user表主鍵索引（默認(rèn)存在，id為主鍵）；
2. order表創(chuàng)建聯(lián)合索引idx_user_create (user_id, create_time DESC)（關(guān)聯(lián)字段user_id在前，排序字段在后）；

• 優(yōu)化效果：聯(lián)查時(shí)通過(guò)user_id快速匹配訂單，同時(shí)利用索引排序，無(wú)需全表掃描與額外排序。

（3）分頁(yè)查詢(xún)優(yōu)化

• 慢查詢(xún)示例（大數(shù)據(jù)量分頁(yè)，如第 1000 頁(yè)）：

-- 慢查詢(xún)：LIMIT offset過(guò)大，需掃描前10000條數(shù)據(jù)，再取10條
SELECT * FROM order ORDER BY create_time DESC LIMIT 10000, 10;

• 優(yōu)化方案（利用索引定位起點(diǎn)，避免全表掃描）：

1. 創(chuàng)建索引idx_create_id (create_time DESC, id)；
2. 優(yōu)化 SQL：

   SELECT o.* FROM order o
   WHERE o.id < (SELECT id FROM order ORDER BY create_time DESC LIMIT 10000, 1)
   ORDER BY create_time DESC LIMIT 10;
   

• 原理：子查詢(xún)通過(guò)索引快速定位第 10000 條數(shù)據(jù)的id，主查詢(xún)通過(guò)id < 目標(biāo)值過(guò)濾，僅掃描 10 條數(shù)據(jù)，大幅提升效率。

三、關(guān)鍵：索引失效場(chǎng)景與避坑

索引創(chuàng)建后若使用不當(dāng)，會(huì)導(dǎo)致索引失效，觸發(fā)全表掃描，需重點(diǎn)規(guī)避以下場(chǎng)景：

1. 場(chǎng)景 1：索引字段使用函數(shù)或運(yùn)算

• 錯(cuò)誤示例：

SELECT * FROM user WHERE LEFT(user_name, 3) = '張'; -- 函數(shù)操作索引字段
SELECT * FROM order WHERE create_time + INTERVAL 1 DAY > NOW(); -- 運(yùn)算操作索引字段

• 原因：函數(shù) / 運(yùn)算會(huì)改變索引字段的值，MySQL 無(wú)法利用索引查找；
• 解決方案：改寫(xiě) SQL，將函數(shù) / 運(yùn)算移到右邊：

SELECT * FROM user WHERE user_name LIKE '張%'; -- 前綴匹配，索引有效
SELECT * FROM order WHERE create_time > NOW() - INTERVAL 1 DAY;

2. 場(chǎng)景 2：模糊查詢(xún)以 “%” 開(kāi)頭

• 錯(cuò)誤示例：

SELECT * FROM user WHERE user_name LIKE '%三'; -- %開(kāi)頭，索引失效

• 原因：%開(kāi)頭的模糊查詢(xún)無(wú)法利用 B + 樹(shù)索引的有序性，需全表掃描；
• 解決方案：1. 前綴匹配（LIKE '張%'，索引有效）；2. 用全文索引（適用于文本字段）。

3. 場(chǎng)景 3：索引字段存在隱式轉(zhuǎn)換

• 錯(cuò)誤示例（user_phone為 VARCHAR 類(lèi)型，傳入數(shù)字）：

SELECT * FROM user WHERE user_phone = 13800138000; -- 隱式轉(zhuǎn)換，索引失效

• 原因：MySQL 會(huì)將索引字段（VARCHAR）轉(zhuǎn)換為數(shù)字類(lèi)型（CAST(user_phone AS UNSIGNED)），導(dǎo)致索引失效；
• 解決方案：傳入?yún)?shù)與字段類(lèi)型一致，加引號(hào)：

SELECT * FROM user WHERE user_phone = '13800138000';

4. 場(chǎng)景 4：聯(lián)合索引不滿足最左前綴原則

• 錯(cuò)誤示例（聯(lián)合索引(user_id, create_time)）：

SELECT * FROM order WHERE create_time BETWEEN '2024-05-01' AND '2024-05-31'; -- 僅用第二個(gè)字段，索引失效

• 解決方案：補(bǔ)充最左前綴字段，或調(diào)整索引順序（若create_time查詢(xún)更頻繁）。

5. 場(chǎng)景 5：OR 連接非索引字段

• 錯(cuò)誤示例（user_name有索引，phone無(wú)索引）：

SELECT * FROM user WHERE user_name = '張三' OR phone = '13800138000'; -- 索引失效

• 原因：OR 連接的字段中存在無(wú)索引字段，MySQL 無(wú)法確定是否使用索引，會(huì)觸發(fā)全表掃描；
• 解決方案：為phone也創(chuàng)建索引，或改用 UNION 連接。

6. 場(chǎng)景 6：WHERE 條件恒成立 / 恒不成立

• 錯(cuò)誤示例：

SELECT * FROM user WHERE 1 = 1; -- 恒成立，索引失效，全表掃描
SELECT * FROM user WHERE id = 123 AND 1 = 0; -- 恒不成立，索引失效

• 解決方案：避免無(wú)效 WHERE 條件，動(dòng)態(tài) SQL 中移除恒成立 / 不成立條件。

四、慢查詢(xún)分析工具與流程

1. 開(kāi)啟慢查詢(xún)?nèi)罩荆ǘㄎ宦樵?xún)）

MySQL 默認(rèn)關(guān)閉慢查詢(xún)?nèi)罩?，需手?dòng)開(kāi)啟，記錄執(zhí)行時(shí)間超過(guò)閾值（默認(rèn) 1 秒）的 SQL：

-- 臨時(shí)開(kāi)啟（重啟MySQL失效）
SET GLOBAL slow_query_log = ON; -- 開(kāi)啟慢查詢(xún)?nèi)罩?
SET GLOBAL long_query_time = 1; -- 慢查詢(xún)閾值（單位：秒）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log'; -- 日志存儲(chǔ)路徑
-- 永久開(kāi)啟（修改my.cnf配置文件，重啟生效）
[mysqld]
slow_query_log = ON
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
log_queries_not_using_indexes = ON -- 記錄未使用索引的查詢(xún)

2. 分析慢查詢(xún)?nèi)罩荆‥XPLAIN 執(zhí)行計(jì)劃）

通過(guò)EXPLAIN關(guān)鍵字分析 SQL 執(zhí)行計(jì)劃，判斷索引是否生效、是否全表掃描：

（1）EXPLAIN 使用方法

在 SQL 前加EXPLAIN，示例：

EXPLAIN SELECT * FROM order WHERE user_id = 123 AND create_time BETWEEN '2024-05-01' AND '2024-05-31';

（2）核心字段解讀（判斷索引是否生效）

• type：查詢(xún)類(lèi)型，優(yōu)先級(jí)：system > const > eq_ref > ref > range > index > ALL；
• ALL：全表掃描（索引失效，需優(yōu)化）；
• range：范圍查詢(xún)（索引有效，如 BETWEEN、IN）；
• ref/eq_ref：等值查詢(xún)（索引有效）。
• key：實(shí)際使用的索引（若為 NULL，說(shuō)明未使用索引）；
• rows：MySQL 預(yù)估掃描的行數(shù)（行數(shù)越少，效率越高）；
• Extra：額外信息，需警惕以下內(nèi)容：
• Using filesort：需額外排序（未利用索引排序，需優(yōu)化）；
• Using temporary：創(chuàng)建臨時(shí)表（效率低，需優(yōu)化）；
• Using index：覆蓋索引（無(wú)需回表，效率高）；
• Using where; Using index：索引覆蓋且有過(guò)濾條件，最優(yōu)。

3. 慢查詢(xún)優(yōu)化流程

1. 開(kāi)啟慢查詢(xún)?nèi)罩?，收集慢查?xún) SQL；
2. 對(duì)慢查詢(xún) SQL 執(zhí)行EXPLAIN，分析執(zhí)行計(jì)劃，定位問(wèn)題（索引失效、全表掃描、額外排序等）；
3. 優(yōu)化索引（創(chuàng)建新索引、調(diào)整聯(lián)合索引順序、刪除冗余索引）；
4. 改寫(xiě) SQL（避免索引失效場(chǎng)景、優(yōu)化分頁(yè)邏輯）；
5. 驗(yàn)證優(yōu)化效果（重新執(zhí)行EXPLAIN，對(duì)比掃描行數(shù)、執(zhí)行時(shí)間）。

五、避坑指南

1. 坑點(diǎn) 1：索引越多越好

• 表現(xiàn)：為表中大部分字段創(chuàng)建索引，導(dǎo)致寫(xiě)入操作（INSERT/UPDATE/DELETE）變慢，磁盤(pán)空間占用過(guò)大；
• 原因：寫(xiě)入時(shí)需同步維護(hù)所有索引的 B + 樹(shù)結(jié)構(gòu)，索引越多，維護(hù)成本越高；
• 解決方案：僅為高頻查詢(xún)字段創(chuàng)建索引，定期刪除冗余、低效索引。

2. 坑點(diǎn) 2：主鍵用 UUID 而非自增 ID

• 表現(xiàn)：UUID 無(wú)序，插入數(shù)據(jù)時(shí)會(huì)導(dǎo)致 B + 樹(shù)頻繁分裂、調(diào)整，寫(xiě)入性能差；
• 解決方案：用自增 ID（INT/BIGINT）作為主鍵，保證主鍵有序，減少 B + 樹(shù)分裂；若需 UUID，可將 UUID 作為二級(jí)索引。

3. 坑點(diǎn) 3：忽略覆蓋索引（避免回表）

• 表現(xiàn)：查詢(xún)時(shí)使用二級(jí)索引，但需回表查詢(xún)完整數(shù)據(jù)，增加 IO 成本；
• 解決方案：創(chuàng)建覆蓋索引（索引包含查詢(xún)所需所有字段），示例：

-- 查詢(xún)字段：user_id、create_time、order_no
-- 覆蓋索引：idx_user_create_no (user_id, create_time, order_no)
SELECT user_id, create_time, order_no FROM order WHERE user_id = 123;

4. 坑點(diǎn) 4：索引字段允許 NULL 值

• 表現(xiàn)：NULL 值會(huì)影響索引的查詢(xún)效率，MySQL 對(duì) NULL 值的處理特殊，無(wú)法有效利用索引；
• 解決方案：索引字段設(shè)置為 NOT NULL，用默認(rèn)值（如空字符串、0）替代 NULL。

5. 坑點(diǎn) 5：未定期維護(hù)索引

• 表現(xiàn)：長(zhǎng)期寫(xiě)入、刪除數(shù)據(jù)后，索引出現(xiàn)碎片，查詢(xún)效率下降；
• 原因：頻繁刪除數(shù)據(jù)會(huì)導(dǎo)致 B + 樹(shù)出現(xiàn)空洞，碎片增多，掃描行數(shù)增加；
• 解決方案：定期執(zhí)行OPTIMIZE TABLE優(yōu)化表，整理索引碎片（適用于 InnoDB 引擎）。

六、終極總結(jié)：索引優(yōu)化的核心是 “精準(zhǔn)與平衡”

MySQL 索引優(yōu)化不是 “盲目創(chuàng)建索引”，而是在 “查詢(xún)性能” 與 “寫(xiě)入性能” 之間尋找平衡 —— 既要通過(guò)索引加速查詢(xún)，又要控制索引數(shù)量，降低寫(xiě)入維護(hù)成本。

核心原則總結(jié)：

1. 索引設(shè)計(jì)貼合業(yè)務(wù)：基于高頻查詢(xún)場(chǎng)景設(shè)計(jì)，避免無(wú)意義索引；
2. 規(guī)避失效場(chǎng)景：熟練掌握索引失效規(guī)則，改寫(xiě) SQL 避免觸發(fā)；
3. 善用工具：通過(guò)慢查詢(xún)?nèi)罩?、EXPLAIN 定位問(wèn)題，驗(yàn)證優(yōu)化效果；
4. 持續(xù)維護(hù)：定期清理冗余索引、優(yōu)化索引碎片，適配業(yè)務(wù)迭代。

記住：最好的索引不是 “最復(fù)雜的”，而是 “最貼合業(yè)務(wù)、最高效、維護(hù)成本最低的”。

MySQL 索引失效場(chǎng)景速查表

按常見(jiàn)失效場(chǎng)景分類(lèi)，含錯(cuò)誤 SQL 示例、核心失效原因、可落地解決方案，附關(guān)鍵備注，快速避坑、優(yōu)化 SQL。

補(bǔ)充：索引生效的「黃金規(guī)則」

1. 索引字段直接作為查詢(xún)條件，不做任何函數(shù) / 運(yùn)算 / 轉(zhuǎn)換；
2. 聯(lián)合索引遵循最左前綴原則，查詢(xún)條件包含從左到右的連續(xù)字段；
3. 模糊查詢(xún)僅前綴匹配（LIKE 'xxx%'）可命中索引；
4. 查詢(xún)參數(shù)與索引字段類(lèi)型嚴(yán)格一致，避免隱式轉(zhuǎn)換；
5. OR 連接的字段全部創(chuàng)建索引，否則整體失效。

快速排查技巧

1. 用EXPLAIN分析 SQL，type=ALL 表示全表掃描（索引失效）；
2. 關(guān)注Extra字段：Using filesort（額外排序）、Using temporary（臨時(shí)表）均需優(yōu)化；
3. 若key=NULL，說(shuō)明未使用任何索引，優(yōu)先檢查上述失效場(chǎng)景。

到此這篇關(guān)于MySQL 索引優(yōu)化實(shí)戰(zhàn)指南(從慢查詢(xún)到高性能)的文章就介紹到這了,更多相關(guān)mysql索引優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！