MySQL和PostgreSQL的Explain的使用方法

3 年 ago
清, 扬
1 minute

首先

我是HRBrain株式会社的工程师。我已经做工程师工作近6年了。在这期间，我有5年以上的MySQL工作经验。目前，我所在的HRBrain公司主要使用PostgreSQL作为主要数据库。在使用PostgreSQL期间，当执行Explain功能时，我发现其与MySQL显示内容完全不同。因此，我将分别解释MySQL和PostgreSQL的Explain所显示的执行计划。

解释是指通过描述或说明，使某事或某个概念变得更清晰或更容易理解的行为。

显示SQL查询执行计划的功能。
可以查看查询规划器执行查询的方式，
可用于优化查询性能等操作。

只需在执行查询的开头加上Explain即可执行。

提前准备

关于如何解释（Explain）查询。

我在PostgreSQL和MySQL中分别创建了以下表格。

    • users: ユーザ情報

 

    • department: 部署情報

 

    orders: 注文情報

实体关系图

ER.png

我们将使用以下查询来查看MySQL和PostgreSQL的Explain详细信息。

EXPLAIN 
SELECT 
  users.name, 
  department.name AS department_name, 
  COUNT(orders.id) AS order_count 
FROM 
  users 
  JOIN department ON users.department_id = department.id 
  LEFT JOIN orders ON users.id = orders.user_id 
WHERE 
  users.age > 25 
GROUP BY 
  users.id, 
  department.id;

如何查看执行计划

MySQL查询的结果

スクリーンショット 2023-07-16 17.32.19.png

MySQL的Explain功能将执行计划以表格形式显示出来。

项目说明

スクリーンショット 2023-07-20 16.30.07.png

解釈基於結果的查詢執行計劃視角。

    1. users表

由于类型为ALL，users表将进行全表扫描（读取所有行）
possible_keys显示有一个名为department_id的索引，但是key中没有显示任何东西，所以索引没有被使用
rows为10，表示users表中总共有10行数据
筛选条件被应用到的行的比例为33.3%

department表

由于类型为eq_ref，使用了索引进行等值连接（*1）
sample.users.department_id和department.id作为连接条件
rows为1说明，当连接sample.users.id和orders.user_id时，结果生成了1行
筛选条件被应用到的行的比例为100%

orders表

由于类型为ref，使用了索引进行范围连接
sample.users.id和orders.user_id作为连接条件
当连接sample.users.id和orders.user_id时，orders生成了一行
查询执行处理了5行
筛选条件被应用到的行的比例为100%

需要从表格中读取执行顺序。

PostgreSQL的结果

スクリーンショット 2023-07-17 0.57.15.png

PostgreSQL的解释与MySQL的Explain显示完全不同。
PostgreSQL的解释以树状形式显示执行计划，并且每个节点都与父节点和子节点相关联。

我們將查看顯示的內容項目。

项目说明

スクリーンショット 2023-07-20 16.34.21.png

解釋基於結果的查詢執行計劃的觀點。

阅读PostgreSQL执行计划的规则有以下两个。

    1. 按顺序遍历子节点，从最遥远的子孙节点开始执行，然后向上追溯至父节点。

 

    如果存在兄弟元素(※2)，则按照先前的行进行依次遍历节点。

为了更容易理解，我们将执行计划绘制成图表。

postgreSQL_2.png
    1. 扫描orders表的数据

 

    1. 扫描users表的数据

 

    1. 创建用于与orders表进行连接的散列表

 

    1. 扫描department表

 

    1. 将orders表和users表进行散列连接

 

    1. 创建用于将department表和users表连接的散列表

 

    1. 将department表和users表进行散列连接

 

    按user.id和department.id进行分组

这里明确显示了执行顺序和查询成本等。

总结

MySQL 是一种开源的关系型数据库管理系统。

    • MySQLのExplainの表示は12の項目で実行計画が表示される。

 

    表示された12項目の内容を読み取り実行順序を読み取る必要がある。

PostgreSQL可以被简单地理解为一种数据库管理系统。

    • QUERY PLANというテキスト形式で出力される。

 

    • 項目は実行したクエリにより内容が変わる。

 

    • 実行計画の予測cost等を出してくれる。

 

    • 実行順序はツリー形式で明示的に表示され、親ノードと子ノードの関係が明確に示される。

 

    クエリのコストや結合順序などの詳細情報が提供される。

通过使用 Explain 功能来查看执行计划的内容，重新考虑索引的使用方法和查询条件，以确认是否可以创建更高性能的查询。

个人观点

PostgreSQL Explain的显示方式与MySQL完全不同，但一旦理解了含义，查询的执行计划就会明确地显示出来，让人非常容易理解。

填补

※1：等值搜索指的是在连接条件中，两个表的列之间通过相等的值进行连接。

※2：兄弟元素指的是在解释中具有相同父节点并在同一层级显示的节点。在示例中，Hash Right Join是父节点，Seq Scan on orders和Hash是兄弟关系。

可以参考：

    • MySQL 8.0 リファレンスマニュアル

 

    • USE THE INDEX, LUKE!

 

    • 【PostgreSQL】Explainの見方（analyze , cost , scan , sort）についてのまとめ

 

    【PostgreSQL】初心者でも読める実行計画の基礎知識
bannerAds