我正在使用 mysql2 NodeJS v8.9.4中的模块。
此函数用于从满足以下条件的消息队列中获取消息： status==0 如果计数 botId 与 status==1 小于10
如果 retry_after 在 wait 表 botId+chatId 只是 botId 小于 NOW （时间戳）
如果没有相同的 chatId 与 status==1 ```
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中也可以在几毫秒内正常运行。
但是当它运行throw 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 ms
-最大：44285毫秒
-平均值：20185.4 ms
到昨天为止，最大值是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个where，查询时间恢复正常。

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

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