SQL调优之遇到json数组性能提升了12倍

1、背景概述

在做交易分润统计的时候存在 omg_trans_inf【交易表】和 omg_trans_fee【手续表】，而手续费表中的字段 fee_list 是一个 jsonb 格式的字段，fee_list 存储代理商层级的分润数据信息，数据格式如下：

[
  {"agentNo": "83000053", "agentLevel": 5, "agentProfit": 1}, 
  {"agentNo": "83000057", "agentLevel": 4, "agentProfit": 3}, 
  {"agentNo": "83000058", "agentLevel": 3, "agentProfit": 5}, 
  {"agentNo": "83000059", "agentLevel": 2, "agentProfit": 7}
]

手续费表存储了交易表的主键id，可以理解为 omg_trans_inf【交易表】和 omg_trans_fee【手续表】通过手续费表的交易主键id进行关联，也就是说同一条交易记录，不同角色的代理商登录进来，看到的都是自身所得的分润金额信息。

2、解决问题(引入json_array_elements函数)

通过json_array_elements函数可以将 fee_list 进行展开, 即就是将 [{}] 转化为 {}，然后再通过多重子查询得到一个虚拟表结构中的数据, 然后再和交易表的主键id作关联查询，分别取对应的分润余额即可（注意：此处的操作是引发BUG的源头！！！）

-- jsonarray展开列表 获取到对应的分润
SELECT
sum_inf.*
FROM
(SELECT 
id,
json_array_elements (agent_fee_list::json) ->> 'agentNo' AS agent_no,
json_array_elements (agent_fee_list::json) ->> 'agentLevel' AS agent_level,
json_array_elements (agent_fee_list :: json) ->> 'agentProfit' AS agent_profit
FROM
omg_trans_fee) AS sum_inf
WHERE sum_inf.agent_no = '83000053'

以上这种方式解决了分润余额的统计问题，但是引发了一个新的问题，被测试同学在聊天群里艾特出来，分润统计接口有时候耗时都在2-10s左右才会返回结果，当时我也就纳闷了，因为还是在测试环境，总交易数据量才813条，为什么会出现慢SQL查询问题呢？这个问题也是深深的吸引了我。

2.1 慢SQL问题排除

• 1、Postgresql中采用 EXPLAIN 或 EXPLAIN ANALYZE 对SQL进行分析
• 2、因为我的那个SQL跟时间有关，我的第一反应是不是因为没有给时间增加索引（为create_time创建索引，cost没有任何改变，说明不是索引的问题）。
• 3、我把LEFT JOIN 关联分润金额那一部分屏蔽掉，此时SQL的性能搜的一下就快了，因此我把重心放到了 分润金额统计那块。

2.2 慢SQL分析

SELECT
-- 	trans.ID,
	  COALESCE ( SUM ( trans.trans_amount ), 0 ) AS total_trans_amt,
		COALESCE ( SUM (tsi.agent_profit::numeric), 0) AS total_profit_amt 
	FROM
		omg_trans_info AS trans
		LEFT JOIN omg_trans_fee AS fee ON trans.ID = fee.trans_id 
		
		LEFT JOIN(
			SELECT * FROM (SELECT
		trans_id,
		jsonb_array_elements ( agent_fee_list ) ->> 'agentProfit' AS agent_profit,
		jsonb_array_elements ( agent_fee_list ) ->> 'agentNo' AS agent_no 
	FROM
		omg_trans_fee
		WHERE delete_status = 0) AS ti
			WHERE ti.agent_no = '83000072'
		) AS tsi ON trans.id  = tsi.trans_id
	WHERE
		trans.delete_status = 0
		AND trans.trans_status = 2 
		AND trans.create_time >= '2024-06-01 00:00:00' 
		AND trans.create_time <= '2024-06-30 23:59:59'  
-- 		ORDER BY trans.ID DESC
	LIMIT 20;

经过EXPLAIN ANALYZE 分析可得 ，cost（SQL耗时）存在1530.97ms，并且进行 Right Join时 Memory usage: 10KB. 说明最上面说的那种方式是不可取的(先把jsonb array展开，然后逐个字段值取出再映射一张虚拟表，会存在好多的子查询)，

2.3 解决慢SQL

SELECT
-- 	trans.ID,
	  COALESCE ( SUM ( trans.trans_amount ), 0 ) AS total_trans_amt,
		COALESCE ( SUM ((jsonb_array_element ( fee.agent_fee_list, 0 ) ->> 'agentProfit') ::numeric), 0) AS total_profit_amt 
	FROM
		omg_trans_info AS trans
		LEFT JOIN omg_trans_fee AS fee ON trans.ID = fee.trans_id 
	WHERE
		trans.delete_status = 0
		AND trans.trans_status = 2 
		AND trans.create_time >= '2024-06-01 00:00:00' 
		AND trans.create_time <= '2024-06-30 23:59:59'  
-- 		ORDER BY trans.ID DESC
	LIMIT 20;

经过EXPLAIN ANALYZE 分析可得 ，cost（SQL耗时）存在129.03ms，与优化前相比 查询速度提升了 11倍。

3、结论

1、优化前的SQL耗时大，cost为1500多（这种还是在数据量小的情况下）.
2、优化前的SQL执行了好多子查询，导致SQL执行变慢。
3、优化后的SQL耗时小，cost为96，相比之前优化了近12倍，性能得到了进一步提升.
4、优化后的SQL很简洁，并且还执行了时间索引值.

4、json_array_elements函数补充

4.1 对如上函数进行验证

/**
83000072: 0.53
83000071: 0.11
83000070: 0.05
*/
SELECT jsonb_array_element(agent_fee_list,-3) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 0
83000072: 0.53
*/
SELECT jsonb_array_element(agent_fee_list,0) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 1
83000072: 0.11
*/
SELECT jsonb_array_element(agent_fee_list,1) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 2
83000072: 0.05
*/
SELECT jsonb_array_element(agent_fee_list,2) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 3 值为null，因为总共数组size为3
*/
SELECT jsonb_array_element(agent_fee_list,2) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 -1 值为0.05
*/
SELECT jsonb_array_element(agent_fee_list,-1) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 -2 值为0.11
*/
SELECT jsonb_array_element(agent_fee_list,-2) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 -3 值为0.53
*/
SELECT jsonb_array_element(agent_fee_list,-3) FROM omg_trans_fee WHERE id = 334;
/**
下标索引值为 -4 值为null ，因为总共数组size为3
*/
SELECT jsonb_array_element(agent_fee_list,-1) FROM omg_trans_fee WHERE id = 334;

小结：jsonb_array_element(jsonb,下标值)，下标从0开始，表示获取数组第一个元素，1表示获取数组中第二个元素，2表示获取数组中第三个元素等依次类推。相反，-1表示获取数组中最后一个元素，-2表示获取数组中倒数第二个，-3表示获取数组中倒数第三个元素，等等以此类推。（注意：当数组下标索引超过数组本身的大小时，得到的结果值为空!）

综上所述，大家可结合自己的场景进行分析，可能你看到最后会发现没什么感觉，但是本篇文章主要是在SQL调优方面关于解决思路的简单分享，希望可以帮助到您，我们常常在面试说的SQL调优、SQL调优，而这次经历让我对SQL调优有了进一步认识了，也为以后在SQL调优方面积攒了宝贵的经验。