在 MySQL 中，字段包含 NULL 值本身不會(huì)直接導(dǎo)致索引失效，但 查詢(xún)條件中涉及 NULL 的操作可能影響索引的使用，具體取決于數(shù)據(jù)分布和優(yōu)化器的選擇。

以下是詳細(xì)分析：

一、索引對(duì) NULL 值的處理機(jī)制

索引存儲(chǔ) NULL 值

• 如果字段允許為 NULL 且有索引，NULL 值會(huì)被記錄在索引中。
• InnoDB 的 B+Tree 索引將 NULL 視為一個(gè)特殊值，與其他非 NULL 值共存于索引結(jié)構(gòu)中。

唯一索引的例外

• 對(duì)于唯一索引（UNIQUE），MySQL 允許插入多個(gè) NULL 值，因?yàn)?NULL 被視為“未知值”，彼此不沖突。
• 例如，唯一索引 UNIQUE(email) 允許多行 email 為 NULL。

二、查詢(xún)條件中涉及 NULL 的場(chǎng)景

1. IS NULL 或 IS NOT NULL

是否使用索引：

取決于 NULL 值的分布比例。優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息（如索引基數(shù)）決定是否使用索引。

• 高 NULL 比例：若某列大部分值為 NULL，優(yōu)化器可能認(rèn)為全表掃描比索引掃描更快，從而放棄索引。
• 低 NULL 比例：若 NULL 值較少，優(yōu)化器可能選擇通過(guò)索引定位數(shù)據(jù)。

示例：

-- 假設(shè) `address` 列有索引且 90% 的值為 NULL
EXPLAIN SELECT * FROM users WHERE address IS NULL;

結(jié)果：type 列為 ALL（全表掃描），索引未生效。

2. 等值查詢(xún)（= NULL 或 = value）

= NULL 無(wú)效：

SQL 標(biāo)準(zhǔn)中 = NULL 會(huì)返回 UNKNOWN，應(yīng)使用 IS NULL。

非 NULL 等值查詢(xún)：

SELECT * FROM users WHERE email = 'user@example.com';  -- 若 email 有索引且非 NULL，索引生效

3. 范圍查詢(xún)或比較操作符

<, >, BETWEEN：

若查詢(xún)條件中包含 NULL 值，可能導(dǎo)致優(yōu)化器放棄索引。

例如：

-- 假設(shè) `price` 有索引且部分值為 NULL
SELECT * FROM products WHERE price > 100;  -- NULL 值會(huì)被過(guò)濾，但索引是否生效取決于非 NULL 值的分布

三、數(shù)據(jù)分布對(duì)索引使用的影響

優(yōu)化器通過(guò)統(tǒng)計(jì)信息（如 cardinality）評(píng)估查詢(xún)成本。以下場(chǎng)景可能導(dǎo)致索引失效：

高 NULL 比例：

若某列大部分值為 NULL，優(yōu)化器認(rèn)為全表掃描更快。

低區(qū)分度：

即使列非 NULL，但值重復(fù)率高（如性別列），優(yōu)化器也可能放棄索引。

四、驗(yàn)證索引是否生效的方法

使用 EXPLAIN 分析查詢(xún)計(jì)劃：

EXPLAIN SELECT * FROM users WHERE address IS NULL;

關(guān)鍵字段：

• type：ref 或 range 表示使用索引，ALL 表示全表掃描。
• key：顯示實(shí)際使用的索引。
• Extra：若顯示 Using index condition，表示索引下推（ICP）生效。

五、優(yōu)化建議

避免在索引列中存儲(chǔ)大量 NULL：

如果 NULL 無(wú)實(shí)際意義，可設(shè)置字段為 NOT NULL 并賦予默認(rèn)值（如空字符串、0）。

例如：

ALTER TABLE users MODIFY address VARCHAR(100) NOT NULL DEFAULT '';

• 覆蓋索引優(yōu)化 IS NULL 查詢(xún)：

CREATE INDEX idx_address ON users (address) INCLUDE (name);  -- MySQL 8.0+ 支持 INCLUDE

若需頻繁查詢(xún) IS NULL，可創(chuàng)建覆蓋索引包含查詢(xún)字段，避免回表。

• 強(qiáng)制使用索引：

SELECT * FROM users USE INDEX (idx_address) WHERE address IS NULL;

• 定期更新統(tǒng)計(jì)信息：

ANALYZE TABLE users;  -- 更新索引統(tǒng)計(jì)信息，幫助優(yōu)化器更準(zhǔn)確決策

六、示例分析

1. 數(shù)據(jù)表結(jié)構(gòu)

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT,
  bonus INT,  -- 允許 NULL，且 80% 的值為 NULL
  INDEX idx_bonus (bonus)
);

2. 查詢(xún)場(chǎng)景

-- 查詢(xún) bonus 為 NULL 的員工
EXPLAIN SELECT * FROM employees WHERE bonus IS NULL;

可能結(jié)果：優(yōu)化器選擇全表掃描（type: ALL），因?yàn)?NULL 值占比過(guò)高。

3. 優(yōu)化方案

• 方案 1：為 bonus 設(shè)置默認(rèn)值 0，減少 NULL 比例。
• 方案 2：強(qiáng)制使用索引（需測(cè)試性能是否提升）：

SELECT * FROM employees USE INDEX (idx_bonus) WHERE bonus IS NULL;

總結(jié)

• 索引不會(huì)因字段存在 NULL 值而失效，但查詢(xún)條件涉及 NULL 時(shí)，優(yōu)化器可能因數(shù)據(jù)分布放棄索引。
• 關(guān)鍵因素：NULL 值的比例、查詢(xún)條件類(lèi)型、索引設(shè)計(jì)。
• 優(yōu)化方向：減少 NULL 值、合理設(shè)計(jì)索引、利用覆蓋索引或統(tǒng)計(jì)信息更新。

通過(guò)合理設(shè)計(jì)表結(jié)構(gòu)和索引，可顯著提升包含 NULL 值字段的查詢(xún)性能。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論