mysql - 高性能查询来统计多对多数据

Question

请求表:

requests
    +id (INT) AUTO_INCREMENT
    +title (VARCHAR)

状态表:

statuses
    +id (INT) AUTO_INCREMENT
    +title (VARCHAR)

关系表:

request_status (MANY TO MANY)
    +id (INT) AUTO_INCREMENT
    +request_id (INT) Foreign Key
    +status_id (INT)

我只想计算当前 status_id 为 2 的请求。当前请求的状态 (status_id) 是 request_status 表中的最后一个。

如果数据量在 1600k 左右，什么才是完美的高性能查询

Answer 1

假设最新状态是id最大的状态:

SELECT COUNT(*)
FROM request_status
WHERE status_id = 2
AND NOT EXISTS (
    SELECT 1
    FROM request_status AS x
    WHERE request_id = request_status.request_id
    AND id > request_status.id
)

或者这个:

SELECT COUNT(*)
FROM (
    SELECT 1
    FROM request_status
    GROUP BY request_id
    HAVING MAX(CASE WHEN status_id = 2 THEN id END) = MAX(id)
) AS x

您将需要一些索引。我建议创建这些:

KEY ix1 (request_id, status_id)
KEY ix2 (status_id,  request_id)