null值在mysql中影响索引效率和查询逻辑，b-tree索引通常不存储null导致is null查询难以利用索引，且null参与比较时结果为unknown，易引发not in陷阱、聚合函数误用等问题；优化方式包括使用默认值替代null、创建函数索引或冗余列索引、重写查询为not exists或union all，并结合explain分析执行计划。
MySQL中NULL值的存在，对索引和查询的影响可以说是一把双刃剑，它既是数据模型中不可或缺的一部分，用于表示缺失或未知，但也可能成为性能瓶颈和逻辑陷阱的根源。在我看来，理解NULL值的行为模式，是写出高效、健壮SQL查询的关键一步。它不像一个简单的0或空字符串，它的语义更复杂，处理起来也需要格外小心。
解决方案
当我们在MySQL中处理NULL值时，核心问题在于其“未知”的特性以及索引结构对其的特殊处理。B-tree索引，作为MySQL中最常见的索引类型，其设计初衷是为了快速查找、排序和范围扫描有序的数据。然而，NULL值天生就是“无序”的，或者说，它的排序位置是特殊的，且不参与常规的比较操作。
具体来说，对于B-tree索引，它通常不会直接存储NULL值作为索引键的一部分。这意味着，如果你的查询条件是
WHERE column IS NULL或
WHERE column IS NOT NULL，MySQL往往难以有效地利用该列上的B-tree索引。它可能需要扫描整个索引，甚至回表进行过滤，这无疑会增加查询成本。例如，在一个包含大量NULL值的列上建立索引，并频繁执行
IS NULL查询时，你会发现索引几乎形同虚设。
此外，NULL值在比较操作中的行为也与众不同。
NULL = NULL的结果不是TRUE或FALSE，而是
UNKNOWN。这导致了许多开发者初次接触时会犯的错误，比如使用
WHERE column = NULL来查找NULL值，这永远不会返回任何结果。正确的做法是使用
IS NULL或
IS NOT NULL。聚合函数（如
COUNT()、
SUM()、
AVG()）在处理NULL时也有其独特的规则，比如
COUNT(column_name)会忽略NULL值，而
COUNT(*)则会包含NULL值的行。这些细节如果不注意，都可能导致查询结果不准确或性能下降。
为什么NULL值在B-tree索引中表现特殊？
这确实是一个值得深思的问题，因为它直接关系到我们如何设计表结构和优化查询。从B-tree索引的内部机制来看，它的核心是维护一个有序的键值对结构，以便通过二分查找等方式快速定位数据。然而，NULL值并没有一个明确的“值”来参与这种排序。它既不大于任何值，也不小于任何值，甚至不等于自身。
因此，大多数B-tree实现，包括MySQL的InnoDB存储引擎，在索引中处理NULL值时会采取一种特殊的策略。通常，它们不会将NULL作为独立的键值存储在索引树的叶子节点中。取而代之的是，对于允许NULL值的列，索引可能会在内部使用一个特殊的标记或者在索引项中不包含该列的值。这就意味着，当你的查询涉及到
IS NULL时，数据库系统无法通过常规的B-tree遍历来快速定位这些行。它可能需要扫描索引的所有叶子节点，或者在某些情况下，如果优化器判断全表扫描更优，甚至会放弃索引。
举个例子，如果你有一个
SELECT * FROM users WHERE email IS NULL;时，MySQL可能无法直接跳到索引中存储NULL值的位置。它可能需要遍历索引的所有条目，检查每一行对应的
SELECT * FROM users WHERE email = 'test@example.com';形成鲜明对比，后者可以通过B-tree快速定位到精确的键值。
另一个值得注意的是，组合索引中如果某个列允许NULL，并且NULL值出现在了索引的前导列，那么这个索引的效率会大打折扣。比如，索引是
(col1, col2)，如果
col1为NULL，那么
col2的索引作用就很难发挥出来，因为整个索引的有序性在
col1处就已经被“打破”了。
处理NULL值时，哪些常见的SQL陷阱需要警惕？
在日常开发中，NULL值就像一个隐形的“坑”，稍不留神就可能踩进去。我个人就遇到过好几次因为对NULL值理解不到位而导致的生产问题。
一个最常见的陷阱就是NOT IN
子句与NULL的组合。假设你有一个查询：
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM blacklist WHERE status = 'active');如果
blacklist表中的
id列，或者子查询的结果集中，包含任何一个NULL值，那么整个
NOT IN条件将永远不会返回任何行。这是因为
NOT IN的逻辑是“不等于列表中的任何一个值”。如果列表包含NULL，那么“不等于NULL”的结果是
UNKNOWN，而不是
TRUE，所以整个条件链就断裂了。这是一个非常隐蔽且危险的陷阱，因为在开发测试时，
blacklist可能没有NULL，一旦上线数据出现NULL，查询就“失效”了。
解决这个问题的常见方法是确保子查询结果不包含NULL，例如：
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM blacklist WHERE status = 'active' AND id IS NOT NULL);或者，更推荐使用
NOT EXISTS：
SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM blacklist WHERE status = 'active' AND orders.customer_id = blacklist.id);
NOT EXISTS在处理NULL时行为更符合直觉。
另一个陷阱是聚合函数对NULL的处理差异。我们知道
COUNT(*)会统计所有行，包括那些包含NULL值的行。但
COUNT(column_name)只会统计
column_name非NULL的行。如果你想计算某个属性的有效值数量，用
COUNT(column_name)是正确的。但如果你误用
COUNT(*)并期望它只统计非NULL的特定列，结果就会出乎意料。同样，
SUM()、
AVG()、
MIN()、
MAX()这些函数也会自动忽略NULL值。这在统计数据时非常有用，但也意味着如果你的数据中存在NULL值，这些聚合结果可能与你直观上“所有行”的预期不符。
最后，NULL = NULL
的结果是UNKNOWN
，这个特性在
WHERE子句中尤其需要注意。我们不能用
=、
!=、
>、
<等比较运算符来直接判断NULL。例如，
WHERE col = NULL永远不会匹配到任何行。必须使用
IS NULL或
IS NOT NULL。在复杂的条件组合中，
UNKNOWN这个中间状态可能会导致整个表达式的结果变得不可预测，从而过滤掉本应包含的行，或者包含不应有的行。
如何优化包含NULL值的查询性能？
既然NULL值有这么多“脾气”，那我们肯定要想办法驯服它，或者至少找到与它和谐共处的方式。优化包含NULL值的查询，我认为可以从几个层面入手。
首先，在表设计阶段就进行权衡。如果一个列的NULL值表示“无意义”或“尚未设置”，并且这个列会频繁参与查询，那么可以考虑是否能用一个默认值来替代NULL。例如，用空字符串
''代替VARCHAR类型的NULL，用
0代替INT类型的NULL，或者用一个特定的日期（如
'1970-01-01'）代替DATETIME类型的NULL。这样做的优点是，这些默认值都是可索引的，并且在比较操作中行为明确。但缺点是，它可能会模糊“未知”和“空”之间的语义区别，需要谨慎。
其次，针对
IS NULL或
IS NOT NULL的查询，有几种索引优化策略：
-
添加冗余列并索引：这听起来有点“笨”，但在某些场景下非常有效。你可以为原列
col
增加一个布尔类型的辅助列，比如col_is_null
，并给它设置默认值TRUE
或FALSE
。然后在这个col_is_null
列上建立索引。这样，当查询WHERE col IS NULL
时，就可以改写为WHERE col_is_null = TRUE
，从而利用到col_is_null
上的索引。 -
MySQL 8.0+ 的函数索引：这是一个非常强大的功能。你可以直接为表达式创建索引，例如
CREATE INDEX idx_col_is_null ON my_table ((col IS NULL));
。这样，WHERE col IS NULL
的查询就能直接利用这个函数索引，效率会大大提升。 -
部分索引（Partial Index）：虽然MySQL本身没有直接支持PostgreSQL那样的部分索引，但可以通过一些技巧实现类似效果。例如，对于
NOT NULL
的查询，如果大部分数据是非NULL的，那么在col
上建立常规索引依然有效。 - 覆盖索引：如果你的查询只需要获取被索引的列，并且这些列包含了NULL值，那么即使需要扫描索引，由于不需要回表，性能也会比全表扫描好得多。确保你的索引包含了所有查询中涉及的列。
再者，查询重写也是一个重要的优化手段。
-
OR (col IS NULL)
的优化：如果你的查询条件是WHERE some_condition AND (col = 'value' OR col IS NULL)
，这通常会导致索引失效。可以考虑将其重写为UNION ALL
：SELECT * FROM my_table WHERE some_condition AND col = 'value' UNION ALL SELECT * FROM my_table WHERE some_condition AND col IS NULL;
这样，两个子查询可以分别利用各自的索引。
-
NOT IN
替换为NOT EXISTS
或LEFT JOIN ... IS NULL
：前面已经提到，这是避免NOT IN
陷阱的最佳实践，同时通常也能带来更好的性能，因为NOT EXISTS
和LEFT JOIN
在处理子查询时，优化器有更多的选择。
最后，要理解你的数据分布。如果一个列的NULL值非常少，那么
IS NULL的查询可能不会造成太大问题。但如果NULL值占据了绝大部分，那么任何涉及到
IS NULL的查询都可能成为瓶颈。结合
EXPLAIN分析查询计划，是诊断和优化NULL值相关性能问题的最直接有效的方法。通过观察
EXPLAIN的输出，你可以清楚地看到查询是否使用了索引，以及扫描了多少行，从而有针对性地进行调整。
