SQL中WITH RECURSIVE的用法
文章目录
定义
WITH RECURSIVE 是 SQL 中的一种高级查询结构,用于执行递归查询。递归查询是一种特殊的查询方式,它能够通过反复应用一个规则或算法,逐步构建出一个结果集,常用于解决层次化或树状数据结构的遍历问题。
WITH RECURSIVE 结构通常包含以下几个关键部分:
1. CTE(Common Table Expression,公用表表达式):
- WITH 关键字引入一个或多个 CTE。
- ECURSIVE 关键字表明至少有一个 CTE 是递归的。
- CTE 名称(cte_name)用于标识递归查询的临时结果集。
- 列名列表(column_list)定义了 CTE 结果集中包含的列及其名称。
- 初始查询(initial_query_result)提供递归过程的起点,即第一次迭代时使用的数据。
- 递归部分(递归子查询)定义了如何将前一次迭代的结果作为输入,计算出下一次迭代的数据。
2.递归查询的结构
- 递归查询通常由两部分构成:初始化查询(非递归部分)和递归子查询(递归部分)。
- 初始化查询:定义递归开始时的基础数据集,通常是与递归逻辑相关的最顶层数据或边界条件。
- 递归子查询:定义如何根据前一次迭代的结果生成下一次迭代的数据。递归子查询通常包含对自身 CTE 名称的引用,以递归地应用相同的操作。
3.连接操作符:
- 递归查询的初始化查询和递归子查询通常通过 UNION 或 UNION ALL 连接起来,形成一个完整的递归查询表达式。
- UNION 会去除结果集中的重复行,而 UNION ALL 不会去除重复,根据实际需求选择合适的连接操作符。
4.终止条件
- 递归查询必须有一个明确的终止条件,否则会无限循环下去。终止条件通常隐含在递归子查询的 WHERE 子句或其他逻辑中,当满足特定条件时,不再产生新的结果。
示例
假设有一个员工表 employees,其中包含 id(员工ID)、name(员工姓名)、manager_id(上级经理ID),结构如下:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
现在我们想查询出一个员工及其所有下属的完整层级关系。可以使用 WITH RECURSIVE 构建如下查询:
WITH RECURSIVE employee_hierarchy AS (
-- 初始化查询:选取根节点(顶级经理,没有上级经理)
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归子查询:根据上一层级结果,查找下一级员工
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
在这个例子中:
- employee_hierarchy 是 CTE 的名称。
- 列名列表为 id, name, manager_id, level。
- 初始化查询选择了所有没有上级经理(manager_id IS NULL)的员工作为递归的起点。
- 递归子查询通过 JOIN 语句将 employee_hierarchy 与 employees 表连接起来,根据 manager_id 匹配关系找到下一层级的员工。同时,level 列每次递归时增加 1,表示员工在层级结构中的深度。
- 最后,通过 SELECT * FROM employee_hierarchy 查询出最终的递归结果集。
总结起来,WITH RECURSIVE 的用法主要包括以下几个步骤:
- 定义 CTE,指定 RECURSIVE 关键字。
- 编写初始化查询和递归子查询,通过 UNION 或 UNION ALL 连接。
- 确保递归子查询中包含终止条件,以避免无限递归。
- 在主查询中引用 CTE 名称,获取递归查询的结果。
这种结构适用于解决各种层次化数据的遍历问题,如组织架构、目录结构、路径搜索、数列生成等。通过递归查询,可以简化复杂查询逻辑,提高代码可读性,并且在某些情况下比使用循环或其他编程语言结构更高效。
EXPLAIN
1.执行 EXPLAIN 命令:
将 WITH RECURSIVE 查询语句包裹在 EXPLAIN 关键字之后,运行该命令以获取查询执行计划。由于 WITH RECURSIVE 查询包含两个部分(初始化查询和递归子查询),您需要分别分析这两个部分。
EXPLAIN WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
然而,MySQL 直接对包含 WITH 子句的查询使用 EXPLAIN 会有语法错误。为了分析递归查询,可以将递归查询转换为临时表(或者视图),然后对临时表进行 EXPLAIN 分析:
CREATE TEMPORARY TABLE temp_employee_hierarchy AS
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
EXPLAIN SELECT * FROM temp_employee_hierarchy;
请注意,这种方法仅能分析递归查询的最终结果集,无法直接分析递归过程中的各个步骤。若要详细了解递归过程中的索引使用情况,可能需要手动分解递归查询并分别进行 EXPLAIN 分析。
2.解读 EXPLAIN 输出:
EXPLAIN 命令的输出会显示查询的执行计划,重点关注以下几个关键列:
- type:表示访问类型,从最好到最差的类型为 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。理想情况下,应至少达到 range 级别,最好达到 ref 级别。如果为 ALL,表示全表扫描,没有使用索引。
- key:显示实际使用的键或索引。如果为 NULL,表示未使用任何索引,需要优化。
- extra:包含额外的信息,如 Using index 表示覆盖索引(无需回表),Using where 表示在索引检索后还需要过滤,Using filesort 和 Using temporary 表示需要额外排序或创建临时表,这些都是不太友好的值
对于上述示例,主要关注 employees 表的查询是否使用了索引,特别是 manager_id 字段上的索引。如果表上有适当的索引(如 manager_id 上的单列索引或复合索引),期望在 EXPLAIN 输出中看到 type 列显示为 ref 或更好的访问类型,并且 key 列显示实际使用的索引名。
3.优化建议:
如果发现查询未有效使用索引,可以尝试以下优化措施:
-
检查索引:确认 employees 表上是否存在针对 manager_id 字段的索引。如果没有,应创建相应的索引。
-
调整查询:确保查询条件和连接条件能够充分利用现有索引。例如,确保 WHERE 子句和 JOIN 条件中涉及的列都已建立索引。
-
分析数据分布:如果表数据分布不均匀,即使存在索引,也可能导致查询优化器选择全表扫描。在这种情况下,可能需要重新评估索引设计或调整查询语句。
-
调整查询:确保查询条件和连接条件能够充分利用现有索引。例如,确保 WHERE 子句和 JOIN 条件中涉及的列都已建立索引。
-
分析数据分布:如果表数据分布不均匀,即使存在索引,也可能导致查询优化器选择全表扫描。在这种情况下,可能需要重新评估索引设计或调整查询语句。
综上所述,分析 WITH RECURSIVE 查询是否走索引,需通过 EXPLAIN 命令获取查询执行计划,并关注 type、key 和 extra 列。根据输出结果判断索引使用情况,并据此进行相应的优化。由于直接对递归查询使用 EXPLAIN 有局限性,可能需要采用变通方法(如创建临时表)或手动分解查询进行分析。