一个SQL优化技巧
我们系统中存在大量类似如下查询：
SELECT p.*, di.name AS district, q.name AS scene_name, (
SELECT user_name
FROM fw_user
WHERE fw_user_id = u.parent_id
) AS parent_phone, split_part(p.location, ',', 1) AS province
, split_part(p.location, ',', 2) AS city
FROM jd_patient p
LEFT JOIN fw_user u ON u.fw_user_id = p.fw_user_id
LEFT JOIN jd_district di ON p.jd_district_id = di.jd_district_id
LEFT JOIN jd_qrcode q ON p.jd_qrcode_id = q.jd_qrcode_id
WHERE 1 = 1
AND p.fw_user_id IS NOT NULL
ORDER BY p.create_date DESC, p.location
LIMIT 10 OFFSET 0;
这些查询在数据量小的时候，不会出现效率瓶颈，但是数据量一旦增大，就会显著地降低查询速度。
使用explain查看其执行速度：
QUERY PLAN
------------------------------------------------------------------------------------------
-----------------
Limit (cost=17975306.40..17975306.43 rows=10 width=2008)
-> Sort (cost=17975306.40..17980273.18 rows=1986709 width=2008)
Sort Key: p.create_date, p.location
-> Hash Left Join (cost=570.27..17932374.34 rows=1986709 width=2008)
Hash Cond: (p.jd_qrcode_id = q.jd_qrcode_id)
-> Hash Left Join (cost=2.26..1120474.10 rows=1986709 width=1994)
Hash Cond: (p.jd_district_id = di.jd_district_id)
-> Nested Loop Left Join (cost=0.42..1093343.76 rows=1986709 width=
1856)
-> Seq Scan on jd_patient p (cost=0.00..107821.94 rows=198670
9 width=1840)
Filter: (fw_user_id IS NOT NULL)
-> Index Scan using pk_fw_user on fw_user u (cost=0.42..0.49
rows=1 width=32)
Index Cond: (fw_user_id = p.fw_user_id)
-> Hash (cost=1.37..1.37 rows=37 width=154)
-> Seq Scan on jd_district di (cost=0.00..1.37 rows=37 width=
154)
-> Hash (cost=427.45..427.45 rows=11245 width=30)
-> Seq Scan on jd_qrcode q (cost=0.00..427.45 rows=11245 width=30)
SubPlan 1
-> Index Scan using pk_fw_user on fw_user (cost=0.42..8.44 rows=1 width
=12)
Index Cond: (fw_user_id = u.parent_id)
(19 rows)
优化方法
上面的语句查询了大量数据(包括数据条数、以及p.* )，然后使用临时表order by，但最终又只返回了10条数据。
在结果集大的时候，这个临时表会非常大，内存放不下，于是全部拷贝到磁盘，导致IO飙升，效率急剧下降。
优化的基本策略是尽早缩小结果范围，思路是拆分sql，将排序操作和查询所有数据的操作分开。
具体来看，考虑到实际上需要的数据只有10条，我们可以先找到这10条数据的jd_patient_id：
SELECT p.jd_patient_id
FROM jd_patient p
LEFT JOIN fw_user u ON u.fw_user_id = p.fw_user_id
LEFT JOIN jd_district di ON p.jd_district_id = di.jd_district_id
LEFT JOIN jd_qrcode q ON p.jd_qrcode_id = q.jd_qrcode_id
WHERE 1 = 1
AND p.fw_user_id IS NOT NULL
ORDER BY p.create_date DESC, p.location
LIMIT 10 OFFSET 0;
然后在这10条数据中联合查询各字段的值：
SELECT p.name, di.name AS district, q.name AS scene_name, (
SELECT user_name
FROM fw_user
WHERE fw_user_id = u.parent_id
) AS parent_phone, split_part(p.location, ',', 1) AS province
, split_part(p.location, ',', 2) AS city
FROM jd_patient p
LEFT JOIN fw_user u ON u.fw_user_id = p.fw_user_id
LEFT JOIN jd_district di ON p.jd_district_id = di.jd_district_id
LEFT JOIN jd_qrcode q ON p.jd_qrcode_id = q.jd_qrcode_id
WHERE p.jd_patient_id IN ( xxxxx )
)
ORDER BY p.create_date DESC, p.location
于是优化后的sql如下：
SELECT p.name, di.name AS district, q.name AS scene_name, (
SELECT user_name
FROM fw_user
WHERE fw_user_id = u.parent_id
) AS parent_phone, split_part(p.location, ',', 1) AS province
, split_part(p.location, ',', 2) AS city
FROM jd_patient p
LEFT JOIN fw_user u ON u.fw_user_id = p.fw_user_id
LEFT JOIN jd_district di ON p.jd_district_id = di.jd_district_id
LEFT JOIN jd_qrcode q ON p.jd_qrcode_id = q.jd_qrcode_id
WHERE p.jd_patient_id IN (
SELECT p.jd_patient_id
FROM jd_patient p
LEFT JOIN fw_user u ON u.fw_user_id = p.fw_user_id
LEFT JOIN jd_district di ON p.jd_district_id = di.jd_district_id
LEFT JOIN jd_qrcode q ON p.jd_qrcode_id = q.jd_qrcode_id
WHERE 1 = 1
AND p.fw_user_id IS NOT NULL
ORDER BY p.create_date DESC, p.location
LIMIT 10 OFFSET 0
)
ORDER BY p.create_date DESC, p.location
这样便避免了产生大临时表。
最终性能提高了两个数量级(17975306.40 --> 150933.10)：
QUERY PLAN
------------------------------------------------------------------------------------------
--------------------------------
Sort (cost=150933.10..150933.12 rows=10 width=201)
Sort Key: p.create_date, p.location
-> Nested Loop Left Join (cost=150755.44..150932.93 rows=10 width=201)
-> Nested Loop Left Join (cost=150755.15..150845.33 rows=10 width=203)
-> Nested Loop Left Join (cost=150755.01..150843.65 rows=10 width=81)
-> Nested Loop (cost=150754.59..150838.86 rows=10 width=81)
-> HashAggregate (cost=150754.16..150754.26 rows=10 width=16)
Group Key: p_1.jd_patient_id
-> Limit (cost=150754.01..150754.03 rows=10 width=86)
-> Sort (cost=150754.01..155720.78 rows=1986709 w
idth=86)
Sort Key: p_1.create_date, p_1.location
-> Seq Scan on jd_patient p_1 (cost=0.00..1
07821.94 rows=1986709 width=86)
Filter: (fw_user_id IS NOT NULL)
-> Index Scan using pk_jd_patient on jd_patient p (cost=0.43.
.8.45 rows=1 width=97)
Index Cond: (jd_patient_id = p_1.jd_patient_id)
-> Index Scan using pk_fw_user on fw_user u (cost=0.42..0.47 rows=1
width=32)
Index Cond: (fw_user_id = p.fw_user_id)
-> Index Scan using pk_jd_district on jd_district di (cost=0.14..0.16 row
s=1 width=154)
Index Cond: (p.jd_district_id = jd_district_id)
-> Index Scan using pk_jd_qrcode on jd_qrcode q (cost=0.29..0.30 rows=1 width=3
0)
Index Cond: (p.jd_qrcode_id = jd_qrcode_id)
SubPlan 1
-> Index Scan using pk_fw_user on fw_user (cost=0.42..8.44 rows=1 width=12)
Index Cond: (fw_user_id = u.parent_id)
(24 rows)
