门户网站 建设快云服务器怎么做网站

门户网站 建设,快云服务器怎么做网站,查域名138,室内设计可以做网站吗文章目录前言1、直接存储一亿数据会遇到的问题是什么#xff1f;2、为什么PostgreSQL是合适的选择#xff1f;3、用PostgreSQL进行存储大体思路一、基础前提#xff1a;链接信息表结构设计1.1 基础表结构1.2 存储空间测算#xff08;一亿条数据#xff09;二、核心调优2、为什么PostgreSQL是合适的选择3、用PostgreSQL进行存储大体思路一、基础前提链接信息表结构设计1.1 基础表结构1.2 存储空间测算一亿条数据二、核心调优PostgreSQL 配置优化postgresql.conf2.1 内存相关配置核心2.2 IO 相关配置提升写入/读取速度2.3 连接与并发配置支撑批量写入三、索引设计高效查询去重亿级数据的核心3.1 必建索引核心3.2 索引优化原则四、数据写入高效导入一亿条链接避免写入瓶颈4.1 批量写入比单条INSERT快100倍4.2 写入优化技巧五、亿级数据的进阶方案分表/分区解决单表查询慢5.1 分区表推荐PostgreSQL 10 原生支持5.2 分表按哈希分表适合无时间维度的场景六、维护与优化亿级数据的长期管理6.1 定期 Vacuum清理死元组6.2 数据归档6.3 监控性能前言1、直接存储一亿数据会遇到的问题是什么直接存储一亿条数据会遇到以下几个核心挑战写入性能瓶颈一亿条数据的插入本身就是个巨大的工程。单条插入会非常慢事务日志会膨胀I/O会成为主要瓶颈。查询性能急剧下降没有合适的索引一个简单的WHERE查询如WHERE url ...可能需要扫描整个表耗时从毫秒级上升到分钟甚至小时级。存储空间和维护成本一亿条数据会占用大量磁盘空间。索引同样会占用空间并且会降低写入速度。VACUUM等维护操作会变得非常缓慢。内存压力如果查询需要处理大量数据而服务器的work_mem等参数配置不当可能会导致磁盘交换使性能雪崩。2、为什么PostgreSQL是合适的选择数据量上限PostgreSQL本身对表大小的限制非常大理论上是32TB一亿条记录对于它来说完全在能力范围内。很多公司在生产环境中用PostgreSQL管理数十亿甚至更多的数据。数据完整性链接信息通常包含URL、标题、描述、状态码等。PostgreSQL提供强大的数据类型如TEXT、VARCHAR、JSONB、TIMESTAMPTZ和约束能保证数据的完整性和一致性。ACID事务在高并发写入和更新的场景下ACID特性可以确保数据不会因为并发操作而损坏。高级功能PostgreSQL的索引特别是GIN和GiST、分区表、全文搜索等功能对于管理和查询海量链接至关重要。3、用PostgreSQL进行存储大体思路场景推荐方案优势写入频率低、查询简单单表 压缩 优化索引实现简单维护成本低高并发写入、按时间查询分区表按时间查询时仅扫描目标分区效率高无时间维度、高频去重单表 MD5唯一索引去重效率最高写入速度快超大规模10亿分表 分区 读写分离水平扩展支撑更高数据量PostgreSQL 存储亿级链接信息的核心是精简表结构减少空间、优化配置提升IO、合理索引加速查询、分区/分表分散压力配合批量写入和定期维护可稳定支撑亿级数据的存储和查询。一、基础前提链接信息表结构设计链接信息通常包含url、创建时间、来源、状态、备注等字段先设计高效的表结构避免冗余、减少存储空间1.1 基础表结构单表存储适合查询简单、写入频率中等的场景-- 创建链接信息表优化字段类型减少单条记录体积CREATETABLEurl_info(id BIGSERIALPRIMARYKEY,-- 自增主键BIGINT 适配亿级数据urlVARCHAR(2048)NOTNULL,-- 链接最大长度2048覆盖99%场景sourceVARCHAR(64)NOTNULL,-- 来源如爬虫-百度、手动录入statusSMALLINTNOTNULLDEFAULT0,-- 状态0-未验证1-有效2-失效create_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,update_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,md5_hashCHAR(32)UNIQUE-- URL的MD5哈希用于去重比直接索引URL高效);-- 关键优化字段类型精简-- 1. 状态用 SMALLINT2字节而非 INT4字节节省50%空间-- 2. 来源用 VARCHAR(64) 而非 TEXT避免变长字段过度占用空间-- 3. MD5哈希用 CHAR(32)固定长度比 VARCHAR 索引效率高。1.2 存储空间测算一亿条数据字段类型单条占用一亿条总占用未压缩压缩后pg_compressidBIGSERIAL8字节800MB~400MBurlVARCHAR(2048)平均64字节6.4GB~2.5GBsourceVARCHAR(64)平均8字节800MB~300MBstatusSMALLINT2字节200MB~100MBcreate_timeTIMESTAMP8字节800MB~400MBupdate_timeTIMESTAMP8字节800MB~400MBmd5_hashCHAR(32)32字节3.2GB~1.2GB总计-~130字节13GB~5.4GB结论一亿条链接信息单表存储含压缩仅需约 5-8GB 存储空间PostgreSQL 完全支撑。二、核心调优PostgreSQL 配置优化postgresql.conf默认配置无法支撑亿级数据的高效写入和查询需调整postgresql.conf关键参数适配亿级数据写入/查询以 PostgreSQL 16 为例。postgresql.conf中的几个关键参数需要根据你的服务器硬件特别是内存和磁盘进行调整。shared_buffersPostgreSQL用于缓存数据的共享内存。通常设置为系统总内存的25%。effective_cache_sizePostgreSQL估算的操作系统可用磁盘缓存。通常设置为系统总内存的50%-75%。work_mem单个查询排序、连接等操作可用的内存。对于复杂查询适当增大此值如64MB可以避免磁盘排序。maintenance_work_memVACUUM、CREATE INDEX等维护操作使用的内存。可以设置得比work_mem大很多如512MB或1GB。checkpoint_completion_target控制检查点完成的平缓程度提高此值如0.9可以平滑I/O负载。2.1 内存相关配置核心# 共享内存建议为物理内存的1/4如64GB内存设为16GB shared_buffers 16GB # 单查询内存避免排序/哈希时频繁写临时文件 work_mem 64MB # 维护操作内存建索引、Vacuum时使用 maintenance_work_mem 2GB # 临时缓冲区减少临时文件IO temp_buffers 1GB2.2 IO 相关配置提升写入/读取速度# 写入策略先写内存批量刷盘适合高并发写入 wal_buffers 128MB # WAL刷盘触发阈值减少IO次数 max_wal_size 64GB min_wal_size 16GB # 开启异步提交提升写入性能允许少量数据丢失业务不敏感时开启 async_commit on # 禁用同步刷盘生产环境建议配SSD再开启 fsync off # 预读块数提升顺序读取效率 effective_io_concurrency 322.3 连接与并发配置支撑批量写入# 最大连接数根据业务调整避免连接数过多 max_connections 500 # 并行查询数提升复杂查询效率 max_parallel_workers_per_gather 8 max_parallel_workers 16三、索引设计高效查询去重亿级数据的核心链接信息的常见查询场景按URL去重、按来源筛选、按状态查询、按时间范围筛选索引设计需兼顾效率和空间3.1 必建索引核心-- 1. URL去重基于MD5哈希的唯一索引比直接索引URL快10倍CREATEUNIQUEINDEXidx_url_md5ONurl_info(md5_hash);-- 2. 来源状态组合索引高频查询如来源为爬虫且状态失效的链接CREATEINDEXidx_source_statusONurl_info(source,status);-- 3. 时间范围索引按创建时间筛选CREATEINDEXidx_create_timeONurl_info(create_time);-- 4. 可选URL前缀索引如需按URL开头模糊查询CREATEINDEXidx_url_prefixONurl_info(url varchar_pattern_ops);3.2 索引优化原则避免对url字段建全值索引URL 长度长索引体积大查询效率低优先用组合索引覆盖高频查询条件如sourcestatuscreate_time减少回表定期清理无效索引用pg_stat_user_indexes查看索引使用情况删除未使用的索引。四、数据写入高效导入一亿条链接避免写入瓶颈4.1 批量写入比单条INSERT快100倍# Python 示例批量插入每次1万条减少网络交互importpsycopg2importhashlibdefbatch_insert_urls(url_list,batch_size10000):connpsycopg2.connect(dbnameyour_db,userpostgres,passwordyour_pwd,hostlocalhost,port5432)curconn.cursor()# 禁用自动提交批量提交conn.autocommitFalseforiinrange(0,len(url_list),batch_size):batchurl_list[i:ibatch_size]values[]forurl,source,statusinbatch:# 计算URL的MD5哈希去重md5hashlib.md5(url.encode(utf-8)).hexdigest()values.append(f({url}, {source},{status}, {md5}))# 批量插入SQLsqlf INSERT INTO url_info (url, source, status, md5_hash) VALUES{,.join(values)}ON CONFLICT (md5_hash) DO NOTHING; -- 去重已存在则跳过 cur.execute(sql)conn.commit()print(f已插入{ibatch_size}条)cur.close()conn.close()# 调用示例假设url_list是一亿条链接的列表# batch_insert_urls(url_list)4.2 写入优化技巧禁用触发器/约束写入前临时禁用非必要的触发器、外键约束写入后恢复关闭索引批量写入前删除非主键索引写入完成后重建比边写边建快使用 COPY 命令若数据在文件中用COPY url_info FROM /path/urls.csv导入最快方式。五、亿级数据的进阶方案分表/分区解决单表查询慢若单表存储一亿条数据后查询效率下降如复杂筛选、排序可采用分区表或分表策略5.1 分区表推荐PostgreSQL 10 原生支持按create_time分区如按月份将一亿条数据分散到多个分区查询时仅扫描目标分区-- 1. 创建分区表主表CREATETABLEurl_info_part(id BIGSERIAL,urlVARCHAR(2048)NOTNULL,sourceVARCHAR(64)NOTNULL,statusSMALLINTNOTNULLDEFAULT0,create_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,update_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP,md5_hashCHAR(32)UNIQUE)PARTITIONBYRANGE(create_time);-- 2. 创建分区按月份示例2025年1月-12月CREATETABLEurl_info_202501PARTITIONOFurl_info_partFORVALUESFROM(2025-01-01)TO(2025-02-01);CREATETABLEurl_info_202502PARTITIONOFurl_info_partFORVALUESFROM(2025-02-01)TO(2025-03-01);-- ... 依次创建其他月份分区-- 3. 为每个分区建索引仅在分区上建减少索引体积CREATEINDEXidx_202501_source_statusONurl_info_202501(source,status);5.2 分表按哈希分表适合无时间维度的场景按md5_hash的哈希值分表将数据分散到 10 个分表-- 创建分表url_info_0 到 url_info_9CREATETABLEurl_info_0(LIKEurl_info INCLUDINGALL);CREATETABLEurl_info_1(LIKEurl_info INCLUDINGALL);-- ... 直到 url_info_9-- 写入时按MD5哈希取模分表INSERTINTOurl_info_{md5_hash%10}(url,source,status,md5_hash)VALUES(...);六、维护与优化亿级数据的长期管理6.1 定期 Vacuum清理死元组-- 自动Vacuum配置postgresql.confautovacuumonautovacuum_vacuum_scale_factor0.05-- 数据变化5%触发Vacuumautovacuum_analyze_scale_factor0.02-- 数据变化2%触发Analyze-- 手动Vacuum批量写入后执行VACUUMANALYZEurl_info;6.2 数据归档将过期的链接数据如1年前的归档到冷表/低成本存储如PostgreSQL的TOAST表、外部存储-- 创建归档表CREATETABLEurl_info_archive(LIKEurl_info INCLUDINGALL);-- 迁移过期数据INSERTINTOurl_info_archiveSELECT*FROMurl_infoWHEREcreate_time2024-01-01;-- 删除原表过期数据DELETEFROMurl_infoWHEREcreate_time2024-01-01;6.3 监控性能用 PostgreSQL 自带工具监控-- 查看表大小SELECTpg_size_pretty(pg_total_relation_size(url_info));-- 查看索引使用情况SELECTindexrelname,idx_scanFROMpg_stat_user_indexesWHERErelnameurl_info;-- 查看慢查询SELECT*FROMpg_stat_statementsORDERBYtotal_timeDESCLIMIT10;