MySQL关联查询单条数据获取过慢问题排查求助

核心瓶颈分析 #

1. 前缀模糊查询导致索引失效：所有LIKE '%xxx%'形式的条件无法利用普通B-tree索引，迫使MySQL扫描符合store_id=407、is_open=0、is_submitted=1 OR is_closed=1的285384行数据，这是性能差的主要原因。
2. 关联表OR条件破坏LEFT JOIN逻辑：oth.transaction_id LIKE '%xxx%'会将LEFT JOIN隐式转为INNER JOIN（因为NULL值不满足该条件），且需要全关联后再过滤，增加额外开销。
3. GROUP BY和ORDER BY触发临时表与文件排序：执行计划中的Using temporary和Using filesort说明MySQL需要创建临时表存储分组结果，再进行排序，处理大量数据时耗时极长。
4. 无意义的类型转换：CAST(obo.id AS CHAR) LIKE '%xxx%'将数值型ID转为字符串查询，完全无法利用主键索引，且该条件大概率无匹配结果，属于无效查询逻辑。

针对性优化方案 #

方案1：改用全文索引替代模糊查询 #

MySQL全文索引专为文本搜索优化，比LIKE '%xxx%'效率高几个数量级。

• 为order_botorder创建覆盖查询字段的全文索引：

ALTER TABLE order_botorder ADD FULLTEXT INDEX idx_fulltext_obo_search (phone_number, order_counter, dine_in_table_number, delivery_address, occasion_raw, terminal_id, cashier_id, payment_type_raw, total, order_hash);

• 为order_ordertransactionhistory创建全文索引：

ALTER TABLE order_ordertransactionhistory ADD FULLTEXT INDEX idx_fulltext_oth_transaction (transaction_id);

• 修改查询语句，用MATCH AGAINST替代LIKE，并通过EXISTS优化关联表过滤：

SELECT
    obo.id, obo.ready_notified, obo.view_notified, obo.order_name,
    CONVERT_TZ(obo.submitted_at, 'GMT', 'America/New_York') AS submitted_at,
    obo.order_counter, obo.is_pos, obo.occasion, obo.dine_in_table_number,
    obo.cashier_id, obo.terminal_name, obo.terminal_id, obo.payment_type,
    obo.total, obo.is_paid_by_split, obo.payment_type_raw, obo.is_submitted,
    obo.is_cancelled, obo.is_split_cancelled,obo.has_refund, obo.has_adjustment, 
    obo.adjusted_total, obo.tip,
    obo.refund_total, obo.refund_pending, obo.order_hash,
    GROUP_CONCAT(oth.transaction_id) AS transaction_id
FROM
    order_botorder obo
LEFT JOIN order_ordertransactionhistory AS oth ON obo.id = oth.bot_order_id
WHERE
    obo.store_id = 407
    AND obo.is_open = 0
    AND (obo.is_submitted = 1 OR obo.is_closed = 1)
    AND (
        MATCH(obo.phone_number, obo.order_counter, obo.dine_in_table_number, obo.delivery_address, obo.occasion_raw, obo.terminal_id, obo.cashier_id, obo.payment_type_raw, obo.total, obo.order_hash) AGAINST ('tr_7AcXLYxUSK6K0WBVshqg1g' IN BOOLEAN MODE)
        OR EXISTS (
            SELECT 1 FROM order_ordertransactionhistory oth_sub 
            WHERE oth_sub.bot_order_id = obo.id 
            AND MATCH(oth_sub.transaction_id) AGAINST ('tr_7AcXLYxUSK6K0WBVshqg1g' IN BOOLEAN MODE)
        )
    )
GROUP BY obo.id
ORDER BY obo.submitted_at DESC
LIMIT 10 OFFSET 0;

方案2：优化基础过滤与排序的联合索引 #

创建包含基础过滤字段和排序字段的联合索引，减少扫描行数并避免文件排序：

CREATE INDEX idx_obo_base_sort ON order_botorder (store_id, is_open, is_submitted, is_closed, submitted_at);

该索引可快速定位符合store_id=407、is_open=0、is_submitted=1 OR is_closed=1的数据，且直接按submitted_at排序，消除Using filesort。

方案3：清理无效查询逻辑 #

删除CAST(obo.id AS CHAR) LIKE '%xxx%'条件，因为数值型ID不可能包含字符串前缀，该条件无实际意义，只会增加查询开销。

方案4：先过滤后关联，缩小数据范围 #

利用CTE先筛选出符合条件的订单，再关联交易历史，减少关联数据量：

WITH filtered_obo AS (
    SELECT * FROM order_botorder
    WHERE
        store_id = 407
        AND is_open = 0
        AND (is_submitted = 1 OR is_closed = 1)
        AND (
            phone_number LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR order_counter LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR dine_in_table_number LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR delivery_address LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR occasion_raw LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR terminal_id LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR cashier_id LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR payment_type_raw LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%' 
            OR total LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%'
            OR order_hash = 'tr_7AcXLYxUSK6K0WBVshqg1g'
        )
    ORDER BY submitted_at DESC
    LIMIT 10 OFFSET 0
)
SELECT
    obo.id, obo.ready_notified, obo.view_notified, obo.order_name,
    CONVERT_TZ(obo.submitted_at, 'GMT', 'America/New_York') AS submitted_at,
    obo.order_counter, obo.is_pos, obo.occasion, obo.dine_in_table_number,
    obo.cashier_id, obo.terminal_name, obo.terminal_id, obo.payment_type,
    obo.total, obo.is_paid_by_split, obo.payment_type_raw, obo.is_submitted,
    obo.is_cancelled, obo.is_split_cancelled,obo.has_refund, obo.has_adjustment, 
    obo.adjusted_total, obo.tip,
    obo.refund_total, obo.refund_pending, obo.order_hash,
    GROUP_CONCAT(oth.transaction_id) AS transaction_id
FROM filtered_obo obo
LEFT JOIN order_ordertransactionhistory AS oth ON obo.id = oth.bot_order_id
WHERE
    EXISTS (SELECT 1 FROM order_ordertransactionhistory oth_sub WHERE oth_sub.bot_order_id = obo.id AND oth_sub.transaction_id LIKE '%tr_7AcXLYxUSK6K0WBVshqg1g%')
    OR EXISTS (SELECT 1 FROM filtered_obo f WHERE f.id = obo.id)
GROUP BY obo.id
ORDER BY obo.submitted_at DESC;

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