Showing Posts From
数据库
数据库设计：E-R 图、3NF 范式与 SQL 约束建表
E-R 图基础 以供应商-零件为例：实体1：供应商（Sno, Sname, City） 实体2：零件（Pno, Pname, Color, Weight） 联系：供应（M:N，含属性 Price）一个供应商可供应多种零件，一种零件可由多个供应商供应，联系属于 多对多（M:N）。 Supplier（供应商） Part（零件） ┌─────────────────┐ ┌─────────────────┐ │ Sno (PK) │ M N │ Pno (PK) │ │ Sname ├────────┤ Pname │ │ City │ 供应 │ Color │ └─────────────────┘ Price │ Weight │ └─────────────────┘3NF 关系模式设计 M:N 联系必须单独拆出一张联系表： Supplier（供应商表） Supplier(Sno PK, Sname, City)Part（零件表） Part(Pno PK, Pname, Color, Weight)Supply（供应联系表） Supply(Sno PK FK→Supplier, Pno PK FK→Part, Price)联合主键 (Sno, Pno) 保证每个供应商对每种零件只有一条记录。三个表均满足 3NF（无传递依赖）。 SQL 建表（含约束） CREATE TABLE Supplier ( Sno CHAR(10) PRIMARY KEY, Sname VARCHAR(50) NOT NULL, City VARCHAR(50) );CREATE TABLE Part ( Pno CHAR(10) PRIMARY KEY, Pname VARCHAR(50) NOT NULL, Color VARCHAR(20), Weight DECIMAL(8,2) );CREATE TABLE Supply ( Sno CHAR(10), Pno CHAR(10), Price DECIMAL(10,2), PRIMARY KEY (Sno, Pno), FOREIGN KEY (Sno) REFERENCES Supplier(Sno), FOREIGN KEY (Pno) REFERENCES Part(Pno), CHECK (Price > 0) );PRIMARY KEY：联合主键，值组合唯一且不为 NULL FOREIGN KEY：引用父表主键，保证参照完整性 CHECK：价格必须大于 0，在插入/更新时自动校验JOIN 查询 查询"供应红色零件的供应商名称"： SELECT DISTINCT S.Sname FROM Supplier S JOIN Supply SP ON S.Sno = SP.Sno JOIN Part P ON SP.Pno = P.Pno WHERE P.Color = '红色';等价子查询写法： SELECT DISTINCT Sname FROM Supplier WHERE Sno IN ( SELECT Sno FROM Supply WHERE Pno IN ( SELECT Pno FROM Part WHERE Color = '红色' ) );JOIN 写法更清晰，子查询写法在没有 JOIN 语法支持时可用。 常见范式对比范式 要求 典型违反1NF 每个字段原子值，不含多值 用逗号分隔多个标签存一列2NF 非主属性完全依赖主键（无部分依赖） 联系表里存冗余属性3NF 非主属性不传递依赖主键 城市→邮编→省份放同一表实践中通常达到 3NF 即可，OLAP 场景允许适度冗余换查询性能。
SQL 建表约束：PRIMARY KEY / FOREIGN KEY / CHECK 写法
建表时声明约束是数据库设计的基础，常见的有主键约束、外键约束和 CHECK 约束。 场景：供应商-零件多对多关系 经典的供应商（Supplier）-零件（Part）-供应（Supply）三表结构：供应商可以供应多种零件（M:N 关系） 每对供应商+零件组合有一个供应价格关系模式（3NF） Supplier(Sno PK, Sname, City) Part(Pno PK, Pname, Color, Weight) Supply(Sno PK FK, Pno PK FK, Price)Supply 的主键是复合主键 (Sno, Pno)，同时 Sno 和 Pno 各自是外键。 SQL 建表 CREATE TABLE Supplier ( Sno CHAR(10) NOT NULL, Sname VARCHAR(50) NOT NULL, City VARCHAR(50), PRIMARY KEY (Sno) );CREATE TABLE Part ( Pno CHAR(10) NOT NULL, Pname VARCHAR(50) NOT NULL, Color VARCHAR(20), Weight DECIMAL(8,2), PRIMARY KEY (Pno) );CREATE TABLE Supply ( Sno CHAR(10) NOT NULL, Pno CHAR(10) NOT NULL, Price DECIMAL(10,2) NOT NULL, PRIMARY KEY (Sno, Pno), FOREIGN KEY (Sno) REFERENCES Supplier(Sno) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (Pno) REFERENCES Part(Pno) ON DELETE CASCADE ON UPDATE CASCADE, CHECK (Price > 0) );各约束说明 PRIMARY KEY：不能为空，不能重复。复合主键用 PRIMARY KEY (col1, col2) 表格级声明，不能用列级。 FOREIGN KEY：引用另一张表的主键或唯一键，保证参照完整性。ON DELETE CASCADE 表示父表删除时子表自动级联删除。 CHECK：定义列值的范围或条件，CHECK (Price > 0) 要求价格必须大于零。 NOT NULL：禁止 NULL 值，通常和主键一起用。 常用查询 查询"供应红色零件的供应商名称"： SELECT DISTINCT s.Sname FROM Supplier s JOIN Supply sp ON s.Sno = sp.Sno JOIN Part p ON sp.Pno = p.Pno WHERE p.Color = '红色';或用子查询： SELECT Sname FROM Supplier WHERE Sno IN ( SELECT sp.Sno FROM Supply sp JOIN Part p ON sp.Pno = p.Pno WHERE p.Color = '红色' );约束的列级 vs 表级写法 列级（约束跟在列后面，只作用于该列）： CREATE TABLE t ( id INT PRIMARY KEY, val INT CHECK (val > 0), fk INT REFERENCES other(id) );表级（单独一行，支持多列主键和复合外键）： CREATE TABLE t ( id1 INT, id2 INT, val INT, PRIMARY KEY (id1, id2), -- 复合主键必须用表级 FOREIGN KEY (id1) REFERENCES a(id), CHECK (val > 0) );复合主键（多列联合唯一）只能用表级声明；单列约束两种写法均可。
供应关系 E-R 建模 + 3NF + SQL：一道数据库题的标准答案
数据库课程有一道经典题目：供应商-零件-供应关系建模。是标准的 M:N 联系带属性，按 E-R → 3NF → SQL 完整走一遍。 题目 某企业采购管理系统：供应商 (Supplier)：编号 Sno、名称 Sname、城市 City 零件 (Part)：编号 Pno、名称 Pname、颜色 Color、重量 Weight 供应商可以供应多种零件，每种零件可由多个供应商供应 每个供应商对每种零件有一个供应价格 Price要求：画 E-R 图 设计满足 3NF 的关系模式 SQL 建"供应"表，带主键、外键、价格 > 0 约束 写"供应红色零件的供应商名称"查询第一步：E-R 图 两个实体、一个联系： ┌──────────────────┐ │ Supplier │ │──────────────────│ │ Sno (PK) │ │ Sname │ │ City │ └────────┬─────────┘ │ M │ ┌──────┴──────┐ │ Supply │◄── Price（联系属性） └──────┬──────┘ │ N │ ┌────────┴─────────┐ │ Part │ │──────────────────│ │ Pno (PK) │ │ Pname │ │ Color │ │ Weight │ └──────────────────┘关系类型：Supplier M——(Supply)——N Part，Supply 联系有属性 Price。 第二步：3NF 关系模式 M:N 联系必须独立成一张表，不能合并到任何一端。 Supplier 表： Supplier ( Sno ← 主键 (PK) Sname City )Part 表： Part ( Pno ← 主键 (PK) Pname Color Weight )Supply 表（M:N 联系变实体）： Supply ( Sno ← 主键的一部分, 外键 → Supplier(Sno) Pno ← 主键的一部分, 外键 → Part(Pno) Price )Supply 的主键是 (Sno, Pno) 联合主键——同一个供应商同一个零件只能有一条供应记录，不同的价格意味着不同的关系需要建模成"报价历史"。 为什么满足 3NF：每个表所有非主属性都完全依赖于主键（无部分依赖 → 2NF） 每个表非主属性只依赖主键，不依赖其它非主属性（无传递依赖 → 3NF）第三步：SQL 建"供应"表 CREATE TABLE Supply ( Sno CHAR(10) NOT NULL, Pno CHAR(10) NOT NULL, Price DECIMAL(10,2) NOT NULL, PRIMARY KEY (Sno, Pno), FOREIGN KEY (Sno) REFERENCES Supplier(Sno), FOREIGN KEY (Pno) REFERENCES Part(Pno), CHECK (Price > 0) );要点：联合主键：PRIMARY KEY (Sno, Pno) 一起才唯一 外键：Sno 必须在 Supplier 里存在、Pno 必须在 Part 里存在 CHECK 约束：Price > 0 由数据库强制，即使应用忘了校验也保证不了负价 DECIMAL(10,2) 而不是 FLOAT——金额永远用定点数，浮点会有精度问题第四步：查询"供应红色零件的供应商名称" 需要连 Supplier / Supply / Part 三张表： SELECT DISTINCT S.Sname FROM Supplier S JOIN Supply SP ON S.Sno = SP.Sno JOIN Part P ON SP.Pno = P.Pno WHERE P.Color = '红色';关键点：DISTINCT — 一个供应商可能供应多种红色零件，去重 JOIN — 内连接，只要有匹配就出结果 WHERE P.Color = '红色' — 过滤条件不用 JOIN 也能写（子查询版本）： SELECT DISTINCT Sname FROM Supplier WHERE Sno IN ( SELECT Sno FROM Supply WHERE Pno IN ( SELECT Pno FROM Part WHERE Color = '红色' ) );两种写法结果一样。现代 SQL 优化器基本能把子查询版本优化成 JOIN，但读起来 JOIN 更直观。 一些延伸 要报价历史怎么办？ 加一个 EffectiveDate： CREATE TABLE SupplyHistory ( Sno CHAR(10), Pno CHAR(10), EffectiveDate DATE, Price DECIMAL(10, 2), PRIMARY KEY (Sno, Pno, EffectiveDate), FOREIGN KEY (Sno) REFERENCES Supplier(Sno), FOREIGN KEY (Pno) REFERENCES Part(Pno), CHECK (Price > 0) );查最新价格： SELECT Sno, Pno, Price FROM SupplyHistory sh WHERE EffectiveDate = ( SELECT MAX(EffectiveDate) FROM SupplyHistory WHERE Sno = sh.Sno AND Pno = sh.Pno );现代 SQL 也可以用窗口函数： SELECT Sno, Pno, Price FROM ( SELECT Sno, Pno, Price, ROW_NUMBER() OVER (PARTITION BY Sno, Pno ORDER BY EffectiveDate DESC) rn FROM SupplyHistory ) t WHERE rn = 1;一句话总结 M:N 联系带属性必须独立成表，联合主键 + 两个外键。3NF 的关系模式设计基本是"每个 M:N 拆一张表 + 每张表消除传递依赖"。查询用 JOIN 比多层子查询好读。
MongoDB 导出数据：mongodump 和 mongoexport 怎么选
MongoDB 导出数据有两个官方工具，用途各不同：mongodump — 二进制 BSON，用于完整备份和恢复（配合 mongorestore） mongoexport — JSON / CSV 明文，用于数据处理和迁移到其它系统搞混了会踩坑：拿 BSON 想给别的系统吃是不行的，拿 JSON 恢复也丢索引和元数据。 mongodump：完整备份 mongodump \ --host localhost \ --port 27017 \ --db test \ --out ./backup产物： backup/ └── test/ ├── users.bson # 数据（BSON 格式） ├── users.metadata.json # 索引、options 等元信息 └── orders.bson恢复： mongorestore ./backup # 或指定库 mongorestore --nsInclude "test.*" ./backupgzip 压缩： mongodump --db test --archive=backup.gz --gzip mongorestore --archive=backup.gz --gzip生产备份用这个——单文件、自带索引元数据、恢复一步到位。 mongoexport：导出 JSON / CSV mongoexport \ --db test \ --collection users \ --out users.json默认是 NDJSON（每行一个 JSON 对象）： {"_id":{"$oid":"..."},"name":"Tom","age":18} {"_id":{"$oid":"..."},"name":"Jerry","age":22}想要标准 JSON 数组： mongoexport --db test --collection users --jsonArray --out users.json导出 CSV： mongoexport \ --db test \ --collection users \ --type=csv \ --fields=name,age,email \ --out users.csvCSV 必须显式列出 --fields，不给字段名会报错。 条件导出 --query 是 MongoDB shell 的 JSON 语法： mongoexport \ --db test \ --collection users \ --query '{"age":{"$gt":18},"status":"active"}' \ --out active_users.json日期区间过滤： --query '{"createdAt":{"$gte":{"$date":"2026-01-01T00:00:00Z"}}}'带账号密码 推荐用 URI 一把传： mongoexport \ --uri="mongodb://admin:PASSWORD@127.0.0.1:27017/test?authSource=admin" \ --collection users \ --jsonArray \ --out users.json或者分开： mongodump \ --host localhost --port 27017 \ --username admin --password PASSWORD \ --authenticationDatabase admin \ --db test --out backupauthSource=admin 很重要——默认认证库是当前 db，绝大多数生产环境把用户建在 admin 库。 Docker 里的 MongoDB 进容器里跑： docker exec -it mongo mongodump --db test --out /tmp/backup docker cp mongo:/tmp/backup ./backup或者从宿主机直连（容器映射了端口）： mongodump --host 127.0.0.1 --port 27017 --db test --out ./backupPython 版本 from pymongo import MongoClient import jsonclient = MongoClient("mongodb://localhost:27017") col = client["test"]["users"]# 不要 _id 字段，方便导入到其它系统 data = list(col.find({}, {"_id": 0}))with open("users.json", "w", encoding="utf-8") as f: json.dump(data, f, ensure_ascii=False, indent=2)按条件流式导出（大数据集省内存）： import jsonwith open("users.ndjson", "w", encoding="utf-8") as f: for doc in col.find({"age": {"$gt": 18}}, {"_id": 0}): f.write(json.dumps(doc, ensure_ascii=False) + "\n")一张对比表需求 用什么备份 + 恢复到 MongoDB mongodump迁移到 MongoDB（跨版本、跨集群） mongodump + mongorestore导出给下游数据管道用 mongoexport --jsonArray导出给 Excel / BI mongoexport --type=csv定制字段结构、脱敏 Python + pymongo增量导出 --query + 时间戳过滤一句话总结 备份恢复用 mongodump，跨系统迁移用 mongoexport。Docker 容器里跑要注意 exec 进去或映射端口。生产环境永远用 URI + authSource=admin。
数据库存储版本范围：SemVer 比较与漏洞版本匹配方案
为什么不能用字符串比较版本号 SELECT * FROM assets WHERE version < '2.15.0'字符串比较结果： '2.10.0' < '2.9.0' -- 错误！字典序 '1' < '9'SemVer（语义化版本）中 2.10.0 > 2.9.0，必须按数字分段比较。 方案一：程序端比较（推荐） 数据库只存原始版本字符串，程序端做 SemVer 比较： Java（semver4j）： <dependency> <groupId>org.semver4j</groupId> <artifactId>semver4j</artifactId> <version>5.3.0</version> </dependency>import org.semver4j.Semver;public class VersionMatcher { public static boolean isVulnerable(String version, String range) { try { Semver v = Semver.parse(version); return v.satisfies(range); } catch (Exception e) { return false; } } }// 使用 isVulnerable("2.14.1", ">=2.0.0 <2.15.0") // true isVulnerable("2.15.0", ">=2.0.0 <2.15.0") // falsePython（packaging 库）： from packaging.version import Version from packaging.specifiers import SpecifierSetdef is_vulnerable(version: str, spec: str) -> bool: try: return Version(version) in SpecifierSet(spec) except Exception: return Falseis_vulnerable("2.14.1", ">=2.0.0,<2.15.0") # True方案二：拆成上下界存数据库 将范围条件拆为四个字段，程序比较时不需要解析字符串： CREATE TABLE vuln_rule ( id BIGINT PRIMARY KEY, component VARCHAR(100), min_version VARCHAR(50), max_version VARCHAR(50), min_include TINYINT DEFAULT 1, -- 1=包含 >=，0=不包含 > max_include TINYINT DEFAULT 0 -- 1=包含 <=，0=不包含 < );表示 >= 2.0.0 < 2.15.0：min_version max_version min_include max_include2.0.0 2.15.0 1 0程序判断： boolean matches(String version, VulnRule rule) { Semver v = Semver.parse(version); Semver min = Semver.parse(rule.getMinVersion()); Semver max = Semver.parse(rule.getMaxVersion()); boolean lowerOk = rule.isMinInclude() ? v.isGreaterThanOrEqualTo(min) : v.isGreaterThan(min); boolean upperOk = rule.isMaxInclude() ? v.isLowerThanOrEqualTo(max) : v.isLowerThan(max); return lowerOk && upperOk; }方案三：版本号数字化后数据库直接查 将 2.15.0 转换为定宽整数 002015000，再存数据库： public static long versionToLong(String version) { String[] parts = version.split("\\."); long major = parts.length > 0 ? Long.parseLong(parts[0]) : 0; long minor = parts.length > 1 ? Long.parseLong(parts[1]) : 0; long patch = parts.length > 2 ? Long.parseLong(parts[2]) : 0; return major * 1_000_000L + minor * 1_000L + patch; }// "2.15.0" -> 2015000 // "2.9.0" -> 2009000 // "2.10.0" -> 2010000 (正确：2010000 > 2009000)数据库存 version_num BIGINT，直接用 SQL 范围查询： SELECT * FROM vuln_rule WHERE version_num >= 2000000 AND version_num < 2015000;适合版本号分段均不超过 999 的情况。 方案对比方案 优点 缺点程序端比较 支持复杂范围（pre-release） 无法在 SQL 直接过滤上下界字段 数据结构清晰，易扩展 多字段 JOIN 稍复杂数字化版本 SQL 直接查询，性能最好 分段超过 999 会溢出推荐：中小规模用方案一（程序端比较），大规模漏洞库（百万级 asset）用方案三配合索引加速。 漏洞管理平台实践 批量扫描 asset 时，避免 N+1 查询： // 一次查出所有漏洞规则 List<VulnRule> rules = vulnRuleMapper.selectAll();// 按 component 分组 Map<String, List<VulnRule>> ruleMap = rules.stream() .collect(Collectors.groupingBy(VulnRule::getComponent));// 批量匹配 for (Asset asset : assets) { List<VulnRule> candidates = ruleMap.getOrDefault(asset.getComponent(), List.of()); for (VulnRule rule : candidates) { if (VersionMatcher.isVulnerable(asset.getVersion(), rule.getRange())) { record(asset, rule); } } }
版本号存数据库 + 范围匹配：SemVer 的正确姿势
漏洞库 / 依赖资产表里经常要存"受影响版本范围"，然后拿具体版本号去匹配——比如 Log4j >= 2.0-beta9 < 2.15.0、Spring 4Shell >= 5.3.0 < 5.3.18 || >= 5.2.0 < 5.2.20。直接 SQL 字符串比较必翻车： SELECT * WHERE version >= '2.9.0' AND version < '2.15.0'; -- 会把 2.10.0 判成 < 2.9.0（字符串按字典序）字典序里 '2.10' < '2.9'（因为 '1' < '9'）。要按语义化版本比，得单独设计。 方案 1：存字符串，程序判断（灵活但慢） 数据库里就存原始范围： CREATE TABLE vuln_rule ( id BIGINT PRIMARY KEY, product VARCHAR(64), affected_range VARCHAR(255), -- '>= 2.0 < 2.15.0' fixed_version VARCHAR(50) );匹配时程序里跑 SemVer 库： Java： import com.vdurmont.semver4j.Requirement; import com.vdurmont.semver4j.Semver;Requirement req = Requirement.buildNPM(">=2.0 <2.15.0"); Semver ver = new Semver("2.14.1", Semver.SemverType.NPM); boolean vulnerable = req.isSatisfiedBy(ver);Node.js： import semver from "semver"; semver.satisfies("2.14.1", ">=2.0 <2.15.0"); // truePython： from packaging.specifiers import SpecifierSet from packaging.version import VersionVersion("2.14.1") in SpecifierSet(">=2.0,<2.15.0") # True优点：范围表达最灵活，各种 >=、<、!=、~、^ 都支持。 缺点：数据库查不了范围——每条规则拿出来在程序里跑一遍，几万条规则会慢。 方案 2：拆上下界（企业最常用） 把 >=2.0 <2.15.0 拆成四列： CREATE TABLE vuln_rule ( id BIGINT PRIMARY KEY, product VARCHAR(64), min_version VARCHAR(50), max_version VARCHAR(50), min_include TINYINT DEFAULT 1, -- 1: >= 0: > max_include TINYINT DEFAULT 0, -- 1: <= 0: < INDEX idx_product (product) );数据：product min_version max_version min_include max_includelog4j 2.0 2.15.0 1 0spring 5.3.0 5.3.18 1 0spring 5.2.0 5.2.20 1 0多个范围（OR 关系）拆成多行。 匹配：数据库先粗筛（按 product），程序做精确 SemVer 比： List<VulnRule> rules = mapper.findByProduct("spring"); for (VulnRule r : rules) { if (semver.gteq(target, r.minVersion) && semver.lt (target, r.maxVersion)) { // 命中 } }优点：结构清晰、粗筛快、精确匹配用程序保证正确。大多数漏洞平台走这条路。 方案 3：版本数字化后 SQL 直接查 把版本号编码成一个数字，SQL 就能直接范围查： 2.15.0 → 002 015 000 → 2015000 2.14.10 → 002 014 010 → 2014010 2.10.0 → 002 010 000 → 2010000每段用固定位数（3 位）保留： long encode(String v) { String[] p = v.split("\\."); long n = 0; for (int i = 0; i < 3; i++) { int part = i < p.length ? Integer.parseInt(p[i].split("[^0-9]")[0]) : 0; n = n * 1000 + part; } return n; }数据库： CREATE TABLE vuln_rule ( id BIGINT PRIMARY KEY, product VARCHAR(64), min_version_int BIGINT, max_version_int BIGINT, min_include TINYINT, max_include TINYINT, INDEX idx_product_range (product, min_version_int, max_version_int) );查询： SELECT * FROM vuln_rule WHERE product = 'log4j' AND 2014001 >= min_version_int -- target 版本 encode 后 AND 2014001 < max_version_int;优点：纯 SQL 就能查，走索引，几百万数据也很快。 缺点：每段有位数上限（3 位 = 999，遇到 10.1000.0 就崩） 预发布版本、build metadata（2.0-beta9、1.0+20130313144700）编码复杂 需要维护 encode / decode 函数大厂漏洞平台一般是"方案 3 粗筛 + 方案 1 精确"组合——SQL 层快速缩小范围，程序层用 SemVer 库保证语义正确。 处理预发布版本 2.0-beta9、2.0-rc1 这种版本比较：SemVer 规定 1.0.0-alpha < 1.0.0-beta < 1.0.0-rc < 1.0.0 字符串比又反过来：'1.0.0' < '1.0.0-alpha'（长度）别自己写比较，用 SemVer 库：Java：semver4j Node：semver Python：packaging 或 python-semver Go：semver三个方案对比方案 存储 查询速度 语义正确性 复杂度 推荐场景存字符串 一列 慢 完美 低 规则量少（< 1 万条）上下界拆列 4 列 中 靠程序 中 推荐，通用最优版本数字化 长整型 极快 简单版本 OK 高 亿级数据 + 版本规范一句话总结 版本号别当字符串比——SQL 存上下界列 + 程序里跑 SemVer 库判定是最稳的组合。追求极致查询速度就把版本 encode 成整数走索引，代价是预发布版本处理复杂。
下载量统计：滑动窗口 vs 整点分桶与 Redis 分钟桶方案
两种统计方式 滑动窗口（实时系统首选） 统计"当前往前推 N 小时"的下载数： -- 1 小时下载量 SELECT COUNT(*) FROM downloads WHERE download_time >= NOW() - INTERVAL 1 HOUR;-- 3 小时下载量 SELECT COUNT(*) FROM downloads WHERE download_time >= NOW() - INTERVAL 3 HOUR;特点：平滑、不会整点突变，适合实时热榜、推荐系统、CDN 统计。 自然时间段（BI 报表常用） 按整点分桶，例如现在 15:37，统计范围是 15:00~15:59： SELECT COUNT(*) FROM downloads WHERE HOUR(download_time) = HOUR(NOW()) AND DATE(download_time) = CURDATE();适合财务报表、数据仓库场景，不适合实时排行——整点会出现排名抽搐。 Redis 分钟桶（高并发推荐） 生产环境不会每次扫大表，而是用 Redis 分钟桶： Key 格式：download:item:{id}:{yyyyMMddHHmm} 例如：download:item:123:202605281537每次下载： INCR download:item:123:202605281537 EXPIRE download:item:123:202605281537 86400查询 1 小时：累加最近 60 个分钟 Key 查询 3 小时：累加最近 180 个分钟 Key 逻辑上是滑动窗口，底层是时间桶——抖音、B站、Steam 热榜的标准做法。 热度分值 单纯看 1 小时容易被刷量，实际排行榜通常组合多个时间窗口： score = download_1h * 0.5 + download_3h * 0.3 + download_24h * 0.2再叠加时间衰减因子防止老内容永远霸榜。 方案选型场景 推荐方案普通业务后台 MySQL NOW() - INTERVAL高并发下载统计 Redis 分钟桶 + 定时汇总热门排序 1h + 3h + 24h 三个窗口组合历史分析 / BI ClickHouse 聚合
Redis 下载量统计：滑动窗口、时间桶与热度排名
两种时间口径 滑动窗口：统计 now() - INTERVAL 到 now() 的下载量，精确反映最近一段时间，但每次查询结果都在变化。 自然时间段：按整点（今天 0 点至今、本小时 0 分至今）统计，实现简单，但整点后计数归零，会出现明显跳变。 大多数业务选择自然时间段作为展示口径，滑动窗口用于后台热度计算。 Redis 分钟级时间桶 每次下载触发 INCR，key 包含资源 ID 和分钟级时间戳： download:item:123:202605281537时间戳格式：yyyyMMddHHmm，精度到分钟。 import redis from datetime import datetimer = redis.Redis()def record_download(item_id: int): minute_key = datetime.utcnow().strftime("%Y%m%d%H%M") key = f"download:item:{item_id}:{minute_key}" r.incr(key) r.expire(key, 86400 * 2) # 保留 2 天，超过不再需要EXPIRE 设为 2 天，避免 key 无限堆积。 查询最近 1 小时下载量 累加过去 60 个分钟 key： from datetime import datetime, timedeltadef get_downloads_1h(item_id: int) -> int: now = datetime.utcnow() keys = [] for i in range(60): t = now - timedelta(minutes=i) minute_key = t.strftime("%Y%m%d%H%M") keys.append(f"download:item:{item_id}:{minute_key}") counts = r.mget(keys) return sum(int(c) for c in counts if c)同理，3 小时累加 180 个 key，24 小时累加 1440 个 key。 热度排名分数 加权组合多个时间窗口，近期权重更高： def get_hot_score(item_id: int) -> float: h1 = get_downloads_1h(item_id) h3 = get_downloads_3h(item_id) h24 = get_downloads_24h(item_id) return h1 * 0.5 + h3 * 0.3 + h24 * 0.2定时任务（如每 5 分钟）批量计算所有资源的热度分，写入 Redis Sorted Set： r.zadd("hot:items", {str(item_id): score})查询热度榜： top10 = r.zrevrange("hot:items", 0, 9, withscores=True)写入性能优化 下载量高时，单个 key 的 INCR 有热点风险。可以用 Pipeline 批量写： pipe = r.pipeline() for item_id in download_batch: key = f"download:item:{item_id}:{minute_key}" pipe.incr(key) pipe.expire(key, 172800) pipe.execute()或者在应用层做本地聚合，每 30 秒一次性写入 Redis，减少 INCR 频率。 离线统计：ClickHouse 实时热度用 Redis 时间桶，历史下载量分析（按天/按地区）用 ClickHouse：每次下载写入 Kafka，消费后落表到 ClickHouse ClickHouse 按 item_id + date 聚合，查询速度远快于 MySQLRedis 时间桶适合实时排行榜，ClickHouse 适合报表和长周期分析，两者互补。
ClickHouse system 日志表清理：TRUNCATE、关闭无用日志和 TTL 配置
ClickHouse 的 system.*_log 表在生产环境不加干预，几个月就能积累几十上百 GB。 空间占用查询 SELECT database, table, formatReadableSize(sum(bytes)) size FROM system.parts GROUP BY database, table ORDER BY sum(bytes) DESC;常见爆炸表：表 典型症状text_log 大量 exception 或 debug 日志trace_log 开了 profile 或查询 traceasynchronous_metric_log metrics 刷新间隔太低metric_log 运行时间过长part_log 小批量高频 insertquery_log 高频查询立即清理 TRUNCATE TABLE system.text_log; TRUNCATE TABLE system.trace_log; TRUNCATE TABLE system.asynchronous_metric_log; TRUNCATE TABLE system.metric_log; TRUNCATE TABLE system.part_log; TRUNCATE TABLE system.query_log; TRUNCATE TABLE system.latency_log; TRUNCATE TABLE system.processors_profile_log;SYSTEM FLUSH LOGS;TRUNCATE 后空间不会立刻全部回收（还有 deleted parts 和文件系统缓存），重启服务通常能完全释放： systemctl restart clickhouse-server关闭不需要的日志 编辑 /etc/clickhouse-server/config.xml 或 /etc/clickhouse-server/config.d/*.xml： <!-- 关闭高噪音日志 --> <text_log remove="1"/> <trace_log remove="1"/> <metric_log remove="1"/> <asynchronous_metric_log remove="1"/> <processors_profile_log remove="1"/> <part_log remove="1"/>重启生效： systemctl restart clickhouse-server生产建议：保留 query_log，用于慢查询分析。其余视需要选择性保留。 设置 TTL 限制保留天数 不想完全关闭，只保留最近几天： <query_log> <database>system</database> <table>query_log</table> <flush_interval_milliseconds>7500</flush_interval_milliseconds> <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> </query_log>part_log 异常：小批量写入问题 part_log 几千万行通常意味着：每条记录单独 INSERT（正确做法是批量几千到几万行） Kafka consumer batch 配置太小 频繁小事务导致 parts 积累、merge 压力大查看各表的活跃 parts 数量： SELECT table, count() FROM system.parts WHERE active GROUP BY table ORDER BY count() DESC;正常表的 parts 数在百到千量级；如果单表几万 parts，说明写入模式有问题。 query_log 分析 高频查询： SELECT query, count() FROM system.query_log GROUP BY query ORDER BY count() DESC LIMIT 20;频繁报错的查询： SELECT exception, count() FROM system.query_log WHERE type = 'ExceptionWhileProcessing' GROUP BY exception ORDER BY count() DESC LIMIT 20;不要直接删目录 rm -rf /var/lib/clickhouse/data/system/* 可能导致 metadata 不一致、启动失败或权限异常，优先使用 TRUNCATE、TTL 或 remove="1" 配置。
ClickHouse system.*_log 表几十 GB？先清后关
线上一台 ClickHouse 跑了两个月，磁盘吃掉几十 GB。查一下： SELECT database, table, formatReadableSize(sum(bytes)) AS size, sum(rows) AS rows FROM system.parts WHERE active GROUP BY database, table ORDER BY sum(bytes) DESC LIMIT 10;结果： system text_log 34.15 GiB 9亿行 system trace_log 11.41 GiB 5亿行 system asynchronous_metric_log 9.36 GiB 347亿行 system metric_log 7.75 GiB 3600万行 system part_log 4.65 GiB 6800万行 system query_log 3.59 GiB 3800万行几乎全是 ClickHouse 自己写的内部监控日志。业务数据加起来才几个 G。 第一步：立刻清理 TRUNCATE 把大头清空： TRUNCATE TABLE system.text_log; TRUNCATE TABLE system.trace_log; TRUNCATE TABLE system.asynchronous_metric_log; TRUNCATE TABLE system.metric_log; TRUNCATE TABLE system.part_log; TRUNCATE TABLE system.processors_profile_log; -- query_log 可以选择保留，用于事后排查 TRUNCATE TABLE system.latency_log;query_log 是"这个 ClickHouse 都处理过什么 SQL"的记录，日常排查很有用，多数情况留着。 清完之后： SYSTEM FLUSH LOGS;再查一次磁盘： du -sh /var/lib/clickhouse/data/system/*空间没立刻回收是正常的。因为：MergeTree 的 parts 只是标记删除，等 merge 文件系统 cache delete_from_disk 有 TTL可以强制一下： OPTIMIZE TABLE system.query_log FINAL;或者最粗暴： sudo systemctl restart clickhouse-server启动时会跳过被标记删除的 parts。 第二步：从根源关掉不需要的日志 清了以后不管，几周后又长回来。要真省心就编辑 config，把不用的日志表直接关掉。 编辑： sudo nano /etc/clickhouse-server/config.d/logs.xml内容： <clickhouse> <!-- 关掉巨吃磁盘的三个 --> <text_log remove="1"/> <trace_log remove="1"/> <asynchronous_metric_log remove="1"/> <metric_log remove="1"/> <part_log remove="1"/> <processors_profile_log remove="1"/> <!-- query_log 保留，但缩短 TTL --> <query_log> <database>system</database> <table>query_log</table> <partition_by>toYYYYMM(event_date)</partition_by> <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> <flush_interval_milliseconds>7500</flush_interval_milliseconds> </query_log> </clickhouse>remove="1" 直接不启用这类表 <ttl> 让 ClickHouse 自动过期删除老数据重启生效： sudo systemctl restart clickhouse-server为什么这些表会爆 asynchronous_metric_log 每秒都在采几百个指标，一天几千万行是正常的。trace_log 记录每个 query 的 profile 事件，非常细。这些表默认全开，对于开发/测试很有用，对生产就是纯磁盘杀手。 生产环境的经验：保留：query_log（+ 7 天 TTL） 可选保留：query_thread_log 排查慢查询用 关掉：text_log、trace_log、asynchronous_metric_log、metric_log、part_log、processors_profile_log如果需要采指标，用 Prometheus 拉 /metrics 接口，别依赖 ClickHouse 内部 metric_log。 顺手加个磁盘水位报警 SELECT name, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total, round(free_space / total_space * 100, 1) AS free_percent FROM system.disks;低于 20% 该发告警了。 一句话总结 ClickHouse 磁盘被吃是 system.*_log 表的锅。先 TRUNCATE 清空、再 config 里 remove="1" 关掉大部分、给 query_log 加个 TTL。生产上从第一天就该这么配。
SQLite 大数据量优化：WAL 模式、批量事务、游标分页和索引策略
SQLite 单数据库文件理论上限约 281TB，实际几十 GB 均可正常运行，性能瓶颈通常来自配置和使用方式，而非 SQLite 本身。 必开：WAL 模式 PRAGMA journal_mode=WAL;默认的 DELETE journal 模式写入时会锁全库，WAL 模式允许读写并发，大幅提升写入性能。需要在每次连接时设置（或写入配置持久化）。 减少同步次数 -- 兼顾安全和性能（推荐） PRAGMA synchronous=NORMAL;-- 最高性能（断电可能丢最近几秒数据，非关键数据可用） PRAGMA synchronous=OFF;批量事务（最重要的优化） 单条 INSERT 性能极差，每次写入都会触发一次磁盘同步： // 错误写法：每条 INSERT 是独立事务 for (const item of items) { db.run("INSERT INTO logs VALUES (?)", item); }改为批量事务，性能提升 100-1000 倍： BEGIN; INSERT INTO logs VALUES (1, 'event_a', 1716800000); INSERT INTO logs VALUES (2, 'event_b', 1716800001); -- ... 批量写入 COMMIT;Node.js（better-sqlite3）示例： const insertMany = db.transaction((items) => { const stmt = db.prepare("INSERT INTO logs (id, type, ts) VALUES (?, ?, ?)"); for (const item of items) { stmt.run(item.id, item.type, item.ts); } });insertMany(items); // 一次事务写入全部Python（sqlite3）示例： conn.execute("BEGIN") conn.executemany("INSERT INTO logs VALUES (?, ?, ?)", items) conn.execute("COMMIT")建议每批 500-5000 条，批次过大会增加内存压力。 游标分页（替代 OFFSET） OFFSET 越大越慢，因为需要扫描并跳过前面所有行： -- 慢：OFFSET 100万，需要扫描 100万行 SELECT * FROM logs LIMIT 20 OFFSET 1000000;改用游标分页（Keyset Pagination）： -- 快：直接从上次最后一条的 id 开始 SELECT * FROM logs WHERE id > ? LIMIT 20;记录上次最后一条的 id 作为下次查询的起始点，无论翻到多后面性能都一致。 索引策略 查看查询执行计划，确认是否走索引： EXPLAIN QUERY PLAN SELECT * FROM logs WHERE user_id = 123 AND created_at > 1716800000;原则：高频 WHERE 条件字段建索引 复合查询建复合索引（字段顺序与查询条件一致） 低选择度字段（如布尔值）单独建索引意义不大 写入密集型表控制索引数量，每个索引都会拖慢写入其他配置 -- 增大缓存（默认 -2000 即 2MB，建议改大） PRAGMA cache_size=-32000; -- 32MB-- 内存临时表（减少磁盘 I/O） PRAGMA temp_store=MEMORY;-- 预写 WAL 检查点 PRAGMA wal_autocheckpoint=1000;大字段（图片、视频、大 JSON、二进制 Blob）建议存文件系统，数据库只存文件路径，避免单数据库文件膨胀过快。
SQLite 存几十 GB 数据的六个优化点
有个错觉——SQLite 只适合小项目。其实很多人低估了它。理论上 SQLite 单库能到 281 TB，实际几十 GB、一亿行都能稳定跑。前提是这几个优化必须做。 先给个使用边界数据量 SQLite 适不适合< 10 GB 完全没问题，甚至比 Postgres 简单10 GB ~ 100 GB 可以，需要认真优化> 100 GB 谨慎，考虑分片 / 换数据库高并发写入 不推荐，SQLite 写是全库锁（WAL 缓解但仍单写）多机共享文件 别玩，NFS 上的 SQLite 特别容易坏适合 SQLite 的典型场景：本地缓存、日志、爬虫存档、IoT 边缘节点、桌面/Electron 应用、单机分析、中小型业务。 1. WAL 模式（必开） PRAGMA journal_mode = WAL;作用：读和写可以并发（默认 journal 会锁全库） 崩溃恢复快 大量写入性能大幅提升一次性开完之后写进库设置，不用每次连接都设。 2. 调 synchronous PRAGMA synchronous = NORMAL; -- 折中，推荐 -- PRAGMA synchronous = OFF; -- 极限性能，接受掉电丢数据FULL（默认）= 每次事务 fsync 磁盘、NORMAL = 检查点时 fsync、OFF = 完全信操作系统。日志/缓存场景 NORMAL 完全够用，性能差好几倍。 3. 批量事务（提升最猛的一条） 单条 insert： for (const row of rows) { db.prepare("INSERT INTO logs VALUES (?, ?)").run(row.a, row.b); }一亿行你等到明年。改成批量： const insert = db.prepare("INSERT INTO logs VALUES (?, ?)"); const many = db.transaction((rows) => { for (const row of rows) insert.run(row.a, row.b); }); many(rows);性能能差 100 ~ 1000 倍。SQLite 每个事务外面套一次 fsync，逐条 insert 就是每行都 fsync。 4. 索引不要滥建 EXPLAIN QUERY PLAN SELECT * FROM logs WHERE user_id = ? AND ts > ?;索引越多，写入越慢（每行 insert 都要维护索引） 只给高频过滤字段建索引 复合索引按 WHERE 里最常见的过滤顺序建：(user_id, ts) 大文本 / JSON 字段别建普通索引，需要就用 FTS55. 分页别用 OFFSET -- 慢，越翻越慢：OFFSET 需要跳过前 N 行 SELECT * FROM logs ORDER BY id LIMIT 20 OFFSET 1000000;-- 快，游标分页：直接从上一页最后一个 id 之后取 SELECT * FROM logs WHERE id > ? ORDER BY id LIMIT 20;学名叫 Keyset Pagination，任何大表分页都该用。 6. 大字段不要塞 blob 图片、视频、大 JSON 直接扔 SQLite 会拖慢一切——包括那些跟大字段无关的查询。 正确做法：文件系统 / 对象存储放实体 SQLite 只存路径 / 元数据或者用 SQLite 官方推荐 的经验值：< 100 KB 存库里更快，> 100 KB 存文件系统更快。 7. 加上几个 PRAGMA PRAGMA cache_size = -100000; -- 100 MB 缓存（负数表示 KB） PRAGMA temp_store = MEMORY; -- 临时表放内存 PRAGMA mmap_size = 268435456; -- 256 MB mmap，减少 syscall PRAGMA busy_timeout = 5000; -- 遇到锁等 5 秒再报错一句话总结 WAL + 批量事务 + Keyset 分页——这三条打下来，几十 GB 的 SQLite 一点都不难。别忘了大字段挪出去、别乱建索引。
IBM Db2 的定位与应用领域
IBM Db2 是 IBM 的大型关系型数据库，在互联网公司几乎看不到，但在金融、政府和大型机生态中至今仍是核心基础设施。 核心应用领域 银行 / 金融（最主要场景） Db2 在这个领域的地位最为稳固：银行核心账务系统 信贷审批和清算 证券交易撮合 保险核心系统原因是 Db2 在 IBM 大型机（z/OS）上的 ACID 事务能力经过几十年验证，部分银行核心系统已稳定运行 20~30 年，日均处理数亿笔交易几乎不宕机。很多老牌银行的底层组合是 IBM Mainframe + z/OS + Db2，更换成本极高。 政府 / 大型国企 历史上大量政府和国企采购了 IBM 的全套方案（小型机 + AIX + WebSphere + Db2），典型领域：税务系统 社保和医保系统 电信计费 能源调度 铁路订票这类系统生命周期很长，更倾向于稳定性而非新技术，因此沿用至今。 大型机（Mainframe）生态 这是 Db2 最特殊的优势——大多数开发者接触不到 IBM 大型机，但全球大量关键基础设施仍在 Mainframe 上运行：全球 80% 以上的信用卡交易、几乎所有主要航空公司的订票系统、大量跨国银行的清算网络。Db2 在这个生态里几乎是"官方搭档"。 ERP / 企业系统 传统 Java EE 企业项目的经典组合：WebSphere + Db2 + Oracle，在财务、供应链、仓储管理类系统中有相当存量。 为什么互联网公司很少用 互联网公司的优先考量是：开源：避免商业授权成本 社区活跃：遇到问题能快速找到解决方案 云原生：与 Kubernetes、微服务架构配合 弹性伸缩：流量波动大，需要横向扩展Db2 的定价体系较重，运维门槛高，人才市场小，不符合互联网团队的需求。 与 MySQL / PostgreSQL 的定位差异维度 Db2 MySQL / PostgreSQL授权模式 商业授权 开源主要场景 传统企业、大型机 互联网、云原生运维成本 高 低事务处理 极强 强大型机支持 原生 无新项目采用率 低 高现实价值 虽然新项目几乎不选 Db2，但现有存量系统的价值不可忽视：每天处理几亿笔交易、稳定运行 20+ 年、几乎零宕机的系统，才是传统金融机构最看重的能力。Db2 DBA 在银行/证券/保险行业仍是稀缺岗位。
Windows 安装 PostgreSQL：Installer / ZIP 手动安装 / Docker 三种方式
方式一：官方 EDB 安装包（推荐新手） 从 postgresql.org/download/windows 下载 EDB 安装器，双击 .exe 按向导操作：选择组件：PostgreSQL Server + pgAdmin + Command Line Tools（全选即可） 安装路径默认 C:\Program Files\PostgreSQL\16\ 设置 postgres 超级用户密码（这一步要记好，后面连接必须用） 端口保持默认 5432 Locale 保持默认 等待安装完成安装后验证： # 命令行里输入（要先配好 PATH） psql -U postgres -h localhost# 输入密码后看到： postgres=#或者打开 pgAdmin 4 用图形界面管理。 方式二：ZIP 手动安装 适合不想用安装包、想自定义路径的场景。 1. 解压到目标目录，如 D:\pgsql 2. 初始化数据目录： D:\pgsql\bin\initdb -D D:\pgsql\data -U postgres -W-W 参数会提示设置 postgres 用户密码，必须加，否则后续连接会失败。 3. 启动服务： D:\pgsql\bin\pg_ctl -D D:\pgsql\data -l D:\pgsql\logs\postgresql.log start4. 连接验证： D:\pgsql\bin\psql -U postgres -h localhost5. 把 bin 目录加入 PATH： 系统属性 → 环境变量 → Path → 新增： D:\pgsql\bin加好之后才能直接用 psql、pg_ctl 等命令。 EDB 安装包也会自动加 PATH，ZIP 安装需要手动配。 方式三：Docker（开发推荐） 一条命令，跳过所有安装步骤： docker run --name pgsql \ -e POSTGRES_PASSWORD=123456 \ -p 5432:5432 \ -d postgres连接： psql -U postgres -h localhost # 密码：123456停止/启动： docker stop pgsql docker start pgsql开发环境用 Docker 最省事，环境隔离，随时删掉重建。 常见问题 连不上 5432：防火墙没放行 → 允许 PostgreSQL 入站 端口冲突 → 改 postgresql.conf 里的 port = 5433忘记 postgres 密码： 修改 pg_hba.conf，把认证方式临时改成 trust，重启服务后进去改密码，再改回 scram-sha-256。 psql 找不到命令： PATH 没配好。把 PostgreSQL bin 目录加进去，或者用完整路径调用。 版本选择版本 建议PostgreSQL 17 新项目首选（最新稳定版）PostgreSQL 16 最稳定，生产首选PostgreSQL 15 及以下 老项目兼容，新项目不建议关于生产环境 生产环境强烈建议用 Linux（Ubuntu/Debian/Rocky Linux）跑 PostgreSQL：更好的 I/O 调度和文件系统（ext4/xfs） pgBackRest、Patroni 等高可用工具主要支持 Linux 运维脚本生态更成熟Windows 版 PostgreSQL 可以用，但长期高并发稳定性和性能不如 Linux。开发/测试环境没问题，生产环境能用 Linux 就用 Linux。