MySQL 8.4并行查询特性配置与SQL执行计划优化实战

MySQL 8.4引入了增强的并行查询特性，允许单个SQL语句利用多CPU核心并行扫描和处理数据。该特性对OLAP类分析查询性能提升显著，但需要合理配置并行度、理解执行计划变化并评估资源占用。本文从并行查询配置到执行计划调优，给出生产环境实践方案。

MySQL 8.4并行查询架构与配置参数

MySQL并行查询通过worker线程分工处理数据分片，由coordinator线程汇总结果。核心配置：

-- 查看并行查询支持状态
SHOW VARIABLES LIKE 'innodb_parallel_read_threads';

-- 核心配置参数
SET GLOBAL innodb_parallel_read_threads = 16;
SET GLOBAL innodb_parallel_min_scan_tree_size = 65536;
SET GLOBAL innodb_parallel_scheduler_sleep_us = 200;
SET GLOBAL optimizer_parallel_hint_threshold = 100;

-- 查询级别指定并行度
SET SESSION innodb_parallel_read_threads = 8;

-- my.cnf持久化
[mysqld]
innodb_parallel_read_threads = 16
innodb_parallel_min_scan_tree_size = 65536
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 8

并行读线程数设置原则：不超过CPU逻辑核心数的50%，剩余核心处理coordinator和线上OLTP请求。innodb_parallel_min_scan_tree_size控制触发并行的最小扫描数据量，过低会导致小查询也启动并行overhead。

并行查询触发条件与执行计划分析

MySQL 8.4支持并行的SQL类型：全表扫描SELECT、带聚合的GROUP BY、无索引的join扫描、大范围INDEX SCAN。通过EXPLAIN FORMAT=JSON查看并行执行计划：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    INDEX idx_customer (customer_id),
    INDEX idx_date (order_date)
) ENGINE=InnoDB;

EXPLAIN FORMAT=JSON
SELECT customer_id, COUNT(*), SUM(amount)
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY customer_id;

-- EXPLAIN输出关键信息
{
  "query_block": {
    "grouping_operation": {
      "table": {
        "access_type": "range",
        "key": "idx_date",
        "rows_examined_per_scan": 9800000,
        "parallel_scan_info": {
          "parallel": true,
          "threads": 8,
          "chunks": 128
        }
      }
    }
  }
}

parallel_scan_info字段表明该查询将使用8个线程并行扫描128个数据块。若未出现该字段，说明未触发并行：

-- 排查并行未触发
-- 1. 查询代价是否够大
EXPLAIN FORMAT=JSON SELECT ...;
-- 2. 检查是否使用了高选择性access type导致不需要并行
-- 3. 强制启用并行（仅排查用）
SET SESSION optimizer_switch = 'parallel_query=on';
SELECT /*+ PARALLEL(8) */ customer_id, COUNT(*), SUM(amount)
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY customer_id;

并行查询性能压测与代价分析

1000万行orders表，64核256GB服务器，innodb_buffer_pool_size=64G：

16线程达到最优性能，32线程反而退化：worker线程间的结果合并存在mutex竞争，32线程时合并开销超过并行收益。

JOIN场景下的并行扫描策略

EXPLAIN FORMAT=JSON
SELECT /*+ PARALLEL(8) NO_INDEX_MERGE() */
    o.customer_id, o.amount, c.name, c.region
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-06-01'
  AND c.region IN ('east', 'west')
GROUP BY o.customer_id, c.name, c.region;

-- 小表作为驱动表走索引，大表作为被驱动表启用并行扫描
-- 强制Hash Join
SET SESSION optimizer_switch = 'hash_join=on';
EXPLAIN ANALYZE
SELECT o.customer_id, c.name, SUM(o.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.customer_id, c.name;

并行查询与OLTP资源冲突处理

混合负载下通过资源组隔离计算资源：

CREATE RESOURCE GROUP oltp_group
    TYPE = USER
    VCPU = 0-31
    THREAD_PRIORITY = 10;

CREATE RESOURCE GROUP olap_group
    TYPE = USER
    VCPU = 32-63
    THREAD_PRIORITY = 5;

ALTER USER 'analyst'@'%' RESOURCE GROUP olap_group;

-- 动态调整组配额
ALTER RESOURCE GROUP olap_group VCPU = 32-47;

并行查询常见问题排查

问题1：并行查询不生效

-- 检查optimizer并行开关
SELECT @@optimizer_switch LIKE '%parallel%';
-- 确认查询不包含不支持并行的特性
--   - 更新/删除语句不支持
--   - FULLTEXT索引查询不支持
-- 检查数据量是否达到阈值
SHOW TABLE STATUS LIKE 'orders';
-- 使用EXPLAIN ANALYZE查看实际执行
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders WHERE amount > 100;

问题2：并行查询导致锁等待

SET SESSION lock_wait_timeout = 300;
SET SESSION transaction_isolation = 'READ-COMMITTED';
SELECT /*+ PARALLEL(8) */ ... FROM orders ...;

问题3：并行查询内存消耗过大

SET SESSION tmp_table_size = 256 * 1024 * 1024;
SET SESSION max_heap_table_size = 256 * 1024 * 1024;
SET GLOBAL innodb_parallel_read_threads_adaptive = ON;

MySQL 8.4并行查询与ClickHouse对比

并行查询使MySQL在分析场景性能提升5-6倍，但相比ClickHouse仍有数量级差距。MySQL并行查询定位是中小规模AP查询，避免在MySQL上做TB级数据分析。

执行计划稳定性的Optimizer Hint使用

SELECT /*+ PARALLEL(8) NO_INDEX_MERGE(o) INDEX(c idx_region) */
    o.customer_id, c.name, SUM(o.amount) AS total
FROM orders o FORCE INDEX (PRIMARY)
JOIN customers c FORCE INDEX (idx_region)
    ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01'
  AND c.region = 'east'
GROUP BY o.customer_id, c.name
HAVING total > 10000;

-- Hint说明:
-- PARALLEL(8)      : 强制8线程并行
-- NO_INDEX_MERGE   : 禁止index merge优化
-- INDEX(c idx_region): 指定c使用idx_region索引
-- FORCE INDEX(PRIMARY): 强制主键扫描触发并行

MySQL并行查询适用场景以全表扫描分析为主，对于高选择性索引查询反而不如串行快。在混合负载系统中，合理设置并行度阈值、使用资源组隔离计算资源、配合执行计划Hint固化，能够让MySQL在不牺牲事务延迟的前提下处理中等规模的分析工作负载。