PolarDB MySQL SQL 优化指南（SQL优化系列 5）-EW帮帮网

❝
开头还是介绍一下群，如果感兴趣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;

上面的查询已经演示了，如果开启上面的查询特殊的功能后， AND od.id = detail_id 将推入到本身OS查询的内部，来提前过滤数据。SQL的执行效率会大大提升。

至于其他的优化方法和参数我们在找时间单独去书，到这里PolarDB的 SQL优化方法相对MySQL本身丰富多，这就直接让MySQL可以支持更多丰富的复杂得SQL的计算，为复杂的应用撰写复杂的SQL，且高效运行提供了条件，且不用去大量的改写SQL。

置顶

IF-Club 你提意见拿礼物 AustinDatabases 破 10000

开发欺负我 Redis 的大 keys的问题，我一个DBA怎么解决？

云基座技术是大厂专有，那小厂和私有云的出路在哪里？

MongoDB 查询优化指南四句真言（查询优化系列 4）

沧海要，《SQL SERVER 运维之道》，清风笑，竟惹寂寥

MySQL SQL 优化指南 SQL 四句真言（优化系列 3）

沧海要，《SQL SERVER 运维之道》，清风笑，竟惹寂寥

SQL SERVER SQL 优化指南四句真言（SQL 优化系列 2）

PostgreSQL SQL 优化指南四句真言（SQL 优化系列 1）