一.首先看一個(gè)我在某公眾號(hào)看到的一個(gè)關(guān)于數(shù)據(jù)庫(kù)優(yōu)化的舉措

二.如果where子句中查詢(xún)的列執(zhí)行了 “is null” 或者 “is not null” 或者 “<=> null” 會(huì)不會(huì)使用索引呢？

先列出結(jié)論：where子句中使用上述對(duì)null的判斷，如果判斷的列設(shè)置了索引，那就可以使用到索引

三.測(cè)試：

1.建表

1 CREATE TABLE`test_null_index` (2 `id` int(11) DEFAULT NULL,3 `mark` varchar(20) DEFAULT NULL,4 `name` varchar(11) DEFAULT NULL

5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插數(shù)據(jù)

create procedure test_null_index(in num int)BEGIN

DECLARE i int;set i=1;while (i

DOif mod(i,10)!=0 then

insert into test_null_index values (i,concat('aaa',i),null);else

insert into test_null_index values (null,concat('aaa',i),'bbb');end if;set i=i+1;END while;END;

call test_null_index(10000);

3.沒(méi)加索引時(shí)，type為All，全表掃描

mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | test_null_index | ALL | NULL | NULL | NULL | NULL | 10589 | Using where |

+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

4.加上索引，可以看到，索引起作用了

mysql> create index idx_test_null ontest_null_index(id);

Query OK,0 rows affected (0.12sec)

Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

| 1 | SIMPLE | test_null_index | ref | idx_test_null | idx_test_null | 5 | const | 998 | Using where |

+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

四.注意，只能使用針對(duì)一個(gè)字段的關(guān)于null的判斷，如果同時(shí)在兩個(gè)字段對(duì)null進(jìn)行判斷，還是會(huì)走全表掃描

1.測(cè)試，可以看到，在name字段加上索引，并查詢(xún)name為空的語(yǔ)句，同樣會(huì)走索引

mysql> create index idx_test_null2 ontest_null_index(name);

Query OK,0 rows affected (0.13sec)

Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE name is null;+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

| 1 | SIMPLE | test_null_index | ref | idx_test_null2 | idx_test_null2 | 36 | const | 8994 | Using where |

+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

1 row in set (0.00 sec)

2.同時(shí)針對(duì)id和name查詢(xún)?yōu)榭盏恼Z(yǔ)句，走的是全表掃描

mysql> explain SELECT * from test_null_index WHERE id is null or name is null;+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | test_null_index | ALL | idx_test_null,idx_test_null2 | NULL | NULL | NULL | 10589 | Using where |

+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

相關(guān)資源：MySQL中NULL對(duì)索引的影響深入講解_mysqlnull對(duì)索引的影響,null對(duì)...
————————————————
版權(quán)聲明：本文為CSDN博主「尋古詩(shī)詞網(wǎng)」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/weixin_36180068/article/details/114754393

總結(jié)

以上是生活随笔為你收集整理的MySQL_列值为null对索引的影响的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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