快捷導(dǎo)航

mysql IS NULL使用索引案例講解

更新時(shí)間：2021年08月13日 17:09:42 作者：祈雨v

這篇文章主要介紹了mysql IS NULL使用索引案例講解,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下

簡(jiǎn)介

mysql的sql查詢(xún)語(yǔ)句中使用is null、is not null、!=對(duì)索引并沒(méi)有任何影響，并不會(huì)因?yàn)閣here條件中使用了is null、is not null、!=這些判斷條件導(dǎo)致索引失效而全表掃描。

mysql官方文檔也已經(jīng)明確說(shuō)明is null并不會(huì)影響索引的使用。

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.

事實(shí)上，導(dǎo)致索引失效而全表掃描的通常是因?yàn)橐淮尾樵?xún)中回表數(shù)量太多。mysql計(jì)算認(rèn)為使用索引的時(shí)間成本高于全表掃描，于是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');

執(zhí)行sql查詢(xún)時(shí)使用is null、is not null，發(fā)現(xiàn)依然使用的索引查詢(xún)，并沒(méi)有出現(xiàn)索引失效的問(wèn)題。

在這里插入圖片描述

分析

分析上述現(xiàn)象，則需要詳細(xì)了解mysql索引的工作原理以及索引數(shù)據(jù)結(jié)構(gòu)。下面，分別通過(guò)工具解析和直接查看二進(jìn)制文件兩種方式分別分析mysql索引數(shù)據(jù)結(jié)構(gòu)。

工具解析

innodb_ruby是一個(gè)非常強(qiáng)大的mysql分析工具，可以用來(lái)輕松解析mysql的.ibd文件進(jìn)而深入理解mysql的數(shù)據(jù)結(jié)構(gòu)。

首先安裝innodb_ruby工具：

yum install -y rubygems ruby-deve
gem install innodb_ruby

innodb_ruby的功能很多，此處我們只需要用來(lái)解析mysql的索引結(jié)構(gòu)，因此只需要如下的命令即可。更多的功能和命令詳見(jiàn)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)

通過(guò)解析工具數(shù)據(jù)mysql的索引結(jié)構(gòu)可以發(fā)現(xiàn)，null值也被儲(chǔ)存到了索引樹(shù)中，并且null值被處理成最小的值放在index_name索引樹(shù)的最左側(cè)。

二進(jìn)制文件

找到user_info表對(duì)應(yīng)的物理文件user_info.ibd，通過(guò)軟件例如UltraEdit打開(kāi)，直接定位到第5個(gè)數(shù)據(jù)頁(yè)（mysql默認(rèn)一個(gè)數(shù)據(jù)頁(yè)占用16KB）。

在這里插入圖片描述

如圖，這些二進(jìn)制數(shù)據(jù)就是index_name索引對(duì)應(yīng)的索引頁(yè)數(shù)據(jù)，只挑選其中的索引記錄，展開(kāi)如下：

最小記錄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字節(jié)00 29 -> 0x00010063偏移0x0029 -> 0x0001008C，即ID為2的索引位置；

ID為2的記錄頭信息最后2字節(jié)00 0B -> 0x0001008C偏移0x000B -> 0x00010097，即ID為3的索引位置；

ID為3的記錄頭信息最后2字節(jié)FF E8 -> 0x00010097偏移0xFFE8 -> 0x0001007F，即ID為1的索引位置；

ID為1的記錄頭信息最后2字節(jié)FF F1 -> 0x0001007F偏移0xFFF1 -> 0x00010070，最大記錄的記錄位置；

由此可見(jiàn)索引記錄是通過(guò)單向鏈表并以索引值排序串聯(lián)在一起，而null值被處理成最小的值放在了索引鏈表的最開(kāi)始位置，也就是索引樹(shù)的最左側(cè)。與innodb_ruby工具解析出來(lái)的結(jié)果一致。

誤解原因

為何大眾誤解認(rèn)為is null、is not null、!=這些判斷條件會(huì)導(dǎo)致索引失效而全表掃描呢？

導(dǎo)致索引失效而全表掃描的通常是因?yàn)橐淮尾樵?xún)中回表數(shù)量太多。mysql計(jì)算認(rèn)為使用索引的時(shí)間成本高于全表掃描，于是mysql寧可全表掃描也不愿意使用索引。使用索引的時(shí)間成本高于全表掃描的臨界值可以簡(jiǎn)單得記憶為20%左右。

詳細(xì)的分析過(guò)程可以見(jiàn)筆者的另一篇博客：mysql回表致索引失效。

也就是如果一條查詢(xún)語(yǔ)句導(dǎo)致的回表范圍超過(guò)全部記錄的20%，則會(huì)出現(xiàn)索引失效的問(wèn)題。而is null、is not null、!=這些判斷條件經(jīng)常會(huì)出現(xiàn)在這些回表范圍很大的場(chǎng)景，然后被人誤解為是這些判斷條件導(dǎo)致的索引失效。

復(fù)現(xiàn)索引失效

復(fù)現(xiàn)索引失效，只需要回表范圍超過(guò)全部記錄的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();

此時(shí)user_info表中一共有1003條記錄，其中只有1條記錄的name值為null。那么is null判斷語(yǔ)句導(dǎo)致的回表記錄只有1/1003不會(huì)超過(guò)臨界值，而is not null判斷語(yǔ)句導(dǎo)致的回表記錄有1002/1003遠(yuǎn)遠(yuǎn)超過(guò)臨界值，將出現(xiàn)索引失效的現(xiàn)象。

由下兩圖也可以見(jiàn)，is null依然正常使用索引，而is not null如預(yù)期由于回表率太高而寧可全表掃描也不使用索引。

在這里插入圖片描述

使用mysql的optimizer tracing（mysql5.6版本開(kāi)始支持）功能來(lái)分析sql的執(zhí)行計(jì)劃：

SET optimizer_trace="enabled=on";
explain select * from user_info where name is not null;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

optimizer tracing輸出的執(zhí)行計(jì)劃可見(jiàn)，該查詢(xún)下，使用全表掃描所需要的時(shí)間成本為206.9；而使用索引所需要的時(shí)間成本為1203.4，遠(yuǎn)遠(yuǎn)高于全表掃描。因此mysql最終選擇全表掃描而出現(xiàn)索引失效的現(xiàn)象。

{
    "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"
                    }
                }
            }
        }
    ]
}

到此這篇關(guān)于mysql IS NULL使用索引案例講解的文章就介紹到這了,更多相關(guān)mysql IS NULL使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: