企业官网建设流程全解析

入侵织梦网站后台,鞋材东莞网站建设,网站有备案号,深圳石岩做网站PostgreSQL架构与索引深度剖析 前言 你维护了一个电商网站#xff0c;需要存储千万级商品数据#xff0c;你选了 MySQL。但随着业务发展#xff0c;产品经理提了各种需求#xff1a;地理位置搜索、全文检索、JSON 文档存储、时序数据分析…于是你引入了 Redis、Elasticse…PostgreSQL架构与索引深度剖析前言你维护了一个电商网站需要存储千万级商品数据你选了 MySQL。但随着业务发展产品经理提了各种需求地理位置搜索、全文检索、JSON 文档存储、时序数据分析…于是你引入了 Redis、Elasticsearch、MongoDB、InfluxDB原本简单的单体架构变成了复杂的分布式系统。有没有一个数据库能原生支持这些需求有它就是 PostgreSQL。个人主页你的主页文章目录PostgreSQL架构与索引深度剖析一、PostgreSQL是什么二、存储架构详解三、索引类型全解析四、进程架构详解五、内存架构详解六、WAL日志机制七、查询执行流程八、PostgreSQL vs MySQL 深度对比九、总结一、PostgreSQL是什么1.1 一句话定义PostgreSQL简称 PG是一个功能最强大的开源关系型数据库被称为数据库天花板。1.2 为什么说它是天花板PG 不仅能完全替代 MySQL在很多场景下还能替代场景传统方案PG 替代方案关系型存储MySQLPG 原生支持全文搜索ElasticsearchPG GIN 索引文档存储MongoDBPG JSONB地理信息PostGIS/Redis GeoHashPG GiST 索引时序数据InfluxDBPG BRIN 索引向量搜索Milvus/PineconePG pgvector 插件一个数据库多种索引无限扩展。1.3 国产数据库的祖师爷PG 完全开源可商用BSD 协议国内很多自研数据库都有 PG 的影子阿里云 PolarDB兼容 PG腾讯云 TDSQL-PG华为 GaussDB基于 PG人大金仓 KingbaseES基于 PG瀚高数据库基于 PG可以说PG 养活了国内大半数据库团队。二、存储架构详解2.1 数据组织方式PG 将数据组织成类似 Excel 表的结构数据库Database └── 模式Schema └── 表Table └── 行Tuple与 MySQL 的区别概念MySQLPostgreSQL数据库DatabaseDatabase模式不支持或等同于 DatabaseSchema一个 Database 可有多个 Schema表TableTable/Relation行RowTuplePG 多了一层 Schema可以在同一个数据库内实现更好的逻辑隔离。2.2 堆表文件数据表在磁盘上以**堆表文件Heap File**的形式存储$PGDATA/base/database_oid/relfilenodedatabase_oid数据库的 OIDrelfilenode表的文件节点号举个例子-- 查看表的存储位置SELECTpg_relation_filepath(products);-- 结果base/16384/163852.3 数据页结构当堆表文件超过1GB时会自动拆分成多个文件16385、16385.1、16385.2…。每个文件由多个8KB的数据页组成┌─────────────────────────────────────────┐ │ Page Header │ ← 24 字节页面元信息 ├─────────────────────────────────────────┤ │ Item Pointers │ ← 行指针数组指向实际数据 │ (Line Pointers) │ ├─────────────────────────────────────────┤ │ │ │ Free Space │ ← 空闲空间 │ │ ├─────────────────────────────────────────┤ │ Tuples │ ← 实际行数据从页尾向前增长 │ (Row Data) │ ├─────────────────────────────────────────┤ │ Special Space │ ← 特殊空间索引页使用 └─────────────────────────────────────────┘与 MySQL InnoDB 的区别特性MySQL InnoDBPostgreSQL页大小16KB默认8KB默认行存储方式聚簇索引数据存在主键索引叶子节点堆表数据和索引分离主键要求必须有主键没有会自动生成可以没有主键2.4 MVCC 实现差异PG 和 MySQL 都支持 MVCC多版本并发控制但实现方式完全不同。MySQL InnoDB 的 MVCC原始数据行 → Undo Log存储旧版本更新时旧版本数据写入 Undo Log通过 Undo Log 链回溯历史版本Undo Log 会定期清理PurgePostgreSQL 的 MVCC原始数据行标记为删除 新数据行插入更新时不修改原数据而是插入一条新数据原数据标记xmax删除事务ID新数据标记xmin创建事务ID旧版本数据由 VACUUM 进程清理打个比方MySQL像用橡皮擦掉旧内容写上新内容旧内容存在草稿纸上PG像在新的一行写新内容旧的那行划掉但还在各自的优缺点特性MySQLPostgreSQL更新性能原地更新较快插入新行较慢读取性能需要回溯 Undo Log直接读取较快空间占用Undo Log 可能膨胀表可能膨胀需要 VACUUM回滚速度需要应用 Undo Log直接丢弃新行很快三、索引类型全解析PG 之所以全能核心在于它支持多种索引类型且架构上支持扩展自定义索引。3.1 B-Tree 索引默认这是最常用的索引类型适用于等值查询和范围查询。CREATEINDEXidx_product_priceONproducts(price);-- 等值查询SELECT*FROMproductsWHEREprice99;-- 范围查询SELECT*FROMproductsWHEREpriceBETWEEN50AND100;PG 的 B-Link Tree vs MySQL 的 BTreeMySQL 使用标准 BTree[30|60] ← 非叶子节点 / | \ [10|20] [40|50] [70|80] ← 叶子节点双向链表PG 使用 B-Link TreeLehman-Yao 算法[30|60] → ← 非叶子节点也有右指针 / | \ [10|20]→[40|50]→[70|80] ← 叶子节点单向链表B-Link Tree 的优势非叶子节点有右指针页分裂时不需要锁整棵树并发性能更好特别是高并发写入场景3.2 GIN 索引全文搜索GINGeneralized Inverted Index通用倒排索引适用于全文搜索和数组查询。原理将文本分词建立词元 → 文档ID的映射。文档1: PostgreSQL 是最强数据库 文档2: MySQL 也是数据库 文档3: PostgreSQL 支持全文搜索 分词后建立倒排索引 ┌──────────────┬─────────────┐ │ 词元 │ 文档ID │ ├──────────────┼─────────────┤ │ PostgreSQL │ 1, 3 │ │ MySQL │ 2 │ │ 数据库 │ 1, 2 │ │ 全文搜索 │ 3 │ └──────────────┴─────────────┘使用示例-- 创建全文搜索索引CREATEINDEXidx_product_name_ginONproductsUSINGgin(to_tsvector(chinese,name));-- 全文搜索SELECT*FROMproductsWHEREto_tsvector(chinese,name) to_tsquery(chinese,蓝牙 耳机);GIN JSONB替代 MongoDB-- 创建 JSONB 列的 GIN 索引CREATEINDEXidx_product_attrsONproductsUSINGgin(attributes jsonb_path_ops);-- 查询 JSON 中包含特定键值对的记录SELECT*FROMproductsWHEREattributes {color: blue, size: L};JSONB 是 PG 优化后的二进制 JSON 格式配合 GIN 索引百万级文档也能毫秒级查询。3.3 GiST 索引地理信息GiSTGeneralized Search Tree通用搜索树是一个索引框架支持多维数据。R-Tree 原理一维数据用线段表示范围二维数据用矩形表示范围。┌─────────────────────────┐ │ 根节点矩形 │ └───────────┬─────────────┘ ┌─────┴─────┐ ↓ ↓ ┌─────────┐ ┌─────────┐ │ 矩形A │ │ 矩形B │ └────┬────┘ └────┬────┘ ↓ ↓ [点1,点2] [点3,点4]查询附近 1 公里的店铺时计算查询点周围 1 公里的矩形范围从根节点开始找与查询矩形相交的子矩形递归向下最终找到叶子节点的坐标点使用示例-- 启用 PostGIS 扩展CREATEEXTENSION postgis;-- 创建地理位置列和索引ALTERTABLEstoresADDCOLUMNlocationgeometry(Point,4326);CREATEINDEXidx_store_locationONstoresUSINGgist(location);-- 查询上海市中心 1 公里内的店铺SELECTname,ST_Distance(location,ST_SetSRID(ST_MakePoint(121.4737,31.2304),4326))asdistanceFROMstoresWHEREST_DWithin(location,ST_SetSRID(ST_MakePoint(121.4737,31.2304),4326),1000-- 1000米)ORDERBYdistance;3.4 BRIN 索引时序数据BRINBlock Range Index块范围索引适用于按时间或 ID 顺序插入的大数据表。原理不为每行数据建索引而是为每批数据页记录范围摘要。┌─────────────────────────────────────────────────┐ │ 页范围 │ 最小时间 │ 最大时间 │ ├────────┼───────────────────┼───────────────────┤ │ 1-128 │ 2025-01-01 00:00 │ 2025-01-15 23:59 │ │ 129-256│ 2025-01-16 00:00 │ 2025-01-31 23:59 │ │ 257-384│ 2025-02-01 00:00 │ 2025-02-15 23:59 │ └────────┴───────────────────┴───────────────────┘查询2025-02-10的数据时直接定位到 257-384 页范围跳过其他页。使用示例-- 创建 BRIN 索引CREATEINDEXidx_logs_created_atONlogsUSINGbrin(created_at);-- 查询某天的日志SELECT*FROMlogsWHEREcreated_atBETWEEN2025-02-10AND2025-02-11;BRIN vs B-Tree 对比特性B-TreeBRIN索引大小大每行一个条目极小每批页一个条目查询精度精确粗略需要扫描范围内的页适用场景随机数据顺序插入的数据时间、自增ID维护成本高低打个比方B-Tree 像书的详细目录精确到每一节BRIN 像书的章节目录只告诉你大概在哪个章节3.5 Hash 索引适用于等值查询不支持范围查询。CREATEINDEXidx_product_skuONproductsUSINGhash(sku);-- 只支持等值查询SELECT*FROMproductsWHEREskuSKU-12345;注意PG 10 之前 Hash 索引不记录 WAL崩溃后需要重建。PG 10 已修复。3.6 索引类型总结索引类型适用场景可替代的中间件B-Tree等值、范围查询MySQLGIN全文搜索、JSONB、数组Elasticsearch、MongoDBGiST地理位置、多维数据PostGIS、Redis GeoHashBRIN时序数据、顺序插入InfluxDBHash等值查询-四、进程架构详解4.1 多进程 vs 多线程MySQL多线程架构mysqld 进程 ├── 连接线程1 ├── 连接线程2 ├── 连接线程3 └── 后台线程IO、Purge等PostgreSQL多进程架构Postmaster主进程 ├── Backend Process 1后端进程 ├── Backend Process 2 ├── Backend Process 3 └── Background Workers后台进程各自的优缺点特性MySQL多线程PostgreSQL多进程内存占用低线程共享内存高每个进程独立内存稳定性一个线程崩溃可能影响整个进程一个进程崩溃不影响其他进程并发连接支持更多连接连接数受限需要连接池上下文切换快慢4.2 Postmaster 主进程Postmaster 是 PG 的指挥中心监听客户端连接请求为每个新连接 Fork 出后端进程监控所有子进程的健康状态管理共享内存客户端连接请求 ↓ Postmaster 接收 ↓ Fork 后端进程 ↓ 后端进程处理请求4.3 Backend Process 后端进程每个客户端连接对应一个后端进程也叫 Postgres 进程。# 查看 PG 进程psaux|greppostgres postgres1234postgres: user db127.0.0.1(54321)idle postgres1235postgres: user db127.0.0.1(54322)SELECT postgres1236postgres: user db127.0.0.1(54323)UPDATE waiting问题如果应用有 500 个连接就要创建 500 个进程内存消耗巨大。解决方案使用连接池PgBouncer轻量级连接池推荐Pgpool-II功能更丰富支持负载均衡应用500连接 → PgBouncer连接池 → PostgreSQL50进程4.4 Background Workers 后台进程PG 有多个后台进程负责维护工作进程名职责Background Writer持续将脏页写入磁盘减少 Checkpoint 压力Checkpointer定期将所有脏页强制写入磁盘WAL Writer将 WAL 缓冲区写入磁盘Autovacuum Launcher启动自动 VACUUM 进程Autovacuum Worker执行 VACUUM 清理死元组Stats Collector收集统计信息Logical Replication逻辑复制相关VACUUM 是什么前面说过PG 更新数据时不删除旧行而是标记为死元组。VACUUM 就是清理这些死元组的进程。-- 手动执行 VACUUMVACUUM products;-- VACUUM FULL重建表会锁表VACUUMFULLproducts;-- 查看表的死元组数量SELECTrelname,n_dead_tupFROMpg_stat_user_tables;与 MySQL 的对比特性MySQLPostgreSQL旧版本清理Purge 线程清理 Undo LogVACUUM 进程清理死元组是否需要手动干预一般不需要大表可能需要调优 VACUUM表膨胀问题较少可能发生需要关注五、内存架构详解5.1 共享内存区域所有后端进程共享的内存区域┌─────────────────────────────────────────────────────┐ │ Shared Memory │ ├─────────────────────────────────────────────────────┤ │ ┌─────────────────────────────────────────────┐ │ │ │ Shared Buffer Pool │ │ ← 缓存数据页 │ │ (shared_buffers) │ │ │ └─────────────────────────────────────────────┘ │ │ ┌─────────────────────────────────────────────┐ │ │ │ WAL Buffer │ │ ← WAL 日志缓冲 │ │ (wal_buffers) │ │ │ └─────────────────────────────────────────────┘ │ │ ┌─────────────────────────────────────────────┐ │ │ │ CLOG Buffer │ │ ← 事务状态缓存 │ └─────────────────────────────────────────────┘ │ │ ┌─────────────────────────────────────────────┐ │ │ │ Lock Space / Other │ │ ← 锁信息等 │ └─────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────┘5.2 Shared Buffer Pool这是 PG 最重要的内存区域用于缓存数据页和索引页。-- 查看 shared_buffers 配置SHOWshared_buffers;-- 推荐设置为物理内存的 25%-- postgresql.confshared_buffers4GB工作流程后端进程查询数据 ↓ 检查 Shared Buffer Pool ├── 命中 → 直接返回 └── 未命中 → 从磁盘读取 → 放入 Buffer Pool → 返回与 MySQL Buffer Pool 的区别特性MySQL InnoDB Buffer PoolPostgreSQL Shared Buffers默认大小128MB128MB推荐大小物理内存的 50-80%物理内存的 25%为什么 PG 推荐更小-PG 依赖操作系统的文件缓存5.3 进程私有内存每个后端进程还有自己的私有内存内存区域用途配置参数work_mem排序、哈希操作work_memmaintenance_work_memVACUUM、CREATE INDEXmaintenance_work_memtemp_buffers临时表temp_buffers-- 查看 work_memSHOWwork_mem;-- 默认 4MB-- 大查询可以临时调大SETwork_mem256MB;SELECT*FROMbig_tableORDERBYcolumn1;注意work_mem是每个操作的内存一个查询可能有多个排序操作总内存 work_mem × 操作数 × 连接数。六、WAL日志机制6.1 为什么需要 WAL数据在 Shared Buffer 中更新后如果还没写入磁盘就崩溃了数据就丢了。解决方案Write-Ahead Logging预写日志先写日志再写数据崩溃后通过日志恢复数据6.2 WAL 工作流程1. 事务开始 2. 修改 Shared Buffer 中的数据页 3. 将修改操作写入 WAL Buffer 4. 事务提交时WAL Buffer 刷入磁盘WAL 文件 5. 后台进程异步将脏页刷入磁盘 6. 崩溃恢复时重放 WAL 日志为什么先写 WAL 而不是直接写数据页WAL 是顺序写数据页是随机写顺序写磁盘性能是随机写的几十倍6.3 WAL 文件结构$PGDATA/pg_wal/ ├── 000000010000000000000001 ├── 000000010000000000000002 ├── 000000010000000000000003 └── ...每个 WAL 文件默认16MB。6.4 与 MySQL Redo Log 的对比特性MySQL Redo LogPostgreSQL WAL文件结构固定大小循环写入持续增长归档后删除默认大小48MB2个文件×24MB无上限可配置保留策略用途崩溃恢复崩溃恢复 复制 PITRPITRPoint-In-Time RecoveryPG 可以通过 WAL 归档实现任意时间点恢复# 恢复到指定时间点recovery_target_time2025-02-15 14:30:00七、查询执行流程7.1 完整流程┌─────────────────────────────────────────────────────────────┐ │ Backend Process │ ├─────────────────────────────────────────────────────────────┤ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────────┐ │ │ │ Parser │ → │ Analyzer│ → │ Planner │ → │ Executor │ │ │ │ (解析器) │ │ (分析器) │ │ (优化器) │ │ (执行器) │ │ │ └─────────┘ └─────────┘ └─────────┘ └──────┬──────┘ │ │ ↓ │ │ ┌─────────────────┐│ │ │ Access Methods ││ │ │ (访问方法) ││ │ └────────┬────────┘│ └───────────────────────────────────────────────────┼─────────┘ ↓ ┌──────────────────────────────────┐ │ Shared Memory │ │ ┌────────────┐ ┌────────────┐ │ │ │Buffer Pool │ │ WAL Buffer │ │ │ └────────────┘ └────────────┘ │ └──────────────────────────────────┘ ↓ ┌──────────────────────────────────┐ │ Disk │ │ ┌────────────┐ ┌────────────┐ │ │ │ Data Files │ │ WAL Files │ │ │ └────────────┘ └────────────┘ │ └──────────────────────────────────┘7.2 各模块职责Parser解析器检查 SQL 语法是否正确生成解析树Parse TreeAnalyzer分析器检查表、列是否存在检查权限生成查询树Query TreePlanner/Optimizer优化器选择使用哪个索引选择 Join 方式Nested Loop、Hash Join、Merge Join生成执行计划Plan TreeExecutor执行器按执行计划调用 Access Methods返回结果7.3 查看执行计划EXPLAINANALYZESELECT*FROMproductsWHEREprice100;QUERYPLAN------------------------------------------------------------------------------------------------------------IndexScanusingidx_product_priceonproducts(cost0.29..8.31rows1width100)(actualtime0.015..0.016rows1loops1)IndexCond:(price100)PlanningTime:0.080ms ExecutionTime:0.030ms7.4 Access Methods访问方法Access Methods 是 PG 扩展性的核心它定义了如何访问不同类型的数据。Access Methods ├── Heap AM堆表访问 ├── Index AM索引访问 │ ├── B-Tree │ ├── GIN │ ├── GiST │ ├── BRIN │ └── Hash └── 自定义 AM插件扩展通过开放 Access Methods 接口用户可以扩展支持向量搜索pgvector图数据库Apache AGE列式存储Citus八、PostgreSQL vs MySQL 深度对比8.1 架构对比特性MySQLPostgreSQL进程模型多线程多进程存储引擎可插拔InnoDB、MyISAM等单一堆表索引与数据聚簇索引数据在主键索引中分离堆表 索引MVCC 实现Undo Log多版本元组连接管理线程池需要外部连接池8.2 功能对比功能MySQLPostgreSQLJSON 支持JSON5.7JSONB更强大全文搜索支持较弱原生支持GIN 索引地理信息需要扩展PostGIS业界标准数组类型不支持原生支持自定义类型不支持支持物化视图不支持支持CTEWITH 语句8.0 支持一直支持窗口函数8.0 支持一直支持表继承不支持支持外部数据包装器不支持FDW可查询外部数据源8.3 SQL 标准兼容性PostgreSQL 对 SQL 标准的支持更完整-- PostgreSQL 支持的高级特性-- 1. 数组操作SELECTARRAY[1,2,3]||ARRAY[4,5];-- {1,2,3,4,5}-- 2. JSONB 操作SELECT{name: test}::jsonb-name;-- testSELECT{name: test}::jsonb-name;-- test文本-- 3. 范围类型SELECT[2025-01-01, 2025-12-31]::daterange 2025-06-15::date;-- true-- 4. 递归 CTEWITHRECURSIVE subordinatesAS(SELECTid,name,manager_idFROMemployeesWHEREid1UNIONALLSELECTe.id,e.name,e.manager_idFROMemployees eJOINsubordinates sONe.manager_ids.id)SELECT*FROMsubordinates;-- 5. 窗口函数SELECTname,salary,RANK()OVER(ORDERBYsalaryDESC)asrank,SUM(salary)OVER()astotalFROMemployees;8.4 性能对比读性能场景MySQLPostgreSQL简单主键查询快聚簇索引稍慢需要回表复杂查询一般更好优化器更强全文搜索弱强写性能场景MySQLPostgreSQL单行更新快原地更新稍慢插入新行批量插入快快高并发写入好好B-Link Tree 优势总结简单 CRUDMySQL 略快复杂查询PostgreSQL 更强特殊数据类型PostgreSQL 完胜8.5 运维对比方面MySQLPostgreSQL学习曲线平缓稍陡社区资源非常丰富丰富云服务支持所有云厂商所有云厂商主从复制成熟成熟高可用方案MHA、MGR、OrchestratorPatroni、repmgr需要关注的问题主从延迟VACUUM、表膨胀8.6 如何选择选 MySQL 的场景团队熟悉 MySQL简单的 CRUD 业务对运维复杂度敏感需要大量社区资源和教程选 PostgreSQL 的场景需要复杂查询和分析需要 JSON 文档存储需要地理信息处理需要全文搜索需要时序数据存储追求 SQL 标准兼容需要高度可扩展性一句话总结MySQL简单、够用、生态好PostgreSQL强大、全能、天花板九、总结9.1 核心架构回顾组件说明Postmaster主进程管理连接和子进程Backend Process后端进程处理客户端请求Shared Buffer共享缓冲区缓存数据页WAL BufferWAL 缓冲区缓存日志Background Workers后台进程VACUUM、Checkpoint 等9.2 索引能力总结索引能力可替代B-Tree等值、范围查询MySQLGIN全文搜索、JSONBElasticsearch、MongoDBGiST地理位置、多维数据PostGISBRIN时序数据InfluxDB9.3 与 MySQL 的核心差异差异点MySQLPostgreSQL进程模型多线程多进程MVCCUndo Log多版本元组索引类型主要是 BTree多种索引扩展性存储引擎可插拔Access Methods 可扩展功能丰富度够用天花板最后一句话PostgreSQL 是数据库领域的全干工程师一个数据库顶多个中间件。如果你的业务需求复杂多变PG 绝对是值得投资学习的选择。热门专栏推荐Agent小册Java基础合集Python基础合集Go基础合集大数据合集前端小册数据库合集Redis 合集Spring 全家桶微服务全家桶数据结构与算法合集设计模式小册消息队列合集等等等还有许多优秀的合集在主页等着大家的光顾感谢大家的支持文章到这里就结束了如果有什么疑问的地方请指出诸佬们一起来评论区一起讨论希望能和诸佬们一起努力今后我们一起观看感谢您的阅读如果帮助到您不妨3连支持一下创造不易您们的支持是我的动力