mysql - mysql优化器中的奇怪查询

Question

我在 Debian 8 机器上使用 mysql 5.5.52，有时查询速度很慢(>3 秒)，通常需要 0.1 秒。我开始使用解释命令来查找发生了什么。

这是查询和解释信息

explain
SELECT
   `box`.`message_id` ID
 , `messages`.`tipo`
 , `messages`.`text`
 , TIME_TO_SEC(TIMEDIFF(NOW(), `messages`.`date`)) `date`
FROM (`box`)
INNER JOIN `messages` ON `messages`.`id` = `box`.`message_id`
WHERE `box`.`user_id` = '1010231' AND `box`.`deleted` = 0 
    AND `messages`.`deleted` = 0 
    AND `messages`.`date` + INTERVAL 10 MINUTE > NOW()
ORDER BY `messages`.`id` ASC LIMIT 100;


id| select_type| table  | type |  possible_keys   | key   | key_len| ref          | rows | Extra    
 1|SIMPLE      |box     |ref   |user_id,message_id|user_id|       4|const         | 2200 |Using where; Using temporary; Using filesort   
 1|SIMPLE      |messages|eq_ref|PRIMARY           |PRIMARY|       4|box.message_id|    1 |Using where

我知道临时表和文件排序是一件坏事，我认为问题是订单键不属于查询(框)中的第一个表并将其更改为 box.message_id，解释信息是

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1 SIMPLE box index user_id,message_id message_id 4 443 Using where

1 SIMPLE messages eq_ref PRIMARY PRIMARY 4 box.message_id 1 Using where

看起来好多了，但我不明白为什么它使用 message_id 索引，最糟糕的是，现在查询需要 1.5 秒而不是最初的 0.1 秒

编辑:

强制查询使用 user_id 索引，我得到与初始查询相同的结果(0.1s)，但没有临时

explain
SELECT
   `box`.`message_id` ID
 , `messages`.`tipo`
 , `messages`.`text`
 , TIME_TO_SEC(TIMEDIFF(NOW(), `messages`.`date`)) `date`
FROM (`box` use index(user_id) )
INNER JOIN `messages` ON `messages`.`id` = `box`.`message_id`
WHERE `box`.`user_id` = '1010231' AND `box`.`deleted` = 0 
    AND `messages`.`deleted` = 0 
    AND `messages`.`date` + INTERVAL 10 MINUTE > NOW()
ORDER BY `box`.`message_id` ASC LIMIT 100;

id| select_type| table  | type |  possible_keys   | key   | key_len| ref          | rows | Extra    
 1|SIMPLE      |box     |ref   |user_id,message_id|user_id|       4|const         | 2200 |Using where; Using filesort   
 1|SIMPLE      |messages|eq_ref|PRIMARY           |PRIMARY|       4|box.message_id|    1 |Using where

我认为跳过临时查询是比初始查询更好的解决方案，下一步是按照 ysth 的建议检查组合索引。

Answer 1

计算要比较的字段值并不是一个好主意。然后你会得到全表扫描。 MySQL 必须先对每个 ROW 执行此操作，然后才能检查条件。最好在恒定的条件下进行。然后MySQL可以使用索引(如果该字段上有索引)

更改自

AND messages.date + INTERVAL 10 MINUTE > NOW() 

至

AND messages.date  > NOW() - INTERVAL 10 MINUTE