欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

解讀索引列中有null值會不會使索引失效

 更新時間:2023年12月13日 14:27:44   作者:zyjzyjjyzjyz  
這篇文章主要介紹了解讀索引列中有null值會不會使索引失效問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

先說答案

null不會使索引失效,但是會影響優(yōu)化器對執(zhí)行計劃的選擇。

網(wǎng)上很多都說null會導(dǎo)致索引失效,這么說并不嚴(yán)謹(jǐn)。先看實驗。

注意:

  • count(列)不會把空值算進(jìn)去。
  • distance 列 如果列中有null會把列當(dāng)成一行輸出。
  • count(*)會把null值算進(jì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,這是普通索引的等職查詢才會有的。

對于explain的詳解:explain性能詳細(xì)分析

explain 
select * from null_test where name is not null;

可以看到name  is  not  null確實沒有走索引,而是全表掃描。這意味著導(dǎo)致索引失效嗎?往下看。

實驗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值當(dāng)成正常的值,mysql默認(rèn)認(rèn)為null是相同的,所以重復(fù)率特別高的話,優(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就在索引上,因為使用索引就可以查到。

總結(jié)

上面我說過mysql內(nèi)部認(rèn)為null是相等的,所以導(dǎo)致當(dāng)插入過多null值,造成重復(fù)率過多,is null不會走索引。而is  not  null因為查詢的結(jié)果過多,優(yōu)化器選擇了全表掃描。

什么原因讓mysql認(rèn)為null是相等的:

其實是有個參數(shù)控制的。

innodb_stats_method

show variables like 'innodb_stats_method';
SET GLOBAL  innodb_stats_method=nulls_unequal;

該參數(shù)有三個值,默認(rèn)為nulls_equal

1、null_equal:認(rèn)為所有的null值都是相等的,也是默認(rèn)值,這種統(tǒng)計方式,會讓優(yōu)化器認(rèn)為某個列中的平均一個值的重復(fù)次數(shù)特別多,傾向于不適用索引去訪問。

2、nulls_unequal:認(rèn)為所有的null值都不相等,這種統(tǒng)計方式,會讓優(yōu)化器認(rèn)為某個列中的平均一個值的重復(fù)次數(shù)特別少,更傾向于使用索引去訪問。

3、nulls_ignored:直接忽略null

在mysql5.7.2版本之后,mysql將這個值寫死為nulls_equal

好了,以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。

相關(guān)文章

  • MySQL中的常用函數(shù)

    MySQL中的常用函數(shù)

    這篇文章主要介紹了MySQL中的常用函數(shù)的相關(guān)資料,需要的朋友可以參考下
    2016-08-08
  • MYSQL與SQLserver之間存儲過程的轉(zhuǎn)換方式

    MYSQL與SQLserver之間存儲過程的轉(zhuǎn)換方式

    這篇文章主要介紹了MYSQL與SQLserver之間存儲過程的轉(zhuǎn)換方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-11-11
  • 一鍵搭建MYSQL主從,輕松應(yīng)對數(shù)據(jù)備份與恢復(fù)

    一鍵搭建MYSQL主從,輕松應(yīng)對數(shù)據(jù)備份與恢復(fù)

    MYSQL主從是一種常見的數(shù)據(jù)庫架構(gòu),它可以提高數(shù)據(jù)庫的可用性和性能,在主從架構(gòu)中,主數(shù)據(jù)庫負(fù)責(zé)處理寫操作,而從數(shù)據(jù)庫負(fù)責(zé)處理讀操作,當(dāng)主數(shù)據(jù)庫發(fā)生故障時,從數(shù)據(jù)庫可以接管并繼續(xù)提供服務(wù),從而實現(xiàn)高可用性,需要的朋友可以參考下
    2023-10-10
  • DB為何大量出現(xiàn)select @@session.tx_read_only 詳解

    DB為何大量出現(xiàn)select @@session.tx_read_only 詳解

    這篇文章主要給大家介紹了關(guān)于DB為何大量出現(xiàn)select @@session.tx_read_only 的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。
    2018-04-04
  • mysqldump數(shù)據(jù)庫備份參數(shù)詳解

    mysqldump數(shù)據(jù)庫備份參數(shù)詳解

    這篇文章主要介紹了mysqldump數(shù)據(jù)庫備份參數(shù)詳解,需要的朋友可以參考下
    2014-05-05
  • MySQL表的增刪改查基礎(chǔ)教程

    MySQL表的增刪改查基礎(chǔ)教程

    這篇文章主要給大家介紹了關(guān)于MySQL表的增刪改查的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2021-04-04
  • MySQL手動注冊binlog文件造成主從異常的原因

    MySQL手動注冊binlog文件造成主從異常的原因

    這篇文章主要介紹了MySQL手動注冊binlog文件造成主從異常的原因,幫助大家更好的理解和使用MySQL數(shù)據(jù)庫,感興趣的朋友可以了解下
    2020-10-10
  • MySql?InnoDB存儲引擎之Buffer?Pool運行原理講解

    MySql?InnoDB存儲引擎之Buffer?Pool運行原理講解

    緩沖池是用于存儲InnoDB表,索引和其他輔助緩沖區(qū)的緩存數(shù)據(jù)的內(nèi)存區(qū)域。緩沖池的大小對于系統(tǒng)性能很重要。更大的緩沖池可以減少磁盤I/O來多次訪問同一表數(shù)據(jù)。在專用數(shù)據(jù)庫服務(wù)器上,可以將緩沖池大小設(shè)置為計算機(jī)物理內(nèi)存大小的百分之80
    2023-01-01
  • ERROR CODE: 1175 YOU ARE USING SAFE UPDATE MODE AN

    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ī)多實例的方法教程

    mysql使用mysqld_multi部署單機(jī)多實例的方法教程

    這篇文章主要給大家介紹了關(guān)于mysql使用mysqld_multi部署單機(jī)多實例的相關(guān)資料,文中通過示例代碼將實現(xiàn)的步驟一步步介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。
    2018-03-03

最新評論