從子表中選擇下一個待辦事項

我有一個包含數百萬條記錄的表，關係是一個（對象）對多個（object_items）。

對象項目：

CREATE TABLE `object_items` (
 `item_name` varchar(50) NOT NULL DEFAULT '',
 `object_id` int(10) unsigned NOT NULL DEFAULT '0',
 `sequence` int(10) unsigned NOT NULL,
 `completed` tinyint(1) NOT NULL DEFAULT '0',
 `is_active` tinyint(1) NOT NULL DEFAULT '0',
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uni_seq_object_id` (`sequence`,`object_id`),
 KEY `idx_object_id` (`object_id`),
 KEY `idx_seq` (`sequence`)
) ENGINE=InnoDB AUTO_INCREMENT=3408237 DEFAULT CHARSET=utf8mb4  

樣本數據：

+-----------+-----------+----------+-----------+------+
| item_name | object_id | sequence | completed | id   |
+-----------+-----------+----------+-----------+------+
| ABCD      |        10 |        1 |         1 |    1 |
| BCDE      |        10 |        2 |         1 |    2 |
| CDEF      |        10 |        3 |         1 |    3 |
| DEFG      |        10 |        4 |         0 |    4 |
| ABCD      |        11 |        1 |         1 |    5 |
| BCDE      |        11 |        2 |         1 |    6 |
| CDEF      |        11 |        3 |         0 |    7 |
| DEFG      |        11 |        4 |         0 |    8 |
| ABCD      |        12 |        1 |         1 |    9 |
| BCDE      |        12 |        2 |         1 |   10 |
+-----------+-----------+----------+-----------+------+

期望的結果：

+-----------+-----------+----------+-----------+------+
| item_name | object_id | sequence | completed | id   |
+-----------+-----------+----------+-----------+------+
| DEFG      |        10 |        4 |         0 |    4 |
| CDEF      |        11 |        3 |         0 |    7 |
+-----------+-----------+----------+-----------+------+

我執行的查詢：

select
 a.*
from object_items a
where a.sequence = (
 select min(sequence)
 from object_items b
 where a.object_id = b.object_id
   and b.completed = 0
)

這實際上有效，但是當我使用限制時，但如果我執行count(*)它就會死掉。

解釋查詢：

+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key           | key_len | ref             | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL          | NULL    | NULL            | 3268598 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ref  | idx_object_id | idx_object_id | 4       | db.a.object_id  |      21 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+---------------+---------+-----------------+---------+----------+-------------+

有沒有更好的方法來獲取下一個尚未完成的 TODO 項目，按順序，只針對那些至少有一個項目要完成的對象，像這樣的重型數據庫？

謝謝

一步步。首先查找未完成的 object_items 和 min(sequence)。這是用

-- Select object_items that are *not* completed, find min(sequence)
SELECT
   object_id, min(sequence) AS sequence
FROM
   object_items a
WHERE
   completed = 0 
GROUP BY
   object_id
ORDER BY
   object_id, sequence ;

object_id | 順序
--------: | -------:
10 | 4
11 | 3

您可以JOIN返回原始表以檢索其餘相應的行數據：

SELECT
   item_name, q.object_id, q.sequence, completed, id
FROM
   (
   -- Select object_items that are *not* completed
   SELECT
       object_id, min(sequence) AS sequence
   FROM
       object_items a
   WHERE
       completed = 0 
   GROUP BY
       object_id
   ) AS q 
   JOIN object_items oi
        ON oi.object_id  = q.object_id 
       AND oi.sequence   = q.sequence
ORDER BY
   q.object_id, q.sequence ;

項目名稱 | object_id | 序列 | 完成| ID
:-------- | --------: | -------: | --------: | -:
DEFG | 10 | 4 | 0 | 4
CDEF | 11 | 3 | 0 | 7

查詢計劃看起來足夠合理，可以很好地擴展（它不需要每次DEPENDENT SUBQUERY數據庫循環時都重新評估）：

編號 | 選擇類型 | 表| 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 額外的 
-: | :---------- | :--------- | :----- | :-------------------------------------- | :---------------- | :------ | :--------------------- | ---: | :-------------------------------------------
1 | 初級 | <派生2> | 全部 | *空* | *空* | *空* | *空* | 10 | 使用哪裡；使用文件排序 
1 | 初級 | 我 | eq_ref | uni_seq_object_id,idx_object_id,idx_seq | uni_seq_object_id | 8 | q.sequence,q.object_id | 1 | 
2 | 派生 | 一個 | 全部 | *空* | *空* | *空* | *空* | 10 | 使用哪裡；使用臨時的；使用文件排序

添加以下索引會使計劃變得更好，因為它允許覆蓋名為 的查詢q：

CREATE INDEX idx_object_items_completed_object_id_sequence 
   ON object_items (completed, object_id, sequence) ; 

編號 | 選擇類型 | 表| 類型 | 可能的鍵 | 關鍵 | key_len | 參考 | 行 | 額外的 
-: | :---------- | :--------- | :----- | :-------------------------------------------- | :-------------------------------------------- | :------ | :--------------------- | ---: | :--------------------------
1 | 初級 | <派生2> | 全部 | *空* | *空* | *空* | *空* | 3 | 使用哪裡；使用文件排序
1 | 初級 | 我 | eq_ref | uni_seq_object_id,idx_object_id,idx_seq | uni_seq_object_id | 8 | q.sequence,q.object_id | 1 | 
2 | 派生 | 一個 | 參考 | idx_object_items_completed_object_id_sequence | idx_object_items_completed_object_id_sequence | 1 | 常量 | 3 | 使用哪裡；使用索引 

你可以在*這裡*查看dbfiddle的所有內容

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