企业网站适合响应式嘛,nas可以做网站下载服务器吗,godaddy怎么建设网站,注册个公司大概要多少钱不知道从什么时候开始#xff0c;网上流传着这么一个说法#xff1a;MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、! 这些条件时便不能使用索引查询#xff0c;只能使用全表扫描。这种说法愈演愈烈#xff0c;甚至被很多同学奉为真理。咱啥话也不说#xff0c;举个例子。…不知道从什么时候开始网上流传着这么一个说法MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、! 这些条件时便不能使用索引查询只能使用全表扫描。这种说法愈演愈烈甚至被很多同学奉为真理。咱啥话也不说举个例子。假如我们有个表s1结构如下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)
) EngineInnoDB CHARSETutf8;这个表里有10000条记录mysql SELECT COUNT(*) FROM s1;
----------
| COUNT(*) |
----------
| 10000 |
----------
1 row in set (0.00 sec)下边我们直接贴几个图imageimage上边几个查询语句的WHERE子句中用了IS NULL、IS NOT NULL、!这些条件但是从它们的执行计划中可以看出来这些语句都采用了相应的二级索引执行查询而不是使用所谓的全表扫描谣言不攻自破。当然戳破这些谣言并不是本文的目的本文来更细致的分析一下这些查询到底是怎么执行的。NULL值是怎么在记录中存储的在MySQL中每一条记录都有它固定的格式我们以InnoDB存储引擎的Compact行格式为例来看一下NULL值是怎样存储的。在Compact行格式下一条记录是由下边这几个部分构成的image为了故事的顺利发展我们新建一个称之为record_format_demo的表CREATE TABLE record_format_demo (c1 VARCHAR(10),c2 VARCHAR(10) NOT NULL,c3 CHAR(10),c4 VARCHAR(10)) CHARSETascii ROW_FORMATCOMPACT;因为我们的重点是NULL值是如何存储在记录中的所以重点唠叨一下行格式的NULL值列表部分其他的部分可以到小册中查看。存储NULL值的过程如下首先统计表中允许存储NULL的列有哪些。我们前边说过主键列、被NOT NULL修饰的列都是不可以存储NULL值的所以在统计的时候不会把这些列算进去。比方说表record_format_demo的3个列c1、c3、c4都是允许存储NULL值的而c2列是被NOT NULL修饰不允许存储NULL值。如果表中没有允许存储NULL的列则NULL值列表也不存在了否则将每个允许存储NULL的列对应一个二进制位二进制位按照列的顺序逆序排列二进制位表示的意义如下因为表record_format_demo有3个值允许为NULL的列所以这3个列和二进制位的对应关系就是这样image再一次强调二进制位按照列的顺序逆序排列所以第一个列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。键值为NULL的记录是怎么在B树中存放的对于InnoDB存储引擎来说记录都是存储在页面中的一个页面默认是16KB大小这些页面可以作为B树的节点而组成一个索引类似这种样子只是用下边的图举个B树的例子而已跟我们上边列举的表没关系聚簇索引和二级索引都对应着像上图一样的B树也就是说有多少个索引就有多少棵对应的B树不过对于聚簇索引索引来说页面中的记录是按照主键值进行排序的而对于二级索引来说页面中的记录是按照给定的索引列的值进行排序的。对于聚簇索引来说B树每一层节点页面都是按照页中记录的主键值大小进行排序的而对于二级索引来说B树每一层节点页面都是按照页中记录的给定的索引列的值进行排序的。对于聚簇索引来说B树叶子节点对应的页面中存储的是完整的用户记录就是一条记录中包含我们定义的所有列值还包含一些InnoDB自己添加的一些隐藏列而对于二级索引来说B树叶子节点对应的页面中存储的只是索引列的值 主键值。按规定一条记录的主键值不允许存储NULL值所以下边语句中的WHERE子句结果肯定为FALSESELECT * FROM tbl_name WHERE primary_key IS NULL;像这样的语句优化器自己就能判定出WHERE子句必定为NULL所以压根儿不会去执行它不信我们看Extra信息提示WHERE子句压根儿不成立image对于二级索引来说索引列的值可能为NULL。那对于索引列值为NULL的二级索引记录来说它们被放在B树的哪里呢答案是放在B树的最左边。比方说我们有如下查询语句SELECT * FROM s1 WHERE key1 IS NULL;那它的查询示意图就如下所示image从图中可以看出对于s1表的二级索引idx_key1来说值为NULL的二级索引记录都被放在了B树的最左边这是因为设计InnoDB的大叔有这样的规定We define the SQL null to be the smallest possible value of a field.也就是说他们把SQL中的NULL值认为是列中最小的值。在通过二级索引idx_key1对应的B树快速定位到叶子节点中符合条件的最左边的那条记录后也就是本例中id值为521的那条记录之后就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了直到某条记录的key1列不为NULL。小贴士 通过B树快速定位到叶子节点的记录的过程是靠一个所谓的页目录Page Directory做到的不过这不是本文的重点大家可以到小册中翻看都有详细解释。使不使用索引的依据到底是什么那既然IS NULL、IS NOT NULL、!这些条件都可能使用到索引那到底什么时候索引什么时候采用全表扫描呢答案很简单成本。当然关于如何定量的计算使用某个索引执行查询的成本比较复杂我们在小册中花了很大的篇幅来唠叨了。不过因为篇幅有限我们在这里只准备定性的分析一下。对于使用二级索引进行查询来说成本组成主要有两个方面读取二级索引记录的成本将二级索引记录执行回表操作也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。很显然要扫描的二级索引记录条数越多那么需要执行的回表操作的次数也就越多达到了某个比例时使用二级索引执行查询的成本也就超过了全表扫描的成本举一个极端的例子比方说要扫描的全部的二级索引记录那就要对每条记录执行一遍回表操作自然不如直接扫描聚簇索引来的快。所以MySQL优化器在真正执行查询之前对于每个可能使用到的索引来说都会预先计算一下需要扫描的二级索引记录的数量比方说对于下边这个查询SELECT * FROM s1 WHERE key1 IS NULL;优化器会分析出此查询只需要查找key1值为NULL的记录然后访问一下二级索引idx_key1看一下值为NULL的记录有多少如果符合条件的二级索引记录数量较少那么统计结果是精确的如果太多的话会采用一定的手段计算一个模糊的值当然算法也比较麻烦我们就不展开说了小册里有说这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然对于某些查询比方说WHERE子句中有IN条件并且IN条件中包含许多参数的话比方说这样SELECT * FROM s1 WHERE key1 IN (a, b, c, ... , zzzzzzz);这样的话需要统计的key1值所在的区间就太多了这样就不能采用index dive的方式去真正的访问二级索引idx_key1而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多。反正不论采用index dive还是依据统计数据估算最终要得到一个需要扫描的二级索引记录条数如果这个条数占整个记录条数的比例特别大那么就趋向于使用全表扫描执行查询否则趋向于使用这个索引执行查询。理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!这些条件仍然可以使用索引本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。不信谣不传谣大家可以看到MySQL中决定使不使用某个索引执行查询的依据很简单就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!这些条件。大家以后也多多辟谣吧没那么复杂只是一个成本而已。欢迎希望文章对你有帮助喜欢的可以关注作者给个赞哦
