P-MySQL SQL优化案例，反观MySQL不死没有天理-CSDN博客

本文链接：https://blog.csdn.net/liuhuayang/article/details/148472549

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

一直使用PolarDB for MySQL,很多人其实好奇到底PolarDB for MySQL数据库本身到底比MySQL强在哪里，SQL的执行有什么不同，怎么优化SQL，方案是什么。今天我们来一个真实的优化案例，来看看PolarDB在运行SQL的时候和MySQL有什么不同，今天我们来实战一下。

首先要说明的是PolarDB for MySQL,使用起来和MySQL没有太多的不同，而且PolarDB for MySQL商业在8.0上又两个版本，8.01 ，8.02。今天我们用的是MySQL的8.01，相对来说现在我更愿意推荐大家使用8.02因为哪个版本上的黑科技比8.01厉害多了，如果要比喻的话，应该是MySQL如果算凡人的话，PolarDB for MySQL 8.01相当于终结者 T800, PolarDB for MySQL8.02相当于终结者 T-X。（没看过终结者的可以自行查询之间的区别）。

今天我们还是拿终结者T800来运行SQL，来比对一下与传统的MySQL有什么不同。

1 并行能力如果你有PolarDB for MySQL 8.01的版本，可以和我一起做，我们先产生几个表，然后我们灌入数据。

-- 用户表（100 万）
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    region_id INT,
    created_at DATETIME
);

-- 地区表（100 条）
CREATE TABLE regions (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 订单表（100 万条）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    status VARCHAR(20),
    INDEX(user_id),
    INDEX(created_at)
);

-- 产品表（1 万条）
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50)
);

-- 插入地区
INSERT INTO regions (id, name)
SELECT seq, CONCAT('Region_', seq)
FROM (
  SELECT 1 AS seq 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 UNION ALL SELECT 10
) AS a;

-- 插入用户
DELIMITER //
CREATE PROCEDURE insert_users()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000000 DO
    INSERT INTO users (id, name, region_id, created_at)
    VALUES (i, CONCAT('User_', i), FLOOR(1 + RAND() * 10), NOW() - INTERVAL FLOOR(RAND() * 365) DAY);
    SET i = i + 1;
  END WHILE;
END;
//
DELIMITER ;
CALL insert_users();

-- 插入产品
INSERT INTO products (id, name, category)
SELECT seq, CONCAT('Product_', seq), CONCAT('Category_', FLOOR(RAND() * 10))
FROM (
  SELECT @row := @row + 1 AS seq FROM
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3,
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t4,
  (SELECT @row := 0) t0
  LIMIT 10000
) AS derived;

-- 插入订单
DELIMITER //
CREATE PROCEDURE insert_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000000 DO
    INSERT INTO orders (id, user_id, product_id, amount, created_at, status)
    VALUES (
      i,
      FLOOR(1 + RAND() * 1000000),
      FLOOR(1 + RAND() * 10000),
      ROUND(100 + (RAND() * 900), 2),
      NOW() - INTERVAL FLOOR(RAND() * 365) DAY,
      IF(RAND() > 0.5, 'paid', 'pending')
    );
    SET i = i + 1;
  END WHILE;
END;
//
DELIMITER ;
CALL insert_orders();

explain SELECT
    r.name AS region_name,
    p.category,
    COUNT(*) AS total_orders,
    SUM(o.amount) AS total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN regions r ON u.region_id = r.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= NOW() - INTERVAL 90 DAY
  AND o.status = 'paid'
GROUP BY r.name, p.category
ORDER BY total_amount DESC
LIMIT 20;

查询分析

从上图我们可以看出PolarDB for MySQL 8.01在执行SQL的时候和MySQL有明显的不同，这里使用了parallel scan，也就是并行扫描来对SQL进行加速，这也是PolarDB for Mysql 与 MySQL 在执行SQL时的一个最大的不同之一。

1 PolarDB for MySQL 会发现在查询数据量大的情况下，且你没有很好的SQL优化的情况下，自动开启并行。这个语句是没有优化的情况下，POALRDB发现了通过并行数据扫描，可以提高数据查询效率，且比使用现有索引的情况下更可能快速的将查询完成，他将直接使用并行来处理SQL。

这里并行度是可以调整的，通过参数，有足够的CPU可以开启2 4 6 8 16 或更多的并行度，来处理一个SQL，这个功能在MySQL中是不存在的。这也是一个问题在MYSQL中很早就出现的SQL执行的性能问题，在POLARDB FOR MYSQL中因为有并行，很多情况再严重了后才被发现，早期通过并行查询就解决了性能的问题。所以在看到并行的执行计划，就需要看是否是因为SQL没有优化导致的问题。

所以基于POLARDB的优化方案，会和基于MySQL的方案略有不同。

1 添加适合的索引，针对这个SQL，如果可以可以针对orders表的返回数据进行索引覆盖的方案。

CREATE INDEX idx_orders_cover ON orders ( status, created_at, user_id, product_id, amount );

2 针对访问的线程，临时调整临时表和排序的缓存大小，在实验中，临时开大后，语句的执行时间由原来的0.719S 变化为0.516S

SET tmp_table_size = 256 * 1024 * 1024;
SET max_heap_table_size = 256 * 1024 * 1024;
SET sort_buffer_size = 8 * 1024 * 1024;

临时调整缓存大小

3 如果CPU够多的情况下，且使用了PLS5的磁盘系统，那么可以开大并行，这里可以开到4个并行。

并行参数8.01

4 如果以上的方案还不能满足实际的需求，可以考虑开启POLARDB FOR MYSQL 的IMCI功能，通过IMCI功能直接对于聚合操作中的部分，变为列式处理。

执行语句

PolarDB for MySQL给出建议列式索引的优化建议

ALTER TABLE orders
  ADD COLUMNSTORE INDEX cs_idx_orders
  (created_at, status, user_id, product_id, amount);

关于POLARDB的IMCI方面属于另一个话题，但在POALRDB FOR MYSQL中是一个完美解决聚合计算的方案，添加一个列式的节点，添加上面的索引后，相关的查询就和飞了一样相当于,MYSQL+CLICKHOUSE的方案，但这里数据在行列中，我们可以认为是同步的，这点 MYSQL + Clickhouse又做不到。

所以这也是我们为什么把MySQL在云上都踢掉的原因，因为MySQL在云原生数据库的面前，都是短处，我要你何用。

总结：在云上使用POLARDB FOR MYSQL，针对SQL的优化的方案很多，且很灵活。在MySQL无法优化的SQL，或者需要改写的部分，放到POLARDB 上很多都不用改写，多种方案齐下手,大部分问题都能解决，所以MYSQL 我真的没法用你，在云上抱歉了MySQL。

置顶

云数据库厂商除了卷技术，下一个阶段还可以卷什么？

PostgreSQL 新版本就一定好--由培训现象让我做的实验

某数据库下的一手好棋！共享存储落子了！

删除数据“八扇屏” 之锦门英豪 --我去-BigData！

PostgreSQL “乱弹” 从索引性能到开发优化

写了3750万字的我，在2000字的OB白皮书上了一课--记《OceanBase 社区版在泛互场景的应用案例研究》

SQLSHIFT 是爱可生对OB的雪中送炭！

青春的记忆，MySQL 30年感谢有你，再见！（译）

老实人做的数据库产品，好像也不“老实” ！