MySQL索引踩坑合集從入門到精通
更新時間：2025年11月13日 10:28:47 作者：程序員牧羊
本文詳細(xì)介紹了MySQL索引的使用,包括索引的類型、創(chuàng)建、使用、優(yōu)化技巧及最佳實踐,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧
MySQL索引完整教程：從入門到入土（附實戰(zhàn)踩坑指南）
“沒有索引的查詢就像在圖書館里不用目錄，一本本翻書找資料——等你找到，項目都上線了 ??”
一、索引是什么？為什么需要它？
1.1 什么是索引？
索引（Index）就像書籍的目錄，可以幫助數(shù)據(jù)庫快速定位到數(shù)據(jù)，而不需要全表掃描。
想象一下：
- 沒有索引：就像在一本1000頁的字典里，從第一頁開始逐頁查找"MySQL"這個詞
- 有索引：直接翻到"M"開頭的部分，瞬間找到
1.2 為什么需要索引？
讓我們看一個真實的場景：
-- 假設(shè)有一個用戶表，有1000萬條數(shù)據(jù) SELECT * FROM users WHERE phone = '13800138000';
沒有索引的情況：
- 需要掃描全表：1000萬行
- 時間復(fù)雜度：O(n)
- 執(zhí)行時間：可能幾秒甚至幾十秒
- 用戶體驗：?? “這頁面卡死了嗎？”
有索引的情況：
- 通過B+樹快速定位：幾層查找
- 時間復(fù)雜度：O(log n)
- 執(zhí)行時間：幾毫秒
- 用戶體驗：?? “秒開，真快！”
二、索引的類型
2.1 按數(shù)據(jù)結(jié)構(gòu)分類
1. B+樹索引（最常用）
B+樹索引是MySQL的默認(rèn)索引類型，適用于大部分場景。
特點：
- 所有數(shù)據(jù)都存儲在葉子節(jié)點
- 葉子節(jié)點之間用指針連接（便于范圍查詢）
- 樹的高度低，查詢效率高
[50]
/ \
[25] [75]
/ \ / \
[10][30] [60][80]2. Hash索引
特點：
- 等值查詢極快，O(1)時間復(fù)雜度
- 不支持范圍查詢
- 不支持排序
- 僅Memory存儲引擎支持
適用場景：
- 等值查詢頻繁
- 不需要范圍查詢
-- Memory引擎的Hash索引
CREATE TABLE user_hash (
id INT PRIMARY KEY,
username VARCHAR(50)
) ENGINE=MEMORY;
CREATE INDEX idx_username ON user_hash(username) USING HASH;
3. 全文索引（FULLTEXT）
特點：
- 用于全文搜索
- 支持中文分詞（MySQL 5.7+）
- 僅MyISAM和InnoDB支持
-- 創(chuàng)建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);
-- 使用全文索引搜索
SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);
2.2 按字段數(shù)量分類
1. 單列索引
-- 在單個列上創(chuàng)建索引 CREATE INDEX idx_username ON users(username);
2. 復(fù)合索引（聯(lián)合索引）
-- 在多個列上創(chuàng)建索引 CREATE INDEX idx_name_phone ON users(name, phone);
?? 踩坑點1：復(fù)合索引的順序很重要！
-- 假設(shè)有索引 idx_name_phone(name, phone) -- ? 可以使用索引 SELECT * FROM users WHERE name = '張三'; SELECT * FROM users WHERE name = '張三' AND phone = '13800138000'; -- ? 無法使用索引（違背最左前綴原則） SELECT * FROM users WHERE phone = '13800138000';
為什么會這樣？ 就像查字典，先按拼音首字母，再按第二個字母。你直接查第二個字母，目錄就幫不上忙了！
2.3 按唯一性分類
1. 普通索引
允許重復(fù)值，最常用。
CREATE INDEX idx_email ON users(email);
2. 唯一索引
不允許重復(fù)值，但允許NULL（可以有多個NULL）。
CREATE UNIQUE INDEX idx_phone ON users(phone);
3. 主鍵索引
特殊的唯一索引，不允許NULL，每個表只能有一個。
-- 創(chuàng)建表時自動創(chuàng)建
CREATE TABLE users (
id INT PRIMARY KEY, -- 主鍵索引
username VARCHAR(50)
);
三、索引的創(chuàng)建和使用
3.1 創(chuàng)建索引的幾種方式
方式1：創(chuàng)建表時創(chuàng)建
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
INDEX idx_username(username), -- 普通索引
UNIQUE INDEX idx_email(email), -- 唯一索引
INDEX idx_name_phone(username, phone) -- 復(fù)合索引
);
方式2：使用ALTER TABLE
ALTER TABLE users ADD INDEX idx_username(username); ALTER TABLE users ADD UNIQUE INDEX idx_email(email);
方式3：使用CREATE INDEX
CREATE INDEX idx_username ON users(username); CREATE UNIQUE INDEX idx_email ON users(email);
3.2 刪除索引
-- 方式1 DROP INDEX idx_username ON users; -- 方式2 ALTER TABLE users DROP INDEX idx_username;
3.3 查看索引
-- 查看表的索引 SHOW INDEX FROM users; -- 查看創(chuàng)建索引的SQL SHOW CREATE TABLE users;
四、工作中常見的踩坑點（血淚教訓(xùn)）
?? 踩坑點1：索引不是越多越好
錯誤示例：
-- 新手：每個字段都加索引，美其名曰"優(yōu)化"
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
age INT,
gender TINYINT,
city VARCHAR(50),
INDEX idx_username(username),
INDEX idx_email(email),
INDEX idx_phone(phone),
INDEX idx_age(age),
INDEX idx_gender(gender),
INDEX idx_city(city)
-- ... 還有10個字段，每個都加索引
);問題：
- 占用存儲空間：每個索引都需要額外存儲
- 降低寫性能：INSERT/UPDATE/DELETE需要維護(hù)所有索引
- 查詢優(yōu)化器可能選錯索引：MySQL會糾結(jié)用哪個索引
正確做法：
- 只為經(jīng)常用于查詢條件的字段創(chuàng)建索引
- 不要為數(shù)據(jù)分布均勻的字段創(chuàng)建索引（如性別、狀態(tài)）
?? 踩坑點2：字符串索引長度設(shè)置不當(dāng)
錯誤示例：
-- 為很長的文本字段創(chuàng)建完整索引 CREATE INDEX idx_content ON articles(content); -- content是TEXT類型，可能幾KB
問題：
- 索引文件巨大
- 查詢效率低
- 浪費存儲空間
正確做法：使用前綴索引
-- 只對前100個字符創(chuàng)建索引
CREATE INDEX idx_content ON articles(content(100));
-- 如何確定前綴長度？
-- 計算不同前綴長度的選擇性
SELECT
COUNT(DISTINCT LEFT(content, 10)) / COUNT(*) AS sel10,
COUNT(DISTINCT LEFT(content, 50)) / COUNT(*) AS sel50,
COUNT(DISTINCT LEFT(content, 100)) / COUNT(*) AS sel100
FROM articles;
-- 選擇性越接近1越好，但也要考慮索引大小?? 踩坑點3：在WHERE子句中對索引列使用函數(shù)
錯誤示例：
-- 有索引 idx_created_at(created_at) SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01'; -- ? 無法使用索引
問題：
- MySQL無法使用索引，因為需要對每行數(shù)據(jù)執(zhí)行函數(shù)
- 導(dǎo)致全表掃描
正確做法：
-- ? 使用范圍查詢 SELECT * FROM orders WHERE created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00'; -- ? 或者在函數(shù)計算列上創(chuàng)建索引（MySQL 5.7+） ALTER TABLE orders ADD INDEX idx_created_date((DATE(created_at))); SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
?? 踩坑點4：LIKE查詢使用不當(dāng)
錯誤示例：
-- 有索引 idx_username(username) SELECT * FROM users WHERE username LIKE '%admin%'; -- ? 無法使用索引 SELECT * FROM users WHERE username LIKE '%admin'; -- ? 無法使用索引
問題：
- 前導(dǎo)通配符
%導(dǎo)致無法使用索引 - 全表掃描，性能極差
正確做法：
-- ? 只有后綴通配符可以使用索引 SELECT * FROM users WHERE username LIKE 'admin%'; -- ? 可以使用索引 -- 如果必須使用前導(dǎo)通配符，考慮： -- 1. 使用全文索引 -- 2. 使用搜索引擎（Elasticsearch） -- 3. 反序存儲（如：將'admin'存儲為'nimda'，然后查詢'%nimda'變成'admin%'）
?? 踩坑點5：OR條件導(dǎo)致索引失效
錯誤示例：
-- 有索引 idx_username(username) 和 idx_email(email) SELECT * FROM users WHERE username = 'admin' OR email = 'admin@example.com'; -- ? 可能無法使用索引
問題：
- MySQL可能無法同時使用兩個索引
- 優(yōu)化器可能選擇全表掃描
正確做法：
-- ? 使用UNION SELECT * FROM users WHERE username = 'admin' UNION SELECT * FROM users WHERE email = 'admin@example.com'; -- ? 或者創(chuàng)建復(fù)合索引 CREATE INDEX idx_username_email ON users(username, email);
?? 踩坑點6：NULL值處理不當(dāng)
錯誤示例：
-- 有索引 idx_email(email)，但email字段允許NULL SELECT * FROM users WHERE email IS NULL; -- ?? 可能無法使用索引 SELECT * FROM users WHERE email IS NOT NULL; -- ?? 可能無法使用索引
問題：
- NULL值在索引中的處理比較復(fù)雜
- 可能導(dǎo)致索引使用效率低下
正確做法：
-- ? 盡量避免NULL，使用默認(rèn)值
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) NOT NULL DEFAULT '', -- 使用空字符串而不是NULL
INDEX idx_email(email)
);
-- ? 如果必須使用NULL，考慮覆蓋索引
CREATE INDEX idx_email_id ON users(email, id);
SELECT id FROM users WHERE email IS NULL; -- 可以使用覆蓋索引?? 踩坑點7：隱式類型轉(zhuǎn)換
錯誤示例：
-- phone字段是VARCHAR類型，有索引 idx_phone(phone) SELECT * FROM users WHERE phone = 13800138000; -- ? 類型不匹配，無法使用索引
問題：
- MySQL會進(jìn)行隱式類型轉(zhuǎn)換
- 導(dǎo)致無法使用索引
正確做法：
-- ? 確保類型匹配 SELECT * FROM users WHERE phone = '13800138000'; -- ? 使用字符串
?? 踩坑點8：ORDER BY和索引
錯誤示例：
-- 有索引 idx_username(username)，但沒有包含age SELECT * FROM users WHERE username = 'admin' ORDER BY age; -- ? 需要額外的排序操作（filesort）
問題：
- 如果ORDER BY的字段不在索引中，需要額外的排序
- 如果數(shù)據(jù)量大，排序會很慢
正確做法：
-- ? 創(chuàng)建包含ORDER BY字段的索引 CREATE INDEX idx_username_age ON users(username, age); SELECT * FROM users WHERE username = 'admin' ORDER BY age; -- ? 可以使用索引，避免filesort
五、索引優(yōu)化技巧
5.1 使用EXPLAIN分析查詢
EXPLAIN是優(yōu)化查詢的神器！
EXPLAIN SELECT * FROM users WHERE username = 'admin';
關(guān)鍵字段：
- type：訪問類型
ALL：全表掃描（最差）??index：全索引掃描range：范圍掃描ref：非唯一索引掃描const：常量查詢（最好）??
- key：使用的索引
- rows：掃描的行數(shù)（越少越好）
- Extra：額外信息
Using index：使用了覆蓋索引（很好）?Using filesort：需要額外排序（不好）??Using temporary：需要臨時表（很不好）??
5.2 覆蓋索引（Covering Index）
覆蓋索引：索引包含了查詢所需的所有字段
-- 普通索引 CREATE INDEX idx_username ON users(username); -- 查詢需要回表 SELECT id, username, email FROM users WHERE username = 'admin'; -- 1. 通過索引找到username = 'admin'的行 -- 2. 根據(jù)主鍵回表查詢email（額外IO） -- 覆蓋索引 CREATE INDEX idx_username_email ON users(username, email); -- 查詢不需要回表 SELECT username, email FROM users WHERE username = 'admin'; -- 1. 通過索引找到username = 'admin'的行 -- 2. 索引中已經(jīng)有email，直接返回（不需要回表）?
優(yōu)勢：
- 減少IO操作
- 提高查詢性能
- 特別是在InnoDB中，可以減少隨機(jī)IO
5.3 索引下推（Index Condition Pushdown，ICP）
MySQL 5.6+支持索引下推優(yōu)化
-- 有索引 idx_name_phone(name, phone) SELECT * FROM users WHERE name LIKE '張%' AND phone = '13800138000'; -- 沒有ICP（MySQL 5.6之前）： -- 1. 通過索引找到name LIKE '張%'的所有行 -- 2. 回表查詢 -- 3. 過濾phone = '13800138000' -- 有ICP（MySQL 5.6+）： -- 1. 通過索引找到name LIKE '張%'的所有行 -- 2. 在索引中直接過濾phone = '13800138000'（索引下推） -- 3. 只回表查詢匹配的行 -- 減少了回表次數(shù)！?
5.4 索引選擇性（Cardinality）
索引選擇性 = 不同值的數(shù)量 / 總行數(shù)
選擇性越高，索引效果越好。
-- 查看索引選擇性
SHOW INDEX FROM users;
-- 或者
SELECT
COUNT(DISTINCT username) / COUNT(*) AS username_sel,
COUNT(DISTINCT gender) / COUNT(*) AS gender_sel
FROM users;
-- username_sel接近1，適合創(chuàng)建索引
-- gender_sel接近0.5（只有男/女），不適合創(chuàng)建索引經(jīng)驗法則：
- 選擇性 > 0.1：適合創(chuàng)建索引
- 選擇性 < 0.1：不適合創(chuàng)建索引（如性別、狀態(tài)等）
5.5 索引合并（Index Merge）
MySQL可以將多個索引合并使用
-- 有索引 idx_username(username) 和 idx_email(email) SELECT * FROM users WHERE username = 'admin' OR email = 'admin@example.com'; -- MySQL可能使用索引合并： -- 1. 使用idx_username查找username = 'admin'的行 -- 2. 使用idx_email查找email = 'admin@example.com'的行 -- 3. 合并結(jié)果
但要注意：
- 索引合并的效率通常不如單個復(fù)合索引
- 如果經(jīng)常這樣查詢，考慮創(chuàng)建復(fù)合索引
六、索引設(shè)計最佳實踐
6.1 索引設(shè)計原則
- 最左前綴原則
- 復(fù)合索引要遵循最左前綴原則
- 將選擇性高的字段放在前面
- 避免冗余索引
-- ? 冗余：idx_username已經(jīng)包含在idx_username_email中 CREATE INDEX idx_username ON users(username); CREATE INDEX idx_username_email ON users(username, email); -- ? 正確：只需要復(fù)合索引 CREATE INDEX idx_username_email ON users(username, email);
- 考慮查詢模式
- 根據(jù)實際查詢場景設(shè)計索引
- 不要盲目創(chuàng)建索引
- 定期分析和優(yōu)化
-- 分析表，更新索引統(tǒng)計信息 ANALYZE TABLE users; -- 查看未使用的索引（MySQL 5.7+） SELECT * FROM sys.schema_unused_indexes;
6.2 索引命名規(guī)范
-- 推薦命名方式 CREATE INDEX idx_username ON users(username); -- 單列索引 CREATE INDEX idx_username_email ON users(username, email); -- 復(fù)合索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 唯一索引 CREATE INDEX idx_created_at ON orders(created_at); -- 時間索引
6.3 索引維護(hù)
-- 重建索引（InnoDB） ALTER TABLE users DROP INDEX idx_username; ALTER TABLE users ADD INDEX idx_username(username); -- 或者使用OPTIMIZE TABLE OPTIMIZE TABLE users;
總結(jié)
索引使用 checklist ?
- 為經(jīng)常用于WHERE條件的字段創(chuàng)建索引
- 為經(jīng)常用于ORDER BY的字段創(chuàng)建索引
- 遵循最左前綴原則設(shè)計復(fù)合索引
- 使用EXPLAIN分析查詢性能
- 避免在索引列上使用函數(shù)
- 避免前導(dǎo)通配符的LIKE查詢
- 定期分析和優(yōu)化索引
- 刪除未使用的索引
- 監(jiān)控索引使用情況
還是那句話
索引是一把雙刃劍：
- 用得好：查詢速度飛起，用戶體驗up up up ??
- 用得不好：寫性能下降，存儲空間浪費，還可能選錯索引 ??
記住：
- 不要過度索引：索引不是越多越好
- 根據(jù)實際場景設(shè)計：不要盲目創(chuàng)建索引
- 定期監(jiān)控和優(yōu)化：索引需要持續(xù)維護(hù)
- 使用EXPLAIN分析：不要憑感覺優(yōu)化
希望這篇文章能幫到你，避免在工作中踩坑。大家都踩過什么坑呢，歡迎留言討論！
參考資料：
到此這篇關(guān)于MySQL索引踩坑合集從入門到精通的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文主要給大家介紹了在Mysql數(shù)據(jù)庫雙機(jī)熱備其中的難點分析以及重要環(huán)節(jié)的經(jīng)驗心得，需要的朋友收藏分享下吧。2017-12-12
解決數(shù)據(jù)庫有數(shù)據(jù)但查詢出來的值為Null問題
這篇文章主要介紹了解決數(shù)據(jù)庫有數(shù)據(jù)但查詢出來的值為Null問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
本文主要介紹了MySQL索引頁結(jié)構(gòu)，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
完全同步復(fù)制（Fully Synchronous Replication）確保主庫上的事務(wù)只有在所有從庫都確認(rèn)接收并應(yīng)用后才會向客戶端返回成功響應(yīng),本文給大家介紹了MySQL實現(xiàn)完全同步復(fù)制的幾種方法,需要的朋友可以參考下2025-08-08
Mysql存儲過程學(xué)習(xí)筆記--建立簡單的存儲過程
我們常用的操作數(shù)據(jù)庫語言SQL語句在執(zhí)行的時候需要要先編譯，然后執(zhí)行，而存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程的名字并給定參數(shù)（如果該存儲過程帶有參數(shù)）來調(diào)用執(zhí)行它。2014-08-08
這篇文章主要介紹了win10上如何安裝mysql5.7.16（解壓縮版）的相關(guān)資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-12-12
Flume如何自定義Sink數(shù)據(jù)至MySQL
Flume是分布式日志收集系統(tǒng),通過自定義Sink,可實現(xiàn)將事件數(shù)據(jù)寫入MySQL,自定義Sink需繼承AbstractSink類和實現(xiàn)Configurable接口,通過process方法處理Channel數(shù)據(jù),適用于特定數(shù)據(jù)存儲需求2024-10-10
MySQL中的DELETE刪除數(shù)據(jù)及注意事項
MySQL的DELETE語句是數(shù)據(jù)庫操作中不可或缺的一部分,通過合理使用索引、批量刪除、避免全表刪除、使用TRUNCATE、使用ORDER BY和LIMIT以及優(yōu)化事務(wù),可以顯著提高DELETE語句的執(zhí)行效率,這篇文章介紹MySQL的DELETE刪除數(shù)據(jù)詳解,感興趣的朋友一起看看吧2025-11-11
這篇文章主要介紹了MySQL如何用分隔符分隔字符串，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
mysql 5.7.20\5.7.21 免安裝版安裝配置教程
這篇文章主要為大家詳細(xì)介紹了mysql5.7.20和mysql5.7.21免安裝版安裝配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-02-02