MySQL InnoDB 数据库在选择时“挂起”

Question 1

请仔细查看进程列表和“show engine innodb status”。你看到了什么???

进程 ID 1、2、4、5、6、13 均正在尝试运行 COMMIT。

谁在阻碍一切??? 进程 ID 40 正在针对 large_table 运行查询。

进程 ID 40 已运行 33 秒。进程 ID 1、2、4、5、6、13 已运行不到 33 秒。进程 ID 40 正在处理某事。发生了什么问题???

在进程 ID 1、2、4、5、6、13 中，有些行具有 MVCC 数据，以保护其事务隔离性。进程 ID 40 有一个查询，该查询正在遍历数据行。如果字段 hotspot_id 上有索引，则该键 + 聚簇索引中实际行的键必须执行内部锁定。（注意：根据设计，InnoDB 中的所有非唯一索引都同时带有您的键（您要索引的列）+ 聚簇索引键）。这种独特的场景本质上是势不可挡的力量与不可移动的物体相遇。

本质上，COMMIT 必须等到可以安全地对 large_table 应用更改时才执行。您的情况并非独一无二、并非个例、也并非罕见现象。

我实际上在 DBA StackExchange 中回答了三个这样的问题。这些问题由同一个人与同一个问题有关。我的回答不是解决方案，但帮助提问者得出了如何处理他的情况的结论。

除了这些答案之外，我回答了另一个人关于 InnoDB 中 SELECT 死锁的问题。

我希望我过去关于这个主题的帖子能帮助您弄清楚发生了什么事。

更新时间：2011-08-25 08:10 EDT

以下是来自进程 ID 40 的查询

SELECT * FROM `large_table`
WHERE (`large_table`.`hotspot_id` = 3000064)
ORDER BY discovered_at LIMIT 799000, 1000;

两个观察结果：

您正在执行 'SELECT *'，是否需要获取每一列？如果您只需要特定的列，则应该标记它们，因为 1000 行的临时表可能比您实际需要的要大。
WHERE 和 ORDER BY 子句通常会暴露性能问题或使表设计更加出色。您需要创建一种机制，在收集数据之前加快密钥的收集速度。

根据这两点观察，您必须做出两项重大改变：

重大变化 #1：重构查询

重新设计查询以便

密钥是从索引中收集的
只有 1000 个或他们被收集
重新加入主表

以下是执行以下三项操作的新查询

SELECT large_table.* FROM
large_table INNER JOIN
(
    SELECT hotspot_id,discovered_at
    FROM large_table
    WHERE hotspot_id = 3000064
    ORDER BY discovered_at
    LIMIT 799000,1000
) large_table_keys
USING (hotspot_id,discovered_at);

子查询 large_table_keys 收集您需要的 1000 个键。然后，子查询的结果将 INNER JOIN 到 large_table。到目前为止，检索的是键，而不是整行。仍然需要读取 799,000 行。有一种更好的方法来获取这些键，这让我们想到了...

重大变化 #2：创建支持重构查询的索引

由于重构后的查询仅包含一个子查询，因此只需创建一个索引。该索引如下：

ALTER TABLE large_table ADD INDEX hotspot_discovered_ndx (hotspot_id,discovered_at);

为什么是这个特定的索引？看看 WHERE 子句。hotspot_id 是一个静态值。这使得所有 hotspot_id 在索引中形成一个顺序列表。现在，看看 ORDER BY 子句。discovered_at 列可能是 DATETIME 或 TIMESTAMP 字段。

索引中呈现的自然顺序如下：

索引包含 hostpot_ids 列表
每个 hotspot_id 都有一个由 discover_at 字段组成的有序列表

建立此索引还可以消除对临时表进行内部排序的需要。

请将这两个重大变化落实到位，您将看到运行时间的差异。

试一试！！！

更新时间 2011-08-25 08:15 EDT

我查看了您的索引。您仍需要创建我建议的索引。

Answer