3 篇博文 含有标签「sql优化」

例 1 筛选字段没加索引​

在处理一个长达 500 行的大型 sql 查询文件的过程中，我发现了响应非常慢。这段 sql 是为后台报表服务的，仅仅查询一天的数据就要花费接近 20 秒的时间，那么如果是一个月的话肯定超时了。毫无疑问，这个 sql 需要优化。

首先查看执行计划。如果是执行 explain 命令来获得执行计划，得到的 cost 并不能直接看出速度的快慢。因此，需要用explain (analyze)。由于查询是由好几个 CTE 子表组成的，所以执行计划也是分别给出了几个 CTE 子表的花费时间。虽然很长，但是，细心的查看后，果然发现了异常：

可以看到，在查询 money_bag_balance 这张子表时，actutal time(实际执行时间)达到了 2295 毫秒，他就是元凶。接下来看，发现是在 trade_list_internal 这张流水表上进行全表扫描，难怪这么慢！于是，我定位到 money_bag_balance 的 sql 语句,这张表是由 income 和 balance 合并而来，以 income 为例：

money_bag_income as (
select
 COALESCE(SUM(CASE WHEN method = 'wx_pay' THEN real_fee ELSE 0 END),0) AS wechat_income,
 COALESCE(SUM(CASE WHEN method = 'ali_pay' THEN real_fee ELSE 0 END),0) AS alipay_income,
 COALESCE(SUM(CASE WHEN method = 'epay' THEN real_fee ELSE 0 END),0) AS epay_income,
 COALESCE(SUM(CASE WHEN method = 'wallet_pay' THEN real_fee ELSE 0 END),0) AS money_bag_income_real,
 COALESCE(SUM(CASE WHEN method = 'virtual_pay' THEN real_fee ELSE 0 END),0) AS money_bag_income_virtual,
 COALESCE(SUM(CASE WHEN method = 'union_pay' THEN real_fee ELSE 0 END),0) AS union_pay_income,
 COALESCE(SUM(CASE WHEN method = 'ccb_pay' THEN real_fee ELSE 0 END),0) AS ccb_pay_income,
 COALESCE(SUM(CASE WHEN method = 'ccb_pay_dc' THEN real_fee ELSE 0 END),0) AS ccb_pay_dc_income
 from trade_list_internal
 where trade_type='charge'
   and status='success'
-- 	 and commit_time between '2023-07-17' and '2023-07-18'
	and  (  (''='2024-02-01' and ''='2024-02-02') or
	(''<>'2024-02-01' and ''='2024-02-02' and commit_time>='2024-02-01' ) or
	(''='2024-02-01' and ''<>'2024-02-02' and commit_time<'2024-02-02' ) or
	(''<>'2024-02-01' and ''<>'2024-02-02' and commit_time >= '2024-02-01' and commit_time<'2024-02-02' ) )
),

commit_time 这个字段没加索引，所以走了全表查询。经过沟通，换了一个正确的且带索引的字段，查询在 0.2s 内完成！一下子提升了四十多倍！

例 2 在筛选字段上进行计算导致索引失效​

和例 1 一样，本例也是一个营收分析的脚本。一样从执行计划开始。观察后发现，多次出现了一个过滤条件：

> Parallel Seq Scan on statistics_by_area_block_street a  (cost=0.00..66759.73 rows=911 width=282) (actual time=825.208..825.444 rows=120 loops=5)

Filter: (((data_owner_id)::text = ANY ('{66ebc3d0-5870-11ea-bbb2-d5c9d3c42033,8d51f590-21c7-11ec-8c04-8f0bedcb705d,95885640-cd3e-11ed-856c-93f3797cd92f,a65816d0-1489-11ee-a19d-0765044c45ed}'::text[])) AND (to_char((sta_date)::timestamp with time zone, 'yyyy-MM-dd'::text) >= '2024-02-03'::text) AND (to_char((sta_date)::timestamp with time zone, 'yyyy-MM-dd'::text) <= '2024-02-03'::text))'

Rows Removed by Filter: 172940

对应的 sql 片段：

where (  (''='2024-02-03' and ''='2024-02-03') or
(''<>'2024-02-03' and ''='2024-02-03' and to_char(sta_date,'yyyy-MM-dd')>='2024-02-03' ) or
(''='2024-02-03' and ''<>'2024-02-03' and to_char(sta_date,'yyyy-MM-dd')<='2024-02-03' ) or
(''<>'2024-02-03' and ''<>'2024-02-03' and to_char(sta_date,'yyyy-MM-dd') between '2024-02-03' and '2024-02-03' )
)
and a.data_owner_id in ('66ebc3d0-5870-11ea-bbb2-d5c9d3c42033','8d51f590-21c7-11ec-8c04-8f0bedcb705d','95885640-cd3e-11ed-856c-93f3797cd92f','a65816d0-1489-11ee-a19d-0765044c45ed')

data_owner_id 和 sta_date 两个字段，前者没有索引，后者加了索引。于是我先把 data_owner_id 加了索引，再运行，速度没有提升。看到后面，发现是 to_char 这个函数用在 sta_date 上导致索引失效。修改成 sta_date<'2024-02-03'后，速度从原来的 5.2s 提升到 0.6s。

例 3 count distinct 问题​

原 sql 如下：

SELECT
	count(CASE WHEN type = 'user' THEN 1 END) "total_count",
	count(CASE WHEN "type" = 'user' AND created_at > CURRENT_DATE THEN 1 END) "today_count",
	count(CASE WHEN "type" = 'user' AND created_at > CURRENT_DATE - 7 THEN 1 END) "7days_count",
	count(CASE WHEN "type" = 'user' AND created_at > CURRENT_DATE - 30 THEN 1 END) "30days_count",
	(SELECT count(DISTINCT user_id) FROM bind_plate_no WHERE deleted = FALSE) bind_total_count -- 执行慢的部分
FROM base_user

base_user 表的数据量是百万级。从执行计划看出，耗时部分出现在 bind_plate_no 表的 aggregate 上。对应的 sql 是一个典型的 count distinct 问题。虽然 user_id 加了索引，但是在 count 内部 distinct 需要大量的额外计算，因此很慢。试过去掉 distinct 后，这句 sql 就变得很快了。但是，不能破坏原有业务逻辑啊。解决方案是先去重、再汇总。

select count(*) from (select distinct user_id FROM bind_plate_no WHERE deleted = FALSE) tmp

新的 sql 运行速度从 9 秒，提升到 2.5 秒。

另外，我也试了另一种解决方案，即 count group by，本质也是先去重、再汇总。结果来看，比上述方案慢了 1 秒。

参考资料：

SQL 优化（二） 快速计算 Distinct Count http://www.jasongj.com/2015/03/15/count_distinct/

子查询​

子查询是指嵌套在其他查询里的查询。主要有两个使用位置，一个是利用子查询进行过滤，另一个是作为计算字段使用子查询。

利用子查询进行过滤​

SELECT cust_id 
FROM Orders 
WHERE order_num IN (SELECT order_num 
 FROM OrderItems 
 WHERE prod_id = 'RGAN01');

顺序是先执行子查询，由内向外。实际上，这种用法的查询效率低，尽量用联结查询代替。

作为计算字段使用子查询​

SELECT cust_name, 
 cust_state, 
 (SELECT COUNT(*) 
 FROM Orders 
 WHERE Orders.cust_id = Customers.cust_id) AS orders 
FROM Customers 
ORDER BY cust_name;

-- 用left join优化
select a.cust_name, a.cust_state, b.count
from Customers a left join (select cust_id, count(cust_id) from Orders group by cust_id) b on a.cust_id=b.cust_id
order by cust_name

上述语句的执行顺序是，先在外层找到符合条件的数据，然后子查询对检索出的每条数据执行一次。

最后还有一种在from后面使用的子查询，作为主查询的数据源：

SELECT column1, column2
FROM (SELECT column3 FROM table1 WHERE condition) AS subquery;

这种情况下，用视图来替代应该是更聪明的选择，因为可以降低sql语句的复杂性。

扩展资料：

SQL子查询优化，看这一篇就够了 https://developer.aliyun.com/article/711481

SQL优化：慎用标量子查询，改用left join提升查询效率 https://www.cnblogs.com/goloving/p/15193637.html

表联结​

想起来有一次回答full join是什么时，以为就是笛卡尔积的尴尬场景。

首先要了解联结的本质：多张表根据条件列出匹配的行，如果没有条件，就会返回笛卡尔积（也就是返回M*N行）。内联结（inner join）就是只返回表之间符合条件的行。而外连接还包括了没有关联行的行。

预备知识​

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