mysql中IS NULL、IS NOT NULL不能走索引？

不知道是啥原因也不知道啥時候, 江湖上流傳著這么一個說法 mysql查詢條件包含IS NULL、IS NOT NULL、!=、like %* 、like %*%, 不能 使用索引查詢， 只能 使用全表掃描。

剛?cè)胄袝r我也是這么認(rèn)為的，還奉為真理！

但是時間工作中你會發(fā)現(xiàn)還是走索引啊！下面我們來一一探究其中的奧秘。

一、首先驗證一下是會走索引的

創(chuàng)建一個表，結(jié)構(gòu)如下：

create table user_info( id int PRIMARY key auto_increment, name varchar(16) default '', age tinyint default 0, address varchar(32) default '', PRIMARY KEY (`id`), KEY `name` (`name`), KEY `address_2` (`address`,`name`) ); ALTER TABLE user_info ADD INDEX (NAME); ALTER TABLE user_info ADD INDEX (address); 數(shù)據(jù)1 INSERT INTO user_info(NAME,age,address) VALUES (9,9,'shenzhen9'); BEGIN DECLARE i INT DEFAULT 1000; WHILE i < 9000 DO INSERT INTO user_info (`NAME`, `age`, `address`) VALUES (NULL, i , SUBSTRING(MD5(RAND()),1,10) ) ; SET i = i+ 1 ; END WHILE ; ① EXPLAIN SELECT * FROM user_info WHERE `name` IS NOT NULL ② EXPLAIN SELECT * FROM user_info WHERE `name` !='9' ③ EXPLAIN SELECT * FROM user_info WHERE `name` is null 數(shù)據(jù)2 INSERT INTO user_info(NAME,age,address) VALUES (null,9,'shenzhen9'); BEGIN DECLARE i INT DEFAULT 1000; WHILE i < 9000 DO INSERT INTO user_info (`NAME`, `age`, `address`) VALUES (REPLACE(UUID(),'-',''), i , SUBSTRING(MD5(RAND()),1,10) ) ; SET i = i+ 1 ; END WHILE ; ④ EXPLAIN SELECT * FROM user_info WHERE `name` IS NOT NULL ⑤ EXPLAIN SELECT * FROM user_info WHERE `name` !='9' ⑥ EXPLAIN SELECT * FROM user_info WHERE `name` is null

執(zhí)行數(shù)據(jù)1 會發(fā)現(xiàn)sql①②走索引，③不走索引

執(zhí)行數(shù)據(jù)2 會發(fā)現(xiàn)sql⑥走索引，④⑤不走索引

二、B+樹數(shù)據(jù)排列規(guī)則

1、聚簇索引索引：

①頁面中的記錄是按照主鍵值進(jìn)行排序的；

②B+樹每一層節(jié)點(diǎn)（頁面）都是按照頁中記錄的主鍵值大小進(jìn)行排序的；

③B+樹葉子節(jié)點(diǎn)對應(yīng)的頁面中存儲的是完整的用戶記錄（就是一條記錄中包含我們定義的所有列值，還包含一些InnoDB自己添加的一些隱藏列）；

2、二級索引：

①頁面中的記錄是按照給定的索引列的值進(jìn)行排序的。

②B+樹每一層節(jié)點(diǎn)（頁面）都是按照頁中記錄的給定的索引列的值進(jìn)行排序的。

③B+樹葉子節(jié)點(diǎn)對應(yīng)的頁面中存儲的只是索引列的值 + 主鍵值。

二級索引值能為空。那對于索引列值為NULL的二級索引記錄，在B+樹的哪個位置呢？

在B+樹的最左邊。如下圖

至于為什么,InnoDB是這樣的規(guī)定：SQL中的NULL值是列中最小的值

什么時候索引又不生效了呢？

對比數(shù)據(jù)1和數(shù)據(jù)2兩個數(shù)據(jù)中null值的數(shù)量不一樣，當(dāng)null值占多數(shù)時is not null 和!=走索引 ,is null不走索引了，數(shù)據(jù)2剛好相反。

索引(二級索引)掃描成本:

1、讀取索引記錄成本

2、反查主鍵索引查找完整數(shù)據(jù)成本即回表

如果查詢讀取的二級索引越多那么需要回表查詢的次數(shù)就會越多，達(dá)到一定的比例就會變成全部查詢了，也就是上面null 查詢時索引有時不生效的原因。

綜上MySQL中決定使不使用某個索引執(zhí)行查詢的依據(jù)是成本大小。而不是在WHERE子句中用了IS NULL、IS NOT NULL、!=這些條件

三、如何讓like‘%字符串%’，‘字符串%’時走索引

通常情況下我們使用like % %、% 的確不會走索引 但是并不代表就一定不能走索引，我們對上面表中name和age建立復(fù)合索引

explain select name from user_info where name like '%a%'; 1 SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index explain select name,age from user_info where name like '%a%'; 1 SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index 以下兩個例子是查詢了不在復(fù)合索引中的列進(jìn)而造成全表掃描 explain select name,age,address from user_info where name like '%a%'; 1 SIMPLE user_info ALL 6 16.67 Using where explain select * from user_info where name like '%a%'; 1 SIMPLE user_info ALL 6 16.67 Using where

所以like走不走索引并不是絕對的，要看使用條件！

來源：https://www.tuicool.com/articles/ANnIFrF

歡迎關(guān)注微信公眾號【慕容千語】

總結(jié)

以上是生活随笔為你收集整理的bcp 不能调用where 子句_技术分享 || Mysql中IS NULL、IS NOT NULL不能走索引？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。