解讀索引列中有null值會不會使索引失效
先說答案
null不會使索引失效，但是會影響優(yōu)化器對執(zhí)行計劃的選擇。
網(wǎng)上很多都說null會導致索引失效，這么說并不嚴謹。先看實驗。
注意：
- count（列）不會把空值算進去。
- distance 列 如果列中有null會把列當成一行輸出。
- count（*）會把null值算進去。
實驗1
create table null_test( id int PRIMARY KEY, name VARCHAR(10), age VARCHAR(10), KEY inx_test_age(age), KEY inx_test_name(name) ) insert into null_test values(1,'a','2'); insert into null_test values(2,'b','3'); insert into null_test values(3,'c','4'); insert into null_test values(4,'d','5'); insert into null_test values(5,null,'6'); insert into null_test values(6,null,'6'); insert into null_test values(7,null,'9'); insert into null_test values(8,'q',null); insert into null_test values(9,'','5'); insert into null_test values(10,'','7'); insert into null_test values(11,'t','');
創(chuàng)建null_test表，并在name、age列上建普通索引，插入null值。
explain select * from null_test where name is null;
可以看到name is null走了索引，并且type是ref，這是普通索引的等職查詢才會有的。
對于explain的詳解：explain性能詳細分析
explain select * from null_test where name is not null;
可以看到name is not null確實沒有走索引，而是全表掃描。這意味著導致索引失效嗎？往下看。
實驗2
create table null_test2( id int PRIMARY KEY, name VARCHAR(10), age VARCHAR(10), KEY inx_test2_age(age), KEY inx_test2_name(name) ) insert into null_test2 values(1,'a','2'); insert into null_test2 values(2,'b','3'); insert into null_test2 values(3,'c','4'); insert into null_test2 values(4,'d','5'); insert into null_test2 values(5,null,'6'); insert into null_test2 values(6,null,'6'); insert into null_test2 values(7,null,'9'); insert into null_test2 values(8,null,'6'); insert into null_test2 values(9,null,'6'); insert into null_test2 values(10,null,'9'); insert into null_test2 values(11,null,'9'); insert into null_test2 values(12,null,'6'); insert into null_test2 values(13,null,'6'); insert into null_test2 values(14,null,'9');
創(chuàng)建null_test2表，插入很多null值。
explain select * from null_test2 where name is null;
可以看到和上面的條件都是相同的，但是卻是走了全表掃描，還沒想明白？接著往下看。
explain select * from null_test2 where name is not null;
可以看到name is not null走了索引，和上面的情況正好相反，這是什么情況？
- 其實這和普通索引上的情況相同，我們把null值當成正常的值，mysql默認認為null是相同的，所以重復率特別高的話，優(yōu)化器肯定不會走索引，而是走全表掃描。
- 還要注意一點，is null時type=ref，is not null時type=range。
實驗3
create table null_test3( id int PRIMARY KEY, name VARCHAR(10), age VARCHAR(10), KEY inx_test2_age(age), UNIQUE KEY inx_test2_name(name) ) insert into null_test3 values(1,'a','2'); insert into null_test3 values(2,'b','3'); insert into null_test3 values(3,'c','4'); insert into null_test3 values(4,'d','5'); insert into null_test3 values(5,null,'6'); insert into null_test3 values(6,null,'6'); insert into null_test3 values(7,null,'9'); insert into null_test3 values(8,null,'6'); insert into null_test3 values(9,null,'6'); insert into null_test3 values(12,'q',null); insert into null_test3 values(13,'','5'); insert into null_test3 values(10,'g','7'); insert into null_test3 values(11,'t','');
explain select * from null_test3 where name is null;
explain select NAME from null_test3 where name is null;
可以看到唯一索引也可以插入多個null，并且null就在索引上，因為使用索引就可以查到。
總結
上面我說過mysql內部認為null是相等的，所以導致當插入過多null值，造成重復率過多，is null不會走索引。而is not null因為查詢的結果過多，優(yōu)化器選擇了全表掃描。
什么原因讓mysql認為null是相等的：
其實是有個參數(shù)控制的。
innodb_stats_method
show variables like 'innodb_stats_method'; SET GLOBAL innodb_stats_method=nulls_unequal;
該參數(shù)有三個值，默認為nulls_equal
1、null_equal:認為所有的null值都是相等的，也是默認值，這種統(tǒng)計方式，會讓優(yōu)化器認為某個列中的平均一個值的重復次數(shù)特別多，傾向于不適用索引去訪問。
2、nulls_unequal:認為所有的null值都不相等，這種統(tǒng)計方式，會讓優(yōu)化器認為某個列中的平均一個值的重復次數(shù)特別少，更傾向于使用索引去訪問。
3、nulls_ignored:直接忽略null
在mysql5.7.2版本之后，mysql將這個值寫死為nulls_equal
好了，以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
在MySQL中,空格是一個特殊的字符,本文主要介紹了MySQL中的空格處理方法,具有一定的參考價值,感興趣的可以了解一下2023-11-11
MySQL的優(yōu)化是非常重要的。其他最常用也最需要優(yōu)化的就是limit。MySQL的limit給分頁帶來了極大的方便，但數(shù)據(jù)量一大的時候，limit的性能就急劇下降。2011-04-04
使用python連接mysql數(shù)據(jù)庫之pymysql模塊的使用
這篇文章主要介紹了使用python連接mysql數(shù)據(jù)庫之pymysql模塊的使用，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-09-09
本篇文章詳細的介紹了Mysql命令，MySQL是一個關系型數(shù)據(jù)庫管理系統(tǒng)，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網(wǎng)站的開發(fā)都選擇MySQL作為網(wǎng)站數(shù)據(jù)庫。2016-11-11
這篇文章主要介紹了mysql一對多關聯(lián)查詢分頁錯誤問題的解決方法,非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下2018-09-09
Windows?Server?2019部署MySQL?8完整步驟教程
MySQL在開發(fā)開源軟件時經(jīng)常被當作該軟件的數(shù)據(jù)管理系統(tǒng),所以我們在開發(fā)時將會經(jīng)常用到它,所以如何安裝MySQL就是一個問題了,這篇文章主要介紹了Windows Server 2019部署MySQL 8的相關資料,需要的朋友可以參考下2026-04-04
MySQL?原理與優(yōu)化之Limit?查詢優(yōu)化
這篇文章主要介紹了MySQL?原理與優(yōu)化之Limit?查詢優(yōu)化，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08
MySQL首次登錄跳過密碼驗證并修改密碼實現(xiàn)方式
文章介紹了如何在MySQL中重置忘記密碼的步驟,包括查找和配置my.ini文件、跳過密碼驗證、連接數(shù)據(jù)庫、修改密碼以及恢復配置和重啟服務2025-11-11
這篇文章主要介紹了MySQL數(shù)據(jù)類型的相關資料，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-01-01
這篇文章主要介紹了MySQL修改root密碼的4種方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-09-09