简单更新/选择上的MySQL表性能问题（大表，许多连接）

debugcn 发表于 Dev

约翰

我一直在使用各种方法运行大约一个星期，在测试运行期间，总是导致大量负载使服务器“崩溃”。

mysql> explain select id FROM task_jobs FORCE INDEX (index_update_get_work) WHERE customer_job_id=31 AND client_reserved=0  AND result_delivered=0 AND (assigned_instance is NULL)  LIMIT 10;
+----+-------------+--------------------+------------+------+-----------------------+-----------------------+---------+-------------------------+--------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys         | key                   | key_len | ref                     | rows   | filtered | Extra       |
+----+-------------+--------------------+------------+------+-----------------------+-----------------------+---------+-------------------------+--------+----------+-------------+
|  1 | SIMPLE      | task_jobs | NULL       | ref  | index_update_get_work | index_update_get_work | 14      | const,const,const,const | 104226 |   100.00 | Using where |
+----+-------------+--------------------+------------+------+-----------------------+-----------------------+---------+-------------------------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

为了保持通用性：我有一个表，其中包含成百万上万行。
该表一次可为数百到数千个云实例提供工作。

所有这些实例都将查询我的表（一次最多可查询3000个查询），并要求接收其工作数据。

有数十万行具有“未完成的作业”的行，但一次只能将10到20个行分发给一个实例。

我当前的方法性能最高，但仍然存在很大问题：

我在LIMIT 10行上进行了更新，其中customer_job_id = 31 AND client_reserved = 0 AND result_delivered = 0 AND（assigned_instance为NULL），
我猜查询是在自我解释，它查找尚未分配的作业，这些作业尚未从特定的“工作编号”。
该查询看起来像刚开始时只是UPDATE而不是SELECT的查询。
现在，程序逻辑选择了一些被删除的行，并进行了第二次更新，以最终使用WHERE ID IN（x，x，x，x，x）将它们分配给实例。

我正在使用这种方法，因此我可以通过将它们更新为忙碌来快速“锁定” 10行，以便下一个实例也可以锁定另外10行，依此类推。

如果我将负载提高到500个实例，则服务器将被锁定，这可以正常工作，并且一次100个实例没有任何问题。它用数百个LOCKED请求填充数据库连接，以花费15秒的时间来更新10行（优化之前为140秒）。

正如您在一开始所看到的，SELECT（实际上是一个UPDATE SET client_reserved = 1，assigned_instance = $ instance_id）必须经过10万行（可能更多）。即使只选择其中的10个，它似乎也要在每个作业完成之前检查并更新前10个。至少EXPLAIN似乎可以说明这一点。

因此，基本上我的问题是找到一种更好的方法。
我需要在几秒钟内从成千上万个不同的连接中获取成千上万的行。
每次我需要从100-500k可用作业/行中获取少量行时，“ WHERE customer_job_id = 31 AND client_reserved = 0 AND result_delivered = 0 AND（assigned_instance为NULL）”。

Assigned_instance是一个varchar（NULL条件为index（1）），其他均为tinyint（1）。我创建了一个将所有这些都合并在一起的索引，但这并没有真正的帮助。

更新
为澄清起见：
我正在使用“更新”，因为主服务器上的API不知道是否还有其他同时请求“给我工作”。
因此，我在许多行上使用了UPDATE来为当前实例“保留”它们。
由于UPDATE是SQL中的“基本”操作，因此不存在使用相同作业（竞争条件）为另一个请求提供服务的风险。

更新问题
一个普遍的问题：我使用LIMIT 10
如果10足够，为什么它会搜索100,000个结果？当我添加ORDER BY RAND（）时，它实际上没有必要查看所有100k结果并对其重新排序（相同的性能成本），这对性能没有任何影响。为什么mysql一旦找到10个命中点就不停止（这就是我希望LIMIT 10并通过省略任何ORDER BY子句来实现的目的）

史蒂文·莫斯利（Steven Moseley）

好的，所以您需要一个任务队列，该队列将存储对可用作业的引用，该引用可以从队列中“弹出”

任务队列

CREATE TABLE task_queue (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    task_job_id INT UNSIGNED NOT NULL
) ENGINE=InnoDB;

您可以非常快速地从表格中“弹出” x个项目，如下所示：

LOCK TABLES task_queue READ;
SELECT * FROM task_queue LIMIT x;
DELETE FROM task_queue LIMIT x;
UNLOCK TABLES;

您可以继续写到队列末尾，并在每分钟运行一次cron来检索和排队符合您条件的新任务：

SELECT id
FROM task_jobs
FORCE INDEX (index_update_get_work) 
WHERE customer_job_id = 31 
    AND client_reserved = 0  
    AND result_delivered = 0 
    AND assigned_instance IS NULL
    AND id NOT IN (SELECT task_job_id FROM task_queue);

单表队列

这是单表队列设计的一个想法：

您需要将队列索引保留在数据库中。您可以使用单行表执行此操作

CREATE TABLE queue_index (
    id INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

使用您的最小ID初始化一次。

INSERT INTO queue_index (id)
SELECT MIN(id) FROM task_jobs
WHERE customer_job_id = 31 
    AND client_reserved = 0  
    AND result_delivered = 0 
    AND assigned_instance IS NULL;

您可以使用以下方法从表格中“弹出” x个项目：

BEGIN TRANSACTION;

SELECT @oldid = id FROM queue_index;

SELECT @newid = MAX(id)
FROM (
    SELECT id
    FROM task_jobs 
    WHERE id > @oldid
    LIMIT x
) AS j;

UPDATE queue_index SET id = @newid;

SELECT * FROM task_queue WHERE id <= @newid;

END TRANSACTION;

然后，添加的任何新task_jobs将自动进入队列进行分配。

如果指针移到给定任务之外，然后又要计划将其重新分配，则应删除记录，然后将其重新插入列表的末尾。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-07-2

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章