索引列和顺序

Question

如果我有一个类似下面语句的select语句，应该按什么顺序和包含哪些列来创建索引呢？

SELECT MIN(BenchmarkID),
       MIN(BenchmarkDateTime),
       Currency1,
       Currency2,
       BenchmarkType
FROM Benchmark
       INNER JOIN MyCurrencyPairs ON Currency1 = Pair1
                            AND Currency2 = Pair2
WHERE BenchmarkDateTime > IN_BeginningTime
GROUP BY Currency1, Currency2, BenchmarkType;

需要注意的事项：

• 基准表将有数十亿行记录
• MyCurrencyPairs表是一个本地表，记录不到10个
• IN_BeginningTime 是一个输入参数
• 列 Currency1 和 Currency2 是 VARCHAR 类型
• 列 BenchmarkID 和 BenchmarkType 是 INT 类型
• 列 BenchmarkDateTime 是 datetime类型（希望这一点很明显）

我已经创建了一个索引，包括 Currency1、Currency2、BenchmarkType、BenchmarkDateTime 和 BenchmarkID，但是我没有得到我想要的速度。 我能创建更好的索引吗？

---

编辑 #1：有人请求下面的解释结果。如果还需要其他信息，请让我知道。

---

编辑 #2：有人请求这两个表的DDL(我假设这是create语句)：

（此基准表存在于数据库中）

CREATE TABLE `benchmark` (
    `SequenceNumber` INT(11) NOT NULL,
    `BenchmarkType` TINYINT(3) UNSIGNED NOT NULL,
    `BenchmarkDateTime` DATETIME NOT NULL,
    `Identifier` CHAR(6) NOT NULL,
    `Currency1` CHAR(3) NULL DEFAULT NULL,
    `Currency2` CHAR(3) NULL DEFAULT NULL,
    `AvgBMBid` DECIMAL(18,9) NOT NULL,
    `AvgBMOffer` DECIMAL(18,9) NOT NULL,
    `AvgBMMid` DECIMAL(18,9) NOT NULL,
    `MedianBMBid` DECIMAL(18,9) NOT NULL,
    `MedianBMOffer` DECIMAL(18,9) NOT NULL,
    `OpenBMBid` DECIMAL(18,9) NOT NULL,
    `ClosingBMBid` DECIMAL(18,9) NOT NULL,
    `ClosingBMOffer` DECIMAL(18,9) NOT NULL,
    `ClosingBMMid` DECIMAL(18,9) NOT NULL,
    `LowBMBid` DECIMAL(18,9) NOT NULL,
    `HighBMOffer` DECIMAL(18,9) NOT NULL,
    `BMRange` DECIMAL(18,9) NOT NULL,
    `BenchmarkId` INT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`BenchmarkId`),
    INDEX `NextBenchmarkIndex01` (`Currency1`, `Currency2`, `BenchmarkType`),
    INDEX `NextBenchmarkIndex02` (`BenchmarkDateTime`, `Currency1`, `Currency2`, `BenchmarkType`, `BenchmarkId`),
    INDEX `BenchmarkOptimization` (`BenchmarkType`, `BenchmarkDateTime`, `Currency1`, `Currency2`)
)

（我正在创建“我的货币对”表格在我的例程中）

CREATE TEMPORARY TABLE MyCurrencyPairs
    (
        Pair1 VARCHAR(50),
        Pair2 VARCHAR(50)
    ) ENGINE=memory;
  CREATE INDEX IDX_MyCurrencyPairs ON MyCurrencyPairs (Pair1, Pair2);

- Miles

---

你能运行一个 EXPLAIN <SELECT STATEMENT HERE> 并发布结果吗？ - FreudianSlip

谢谢您的解释。Currency1和2字段，它们是varchar类型有什么原因吗？即，它们包含文本吗？如果是这样，是否有任何方法可以将它们转换为查找表，以便可以将字段类型更改为INT？例如，“GBP”=> 1，“USD”=> 2 - FreudianSlip

很遗憾，这个系统已经存在一段时间了，这将是一个重大的改变。我希望最初的架构师能够这样做...不知道为什么你会尝试在VARCHAR上匹配所有内容..... - Miles

在这种情况下，我已经没有更多的想法了 - 据我所知，你的索引似乎是正确的。也许需要对关键缓冲区进行一些服务器调整？ - FreudianSlip

你能否发布这些表的创建DDL吗？ - Justin Swartsel

Answer 1

BenchMarkDateTime 应该是您索引中的第一列。

规则是，如果您仅使用复合索引的一部分，则使用的部分应为前导部分。

其次，Group By 应该与索引匹配。

如果您的查询可以使用“=”而不是范围检查查询“>”，则性能会更好。

- srini.venigalla

Answer 2

主要问题在于MySQL不能直接使用索引来处理聚合。这是由于与 MyCurrencyPairs 的连接以及您要求 MIN(BenchmarkId)，同时也有关于 BenchmarkDateTime 的范围条件。为了得到更好的执行计划，这两个条件需要被消除。

让我们先看一下所需的索引和相应的查询：

ALTER TABLE benchmark
  ADD KEY `IDX1` (
    `Currency1`,
    `Currency2`,
    `BenchmarkType`,
    `BenchmarkDateTime`
  ),
  ADD KEY `IDX2` (
    `Currency1`,
    `Currency2`,
    `BenchmarkType`,
    `BenchmarkId`,
    `BenchmarkDateTime`
  );

SELECT
  (
    SELECT
      BenchmarkId
    FROM
      benchmark FORCE KEY (IDX2)
    WHERE
      Currency1 = ob.Currency1 AND
      Currency2 = ob.Currency2 AND
      BenchmarkType = ob.BenchmarkType
      AND BenchmarkDateTime > IN_BeginningTime
    ORDER BY
      Currency1, Currency2, BenchmarkType, BenchmarkId
    LIMIT 1
  ) AS BenchmarkId
  ob.*
FROM
  (
    SELECT
      MIN(BenchmarkDateTime),
      Currency1,
      Currency2,
      BenchmarkType
    FROM
      benchmark
    WHERE
      BenchmarkDateTime > IN_BeginningTime
    GROUP BY
      Currency1, Currency2, BenchmarkType
  ) AS ob
INNER JOIN
  MyCurrencyPairs ON Currency1 = Pair1 AND Currency2 = Pair2;

第一个变化是GROUP BY部分在自己的子查询中进行。这意味着它生成了所有Currency1，Currency2，BenchmarkType的组合，即使它们没有出现在MyCurrencyPairs中，但除非有大量的组合，否则MySQL现在可以使用索引执行操作，这应该会更快。这个子查询使用IDX1而不需要临时表或文件排序。

第二个变化是将MIN(BenchmarkId)部分隔离到自己的子查询中。那个子查询中的排序可以使用IDX2来处理，因此这里也不需要排序。FORCE KEY (IDX2)提示以及即使“固定值”列Currency1，Currency2和BenchmarkType出现在ORDER部分中也是必需的，以使MySQL优化器做正确的事情。同样，这是一个权衡。如果最终结果集很大，子查询可能会导致损失，但我认为行数不会太多。

解释该查询会得到以下查询计划（为了可读性省略了不相关的列）：

+----+--------------------+-----------------+-------+---------+------+---------------------------------------+
| id | select_type        | table           | type  | key_len | rows | Extra                                 |
+----+--------------------+-----------------+-------+---------+------+---------------------------------------+
|  1 | PRIMARY            | <derived3>      | ALL   | NULL    | 1809 |                                       |
|  1 | PRIMARY            | MyCurrencyPairs | ref   | 106     |    2 | Using where                           |
|  3 | DERIVED            | benchmark       | range | 17      | 1225 | Using where; Using index for group-by |
|  2 | DEPENDENT SUBQUERY | benchmark       | ref   | 9       |  520 | Using where; Using index              |
+----+--------------------+-----------------+-------+---------+------+---------------------------------------+

我们可以看到所有有趣的部分都被索引正确覆盖，我们既不需要临时表也不需要文件排序。

在我的测试数据上，这个版本的速度大约是之前的20倍（1.07秒对比0.05秒），但是我的基准表中只有大约120万行，并且数据分布可能存在偏差，所以你的情况可能会有所不同。

- Björn Steinbrink

---

即使这对你不起作用，我仍然感激知道我们谈论的是什么时间。我猜它不完全在1秒范围内，但我们是否仍在讨论几秒钟，还是几分钟甚至几小时？ - Björn Steinbrink