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

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

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