MySQL 5.7大数据集Union All查询耗时10分钟，求优化方案

一、优化索引（最核心的点） #

你说已经给涉及字段加了索引，但索引的字段顺序和覆盖能力可能没踩对：

• 针对两个子查询的过滤逻辑，创建复合覆盖索引：
  第一个子查询要过滤customer_id, product_code, day，还要用到session_ip_address, session_id，直接建这个索引：

  CREATE INDEX idx_tableA_cust_prod_day_ip_session ON tableA(customer_id, product_code, day, session_ip_address, session_id);
  

  第二个子查询多了个metric过滤，还要用到count，建这个索引：

  CREATE INDEX idx_tableA_cust_prod_day_metric_ip_count ON tableA(customer_id, product_code, day, metric, session_ip_address, count);
  

  这两个索引能让MySQL直接从索引里拿所有需要的数据，不用回表查原表，IO开销会大幅降低。
• 别在GROUP BY里用DATE_FORMAT(day, ...)生成的date字段分组，改成先按原始day分组，再在外面的查询里格式化日期，能减少分组时的计算量。

二、合并查询，减少表扫描次数 #

你现在两个子查询都扫了tableA里相同的customer_id, product_code, day范围，等于读了两次同一份数据，完全可以改成只扫一次：
因为MySQL 5.7不支持WITH语法，用临时表来实现：

-- 先把符合条件的数据一次性捞出来存临时表
CREATE TEMPORARY TABLE temp_filtered_data
SELECT 
    product_code,
    session_ip_address,
    day,
    session_id,
    metric,
    count
FROM tableA
WHERE 
    customer_id IN (?) 
    AND product_code = ? 
    AND day IN (?);

-- 再从临时表里分别处理两个子查询的逻辑
SELECT 
    product_code AS product_id,
    UPPER(session_ip_address) AS identifier,
    DATE_FORMAT(day, '%d %b %Y') AS date,
    day,
    'Event_SubSessions' AS metric,
    COUNT(DISTINCT session_id) AS count
FROM temp_filtered_data
GROUP BY day, session_ip_address
UNION ALL
SELECT 
    product_code AS product_id,
    UPPER(session_ip_address) AS identifier,
    DATE_FORMAT(day, '%d %b %Y') AS date,
    day,
    metric,
    SUM(count) AS count
FROM temp_filtered_data
WHERE metric IN ('Searches_Regular', 'Event_Citation', 'Event_Record_Print',
                 'Event_Record_Views', 'Event_Record_Export', 'Event_Record_Save',
                 'Event_Result_Clicks', 'Event_Record_Email')
GROUP BY day, session_ip_address, metric
ORDER BY date, identifier, metric;

-- 用完临时表删掉
DROP TEMPORARY TABLE temp_filtered_data;

这样只扫一次原表，IO直接减半，速度提升明显。

三、优化分组和排序的计算逻辑 #

• 把UPPER(identifier)的字符串转换放到最外层查询，别在分组的时候处理，减少分组阶段的计算量。
• 排序的时候别用格式化后的date字段，因为DATE_FORMAT(day, '%d %b %Y')的顺序和原始day字段的顺序是完全一致的，直接按day, identifier, metric排序就行，字符串排序比日期类型排序慢很多：

-- 基于上面的临时表，调整排序逻辑
SELECT 
    product_id,
    UPPER(identifier) AS identifier,
    DATE_FORMAT(day, '%d %b %Y') AS date,
    day,
    metric,
    count
FROM (
    SELECT 
        product_code AS product_id,
        session_ip_address AS identifier,
        day,
        'Event_SubSessions' AS metric,
        COUNT(DISTINCT session_id) AS count
    FROM temp_filtered_data
    GROUP BY day, session_ip_address
    UNION ALL
    SELECT 
        product_code AS product_id,
        session_ip_address AS identifier,
        day,
        metric,
        SUM(count) AS count
    FROM temp_filtered_data
    WHERE metric IN ('Searches_Regular', 'Event_Citation', 'Event_Record_Print',
                     'Event_Record_Views', 'Event_Record_Export', 'Event_Record_Save',
                     'Event_Result_Clicks', 'Event_Record_Email')
    GROUP BY day, session_ip_address, metric
) AS records
ORDER BY day, identifier, metric;

四、调整MySQL配置参数 #

针对大数据量查询，调几个参数能帮上忙：

• sort_buffer_size：调大排序缓冲区，避免MySQL用磁盘做临时排序，建议设成2M-4M（根据服务器内存来，别设太大）。
• tmp_table_size和max_heap_table_size：调大临时表的内存上限，避免临时表写到磁盘，建议设成64M-128M。
• innodb_buffer_pool_size：如果用的是InnoDB引擎，把这个值设成服务器内存的50%-70%，让更多数据存在内存里，减少磁盘读取。

五、其他可选优化 #

• 给tableA按day分区：如果数据是按日期增长的，按月或者按季度分区，查询时只会扫描对应日期的分区，不用扫全表。
• 检查COUNT(DISTINCT session_id)：如果同day+session_ip_address下session_id重复不多，或者业务允许近似值，可以考虑预先统计，替换掉这个比较耗时的计算。
• 排查锁等待：有时候查询慢不是因为本身性能差，是被其他事务锁了，用SHOW PROCESSLIST看看有没有锁阻塞的情况。

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