mysql IS NULL 使用索引

悠悠 2024-04-17 16:39 62阅读 0赞

## 简介 ##

mysql的sql查询语句中使用`is null`、`is not null`、`!=`对索引并没有任何影响，并不会因为`where`条件中使用了`is null`、`is not null`、`!=`这些判断条件导致索引失效而全表扫描。

[mysql官方文档][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`，发现依然使用的索引查询，并没有出现索引失效的问题。

![在这里插入图片描述][2019082818564265.png]

![在这里插入图片描述][20190828185700456.png]

## 分析 ##

分析上述现象，则需要详细了解mysql索引的工作原理以及索引数据结构。下面，分别通过工具解析和直接查看二进制文件两种方式分别分析mysql索引数据结构。

#### 工具解析 ####

[innodb\_ruby][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）。

![在这里插入图片描述][20190828185721229.png]

如图，这些二进制数据就是`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回表致索引失效][mysql 1]。

也就是如果一条查询语句导致的回表范围超过全部记录的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`如预期由于回表率太高而宁可全表扫描也不使用索引。

![在这里插入图片描述][20190828185746864.png]

![在这里插入图片描述][20190828185800759.png]

使用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]: https://dev.mysql.com/doc/refman/5.5/en/is-null-optimization.html
[2019082818564265.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/1c813d49713a4b3092fde38e01c2304c.png
[20190828185700456.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/79c2f5749126444ea8304b6c4ef7a2b8.png
[innodb_ruby]: https://github.com/jeremycole/innodb_ruby
[wiki]: https://github.com/jeremycole/innodb_ruby/wiki
[20190828185721229.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/f77132d1ca864ba9a74319433ac2fc06.png
[mysql 1]: https://blog.csdn.net/sz85850597/article/details/91999015
[20190828185746864.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/18571d7efc21432f8cbb545275acb4ae.png
[20190828185800759.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/06e645327a8947c1807d144a31b054ac.png