MySQL索引選擇與失效場景全解析
更新時間：2025年06月19日 09:42:01 作者：北辰alk
在MySQL數(shù)據(jù)庫中,索引是一種重要的優(yōu)化手段,它能夠顯著提升查詢速度,尤其是在大數(shù)據(jù)量的表中,然而,如果不正確地使用,索引可能會失去作用,導致查詢性能下降,所以本文給大家介紹了MySQL索引選擇與失效場景全解析,需要的朋友可以參考下
一、適合建立索引的字段特征
1.1 高選擇性的字段
選擇性公式：
選擇性 = 不重復的值數(shù)量(DISTINCT) / 總記錄數(shù)
選擇性越接近1，索引效果越好
示例分析：
-- 查看字段選擇性 SELECT COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity, COUNT(DISTINCT phone)/COUNT(*) AS phone_selectivity FROM users;
1.2 常用查詢條件的字段
| 查詢類型 | 索引效果 | 示例 |
|---|---|---|
| WHERE條件 | ★★★ | WHERE user_id = 1001 |
| JOIN條件 | ★★★ | ON a.order_id = b.id |
| 排序字段 | ★★ | ORDER BY create_time DESC |
| 分組字段 | ★★ | GROUP BY department |
1.3 具體推薦場景
1.3.1 應當建索引的字段
- 主鍵和外鍵字段（自動創(chuàng)建）
- 高頻查詢的WHERE條件字段
- 多表JOIN的關聯(lián)字段
- 排序和分組字段（特別是組合排序）
- 區(qū)分度高的狀態(tài)字段（如訂單狀態(tài)）
1.3.2 數(shù)值類型優(yōu)先
-- 好的索引字段 ALTER TABLE products ADD INDEX idx_category_id (category_id); -- 整型 ALTER TABLE users ADD INDEX idx_phone (phone); -- 定長字符串 -- 較差的索引選擇 ALTER TABLE logs ADD INDEX idx_content (content(255)); -- 長文本前綴索引
二、索引失效的8大常見場景
2.1 違反最左前綴原則
聯(lián)合索引結構示例：
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
失效案例：
-- 有效使用索引 EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1; -- 部分失效（只用到了user_id） EXPLAIN SELECT * FROM orders WHERE user_id = 1001; -- 完全失效（跳過了user_id） EXPLAIN SELECT * FROM orders WHERE status = 1;
2.2 對索引列使用函數(shù)或運算
失效示例：
-- 索引失效 EXPLAIN SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; EXPLAIN SELECT * FROM products WHERE price + 100 > 500; -- 優(yōu)化方案 EXPLAIN SELECT * FROM users WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';
2.3 隱式類型轉換
常見陷阱：
-- phone字段是varchar類型 EXPLAIN SELECT * FROM users WHERE phone = 13800138000; -- 失效 EXPLAIN SELECT * FROM users WHERE phone = '13800138000'; -- 有效 -- 枚舉值比較 EXPLAIN SELECT * FROM orders WHERE status = '1'; -- 可能失效 EXPLAIN SELECT * FROM orders WHERE status = 1; -- 有效
2.4 使用不等于(!=或<>)
失效分析：
-- 索引失效 EXPLAIN SELECT * FROM users WHERE age != 30; -- 優(yōu)化方案（范圍查詢+union） EXPLAIN SELECT * FROM users WHERE age < 30 UNION ALL SELECT * FROM users WHERE age > 30;
2.5 LIKE以通配符開頭
對比示例：
-- 索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '%張%';
-- 索引有效（前綴匹配）
EXPLAIN SELECT * FROM users WHERE name LIKE '張%';
-- 特殊優(yōu)化方案（全文索引）
ALTER TABLE users ADD FULLTEXT INDEX ft_idx_name (name);
EXPLAIN SELECT * FROM users WHERE MATCH(name) AGAINST('張*' IN BOOLEAN MODE);
2.6 OR條件使用不當
失效場景：
-- 索引失效（其中一個條件無索引） EXPLAIN SELECT * FROM users WHERE user_id = 1001 OR register_ip = '192.168.1.1'; -- 優(yōu)化方案 EXPLAIN SELECT * FROM users WHERE user_id = 1001 UNION ALL SELECT * FROM users WHERE register_ip = '192.168.1.1' AND user_id != 1001;
2.7 數(shù)據(jù)分布不均勻
案例演示：
-- 當status=1占90%數(shù)據(jù)時 EXPLAIN SELECT * FROM orders WHERE status = 1; -- 可能全表掃描 -- 查看數(shù)據(jù)分布 SELECT status, COUNT(*) FROM orders GROUP BY status;
2.8 索引列參與IS NULL判斷
特殊情況：
-- MySQL 5.7+可以走索引 EXPLAIN SELECT * FROM users WHERE phone IS NULL; -- 通常需要結合其他條件 EXPLAIN SELECT * FROM users WHERE phone IS NULL AND create_time > '2023-01-01';
三、高級索引失效場景
3.1 索引合并導致的性能問題
-- 可能不如預期高效 EXPLAIN SELECT * FROM users WHERE username = 'admin' OR email = 'admin@example.com'; -- 優(yōu)化方案 CREATE INDEX idx_username_email ON users(username, email);
3.2 范圍查詢后的索引失效
-- 只有user_id和status能用索引，age失效 EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status > 0 AND age = 30; -- 優(yōu)化索引順序 ALTER TABLE orders ADD INDEX idx_user_age_status (user_id, age, status);
3.3 不同字符集比較
-- 不同字符集比較導致失效 EXPLAIN SELECT * FROM users u JOIN logs l ON u.username = l.operator WHERE u.charset = 'utf8mb4' AND l.charset = 'latin1';
四、索引使用最佳實踐
4.1 索引設計黃金法則
三星索引原則：
- 一星：WHERE條件列是索引前綴
- 二星：ORDER BY/GROUP BY列在索引中
- 三星：SELECT列被索引覆蓋
索引維護成本：
- 寫操作需要更新索引
- 每個表最佳索引數(shù)通常為3-5個
4.2 實戰(zhàn)案例解析
電商系統(tǒng)優(yōu)化：
-- 原始查詢 SELECT product_id, product_name, price FROM products WHERE category_id = 5 AND status = 1 AND stock > 0 ORDER BY sales_volume DESC LIMIT 20; -- 優(yōu)化方案 ALTER TABLE products ADD INDEX idx_cat_status_stock_sales (category_id, status, stock, sales_volume DESC);
4.3 監(jiān)控與調(diào)優(yōu)工具
索引使用分析：
-- 查看未使用的索引 SELECT * FROM sys.schema_unused_indexes; -- 索引統(tǒng)計信息 SHOW INDEX FROM products; -- 查詢性能分析 EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;
五、MySQL 8.0索引新特性
5.1 倒序索引
ALTER TABLE orders ADD INDEX idx_create_time (create_time DESC);
5.2 隱藏索引
-- 測試刪除索引的影響 ALTER TABLE users ALTER INDEX idx_email INVISIBLE; ALTER TABLE users ALTER INDEX idx_email VISIBLE;
5.3 函數(shù)索引
-- 對JSON字段建立索引 ALTER TABLE products ADD INDEX idx_price_data ((CAST(price_data->'$.price' AS DECIMAL(10,2))));
六、總結與決策流程圖
6.1 索引創(chuàng)建決策流程
6.2 索引失效排查清單
- 檢查EXPLAIN執(zhí)行計劃
- 驗證SQL是否遵循最左前綴原則
- 檢查是否有隱式類型轉換
- 排查是否使用了函數(shù)或運算
- 分析數(shù)據(jù)分布情況
- 確認字符集和排序規(guī)則一致性
通過系統(tǒng)性地理解索引適用場景和失效原理，可以顯著提升數(shù)據(jù)庫查詢性能。實際應用中應當結合業(yè)務特點和數(shù)據(jù)分布，定期審查索引效果，避免過度索引和索引濫用。
以上就是MySQL索引選擇與失效場景全解析的詳細內(nèi)容，更多關于MySQL索引選擇與失效的資料請關注腳本之家其它相關文章！
相關文章
hash分區(qū)的目的是將數(shù)據(jù)按照某列進行hash計算后更加均勻的分散到各個分區(qū),下面這篇文章主要給大家介紹了關于MySQL分區(qū)之HASH分區(qū)的相關資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2022-04-04
這篇文章主要介紹了mysql5.7中的密碼忘記如何找回問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-06-06
這篇文章主要介紹了MySQL中describe命令的使用方法小結,describe命令主要用于獲取表或列等的各種信息,需要的朋友可以參考下2015-12-12
詳解MySQL子查詢（嵌套查詢）、聯(lián)結表、組合查詢
這篇文章主要介紹了MySQL子查詢（嵌套查詢）、聯(lián)結表、組合查詢，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-03-03
文章介紹了MySQL 8的安裝步驟,包括配置忽略大小寫、備份數(shù)據(jù)、停止和刪除數(shù)據(jù)庫文件、配置my.cnf文件、初始化、啟動服務和登錄設置密碼,還討論了在配置遠程連接時遇到的常見問題,特別是MySQL 8版本中由于密碼加密方法變化導致的問題解決方法2024-11-11
最近線上頻繁的出現(xiàn)slave延時的情況，經(jīng)排查發(fā)現(xiàn)為用戶在刪除數(shù)據(jù)的時候，由于表主鍵的主鍵的缺少，同時刪除條件沒有索引，或或者刪除的條件過濾性極差，導致slave出現(xiàn)hang住2016-05-05
今天小編就為大家分享一篇關于Mysql5.7修改root密碼教程，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-02-02
在MySQL數(shù)據(jù)庫中,在不同的情形下,空值往往代表不同的含義,這是MySQL數(shù)據(jù)庫的一種特性,下面這篇文章主要給大家介紹了關于MySQL該如何判斷不為空的相關資料,需要的朋友可以參考下2023-02-02
本文主要介紹了基于mysql樂觀鎖實現(xiàn)秒殺，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-07-07
MySQL出現(xiàn)SQL Error (2013)連接錯誤的解決方法
這篇文章主要介紹了MySQL出現(xiàn)SQL Error (2013)連接錯誤的解決方法,2013錯誤主要還是在于用戶的授權問題,需要的朋友可以參考下2016-06-06