MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗?
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗?,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2965字,纯文字阅读大概需要5分钟。
内容图文
![MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗?](/upload/InfoBanner/zyjiaocheng/510/15a3695eb09b477797ddd7f0c5fba675.jpg)
看面试题的时候,总能看到MySQL在什么情况下用不上索引,如下:
MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询,只能使用全表扫描。
看到网上有验证,搬过来让大家看看,结构如下:
CREATE TABLE s1 (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 VARCHAR(100),
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
这个表里有10000条记录:
mysql> SELECT COUNT(*) FROM s1;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
下边贴几个图:
上边几个查询语句的WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件,但是从它们的执行计划中可以看出来,这些语句都采用了相应的二级索引执行查询,而不是使用所谓的全表扫描,谣言不攻自破。当然,戳破这些谣言并不是本文的目的,本文来更细致的分析一下这些查询到底是怎么执行的。
NULL值是怎么在记录中存储的
在MySQL中,每一条记录都有它固定的格式,我们以InnoDB存储引擎的Compact行格式为例,来看一下NULL值是怎样存储的。在Compact行格式下,一条记录是由下边这几个部分构成的:
新建一个称之为record_format_demo的表:
CREATE TABLE record_format_demo (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=COMPACT;
因为我们的重点是NULL值是如何存储在记录中的,所以重点唠叨一下行格式的NULL值列表部分。存储NULL值的过程如下:
1.首先统计表中允许存储NULL的列有哪些。
我们前边说过,主键列、被NOT NULL修饰的列都是不可以存储NULL值的,所以在统计的时候不会把这些列算进去。比方说表record_format_demo的3个列c1、c3、c4都是允许存储NULL值的,而c2列是被NOT NULL修饰,不允许存储NULL值。
2.如果表中没有允许存储NULL的列,则NULL值列表也不存在了,否则将每个允许存储NULL的列对应一个二进制位,二进制位按照列的顺序逆序排列,二进制位表示的意义如下:
因为表record_format_demo有3个值允许为NULL的列,所以这3个列和二进制位的对应关系就是这样:
再一次强调,二进制位按照列的顺序逆序排列,所以第一个列c1和最后一个二进制位对应。
二进制位的值为1时,代表该列的值为NULL。
二进制位的值为0时,代表该列的值不为NULL。
设计InnoDB的大叔规定NULL值列表必须用整数个字节的位表示,如果使用的二进制位个数不是整数个字节,则在字节的高位补0。
表record_format_demo只有3个值允许为NULL的列,对应3个二进制位,不足一个字节,所以在字节的高位补0,效果就是这样:
以此类推,如果一个表中有9个允许为NULL,那这个记录的NULL值列表部分就需要2个字节来表示了。
假设我们现在向record_format_demo表中插入一条记录:
INSERT INTO record_format_demo(c1, c2, c3, c4)
VALUES('eeee', 'fff', NULL, NULL);
这条记录的c1、c3、c4这3个列中c3和c4的值都为NULL,所以这3个列对应的二进制位的情况就是:
所以这记录的NULL值列表用十六进制表示就是:0x06。
MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗?
标签:第一个 HERE compact 面试 关系 列表 为我 包含 索引
本文系统来源:https://www.cnblogs.com/niuben/p/11197945.html
内容总结
以上是互联网集市为您收集整理的MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗?全部内容,希望文章能够帮你解决MySQL中 IS NULL、IS NOT NULL、!= 能用上索引吗?所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。