理解业务表结构微调sql得秒杀效果

godba 2025-02-04

前言：

一直认为表设计才是Sql优化的王道，因为性能是设计出来的而不是调优出来的。就以本案例说明问题。

SQL语句：

SELECT T.ID,T.title,T.CONTENT,T.LEVEL,T.TYPE,T.target_url,
T.attachment_url,T.source_app_name,T.create_time
FROM
(SELECT n.ID,n.title,n.CONTENT,n.LEVEL,n.TYPE,
n.target_url,n.attachment_url,n.source_app_name,
n.create_time
FROM  t_fXXXXXXk_notify n 
JOIN t_fXXXXXXk_user_notify u ON n.ID = u.notify_id
WHERE u.user_id ='E94A0732B6FC5AA1E0430901E80A21E9'
AND n.status = 'DONE'
AND n.TYPE NOT IN ( 'remind', '系统公告' )
AND n.LEVEL IS NOT NULL
AND u.READ = FALSE
) T
ORDER BY T.create_time LIMIT 100 OFFSET 500;

执行计划：

Limit (cost=238.57..262.33 rows=10 width=342) (actual time=217.019..217.697
rows=10 loops=1)
Buffers: shared hit=46729
-> Nested Loop (cost=0.98..2231886.91 rows=939404 width=342) (actualtime=204.298..217.683 rows=110 loops=1)
Buffers: shared hit=46729
-> Index Scan using index_notify_create_time on  t_fXXXXXXk_notify n
(cost=0.43..154744.55 rows=1445351 width=342) (actual time=0.024..12.893
rows=8300 loops=1)
Filter: ((level IS NOT NULL) AND ((type)::text <> ALL ('{remind,系统公告}'::text[])) AND ((status)::text = 'DONE'::text))
Rows Removed by Filter: 1261
Buffers: shared hit=4136
-> Index Scan using index_user_notify_notify_id on t_fXXXXXXk_user_notify u (cost=0.56..1.38 rows=6 width=33) (actual time=0.024..0.024 rows=0 loops=8300)
Index Cond: ((notify_id)::text = (n.id)::text)
Filter: ((NOT read) AND ((user_id)::text ='E94A0732B6FC5AA1E0430901E80A21E9'::text))

首先看懂执行计划：

执行计划中使用首先根据 create time 索引扫描 n表然后使用nest loop 驱动u表，再根据 user_id 过滤数据。熟悉执行计划的同学都知道，user_id 过滤放到最后，显然大量的数据在此排除掉需要消耗大量执行成本。导致整体逻辑读 4.6W。

读SQl的业务 + 咨询开发。其实就是查询某用户的未读消息，并且分页显示最新消息。 u表中虽然有用户信息，但是没有消息的时间信息，所以需要关联n表获取时间并且排序。

第一步创建冗余表

 create table  t_fXXXXXXk_user_notify_0107 as 
 select * from  t_fXXXXXXk_user_notify;
 --> 查询时间: 103.584s

第二步冗余create_time字段+设置主键

--给表 t_fXXXXXXk_user_notify_0107 冗余 t_fXXXXXXk_notify
---表的create_time字段接受消息时间，字段记录为 notify_create_time

 alter table  t_fXXXXXXk_user_notify_0107 add column 
 notify_create_time timestamp(0) without time zone;
 --> OK
 
  ALTER TABLE   t_fXXXXXXk_user_notify_0107 ADD CONSTRAINT t_framework_user_notify_0107_pkey PRIMARY KEY ( id )
--> OK
--> 查询时间: 73.907s

第三步根据更新 notify_create_time 字段的值

 update  t_fXXXXXXk_user_notify_0107 t
set notify_create_time = t2.create_time
from t_fXXXXXXk_notify t2
where t.notify_id = t2.id --- t2.id 是主键  
-- update  t_fXXXXXXk_user_notify_0107 t
-- set notify_create_time = t2.create_time
-- from t_fXXXXXXk_notify t2
-- where t.notify_id = t2.id
--> Affected rows: 3847019
--> 查询时间: 385.928s

第四步: 建立索引

create index idx_t_fXXXXXXk_user_notify_0107_uid_read_ctime on
 t_fXXXXXXk_user_notify_0107 (user_id,READ, notify_create_time
);


--> OK
---> 查询时间: 92.357s

测试：

SELECT T.ID,T.title,T.CONTENT,T.LEVEL,T.TYPE,T.target_url,
T.attachment_url,T.source_app_name,T.create_time
FROM
(SELECT n.ID,n.title,n.CONTENT,n.LEVEL,n.TYPE,
n.target_url,n.attachment_url,n.source_app_name,
 u.notify_create_time   ---新字段 
FROM  t_fXXXXXXk_notify n 
JOIN  t_fXXXXXXk_user_notify_0107  u ON n.ID = u.notify_id
WHERE u.user_id ='E94A0732B6FC5AA1E0430901E80A21E9'
AND n.status = 'DONE'
AND n.TYPE NOT IN ( 'remind', '系统公告' )
AND n.LEVEL IS NOT NULL
AND u.READ = FALSE
) T
ORDER BY T.notify_create_time   ---新字段 排序
 LIMIT 100 OFFSET 500;

执行效果符合预期：9.5ms, 2472 逻辑读。

总结：

SQL	执行成本
老SQL	217ms, 4.6W逻辑读
新SQL	9.5ms, 2472 逻辑读

当时分析该故障时情况稍微复杂点，当时做的故障总结如下

sql sql优化

文章转载自godba，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

理解业务表结构微调sql得秒杀效果

评论