一、核心結(jié)論（背下來就能用）

• 單列索引 + NOT IN / != / IS NOT NULL → 99% 索引失效
  • MySQL 優(yōu)化器會(huì)直接判定：全表掃描更快。
• 聯(lián)合索引（最左前綴）
  • 前面是等值匹配（=），后面跟 NOT IN / != / IS NOT NULL → 前面能命中索引，后面失效。
  • 也就是說：索引只能用到前面的等值字段，后面的范圍 / 否定條件用不上索引。
• 唯一索引 + 否定條件
  • 依然無法高效使用索引。

二、分場(chǎng)景詳細(xì)講（一看就懂）

場(chǎng)景 1：?jiǎn)瘟兴饕ㄗ畛Ｒ姡?/h3>

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

CREATE TABLE user (
    id INT PRIMARY KEY,
    age INT,
    name VARCHAR(50),
    INDEX idx_age(age)
);

這些 SQL 索引一定失效：

SELECT * FROM user WHERE age != 18;
SELECT * FROM user WHERE age NOT IN (18, 19, 20);
SELECT * FROM user WHERE age IS NOT NULL;

原因：
MySQL 優(yōu)化器認(rèn)為：否定條件意味著要查詢表中的大部分?jǐn)?shù)據(jù)（高選擇性差），直接進(jìn)行全表掃描比走索引回表再過濾更快。

場(chǎng)景 2：聯(lián)合索引（最容易踩坑）

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

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name_age(name, age) -- 聯(lián)合索引
);

SQL：

SELECT * FROM user
WHERE name = '張三'
  AND age != 18;

結(jié)果：
索引只用到 name（等值匹配），age 用不上！
執(zhí)行過程：

1. 先通過 name = '張三' 在索引 idx_name_age 中快速定位到所有相關(guān)記錄。
2. 然后在得到的結(jié)果集里，逐行判斷 age != 18（無法再利用索引進(jìn)行高效過濾）。

場(chǎng)景 3：什么時(shí)候 NOT IN / != 能用到索引？（極少）

只有一種情況：否定條件過濾后，剩下的數(shù)據(jù)極少極少。

示例：
假設(shè) status 字段只有 0 和 1，且表中 99% 的數(shù)據(jù)是 status=1。

SELECT * FROM order WHERE status != 1;

這種情況下，MySQL 可能會(huì)走索引（因?yàn)樾枰獟呙璧臄?shù)據(jù)量很小）。但這種數(shù)據(jù)分布在業(yè)務(wù)中幾乎遇不到！

三、最關(guān)鍵：IS NOT NULL 什么時(shí)候失效？

• 只要字段允許 NULL，IS NOT NULL 一定無法有效使用索引。

• WHERE email IS NOT NULL

• MySQL 認(rèn)為：幾乎所有行都滿足 IS NOT NULL → 全表掃描。
• 唯一例外： 字段絕大部分都是 NULL，你查 IS NOT NULL 才會(huì)走索引。（現(xiàn)實(shí)中極少）

四、總結(jié)：你只要記住這 3 條鐵律

1. 單列索引 + NOT IN / != / IS NOT NULL → 索引必失效。
2. 聯(lián)合索引 + 前面等值，后面否定 → 只命中前面等值部分。
3. 否定條件天生不適合索引（MySQL 優(yōu)化器不喜歡）。

五、實(shí)戰(zhàn)優(yōu)化方案（遇到 NOT IN 怎么改？）

1. 盡量改成范圍查詢> < >= <=

-- 原語句（索引失效）
SELECT * FROM user WHERE age NOT IN (18);
-- 優(yōu)化后（可能走 range 索引掃描）
SELECT * FROM user WHERE age > 18 OR age < 18;
-- 注意：OR 條件可能導(dǎo)致索引失效，需結(jié)合實(shí)際情況看執(zhí)行計(jì)劃

2. 盡量使用覆蓋索引

-- 原語句（全表掃描）
SELECT * FROM user WHERE age != 18;
-- 優(yōu)化后（可能走 index 索引掃描，比全表快）
SELECT age FROM user WHERE age != 18;
-- 因?yàn)?age 字段就在索引 idx_age 中，無需回表

3. 業(yè)務(wù)上避免使用 NOT IN

用 LEFT JOIN / NOT EXISTS 代替：

-- 查找沒有訂單的用戶
SELECT u.* FROM user u
LEFT JOIN order o ON u.id = o.user_id
WHERE o.id IS NULL;
-- 或
SELECT * FROM user u
WHERE NOT EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

用狀態(tài)字段代替（0/1）： 設(shè)計(jì)表時(shí)，對(duì)于是否、開關(guān)等字段，盡量使用 TINYINT 表示狀態(tài)，查詢時(shí)使用 status = 0 而非 status != 1。

六、OR 連接無索引字段：索引失效的隱形殺手

核心定義（一句話說清）

OR 連接無索引字段 = 一邊有索引，一邊沒索引 → 整條 SQL 索引直接失效！

MySQL 的規(guī)則很簡(jiǎn)單：只要 OR 兩邊任意一個(gè)字段沒有索引，整個(gè) WHERE 條件就無法使用索引，直接全表掃描。

舉個(gè)超級(jí)簡(jiǎn)單的例子（一看就明白）

假設(shè)你有一張用戶表：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20)
);

你只給 name 建了索引，phone 沒建索引：

CREATE INDEX idx_name ON user(name);  -- name 有索引
-- phone 無索引

現(xiàn)在你寫了這條 SQL：

SELECT * FROM user 
WHERE name = '張三' 
   OR phone = '13800138000';

結(jié)果：索引完全失效！
明明 name 有索引，但因?yàn)?OR 后面的 phone 沒有索引，MySQL 只能：
全表掃描每一行，判斷 name 或 phone 是否滿足條件

為什么 OR 連無索引字段會(huì)失效？（超簡(jiǎn)單原理）

• AND 的邏輯：先查有索引的字段 → 縮小范圍 → 再查無索引字段（沒問題）
• OR 的邏輯：兩個(gè)條件任意一個(gè)滿足就算 → 你必須把整張表都掃一遍，才能確保不漏數(shù)據(jù) → 所以索引直接廢掉

3 種必失效場(chǎng)景（你寫 SQL 時(shí)對(duì)照看）

場(chǎng)景 1：一邊有索引，一邊無索引（最常見）

WHERE 有索引字段 = '值' OR 無索引字段 = '值'
-- 結(jié)果：索引失效

場(chǎng)景 2：兩邊都無索引

WHERE 無索引字段1 = '值' OR 無索引字段2 = '值'
-- 結(jié)果：本來就沒索引，全表掃描

場(chǎng)景 3：聯(lián)合索引不滿足 OR 條件

CREATE INDEX idx_a_b ON table(a, b);  -- 聯(lián)合索引
WHERE a = 1 OR c = 2
-- 結(jié)果：c 無索引 → 失效

什么時(shí)候 OR 不會(huì)失效？（唯一正確用法）

OR 兩邊的字段 都 有 獨(dú) 立 索 引！

例子：

CREATE INDEX idx_name ON user(name);
CREATE INDEX idx_phone ON user(phone);  -- 兩個(gè)字段都有獨(dú)立索引
SELECT * FROM user 
WHERE name = '張三' 
   OR phone = '13800138000';

? 這種情況 MySQL 會(huì)使用：index_merge（索引合并）
兩邊分別走索引，再合并結(jié)果，不會(huì)失效。

終極總結(jié)（背會(huì)這 2 句就夠了）

1. OR 只要連接一個(gè)無索引字段 → 整條 SQL 索引直接失效，全表掃描！
2. OR 想要生效 → 兩邊字段必須都有獨(dú)立索引。

你以后寫 SQL 記住這個(gè)口訣：

AND 可以混，OR 必須全！

• AND：一個(gè)有索引就行
• OR：必須全部有索引，否則完蛋

六、一句話終極總結(jié)

只要 SQL 中出現(xiàn) NOT IN、!=、IS NOT NULL 這類否定條件，就默認(rèn)索引不會(huì)生效，除非數(shù)據(jù)分布極端特殊（否定后數(shù)據(jù)量極少）。在設(shè)計(jì)和優(yōu)化時(shí)，應(yīng)盡量避免直接使用這類條件。

到此這篇關(guān)于MySQL 索引失效場(chǎng)景分析之NOT IN、!=、IS NOT NULL 何時(shí)生效與失效的文章就介紹到這了,更多相關(guān)mysql索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論