MySQL默认值NULL、空值、Empty String的区别，哪个更好？

当我们在数据库中添加一个新的字段时，通常会面临选择：应该设置默认值为NULL、空值，还是Empty String（空字符串）？这篇文章将帮助你理解这些选择之间的差异及其影响

三种值的介绍

1. 空值：

   • 空值（空白）通常意味着字段没有被显式设置。当在设计表结构时保存空值，它通常会自动转变为NULL。

2. NULL：

   • NULL表示未知或未定义的值。它在数据库中是一个特殊的标记，不等同于空字符串或零。

3. Empty String：

   • Empty String是一个长度为0的字符串，表示字段已被定义为一个空的文本值，即''或""。

NOT NULL的好处

1. 节省空间：

   • NULL列需要额外的字节来存储是否为NULL的标志位，因此使用NOT NULL可以节省空间。

2. 减少空指针问题：

   • 查询时，可以减少与NULL相关的空指针异常问题。

3. 减少计算错误：

   • 在统计时，NULL值不会被计入，例如COUNT(column)会忽略NULL值，这可能导致意外的结果。

设置为NULL的坏处

1. 索引效率：

   • 含有NULL值的列在查询优化时较困难，索引中不存储NULL值，这可能导致索引效率下降。建议使用0、特殊值或空字符串代替。

2. 负向条件查询：

   • 使用!=或NOT IN时，NULL值可能导致查询结果为空，容易引发错误。

3. 占用空间：

   • NULL虽然不占据数据存储空间，但需要额外字节标记，空字符串''不需要这个标记。

4. 统计问题：

   • COUNT()统计时忽略NULL，但不忽略空字符串，这可能导致统计分析误差。

5. 查询复杂性：

   • 在SQL中，判断NULL需要使用IS NULL或IS NOT NULL，而空字符串则可以用常规比较操作符。

综上建议

• 字符串类型字段：推荐设置默认值为空字符串''。这样可以避免NULL带来的复杂性，并确保字段始终有可比较的值。
• 整数类型字段：建议设置默认值为0。这避免了处理NULL整数值的复杂性，并提供一个明确的初始值。

扩展

在MySQL中，WHERE <column> IS NOT NULL这种查询条件通常会导致全表扫描（full table scan），即使该列上存在索引。这主要是由于以下几个原因：

索引查找和全表扫描的区别

1. 索引结构：

   • 索引是按照特定顺序（如B-Tree）存储的，主要用于高效查找特定值或范围内的值。
   • IS NOT NULL需要查找所有非NULL值，这实际上是一个范围查询，可能分布在整个表的数据页中。

2. 数据分布：

   • IS NOT NULL条件需要遍历所有可能的行以确定非NULL值。这涉及大量随机I/O操作，可能导致性能问题。
   • 即使索引中存储了NULL和非NULL值，但为了查找所有非NULL值，仍然会造成大量随机读写，效率不高。

执行计划

通过查看执行计划（EXPLAIN），我们可以理解MySQL优化器如何处理这种查询。假设我们有如下表结构和索引：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) DEFAULT NULL
);

CREATE INDEX idx_username ON users(username);

使用EXPLAIN查看查询计划：

EXPLAIN SELECT * FROM users WHERE username IS NOT NULL;

通常结果会显示：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | idx_username  | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+------+-------+

解释

• type = ALL：

  • ALL表示全表扫描。
  • 即使possible_keys显示idx_username为可能使用的索引，但优化器认为全表扫描更有效。

• rows：

  • 估计需要扫描的行数，这个数值较大，表明全表扫描。

为什么全表扫描？

1. 覆盖范围广：

   • IS NOT NULL条件覆盖了所有非NULL值，索引无法有效地利用区间信息进行高效查找。

2. 数据访问模式：

   • 索引通常用于查找特定值或较小范围内的值。
   • 对于大量分布广泛的数据，索引查找效率较低，因为需要多次随机访问数据页。

3. 优化器选择：

   • MySQL优化器基于统计信息选择最优查询方案。
   • 在数据分布和索引效能较差的情况下，全表扫描可能会比索引查找更快。

解决方案

• 若查询非NULL值频繁且数据量大，可以考虑业务逻辑调整或设计更适合的索引方案。
• 例如，新增一个标志字段，明确区分NULL和非NULL状态，通过该标志进行查询。

示例改进

ALTER TABLE users ADD COLUMN is_username_not_null TINYINT(1) GENERATED ALWAYS AS (username IS NOT NULL);

CREATE INDEX idx_is_username_not_null ON users(is_username_not_null);

EXPLAIN SELECT * FROM users WHERE is_username_not_null = 1;

这种方式通过增加生成列和索引，优化查询性能。

总结

• IS NOT NULL条件查询通常会导致全表扫描，即使存在索引。
• 数据分布、访问模式和优化器选择是主要原因。
• 根据实际需求设计合适的索引和表结构，可以提高查询性能。