Nodejs Mysql 优化查询

Question

我在nodejs v8.9.4中使用mysql2模块。
这是我从满足此条件的消息队列获取消息的函数：

status==0如果status==1的botId计数小于 10
，如果retry_after表中botId+chatIdwait并且只有botId小于NOW(时间戳)，
如果没有与status==1
相同的chatId

static async Find(activeMessageIds, maxActiveMsgPerBot) {
let params                 = [maxActiveMsgPerBot];
let filterActiveMessageIds = ' ';
let time                   = Util.GetTimeStamp();
if (activeMessageIds && activeMessageIds.length) {
filterActiveMessageIds = 'q.id NOT IN (?) AND ';
params.push(activeMessageIds);
}
let q =
`select q.* 
from bot_message_queue q 
left join bot_message_queue_wait w on q.botId=w.botId AND q.chatId=w.chatId 
left join bot_message_queue_wait w2 on q.botId=w2.botId AND w2.chatId=0 
where
q.status=0 AND
q.botId NOT IN (select q2.botId from bot_message_queue q2 where q2.status=1 group by q2.botId HAVING COUNT(q2.botId)>?) AND 
${filterActiveMessageIds} 
q.chatId NOT IN (select q3.chatId from bot_message_queue q3 where q3.status=1 group by q3.chatId) AND 
(w.retry_after IS NULL OR w.retry_after <= ?) AND 
(w2.retry_after IS NULL OR w2.retry_after <= ?)  
order by q.priority DESC,q.id ASC  
limit 1;`;
params.push(time);
params.push(time);
let con    = await DB.connection();
let result = await DB.query(q, params, con);
if (result && result.length) {
result      = result[0];
let updateQ = `update bot_message_queue set status=1 where id=?;`;
await DB.query(updateQ, [result.id], con);
} else
result = null;
con.release();
return result;
}

此查询在我的本地开发系统上运行良好。它在服务器phpmyadmin中也能在几毫秒内运行良好。

但是当它运行时抛出 nodejs+mysql2 CPU 使用率上升到 100% 此表中只有 2K 行。

CREATE TABLE IF NOT EXISTS `bot_message_queue` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`botId` int(10) UNSIGNED NOT NULL,
`chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
`type` varchar(50) DEFAULT NULL,
`message` longtext NOT NULL,
`add_date` int(10) UNSIGNED NOT NULL,
`status` tinyint(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=waiting,1=sendig,2=sent,3=error',
`priority` tinyint(1) UNSIGNED NOT NULL DEFAULT '5' COMMENT '5=normal messages,<5 = bulk messages',
`delay_after` int(10) UNSIGNED NOT NULL DEFAULT '1000',
`send_date` int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `botId` (`botId`,`status`),
KEY `botId_2` (`botId`,`chatId`,`status`,`priority`),
KEY `chatId` (`chatId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `bot_message_queue_wait` (
`botId` int(10) UNSIGNED NOT NULL,
`chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
`retry_after` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`botId`,`chatId`),
KEY `retry_after` (`retry_after`),
KEY `botId` (`botId`,`chatId`,`retry_after`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更新：此处为真实表数据 更新 2：获取消息时间 ：
- 最小 ： 1788 毫秒- 最大 ： 44285 毫秒- 平均 ： 20185.4 毫秒

直到昨天，最大值约为 20 毫秒:(现在是 40 秒!!

更新 3：我合并了这两个连接和位置：

left join bot_message_queue_wait w on q.botId=w.botId AND q.chatId=w.chatId 
left join bot_message_queue_wait w2 on q.botId=w2.botId AND w2.chatId=0 
(w.retry_after IS NULL OR w.retry_after <= ?) AND 
(w2.retry_after IS NULL OR w2.retry_after <= ?)  

成一个，我希望这将按预期工作！

left join bot_message_queue_wait w on q.botId=w.botId AND ( q.chatId=w.chatId OR w.chatId=0 )

暂时我删除了 2 个位置，查询时间恢复正常。

q.botId NOT IN (select ...)
q.chatId NOT IN (select ...)

所以这 2 个查询是阻塞点，需要修复。

Answer 1

NOT IN ( SELECT ... )很难优化。

OR无法优化。

在ORDER BY中，混合使用DESC和ASC消除了索引的使用(直到8.0)。 考虑将ASC更改为DESC。 在那之后，INDEX(priority, id)可能会有所帮助。

什么是${filterActiveMessageIds}？

中不需要GROUP BY

NOT IN ( SELECT  q3.chatId
from  bot_message_queue q3
where  q3.status=1
group by  q3.chatId )

按此顺序INDEX(status, chatid)将有利于该子查询。

按此顺序INDEX(status, botid)

有关索引创建的更多信息：http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Answer 2

在这种情况下，我会将 NOT

1. IN 子查询替换为 NOT EXIST，因为它可以表现得更好。
2. 将 ORDER BY 切换到所有 DESC 或所有 ASC

因此，要优化查询，首先添加以下索引：

ALTER TABLE `bot_message_queue` ADD INDEX `bot_message_queue_idx_status_botid_chatid_priori_id` (`status`,`botId`,`chatId`,`priority`,`id`);
ALTER TABLE `bot_message_queue` ADD INDEX `bot_message_queue_idx_priority_id` (`priority`,`id`);
ALTER TABLE `bot_message_queue` ADD INDEX `bot_message_queue_idx_botid_status` (`botId`,`status`);
ALTER TABLE `bot_message_queue` ADD INDEX `bot_message_queue_idx_chatid_status` (`chatId`,`status`);
ALTER TABLE `bot_message_queue_wait` ADD INDEX `bot_message_queue_wa_idx_chatid_botid` (`chatId`,`botId`);

现在，您可以尝试运行此查询(请注意，我将顺序更改为所有 DESC，因此如果需要，您可以将其更改为 ASC)：

SELECT
bot_message_queue.* 
FROM
bot_message_queue q 
LEFT JOIN
bot_message_queue_wait w 
ON q.botId = w.botId 
AND q.chatId = w.chatId 
LEFT JOIN
bot_message_queue_wait w2 
ON q.botId = w2.botId 
AND w2.chatId = 0 
WHERE
q.status = 0 
AND NOT EXISTS (
SELECT
1 
FROM
bot_message_queue AS q21 
WHERE
q21.status = 1 
AND q.botId = q21.botId 
GROUP BY
q21.botId 
HAVING
COUNT(q21.botId) > ? 
ORDER BY
NULL
) 
AND NOT EXISTS (
SELECT
1 
FROM
bot_message_queue AS q32 
WHERE
q32.status = 1 
AND q.chatId = q32.chatId 
GROUP BY
q32.chatId 
ORDER BY
NULL
) 
AND (
w.retry_after IS NULL 
OR w.retry_after <= ?
) 
AND (
w2.retry_after IS NULL 
OR w2.retry_after <= ?
) 
ORDER BY
q.priority DESC,
q.id DESC LIMIT 1