我的慢查詢日誌中有這個。

慢查詢日誌

# Time: 2020-03-09T08:42:46.946084Z
# User@Host: admin_admin[admin_admin] @ localhost [127.0.0.1]  Id:    16
# Query_time: 0.001558  Lock_time: 0.000278 Rows_sent: 0  Rows_examined: 255
SET timestamp=1583743366;
select DISTINCT q.id,q.*
           from bot_message_queue q
           left join bot_message_queue_wait w1 on q.botId=w1.botId AND q.chatId=w1.chatId
           left join bot_message_queue_wait w2 on q.botId=w2.botId AND w2.chatId=0
           where
             q.status=0 AND

             (w2.retry_after IS NULL OR w2.retry_after < 1583743366) AND
             (w1.retry_after IS NULL OR w1.retry_after < 1583743366)
           order by q.priority DESC,q.id ASC
           limit 1;

我的桌子

在查詢中涉及的表的 DDL 之後。

bot_message_queue

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,
 `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,
 `identifier` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `botId` (`botId`,`status`),
 KEY `chatId` (`chatId`,`status`),
 KEY `botId_2` (`botId`,`chatId`,`status`,`priority`,`identifier`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;

bot_message_queue_wait

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;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bot_message_queue_wait`
--
ALTER TABLE `bot_message_queue_wait`
 ADD CONSTRAINT `message_queue_wait_botId` FOREIGN KEY (`botId`) REFERENCES `bot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

問題

它並不慢，但它會根據每個請求讀取整個表格。

我應該更改/添加哪些索引來解決這個問題？

更新

解釋結果沒有區別

更新 2

這是一個非常繁重的讀/寫/更新表，它是我整個數據庫中最常用的表，通常它只有不到 1000 行，但當有人發送批量消息時它可能會上升到 10000 多行……

我清理每小時增加處理的行以使其保持小…

既然retry_after是NOT NULL，你就不需要檢查了NULL。刪除該檢查將擺脫OR，從而防止 sargability。

ORDER BY q.priority DESC, q.id ASC可能需要排序，至少因為兩列方向相反（DESC/ASC）。（在 MySQL 8.0 中，您可以擁有一個像這樣拆分的索引。）這樣做是否同樣有效q.id DESC？或者也許反轉 的值，q.priority所以兩者都可以ASC。注意：您必須更改所有值和程式碼；簡單地使用表達式 ( -q.priority) 只會讓事情變得更糟。

有效的DISTINCT原因是另一種類型——重複數據刪除。有了LIMIT 1，誰在乎是否有重複！

把碎片放在一起：

select  q.id
   from  bot_message_queue q
   where  q.status=0
     AND  EXISTS (
       SELECT  1
           FROM  bot_message_queue_wait w1
           WHERE  botId=w1.botId
             AND  q.chatId=w1.chatId
             AND  w1.retry_after < 1583743366
                 )
     AND  EXISTS (
       SELECT  1
           FROM  bot_message_queue_wait w2
           WHERE  botId=w2.botId
             AND  0=w2.chatId
             AND  w2.retry_after < 1583743366
                 )
   order by  q.priority DESC, q.id DESC
   limit  1;

bot_message_queue並按照給定的順序為 提供這些索引。我不知道哪個更好：

(status, chatId, priority, id)
(status, priority, id, chatId)

並擺脫它bot_message_queue_wait；它與數據加上集群 PK 是多餘的：

KEY `botId` (`botId`,`chatId`,`retry_after`)

由於LIMIT，您不應該相信EXPLAIN. 相反，使用此處描述的“處理程序計數”技術：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

試試這個：

CREATE INDEX ix_bot_message_queue_status ON bot_message_queue (status, priority desc, id, botId, chatId);           
select q.id
from bot_message_queue q
where
             q.status=0 
             AND
             (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId) 
             OR EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId AND w1.retry_after < 1583743366))
             AND 
             (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId) 
             OR EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId AND w2.retry_after < 1583743366))

order by q.priority DESC,q.id ASC
limit 1;            

如果您的 MySQL 版本不支持降序索引，您可能需要對生成的列使用技巧

引用自：https://dba.stackexchange.com/questions/261497