mysql - 为复杂过滤创建sql索引

Question

sql 数据库中有表人类。我有这个表的用户界面和过滤器表单，如下所示:

我只能设置一些值(例如仅年龄和状态)。如果未指定筛选项，则不会将其添加到 sql WHERE 条件中。 WHERE 条件按图片上描述的顺序组合。因此，如果我想为所有情况创建索引以获得性能提升，我需要创建以下索引:

名字

姓氏

年龄

状态

生日

性别

名字 + 姓氏

名字+姓氏+年龄

名字+姓氏+年龄+州

...

状态 + 生日

状态+生日+性别

...

州 + 性别

对我来说看起来很糟糕。我应该只选择最常用的组合吗？你怎么认为？

Answer 1

如果您有索引 first name + last name + age + state ，你也不需要 first name + last name + age和 first name + last name和 first name .如果您有索引 first name + last name + age + state并且用户仅搜索“名字”和“姓氏”，数据库将能够使用该索引。只要用户以与索引相同的从左到右的顺序指定列，即使未指定每一列，数据库也能够使用该索引。

例如，如果您有索引 first name + last name + age + state并且用户指定“名字”和“姓氏”，然后数据库将能够使用该索引跳转到匹配的行。但是，如果用户指定“名字”和“年龄”，或“名字”和“州”，那么数据库将仅部分使用索引跳转到具有匹配名字的行，但随后必须扫描与“age”或“state”匹配的行。如果您想了解这背后的技术细节，请阅读数据库索引和 B+ 树。 This是一个很好的解释。

运行单个查询时，数据库也可以使用多个索引。如果你有索引

`last name`
`state`
`age`

并且用户搜索“姓氏”、“州”和“年龄”，数据库将能够使用所有三个索引快速找到每个字段的匹配行，然后将结果组合起来，不匹配的行'不匹配所有三个索引将不会被选中。如果您查看执行计划，您将能够看到它这样做。当然，这比拥有包含所有必要字段的单个索引慢一点，但它会阻止您拥有大量索引。

另请注意，即使存在索引，数据库也不一定会使用该索引，因为进行行扫描可能会更快。例如，上面的例子有三个不同的索引，假设用户搜索“姓氏”、“名字”和“州”。由于“姓氏”和“名字”的组合具有如此高的选择性(意味着该索引中的大多数值都是唯一的)，因此仅使用索引来获取与第一个匹配的所有行可能会更快name 和 last name 然后对这些行进行简单的迭代扫描以找到那些也具有匹配状态的行，而不是使用 state索引，然后连接由两个索引返回的行。

当您设计索引时，如果索引的选择性非常低，索引不会给您带来很大的性能提升(实际上可能比执行全表扫描更糟糕)。例如，性别不是索引的好字段，因为您只有两个可能的值。如果用户只搜索性别，无论有没有索引，您都不会获得良好的性能，因为您将返回一半的行。

行对行，全表扫描实际上比使用索引更快。这样做的原因是当数据库进行表扫描时，它能够直接跳转到磁盘上的数据页。当它使用索引时，它必须经过几个中间索引页才能真正到达数据存储在磁盘上的位置。对于像“性别”这样的字段，您将选择一半的行，跟踪表中一半行的索引链接所增加的开销可能超过仅扫描整个表而不使用索引的成本。

我会推荐索引

`first name, last name`
`birthdate`
`state`

如果您有经常搜索的特定字段组合，那么您也可以为其创建索引以加快速度。但是，不要为每个字段组合都建立索引。

如果你使用“生日”而不是“生日”，那么你就不需要“年龄”，因为你可以根据“生日”来计算，然后做 between查询“生日”。如果您被迫为“生日”和“年龄”设置单独的列，那么您也可以索引“年龄”。但是，就像下面的另一位用户评论的那样，您必须不断更新您的年龄。我强烈建议反对这种设计。

最后要考虑的一件事是是否尝试制作覆盖索引。覆盖索引是指用户搜索的每个字段都是索引的一部分。例如，假设您的表中有 100 个字段，但用户通常只对根据姓名查找某人的状态和年龄感兴趣。所以你的大部分查询看起来像这样

SELECT STATE, AGE FROM PEOPLE WHERE FIRSTNAME = 'Homer' AND LASTNAME = 'Simpson'

如果您的索引是 LASTNAME, FIRSTNAME ，然后数据库将在您的索引中查找“Homer”和“Simpson”(这将涉及从磁盘读取几个索引页)，使用索引指针转到存储数据记录的磁盘页，读取整个数据页，将其解析为字段，然后返回状态和年龄。

现在，假设您运行相同的查询，但您的索引是 LASTNAME, FIRSTNAME, STATE, AGE .数据库引擎仍将使用您的索引来查找“Homer”和“Simpson”，但是一旦找到合适的索引记录(与上面的工作方式完全相同)，该索引记录就已经有了 STATE和 AGE .因此，数据库可以直接从索引中获取查询结果，而无需从磁盘读取数据页。

覆盖索引可以显着提高性能的一种情况是在表扫描的情况下。假设您的表中有 100 个字段(因此单行的大小为几百字节或更多)。现在用户运行查询

SELECT FIRSTNAME, LASTNAME, AGE FROM PEOPLE

数据库必须读取整个表(包括此查询不需要的所有 100 个字段)才能获得结果。如果您有索引 LASTNAME, FIRSTNAME, AGE ，那么数据库可以通过扫描整个索引而不是扫描整个表来获取结果。由于在这种情况下，单个索引元素比单个数据行要小得多，因此查询速度会快得多。

在您的表中字段很少的特定情况下，覆盖索引可能不会很有用，因为索引中的字段与表中的字段相同，从而破坏了整个目的。但是，对于包含数十个字段的表，其中通常只查询少数几个字段，覆盖索引可能是加快查询速度的好方法。