目录第一章为什么需要数据库性能优化第二章执行计划 — SQL的“导航地图”2.1 什么是执行计划2.2 如何查看执行计划2.3 执行计划中的关键术语零基础版第三章企业案例背景——某电商平台3.1 业务场景3.2 典型业务查询第四章优化前问题诊断4.1 原查询一标量子查询方式4.2 原查询二多表关联方式第五章优化方案实施5.1 索引优化最立竿见影5.2 分区表优化针对时间维度的巨表5.3 查询重写——将标量子查询改为 JOIN GROUP BY5.4 复杂关联查询——CTE分阶段聚合优化后查询二第六章优化效果对比第七章高级优化技巧7.1 物化视图——预计算结果集7.2 参数化查询预编译语句7.3 监控与持续调优第八章知识补充 — 列存表与预编译详解8.1 PostgreSQL中的列式存储8.2 预编译语句原理第九章内存对齐与行存储优化PostgreSQL存储层调优9.1 什么是内存对齐9.2 PostgreSQL中的行存储结构9.3 一个具体的浪费示例9.4 如何检查表的对齐浪费9.5 企业实战案例订单表列顺序优化9.6 优化原则总结9.7 内存对齐与CPU缓存优化延伸第十章企业最佳实践总结10.1 亿级数据优化的“黄金法则”10.2 从45秒到1.8秒的启示第十一章 数据开发优化面试指南企业级高频考点11.1 基础概念类11.2 索引与存储优化11.3 查询重写优化11.4 分区与分片11.5 数据库参数调优11.6 实战场景题11.7 面试避坑指南第一章为什么需要数据库性能优化在企业级应用中数据库往往是最容易成为系统瓶颈的环节。一个未经优化的SQL语句可能从毫秒级响应变成分钟级超时直接影响用户体验和业务收入。尤其是当数据量达到千万、亿级时不合理的查询设计会导致数据库服务器CPU飙高、内存耗尽、磁盘I/O拥堵。性能优化的目标就是用最少的资源、最短的时间返回正确的结果。本章将以一个真实电商平台为背景带你掌握从执行计划分析到索引、分区、查询重写等全套优化手段最终实现20倍以上的性能提升。第二章执行计划 — SQL的“导航地图”2.1 什么是执行计划执行计划是数据库执行SQL语句时所采用的操作步骤和算法的详细描述。就像你开车去一个陌生地点导航会规划出路线高速、国道、小路数据库也会为你的查询选择一条“路径”。理解执行计划是优化的第一步。2.2 如何查看执行计划-- 最基本只显示计划不实际执行 EXPLAIN SELECT * FROM users WHERE age 30; -- 实际执行并显示真实耗时、缓存命中情况推荐 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age 30; -- 更详细的输出包含输出列信息 EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM orders WHERE order_date 2023-01-01;2.3 执行计划中的关键术语零基础版术语含义性能启示Seq Scan全表顺序扫描一行一行读取小表可以接受大表非常慢Index Scan使用B-tree索引快速定位效率高适合过滤条件选择性好Index Only Scan索引中已包含所需所有列无需回表最优速度最快BitmapHeap Scan先通过索引找到数据位置再批量读取适合返回较多行时Nested Loop嵌套循环连接外表每行都要匹配内表适用于小表驱动大表且有索引Hash Join先构建哈希表再匹配适合两张大表等值连接Merge Join先排序再合并适合已排序的数据Sort显式排序操作消耗内存可能溢出到磁盘企业口诀Seq Scan是大忌Index Scan是利器Hash Join大表欢Nested Loop小表迷。第三章企业案例背景——某电商平台3.1 业务场景该电商平台每天产生数千万订单需要分析用户购买行为、区域销售趋势等。核心数据表如下表名记录数说明users3000万用户基本信息orders5000万订单主表order_items2亿订单商品明细products500万商品信息3.2 典型业务查询查询一统计每个城市VIP用户的订单总额、订单次数、平均订单金额按总额降序取前100名。查询二分析一线城市北上广深的活跃用户注册超2年统计其总消费、购买商品种类数、最后一次购买时间等并筛选出消费≥1000元且订单数≥3的用户。这两个查询在生产环境执行时间分别为45秒和68秒用户无法接受。下面我们一步步优化。第四章优化前问题诊断4.1 原查询一标量子查询方式SELECT u.city, u.vip_level, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.user_id) AS order_count, (SELECT SUM(total_amount) FROM orders o WHERE o.user_id u.user_id) AS total_amount, (SELECT AVG(total_amount) FROM orders o WHERE o.user_id u.user_id) AS avg_amount FROM users u WHERE u.vip_level 2 AND u.registration_date 2022-01-01 ORDER BY total_amount DESC LIMIT 100;执行计划分析每个用户会执行3次标量子查询COUNT、SUM、AVG导致子查询被重复执行数百万次。orders表上没有user_id索引导致每次子查询都全表扫描。总执行时间45.2秒。4.2 原查询二多表关联方式SELECT u.user_id, u.username, u.city, u.vip_level, COUNT(DISTINCT o.order_id) AS order_count, SUM(o.total_amount) AS total_spent, COUNT(oi.item_id) AS item_count, COUNT(DISTINCT oi.product_id) AS unique_products, AVG(oi.price * oi.quantity) AS avg_item_value, MAX(o.order_date) AS last_order_date FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.status completed AND o.order_date 2023-01-01 LEFT JOIN order_items oi ON o.order_id oi.order_id LEFT JOIN products p ON oi.product_id p.product_id WHERE u.registration_date 2020-01-01 AND u.city IN (北京,上海,广州,深圳) AND (p.category_id BETWEEN 1 AND 10 OR p.category_id IS NULL) GROUP BY u.user_id, u.username, u.city, u.vip_level HAVING COUNT(DISTINCT o.order_id) 3 AND SUM(o.total_amount) 1000 ORDER BY total_spent DESC LIMIT 50;执行计划暴露的问题所有表users、orders、order_items、products都使用全表扫描Seq Scan。orders表与order_items关联时缺乏索引导致Nested Loop灾难2亿行×5000万行天文数字。GROUP BY之前已产生巨大的中间结果集内存不足以排序大量使用临时磁盘文件。总执行时间68.7秒。第五章优化方案实施5.1 索引优化最立竿见影-- 用户表复合索引覆盖过滤和排序 CREATE INDEX CONCURRENTLY idx_users_vip_city ON users(vip_level, city, registration_date); -- 订单表关键连接字段 CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); CREATE INDEX CONCURRENTLY idx_orders_user_status_date ON orders(user_id, status, order_date); CREATE INDEX CONCURRENTLY idx_orders_date_status ON orders(order_date, status); -- 订单详情表 CREATE INDEX CONCURRENTLY idx_order_items_order_id ON order_items(order_id); CREATE INDEX CONCURRENTLY idx_order_items_product_id ON order_items(product_id); CREATE INDEX CONCURRENTLY idx_order_items_composite ON order_items(order_id, product_id, price, quantity); -- 商品表 CREATE INDEX CONCURRENTLY idx_products_category ON products(category_id);CONCURRENTLY选项允许在不阻塞读写的情况下创建索引适合生产环境。5.2 分区表优化针对时间维度的巨表将orders表按order_date进行范围分区每年一个分区。这样查询特定年份的数据时只扫描对应分区避免全表。-- 创建分区主表 CREATE TABLE orders_partitioned (LIKE orders INCLUDING DEFAULTS) PARTITION BY RANGE (order_date); -- 创建年度分区 CREATE TABLE orders_2020 PARTITION OF orders_partitioned FOR VALUES FROM (2020-01-01) TO (2021-01-01); CREATE TABLE orders_2021 PARTITION OF orders_partitioned FOR VALUES FROM (2021-01-01) TO (2022-01-01); CREATE TABLE orders_2022 PARTITION OF orders_partitioned FOR VALUES FROM (2022-01-01) TO (2023-01-01); CREATE TABLE orders_2023 PARTITION OF orders_partitioned FOR VALUES FROM (2023-01-01) TO (2024-01-01); -- 迁移数据并建立本地索引 INSERT INTO orders_partitioned SELECT * FROM orders WHERE order_date 2020-01-01; CREATE INDEX idx_orders_part_user_status ON orders_partitioned(user_id, status);5.3 查询重写——将标量子查询改为 JOIN GROUP BY优化后查询一SELECT u.city, u.vip_level, COUNT(o.order_id) AS order_count, COALESCE(SUM(o.total_amount), 0) AS total_amount, COALESCE(AVG(o.total_amount), 0) AS avg_amount FROM users u LEFT JOIN orders_partitioned o ON u.user_id o.user_id AND o.order_date 2022-01-01 WHERE u.vip_level 2 AND u.registration_date 2022-01-01 GROUP BY u.user_id, u.city, u.vip_level ORDER BY total_amount DESC LIMIT 100;改进点一次LEFT JOIN完成所有聚合避免重复子查询。利用分区裁剪只扫描2022年后的订单分区。执行时间从45秒降至1.8秒。5.4 复杂关联查询——CTE分阶段聚合优化后查询二WITH user_orders AS ( SELECT u.user_id, u.username, u.city, u.vip_level, COUNT(o.order_id) AS order_count, SUM(o.total_amount) AS total_spent, MAX(o.order_date) AS last_order_date FROM users u INNER JOIN orders_partitioned o ON u.user_id o.user_id WHERE u.registration_date 2020-01-01 AND u.city IN (北京,上海,广州,深圳) AND o.status completed AND o.order_date 2023-01-01 GROUP BY u.user_id, u.username, u.city, u.vip_level HAVING COUNT(o.order_id) 3 AND SUM(o.total_amount) 1000 ), order_items_agg AS ( SELECT oi.order_id, COUNT(oi.item_id) AS item_count, COUNT(DISTINCT oi.product_id) AS unique_products, AVG(oi.price * oi.quantity) AS avg_item_value FROM order_items oi INNER JOIN products p ON oi.product_id p.product_id WHERE p.category_id BETWEEN 1 AND 10 GROUP BY oi.order_id ) SELECT uo.*, COALESCE(oia.item_count, 0) AS total_items, COALESCE(oia.unique_products, 0) AS total_unique_products, COALESCE(oia.avg_item_value, 0) AS overall_avg_item_value FROM user_orders uo LEFT JOIN orders_partitioned o ON uo.user_id o.user_id LEFT JOIN order_items_agg oia ON o.order_id oia.order_id GROUP BY uo.user_id, uo.username, uo.city, uo.vip_level, uo.order_count, uo.total_spent, uo.last_order_date ORDER BY uo.total_spent DESC LIMIT 50;改进点将多表大连接拆分为两个CTE分别聚合后再关联大幅减少中间结果集。利用分区表索引快速过滤。执行时间从68.7秒降至3.2秒。第六章优化效果对比指标优化前优化后提升倍数查询一执行时间45.2秒1.8秒25倍查询二执行时间68.7秒3.2秒21倍内存使用8GB1.2GB85%↓磁盘I/O极高显著降低大量减少执行计划操作Seq Scan 多次子查询Index Scan Hash Join质的飞跃第七章高级优化技巧7.1 物化视图——预计算结果集对于统计报表类查询可以将聚合结果预先存储为物化视图并定期刷新。CREATE MATERIALIZED VIEW mv_user_order_stats AS SELECT u.user_id, u.city, u.vip_level, COUNT(o.order_id) AS total_orders, SUM(o.total_amount) AS total_spent, AVG(o.total_amount) AS avg_order_value FROM users u LEFT JOIN orders_partitioned o ON u.user_id o.user_id AND o.status completed GROUP BY u.user_id, u.city, u.vip_level; -- 创建索引加速查询 CREATE INDEX idx_mv_city_spent ON mv_user_order_stats(city, total_spent DESC); -- 并发刷新不阻塞查询 REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_order_stats;7.2 参数化查询预编译语句在应用层多次执行相同结构的查询时使用预编译语句可避免重复解析SQL降低CPU开销。PREPARE user_order_analysis (date, text) AS SELECT city, vip_level, COUNT(*) AS user_count, SUM(total_amount) AS revenue FROM users u JOIN orders_partitioned o ON u.user_id o.user_id WHERE u.registration_date $1 AND u.city $2 AND o.status completed GROUP BY city, vip_level; EXECUTE user_order_analysis(2022-01-01, 北京);7.3 监控与持续调优-- 查找慢查询需要pg_stat_statements扩展 SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements WHERE mean_exec_time 1000 -- 超过1秒 ORDER BY mean_exec_time DESC LIMIT 10; -- 检查索引使用率 SELECT schemaname, tablename, indexname, idx_scan, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan;第八章知识补充 — 列存表与预编译详解8.1 PostgreSQL中的列式存储PostgreSQL原生是行存储但可以通过扩展实现列存适用于分析型查询大量聚合、少选列。使用cstore_fdw扩展开源-- 安装后创建外部服务器 CREATE EXTENSION cstore_fdw; CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw; -- 创建列存表 CREATE FOREIGN TABLE sales_columnar ( sale_date date, product_id int, amount numeric ) SERVER cstore_server OPTIONS (compression lz4);注意列存表不支持更新/删除也不支持索引适合只读归档或ETL中间层。8.2 预编译语句原理预编译语句Prepared Statement将SQL语句的解析、规划与参数执行分离。首次执行时数据库生成执行计划并缓存后续执行只替换参数跳过解析阶段。在高并发、重复SQL场景下可减少10%~30%的CPU消耗。第九章内存对齐与行存储优化PostgreSQL存储层调优9.1 什么是内存对齐内存对齐Memory Alignment是计算机系统存储数据的一种方式它要求数据的起始地址必须是其自身大小的整数倍。例如4字节的int类型通常需要存储在地址为4的倍数的位置。这样做可以让CPU单次指令读取完整数据避免跨缓存行访问从而提升性能。在数据库领域内存对齐会影响行存储的紧凑性和CPU缓存效率。PostgreSQL虽然会自动处理基本对齐但列的声明顺序会影响每行记录的实际磁盘占用和内存占用。一个设计不良的表结构可能因填充字节padding浪费20%~30%的空间进而导致更多的磁盘I/O读取更多数据页更低的缓存命中率更慢的索引扫描和顺序扫描9.2 PostgreSQL中的行存储结构PostgreSQL每行数据包含固定长度的头信息23字节对齐到8字节然后依次存储各列的值。每个列的对齐规则由其数据类型决定数据类型典型长度对齐要求char(1)1字节1字节smallint(int2)2字节2字节int(int4)4字节4字节bigint(int8)8字节8字节float44字节4字节float88字节8字节timestamp8字节8字节varchar/text变长通常1字节对齐但指针引用当不同对齐要求的列混合时PostgreSQL会在列之间自动插入填充字节以满足下一个列的对齐要求这些填充字节不存储任何业务数据但占用磁盘和内存。9.3 一个具体的浪费示例假设有下表未优化列顺序CREATE TABLE bad_alignment ( flag char(1), -- 1字节 id bigint, -- 8字节需要8字节对齐 score int, -- 4字节 note text, status smallint -- 2字节 );存储布局简化行头23字节 填充到8字节边界 24字节flag占用1字节但下一个id需要8字节对齐因此后面插入7字节填充 → 实际占8字节id8字节score4字节下一个status需要2字节对齐仅需填充0字节注意后续text是变长通常不要求强对齐但分数后仍可能产生2字节填充若text起始需要4字节边界实际变长字段引用指针为8字节但存储位置较复杂。实际通过pg_class查询表大小会发现每一行浪费约15~20%的空间。优化后列顺序按对齐大小降序CREATE TABLE good_alignment ( id bigint, -- 8字节 score int, -- 4字节 status smallint, -- 2字节 flag char(1), -- 1字节后面填充1字节到2字节边界但总行大小已被优化 note text );原则将对齐要求大的列放在前面小对齐的列放在后面可以最小化填充字节让行更紧凑。9.4 如何检查表的对齐浪费PostgreSQL没有内置函数直接显示填充浪费但可以通过比较表实际大小与理论最小大小来估算。-- 计算理论最小行大小根据列类型 SELECT pg_column_size(ROW( -- 填入各列的典型值注意变长字段需填入实际长度 1::bigint, 1::int, 1::smallint, a::char(1), )) AS min_row_size; -- 查看实际平均行大小 SELECT avg(pg_column_size(t)) FROM (SELECT * FROM good_alignment LIMIT 1000) t;此外使用pageinspect扩展可以更直观地查看页内行布局。9.5 企业实战案例订单表列顺序优化某电商平台的订单表orders原有列顺序简化order_id (bigint), user_id (bigint), amount (numeric), status (varchar), created_at (timestamp), is_deleted (smallint), flag (char(1))该表有20亿行数据量约3.5TB。通过调整列顺序将is_deleted和flag移动到所有定长数值列之后并重新建表CREATE TABLE new AS ... ORDER BY ...新表占用量减少22%全表扫描速度提升18%缓存效率提高。9.6 优化原则总结1优先将固定长度定长且对齐要求大的列放在前面如bigint、double、timestamp。2将对齐要求小的列如char(1)、boolean放在后面。3变长列varchar、text、bytea通常放在最后因为它们不参与对齐填充链。4避免大量char(n)定长字符串尽量使用varchar虽然varchar在PG中存储开销类似text但有长度限制会额外占用1~4字节。5注意NULL值每个NULL在行头中占用1个位bit但过多NULL不会引起对齐填充问题。6在生产环境修改列顺序需要CREATE TABLE newINSERTRENAME会影响业务应安排在维护窗口并配合pg_repack等工具。9.7 内存对齐与CPU缓存优化延伸数据库的Shared Buffers共享缓冲区中存储的是数据页通常8KB页内每行的紧凑程度决定了相同内存能容纳的行数。更紧凑的行 更高的缓存命中率 更少的磁盘I/O。对于频繁更新的表行因UPDATE会存储旧版本MVCC过度填充可能导致页内死元组更多加速VACUUM压力。因此初始对齐优化也能间接延长VACUUM周期。一句话记大靠前小靠后变长末尾放对齐少浪费查询快几倍。第十章企业最佳实践总结10.1 亿级数据优化的“黄金法则”1先看执行计划再写SQL。任何优化都要基于真实执行计划。2索引是首选武器但不宜过多影响写入。常用过滤、关联字段建索引。3避免标量子查询能用JOIN/聚合代替的就用。4大表按时间分区查询时自动裁剪分区。5复杂查询拆解为多个CTE或临时表分阶段聚合。6物化视图适用于实时性要求不高的报表。7定期维护ANALYZE更新统计信息、REINDEX重建膨胀索引、VACUUM清理死元组。8监控慢查询建立性能基线。10.2 从45秒到1.8秒的启示本案例证明了90%的性能问题源于糟糕的SQL写法。通过系统性的优化索引、分区、查询重写即使亿级数据也能实现亚秒级响应。性能优化不是一蹴而就的而是一个持续迭代、不断改进的过程。记住数据库慢往往不是数据库慢而是你的SQL慢。第十一章 数据开发优化面试指南企业级高频考点本部分以面试问答形式梳理数据库性能优化中最常见的考点和答题要点帮助你在面试中自信应对。11.1 基础概念类Q1请解释一下执行计划如何查看执行计划是数据库为SQL语句选择的“操作路线图”。使用EXPLAIN查看预设计划EXPLAIN (ANALYZE, BUFFERS)查看真实执行情况。关键术语Seq Scan全表扫描、Index Scan索引扫描、Nested Loop小表循环、Hash Join大表哈希连接。优化目标将Seq Scan改为Index Scan将低效连接类型改为高效类型。Q2什么是回表怎么避免回表是指通过索引找到行指针后再去数据页获取完整行的过程。避免方法①建立覆盖索引CREATE INDEX ... INCLUDE让索引包含查询所需全部列②只查询索引中的列Index Only Scan。Q3PostgreSQL中的CONCURRENTLY选项有什么用在创建索引或刷新物化视图时加CONCURRENTLY可以避免锁表允许并发的DML操作。代价是执行时间更长、资源消耗更高。生产环境必用。11.2 索引与存储优化Q4复合索引的列顺序怎么定把等值过滤的列放前面范围过滤、、BETWEEN的列放后面。同时考虑索引覆盖——如果WHERE条件后还有ORDER BY尽量让排序字段也在索引中。Q5什么情况下索引会失效常见场景对索引列使用函数WHERE upper(name) ABC→ 改表达式索引或函数索引。隐式类型转换如WHERE phone 123phone是varchar→ 统一类型。使用!或→ 大部分情况不走索引。LIKE %abc前模糊 → 只有后模糊abc%走索引。数据分布倾斜优化器认为全表扫描更快 → 更新统计信息或强制索引。Q6内存对齐优化是什么能举例吗数据库行存储时CPU要求数据地址是其大小的倍数。不同数据类型对齐要求不同。将对齐要求大的列bigint、timestamp放在前面小的列char(1)、smallint放在后面变长列text放在最后可以减少行内填充字节提升缓存命中率。实测可减少15%~25%表体积。11.3 查询重写优化Q7标量子查询有什么问题怎么改写标量子查询会对每一行执行一次子查询导致复杂度O(N×M)。改写为LEFT JOIN GROUP BY把多行聚合为一行再关联可提升几十倍性能。Q8IN和EXISTS哪个性能好子查询结果集小用IN子查询结果集大用EXISTS因为EXISTS可短路找到即停。NOT IN要注意NULL陷阱子查询包含NULL会导致结果集为空一般用NOT EXISTS代替。Q9大表关联如何优化① 分区裁剪对大表按时间/业务键分区② 使用WITHCTE分阶段聚合减少中间结果集③ 确保关联字段有索引④ 评估改用Hash JoinvsMerge Join⑤ 考虑物化视图预计算。11.4 分区与分片Q10分区表能带来哪些好处分区裁剪查询只扫描相关分区I/O减少。批量管理快速DROP整个分区如删除历史数据。并行扫描可每个分区并行扫描。Q11分区键选什么有哪些注意事项首选时间、区域等范围字段。注意分区键必须是主键的一部分PostgreSQL限制避免分区数过多建议每分区千万级总分区1000定期创建新分区。11.5 数据库参数调优Q12work_mem、shared_buffers分别影响什么怎么设置shared_buffers共享缓存大小通常设为内存的15%~25%。过大会增加维护开销。work_mem单个查询排序、哈希表可用内存。设太小会导致磁盘spill设太大可能耗尽内存多个并发同时消耗。建议从64MB开始测试。Q13ANALYZE和VACUUM的作用ANALYZE更新表统计信息让优化器选择正确计划。大量数据变更后执行。VACUUM清理死元组MVCC留下的过期行回收磁盘空间更新可见性映射。VACUUM FULL会锁表并收缩空间慎用。11.6 实战场景题Q14一张订单表有5亿行每天新增3000万查询最近7天订单需要3秒如何优化到500ms内①分区按日分区查询只扫描7个分区。②索引在分区上建立(order_date, user_id)复合索引。③聚类如果查询主要以时间为范围可CLUSTER表按order_date排序。④物化视图预聚合格子粒度如小时级别。⑤ 考虑列存如果分析型为主或归档历史分区到外部表。Q15线上突然CPU飙高到90%如何定位① 先查pg_stat_activity看活跃查询② 启用pg_stat_statements找出耗时最长的SQL③ 对该SQL执行EXPLAIN (ANALYZE, BUFFERS)分析执行计划④ 常见原因索引失效、全表扫描、Nested Loop笛卡尔积、统计信息陈旧⑤ 紧急时可kill该查询pg_terminate_backend。Q16你优化过最慢的一个SQL是什么怎么做的参考答案模板背景订单明细表2亿行查询用户近3个月消费排行榜原SQL使用标量子查询耗时68秒。分析执行计划发现全表扫描重复子查询。优化建立(user_id, order_date)索引将标量子查询改为JOIN GROUP BY再用CTE分阶段聚合。优化后耗时1.2秒提升56倍。收获永远先看执行计划相信数据不猜测。11.7 面试避坑指南错误回答正确思路“加索引就能加速”索引要建在过滤/关联列且要考虑写放大和存储成本。“分区表越多越好”分区过多会增加规划时间通常几百个分区为宜。“ANALYZE会锁表”ANALYZE不会锁表只是读取采样VACUUM FULL才锁表。“调大work_mem一定好”并发连接多时可能耗尽内存导致OOM。“NOT IN与NOT EXISTS一样”NOT IN有NULL陷阱且优化器选择不同。一句话总结面试核心先看执行计划索引覆盖再分区改写子查询统计信息要勤参数调优控内存生产操作加并发。
【AI大数据工程师特训笔记】第13讲：数据库性能手术刀
发布时间：2026/5/30 12:12:01
目录第一章为什么需要数据库性能优化第二章执行计划 — SQL的“导航地图”2.1 什么是执行计划2.2 如何查看执行计划2.3 执行计划中的关键术语零基础版第三章企业案例背景——某电商平台3.1 业务场景3.2 典型业务查询第四章优化前问题诊断4.1 原查询一标量子查询方式4.2 原查询二多表关联方式第五章优化方案实施5.1 索引优化最立竿见影5.2 分区表优化针对时间维度的巨表5.3 查询重写——将标量子查询改为 JOIN GROUP BY5.4 复杂关联查询——CTE分阶段聚合优化后查询二第六章优化效果对比第七章高级优化技巧7.1 物化视图——预计算结果集7.2 参数化查询预编译语句7.3 监控与持续调优第八章知识补充 — 列存表与预编译详解8.1 PostgreSQL中的列式存储8.2 预编译语句原理第九章内存对齐与行存储优化PostgreSQL存储层调优9.1 什么是内存对齐9.2 PostgreSQL中的行存储结构9.3 一个具体的浪费示例9.4 如何检查表的对齐浪费9.5 企业实战案例订单表列顺序优化9.6 优化原则总结9.7 内存对齐与CPU缓存优化延伸第十章企业最佳实践总结10.1 亿级数据优化的“黄金法则”10.2 从45秒到1.8秒的启示第十一章 数据开发优化面试指南企业级高频考点11.1 基础概念类11.2 索引与存储优化11.3 查询重写优化11.4 分区与分片11.5 数据库参数调优11.6 实战场景题11.7 面试避坑指南第一章为什么需要数据库性能优化在企业级应用中数据库往往是最容易成为系统瓶颈的环节。一个未经优化的SQL语句可能从毫秒级响应变成分钟级超时直接影响用户体验和业务收入。尤其是当数据量达到千万、亿级时不合理的查询设计会导致数据库服务器CPU飙高、内存耗尽、磁盘I/O拥堵。性能优化的目标就是用最少的资源、最短的时间返回正确的结果。本章将以一个真实电商平台为背景带你掌握从执行计划分析到索引、分区、查询重写等全套优化手段最终实现20倍以上的性能提升。第二章执行计划 — SQL的“导航地图”2.1 什么是执行计划执行计划是数据库执行SQL语句时所采用的操作步骤和算法的详细描述。就像你开车去一个陌生地点导航会规划出路线高速、国道、小路数据库也会为你的查询选择一条“路径”。理解执行计划是优化的第一步。2.2 如何查看执行计划-- 最基本只显示计划不实际执行 EXPLAIN SELECT * FROM users WHERE age 30; -- 实际执行并显示真实耗时、缓存命中情况推荐 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age 30; -- 更详细的输出包含输出列信息 EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM orders WHERE order_date 2023-01-01;2.3 执行计划中的关键术语零基础版术语含义性能启示Seq Scan全表顺序扫描一行一行读取小表可以接受大表非常慢Index Scan使用B-tree索引快速定位效率高适合过滤条件选择性好Index Only Scan索引中已包含所需所有列无需回表最优速度最快BitmapHeap Scan先通过索引找到数据位置再批量读取适合返回较多行时Nested Loop嵌套循环连接外表每行都要匹配内表适用于小表驱动大表且有索引Hash Join先构建哈希表再匹配适合两张大表等值连接Merge Join先排序再合并适合已排序的数据Sort显式排序操作消耗内存可能溢出到磁盘企业口诀Seq Scan是大忌Index Scan是利器Hash Join大表欢Nested Loop小表迷。第三章企业案例背景——某电商平台3.1 业务场景该电商平台每天产生数千万订单需要分析用户购买行为、区域销售趋势等。核心数据表如下表名记录数说明users3000万用户基本信息orders5000万订单主表order_items2亿订单商品明细products500万商品信息3.2 典型业务查询查询一统计每个城市VIP用户的订单总额、订单次数、平均订单金额按总额降序取前100名。查询二分析一线城市北上广深的活跃用户注册超2年统计其总消费、购买商品种类数、最后一次购买时间等并筛选出消费≥1000元且订单数≥3的用户。这两个查询在生产环境执行时间分别为45秒和68秒用户无法接受。下面我们一步步优化。第四章优化前问题诊断4.1 原查询一标量子查询方式SELECT u.city, u.vip_level, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.user_id) AS order_count, (SELECT SUM(total_amount) FROM orders o WHERE o.user_id u.user_id) AS total_amount, (SELECT AVG(total_amount) FROM orders o WHERE o.user_id u.user_id) AS avg_amount FROM users u WHERE u.vip_level 2 AND u.registration_date 2022-01-01 ORDER BY total_amount DESC LIMIT 100;执行计划分析每个用户会执行3次标量子查询COUNT、SUM、AVG导致子查询被重复执行数百万次。orders表上没有user_id索引导致每次子查询都全表扫描。总执行时间45.2秒。4.2 原查询二多表关联方式SELECT u.user_id, u.username, u.city, u.vip_level, COUNT(DISTINCT o.order_id) AS order_count, SUM(o.total_amount) AS total_spent, COUNT(oi.item_id) AS item_count, COUNT(DISTINCT oi.product_id) AS unique_products, AVG(oi.price * oi.quantity) AS avg_item_value, MAX(o.order_date) AS last_order_date FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.status completed AND o.order_date 2023-01-01 LEFT JOIN order_items oi ON o.order_id oi.order_id LEFT JOIN products p ON oi.product_id p.product_id WHERE u.registration_date 2020-01-01 AND u.city IN (北京,上海,广州,深圳) AND (p.category_id BETWEEN 1 AND 10 OR p.category_id IS NULL) GROUP BY u.user_id, u.username, u.city, u.vip_level HAVING COUNT(DISTINCT o.order_id) 3 AND SUM(o.total_amount) 1000 ORDER BY total_spent DESC LIMIT 50;执行计划暴露的问题所有表users、orders、order_items、products都使用全表扫描Seq Scan。orders表与order_items关联时缺乏索引导致Nested Loop灾难2亿行×5000万行天文数字。GROUP BY之前已产生巨大的中间结果集内存不足以排序大量使用临时磁盘文件。总执行时间68.7秒。第五章优化方案实施5.1 索引优化最立竿见影-- 用户表复合索引覆盖过滤和排序 CREATE INDEX CONCURRENTLY idx_users_vip_city ON users(vip_level, city, registration_date); -- 订单表关键连接字段 CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); CREATE INDEX CONCURRENTLY idx_orders_user_status_date ON orders(user_id, status, order_date); CREATE INDEX CONCURRENTLY idx_orders_date_status ON orders(order_date, status); -- 订单详情表 CREATE INDEX CONCURRENTLY idx_order_items_order_id ON order_items(order_id); CREATE INDEX CONCURRENTLY idx_order_items_product_id ON order_items(product_id); CREATE INDEX CONCURRENTLY idx_order_items_composite ON order_items(order_id, product_id, price, quantity); -- 商品表 CREATE INDEX CONCURRENTLY idx_products_category ON products(category_id);CONCURRENTLY选项允许在不阻塞读写的情况下创建索引适合生产环境。5.2 分区表优化针对时间维度的巨表将orders表按order_date进行范围分区每年一个分区。这样查询特定年份的数据时只扫描对应分区避免全表。-- 创建分区主表 CREATE TABLE orders_partitioned (LIKE orders INCLUDING DEFAULTS) PARTITION BY RANGE (order_date); -- 创建年度分区 CREATE TABLE orders_2020 PARTITION OF orders_partitioned FOR VALUES FROM (2020-01-01) TO (2021-01-01); CREATE TABLE orders_2021 PARTITION OF orders_partitioned FOR VALUES FROM (2021-01-01) TO (2022-01-01); CREATE TABLE orders_2022 PARTITION OF orders_partitioned FOR VALUES FROM (2022-01-01) TO (2023-01-01); CREATE TABLE orders_2023 PARTITION OF orders_partitioned FOR VALUES FROM (2023-01-01) TO (2024-01-01); -- 迁移数据并建立本地索引 INSERT INTO orders_partitioned SELECT * FROM orders WHERE order_date 2020-01-01; CREATE INDEX idx_orders_part_user_status ON orders_partitioned(user_id, status);5.3 查询重写——将标量子查询改为 JOIN GROUP BY优化后查询一SELECT u.city, u.vip_level, COUNT(o.order_id) AS order_count, COALESCE(SUM(o.total_amount), 0) AS total_amount, COALESCE(AVG(o.total_amount), 0) AS avg_amount FROM users u LEFT JOIN orders_partitioned o ON u.user_id o.user_id AND o.order_date 2022-01-01 WHERE u.vip_level 2 AND u.registration_date 2022-01-01 GROUP BY u.user_id, u.city, u.vip_level ORDER BY total_amount DESC LIMIT 100;改进点一次LEFT JOIN完成所有聚合避免重复子查询。利用分区裁剪只扫描2022年后的订单分区。执行时间从45秒降至1.8秒。5.4 复杂关联查询——CTE分阶段聚合优化后查询二WITH user_orders AS ( SELECT u.user_id, u.username, u.city, u.vip_level, COUNT(o.order_id) AS order_count, SUM(o.total_amount) AS total_spent, MAX(o.order_date) AS last_order_date FROM users u INNER JOIN orders_partitioned o ON u.user_id o.user_id WHERE u.registration_date 2020-01-01 AND u.city IN (北京,上海,广州,深圳) AND o.status completed AND o.order_date 2023-01-01 GROUP BY u.user_id, u.username, u.city, u.vip_level HAVING COUNT(o.order_id) 3 AND SUM(o.total_amount) 1000 ), order_items_agg AS ( SELECT oi.order_id, COUNT(oi.item_id) AS item_count, COUNT(DISTINCT oi.product_id) AS unique_products, AVG(oi.price * oi.quantity) AS avg_item_value FROM order_items oi INNER JOIN products p ON oi.product_id p.product_id WHERE p.category_id BETWEEN 1 AND 10 GROUP BY oi.order_id ) SELECT uo.*, COALESCE(oia.item_count, 0) AS total_items, COALESCE(oia.unique_products, 0) AS total_unique_products, COALESCE(oia.avg_item_value, 0) AS overall_avg_item_value FROM user_orders uo LEFT JOIN orders_partitioned o ON uo.user_id o.user_id LEFT JOIN order_items_agg oia ON o.order_id oia.order_id GROUP BY uo.user_id, uo.username, uo.city, uo.vip_level, uo.order_count, uo.total_spent, uo.last_order_date ORDER BY uo.total_spent DESC LIMIT 50;改进点将多表大连接拆分为两个CTE分别聚合后再关联大幅减少中间结果集。利用分区表索引快速过滤。执行时间从68.7秒降至3.2秒。第六章优化效果对比指标优化前优化后提升倍数查询一执行时间45.2秒1.8秒25倍查询二执行时间68.7秒3.2秒21倍内存使用8GB1.2GB85%↓磁盘I/O极高显著降低大量减少执行计划操作Seq Scan 多次子查询Index Scan Hash Join质的飞跃第七章高级优化技巧7.1 物化视图——预计算结果集对于统计报表类查询可以将聚合结果预先存储为物化视图并定期刷新。CREATE MATERIALIZED VIEW mv_user_order_stats AS SELECT u.user_id, u.city, u.vip_level, COUNT(o.order_id) AS total_orders, SUM(o.total_amount) AS total_spent, AVG(o.total_amount) AS avg_order_value FROM users u LEFT JOIN orders_partitioned o ON u.user_id o.user_id AND o.status completed GROUP BY u.user_id, u.city, u.vip_level; -- 创建索引加速查询 CREATE INDEX idx_mv_city_spent ON mv_user_order_stats(city, total_spent DESC); -- 并发刷新不阻塞查询 REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_order_stats;7.2 参数化查询预编译语句在应用层多次执行相同结构的查询时使用预编译语句可避免重复解析SQL降低CPU开销。PREPARE user_order_analysis (date, text) AS SELECT city, vip_level, COUNT(*) AS user_count, SUM(total_amount) AS revenue FROM users u JOIN orders_partitioned o ON u.user_id o.user_id WHERE u.registration_date $1 AND u.city $2 AND o.status completed GROUP BY city, vip_level; EXECUTE user_order_analysis(2022-01-01, 北京);7.3 监控与持续调优-- 查找慢查询需要pg_stat_statements扩展 SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements WHERE mean_exec_time 1000 -- 超过1秒 ORDER BY mean_exec_time DESC LIMIT 10; -- 检查索引使用率 SELECT schemaname, tablename, indexname, idx_scan, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan;第八章知识补充 — 列存表与预编译详解8.1 PostgreSQL中的列式存储PostgreSQL原生是行存储但可以通过扩展实现列存适用于分析型查询大量聚合、少选列。使用cstore_fdw扩展开源-- 安装后创建外部服务器 CREATE EXTENSION cstore_fdw; CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw; -- 创建列存表 CREATE FOREIGN TABLE sales_columnar ( sale_date date, product_id int, amount numeric ) SERVER cstore_server OPTIONS (compression lz4);注意列存表不支持更新/删除也不支持索引适合只读归档或ETL中间层。8.2 预编译语句原理预编译语句Prepared Statement将SQL语句的解析、规划与参数执行分离。首次执行时数据库生成执行计划并缓存后续执行只替换参数跳过解析阶段。在高并发、重复SQL场景下可减少10%~30%的CPU消耗。第九章内存对齐与行存储优化PostgreSQL存储层调优9.1 什么是内存对齐内存对齐Memory Alignment是计算机系统存储数据的一种方式它要求数据的起始地址必须是其自身大小的整数倍。例如4字节的int类型通常需要存储在地址为4的倍数的位置。这样做可以让CPU单次指令读取完整数据避免跨缓存行访问从而提升性能。在数据库领域内存对齐会影响行存储的紧凑性和CPU缓存效率。PostgreSQL虽然会自动处理基本对齐但列的声明顺序会影响每行记录的实际磁盘占用和内存占用。一个设计不良的表结构可能因填充字节padding浪费20%~30%的空间进而导致更多的磁盘I/O读取更多数据页更低的缓存命中率更慢的索引扫描和顺序扫描9.2 PostgreSQL中的行存储结构PostgreSQL每行数据包含固定长度的头信息23字节对齐到8字节然后依次存储各列的值。每个列的对齐规则由其数据类型决定数据类型典型长度对齐要求char(1)1字节1字节smallint(int2)2字节2字节int(int4)4字节4字节bigint(int8)8字节8字节float44字节4字节float88字节8字节timestamp8字节8字节varchar/text变长通常1字节对齐但指针引用当不同对齐要求的列混合时PostgreSQL会在列之间自动插入填充字节以满足下一个列的对齐要求这些填充字节不存储任何业务数据但占用磁盘和内存。9.3 一个具体的浪费示例假设有下表未优化列顺序CREATE TABLE bad_alignment ( flag char(1), -- 1字节 id bigint, -- 8字节需要8字节对齐 score int, -- 4字节 note text, status smallint -- 2字节 );存储布局简化行头23字节 填充到8字节边界 24字节flag占用1字节但下一个id需要8字节对齐因此后面插入7字节填充 → 实际占8字节id8字节score4字节下一个status需要2字节对齐仅需填充0字节注意后续text是变长通常不要求强对齐但分数后仍可能产生2字节填充若text起始需要4字节边界实际变长字段引用指针为8字节但存储位置较复杂。实际通过pg_class查询表大小会发现每一行浪费约15~20%的空间。优化后列顺序按对齐大小降序CREATE TABLE good_alignment ( id bigint, -- 8字节 score int, -- 4字节 status smallint, -- 2字节 flag char(1), -- 1字节后面填充1字节到2字节边界但总行大小已被优化 note text );原则将对齐要求大的列放在前面小对齐的列放在后面可以最小化填充字节让行更紧凑。9.4 如何检查表的对齐浪费PostgreSQL没有内置函数直接显示填充浪费但可以通过比较表实际大小与理论最小大小来估算。-- 计算理论最小行大小根据列类型 SELECT pg_column_size(ROW( -- 填入各列的典型值注意变长字段需填入实际长度 1::bigint, 1::int, 1::smallint, a::char(1), )) AS min_row_size; -- 查看实际平均行大小 SELECT avg(pg_column_size(t)) FROM (SELECT * FROM good_alignment LIMIT 1000) t;此外使用pageinspect扩展可以更直观地查看页内行布局。9.5 企业实战案例订单表列顺序优化某电商平台的订单表orders原有列顺序简化order_id (bigint), user_id (bigint), amount (numeric), status (varchar), created_at (timestamp), is_deleted (smallint), flag (char(1))该表有20亿行数据量约3.5TB。通过调整列顺序将is_deleted和flag移动到所有定长数值列之后并重新建表CREATE TABLE new AS ... ORDER BY ...新表占用量减少22%全表扫描速度提升18%缓存效率提高。9.6 优化原则总结1优先将固定长度定长且对齐要求大的列放在前面如bigint、double、timestamp。2将对齐要求小的列如char(1)、boolean放在后面。3变长列varchar、text、bytea通常放在最后因为它们不参与对齐填充链。4避免大量char(n)定长字符串尽量使用varchar虽然varchar在PG中存储开销类似text但有长度限制会额外占用1~4字节。5注意NULL值每个NULL在行头中占用1个位bit但过多NULL不会引起对齐填充问题。6在生产环境修改列顺序需要CREATE TABLE newINSERTRENAME会影响业务应安排在维护窗口并配合pg_repack等工具。9.7 内存对齐与CPU缓存优化延伸数据库的Shared Buffers共享缓冲区中存储的是数据页通常8KB页内每行的紧凑程度决定了相同内存能容纳的行数。更紧凑的行 更高的缓存命中率 更少的磁盘I/O。对于频繁更新的表行因UPDATE会存储旧版本MVCC过度填充可能导致页内死元组更多加速VACUUM压力。因此初始对齐优化也能间接延长VACUUM周期。一句话记大靠前小靠后变长末尾放对齐少浪费查询快几倍。第十章企业最佳实践总结10.1 亿级数据优化的“黄金法则”1先看执行计划再写SQL。任何优化都要基于真实执行计划。2索引是首选武器但不宜过多影响写入。常用过滤、关联字段建索引。3避免标量子查询能用JOIN/聚合代替的就用。4大表按时间分区查询时自动裁剪分区。5复杂查询拆解为多个CTE或临时表分阶段聚合。6物化视图适用于实时性要求不高的报表。7定期维护ANALYZE更新统计信息、REINDEX重建膨胀索引、VACUUM清理死元组。8监控慢查询建立性能基线。10.2 从45秒到1.8秒的启示本案例证明了90%的性能问题源于糟糕的SQL写法。通过系统性的优化索引、分区、查询重写即使亿级数据也能实现亚秒级响应。性能优化不是一蹴而就的而是一个持续迭代、不断改进的过程。记住数据库慢往往不是数据库慢而是你的SQL慢。第十一章 数据开发优化面试指南企业级高频考点本部分以面试问答形式梳理数据库性能优化中最常见的考点和答题要点帮助你在面试中自信应对。11.1 基础概念类Q1请解释一下执行计划如何查看执行计划是数据库为SQL语句选择的“操作路线图”。使用EXPLAIN查看预设计划EXPLAIN (ANALYZE, BUFFERS)查看真实执行情况。关键术语Seq Scan全表扫描、Index Scan索引扫描、Nested Loop小表循环、Hash Join大表哈希连接。优化目标将Seq Scan改为Index Scan将低效连接类型改为高效类型。Q2什么是回表怎么避免回表是指通过索引找到行指针后再去数据页获取完整行的过程。避免方法①建立覆盖索引CREATE INDEX ... INCLUDE让索引包含查询所需全部列②只查询索引中的列Index Only Scan。Q3PostgreSQL中的CONCURRENTLY选项有什么用在创建索引或刷新物化视图时加CONCURRENTLY可以避免锁表允许并发的DML操作。代价是执行时间更长、资源消耗更高。生产环境必用。11.2 索引与存储优化Q4复合索引的列顺序怎么定把等值过滤的列放前面范围过滤、、BETWEEN的列放后面。同时考虑索引覆盖——如果WHERE条件后还有ORDER BY尽量让排序字段也在索引中。Q5什么情况下索引会失效常见场景对索引列使用函数WHERE upper(name) ABC→ 改表达式索引或函数索引。隐式类型转换如WHERE phone 123phone是varchar→ 统一类型。使用!或→ 大部分情况不走索引。LIKE %abc前模糊 → 只有后模糊abc%走索引。数据分布倾斜优化器认为全表扫描更快 → 更新统计信息或强制索引。Q6内存对齐优化是什么能举例吗数据库行存储时CPU要求数据地址是其大小的倍数。不同数据类型对齐要求不同。将对齐要求大的列bigint、timestamp放在前面小的列char(1)、smallint放在后面变长列text放在最后可以减少行内填充字节提升缓存命中率。实测可减少15%~25%表体积。11.3 查询重写优化Q7标量子查询有什么问题怎么改写标量子查询会对每一行执行一次子查询导致复杂度O(N×M)。改写为LEFT JOIN GROUP BY把多行聚合为一行再关联可提升几十倍性能。Q8IN和EXISTS哪个性能好子查询结果集小用IN子查询结果集大用EXISTS因为EXISTS可短路找到即停。NOT IN要注意NULL陷阱子查询包含NULL会导致结果集为空一般用NOT EXISTS代替。Q9大表关联如何优化① 分区裁剪对大表按时间/业务键分区② 使用WITHCTE分阶段聚合减少中间结果集③ 确保关联字段有索引④ 评估改用Hash JoinvsMerge Join⑤ 考虑物化视图预计算。11.4 分区与分片Q10分区表能带来哪些好处分区裁剪查询只扫描相关分区I/O减少。批量管理快速DROP整个分区如删除历史数据。并行扫描可每个分区并行扫描。Q11分区键选什么有哪些注意事项首选时间、区域等范围字段。注意分区键必须是主键的一部分PostgreSQL限制避免分区数过多建议每分区千万级总分区1000定期创建新分区。11.5 数据库参数调优Q12work_mem、shared_buffers分别影响什么怎么设置shared_buffers共享缓存大小通常设为内存的15%~25%。过大会增加维护开销。work_mem单个查询排序、哈希表可用内存。设太小会导致磁盘spill设太大可能耗尽内存多个并发同时消耗。建议从64MB开始测试。Q13ANALYZE和VACUUM的作用ANALYZE更新表统计信息让优化器选择正确计划。大量数据变更后执行。VACUUM清理死元组MVCC留下的过期行回收磁盘空间更新可见性映射。VACUUM FULL会锁表并收缩空间慎用。11.6 实战场景题Q14一张订单表有5亿行每天新增3000万查询最近7天订单需要3秒如何优化到500ms内①分区按日分区查询只扫描7个分区。②索引在分区上建立(order_date, user_id)复合索引。③聚类如果查询主要以时间为范围可CLUSTER表按order_date排序。④物化视图预聚合格子粒度如小时级别。⑤ 考虑列存如果分析型为主或归档历史分区到外部表。Q15线上突然CPU飙高到90%如何定位① 先查pg_stat_activity看活跃查询② 启用pg_stat_statements找出耗时最长的SQL③ 对该SQL执行EXPLAIN (ANALYZE, BUFFERS)分析执行计划④ 常见原因索引失效、全表扫描、Nested Loop笛卡尔积、统计信息陈旧⑤ 紧急时可kill该查询pg_terminate_backend。Q16你优化过最慢的一个SQL是什么怎么做的参考答案模板背景订单明细表2亿行查询用户近3个月消费排行榜原SQL使用标量子查询耗时68秒。分析执行计划发现全表扫描重复子查询。优化建立(user_id, order_date)索引将标量子查询改为JOIN GROUP BY再用CTE分阶段聚合。优化后耗时1.2秒提升56倍。收获永远先看执行计划相信数据不猜测。11.7 面试避坑指南错误回答正确思路“加索引就能加速”索引要建在过滤/关联列且要考虑写放大和存储成本。“分区表越多越好”分区过多会增加规划时间通常几百个分区为宜。“ANALYZE会锁表”ANALYZE不会锁表只是读取采样VACUUM FULL才锁表。“调大work_mem一定好”并发连接多时可能耗尽内存导致OOM。“NOT IN与NOT EXISTS一样”NOT IN有NULL陷阱且优化器选择不同。一句话总结面试核心先看执行计划索引覆盖再分区改写子查询统计信息要勤参数调优控内存生产操作加并发。
相关文章
低成本仿生机械手DIY：基于Arduino与舵机的完整制作教程
1. 项目概述：从灵感到可动的机械之手几年前，我在一个机器人展会上第一次看到仿生机械手，它流畅地抓取水杯、捏起硬币，那种精准与优雅让我着迷。但一看价格标签，动辄数万甚至数十万，瞬间觉得这技术离普通人太…
Arduino旋转炮台：从电位器到舵机的机电一体化控制实践
1. 项目概述与核心思路做嵌入式开发或者机电控制项目，最让人兴奋的莫过于看着自己写的代码，通过几根电线，让现实世界里的机械结构“活”起来。今天分享的这个基于Arduino的旋转炮台项目，就是一个非常典型的入门级机电一体化实践。…
Selenium4相对定位器：告别脆弱XPath！用它搞定动态表单和复杂布局（保姆级避坑指南）
Selenium4相对定位器：告别脆弱XPath！用它搞定动态表单和复杂布局（保姆级避坑指南）在自动化测试的世界里，UI元素的定位一直是工程师们最头疼的问题之一。那些精心编写的测试脚本，往往因为前端一个小小的布局…
当你被文档下载限制卡住时，这个工具能做什么？
当你被文档下载限制卡住时，这个工具能做什么？ 【免费下载链接】kill-doc 看到经常有小伙伴们需要下载一些免费文档，但是相关网站浏览体验不好各种广告，各种登录验证，需要很多步骤才能下载文档，该脚本就是为…
IPD价值量化与商业闭环（2）：研发投入高、回报低？ IPD重塑企业毛利与ROI的增长新格局
许多企业在引入 IPD 时，往往只关注流程和组织的变革，而忽视了其对财务表现的深层影响机制，导致无法准确评估 IPD 的投资回报，也难以通过财务指标来指导 IPD 体系的持续优化。研发投入、毛利率和 ROI 是衡量企业研发经营效率和效果…
Arduino入门教程十九｜双74HC164级联拓展15路LED输出【流畅往返流水灯+奇偶交替闪烁】（零IO浪费）
我整理了一套Arduino 零基础 从入门到高级 完整系统课程，包含视频讲解、全套源码、接线图纸、库文件、ESP32/ESP32-S3 摄像头 & 物联网实战项目，循序渐进，新手也能零基础吃透。需要系统学习可以查看我主页专属课程（零基础保姆级Arduino教程从入门到实战_在线视频教程-C…
Go语言配置管理与密钥安全
Go语言配置管理与密钥安全 配置管理和密钥安全是应用开发中的重要环节。本文将深入探讨如何使用Go语言实现安全、灵活的配置管理方案。一、配置管理概述 配置管理涉及应用运行时参数的管理，包括： 配置来源：环境变量、配置文件、命令行参数配置…
GBA掌机IPS屏幕改装与LED灯条加装全攻略
1. 项目概述与改装价值手头有一台老旧的Gameboy Advance，屏幕暗淡、色彩发灰，在光线稍暗的环境下几乎没法玩。这大概是所有GBA老玩家的共同痛点。原装屏幕采用的是无背光的反射式液晶，玩起来得找个好角度对着光源，体验实在说不上好…
TSO内存模型：多核处理器中的性能与正确性平衡
1. TSO内存模型的核心挑战与设计哲学在现代多处理器架构中，内存模型是硬件与软件之间的关键契约。Total Store Order (TSO) 作为最广泛应用的宽松内存模型之一（如Intel x86/x64架构），其设计源于对性能与正确性的平衡考量。让我们从…
Win11/Win10深度学习环境搭建：实测PyCharm远程连接WSL2下的CUDA，性能比虚拟机强多少？
Win11/Win10深度学习环境终极对决：WSL2 CUDA vs 虚拟机 vs 双系统实测指南当开发者需要在Windows系统上进行深度学习开发时，通常会面临三种选择：虚拟机方案、双系统方案和WSL2方案。本文将基于实际测试数据，从GPU性能、开发便利性…
SketchUp STL插件终极指南：3D打印工作流完全掌握
SketchUp STL插件终极指南：3D打印工作流完全掌握 【免费下载链接】sketchup-stl A SketchUp Ruby Extension that adds STL (STereoLithography) file format import and export. 项目地址: https://gitcode.com/gh_mirrors/sk/sketchup-stl SketchUp STL插件…
基于ICL8038的多波形信号发生器：从原理到制作的完整指南
1. 项目概述：从零构建一个基于ICL8038的多波形信号发生器在电子实验、设备调试乃至生物医学信号处理领域，一个稳定可靠、波形纯净的信号源是不可或缺的“心脏”。无论是用于测试放大器的频率响应，还是模拟生理电信号进行算法研究，…
施工现场安全事故预警准确率达94.6%？——解密某央企AI Agent边缘计算部署架构与3个月落地实录
更多请点击： https://codechina.net 第一章：施工现场安全事故预警准确率达94.6%？——解密某央企AI Agent边缘计算部署架构与3个月落地实录 在华北某大型地铁盾构施工现场，一套轻量化AI Agent系统于2024年Q2完成全栈部署ÿ…
附录 B：术语表
本术语表面向“从 MM 到 HMM”专栏阅读过程中的快速查阅。它不是内核 API 手册，而是把文章中反复出现的概念放到同一张地图上：先给出直观含义，再说明它在 Linux MM/HMM 语境里的作用。建议阅读方式： 初读专栏时，把它当…
Midjourney渐变美学的神经渲染原理（附RGB-HSV-LCH三空间渐变映射对照表·行业首曝）
更多请点击： https://kaifayun.com 第一章：Midjourney渐变美学的神经渲染原理（附RGB-HSV-LCH三空间渐变映射对照表行业首曝） Midjourney 的渐变美学并非传统插值实现，而是由其隐式神经渲染器（Implicit Neu…
MPC-BE：基于DirectShow架构的专业级开源媒体播放解决方案
MPC-BE：基于DirectShow架构的专业级开源媒体播放解决方案 【免费下载链接】MPC-BE MPC-BE – универсальный проигрыватель аудио и видеофайлов для операционной системы Windows. 项目地址:…
如何快速计算3D模型体积和重量：STL-Volume-Model-Calculator终极指南
如何快速计算3D模型体积和重量：STL-Volume-Model-Calculator终极指南 【免费下载链接】STL-Volume-Model-Calculator STL Volume Model Calculator Python 项目地址: https://gitcode.com/gh_mirrors/st/STL-Volume-Model-Calculator 你是否曾经为3D打印项目…
通过Taotoken CLI工具一键配置团队开发环境与模型密钥
通过Taotoken CLI工具一键配置团队开发环境与模型密钥 1. CLI工具安装与基本使用 Taotoken提供的CLI工具可通过npm全局安装或直接使用npx运行。对于需要频繁使用CLI的团队，推荐全局安装： npm install -g taotoken/taotoken对于临时使用或项目级配置&a…