快捷導(dǎo)航

一文揭秘MySQL索引失效原因與優(yōu)化方案

更新時間：2025年07月16日 09:56:52 作者：都叫我大帥哥

這篇文章主要為大家詳細介紹了MySQL中出現(xiàn)索引失效的原因以及相關(guān)優(yōu)化方案,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下

一、索引：數(shù)據(jù)庫世界的超級目錄

索引如同圖書館的圖書目錄：

聚簇索引：書架按編號排序（數(shù)據(jù)即索引）
二級索引：獨立目錄卡片（需回表查詢）
B+樹結(jié)構(gòu)：多叉平衡樹，3層可存2000萬數(shù)據(jù)（假設(shè)每頁16KB）

// Java中創(chuàng)建索引示例（Spring Data JPA）
@Entity
@Table(indexes = @Index(columnList = "username,email", name = "idx_user_identity"))
public class User {
    @Id
    private Long id;
    private String username; // 索引列
    private String email;    // 索引列
    private Integer age;
    // Getter/Setter省略
}

二、索引罷工的五大罪狀（失效場景）

1.最左匹配原則

-- 創(chuàng)建聯(lián)合索引
CREATE INDEX idx_soldier ON army(squad, team, soldier);

-- 有效查詢 ?
SELECT * FROM army WHERE squad = 'A'; 
SELECT * FROM army WHERE squad = 'A' AND team = 2;

-- 索引罷工 ?
SELECT * FROM army WHERE team = 2;        -- 跳過squad
SELECT * FROM army WHERE soldier = 'Tom'; -- 跳過頭兩列

原理：聯(lián)合索引如電話簿，必須先按省→市→姓名查找，跳級查詢無效

2.隱式轉(zhuǎn)換

// Java代碼中常見的類型錯誤
@Query("SELECT u FROM User u WHERE u.username = :name") // username是varchar
User findByUsername(@Param("name") Integer name); // 傳入Integer類型！

執(zhí)行SQL：

SELECT * FROM user WHERE username = 100; 
-- 類型轉(zhuǎn)換導(dǎo)致：username列索引失效！

原理：MySQL被迫對索引列做類型轉(zhuǎn)換（CAST），如同要求目錄同時支持字母和數(shù)字排序

3.函數(shù)計算

-- 生日字段有索引
SELECT * FROM user WHERE YEAR(birthday) = 1990;  -- 索引失效 ?

-- 優(yōu)化方案 ?
SELECT * FROM user 
WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

血淚案例：某電商平臺因DATE(create_time)查詢導(dǎo)致CPU飆升90%

4.范圍查詢阻斷連鎖反應(yīng)

CREATE INDEX idx_sales ON orders(region, amount, product);

-- 索引僅用到 region 和 amount ?
SELECT * FROM orders 
WHERE region = 'East' 
  AND amount > 1000 
  AND product = 'Phone';

破解方案：調(diào)整索引順序為(region, product, amount)

5.OR引發(fā)的危機

-- 即使name和age都有獨立索引
SELECT * FROM user WHERE name = 'John' OR age = 30;
-- MySQL通常選擇全表掃描！

優(yōu)化方案：改用UNION

SELECT * FROM user WHERE name = 'John'
UNION ALL
SELECT * FROM user WHERE age = 30;

三、原理深潛：B+樹為何罷工

當發(fā)生索引失效時：

優(yōu)化器計算使用索引的成本
若預(yù)計掃描超過30%數(shù)據(jù)（默認閾值）
選擇全表掃描作為“更優(yōu)方案”

冷知識：FORCE INDEX可強制使用索引，但如同用槍逼工人干活，慎用！

四、避坑指南：四大生存法則

前綴索引策略

ALTER TABLE article ADD INDEX idx_title(title(10));

對長文本取前N個字符（需保證區(qū)分度>90%）

覆蓋索引護盾

-- 建立覆蓋索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查詢只需索引列
SELECT user_id, status FROM orders WHERE user_id = 1001;

索引下推（ICP）

MySQL 5.6+ 黑科技：

在存儲引擎層提前過濾數(shù)據(jù)

索引散兵清理

-- 每月檢查無用索引
SELECT * FROM sys.schema_unused_indexes;

五、最佳實踐：索引優(yōu)化軍規(guī)

場景	錯誤做法	正確方案
分頁查詢	LIMIT 1000000,10	WHERE id > last_id LIMIT
狀態(tài)字段索引	建在gender列	用枚舉值或放棄索引
JSON字段查詢	WHERE json->'$.id'=10	生成列+索引
模糊查詢	LIKE '%關(guān)鍵字%'	全文索引或ES

// 分頁優(yōu)化Java實現(xiàn)
public Page<User> getUsers(Long lastId, int limit) {
    String sql = "SELECT * FROM user WHERE id > ? ORDER BY id ASC LIMIT ?";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(), lastId, limit);
}

六、面試考點區(qū)

問題1：varchar字段傳int參數(shù)為何索引失效？

答：觸發(fā)隱式轉(zhuǎn)換→索引列計算→B+樹失效→全表掃描

問題2：如何判斷索引選擇性？

答：SELECT COUNT(DISTINCT col)/COUNT(*) FROM table

結(jié)果>0.2適合建索引

問題3：EXPLAIN中哪些信號危險？

type: ALL（核爆級）
Extra: Using filesort（排序災(zāi)難）
rows: 1000000（預(yù)估掃描行數(shù)）

七、終極總結(jié)：與索引和平共處原則

設(shè)計階段

優(yōu)先整數(shù)字段索引
聯(lián)合索引遵循ASC排序原則

開發(fā)階段

// MyBatis防類型事故
@Param("userId") Long userId // 而非Integer

運維階段

-- 每月執(zhí)行
ANALYZE TABLE orders; 
OPTIMIZE TABLE critical_data;

最后忠告：索引不是銀彈！200萬數(shù)據(jù)以下，精心設(shè)計的索引比分布式更有效；500萬以上，考慮分庫分表+索引的組合拳。

附錄：索引健康檢查清單

- [ ] 所有SQL都通過EXPLAIN驗證
- [ ] 聯(lián)合索引列順序符合查詢模式
- [ ] 避免在WHERE子句中使用函數(shù)
- [ ] 定期清理冗余索引（工具：pt-duplicate-key-checker）
- [ ] 為慢查詢設(shè)置監(jiān)控（>0.5秒報警）

到此這篇關(guān)于一文揭秘MySQL索引失效原因與優(yōu)化方案的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: