预备知识

查询 sql 语句执行顺序

(8) SELECT (9)DISTINCT<Select_list>
(1) FROM <left_table> (3) <join_type>JOIN<right_table>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_list>
(11) LIMIT<limit_number>

具体说明详见:

SQL 的书写顺序和执行顺序https://zhuanlan.zhihu.com/p/77847158

优化目标

在前一段时间写了不下百段的逻辑库后,sql 优化将是我下一阶段的学习方向,它和业务表现息息相关。奇怪的是,它并没有在任何一个路线图上出现,尽管它其实非常重要。

首先明确什么是 sql 优化:

SQL 优化,就是指将一条 SQL 写的更加简洁,让 SQL 的执行速度更快,易读性与维护性更好。

其中,快自然是首要的优化目标,至于易读性嘛,只能说兼顾,毕竟大家都是在屎山上拉屎的那个。那么如何定义一段好的 sql 语句呢,标准如下:

减小查询的数据量、提升 SQL 的索引命中率

另外,从测试指标上,最基本的有执行时间,这在 navicat 上都有显示。遇到一条执行慢的 sql,应该先用 explain 命令得到查询计划,里面有很多有用的信息。

看懂 pgsql 上的执行计划

首先,最重要的,分清一次查询有没有走了索引: seq scan 是全表扫描,index scan / Bitmap Index Scan / Index Only Scan 是走了索引。

rows 是优化器预估的返回的行数,不是扫描的行数。rows 主要是为优化器选择合适的执行计划做参考的。

条件过滤:出现 Filter。

嵌套循环连接,一般来说,这个是导致 sql 变慢的重要原因之一。在联结(join)操作时就会出现。优化的基本原则是小表驱动大表。下面是一个包含 Nest Loop Join 的简单执行计划的示例:

EXPLAIN SELECT *
FROM table1
JOIN table2 ON table1.column_id = table2.column_id;

这里是一个示例执行计划的输出:

Nested Loop Join
  (cost=1000.00..2500.00 rows=100 width=32)
  -> Seq Scan on table1
        (cost=0.00..500.00 rows=50 width=16)
  -> Index Scan using index_column_id on table2
        (cost=500.00..1000.00 rows=50 width=16)
        Index Cond: (table1.column_id = table2.column_id)

在这个执行计划中,可以看到:

  • Nested Loop Join 表示使用了 Nest Loop Join。
  • Seq Scan on table1 表示对 table1 进行了顺序扫描,即全表扫描。
  • Index Scan using index_column_id on table2 表示对 table2 使用了索引扫描。
  • Index Cond: (table1.column_id = table2.column_id) 表示连接条件是基于列 column_id 的相等条件。

散列连接(Hash Join)是数据库查询中一种常见的连接算法,用于将两个表的数据连接起来。与 Nest Loop Join 不同,Hash Join 的连接过程不是基于嵌套循环,而是通过散列算法将连接条件的列的值映射到一个散列表中,然后在散列表中查找匹配的行。

以下是 Hash Join 的基本步骤:

  • 构建散列表:将连接条件的列的值通过散列算法映射到散列表中。

  • 将第一个表的每一行添加到散列表中。

  • 遍历第二个表的每一行,通过散列算法找到散列表中匹配的行。
    相比于 Nest Loop Join,Hash Join 的优势主要体现在以下几个方面:

  • 性能: 在某些情况下,Hash Join 的性能可能比 Nest Loop Join 更好。特别是在连接大型表时,Hash Join 的性能通常更高效,因为它可以利用散列表的快速查找特性。

  • 适用于等值连接: Hash Join 通常用于等值连接(即连接条件是相等关系),而 Nest Loop Join 更适合处理其他类型的连接条件。如果连接条件是等值关系,Hash Join 可能会更为高效。

  • 适用于大型表: 当连接的表很大时,Hash Join 可以更好地利用内存,因为它在内存中构建散列表。这有助于减少 I/O 操作,提高查询性能。

然而,Hash Join 也有一些限制,例如对内存的需求较高,如果内存不足可能导致性能下降。因此,在选择连接算法时,需要根据具体的查询和表结构来进行优化。数据库优化器通常会根据统计信息和查询条件选择合适的连接策略。