CTE 的全称是 Common Table Expressions，有时候也缩写为 CTEs，在 SQL 标准中使用 WITH clause表达^[1]。在 ANSI standard 标准的 SQL:1999 中定义，参考：ISO/IEC 9075-2:1999^[7][8]。该特性支持在 MySQL 8.0 中正式被引入^[9]。

通常，如果不需要编写特别复杂的 SQL ，更为具体的，如果无需编写带有递归功能的 SQL 时，CTE 通常都不是必须的。所以，该语法功能，对于分析型的场景或者数据开发等场景更为常用。

这里概述 CTE 的两个常用功能：简化子查询结构、实现递归执行。其中，实现递归执行，是 CTE 独特的能力，是对 SQL 能力的一种补充。

简化子查询结构

CTE 功能提供了一种新的子查询或临时表的写法。可以把后续 SQL 中需要使用的临时表或子查询独立的放在WITH子句中，使得结构看起来更为清晰一些。

例如，如下两个写法在语义上是等价的：

WITH 
  t_mid as (
    SELECT * FROM t_1 JOIN t_2
  )
SELECT * FROM t_mid;

SELECT * 
FROM 
  (
    SELECT * FROM t_1 JOIN t_2
  ) t_mid;

递归执行

该功能是 CTE 的核心能力。例如，在表中查询好友的好友、查询主管的主管、递归数据生成等功能。

递归执行方式概述

在各个数据库的关于 CTE 介绍都会较为详细的描述，这里对该执行过程做一个简单的描述。

对于递归 CTE 对应的 SQL 总是有类似结构：

WITH RECURSIVE table_cte (col_01 ... ) AS (
  SELECT ...                 -- return initial row set
  UNION ALL
  SELECT ... FROM  table_cte -- return additional row sets
)

在该结构中：

• 第一个SELECT为数据初始化
• 第二个SELECT则为递归执行的部分，该部分在引用表table_cte时，总是使用该表的最后一条生成记录

斐波那契数列的生成

这是一个MySQL文档中的示例^[10]。

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
...
|   10 |    34 |         55 |
+------+-------+------------+

根据前面小结“递归执行方式概述”中所描述，在每次执行上述的第二个 SELECT，即SELECT n + 1, next_fib_n, fib_n + next_fib_n FROM fibonacci时，总是使用 CTE 表fibonacci最后生成的记录。所以，该SQL中引用的列n 、fib_n和next_fib_n均为表fibonacci最后生成的记录，即上一条记录。

所以，该 SQL 第一列返回的是一个自增的序列；第二列，即为斐波那契数列；第三列是用于计算的中间列。

广度优先还是深度优先

在 MySQL 的递归遍历中，并没有像 PostgreSQL 那样可以通过 SQL 关键字去控制。在文档中，也没有描述是广度优先还是深度优先。这里通过一些 SQL 变量来看看，到底是广度优先还是深度优先。

先说结论吧：MySQL 使用的是广度优先遍历。关注验证过程的，可以继续阅读这一段的示例和解读。

这里使用 MySQL 官方文档中另一个示例。该示例包含一个企业人员组织架构的关系：

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

我们可以使用如下的递归 CTE 语句进行组织架构遍历：

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

我们对这条 SQL 语句进行如下修改，以观察是广度优先还是深度优先：

WITH RECURSIVE employee_paths (id, name, path,seq) AS
(
  SELECT id, name, CAST(id AS CHAR(200)),@seq:=1
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id),@seq:=@seq+1
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY seq;

+------+---------+-----------------+------+
| id   | name    | path            | seq  |
+------+---------+-----------------+------+
|  333 | Yasmina | 333             |    1 |
|  198 | John    | 333,198         |    2 |
|  692 | Tarek   | 333,692         |    3 |
|   29 | Pedro   | 333,198,29      |    4 |
|  123 | Adil    | 333,692,123     |    5 |
|   72 | Pierre  | 333,198,29,72   |    6 |
| 4610 | Sarah   | 333,198,29,4610 |    7 |
+------+---------+-----------------+------+

该结果的最后一列 seq，反映了输出的先后顺序。可以看到，这里使用的广度优先遍历。

参考链接

• [1] 15.2.20 WITH (Common Table Expressions)@MySQL
• [2] 7.8. WITH Queries (Common Table Expressions)@PostgreSQL
• [3] WITH common_table_expression (Transact-SQL)@SQL Server
• [4] subquery_factoring_clause@Oracle Documentatioin
• [5] Hierarchical and recursive queries in SQL@Wikipedia
• [6] Common table expressions for ANSI SQL@AWS Documentation
• [7] https://www.iso.org/standard/26197.html
• [8] Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)
• [9] https://dev.mysql.com/blog-archive/whats-new-in-mysql-8-0-generally-available/
• [10] https://dev.mysql.com/doc/refman/8.4/en/with.html#common-table-expressions-recursive-fibonacci-series

在昨天进行的SQL编程大赛中，所有 MySQL 选手的成绩都没有进入八强的。个人也对这个问题比较感兴趣，经过初步分析，重要的原因在于 MySQL 实现中没有比较好的并行加速的能力。而在 MySQL 的衍生版本中，倒是有几个版本提供了并行执行的能力。包括了 PolarDB 的 Elastic Parallel Query^[2]、Amazon Aurora 的 Parallel query^[3] 。所以，也打算验证一下，如果加上这些并行能力，是否能够更快。

结果综述

PolarDB MySQL 运行了与 MySQL “几乎”（仅添加Hint开启并行）相同的SQL（参考）运行最快为：3.821 s。相比在同一个集群，不开启并行的时间是 6.647s，速度提升了 42.5% 。此外也测试了Aurora的相同的规格，几经调试依旧无法使用其并行能力。

在相同的SQL实现下，PolarDB MySQL 可能是所有 MySQL 版本中性能最好的。如果，感觉还有什么版本可能有更好的性能，欢迎留言。

数据与SQL

这次是尝试使用 MySQL 高性能的完成“第二次SQL编程大赛”的进阶挑战。完整的题目描述可以参考：赛题说明^[4]。这里实现的 PolarDB MySQL 版本 SQL 参考：gt.polardb.sql@GitHub^[1]（或参考本文结尾部分）。

PolarDB的规格选择

这里选择与试题类似的4c8g规格，详细参数如下：

主要参数：

• CPU架构：x86
• 产品版本：企业版
• 小版本号：8.0.2（与 MySQL 8.0.18 完全兼容）
• IMCI只读节点个数：0
• 初始只读节点个数：0
• 初始读写节点个数：1
• 节点规格：4 核 8GB（通用）
• 存储类型：PSL5

在 PolarDB 上并行执行

PolarDB 的并行执行可以使用 Hint 较为方便的开启：

SELECT
  /*+PARALLEL(8)*/
...

可以通过执行计划观察，实际是否使用了并行：

| -> Gather (merge sort; slice: 1; workers: 8)  (cost=2024861688.70 rows=1995124000) (actual time=1774.761..2159.495 rows=1000000 loops=1)
    -> Sort: <temporary>.p_id  (cost=1705198767.38 rows=249390500) (actual time=480.641,781.971,239.446..503.108,818.048,250.387 rows=125000,220305,60043 loops=1,1,1)
        -> Stream results (actual time=1.631,2.232,1.396..380.381,630.231,168.213 rows=125000,220305,60043 loops=1,1,1)
            -> Left hash join (t_01.a_s = p_01.a_s), (t_01.d_s = p_01.d_s), extra conditions: ((p_01.seq >= ((t_01.p_seat_to - t_01.seat_count) + 1)) and (p_01.seq <= t_01.p_seat_to))  (cost=25015432.30 rows=249390500) (actual time=1.621,2.220,1.387..200.048,332.885,86.841 rows=125000,220305,60043 loops=1,1,1)
                -> Parallel table scan on p_01, with parallel partitions: 8 (actual time=0.002,0.003,0.002..48.149,74.839,20.489 rows=125000,220305,60043 loops=1,1,1)
                    -> Materialize (shared access, partitions: 8, partition_keys: a_s,) (actual time=0.001,0.002,0.001..24.240,34.098,9.331 rows=125000,220305,60043 loops=1,1,1)
                        -> Gather (slice: 1; workers: 8)  (cost=1146187.18 rows=997560) (actual time=158.204..362.897 rows=1000000 loops=1)
                            -> Window aggregate  (cost=1090064.43 rows=124695) (actual time=160.066,167.128,157.433..296.715,314.531,278.945 rows=125000,149658,102922 loops=1,1,1)
                                -> Repartition (hash keys: passenger.departure_station, passenger.arrival_station; merge sort; slice: 2; workers: 8)  (cost=514229.38 rows=124695) (actual time=160.059,167.121,157.424..223.566,236.036,217.548 rows=125000,149658,102922 loops=1,1,1)
                                    -> Sort: passenger.departure_station, passenger.arrival_station  (cost=12554.66 rows=124695) (actual time=152.998,157.330,149.335..172.035,180.041,168.003 rows=125000,132932,111478 loops=1,1,1)
                                        -> Parallel table scan on passenger, with parallel partitions: 745 (actual time=0.052,0.058,0.046..57.559,65.907,54.511 rows=125000,132932,111478 loops=1,1,1)
                -> Hash
                    -> Table scan on t_01
                        -> Materialize (shared access) (actual time=0.002,0.003,0.001..0.744,0.935,0.666 rows=2000,2000,2000 loops=1,1,1)
                            -> Gather (slice: 1; workers: 8)  (cost=3416.33 rows=2000) (actual time=6.884..7.646 rows=2000 loops=1)
                                -> Window aggregate with buffering  (cost=3293.83 rows=250) (actual time=1.955,2.233,1.724..3.692,3.760,3.633 rows=250,292,196 loops=1,1,1)
                                    -> Repartition (hash keys: t_include_no_seat.d_s, t_include_no_seat.a_s; slice: 2; workers: 1)  (cost=3161.31 rows=250) (actual time=1.930,2.215,1.691..2.759,2.922,2.589 rows=250,292,196 loops=1,1,1)
                                        -> Sort: t_include_no_seat.d_s, t_include_no_seat.a_s, t_include_no_seat.if_no_seat, t_include_no_seat.t_id (actual time=1.383,1.383,1.383..2.070,2.070,2.070 rows=2000,2000,2000 loops=1,1,1)
                                            -> Table scan on t_include_no_seat (actual time=1.381,1.381,1.381..1.685,1.685,1.685 rows=2000,2000,2000 loops=1,1,1)
                                                -> Materialize with deduplication (shared access) (actual time=0.001,0.001,0.001..0.295,0.295,0.295 rows=2000,2000,2000 loops=1,1,1)
                                                    -> Table scan on <union temporary>  (cost=2.50 rows=0) (actual time=0.001..0.290 rows=2000 loops=1)
                                                        -> Union materialize with deduplication  (actual time=2.330..3.003 rows=2000 loops=1)
                                                            -> Table scan on train  (cost=101.25 rows=1000) (actual time=0.050..0.380 rows=1000 loops=1)
                                                            -> Table scan on train  (cost=101.25 rows=1000) (actual time=0.023..0.348 rows=1000 loops=1)
 |

这里的诸如Gather (merge sort; slice: 1; workers: 8)等内容，显示对应的部分会通过多线程并行执行。

执行时间统计

这里运行了该 SQL 三次的结果统计如下：

real	0m3.863s
user	0m0.379s
sys	0m0.100s

real	0m3.917s
user	0m0.414s
sys	0m0.123s

real	0m3.821s
user	0m0.422s
sys	0m0.128s

说明：这里，因为PolarDB是运行在云端，故仅需关注这里的 real 部分的时间。

不开启并行时 PolarDB 的性能

该组数据可用对比：

real	0m6.743s
user	0m0.394s
sys	0m0.120s

real	0m6.647s
user	0m0.393s
sys	0m0.120s

real	0m6.665s
user	0m0.407s
sys	0m0.125s

并行执行的一些状态参数

mysql> show global status like '%pq_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| PQ_fallback_one_worker              | 0     |
| PQ_local_workers_created            | 297   |
| PQ_migrant_workers_created          | 0     |
| PQ_net_exchange_fail_connect        | 0     |
| PQ_refused_over_computing_resource  | 0     |
| PQ_refused_over_max_queuing_time    | 0     |
| PQ_refused_over_total_workers       | 0     |
| PQ_remote_workers_created           | 0     |
| PQ_running_local_workers            | 0     |
| PQ_running_migrant_workers          | 0     |
| PQ_running_remote_workers           | 0     |
| PQ_sched_adative_resource_dec_count | 0     |
| PQ_sched_adative_resource_inc_count | 0     |
+-------------------------------------+-------+

使用 Aurora 的并行执行

这里也尝试使用 Aurora 的并行查询进行优化，但是并没有成功。Aurora 的并行执行并没有 Hint 可以控制，而是优化器根据需要选择使用。在本次测试中，在一个 4c32gb 的Aurora实例上，几经尝试，都未能实现并行。故未成功测试。在非并行时，Aurora的执行时间为 6.921s。

开启Aurora并行执行

要使用 Aurora 的并行执行能力，需要先创建最新版本的Aurora，在选择的参数组（parameter group）时，该参数组需要打开 aurora_parallel_query 参数。在实例创建完成后，可以通过如下命令查看并行查询是否打开：

mysql> show global variables like '%aurora_parallel_query%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| aurora_parallel_query | ON    |
+-----------------------+-------+

Aurora上的执行时间

这里记录相关SQL的执行时间如下：

real	0m6.921s
user	0m1.022s
sys	0m0.076s
[ec2-user@xterm-256color- delete_me]$ time mysql --local-infile=true -hpq-testing.cluster-cjzowaj9vqpd.ap-northeast-1.rds.amazonaws.com -ub_admin -p-f7HNhmp_frX game_ticket < aurora.sql > aurora.ret
mysql: [Warning] Using a password on the command line interface can be insecure.

real	0m6.955s
user	0m1.012s
sys	0m0.076s
[ec2-user@xterm-256color- delete_me]$ time mysql --local-infile=true -hpq-testing.cluster-cjzowaj9vqpd.ap-northeast-1.rds.amazonaws.com -ub_admin -p-f7HNhmp_frX game_ticket < aurora.sql > aurora.ret
mysql: [Warning] Using a password on the command line interface can be insecure.

real	0m7.154s
user	0m0.970s
sys	0m0.138s

最后

PolarDB MySQL 在并行执行开启的情况下性能提升了42.5%，最终执行时间为 3.821 s。有可能是所有 MySQL 兼容的发型版本中性能最快的。

gt.polardb.sql

-- explain analyze
WITH
  t_no_seat_virtual AS (
    select
      train_id as t_id,
      departure_station as d_s,
      arrival_station as a_s,
      seat_count,
      seat_count*0.1 as seat_count_no_seat
    from train
  ),
  t_include_no_seat AS (
    select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
    from t_no_seat_virtual
    union
    select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
    from t_no_seat_virtual
  )
SELECT
  /*+PARALLEL(8)*/
  p_01.p_id,         -- output 01
  p_01.d_s,          -- output 02
  p_01.a_s,          -- output 03
  t_01.t_id as t_id, -- output 04
  IF(
      if_no_seat,
      "" ,
      ceil((p_01.seq-t_01.p_seat_to + t_01.seat_count)/100)
  ) as t_carr_id, -- output 05

  CASE IF( !isnull(t_01.t_id) and if_no_seat,-1,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)%5))
    WHEN 1  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"A")
    WHEN 2  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"B")
    WHEN 3  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"C")
    WHEN 4  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"E")
    WHEN 0  THEN CONCAT( IF( (p_01.seq-t_01.p_seat_to + t_01.seat_count)%100 = 0, "20" ,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5)) ,"F")
    WHEN -1 THEN "无座"
    ELSE NULL
  END as seat_index   -- output 06
FROM
  (
    select
      /*+PARALLEL(8)*/
      ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq ,
      passenger_id as p_id,
      departure_station as d_s,
      arrival_station as a_s
    from
    passenger
  ) as p_01

  LEFT JOIN

  (
    select
      /*+PARALLEL(8)*/
      seat_count,
      sum(seat_count)
        over (
               PARTITION BY d_s,a_s
               ORDER BY     if_no_seat,t_id
             ) as p_seat_to ,
      t_id,
      d_s ,
      a_s ,
      if_no_seat
    from
    t_include_no_seat
  ) t_01

  ON
        p_01.seq >= p_seat_to-seat_count + 1
    and p_01.seq <= p_seat_to
    and p_01.d_s =  t_01.d_s
    and p_01.a_s =  t_01.a_s
ORDER BY p_01.p_id

参考链接

• [1] https://github.com/orczhou/sql-game-2nd/blob/main/gt.polardb.sql
• [2] https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/elastic-parallel-query/
• [3] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-parallel-query.html
• [4] https://www.ninedata.cloud/sql_train2024

随着企业规模增长，或者，企业原本就在监管非常严格的行业或环境，所面临合规要求也会逐步增多，密码的定期轮换通常也是众多要求中的一个基础项。MySQL 8.0 提供的“双密码”管理机制，可以让大规模数据库密码轮换变得更平滑，而无需任何的停机时间。

100台MySQL、1000台应用服务

考虑这样的场景，你是一个 DBA ，生产环境有 100 台 MySQL 实例，运行在这些数据库上约有 1000 台应用服务器。如果需要对，这些 MySQL 的应用账号密码统一做一次更新，如何实现不停机的迁移。

在 MySQL 之前（8.0.14版本之前）的单密码机制下，更换密码通常会遇到以下问题：无论是先更新数据库服务器上的密码，还是新调整应用服务器上的密码，这个先后顺序都会带来一定的服务不可用时间。通常，为了降低这个服务不可用时间，应用变更人员和 DBA 则需要非常密切的配合。

双密码机制（dual password）

从 MySQL 8.0.14 起，开始支持了双密码策略。在密码更换时，可以保留旧密码依旧可用：

ALTER USER 'appuser1'@'host1.example.com'
  IDENTIFIED BY 'password_b'
  RETAIN CURRENT PASSWORD;

在一段时间后，可以删除旧密码：

ALTER USER 'appuser1'@'host1.example.com'
  DISCARD OLD PASSWORD;

构建批量密码轮换流程

有了该功能，就可以使用上述的命令，更加平滑的实现大规模数据库场景下的密码更换。

1. 给变更数据库服务器上账号的密码，同时将原密码作为备用密码保留（使用“RETAIN CURRENT PASSWORD”）
2. 变更应用服务器上的访问数据库的密码
3. 检查并观察一段时间，删除数据库中的原密码（使用“DISCARD OLD PASSWORD ”）

这是一个简化的过程，实际的操作还需要考虑：

• 严格遵循企业内部的变更规范
• 灰度的进行发布，而不是一次性操作所有的实例/服务器
• 做好检查，需要确保诸如密码已经变更完成、应用服务器密码均已经更新
• 做好回滚方案，避免过程中操作失误导致的问题
• 在业务低峰时间进行操作

参考阅读

• [1] Dual Password Support
• [2] MySQL Dual Passwords – How To Manage Them Programmatically