MySQL中NULL值的存在对索引和查询有何影响？

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

IS NULL

此外，NULL值在比较操作中的行为也与众不同。

NULL = NULL

UNKNOWN

WHERE column = NULL

IS NULL

IS NOT NULL

COUNT()

SUM()

AVG()

COUNT(column_name)

COUNT(*)

为什么NULL值在B-tree索引中表现特殊？

这确实是一个值得深思的问题，因为它直接关系到我们如何设计表结构和优化查询。从B-tree索引的内部机制来看，它的核心是维护一个有序的键值对结构，以便通过二分查找等方式快速定位数据。然而，NULL值并没有一个明确的“值”来参与这种排序。它既不大于任何值，也不小于任何值，甚至不等于自身。

因此，大多数B-tree实现，包括MySQL的InnoDB存储引擎，在索引中处理NULL值时会采取一种特殊的策略。通常，它们不会将NULL作为独立的键值存储在索引树的叶子节点中。取而代之的是，对于允许NULL值的列，索引可能会在内部使用一个特殊的标记或者在索引项中不包含该列的值。这就意味着，当你的查询涉及到

IS NULL

举个例子，如果你有一个

email

SELECT * FROM users WHERE email IS NULL;

email

SELECT * FROM users WHERE email = 'test@example.com';

另一个值得注意的是，组合索引中如果某个列允许NULL，并且NULL值出现在了索引的前导列，那么这个索引的效率会大打折扣。比如，索引是

(col1, col2)

col1

col2

col1

处理NULL值时，哪些常见的SQL陷阱需要警惕？

在日常开发中，NULL值就像一个隐形的“坑”，稍不留神就可能踩进去。我个人就遇到过好几次因为对NULL值理解不到位而导致的生产问题。

一个最常见的陷阱就是

NOT IN

SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM blacklist WHERE status = 'active');

blacklist

id

NOT IN

NOT IN

UNKNOWN

TRUE

blacklist

DeepBrain

AI视频生成工具，ChatGPT +生成式视频AI =你可以制作伟大的视频!

108

查看详情

解决这个问题的常见方法是确保子查询结果不包含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的处理差异。我们知道

COUNT(*)

COUNT(column_name)

column_name

COUNT(column_name)

COUNT(*)

SUM()

AVG()

MIN()

MAX()

最后，

NULL = NULL

UNKNOWN

WHERE

=

!=

>

<

WHERE col = NULL

IS NULL

IS NOT NULL

UNKNOWN

如何优化包含NULL值的查询性能？

既然NULL值有这么多“脾气”，那我们肯定要想办法驯服它，或者至少找到与它和谐共处的方式。优化包含NULL值的查询，我认为可以从几个层面入手。

首先，在表设计阶段就进行权衡。如果一个列的NULL值表示“无意义”或“尚未设置”，并且这个列会频繁参与查询，那么可以考虑是否能用一个默认值来替代NULL。例如，用空字符串

''

0

'1970-01-01'

其次，针对

IS NULL

IS NOT NULL

1. 添加冗余列并索引：这听起来有点“笨”，但在某些场景下非常有效。你可以为原列

   col

   登录后复制
   增加一个布尔类型的辅助列，比如

   col_is_null

   登录后复制
   ，并给它设置默认值

   TRUE

   登录后复制
   或

   FALSE

   登录后复制
   。然后在这个

   col_is_null

   登录后复制
   列上建立索引。这样，当查询

   WHERE col IS NULL

   登录后复制
   时，就可以改写为

   WHERE col_is_null = TRUE

   登录后复制
   ，从而利用到

   col_is_null

   登录后复制
   上的索引。
2. MySQL 8.0+ 的函数索引：这是一个非常强大的功能。你可以直接为表达式创建索引，例如

   CREATE INDEX idx_col_is_null ON my_table ((col IS NULL));

   登录后复制
   。这样，

   WHERE col IS NULL

   登录后复制
   的查询就能直接利用这个函数索引，效率会大大提升。
3. 部分索引（Partial Index）：虽然MySQL本身没有直接支持PostgreSQL那样的部分索引，但可以通过一些技巧实现类似效果。例如，对于

   NOT NULL

   登录后复制
   的查询，如果大部分数据是非NULL的，那么在

   col

   登录后复制
   上建立常规索引依然有效。
4. 覆盖索引：如果你的查询只需要获取被索引的列，并且这些列包含了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

IS NULL

EXPLAIN

EXPLAIN

以上就是MySQL中NULL值的存在对索引和查询有何影响？的详细内容，更多请关注php中文网其它相关文章！