xmlns="http://www.w3.org/2000/svg"
style="display:
none;">
经过比较，代码用formatter_style="colorful"和paragraph_config={"font":
"Milky
SC"}设置效果较好。
frommanimimport*frommanim_slidesimportSlide#配置中文字体，请根据系统环境调整
config.tex_template.add_to_preamble(r"\usepackage{ctex}")config.tex_template.add_to_preamble(r"\usepackage{xcolor}")classQueryOptimization(Slide):"""PostgreSQL查询优化专题幻灯片
查询语句写法规范与技巧"""
defconstruct(self):#----------
----------
title=Text("PostgreSQL查询优化实战"
,font_size=48,color=BLUE)subtitle=Text("第二专题：查询语句写法规范与技巧",font_size=36,color=GRAY)authors=Text("少查·快连·精索·常析·避坑",font_size=28,color=GREEN)VGroup(title,subtitle,authors).arrange(DOWN,buff=0.5)self.play(Write(title))self.play(FadeIn(subtitle,shift=UP))self.play(FadeIn(authors,shift=UP))self.wait(1)self.next_slide()#清除当前画面
self.clear()#----------
----------
where_title=Text("1.优先使用WHERE而非HAVING"
,font_size=40,color=YELLOW).to_edge(UP)self.play(Write(where_title))#WHERE示例
修复参数
where_code=Code(code_string='''正：
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"MilkyHan
SC"
}).scale(0.8).shift(UP*0.5)#HAVING示例
修复参数
having_code=Code(code_string='''误：
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"MilkyHan
SC"
}).next_to(where_code,DOWN,buff=0.5).scale(0.8)self.play(FadeIn(where_code,shift=LEFT))self.play(FadeIn(having_code,shift=RIGHT))note=Text("WHERE:在聚合后过滤分组"
,font_size=24,color=BLUE).to_edge(DOWN)self.play(Write(note))self.wait(2)self.next_slide()#----------
----------
self.clear()select_title=Text("2.避免使用SELECT
*"
,font_size=40,color=YELLOW).to_edge(UP)self.play(Write(select_title))#不好的写法
select_star=Code(code_string='''误：
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"MilkyHan
SC"
}).scale(0.7).shift(LEFT*3+UP*0.5)#好的写法
select_specific=Code(code_string='''正：
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"MilkyHan
SC"
}).scale(0.7).shift(RIGHT*3+UP*0.5)self.play(FadeIn(select_star,shift=LEFT))self.play(FadeIn(select_specific,shift=RIGHT))#添加覆盖索引说明
cover_idx=Code(code_string='''覆盖索引示例
'''
,language="sql",background="rectangle",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":16,"font":"MilkyHan
SC"
}).scale(0.7).to_edge(DOWN)self.play(FadeIn(cover_idx,shift=UP))self.wait(2)self.next_slide()#----------
----------
self.clear()limit_title=Text("3.善用LIMIT分页与游标"
,font_size=40,color=YELLOW).to_edge(UP)self.play(Write(limit_title))#传统分页问题
offset_pagination=Code(code_string='''误：
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"MilkyHan
SC"
}).scale(0.8).shift(UP*0.5)#游标分页优化
cursor_pagination=Code(code_string='''正：
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"MilkyHan
SC"
}).next_to(offset_pagination,DOWN,buff=0.5).scale(0.8)self.play(FadeIn(offset_pagination,shift=LEFT))self.play(FadeIn(cursor_pagination,shift=RIGHT))#性能对比
perf_note=Text("OFFSET10000:
扫描10行"
,font_size=24,color=GREEN).to_edge(DOWN)self.play(Write(perf_note))self.wait(2)self.next_slide()#----------
----------
self.clear()join_title=Text("4.JOIN查询优化技巧"
,font_size=40,color=YELLOW).to_edge(UP)self.play(Write(join_title))#先过滤再JOIN
join_example=Code(code_string='''正：
等价改写（优化器可能自动优化，但显式写更清晰）
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"MilkyHan
SC"
}).scale(0.7).shift(UP*0.5)#JOIN索引要求
join_index=Code(code_string='''JOIN字段必须建索引
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"MilkyHan
SC"
}).next_to(join_example,DOWN,buff=0.3).scale(0.7)self.play(FadeIn(join_example,shift=UP))self.play(FadeIn(join_index,shift=DOWN))join_note=Text("原则：小结果集驱动大结果集JOIN列务必索引"
,font_size=24,color=BLUE).to_edge(DOWN)self.play(Write(join_note))self.wait(2)self.next_slide()#----------
----------
self.clear()summary_title=Text("查询写法五大黄金法则",font_size=44,color=YELLOW).to_edge(UP)self.play(Write(summary_title))rules=VGroup(Text("1️⃣WHERE优先于HAVING，先缩小数据集"
,font_size=28),Text("2️⃣列显式化:
*，只取所需字段"
,font_size=28),Text("3️⃣分页优化:
用游标分页替代OFFSET深分页"
,font_size=28),Text("4️⃣JOIN精简:
先过滤再JOIN，确保关联字段有索引"
,font_size=28),Text("5️⃣批量操作:
避免循环单条SQL，使用批量DML"
,font_size=28),).arrange(DOWN,aligned_edge=LEFT,buff=0.3).shift(UP*0.5)forruleinrules:self.play(Write(rule,lag_ratio=0.1))self.wait(0.3)#性能对比示例
batch_example=Code(code_string='''误：
'''
,language="sql",background="rectangle",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":16,"font":"MilkyHan
SC"
}).scale(0.6).to_edge(DOWN)self.play(FadeIn(batch_example,shift=UP))self.wait(3)#下一专题预告
self.clear()next_topic=Text("下一专题预告",font_size=48,color=BLUE)topic_name=Text("表结构设计基础规范",font_size=36,color=GREEN)topic_points=VGroup(Text("•遵循三范式但适度反范式"
,font_size=28),Text("•合理选择数据类型"
,font_size=28),Text("•主键必设且用INT/UUID"
,font_size=28),).arrange(DOWN,aligned_edge=LEFT,buff=0.2)VGroup(next_topic,topic_name,topic_points).arrange(DOWN,buff=0.5)self.play(Write(next_topic),Write(topic_name),*[Write(point)forpointintopic_points])self.wait(3)classPreviousTopicReview(Slide):"""快速回顾第一专题核心内容"""defconstruct(self):title=Text("第一专题回顾:索引优化"
,font_size=48,color=YELLOW).to_edge(UP)self.play(Write(title))#核心要点回顾
points=VGroup(Text("🔑建得准:
范围/排序在后)"
,font_size=28),Text("🔑用得对:
避免函数、隐式转换、左模糊"
,font_size=28),Text("🔑管得勤:
删除冗余索引"
,font_size=28),Text("🔑B+树特点:
高度平衡"
,font_size=28),).arrange(DOWN,aligned_edge=LEFT,buff=0.3).shift(UP*1)forpointinpoints:self.play(Write(point,lag_ratio=0.1))self.wait(0.2)#快速示例
example=Code(code_string='''优秀复合索引示例
'''
,language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"MilkyHan
SC"
}).scale(0.7).to_edge(DOWN)self.play(FadeIn(example,shift=UP))self.wait(3)#manim
QueryOptimization
#manim
PreviousTopicReview
