DeepSeek辅助生成的PostgreSQL 查询优化实战幻灯片脚本

经过比较，代码用formatter_style="colorful"和paragraph_config={"font": "Milky Han Mono 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()# ---------- 1. WHERE vs HAVING 对比 ----------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=''' -- 正： 正确：使用WHERE在聚合前过滤 SELECT status, COUNT(*) FROM users WHERE created_at >= '2024-01-01' -- 先过滤 GROUP BY status; ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"Milky Han Mono SC"}).scale(0.8).shift(UP*0.5)# HAVING示例 - 修复参数having_code=Code(code_string=''' -- 误： 低效：使用HAVING在聚合后过滤 SELECT status, COUNT(*) FROM users GROUP BY status HAVING status = 1; -- 后过滤，浪费资源 ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"Milky Han Mono 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: 在聚合前过滤行\nHAVING: 在聚合后过滤分组",font_size=24,color=BLUE).to_edge(DOWN)self.play(Write(note))self.wait(2)self.next_slide()# ---------- 2. 避免SELECT * ----------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=''' -- 误： 糟糕：返回所有列 SELECT * FROM users WHERE status = 1; -- 问题： -- 1. 可能返回不需要的大字段(TEXT/JSON) -- 2. 无法使用覆盖索引 -- 3. 表结构变更影响应用程序 ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"Milky Han Mono SC"}).scale(0.7).shift(LEFT*3+UP*0.5)# 好的写法select_specific=Code(code_string=''' -- 正： 优秀：只返回需要的列 SELECT id, email, created_at FROM users WHERE status = 1; -- 优点： -- 1. 减少网络传输 -- 2. 可能使用覆盖索引 -- 3. 程序更健壮 ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"Milky Han Mono 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=''' -- 覆盖索引示例 CREATE INDEX idx_user_status_covering ON users(status) INCLUDE (id, email); -- 查询可以直接从索引获取数据，无需回表 ''',language="sql",background="rectangle",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":16,"font":"Milky Han Mono SC"}).scale(0.7).to_edge(DOWN)self.play(FadeIn(cover_idx,shift=UP))self.wait(2)self.next_slide()# ---------- 3. 善用LIMIT分页 ----------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=''' -- 误： 深分页性能问题 SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000; -- OFFSET 10000 仍然需要扫描10010行 ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"Milky Han Mono SC"}).scale(0.8).shift(UP*0.5)# 游标分页优化cursor_pagination=Code(code_string=''' -- 正： 游标分页（键集分页） SELECT * FROM users WHERE id > 10000 -- 记住上一页最后一条的ID ORDER BY id LIMIT 10; -- 优点：始终只扫描10行，性能稳定 ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"Milky Han Mono 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("OFFSET 10000: 扫描10010行 | 游标分页: 扫描10行",font_size=24,color=GREEN).to_edge(DOWN)self.play(Write(perf_note))self.wait(2)self.next_slide()# ---------- 4. JOIN优化技巧 ----------self.clear()join_title=Text("4. JOIN查询优化技巧",font_size=40,color=YELLOW).to_edge(UP)self.play(Write(join_title))# 先过滤再JOINjoin_example=Code(code_string=''' -- 正： 先缩小数据集再JOIN SELECT u.id, u.email, o.order_amount FROM ( SELECT * FROM users WHERE status = 1 -- 先过滤用户 ) u JOIN ( SELECT * FROM orders WHERE created_at > now() - interval '30 days' -- 只关联最近30天订单 ) o ON u.id = o.user_id; -- 等价改写（优化器可能自动优化，但显式写更清晰） ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"Milky Han Mono SC"}).scale(0.7).shift(UP*0.5)# JOIN索引要求join_index=Code(code_string=''' -- JOIN字段必须建索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at); -- 小表驱动大表 -- 优化器通常会自动选择，但了解原理有帮助 ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":18,"font":"Milky Han Mono 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()# ---------- 5. 查询写法规范总结 ----------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️⃣ 列显式化: 杜绝SELECT *，只取所需字段",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=''' -- 误： 低效：循环单条插入 BEGIN; INSERT INTO logs VALUES (1, 'msg1'); INSERT INTO logs VALUES (2, 'msg2'); INSERT INTO logs VALUES (3, 'msg3'); COMMIT; -- 正： 高效：批量插入 INSERT INTO logs VALUES (1, 'msg1'), (2, 'msg2'), (3, 'msg3'); ''',language="sql",background="rectangle",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":16,"font":"Milky Han Mono 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("🔑 管得勤: EXPLAIN ANALYZE验证, 删除冗余索引",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=''' -- 优秀复合索引示例 CREATE INDEX idx_users_status_created ON users(status, created_at DESC); -- 高效查询 EXPLAIN ANALYZE SELECT id, email FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 10; ''',language="sql",background="window",add_line_numbers=False,formatter_style="colorful",paragraph_config={"font_size":20,"font":"Milky Han Mono SC"}).scale(0.7).to_edge(DOWN)self.play(FadeIn(example,shift=UP))self.wait(3)# 运行命令：# manim -pql query_slides.py QueryOptimization# manim -pql query_slides.py PreviousTopicReview