mysql - 如何修复更新语句阻塞 MYSQL 中的多个表

Question

我在使用 mysql 数据库时遇到了一个问题，在创建新表并将 CRUD 数据库查询逻辑添加到我的 Web 应用程序（后端用 c 编写）之后，更新查询有时需要 10-20 分钟才能执行。

Web 应用程序具有 apache 模块，这些模块与连接到 mysql (MariaDB 10.4) 数据库的服务器守护进程通信。每个服务器守护进程都有大约 20 个工作线程，等待处理来自 apache 模块的任何请求。工作线程维护与 mysql 数据库的同意连接。我添加了以下架构的新表：

CREATE TABLE MyTable
( 
   table_id INT NOT NULL AUTO_INCREMENT,
   index_id INT NOT NULL,
   int_column_1 INT DEFAULT 0,
   decimal_column_1 DECIMAL(9,3) DEFAULT 0,
   decimal_column_2 DECIMAL(9,3) DEFAULT 0,
   varchar_column_1 varchar(3000) DEFAULT NULL,
   varchar_column_2 varchar(3000) DEFAULT NULL,
   deleted tinyint DEFAULT 0,
   PRIMARY KEY (table_id) ,
   KEY index_on_index_id (index_id)
) 

然后我添加了以下 crud 操作：

1. RETRIEVE:
SELECT * FROM MyTable table_id, varchar_column_1,... WHERE index_id = ${given index_id}

2. CREATE:
INSERT INTO MyTable (index_id, varchar_column_2, ,,,) VALUES ( ${given}, ${given})
注意：这是使用准备语句完成的，因为 ${given varchar_column_2} 是用户输入的值。

3. 更新：
UPDATE MyTable SET varchar_column_1 = ISNULL(${given varchar_column_2}, `varchar_column_2 `) WHERE table_id = ${given table_id}
注意：这也是使用准备语句完成的，因为 ${given varchar_column_2} 是用户输入的值。此外，isnull 是对给定 varchar_column_2 可能为 null 的可能性的混合解决方案，因此该列将被设置为表中的值。

4. 删除：
UPDATE MyTable SET deleted = 1 WHERE table_id = ${given table_id}

最后还有一个删除 index_id 操作：
UPDATE MyTable SET deleted = 1 WHERE index_id = ${given index_id }

这在没有经过适当测试的情况下部署到生产服务器。在那个生产服务器上，运行了我编写的脚本，该脚本在 MyTable 中填充了大约 30,000 个条目。然后，使用 crud 操作，对表执行了大约 600 次更新、50 次创建、20 次删除和数千次检索。正在发生的问题是，在执行这些操作一段时间（一两个小时）后，更新操作将需要 10 多分钟才能执行。最终，服务器守护程序中的所有工作线程都将等待更新操作，并且对守护程序的任何其他请求都会超时。这种行为一天发生了两次，两天后又发生了一次。

这种行为的三个部分真的让我感到困惑。一是数据库上的所有更新操作都被阻止了。因此，即使守护程序或任何守护程序正在更新数据库中的不同表，该更新也需要 10 分钟以上。接下来是选择操作将立即执行，因为所有更新查询都需要 10 分钟以上。最后，在 10-20 分钟后，所有 20 次更新查询将成功执行，数据库将正确更新，线程将恢复正常工作。

我收到了数据库的转储并运行

EXPLAIN ${mysql query}
了每个新的 CRUD 查询，但没有一个产生奇怪的结果。在“Extras”列中，对于具有 where 子句的查询，唯一的条目是“使用 where 子句”。另一个潜在的问题是使用 varchars。由于 UPDATE 操作使用最多，并且似乎是导致问题的原因，我认为可能是 varchars 的大小变化很大（它们的范围从 8 个字符到 500 个字符），它可能会遇到一些 mysql导致执行时间长的内存问题。我还认为表级锁可能存在问题，但运行
Show status like ' table%
返回 table_locks_waited = 0。

不幸的是，没有在有问题的生产服务器上进行数据库监控，我只有事务发生时的顺序。为此，每次发生此问题时，被阻止的第一个更新查询是对数据库中不同表的更新。这是两次相同的查询（但它也是应用程序中最常见的更新查询），但它已经在应用程序中使用了几个月，没有任何问题。

我试图在具有相同表和 CRUD 操作的服务器上重现此问题，但 MyTable 中只有 600 个条目。发出大约 100 个更新请求、20 个创建请求、5 个删除请求和数百个获取请求。我无法重现需要 10 分钟以上的更新查询的问题。这让我觉得也许桌子的大小与它有关。

我正在寻找有关可能导致此问题的任何建议，或有关如何更好地诊断问题的任何想法。

很抱歉这个问题很长。我是一名初级软件工程师，有点过头了。任何帮助将非常感激。如果需要，我还可以提供有关数据库或应用程序的任何其他信息。

标签： mysqlc