mysql - Nodejs Mysql优化查询

我在nodejs v8.9.4中使用mysql2模块。
这是我从符合以下条件的消息队列中获取消息的功能：

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中正常运行。

但是运行时抛出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;

更新：Real table data here
更新2：
FetchMessageTime：
-最小值：1788毫秒
-最大值：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个位置，查询时间又恢复了正常。

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

因此，这两个查询是关键点，需要加以解决。

最佳答案

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

关于mysql - Nodejs Mysql优化查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49937703/