MySQL多表检索慢查询优化：索引配置与SQL改写方案咨询

一、索引优化 #

针对你的表结构和查询逻辑，创建以下复合索引，让每个查询分支都能高效利用索引：

1. company_customer表索引

CREATE INDEX idx_cc_company_number_customer ON company_customer (company_id, number, customer_id);
CREATE INDEX idx_cc_company_customer_number ON company_customer (company_id, customer_id, number);

• idx_cc_company_number_customer：快速过滤company_id=1且number LIKE 'J%'的行，同时直接获取customer_id用于关联，避免回表。
• idx_cc_company_customer_number：通过customer_id快速关联company_customer并获取number，适配从customer表发起的查询。

2. customer表索引

CREATE INDEX idx_customer_lastname_firstname ON customer (lastname, firstname, id);
CREATE INDEX idx_customer_firstname_lastname ON customer (firstname, lastname, id);

• idx_customer_lastname_firstname：匹配lastname LIKE 'J%'的条件，同时包含firstname（支持排序）和id（支持关联），实现覆盖查询，无需回表取数据。
• idx_customer_firstname_lastname：匹配firstname LIKE 'J%'的条件，同样覆盖排序和关联所需字段。

二、SQL语句改写 #

原查询的OR条件会导致MySQL无法高效利用多个索引，且数据量大时会触发全量关联+排序的低效逻辑。将查询拆分为三个独立分支（用UNION ALL合并），避免重复数据后再排序取结果：

SELECT number, firstname, lastname
FROM (
    -- 分支1：lastname以J开头的客户
    SELECT cc.number, c.firstname, c.lastname
    FROM customer c
    JOIN company_customer cc ON cc.customer_id = c.id
    WHERE cc.company_id = 1 
      AND c.lastname LIKE 'J%'

    UNION ALL

    -- 分支2：firstname以J开头，但lastname不以J开头（避免与分支1重复）
    SELECT cc.number, c.firstname, c.lastname
    FROM customer c
    JOIN company_customer cc ON cc.customer_id = c.id
    WHERE cc.company_id = 1 
      AND c.firstname LIKE 'J%'
      AND c.lastname NOT LIKE 'J%'

    UNION ALL

    -- 分支3：number以J开头，但姓名都不以J开头（避免与前两个分支重复）
    SELECT cc.number, c.firstname, c.lastname
    FROM company_customer cc
    JOIN customer c ON cc.customer_id = c.id
    WHERE cc.company_id = 1 
      AND cc.number LIKE 'J%'
      AND c.lastname NOT LIKE 'J%'
      AND c.firstname NOT LIKE 'J%'
) AS combined_results
ORDER BY lastname, firstname
LIMIT 20;

改写逻辑说明 #

• 每个分支单独使用最优索引：分支1用idx_customer_lastname_firstname+idx_cc_company_customer_number，分支2用idx_customer_firstname_lastname+idx_cc_company_customer_number，分支3用idx_cc_company_number_customer+customer主键索引。
• UNION ALL比UNION更高效（无需去重），通过条件过滤避免重复数据。
• 最终仅对三个分支的结果合并排序，数据量远小于原查询的全量关联数据，彻底避免Using temporary; Using filesort的开销。

三、原查询慢的原因 #

从你的EXPLAIN结果可以看出：

• 数据量大时，MySQL选择先扫描company_customer中所有company_id=1的行（约20万条），再逐个关联customer表，之后过滤LIKE条件，最后对所有符合条件的数据排序。这个过程需要大量IO和内存操作，触发临时表和文件排序，导致耗时过长。
• 数据量小时，MySQL选择从customer表的IDX_lf索引直接取20条符合条件的行，再关联company_customer，所以速度快，但这个执行计划在数据量大时会被优化器放弃（因为索引扫描范围过大）。

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