mysql - 多列上的多个索引

Question

假设我有一个大小适中的 MySQL 表(我们将其称为 departments)，其中有一堆像这样聚集在一起的列:

部门表:

| id | ds_settings | ds_reports | sales_settings | sales_reports | eng_settings | eng_reports | ops_settings | ops_reports | queryable_id | queryable_type |
|----|-------------|------------|----------------|---------------|--------------|-------------|--------------|-------------|--------------|----------------|

就列而言，我们有“设置”和“报告”。查询此表时，它通常只查找给定“可查询”ID 和类型的所有设置或报告。

所以对这个表的大多数查询最终会看起来像这样:

SELECT ds_settings, sales_settings, eng_settings, ops_settings
    FROM departments
    where queryable_id = 1
      AND queryable_type = "User"

为什么问题是，索引这个表的正确方法是什么？包含一个包含所有“设置”和所有“报告”的索引是否具有设计意义，例如:

UNIQUE KEY `index_on_settings` (`queryable_id`,`queryable_type`,
                   `ds_settings`,`sales_settings`,`eng_settings`)

...或者这是对复合索引应该如何工作的误解？

Answer 1

考虑键时，应按顺序将以下元素用于索引。字段用于:

• 加入
• 哪里(常量字段)
• 哪里(范围字段)
• 分类
• 分组依据

在这种情况下，您通过常量查找值按两个字段进行搜索，因此将它们保留为索引。无需施加唯一约束。

虽然您可以在索引中包含检索到的字段，但它的缺点是会增加索引中条目的大小并使搜索索引的速度变慢。如果您在一个非常常见的查询中有一个小字段，那么这可能是值得的，但是如果您的情况似乎还为时过早。

所以:

ALTER TABLE departments ADD KEY index_on_settings (queryable_id, queryable_type)

我假设 id 是主键。

推荐通读https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html .这里还有一个关于索引使用的很好的介绍 https://github.com/jynus/query-optimization .