mysqlISNULL使⽤索引案例讲解
简介
mysql的sql查询语句中使⽤is null、is not null、!=对索引并没有任何影响，并不会因为where条件中使⽤了is null、is not null、!=这些判断条件导致索引失效⽽全表扫描。
也已经明确说明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索引数据结构。
⼯具解析
是⼀个⾮常强⼤的mysql分析⼯具，可以⽤来轻松解析mysql的.ibd⽂件进⽽深⼊理解mysql的数据结构。
⾸先安装innodb_ruby⼯具：
yum install -y rubygems ruby-deve
gem install innodb_ruby
innodb_ruby的功能很多，此处我们只需要⽤来解析mysql的索引结构，因此只需要如下的命令即可。更多的功能和命令详见。
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
查看mysql索引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%左右。
详细的分析过程可以见笔者的另⼀篇博客：。
也就是如果⼀条查询语句导致的回表范围超过全部记录的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使⽤内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。