解讀索引列中有null值會(huì)不會(huì)使索引失效
先說(shuō)答案
null不會(huì)使索引失效，但是會(huì)影響優(yōu)化器對(duì)執(zhí)行計(jì)劃的選擇。
網(wǎng)上很多都說(shuō)null會(huì)導(dǎo)致索引失效，這么說(shuō)并不嚴(yán)謹(jǐn)。先看實(shí)驗(yàn)。
注意：
- count（列）不會(huì)把空值算進(jìn)去。
- distance 列 如果列中有null會(huì)把列當(dāng)成一行輸出。
- count（*）會(huì)把null值算進(jìn)去。
實(shí)驗(yàn)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，這是普通索引的等職查詢才會(huì)有的。
對(duì)于explain的詳解：explain性能詳細(xì)分析
explain select * from null_test where name is not null;
可以看到name is not null確實(shí)沒(méi)有走索引，而是全表掃描。這意味著導(dǎo)致索引失效嗎？往下看。
實(shí)驗(yàn)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;
可以看到和上面的條件都是相同的，但是卻是走了全表掃描，還沒(méi)想明白？接著往下看。
explain select * from null_test2 where name is not null;
可以看到name is not null走了索引，和上面的情況正好相反，這是什么情況？
- 其實(shí)這和普通索引上的情況相同，我們把null值當(dāng)成正常的值，mysql默認(rèn)認(rèn)為null是相同的，所以重復(fù)率特別高的話，優(yōu)化器肯定不會(huì)走索引，而是走全表掃描。
- 還要注意一點(diǎn)，is null時(shí)type=ref，is not null時(shí)type=range。
實(shí)驗(yàn)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;
可以看到唯一索引也可以插入多個(gè)null，并且null就在索引上，因?yàn)槭褂盟饕涂梢圆榈健?/p>
總結(jié)
上面我說(shuō)過(guò)mysql內(nèi)部認(rèn)為null是相等的，所以導(dǎo)致當(dāng)插入過(guò)多null值，造成重復(fù)率過(guò)多，is null不會(huì)走索引。而is not null因?yàn)椴樵兊慕Y(jié)果過(guò)多，優(yōu)化器選擇了全表掃描。
什么原因讓mysql認(rèn)為null是相等的：
其實(shí)是有個(gè)參數(shù)控制的。
innodb_stats_method
show variables like 'innodb_stats_method'; SET GLOBAL innodb_stats_method=nulls_unequal;
該參數(shù)有三個(gè)值，默認(rèn)為nulls_equal
1、null_equal:認(rèn)為所有的null值都是相等的，也是默認(rèn)值，這種統(tǒng)計(jì)方式，會(huì)讓優(yōu)化器認(rèn)為某個(gè)列中的平均一個(gè)值的重復(fù)次數(shù)特別多，傾向于不適用索引去訪問(wèn)。
2、nulls_unequal:認(rèn)為所有的null值都不相等，這種統(tǒng)計(jì)方式，會(huì)讓優(yōu)化器認(rèn)為某個(gè)列中的平均一個(gè)值的重復(fù)次數(shù)特別少，更傾向于使用索引去訪問(wèn)。
3、nulls_ignored:直接忽略null
在mysql5.7.2版本之后，mysql將這個(gè)值寫死為nulls_equal
好了，以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了MySQL中的常用函數(shù)的相關(guān)資料,需要的朋友可以參考下2016-08-08
MYSQL與SQLserver之間存儲(chǔ)過(guò)程的轉(zhuǎn)換方式
這篇文章主要介紹了MYSQL與SQLserver之間存儲(chǔ)過(guò)程的轉(zhuǎn)換方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-11-11
一鍵搭建MYSQL主從，輕松應(yīng)對(duì)數(shù)據(jù)備份與恢復(fù)
MYSQL主從是一種常見(jiàn)的數(shù)據(jù)庫(kù)架構(gòu),它可以提高數(shù)據(jù)庫(kù)的可用性和性能,在主從架構(gòu)中,主數(shù)據(jù)庫(kù)負(fù)責(zé)處理寫操作,而從數(shù)據(jù)庫(kù)負(fù)責(zé)處理讀操作,當(dāng)主數(shù)據(jù)庫(kù)發(fā)生故障時(shí),從數(shù)據(jù)庫(kù)可以接管并繼續(xù)提供服務(wù),從而實(shí)現(xiàn)高可用性,需要的朋友可以參考下2023-10-10
DB為何大量出現(xiàn)select @@session.tx_read_only 詳解
這篇文章主要給大家介紹了關(guān)于DB為何大量出現(xiàn)select @@session.tx_read_only 的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2018-04-04
mysqldump數(shù)據(jù)庫(kù)備份參數(shù)詳解
這篇文章主要介紹了mysqldump數(shù)據(jù)庫(kù)備份參數(shù)詳解,需要的朋友可以參考下2014-05-05
這篇文章主要給大家介紹了關(guān)于MySQL表的增刪改查的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
MySQL手動(dòng)注冊(cè)binlog文件造成主從異常的原因
這篇文章主要介紹了MySQL手動(dòng)注冊(cè)binlog文件造成主從異常的原因，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2020-10-10
MySql?InnoDB存儲(chǔ)引擎之Buffer?Pool運(yùn)行原理講解
緩沖池是用于存儲(chǔ)InnoDB表，索引和其他輔助緩沖區(qū)的緩存數(shù)據(jù)的內(nèi)存區(qū)域。緩沖池的大小對(duì)于系統(tǒng)性能很重要。更大的緩沖池可以減少磁盤I/O來(lái)多次訪問(wèn)同一表數(shù)據(jù)。在專用數(shù)據(jù)庫(kù)服務(wù)器上，可以將緩沖池大小設(shè)置為計(jì)算機(jī)物理內(nèi)存大小的百分之802023-01-01
ERROR CODE: 1175 YOU ARE USING SAFE UPDATE MODE AN
這篇文章主要介紹了ERROR CODE: 1175 YOU ARE USING SAFE UPDATE MODE AN,本文是在MySQL Workbench的環(huán)境操作,需要的朋友可以參考下2014-11-11
mysql使用mysqld_multi部署單機(jī)多實(shí)例的方法教程
這篇文章主要給大家介紹了關(guān)于mysql使用mysqld_multi部署單機(jī)多實(shí)例的相關(guān)資料，文中通過(guò)示例代碼將實(shí)現(xiàn)的步驟一步步介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2018-03-03