mysql IS NULL使用索引案例讲解

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使用内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！