在一个有70%空值的列上创建索引：使用null还是空值？

Question

我们目前正在优化一个将最终拥有超过1亿条记录的MySQL表（InnoDB）。

在一列中，我们存储IP地址（VARCHAR 45），我们需要在此列上创建索引，因为我们必须能够检索每个特定IP地址的所有行。

70％的所有行，但是不会存储IP地址（为空）。

我们的问题：我们应该将这些空值存储为NULL，从而在此列上允许NULL（每行将增加1字节）。 还是不允许NULL并将这些空值存储为''（空字符串）？

对于性能来说哪种方式最好？

我们将永远不需要搜索为空的行（= ''）或null（IS NULL），只搜索特定的IP地址（= '123.456.789.123'）。

更新：确实有很多关于类似场景的SO问题。然而，一些答案似乎是矛盾的或者说“取决于情况”。我们将运行一些测试，并在此处发布我们的特定场景发现。

- Lionel

---

可能是[MySQL：NULL vs“”]的重复问题（https://dev59.com/33NA5IYBdhLWcg3wEZaT）。 - Shadow

@Shadow 是的，看起来是一个类似的问题 - 但乍一看，似乎两个得分最高的答案说的是相反的？一个说“使用null”，另一个说“不要使用null！” - Mörre

排名前两位的答案实际上并没有明确回答是或否。第三个答案则明确说明了索引。 - Shadow

@Lionel，为什么不将IP地址字段设置为varbinary(16)，并使用MySQL的inet6_aton()函数将字符串表示转换为二进制表示呢？http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet6-aton - Shadow

@Lionel 1. Inet6_aton() 可在 v5.6 中使用，并且可以使用 inet6_ntoa() 轻松将数字形式转换回人类可读格式。2. 在有关优化的问题中，很少会得到直接的答案。这里也不会给出一个。另一个主题列出了您需要考虑的所有要点，然后您需要评估在您特定的环境和数据中哪个更有效。3. 对于您来说，什么更重要：速度还是数据存储？ - Shadow

Answer 1

VARCHAR(39)可以同时容纳IPv4（旧格式，没有更多可用值）和IPv6。

如果70％的值相同（''或NULL），优化器可能会出现问题。我建议您再建一个表格，将IP和ID一起存储以便与原始表进行连接。通过在第二个表中没有“空”IP，优化器更有可能“做正确的事情”。

这样，可以使用LEFT JOIN来查看是否存在IP。

IPv6可以存储在BINARY（16）中以节省空间。

- Rick James

---

如果第二个表没有任何空的IP地址，你将不得不使用null作为它的外键，这会让你回到起点。 - user207421

这是反对外键的一个观点。它们并不在所有情况下都有用。 - Rick James

@EJP，你误解了。Rick建议使用一对一的关系，新表格中有一个指向原始表格的引用。原始表格中不会有IP或IP_id列。 - Arth

Answer 2

使用 NULL 值。对于 NULL 值，InnoDB 不需要任何空间成本，并且排除了 NULL 值的索引，因此您将更快地查找存在的值。

至于如何存储IP本身（字符串与数字），这似乎是一个不太重要的优化点。

- manchicken

---

有趣的说法，但是没有适当的参考资料，我很难批准。 - Jonathan Parent Lévesque

Answer 3

NULL和空字符串的主要区别与比较值有关。两个空字符串被认为是相等的，而两个NULL值则不是。例如，如果您想基于IP值列连接两个表，则对于NULL和空字符串，结果将大不相同，很可能您需要NULL的行为。

如果您只打算搜索特定的IP地址，则使用NULL或空字符串应该没有关系。如果IP值列已经建立索引，优化器将从InnoDB获取一个估计值，用于具有特定值的行数。在这种情况下，不会使用每个值的一般统计信息。

避免使用NULL值可以在70%的行为NULL时，在100万行中节省30 MB的空间。（对于值为空字符串的行，您将不会节省任何空间，因为您需要一个字节来存储长度信息。）与将IP值存储为二进制字符串所能节省的空间相比，这微不足道，我认为存储开销不是一个有效的问题。

- Øystein Grøvlen

---

NULL 值的空间成本仅在 MyISAM 中相关。InnoDB 对 NULL 没有空间成本。 - manchicken

InnoDB行头包含一个位向量，用于表示空列。如果没有空列，则行头将不包含此位向量。因此，与具有1-8个空列的相同表相比，没有空列的表每行将少使用1个字节。请参见https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html。 - Øystein Grøvlen