SQL:结合15 million+行查询的位置、顺序和限制

Question

我有两张桌子，item和config。

item有1500万行，config有1000行。

我想用一个WHERE子句连接这两个表，并对结果排序。

这个看起来可能是这样的：

SELECT
    `t0`.`id`,
    `t0`.`item_name`,
    `t1`.`id`,
    `t1`.`config_name`,
FROM
    `item` t0
    LEFT OUTER JOIN `config` `t1` ON `t0`.`config_id` = `t1`.`id`
WHERE (`t0`.`config_id` = 678)
ORDER BY
    `t0`.`item_name` ASC;

在~800 in中成功运行，并返回~50k行.

我也想要分页这个结果，所以我运行相同的查询并添加一个LIMIT

SELECT
    `t0`.`id`,
    `t0`.`item_name`,
    `t1`.`id`,
    `t1`.`config_name`,
FROM
    `item` t0
    LEFT OUTER JOIN `config` `t1` ON `t0`.`config_id` = `t1`.`id`
WHERE (`t0`.`config_id` = 678)
ORDER BY
    `t0`.`item_name` ASC LIMIT 200;

这个查询现在需要超过5分钟。

我正在努力了解是什么造成了这种差异。

我可以简化查询，完全删除JOIN，只查询大型表以尝试隔离减速的原因：

SELECT
    `t0`.`id`,
    `t0`.`item_name`,
FROM
    `item` t0
WHERE (`t0`.`config_id` = 678)
ORDER BY
    `t0`.`item_name` ASC;

这个查询运行良好，但是添加LIMIT会大大增加查询时间。

如何解决这个问题或更好地诊断是什么引起的？

不带 LIMIT的简化查询LIMIT的执行计划如下：

+----+-------------+-------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys |    key    | key_len |  ref  | rows  | filtered |                 extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | t0    | NULL       | ref  | ITEM_FK_1     | ITEM_FK_1 |       8 | const | 98524 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------------------------+

将LIMIT 200添加到查询中将产生以下执行计划：

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys |        key         | key_len | ref  | rows  | filtered |          extra           |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | t0    | NULL       | index | ITEM_FK_1     | ITEM_RULE_ITEM_UNQ |     775 | NULL | 31933 |     0.63 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+-------+----------+--------------------------+

Answer 1

要查找带有config_id=678的行并按item_name对它们进行排序，并且只使用前200行，您可以(除其他外)以下选项：

1. 使用由item_name排序的索引，并继续阅读，直到找到200行也满足config_id=678 (不需要排序)为止。
2. 使用config_id=678上的索引(外键)获取带有config_id的所有行，然后按名称排序这些行，并取前200行

其中哪一个更快取决于你的数据。

对于第一个问题，它将取决于带有config_id=678的行在哪里。如果前200行(按名称排序，例如以A开头)都有这个id，这将非常快:您可以读取200行，停止，甚至不需要订购任何东西。如果您运气不佳，并且所有这些id都位于列表的末尾(例如，只有以Z开头的名称才有此id)，则必须在找到适合的200行之前读取所有行。

第二个选项取决于使用config_id=678的行数。它将读取它们中的所有50k (使用索引)，对它们进行排序，并给出第一个200。这将是介于快和慢的选项之间。

基本上，MySQL现在必须猜测哪个版本更快。对于使用limit 200的查询，它猜错了，显然，它必须读取比预期更多的行。

想让你了解一下MySQL在想什么：

• MySQL假设您有98.524行(而不是50k)与config_id=678 (在您的第一个执行计划中rows中的数字)。
• 您有1500万行，所以一个特定行具有id的概率是98.524 / 15 Mill = 1/150。您需要其中的200个，所以您需要阅读200*150=30.000 (或31.933，第二个执行计划中的数字)行，直到您可能找到足够的数据为止。

现在，MySQL将读取100 k行并排序与可能读取30k行进行比较，并选择了后者。在这种情况下是错误的(虽然5分钟看上去有点多，但是还有其他一些因素，比如索引大小的增加，或者可能是覆盖范围的缺失，这可能会减缓它的速度)。但可能适合不同的身份。

如果您增加了限制(以后的页面必须这样做)，MySQL将在某个时候切换执行计划(例如，找到具有此概率的前1.000行需要1.000*150=150k >100 k行)。

那么，你能做什么呢？

1. 您可以使用力 MySQL来使用所需的索引，例如使用... from item t0 force index (ITEM_FK_1) left outer join ...。这有一个缺点，即取决于id，不同的执行计划可能会更快。
2. 您可以添加一个最佳索引:复合索引(config_id, item_name)允许您只读取具有正确id的行，并且由于它们是按名称排序的，所以可以在前200行之后停止。无论您的数据分布如何，您总是读取200行(或更少)。假设id是主键，没有比这更快的解决方案了。

我赞成备选案文2。

Answer 2

加上这个

INDEX(config_id, item_name,  id)   -- in this order!

和DROP的任何索引，是一个‘前缀’的那个。