mysql IS NULL使用索引案例讲解

 更新时间:2021年8月14日 00:00  点击:2432

简介

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

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [[email protected] ~]# wget http://www.cm...2015-03-15
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
  • Mysql命令大全(详细篇)

    一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
  • MySQL数据库备份还原方法

    MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • mongodb与mysql命令详细对比

    传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
  • 如何解决安装MySQL5.0后出现1607异常

    最近项目比较多,时间不宽松,一直没给大家整理,今天小编抽个时间把我的解决方案分享给大家,具体内容如下所示。问题描述:我在自己的电脑上配置了Mysql5环境,同时安装了一个phpMyAdmin管理工具,安装完成后,发现在phpMyAdmin里面...2015-10-21
  • Delphi远程连接Mysql的实现方法

    这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30
  • MySQL 从全库备份中恢复指定的表和库示例

    这篇文章主要介绍了MySQL 从全库备份中恢复指定的表和库示例的相关资料,这里提供了相应的方法,来实现恢复指定的表和库数据,需要的朋友可以参考下...2016-12-02
  • MySQL数据库遭到攻击篡改(使用备份和binlog进行数据恢复)

    这篇文章主要介绍了MySQL数据库遭到攻击篡改(使用备份和binlog进行数据恢复),需要的朋友可以参考下...2016-04-26
  • python操作mysql、excel、pdf的示例

    这篇文章主要介绍了python操作mysql、excel、pdf的示例,帮助大家更好的理解和学习使用python,感兴趣的朋友可以了解下...2021-03-29
  • mysql Non-Transactional Database Only(只支持MyISAM)

    按照discuz官方的建议,选的都是Non-Transactional Database Only 只支持MyISAM,其实默认都安装也挺好 ...2016-04-18