未使用带有 WHERE 子句和 ORDER BY 的查询索引

Question

我创建了一个表，如下所示

CREATE TABLE IF NOT EXISTS
`table`
  (
     id          VARCHAR(100) NOT NULL,
     seq_id      BIGINT UNSIGNED NOT NULL,
     in_use      BOOLEAN NOT NULL DEFAULT false,
     scheduled   BOOLEAN NOT NULL DEFAULT false,
     dialed      BOOLEAN NOT NULL DEFAULT false,
     priority    INT UNSIGNED NOT NULL DEFAULT 0,
     data_0      VARCHAR(100) NULL,
     data_1      VARCHAR(100) NULL,
     data_2      VARCHAR(40) NULL,
     data_3      VARCHAR(200) NULL,
     data_4      VARCHAR(10) NULL,
     data_5      DECIMAL(65, 20) NULL,
     data_6      DECIMAL(65, 20) NULL,
     PRIMARY KEY (`id`)
  )

有一个大型查询根据 where 子句选择行，然后对结果进行排序。

SELECT id
FROM
`table`
WHERE  ( dialed = false
         AND in_use = false
         AND scheduled = false )
ORDER  BY priority DESC,
          data_6 ASC,
          data_5 DESC,
          data_4 ASC,
          data_3 DESC,
          seq_id
LIMIT  100

我试图通过运行 EXPLAIN 来找到该查询的最佳索引。 我创建了一些不同的索引；

1. （已拨打、正在使用、已安排、优先级、data_6、data_5、data_4、data_3、seq_id）
2. （优先级、data_6、data_5、data_4、data_3、seq_id）
3. （已安排、正在使用、已拨打、优先级、data_6）
4. （预定，使用中）

EXPLAIN 查询显示以下内容；

possible_keys: [index1],[index3],[index4]         
key: [index4]
key_len: 2
ref: const, const
rows: 448
filtered: 100.0
Extra: Using index condition; Using where; Using filesort

我很好奇为什么不使用包含 ORDER BY 列（index1 和 index3）的索引，以及为什么它选择仅包含 WHERE 子句列的子集的索引？我认为索引 1 具有查询的完整列覆盖率，是理想的选择。

仅覆盖 ORDER BY 列的索引 (index2) 根本没有显示在 possible_keys 中。我在这里定义索引的顺序是否错误？

查询是否可以使用一个索引进行过滤，然后使用另一个索引对结果进行排序？

如您所见，我正在运行 448 行的测试。该查询可以在更大的表上运行；高达一百万。对于更大的表，其他索引最终会比索引4 性能更高吗？

最后，像索引 1 这样具有许多列的索引是否会仅仅因为列数而降低性能？

Answer 1

3种可能：

如果这个组合选择性“足够”，那么它是有用的：INDEX(dialed, in_use, Schedule)。这 3 个的顺序并不重要。

如果您使用 MySQL 8.0，那么这可能很有用（按给定的顺序）：

INDEX(priority DESC,
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)

旧版本忽略 DESC，使它们不使用 INDEX

甚至（再次在 8.0 上）：

INDEX(dialed, in_use, schedule,   -- in any order
      priority DESC,    -- the rest, as specified
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)