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;

在这里插入图片描述

分析

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

工具解析

innodb_ruby是一个非常强大的mysql分析工具，可以用来轻松解析mysql的.ibd文件进而深入理解mysql的数据结构。

首先安装innodb_ruby工具：

yum install -y rubygems ruby-devegem install innodb_ruby

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-recurseROOT 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)

$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I index_name index-recurseROOT NODE #4: 3 records, 38 bytes  RECORD: (name=:NULL) → (id=2)  RECORD: (name="cat") → (id=3)  RECORD: (name="tom") → (id=1)

二进制文件

找到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)

00 04 00 0B 00 00 记录头信息73 75 70 72 65 6D 75 6D 最大记录(固定值supremum)

03 00 00 00 10 FF F1 记录头信息74 6F 6D 字段name的值:tom80 00 00 01 RowID:主键id的值为1

01 00 00 18 00 0B 记录头信息字段name的值:null80 00 00 02RowID:主键id的值为2

03 00 00 00 20 FF E8 记录头信息63 61 74 字段name的值:cat80 00 00 03 RowID:主键id的值为3

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 DOSTART 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();

由下两图也可以见，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;

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

简介

案例

分析

工具解析

二进制文件

误解原因

复现索引失效

更多相关文章

随机推荐