mysql对NULL值是怎么存储的
在mysql优化中我们建议将表中的字段设置为非NULL
理由是
- 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间
- 进行比较和计算时要对 NULL 值做特别的处理
还有这里需要澄清的是一个谣言，在此之前一直坚信的一点
就是对字段进行!= 、not null 、 <>判断会导致该字段的索引失效！
为什么说这个是谣言呢？
因为你肯定没有自己动手试试，自己用explain语句试一下就知道了
当然我们今天更想知道的是对于一个NULL值mysql是怎么存储的，以及查询的时候对于NULL索引是怎么查询的？
1、NULL值是怎么在记录中存储的
在MySQL中，每一条记录都有它固定的格式，我们以InnoDB存储引擎的Compact行格式为例，来看一下NULL值是怎样存储的。在Compact行格式下，一条记录是由下边这几个部分构成的：
我们举例说明
首先创建表record_format_demo
1. CREATE TABLE record_format_demo (
2. c1 VARCHAR(10),
3. c2 VARCHAR(10) NOT NULL,
4. c3 CHAR(10),
5. c4 VARCHAR(10)
6. ) CHARSET=ascii ROW_FORMAT=COMPACT; 是的，没错，我们需要关注的就是每行记录的额外信息NULL值列表
存储NULL值的过程如下：
1）首先统计表中允许存储NULL的列有哪些。
比方说表record_format_demo的3个列c1、c3、c4都是允许存储NULL值的，而c2列是被NOT NULL修饰，不允许存储NULL值。
2）如果表中没有允许存储NULL的列，则NULL值列表也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列
因为表record_format_demo有3个值允许为NULL的列，所以这3个列和二进制位的对应关系就是这样：
再一次强调，二进制位按照列的顺序逆序排列，所以第一个列c1和最后一个二进制位对应。
3）设计InnoDB的大牛规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。
表record_format_demo只有3个值允许为NULL的列，对应3个二进制位，不足一个字节，所以在字节的高位补0，效果就是这样
假设我们现在向record_format_demo表中插入一条记录：
1. INSERT INTO record_format_demo(c1, c2, c3, c4)
2. VALUES('eeee', 'fff', NULL, NULL); 这条记录的c1、c3、c4这3个列中c3和c4的值都为NULL，所以这3个列对应的二进制位的情况就是：
二进制位表示的意义如下：
- 二进制位的值为1时，代表该列的值为NULL。
- 二进制位的值为0时，代表该列的值不为NULL。
所以这记录的NULL值列表用十六进制表示就是：0x06。
所以存储NULL需要额外的空间就是因为需要存储这个NULL值列表
因此建议字段设置为非NULL，如果那个字段确实没有值，可以用’ ’填充
因为’ ’是不会占据空间的。
2、键值为NULL的记录（索引）是怎么在B+树中存放的
对于InnoDB存储引擎来说，记录都是存储在页面中的（一个页面默认是16KB大小），这些页面可以作为B+树的节点而组成一个索引，类似这种样子：
聚簇索引和二级索引都对应着像上图一样的B+树（也就是说有多少个索引就有多少棵对应的B+树），不过：
1）对于聚簇索引来说，B+树每一层节点（页面）都是按照页中记录的主键值大小进行排序的；而对于二级索引来说，B+树每一层节点（页面）都是按照页中记录的给定的索引列的值进行排序的。
2）对于聚簇索引来说，B+树叶子节点对应的页面中存储的是完整的用户记录（就是一条记录中包含我们定义的所有列值，还包含一些InnoDB自己添加的一些隐藏列）；而对于二级索引来说，B+树叶子节点对应的页面中存储的只是索引列的值 + 主键值。
那么首先对于主键（聚簇索引）来说，肯定不会有NULL值的
如果你执行这样的语句
// 不会执行的语句
1. SELECT * FROM tbl_name WHERE primary_key IS NULL; 语句优化器自己就能判定出WHERE子句必定为NULL，所以压根儿不会去执行它
其次就是二级索引，索引列的值可能为NULL。那对于索引列值为NULL的二级索引记录来说，它们被放在B+树的哪里呢？
答案是：放在B+树的最左边。
比如我们对一个key做NULL值查询
值为NULL的二级索引记录都被放在了B+树的最左边，这是因为设计InnoDB的大牛有这样的规定：
1. We define the SQL null to be the smallest possible value of a field.也就是说他们把SQL中的NULL值认为是列中最小的值。
到此，我们已经成功辟谣并且找到了依据了。
那么我们平时说的索引失效一般在什么情况下发生呢？
3、使不使用索引的依据到底是什么？
答案很简单：成本。
对于使用二级索引进行查询来说，成本组成主要有两个方面：
1）读取二级索引记录的成本
2）将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。
很显然，要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）。
所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：
// 查询空语句
1. SELECT * FROM s1 WHERE key1 IS NULL; 优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦，我们就不展开说了），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。
当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，
比方说这样：
// in查询语句
1. 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、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。
