仅用于 IS NULL 和 IS NOT NULL 的列创建索引

Question

我有一张表，其中有一个已删除的列。在每个SQL语句中，我都会检查这个 标记 是否为空。如果有人想要删除条目，则将该标记设置为当前时间戳。

如果需要恢复条目，则使用此时间戳来还原它们。这是唯一使用此列值的用例。

在所有其他情况下，重要的是只知道它是否为空或不为空。

未来，该表可能会包含数百万行记录。

在此列上创建索引是否有用？因为99％的语句和用例与该值无关。

MySQL是否优化IS NULL条件，因此不需要索引？

- lszrh

Answer 1

在“deleted”上创建的索引也会对null值进行索引，从而允许更快地查找非null / null。我认为在这种情况下这将是足够的，并且不会引起太多的开销，因为时间戳是在删除时设置的，因此不会经常更改。（相反：使用一个编辑时间戳，它会经常更改并且仅有时设置为空，将导致每次记录更改时都要调整索引。那可能不是最优的。这里不是这种情况。）（还有一点，但我不知道索引器是否聪明到能够利用它，预期的更改总是在索引的末尾，无论在空端还是在“最近”端。当然，需要进行剖析（如果重要的话，包括查询执行时间和存储空间）以查找是否存在实际问题。

- Inca

Answer 2

你不能创建一个“档案”表格，用它来存储已删除的行和它们的时间戳吗。 如果用户想要恢复一行，你只需将它从档案转移到主表中。

这样你就不必在每个查询中检查“flag IS NOT NULL”了。

- j_freyre

---

这是一个极其幼稚的建议。您将会破坏所有外键或在归档表中进行复制时遇到严重的问题。此外，当从序列中取消删除时，您将获得不同的主键，这将破坏任何现有的URI，除非您专门使用slug。 - davidtgq

Answer 3

根据这本书(高性能MySQL, 第二版)，在MySQL的列定义中不建议使用"允许NULL"。MySQL会使用额外的字节来存储单元格的状态(Null或Not Null)，索引大小将比没有"允许NULL"的情况更大。更好的解决方案是将行TINYINT数据类型，并为活动行存储值1，对于删除的行存储值0。因此，建议永远不要在列定义中使用"允许NULL"。

- Alexey Churkin

---

需要额外的空间，是的。但速度呢？检查 IS NULL 还是检查 = 0 更快？还是这只是微不足道的优化？ - lszrh

永远不要使用NULL？那么如果需要将数据导入到需要为1或0的列中，但您还需要知道是否曾经存在任何值呢？NULL是有其存在的理由的，应在合适的情况下使用。不使用NULL可能会在检查数据完整性时造成严重问题，特别是对于可能包含空格的文本列。除非有令人信服的理由不使用它，否则我总是将NULL用作默认值。 - photocode