时间戳

首页 > 解决方案 > 多列上的多个索引

mysql - 多列上的多个索引

问题描述

假设我有一个大小合适的 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`)

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

标签： mysqldatabaseindexingcompound-index

解决方案

在考虑键时，应按顺序将以下元素用于索引。用于：

  • 加入
  • 其中（常量字段）
  • 哪里（范围字段）
  • 排序
  • 通过...分组

在这种情况下，您通过常量查找值搜索两个字段，因此将它们保留为索引。没有必要强加一个唯一的约束。

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

所以：

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。

推荐阅读