MySQL 强制对主键进行过滤使查询速度更快

Question

我有两个表“订单”和“项目”。它们看起来大致是这样的。

CREATE TABLE `Order` (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `item_id` INT NOT NULL,
  `time` DATETIME NOT NULL,
  PRIMARY KEY (`order_id`),
  INDEX `combo_id` (`item_id` ASC, `order_id` DESC) VISIBLE,
  CONSTRAINT `fk_order_item`
    FOREIGN KEY (`item_id`)
    REFERENCES `Item` (`item_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `Item` (
  `item_id` INT NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(1) NOT NULL,
  PRIMARY KEY (`item_id`),
  INDEX `status` (`status` ASC) VISIBLE);

我有一个查询来获取状态不是“D”的每个商品的最后 5 个订单。

select * from 
(SELECT order_id, item_id,
        rank() over (partition by Item.item_id 
                     order by order_id desc) drank
 FROM Order
 JOIN Item ON Item.item_id = Order.item_id
 WHERE Item.status != 'D' ) tmp 
 WHERE tmp.drank <= 5;

Order 表中有大约 300K 行，Item 表中有 500 行。该查询运行速度较慢，大约需要 2 秒。执行计划是这样的。

table |type   |possible_keys    |key    |ref  |rows   |filtered|extra
-------------------------------------------------------------------------------------
Order |range  |item_id,combo_id |item_id|NULL |117003 |90.00   |Using index condition; Using where; Using MRR; Using temporary; Using filesort
Item  |eq_ref |PRIMARY,status   |PRIMARY|NULL |1      |52.57   |Using where;

但是，如果我添加过滤器 order_id > 0 来强制查询引擎使用 order_id 作为索引，则查询速度会快得多。只需要1秒。现在执行计划是这样的。

table |type   |possible_keys            |key    |ref  |rows   |filtered|extra
-------------------------------------------------------------------------------------
Order |range  |PRIMARY,item_id,combo_id |PRIMARY|NULL |117003 |45.00   |Using where; Using temporary; Using filesort
Item  |eq_ref |PRIMARY,status           |PRIMARY|NULL |1      |52.57   |Using where;

我真的不明白为什么这个黑客会起作用。为什么复合索引combo_id并不能提高查询速度？为什么强制 order_id 会使过滤后的数量减少一半？有人可以帮忙解释一下吗？

Answer 1

如果您像这样表达查询，它会如何工作？

select o.order_id, o.item_id, drank, i.*
from (select o.*,
             rank() over (partition by o.item_id 
                          order by order_id desc
                         ) as seqnum
      from orders o
     ) o join
     item i
     on i.item_id = o.item_id
where i.status <> 'D' and seqnum <= 5;

我认为这可以利用 orders(item_id, order_id) 上的索引作为 row_number()。这可能有助于提高性能。