mysql - 慢查询。错误的数据库结构？

Question

我有一个包含任务表的数据库。任务有一个生命周期。任务生命周期的状态可以改变。这些状态转换存储在单独的表 tasktransitions 中。现在我编写了一个查询来查找所有打开/重新打开的任务和最近更改的任务，但我已经看到执行时间变得非常长(>0.5s)的任务数量相当少(<1000)。

任务

+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| taskid      | int(11) | NO   | PRI | NULL    | auto_increment |
| description | text    | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

任务转换

+------------------+-----------+------+-----+-------------------+----------------+
| Field            | Type      | Null | Key | Default           | Extra          |
+------------------+-----------+------+-----+-------------------+----------------+
| tasktransitionid | int(11)   | NO   | PRI | NULL              | auto_increment |
| taskid           | int(11)   | NO   | MUL | NULL              |                |
| status           | int(11)   | NO   | MUL | NULL              |                |
| description      | text      | NO   |     | NULL              |                |
| userid           | int(11)   | NO   |     | NULL              |                |
| transitiondate   | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
+------------------+-----------+------+-----+-------------------+----------------+

查询

SELECT tasks.taskid,tasks.description,tasklaststatus.status
FROM tasks
LEFT OUTER JOIN
(
    SELECT tasktransitions.taskid,tasktransitions.transitiondate,tasktransitions.status
    FROM tasktransitions
    INNER JOIN 
    (
        SELECT taskid,MAX(transitiondate) AS lasttransitiondate
        FROM tasktransitions
        GROUP BY taskid
    ) AS tasklasttransition ON tasklasttransition.lasttransitiondate=tasktransitions.transitiondate AND tasklasttransition.taskid=tasktransitions.taskid
) AS tasklaststatus ON tasklaststatus.taskid=tasks.taskid
WHERE tasklaststatus.status IS NULL OR tasklaststatus.status=0 or tasklaststatus.transitiondate>'2013-09-01';

上述查询的更具可读性、描述性更少的版本:

SELECT t.taskid, t.description,ls.status
FROM tasks AS t
LEFT OUTER JOIN
(
    SELECT tt.taskid, tt.transitiondate, tt.status
    FROM tasktransitions AS tt
    INNER JOIN 
    (
        SELECT taskid,MAX(transitiondate) AS transitiondate
        FROM tasktransitions
        GROUP BY taskid
    ) AS lt USING (taskid,transitiondate)
) AS ls USING (taskid)
WHERE ls.status IS NULL OR ls.status=0 or ls.transitiondate>'2013-09-01';

我想知道数据库结构是否是性能方面的最佳选择。添加索引有帮助吗？我已经尝试添加一些，但没有看到很大的改进。

+-----------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name       | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tasktransitions |          0 | PRIMARY        |            1 | tasktransitionid | A         |         896 |     NULL | NULL   |      | BTREE      |         |               |
| tasktransitions |          1 | taskid_date_ix |            1 | taskid           | A         |         896 |     NULL | NULL   |      | BTREE      |         |               |
| tasktransitions |          1 | taskid_date_ix |            2 | transitiondate   | A         |         896 |     NULL | NULL   |      | BTREE      |         |               |
| tasktransitions |          1 | status_ix      |            1 | status           | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

还有什么建议吗？

Answer 1

未经测试，不确定这是否是您的目标，但仅使用一个子查询。

SELECT 
    tasks.taskid, tasks.description, 
    (SELECT MAX(transitiondate) 
    FROM tasktransitions WHERE tasktransitions.taskid=tasks.id) lasttransitiondate
FROM tasks
LEFT JOIN tasktransitions tt ON tt.taskid = tasks.id
WHERE tt.tasktransitionid IS NULL 
    OR tt.tasktransitionid = 0 
    OR tt.transitiondate > '2013-09-01';

要完全避免子查询，您可以向 tasks 表添加一个 last_trasition_id 外键，并通过在插入到任务转换。