经过比较代码用formatter_stylecolorful和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):# ---------- 标题页 ----------titleText(PostgreSQL 查询优化实战,font_size48,colorBLUE)subtitleText(第二专题查询语句写法规范与技巧,font_size36,colorGRAY)authorsText(少查·快连·精索·常析·避坑,font_size28,colorGREEN)VGroup(title,subtitle,authors).arrange(DOWN,buff0.5)self.play(Write(title))self.play(FadeIn(subtitle,shiftUP))self.play(FadeIn(authors,shiftUP))self.wait(1)self.next_slide()# 清除当前画面self.clear()# ---------- 1. WHERE vs HAVING 对比 ----------where_titleText(1. 优先使用WHERE而非HAVING,font_size40,colorYELLOW).to_edge(UP)self.play(Write(where_title))# WHERE示例 - 修复参数where_codeCode(code_string -- 正 正确使用WHERE在聚合前过滤 SELECT status, COUNT(*) FROM users WHERE created_at 2024-01-01 -- 先过滤 GROUP BY status; ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:20,font:Milky Han Mono SC}).scale(0.8).shift(UP*0.5)# HAVING示例 - 修复参数having_codeCode(code_string -- 误 低效使用HAVING在聚合后过滤 SELECT status, COUNT(*) FROM users GROUP BY status HAVING status 1; -- 后过滤浪费资源 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:20,font:Milky Han Mono SC}).next_to(where_code,DOWN,buff0.5).scale(0.8)self.play(FadeIn(where_code,shiftLEFT))self.play(FadeIn(having_code,shiftRIGHT))noteText(WHERE: 在聚合前过滤行\nHAVING: 在聚合后过滤分组,font_size24,colorBLUE).to_edge(DOWN)self.play(Write(note))self.wait(2)self.next_slide()# ---------- 2. 避免SELECT * ----------self.clear()select_titleText(2. 避免使用SELECT *,font_size40,colorYELLOW).to_edge(UP)self.play(Write(select_title))# 不好的写法select_starCode(code_string -- 误 糟糕返回所有列 SELECT * FROM users WHERE status 1; -- 问题 -- 1. 可能返回不需要的大字段(TEXT/JSON) -- 2. 无法使用覆盖索引 -- 3. 表结构变更影响应用程序 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:18,font:Milky Han Mono SC}).scale(0.7).shift(LEFT*3UP*0.5)# 好的写法select_specificCode(code_string -- 正 优秀只返回需要的列 SELECT id, email, created_at FROM users WHERE status 1; -- 优点 -- 1. 减少网络传输 -- 2. 可能使用覆盖索引 -- 3. 程序更健壮 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:18,font:Milky Han Mono SC}).scale(0.7).shift(RIGHT*3UP*0.5)self.play(FadeIn(select_star,shiftLEFT))self.play(FadeIn(select_specific,shiftRIGHT))# 添加覆盖索引说明cover_idxCode(code_string -- 覆盖索引示例 CREATE INDEX idx_user_status_covering ON users(status) INCLUDE (id, email); -- 查询可以直接从索引获取数据无需回表 ,languagesql,backgroundrectangle,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:16,font:Milky Han Mono SC}).scale(0.7).to_edge(DOWN)self.play(FadeIn(cover_idx,shiftUP))self.wait(2)self.next_slide()# ---------- 3. 善用LIMIT分页 ----------self.clear()limit_titleText(3. 善用LIMIT分页与游标,font_size40,colorYELLOW).to_edge(UP)self.play(Write(limit_title))# 传统分页问题offset_paginationCode(code_string -- 误 深分页性能问题 SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000; -- OFFSET 10000 仍然需要扫描10010行 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:20,font:Milky Han Mono SC}).scale(0.8).shift(UP*0.5)# 游标分页优化cursor_paginationCode(code_string -- 正 游标分页键集分页 SELECT * FROM users WHERE id 10000 -- 记住上一页最后一条的ID ORDER BY id LIMIT 10; -- 优点始终只扫描10行性能稳定 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:20,font:Milky Han Mono SC}).next_to(offset_pagination,DOWN,buff0.5).scale(0.8)self.play(FadeIn(offset_pagination,shiftLEFT))self.play(FadeIn(cursor_pagination,shiftRIGHT))# 性能对比perf_noteText(OFFSET 10000: 扫描10010行 | 游标分页: 扫描10行,font_size24,colorGREEN).to_edge(DOWN)self.play(Write(perf_note))self.wait(2)self.next_slide()# ---------- 4. JOIN优化技巧 ----------self.clear()join_titleText(4. JOIN查询优化技巧,font_size40,colorYELLOW).to_edge(UP)self.play(Write(join_title))# 先过滤再JOINjoin_exampleCode(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; -- 等价改写优化器可能自动优化但显式写更清晰 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:18,font:Milky Han Mono SC}).scale(0.7).shift(UP*0.5)# JOIN索引要求join_indexCode(code_string -- JOIN字段必须建索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at); -- 小表驱动大表 -- 优化器通常会自动选择但了解原理有帮助 ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:18,font:Milky Han Mono SC}).next_to(join_example,DOWN,buff0.3).scale(0.7)self.play(FadeIn(join_example,shiftUP))self.play(FadeIn(join_index,shiftDOWN))join_noteText(原则小结果集驱动大结果集 | JOIN列务必索引,font_size24,colorBLUE).to_edge(DOWN)self.play(Write(join_note))self.wait(2)self.next_slide()# ---------- 5. 查询写法规范总结 ----------self.clear()summary_titleText(查询写法五大黄金法则,font_size44,colorYELLOW).to_edge(UP)self.play(Write(summary_title))rulesVGroup(Text(1️⃣ 过滤先行: WHERE优先于HAVING先缩小数据集,font_size28),Text(2️⃣ 列显式化: 杜绝SELECT *只取所需字段,font_size28),Text(3️⃣ 分页优化: 用游标分页替代OFFSET深分页,font_size28),Text(4️⃣ JOIN精简: 先过滤再JOIN确保关联字段有索引,font_size28),Text(5️⃣ 批量操作: 避免循环单条SQL使用批量DML,font_size28),).arrange(DOWN,aligned_edgeLEFT,buff0.3).shift(UP*0.5)forruleinrules:self.play(Write(rule,lag_ratio0.1))self.wait(0.3)# 性能对比示例batch_exampleCode(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); ,languagesql,backgroundrectangle,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:16,font:Milky Han Mono SC}).scale(0.6).to_edge(DOWN)self.play(FadeIn(batch_example,shiftUP))self.wait(3)# 下一专题预告self.clear()next_topicText(下一专题预告,font_size48,colorBLUE)topic_nameText(表结构设计基础规范,font_size36,colorGREEN)topic_pointsVGroup(Text(• 遵循三范式但适度反范式,font_size28),Text(• 合理选择数据类型,font_size28),Text(• 主键必设且用INT/UUID,font_size28),).arrange(DOWN,aligned_edgeLEFT,buff0.2)VGroup(next_topic,topic_name,topic_points).arrange(DOWN,buff0.5)self.play(Write(next_topic),Write(topic_name),*[Write(point)forpointintopic_points])self.wait(3)classPreviousTopicReview(Slide):快速回顾第一专题核心内容defconstruct(self):titleText(第一专题回顾: 索引优化,font_size48,colorYELLOW).to_edge(UP)self.play(Write(title))# 核心要点回顾pointsVGroup(Text( 建得准: 复合索引设计 (等值列在前, 范围/排序在后),font_size28),Text( 用得对: 避免函数、隐式转换、左模糊,font_size28),Text( 管得勤: EXPLAIN ANALYZE验证, 删除冗余索引,font_size28),Text( B树特点: 叶子存数据, 双向链表, 高度平衡,font_size28),).arrange(DOWN,aligned_edgeLEFT,buff0.3).shift(UP*1)forpointinpoints:self.play(Write(point,lag_ratio0.1))self.wait(0.2)# 快速示例exampleCode(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; ,languagesql,backgroundwindow,add_line_numbersFalse,formatter_stylecolorful,paragraph_config{font_size:20,font:Milky Han Mono SC}).scale(0.7).to_edge(DOWN)self.play(FadeIn(example,shiftUP))self.wait(3)# 运行命令# manim -pql query_slides.py QueryOptimization# manim -pql query_slides.py PreviousTopicReview