MySQL语句执行缓慢且索引误用问题排查求助

主查询执行耗时约12-15秒，核心问题出在第一个COALESCE中的子查询：

• 将该子查询替换为固定值"0"时，查询仅需0.0051秒
• 单独执行指定client_id的该子查询，速度正常

涉及的rest_io_log表包含500万+数据，已创建相关索引：

• 单字段索引：timestamp（仅包含timestamp字段）
• 联合索引：index_account_id_client_id_timestamp（字段顺序account_id, client_id, timestamp）

初始EXPLAIN执行计划 #

初始执行计划分析 #

尽管存在适合的联合索引index_account_id_client_id_timestamp，优化器却选择了timestamp单字段索引。由于查询同时使用account_id和client_id进行等值过滤，单字段索引无法有效缩小数据范围，导致全索引扫描，性能急剧下降。

强制使用索引后的情况 #

在子查询中添加USE INDEX (index_account_id_client_id_timestamp)后，执行时间降至8秒，对应的EXPLAIN结果如下：

优化后执行计划分析 #

强制使用联合索引后，查询类型从index变为ref，可以通过account_id和client_id快速定位数据，扫描行数从预估2行（实际远多）变为1972行，性能得到提升，但仍有优化空间。

完整SQL语句（带强制索引） #

SELECT
    cl.timestamp AS active_since,
    GREATEST
    (
        COALESCE
        (
            (
                SELECT
                    timestamp AS last_request
                FROM
                    rest_io_log USE INDEX (index_account_id_client_id_timestamp)
                WHERE
                    account_id = 12345 AND
                    client_id = cl.client_id
                ORDER BY
                    timestamp DESC
                LIMIT
                    1
            ),
            "0000-00-00 00:00:00"
        ),
        COALESCE
        (
            (
                SELECT
                    CONCAT(date, " 00:00:00") AS last_request
                FROM
                    traffic
                WHERE
                    account_id = 12345 AND
                    client_id = cl.client_id
                ORDER BY
                    date DESC
                LIMIT
                    1
            ),
            "0000-00-00 00:00:00"
        )
    ) AS last_request,
    (
        SELECT
            requests
        FROM
            traffic
        WHERE
            account_id = 12345 AND
            client_id = cl.client_id AND
            date=NOW()
    ) AS traffic_today,
    cl.client_id AS user_account_name,
    t.rate_name,
    t.rate_traffic,
    t.rate_price
FROM
    clients AS cl
        LEFT JOIN
        (
            SELECT
                id AS rate_id,
                name AS rate_name,
                daily_max_traffic AS rate_traffic,
                price AS rate_price
            FROM
                rates
        ) AS t
        ON cl.rate=t.rate_id
WHERE
    cl.user_id LIKE "12345|%"
  AND
    cl.client_id LIKE "api_%"
  AND
    cl.client_id LIKE "%_12345"
;

查询结果 #

解决方案 #

1. 更新表统计信息 #

MySQL优化器依赖表统计信息选择索引，执行以下命令更新rest_io_log的统计数据，让优化器能准确判断索引效率：

ANALYZE TABLE rest_io_log;

执行后移除USE INDEX强制语句，重新执行查询，观察优化器是否自动选择正确的联合索引。

2. 改写依赖子查询为JOIN #

依赖子查询会对clients返回的每一行执行一次子查询，当结果行数较多时性能较差。将子查询改写为LEFT JOIN + GROUP BY的形式，批量获取所需数据：

SELECT
    cl.timestamp AS active_since,
    GREATEST(
        COALESCE(ril.last_request, "0000-00-00 00:00:00"),
        COALESCE(t.last_request, "0000-00-00 00:00:00")
    ) AS last_request,
    tt.requests AS traffic_today,
    cl.client_id AS user_account_name,
    t.rate_name,
    t.rate_traffic,
    t.rate_price
FROM clients AS cl
LEFT JOIN (
    SELECT id AS rate_id, name AS rate_name, daily_max_traffic AS rate_traffic, price AS rate_price
    FROM rates
) AS t ON cl.rate = t.rate_id
LEFT JOIN (
    SELECT client_id, MAX(timestamp) AS last_request
    FROM rest_io_log
    WHERE account_id = 12345
    GROUP BY client_id
) AS ril ON cl.client_id = ril.client_id
LEFT JOIN (
    SELECT client_id, CONCAT(MAX(date), " 00:00:00") AS last_request
    FROM traffic
    WHERE account_id = 12345
    GROUP BY client_id
) AS t ON cl.client_id = t.client_id
LEFT JOIN (
    SELECT client_id, requests
    FROM traffic
    WHERE account_id = 12345 AND date = NOW()
) AS tt ON cl.client_id = tt.client_id
WHERE
    cl.user_id LIKE "12345|%"
    AND cl.client_id LIKE "api_%"
    AND cl.client_id LIKE "%_12345";

这种方式将多次子查询合并为批量聚合查询，大幅减少执行次数，提升性能。

3. 优化WHERE条件中的LIKE语句 #

cl.client_id LIKE "%_12345"使用了前置通配符，会导致client_id相关索引失效。如果12345是固定长度的后缀，可以改为：

RIGHT(cl.client_id, 5) = '12345'

如果后缀长度不固定，建议将client_id拆分为前缀和后缀字段单独存储，或使用全文索引优化模糊查询。

4. 验证索引有效性 #

确保index_account_id_client_id_timestamp索引的字段顺序正确，当前account_id, client_id, timestamp的顺序符合查询需求：

• 前两个字段用于等值过滤，最后一个字段用于排序
• 该索引是覆盖索引，查询仅需访问索引即可获取timestamp，无需回表

内容的提问来源于stack exchange，提问作者Charliexyx