mysql IS NULL使用索引案例講解
簡介
mysql的sql查詢語句中使用is null、is not null、!=對索引並沒有任何影響，並不會因為where條件中使用瞭is null、is not null、!=這些判斷條件導致索引失效而全表掃描。
mysql官方文檔也已經明確說明is null並不會影響索引的使用。
MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.
事實上，導致索引失效而全表掃描的通常是因為一次查詢中回表數量太多。mysql計算認為使用索引的時間成本高於全表掃描，於是mysql寧可全表掃描也不願意使用索引。
案例
CREATE TABLE `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(11) DEFAULT NULL, `age` int(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user_info` (`id`, `name`, `age`) VALUES ('1', 'tom', '18');
INSERT INTO `user_info` (`id`, `name`, `age`) VALUES ('2', null, '19');
INSERT INTO `user_info` (`id`, `name`, `age`) VALUES ('3', 'cat', '20');
執行sql查詢時使用is null、is not null，發現依然使用的索引查詢，並沒有出現索引失效的問題。
分析
分析上述現象，則需要詳細瞭解mysql索引的工作原理以及索引數據結構。下面，分別通過工具解析和直接查看二進制文件兩種方式分別分析mysql索引數據結構。
工具解析
innodb_ruby是一個非常強大的mysql分析工具，可以用來輕松解析mysql的.ibd文件進而深入理解mysql的數據結構。
首先安裝innodb_ruby工具：
yum install -y rubygems ruby-deve gem install innodb_ruby
innodb_ruby的功能很多，此處我們隻需要用來解析mysql的索引結構，因此隻需要如下的命令即可。更多的功能和命令詳見wiki。
innodb_space -s ibdata1 -T sakila/film -I PRIMARY index-recurse
解析主鍵索引：
$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I PRIMARY index-recurse ROOT NODE #3: 3 records, 89 bytes RECORD: (id=1) → (name="tom", age=18) RECORD: (id=2) → (name=:NULL, age=19) RECORD: (id=3) → (name="cat", age=20)
解析普通索引index_name：
$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I index_name index-recurse ROOT NODE #4: 3 records, 38 bytes RECORD: (name=:NULL) → (id=2) RECORD: (name="cat") → (id=3) RECORD: (name="tom") → (id=1)
通過解析工具數據mysql的索引結構可以發現，null值也被儲存到瞭索引樹中，並且null值被處理成最小的值放在index_name索引樹的最左側。
二進制文件
找到user_info表對應的物理文件user_info.ibd，通過軟件例如UltraEdit打開，直接定位到第5個數據頁（mysql默認一個數據頁占用16KB）。
如圖，這些二進制數據就是index_name索引對應的索引頁數據，隻挑選其中的索引記錄，展開如下：
最小記錄0x00010063
01 B2 01 00 02 00 29 記錄頭信息 69 6E 66 69 6D 75 6D 最小記錄(固定值infimum)
最大記錄0x00010070
00 04 00 0B 00 00 記錄頭信息 73 75 70 72 65 6D 75 6D 最大記錄(固定值supremum)
ID為1的索引0x0001007f
03 00 00 00 10 FF F1 記錄頭信息 74 6F 6D 字段name的值:tom 80 00 00 01 RowID:主鍵id的值為1
ID為2的索引0x0001008c
01 00 00 18 00 0B 記錄頭信息 字段name的值:null 80 00 00 02 RowID:主鍵id的值為2
ID為3的索引0x00010097
03 00 00 00 20 FF E8 記錄頭信息 63 61 74 字段name的值:cat 80 00 00 03 RowID:主鍵id的值為3
最小記錄的記錄頭信息最後2字節00 29 -> 0x00010063偏移0x0029 -> 0x0001008C，即ID為2的索引位置；
ID為2的記錄頭信息最後2字節00 0B -> 0x0001008C偏移0x000B -> 0x00010097，即ID為3的索引位置；
ID為3的記錄頭信息最後2字節FF E8 -> 0x00010097偏移0xFFE8 -> 0x0001007F，即ID為1的索引位置；
ID為1的記錄頭信息最後2字節FF F1 -> 0x0001007F偏移0xFFF1 -> 0x00010070，最大記錄的記錄位置；
由此可見索引記錄是通過單向鏈表並以索引值排序串聯在一起，而null值被處理成最小的值放在瞭索引鏈表的最開始位置，也就是索引樹的最左側。與innodb_ruby工具解析出來的結果一致。
誤解原因
為何大眾誤解認為is null、is not null、!=這些判斷條件會導致索引失效而全表掃描呢？
導致索引失效而全表掃描的通常是因為一次查詢中回表數量太多。mysql計算認為使用索引的時間成本高於全表掃描，於是mysql寧可全表掃描也不願意使用索引。使用索引的時間成本高於全表掃描的臨界值可以簡單得記憶為20%左右。
詳細的分析過程可以見筆者的另一篇博客：mysql回表致索引失效。
也就是如果一條查詢語句導致的回表范圍超過全部記錄的20%，則會出現索引失效的問題。而is null、is not null、!=這些判斷條件經常會出現在這些回表范圍很大的場景，然後被人誤解為是這些判斷條件導致的索引失效。
復現索引失效
復現索引失效，隻需要回表范圍超過全部記錄的20%，如下插入1000條非null記錄。
delimiter // CREATE PROCEDURE init_user_info() BEGIN DECLARE indexNo INT; SET indexNo = 0; WHILE indexNo < 1000 DO START TRANSACTION; insert into user_info(name,age) values (concat(floor(rand()*1000000000)),floor(rand()*100)); SET indexNo = indexNo + 1; COMMIT; END WHILE; END // delimiter ; call init_user_info();
此時user_info表中一共有1003條記錄，其中隻有1條記錄的name值為null。那麼is null判斷語句導致的回表記錄隻有1/1003不會超過臨界值，而is not null判斷語句導致的回表記錄有1002/1003遠遠超過臨界值，將出現索引失效的現象。
由下兩圖也可以見，is null依然正常使用索引，而is not null如預期由於回表率太高而寧可全表掃描也不使用索引。
使用mysql的optimizer tracing（mysql5.6版本開始支持）功能來分析sql的執行計劃：
SET optimizer_trace="enabled=on"; explain select * from user_info where name is not null; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
optimizer tracing輸出的執行計劃可見，該查詢下，使用全表掃描所需要的時間成本為206.9；而使用索引所需要的時間成本為1203.4，遠遠高於全表掃描。因此mysql最終選擇全表掃描而出現索引失效的現象。
{
"rows_estimation": [
{
"table": "`user_info`",
"range_analysis": {
"table_scan": {
"rows": 1004, // 全表掃描需要掃描1004條記錄
"cost": 206.9 // 全表掃描需要的成本為206.9
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "index_name",
"usable": true,
"key_parts": [
"name",
"id"
]
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "index_name",
"ranges": [
"NULL < name"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1002, // 索引需要掃描1002條記錄
"cost": 1203.4, // 索引需要的成本為1203.4
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
}
到此這篇關於mysql IS NULL使用索引案例講解的文章就介紹到這瞭,更多相關mysql IS NULL使用內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！