mysql - MySQL中延迟连接的作用

Question

我正在阅读高性能 MySQL，我对延迟连接有点困惑。

书上说下面的操作不能通过index(sex, rating)来优化，因为高offset要求他们花费大部分时间扫描大量数据，然后他们会丢弃这些数据。

mysql> SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;

虽然延迟连接有助于最大限度地减少 MySQL 必须收集的数据的工作量，但它只会丢弃这些数据。

  SELECT <cols> FROM profiles INNER JOIN (
  SELECT <primary key cols> FROM profiles
  WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
  ) AS x USING(<primary key cols>);

为什么延迟连接会最大限度地减少收集的数据量。

Answer 1

您提供的示例假定使用了 InnoDB。假设 PRIMARY KEY只是id .

INDEX(sex, rating)

是“辅助键”。每个辅助键(在 InnoDB 中)都隐含地包含 PK，所以它实际上是一个有序列表 (sex, rating, id)值。要获取“数据”( <cols> )，它使用 id向下钻取 PK BTree(也包含数据)以查找记录。

快速案例:因此，

SELECT id FROM profiles
    WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10

将对索引中的 100010 个“行”进行“范围扫描”。这对于 I/O 来说非常有效，因为所有信息都是连续的，没有任何浪费。 (不，跳过 100000 行还不够聪明；那会非常困惑，尤其是当您考虑 transaction_isolation_mode 时。)这 100010 行可能适合索引的大约 1000 个 block 。然后它得到 id 的 10 个值.

有了这 10 个 ID，它可以进行 10 次连接(“NLJ”=“嵌套循环连接”)。这 10 行很可能分散在表格周围，可能需要 10 次磁盘访问。

让我们“计算磁盘命中数”(忽略 BTrees 中的非叶节点，它们可能无论如何都被缓存):1000 + 10 = 1010。在普通磁盘上，这可能需要 10 秒。

Slow Case:现在让我们看看原始查询 (SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;)。让我们继续假设 INDEX(sex, rating)加上隐式 id最后。

和以前一样，它将索引扫描 100010 行(估计 1000 次磁盘命中)。但事实上，做上面所做的事情太愚蠢了。它将进入数据以获取 <cols> .这通常(取决于缓存)需要随机磁盘命中。这可能超过 100010 次磁盘命中(如果表很大并且缓存不是很有用)。

再次抛出 100000 个，交付 10 个。总“成本”:100010 次磁盘命中(最坏情况)，可能需要 17 分钟。

请记住，高性能 MySQL 有 3 个版本；它们是在过去 13 年左右的时间里写成的。您可能使用的 MySQL 版本比他们介绍的要新得多。我不知道优化器是否在这方面变得更聪明了。这些，如果对你可用，可能会提供线索:

EXPLAIN FORMAT=JSON SELECT ...;
OPTIMIZER TRACE...

我最喜欢的“处理程序”技巧可能有助于研究事物的工作原理:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%'.

您可能会看到像 100000 和 10 这样的数字，或者它们的小倍数。但是，请记住，索引的快速范围扫描计为每行 1 次，对于 <cols> 的大集合，慢速随机磁盘命中也是如此。 .

概述:要使这项技术发挥作用，子查询需要一个“覆盖”索引，并且列的顺序正确。

“覆盖”是指(sex, rating, id)包含所有触及的列。 (我们假设 <cols> 包含其他列，可能是那些在 INDEX 中不起作用的大列。)

列的“正确”排序:列的顺序恰到好处，可以顺利完成查询。 (另见 my cookbook。)

• 先到先得WHERE列与 = 相比到常量。 ( sex )
• 然后是整个ORDER BY ， 为了。 ( rating )
• 最后是“覆盖”。 ( id )