mysql - 使 MySQL 为查询选择最佳索引

Question

在 MySQL 5.6 DB 中，我有一个具有以下结构的巨大 SQL 表:

CREATE TABLE `tbl_requests` (
    `request_id` BIGINT(20) UNSIGNED NOT NULL,
    `option_id` BIGINT(20) UNSIGNED NOT NULL,
    `symbol` VARCHAR(30) NOT NULL,
    `request_time` DATETIME(6) NOT NULL,
    `request_type` SMALLINT(6) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`request_id`),
    INDEX `key_request_type_symbol` (`request_type`, `symbol`),
    INDEX `key_request_time` (`request_time`),
    INDEX `key_request_symbol` (`symbol`)
);

表中有超过 8 亿条记录，symbol 字段约有 25,000 种，request_type 中有约 100 个不同的值。我的目标是尽可能快地进行如下查询:

SELECT tbl_requests.*
FROM tbl_requests  use index (key_request_type_symbol)
-- use index (key_request_time) -- use index (key_request_type_symbol)
WHERE (tbl_requests.request_time >= '2016-02-23' AND 
       tbl_requests.request_time <= '2016-12-23') 
AND (tbl_requests.request_type IN (0, 1, 9))  
[AND (tbl_requests.symbol = 'AAPL' ... )]
ORDER BY tbl_requests.request_time DESC, tbl_requests.request_id DESC
LIMIT 0,100;

通过 tbl_requests.symbol 字段进行不同种类的过滤，从无过滤器到一组值再到一组匹配模式再到混合匹配。

我看到的是不同的索引在不同的情况下给出了最好的性能，MySQL 无法猜测哪个更好。例如，在没有过滤器的情况下，最快的是 key_request_time 索引(0.016 秒)，MySQL 正确地选择了它(EXPLAIN 命令的结果):

"id": 1,
"select_type": "SIMPLE",
"table": "tbl_requests",
"type": "range",
"possible_keys": "key_request_type_symbol,key_request_time",
"key": "key_request_time",
"key_len": "8",
"ref": null,
"rows": 428944675,
"Extra": "Using index condition; Using where"

如果使用索引 key_request_type_symbol 索引，则此查询将花费大量时间(可能是几个小时？)。

我使用语法

FROM tbl_requests use index (key_request_type_symbol)

强制使用索引。

当过滤器中使用一个符号时

AND (tbl_requests.symbol = 'BAC')

MySQL 服务器正在选择相同的 key_request_time 索引，查询时间超过 10 秒。但是如果使用 key_request_type_symbol 索引，查询大约需要 0.7 秒。此外，当使用第一个索引时，如果再次重复查询，它会持续占用 10 秒以上，而当使用第二个索引时，重复查询需要 0.1 秒。
key_request_type_symbol 索引的解释信息:

"id": 1,
"select_type": "SIMPLE",
"table": "tbl_requests",
"type": "range",
"possible_keys": "key_request_type_symbol",
"key": "key_request_type_symbol",
"key_len": "34",
"ref": null,
"rows": 17117,
"Extra": "Using index condition; Using where; Using filesort"

行数少了很多，但有文件排序。

在 key_request_type_symbol 的情况下，表中有多少匹配行很重要。对于“AMZN”符号，行数 = 79762，时间为 0.15 秒，而如果使用 key_request_time 索引，则需要 4.4 秒。但是 MySQL 比 key_request_type_symbol 更喜欢它。

在下面的例子中可以看得很清楚。如果我使用:

tbl_requests.symbol LIKE 'A%' 

使用 key_request_time 索引需要 0.172 秒。
使用 key_request_type_symbol 索引需要 173 秒。 (约慢 1000 倍)
行=6367732

对于:

tbl_requests.symbol LIKE 'AM%' 

使用 key_request_time 索引需要 0.640 秒。
使用 key_request_type_symbol 索引需要 2.2 秒。 (约慢 3 倍)
行=838822

对于:

tbl_requests.symbol LIKE 'AMZ%' 

使用 key_request_time 索引需要 4.5 秒。
使用 key_request_type_symbol 索引需要 0.15 秒。 (快约 30 倍)
行=73083

对于:

tbl_requests.symbol LIKE 'AMZN%' 

使用 key_request_time 索引需要 4.4 秒。
使用 key_request_type_symbol 索引需要 0.15 秒。 (快约 30 倍)
行=79762

此外，当使用 key_request_type_symbol 索引时，再次使用相同的符号过滤器时执行速度会更快，而 key_request_time 时序保持大致相同。

我将收到很多关于一个符号的查询，所以我需要他们尽快。但我也可能会收到由许多符号过滤的查询。如何强制服务器在每种情况下为我选择最快的方式？

我能想到的一种方法是提前发送 EXPLAIN 语句并检查 key_request_type_symbol 索引情况下的预期行数，然后修改查询以相应地使用这个或那个索引(比如，如果行数超过 300000，请使用 key_request_time)。

但也许我遗漏了什么？也许索引不正确(但我找不到更好的)？保持查询不变并强制 MySQL 足够智能以自动选择最快的方式会很好。

Answer 1

这是您遗漏的有关 MySQL 如何使用索引的规则:

1. 索引中最左边的列必须与相等比较的列匹配(例如 symbol = 'AAPL' )。您可以有多个列，只要它们都满足相等条件即可。
2. 然后索引中的单个下一列可以匹配一个列进行范围比较。范围比较不是相等。所以:<> , > , < , IN() , BETWEEN , LIKE没有前导通配符，或 IS [NOT] NULL .
3. 索引也可以用于GROUP BY或 ORDER BY ，但如果您对范围条件使用了索引，则不会。基本上，在进行相等性测试的列之后，您的索引中会多一列。

示例:假设您有一个具有以下条件的查询:

WHERE a = 1 AND b = 2 AND c > 3 AND d IN (4,5,6)

假设您在 (a, b, c, d) 上按顺序有一个索引。只有索引中的 a、b、c 列将有助于查询。由于 c 列处于不等式比较中，因此索引中的最后一列有帮助。

(实际上，InnoDB 最近有一个称为“索引条件下推”的功能，它可能允许存储引擎通过搜索 d 的值来提供更多帮助，但不要指望它与常规索引查找一样好。我在你的一个 EXPLAIN 输出中看到了注释“使用索引条件”，表明它正在使用这个特性。阅读 http://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html 了解更多细节。)

同样，由于 c 的不等式条件，此查询将无法使用 d 来避免以下查询中的文件排序。

WHERE a = 1 AND b = 2 AND c > 3
ORDER BY d

而以下将能够使用 d 来优化排序，因为一旦查询找到 c=3 的行子集，那么其余匹配项自然会按 d 顺序读取。

WHERE a = 1 AND b = 2 AND c = 3
ORDER BY d

现在了解如何将其应用于您的查询:

WHERE (tbl_requests.request_time >= '2016-02-23' AND 
       tbl_requests.request_time <= '2016-12-23') 
AND (tbl_requests.request_type IN (0, 1, 9))  
[AND (tbl_requests.symbol = 'AAPL' ... )]
ORDER BY tbl_requests.request_time DESC, tbl_requests.request_id DESC

符号的条件是相等。它应该位于索引的最左侧。

request_time 和request_type 的条件都是不等的。您只能从索引中的一个或另一个中受益。选择最选择性的那个——它能最好地缩小搜索范围。将另一列添加到索引中以防万一 ICP 可以提供一点帮助。

我猜想在大多数情况下，request_time 列更具选择性。我看到你的条件是 10 个月的范围，这可能是你表格的大部分内容，但根据你选择的日期范围，它可能会更窄。

同样，request_type 的三个值 0、1、9 也可能匹配表中的大部分行。如果是这样，那么该条件就不会非常有选择性，我会将该列放在最后。

ALTER TABLE tbl_requests ADD INDEX (symbol, request_time, request_type);

顺序 request_time 发生在不等式条件之后，因此无法避免对匹配行进行文件排序，抱歉。