PolarDB MySQL SQL 优化指南（SQL优化系列 5）

liuaustin32025年09月10日约 20 分钟

❝
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

PolarDB for MySQL 云原生数据库的SQL优化，这集一定有人说，和MySQL优化有区别吗？你把那个吗去了，一样我写他做什么。尤其现在大部分人还都不明白，云原生和线下数据库的区别的当今，更应该让更多的人，知道云原生的数据库优化的手段和方法。

这里也有四句真言，针对PolarDB for MySQL的SQL优化。

PolarDB的优化，方法多来，眼缭乱。如若只会MYSQL，那你还的，勤学习，行列，并行加参数，优化那是手法多，架构拆分加代理，开发还要懂拆分。

这里我们先从第一个特性来说，读写分离。说到MySQL的读写分离，一言难尽就凭着BINLOG解析日志，产生REDO在从库的重新生成数据的方式，读写分离在MySQL中就不是一个好的方案，只能叫个方案。

但在PolarDB for MySQL中则不是，完全可以利用多个从节点，将select 的语句分流到只读节点上，因为PolarDB for MySQL可以做到读写分离中的读节点和写节点的数据是一致的，这是通过软硬兼施的方案，实现的功能。PolarDB 会智能的评判那个从节点性能更好，将SQL打入到从节点本身。

那么在掌握了MySQL的基本SQL优化手段这个咱们不提了，咱们今天只说PolarDB for MySQL 自己的优化方式。

下面详细讲一下代理，这里有几个地方需要解释 1 事务全拆分，这里我之前因为这个事情和PolarDB的负责这块的负责人有过争论，事实上我还是用老的MySQL的代理的思路去想这个事情，提出事务拆分会导致业务逻辑错误，但实时上不会，这块的负责人周老师给我解释。

1 PolarDB 的代理在协调SQL读取数据，完全会评判数据在不在从库，如果不在哪怕是事务全拆分，也不会有读不到，或者由于时间的问题读错的问题。

2 设置中玩意有超大事务，可以系统可以自动选择从主库读，只要你上面选择了主库是否接受读。

但得到的性能优势是显而易见的，把大事务全部拆分，里面的各种 select 可能会打散到多个只读节点，那么一个节点的性能瓶颈就不存在了。

同时在开启连接池，会大大降低PolarDB建立和释放连接的问题，连接会复用，大大降低内存的碎片和消耗。

第二个如何优化PolarDB for MySQL的查询方面的参数。这里我们先列出一些参数，然后我们在用案例去讲 PolarDB 独有的优化方式。我们将在文尾，将能统计的优化SQL的PolarDB参数进行列表。

这里我们举几个例子关于设置PolarDB 并行查询的例子

1 loose_max_parallel_degree 这个参数是设置每个SQL可以用到的并行，默认是0，通过PolarDB自己控制，具体的设置可以根据CPU的数量来进行调整。但调整过大的max_parallel_degree 会造成一个问题。

产生并行的SQL都是大型的SQL，而调大的情况下，会导致CPU资源不足，尤其在大SQL并行运行很多的情况下。

2 loose_cost_threshold_for_parallelism 这个参数是调整启用并行的标准，每个PolarDB启用并行是有cost标准的，这里默认是50000，具体可以根据自身的情况调整大或小。

这两个参数之间的关系是，如果你将cost调整小，会积极触发SQL并行，同时如果你的max_parallel_degree 调整的过大，会导致CPU不足的情况，反之如果Cost调大，max_parallel_degree 调整的小了，将对于并发高，大型SQL高的情况有利，但会造成部分SQL无法使用并行的情况，

3 loose_max_parallel_workers

这个参数是意义和上面的不同，这是一个池的概念，也就是假设我有 64个CPU的PolarDB 我想这里不想把所有的CPU都给SQL作为并行使用，这里我可以设置 loose_max_parallel_workers = 32，这里是告知系统，只有32个CPU参与SQL并行，其他的32个不参与并行计算。

在CPU更多的情况下，可以保证不会有超越性能极限的情况下的CPU进入并行资源池。

我们下面通过一个例子来查看启用并行后的情况，在SQL中先禁止并行，然后在打开并行的对比。

DELIMITER $$
CREATE DEFINER=`polar_admin`@`%` PROCEDURE `sp_load_test_data`()
BEGIN
    -- ====== 必须先声明变量（DECLARE 必须在 BEGIN 后最前面） ======
    DECLARE v_users INT DEFAULT 100000;        -- 最小表 100k
    DECLARE v_products INT DEFAULT 200000;     -- 产品 200k
    DECLARE v_customers INT DEFAULT 500000;    -- customers 500k
    DECLARE v_order_items INT DEFAULT 300000;  -- order_items 300k
    DECLARE v_orders INT DEFAULT 10000000;     -- orders 10M (最大表)

    -- ====== 会话级超时设置，防止客户端/连接断开 ======
    SET SESSION net_write_timeout    = 600;
    SET SESSION net_read_timeout     = 600;
    SET SESSION wait_timeout         = 28800;
    SET SESSION interactive_timeout  = 28800;
    SET SESSION MAX_EXECUTION_TIME   = 0;

    -- ====== 清理旧表（先删 seq_numbers 再建，避免重复） ======
    DROP TABLE IF EXISTS seq_numbers;
    DROP TABLE IF EXISTS order_items;
    DROP TABLE IF EXISTS orders;
    DROP TABLE IF EXISTS customers;
    DROP TABLE IF EXISTS products;
    DROP TABLE IF EXISTS users;

    -- ====== 建表：5 张表（无外键），含索引与 AUTO_INCREMENT ======
    CREATE TABLE users (
        user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        user_name VARCHAR(100),
        email VARCHAR(150),
        country VARCHAR(50),
        INDEX idx_email (email),
        INDEX idx_country (country)
    ) ENGINE=InnoDB;

    CREATE TABLE products (
        product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        product_name VARCHAR(200),
        category VARCHAR(100),
        price DECIMAL(10,2),
        INDEX idx_category (category),
        INDEX idx_price (price)
    ) ENGINE=InnoDB;

    CREATE TABLE customers (
        customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        customer_name VARCHAR(200),
        city VARCHAR(100),
        phone VARCHAR(50),
        INDEX idx_city (city)
    ) ENGINE=InnoDB;

    CREATE TABLE orders (
        order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        customer_id BIGINT,
        order_date DATETIME,
        status VARCHAR(50),
        INDEX idx_customer (customer_id),
        INDEX idx_order_date (order_date)
    ) ENGINE=InnoDB;

    CREATE TABLE order_items (
        item_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        order_id BIGINT,
        product_id BIGINT,
        quantity INT,
        price DECIMAL(10,2),
        INDEX idx_order (order_id),
        INDEX idx_product (product_id)
    ) ENGINE=InnoDB;

    -- ====== 生成序列表 seq_numbers（1..10,000,000） ======
    -- 说明：用 7 次 digits CROSS JOIN 生成 10^7 行，再 LIMIT 10000000
    CREATE TABLE seq_numbers (n INT PRIMARY KEY) ENGINE=InnoDB;

    /* 初始化用户变量并填充 seq_numbers 到 10,000,000（一次性生成）
       注意：此语句会产生大量行并占用资源，执行时间视实例规格而定 */
    INSERT INTO seq_numbers (n)
    SELECT @row := @row + 1 AS n
    FROM
      (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
    CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e
    CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) f
    CROSS JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) g
    CROSS JOIN (SELECT @row := 0) r
    LIMIT 10000000;  -- 固定生成 10,000,000 个序号

    -- ====== 批量插入：从 seq_numbers 中取前 N 条，避免逐行插入 ======
    -- users 100,000
    INSERT INTO users (user_name, email, country)
    SELECT CONCAT('user_', n),
           CONCAT('user_', n, '@example.com'),
           ELT(1 + FLOOR(RAND()*5), 'China','USA','UK','Germany','India')
    FROM seq_numbers
    WHERE n <= 100000;

    -- products 200,000
    INSERT INTO products (product_name, category, price)
    SELECT CONCAT('product_', n),
           ELT(1 + FLOOR(RAND()*8), 'Electronics','Books','Clothing','Food','Sports','Home','Beauty','Toys'),
           ROUND(RAND()*1000, 2)
    FROM seq_numbers
    WHERE n <= 200000;

    -- customers 500,000
    INSERT INTO customers (customer_name, city, phone)
    SELECT CONCAT('customer_', n),
           ELT(1 + FLOOR(RAND()*6), 'Beijing','Shanghai','Shenzhen','Guangzhou','NewYork','London'),
           CONCAT('1', LPAD(FLOOR(RAND()*1000000000), 10, '0'))
    FROM seq_numbers
    WHERE n <= 500000;

    -- orders 10,000,000 （可能耗时，依赖 seq_numbers 生成的大序列）
    INSERT INTO orders (customer_id, order_date, status)
    SELECT
      1 + FLOOR(RAND() * 500000),  -- random customer id within 1..500k
      DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*3650) DAY), -- 随机近10年日期
      ELT(1 + FLOOR(RAND()*4), 'PENDING','PAID','SHIPPED','CANCELLED')
    FROM seq_numbers
    WHERE n <= 10000000;

    -- order_items 300,000
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT
      1 + FLOOR(RAND() * 10000000),  -- random order id within 1..10M
      1 + FLOOR(RAND() * 200000),    -- random product id within 1..200k
      1 + FLOOR(RAND() * 10),
      ROUND(RAND()*500, 2)
    FROM seq_numbers
    WHERE n <= 300000;

    -- ====== 可选：删除 seq_numbers（若希望释放空间） ======
    -- DROP TABLE IF EXISTS seq_numbers;

END$$
DELIMITER ;

通过存储过程，我们产生测试表和数据。

通过执行固定的SQL语句，在不添加任何优化措施的情况下，我们查看通过并行打开后的结果差

分别是不打开并行，打开4个并行，打开2个并行

从上图，开启4个并行后，执行时间比不开启并行速度要快 32%，开启两个并行快 29%。

这里可以得出，开启并行后，的确比不开要快，但开启过多的并行并未得到更快线性的速度提升。

EXPLAIN ANALYZE
SELECT /*+ PARALLEL(4) */
    u.user_id,
    u.user_name,
    COUNT(o.order_id) AS total_orders,
    SUM(oi.quantity * p.price) AS total_amount
FROM users u
JOIN orders o
    ON u.user_id = o.customer_id
JOIN order_items oi
    ON o.order_id = oi.order_id
JOIN products p
    ON oi.product_id = p.product_id
GROUP BY u.user_id, u.user_name
ORDER BY total_amount DESC
LIMIT 10;

在四句真言里面还有一个部分是列式，如果我们打开列式的情况下，这个SQL能在提速吗？我们在进行测试。

![列式节点](https://files.mdnice.com/user/47359/beb85a98-de30-4f4b-9f44-ccf994371369.png)

大家可以看上图，在PolarDB for MySQL不进行任何的特殊优化，还是刚才那个语句，速度直接从3.4秒，变成了0.015秒，仅仅是对这些表添加了列式索引。速度提升了227倍。

use  test;
-- ============================
-- 1. users 表
-- ============================
ALTER TABLE users COMMENT 'COLUMNAR=1';

ALTER TABLE users 
MODIFY COLUMN user_id INT COMMENT 'COLUMNAR=1',
MODIFY COLUMN user_name VARCHAR(50) COMMENT 'COLUMNAR=1';

-- ============================
-- 2. orders 表
-- ============================
ALTER TABLE orders COMMENT 'COLUMNAR=1';

ALTER TABLE orders
MODIFY COLUMN order_id INT COMMENT 'COLUMNAR=1',
MODIFY COLUMN customer_id INT COMMENT 'COLUMNAR=1';

-- ============================
-- 3. order_items 表
-- ============================
ALTER TABLE order_items COMMENT 'COLUMNAR=1';

ALTER TABLE order_items
MODIFY COLUMN order_id INT COMMENT 'COLUMNAR=1',
MODIFY COLUMN product_id INT COMMENT 'COLUMNAR=1',
MODIFY COLUMN quantity INT COMMENT 'COLUMNAR=1';

-- ============================
-- 4. products 表
-- ============================
ALTER TABLE products COMMENT 'COLUMNAR=1';

ALTER TABLE products
MODIFY COLUMN product_id INT COMMENT 'COLUMNAR=1',
MODIFY COLUMN price DECIMAL(10,2) COMMENT 'COLUMNAR=1';

到这里前面的两句算是解释完毕，一句话，科技改变MySQL的SQL语句执行。

剩下还有PolarDB的本身的一些优化方式和方法，这里也总结一下，查询改写子查询，IN的谓词转成JOIN，子查询解关联，连接条件可下推。

由于内容太多，今天我们只挑选其中的一条连接条件下推来说说POLARDB for mysql的优化部分，首先必须是8.02版本，且是2.10版本以上的PolarDB.原理是，将驱动表外层的链接条件下推到驱动表内部，使得驱动表内部的执行计划更高效的利用索引，从而大幅度提升查询性能，

要打开这个性能的开会需要从loose_polar_optimizer_switch 中的 'join_predicate_pushdown=ON'。loose_join_predicate_pushdown_opt_mode

'join_predicate_pushdown=ON,loose_join_predicate_pushdown_opt_mode=replica_on'

这部分的原理是驱动表与被驱动表中的条件过滤的问题，我们看下面的案例

--优化前
SELECT *
FROM (
  SELECT *
  FROM sample_table.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM sample_table.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN (
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM sample_table.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
  ) os
  ON od.id = os.detail_id;

  ---优化后
  SELECT *
FROM (
  SELECT *
  FROM db_order.tb_order
  WHERE create_date >= DATE_SUB(CAST('2022-12-05 15:12:05' AS datetime), INTERVAL 5 MINUTE)
    AND product_type IN (2, 4)
) o
  LEFT JOIN (
    SELECT *
    FROM db_order.tb_order_detailed
    WHERE update_time >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
  ) od
  ON o.order_id = od.order_id
  LEFT JOIN LATERAL((
    SELECT t.*, row_number() OVER (PARTITION BY detail_id ORDER BY update_date DESC) AS rn
    FROM db_order.tb_order_sku t
    WHERE update_date >= DATE_SUB('2022-12-05 15:12:05', INTERVAL 50 MINUTE)
      AND coalesce(product_type, '0') <> '5'
      AND od.id = detail_id
  )) os;