数据库开发规范

#  --- 数据库开发设计规范 --- #
1),MySQL 
2),PG
3),Redis
4),MongoDB
5),ES

# MySQL：
1.MySQL数据库选择
【强制】当前建议版本：MySQL 8.0+ (LTS版本)

【建议】字符集
character-set-server = utf8mb4  
collation-server = utf8mb4_bin

【强制】事务引擎
default_storage_engine = innodb

【建议】事务
tx_isolation = READ-COMMITTED
transaction_isolation = READ-COMMITTED 

【建议】大小写不明感
lower_case_table_names = 1 

【强制】SQL模式
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

【建议】timestamp时间戳设置
explicit_defaults_for_timestamp = off
1，timestamp 可以用datetime 替代。
2，如果不想使用datetime 替代，那么就要显式指定：  
   a, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 仅插入时自动   
   b, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 插入和更新都自动  
   c,  custom_time TIMESTAMP NULL DEFAULT NULL    -- 完全手动控制不能直接 
   -- create table t( c1 timestamp) ,或者 c1 timestamp(3) 这样的， 都需要按以上a,b,c 需求定义好。

2.1.1 库名
【强制】库的名称必须控制在32个字符以内，相关模块的表名与表名之间尽量体现join的关系，如user表和user_login表。
【强制】库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀。
【强制】一般分库名称命名格式是库通配名_编号，编号从0开始递增，比如wenda_001以时间进行分库的名称格式是“库通配名_时间”
【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。
        创建数据库SQL举例：create database db1 default character set utf8mb4;。
        
2.1.2 表结构
【强制】表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写。
【强制】表名要求模块名强相关，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。
【强制】创建表时必须显式指定字符集为utf8或utf8mb4。MySQL8.0后建议一律utf8mb4
【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。
【建议】建表时关于主键：
       (1)强制要求主键为id，类型为int或bigint，且为auto_increment
       (2)标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部page分裂和大量随机I/O，性能下降。
【建议】核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。
【建议】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

【建议】分表命名：
             年表1: table_name_2025。
             年表2： table_name_(a...b)_2025。
             月表1：table_name_2025(01...12)。
             月表2：table_name_(a...b)_2025(01...12)。
             日表1：table_name_2025(0101...1231)。
             日表2：table_name_(a...b)_2025(0101...1231)。
【建议】备份表统一 以 bak_时间戳为后缀( t_table_name1表 -> bak_t_table_name1_20251231)
 2.1.3 列数据类型优化
【建议】表中的自增列（auto_increment属性），推荐使用bigint类型。因为无符号int存储范围为-2147483648~2147483647（大约21亿左右），溢出后会导致报错。
【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。
【建议】业务中IP地址字段推荐使用int类型，不推荐用char(15)。因为int只占4字节，可以用如下函数相互转换，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间。     
        SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); 
【建议】不推荐使用enum，set。 因为它们浪费空间，且枚举值写死了，变更不方便。推荐使用tinyint或smallint。
【建议】不推荐使用blob，text等类型。它们都比较浪费硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而浪费内存空间，影响系统性能。
【建议】字段不建议定义字符集,如一定要定义字符集,使用utf8mb4,MySQL8.0.30 后,utf8 显示转换为utf8mb3。
【建议】业务数据 不建议大小写混用 eg: abc和aBc 在字符集排序为非*_bin 模式下,默认转化为小写识别。
【建议】字段定义默认值,不推荐使用default null, 建议使用特定字段代替。eg: default '', default 0, default 特定值。mysql中 null不等于'',是一个特别的存在。
【强制】云数据库使用utf8mb4, 所以对应的字符类型varchar 字段定义<191,否然针对该字段创建创建索引会报767。
【建议】不建议使用外键约束，最好通过程序限制，包括触发器等。

2.1.4 索引设计
【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新。
【建议】主键的名称以“pk_”开头，唯一键以“uk_”或“uq_”开头，普通索引以“idx_”开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。
【强制】单个索引中每个索引记录的长度不能超过64KB。
【建议】单个表上的索引个数不能超过7个,资源索引不重复(有些字段创建了uk,没必要在创建idx)。
【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)计算出来。
【建议】在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。
【建议】建表或加索引时，保证表里互相不存在冗余索引。对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。
【建议】mysql8.0 之后，mysql 支持函数索引eg: order by create_time desc ,直接在创建索引时创建。

一个规范的建表语句示例参考：
CREATE TABLE t_user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT '用户id',
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT '用户邮箱',
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
  `create_time` timestamp NOT NULL COMMENT '用户记录创建的时间',
  `update_time` timestamp NOT NULL COMMENT '用户资料修改的时间',
  `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB AUTO_INCREMENT =1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='网站用户基本信息';

# PostgreSQL:
【强制】库名、表名、列名、函数名等必须全小写，使用下划线分隔。
【强制】对象名长度不超过 63 个字符（PG 内部硬限制），建议表名控制在 32 字符以内。
【强制】严禁使用 SQL 保留字（如 select, order, group 等），可通过 pg_get_keywords() 查询。
【强制】禁止使用中文、美元符号 $、双引号 "（除非必须包含空格或大写），禁止以 pg_ 开头。
【建议】不建议在默认的 public schema 下建业务表，应为每个应用分配独立 Schema（如 schema_user, schema_order）。
            PG实例 ->库 -> 模式schema ->表
【强制】建表、建字段必须带 COMMENT。建议使用英文或 UTF-8 中文，避免 pg_dump 时因编码不一致导致乱码。

表结构与数据类型设计：
【强制】主键：每张表必须有主键（Primary Key）或唯一非空约束。推荐使用自增 BIGINT 或 UUID（UUID 需设为 VARCHAR(32) 或原生类型）。
【强制】NOT NULL：所有业务字段尽量定义为 NOT NULL，并设置合理的 DEFAULT 值（如 DEFAULT '', DEFAULT 0），避免 NULL 参与计算和索引带来的性能损耗。
【强制】外键处理：生产环境禁止使用物理外键（Foreign Key Constraint）。关系完整性由应用层保证，仅在逻辑上保持字段名和类型一致。
【强制】触发器：禁止使用触发器（Trigger）。触发器会导致逻辑隐蔽、调试困难及性能下降，业务逻辑应全部上移至应用层。

数据类型选型：
【强制】字符集：数据库必须使用 UTF-8 编码。
【强制】字符串类型：
            长度不固定：使用 TEXT 或 VARCHAR(N)。禁用 CHAR(N)（PG 中 TEXT 和 VARCHAR 性能无差异，CHAR 反而有填充开销）。
           大小写不敏感：使用 CITEXT 类型，而非 LOWER() 函数。
【强制】数值类型：
         整数：优先用 INTEGER (4字节) 或 BIGINT (8字节)。金额/高精度小数必须用 NUMERIC(20,4)，严禁用 FLOAT/DOUBLE。
         IP/MAC：使用原生 INET, CIDR, MACADDR 类型，支持范围查询且节省空间。

【推荐】JSONB：半结构化数据必须使用 JSONB（二进制存储，支持 GIN 索引），禁止使用 JSON（文本存储，每次查询需重新解析）。
【强制】时间类型：统一使用 TIMESTAMP(3)（毫秒精度）或 TIMESTAMPTZ（带时区）。禁止使用 TIMESTAMP WITHOUT TIME ZONE 除非明确业务场景。

大表与分区：
【强制】分表阈值：单表预估超过 500万~1000万行 或容量超过 10GB，必须考虑分区表或归档策略。
【强制】分区版本：必须使用 PG 12+ 的声明式分区（Declarative Partitioning），低版本在更新/删除时会扫描所有分区，性能极差。
【建议】HOT 更新：对于频繁更新的表，建表时指定 WITH (fillfactor = 85)，预留 15% 空间给 Heap-Only Tuple 更新，减少索引膨胀。

索引设计规范：
【强制】索引命名：
            主键：pk_<表名>
            唯一索引：uk_<表名>_<字段名>
            普通索引：idx_<表名>_<字段名>
【强制】长度限制：B-Tree 索引字段建议不超过 2000 字节。超长字段如需索引，使用 Hash 函数索引 或 分词索引（pg_trgm / zhparser）。
【建议】联合索引：遵循 最左前缀 原则。将区分度高、等值查询频繁的字段放在最左侧。
【建议】覆盖索引：对于高频查询，尽量利用 INCLUDE 子句创建覆盖索引，避免回表。
【强制】冗余检查：严禁存在冗余索引（如已有 (a,b)，再建 (a) 即为冗余）。
【建议】部分索引：对于状态字段（如 status = 'ACTIVE'），使用 WHERE status = 'ACTIVE' 创建部分索引，大幅减小索引体积。

# Oracle数据库开发设计建议规范:
略

# Redis数据库：
1.连接建议
【建议】连接机制：
前端程序连接 Redis 必须要有连接超时和失败重连机制。失败重试必须设置间隔时间，避免雪崩效应。
【建议】连接池配置：
必须根据业务需要配置连接池的 初始连接数、最小连接数、最大连接数。
必须配置 超时时间以及连接回收机制，防止连接耗尽。
【建议】错误处理：
前端程序报错里应尽量提示 Redis原生态的报错信息，便于排查问题。
【建议】热点数据缓存：
对于单表读写比大于10:1的数据行或单个列，建议将热点数据放在缓存里（如 Redis），以降低 MySQL 压力。

键设计规范
【强制】命名格式：使用冒号 : 分隔的层级结构，建议格式为 <业务名>:<子业务名>:<唯一标识>。
示例：user:login:token:123 或 sz:order:detail:20260610 (深圳订单业务)。

【强制】长度控制：Key 的长度应控制在 32 个字符以内，避免占用过多内存。
【强制】禁止包含特殊字符：除了冒号，避免使用空格、换行符或中文。

服务库规范
【建议】不同服务建议选择不同库，同时避开默认0库。

2.2 值（Value）存储规范
【建议】数据结构选型：
String：最常用，适合存储 JSON 字符串、序列化对象、Token 等。
Hash：适合存储对象（如用户信息），便于修改对象的单个字段。
Set/List：用于存储集合数据（如粉丝列表、消息队列）。
Sorted Set：用于排行榜、带权重的队列。

【强制】大 Key 治理：
禁止存储单个 Value 超过 500KB 的数据（大 Value 会导致网络阻塞和 GC 压力）。
禁止在一个 Hash/Set 中存储超过 5000 个元素（大集合会导致 KEYS 或 HGETALL 命令阻塞主线程）。

【建议】序列化格式：建议使用 JSON 或 Protobuf，不推荐使用 Java 原生序列化（跨语言兼容性差）。

2.3 过期策略与淘汰
【强制】必须设置过期时间：所有缓存 Key 必须显式设置 TTL（Time To Live），严禁出现永不过期的缓存（防止内存溢出）。
【建议】过期时间随机化：在设置过期时间时，增加一个随机值（如基础时间 + 0~300秒随机），防止缓存雪崩（大量 Key 同时失效）。
 eg: 10.36.99.4  redis 凌晨00：00 - 00：05 之间，因业务设计，在此过程中存在大量key 过期，导致cpu等负载较高(雪崩), 同时txy redis cdb 一个实例只配置1个CPU。
【强制】生产禁止使用keys * , scan 太频繁操作。
 
# MongoDB
MongoDB规范
基础命名和结构规范
【强制】数据库名小写，长度64字符以内，禁止 / \ . " $ 等特殊字符。
【强制】集合名 小写或驼峰，长度64字符以内，禁止以 system. 开头。命名格式：<模块>_<实体> (如 order_detail)。
【建议】尽量简短32字符内，因为 MongoDB 会在每个文档中存储字段名，过长会浪费存储和内存。
【强制】_id 设计推荐使用默认的 ObjectId。禁止自定义 _id 值，除非有明确业务需求，自定义 _id 会显著降低插入性能。

MongoDB 的设计哲学是 “查询优先”，而非关系型数据库的“范式优先”。
【建议】优先嵌入：
            数据总是一起读取（如：订单 + 商品明细）。
            子文档数量有限且稳定（建议 < 100 个元素）。
            需要单文档原子性更新。
            优势：减少 Join，提升读性能，保证局部性。

【建议】使用引用：
          子文档无限增长（如：用户操作日志、评论列表）。
          子文档被多处共享（如：商品分类、标签）。
          子文档需要独立频繁更新（如：用户头像、昵称）。
          优势：避免 16MB 文档限制，减少冗余，更新成本低。

 数组设计陷阱
【强制】禁止无限增长数组：不要将日志、消息队列等无界数据存入数组字段。
【建议】数组大小缓存：如果需要根据数组长度排序或过滤，必须在文档中增加一个 count 字段手动维护，禁止使用 $size 运算符（无法利用索引）。
【建议】避免对大数组建索引：对包含 1万+ 元素的数组创建 Multikey Index 会导致严重的写入放大和索引爆炸。

索引与性能优化
【强制】索引数量控制：单个集合索引数建议10个内，最好5个内。每次 Insert/update 都会同步更新所有索引，过多索引在数据写入时加大成本。
【强制】复合索引顺序：遵循 ESR 原则：
             Equality (等值匹配字段)
             Sort (排序字段)
             Range (范围查询字段)
【建议】覆盖索引：尽量让查询只扫描索引，不回表（Projection 只包含索引字段）。
【建议】后台建索引：生产环境必须使用 background: true 或批量 createIndexes，防止阻塞业务写入。
【强制】排序限制：内存排序默认限制 32MB，超过会报错。必须为排序字段建索引，或使用 allowDiskUse: true（但会慢）。

 分片与高可用设计：
【强制】分片时机：建议在集合达到 256GB(约上亿条数据) 之前完成分片规划。
【强制】分片键选择：
            必须是高基数（High Cardinality），避免热点。
           必须包含在所有查询条件中，避免 Scatter-Gather（广播查询）。
           一旦确定，不可修改（4.4+ 虽支持变更但风险极大）。
【建议】单分片容量：单个 Shard 的数据量建议控制在 2TB 以内，便于恢复和平衡。

 连接与驱动规范
【强制】连接池：
             严禁短连接（每次请求新建连接）。使用官方最新驱动的连接池。
             maxPoolSize 建议 ≤ 200，根据 CPU 核数和线程模型调整。
             
【建议】批量操作：
            写入/更新必须使用 BulkWrite API。
           每批大小控制在 1000 ~ 5000 条。
           推荐 ordered: false (无序)，单条失败不影响整批，且性能更高。
【建议】按需返回：find() 和 aggregate() 必须指定 Projection，禁止 select *，减少网络带宽和内存开销。

 
# ES规范：
【强制】索引命名 必须小写，下划线隔开 格式推荐：<业务>_<模块>_<环境> (如 order_log_prod)。
【建议】索引名和字段名长度不超过 32 个字符，避免内部元数据膨胀。
【强制】禁止以.开头（系统保留），禁止以 _开头（内部字段保留），禁止大写字母。
【建议】生产环境必须使用别名（Alias）进行读写，便于后续 Reindex 或滚动升级时无感切换。
【强制】ES 7.x+ 已废弃 Type，严禁创建多 Type，统一使用 _doc。

Mapping 设计核心原则:
【强制】关闭动态映射：生产环境必须显式定义 Mapping，设置 dynamic: false 或 strict。
        原因：防止脏数据写入导致字段类型自动推断错误（如数字被推断为 String），引发聚合失败或内存爆炸。

字段类型选型:
【强制】Text vs Keyword：
             Keyword：用于精确匹配、聚合、排序（如 ID、状态码、标签、IP）。不分词。
             Text：仅用于全文检索（如标题、内容）。必须指定分词器（中文推荐 ik_max_word / aliws）。
             组合策略：若字段既需搜索又需聚合，使用 fields 多字段特性：
"title": {
  "type": "text",
  "analyzer": "ik_max_word",
  "fields": {
    "keyword": { "type": "keyword", "ignore_above": 256 }
  }
}

【强制】数值与日期:
            整数优先用 integer (4字节) 而非 long (8字节)，节省内存。
            时间必须用 date 类型，格式统一为 yyyy-MM-dd'T'HH:mm:ss.SSSZ。
【建议】Boolean：布尔值会建立倒排索引，适合高基数过滤，但不支持 Range 查询。

【建议】分片规划：
          单分片大小：30GB ~ 50GB。
          单节点分片数：≤ 20（每 GB 堆内存 ≤ 20 分片）。
          主分片数：根据数据总量和节点数计算，不要盲目设大（如 20+），Rebalance 成本极高。
          参考： 节点数量按照1.5~3倍的原则来创建分片. 例如,如果你有3个节点, 则推荐你创建的分片数最多不超过9(3x3)个.