MariaDB查询优化

Question

是否有优化此查询的选项。它已经运行了将近4个小时，但我仍然没有结果。该表的当前行计数为282359

select c.id
from capacity_log c
where c.id = (
select c1.id
from capacity_log c1
where  
c1.date_occurred < '2020-10-1' 
and c1.aux2 is null
and c1.order_product_id = c.order_product_id
and yearweek(c1.date_occurred) = yearweek(c.date_occurred)
order by c1.used_capacity desc limit 1
)

其想法是为每个order_product_id 取每周最大使用容量列的id

CREATE TABLE `capacity_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`date_occurred` DATETIME NOT NULL,
`ip_address` VARCHAR(255) NOT NULL DEFAULT '',
`order_product_id` INT UNSIGNED NOT NULL,
`serial` VARCHAR(255) NOT NULL DEFAULT '',
`used_capacity` BIGINT NULL DEFAULT NULL,
`aux2` INT NULL DEFAULT NULL,
`request` BLOB NULL,
`retry_count` INT NOT NULL DEFAULT '0',
`fetch_time` INT NOT NULL DEFAULT '0',
`response` BLOB NULL,
`custom_fetch_time` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `user_id` (`order_product_id`))


+------+--------------------+-------+------+---------------+---------+---------+--------------------------------+--------+-----------------------------+
| id   | select_type        | table | type | possible_keys | key     | key_len | ref                            | rows   | Extra                       |
+------+--------------------+-------+------+---------------+---------+---------+--------------------------------+--------+-----------------------------+
|    1 | PRIMARY            | c     | ALL  | NULL          | NULL    | NULL    | NULL                           | 390573 | Using where                 |
|    2 | DEPENDENT SUBQUERY | c1    | ref  | user_id       | user_id | 4       | web_license.c.order_product_id |    134 | Using where; Using filesort |
+------+--------------------+-------+------+---------------+---------+---------+--------------------------------+--------+-----------------------------+

版本：10.1.47-MariaDB-0+deb9u1

Answer 1

使用窗口功能，需要升级到MariaDB 10.2或更高版本：

select t.id
from (
select id, row_number() over (partition by order_product_id,
yearweek(date_occurred) order by used_capacity desc) as rownum
from capacity_log
where date_occurred < '2020-10-1'
and aux2 is null
) as t
where t.rownum = 1;

我认为以下索引将有助于此查询：

alter table capacity_log add index (aux2, date_occurred, order_product_id, used_capacity);

但我不使用MariaDB，所以我还没有测试过。