卡布达研发团队

# 数据库规范

整理 版本 日期 描述
杨工 1.0.0 2022.06.09 初稿
酥梨 1.0.1 2023.02.10 增加数据库和表字符集统一相关规范
驴打滚 1.0.2 2023.03.17 增加逻辑删除字段 deleted_at 相关规范

# 存储引擎

  • InnoDB在MySQL5.5开始做为默认的存储引擎，支持事务，级别锁，适合高并发布场景，XA协议支持分配事务。
  • MyISAM 不支持事务，性能优先，表级锁，不适合高并发场景。(一般不建议使用)

# 字符集规范

  • 旧表使用CHARSET=utf8 COLLATE=utf8_general_ci
  • 新表使用CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  • join字段字符集不同时，以旧表为准

# 约束

  • 业务开发中不允许直接使用 delete 相关语句删除数据，有需要删除的业务 使用 deleted_at 字段进行逻辑删除
  • 字段注释及表注释清晰可读，避免对接产生歧义

# 基本命名规则

  • 使用有意义的英文词汇，词汇中间以下划线分割
  • 只能使用英文字母、下划线，并以英文字母开头
  • 库、表、字段名全部采用小写，不可使用驼峰命名
  • 禁用 MYSQL 保留字、关键字作为字段名，如 desc、index、order、by、sum、select 等
  • 命名禁止超过 32 个字符，使用名词不是动词
  • 数据库、数据表一律使用前缀
    • 临时库、表名必须以 tmp 为前缀，以日期为后缀
    • 备份库、表以 bak 为前缀，以日期为后缀
Q：为什么库、表、字段全部采用小写？

A：在 MySQL 中，数据库和表对就于那些目录下的目录和文件。因而，操作系统的敏感性决定数据库和表命名的大小写敏感。
    Windows下是不区分大小写的。
    Linux下大小写规则：
    数据库名与表名是严格区分大小写的；
    表的别名是严格区分大小写的；
    列名与列的别名在所有的情况下均是忽略大小写的；
    变量名也是严格区分大小写的；
1
2
3
4
5
6
7
8
9

# 表命名

同一模块的表尽可能使用相同的前缀，表名称尽可能表达含义，不得使用复数名次，因为表名仅仅用来表示表里的实体内容，不应该表示实体数量。

// 排行榜设置审核表
kbd_rank_config_review

// 排行榜查看联系记录表
kbd_rank_contact_record

// 排行榜名次记录表
kbd_rank_record
1
2
3
4
5
6
7
8

# 字段设置规范

  • 字段必须有默认值，不允许设置null为默认值
  • 用尽量少的存储空间来存储一个字节的数据
  • 固定长度的字段使用char,如密码
  • 能使用int类型就不要使用char,varchar
  • 能使用varchar(16)就不要使用varchar(255)
  • 能使用tinyint就不要使用smallint和int

# 字段命名

  • 表达其实际含义的英文单词或简写。布尔意义的字段以“is_”作为前缀，后接动词过去分词。
  • 各表之间相同意义的字段应同名。各表之间相同意义的字段，以去掉模块前缀的表名_字段名命名。
  • 表的主键一般都约定成为 id，自增类型。

# 固定字段

所有表创建时，必须包含下面的字符串段

名 类型 长度 主键 注解
id 整型 11 1 主键（自增）
created_at 整型 11 0 创建时间
updated_at 整型 11 0 更新时间
deleted_at 整型 11 0 删除时间（用于逻辑删除 未删除-0 已删除-删除时间）

注：长途根据实际情况设置
参考文档 Sequelize 实现软删除（paranoid） (opens new window) Hyperf 软删除 (opens new window)

# 索引命名

  • 唯一索引名以 uk_ 为前缀（unique key）。
  • 普通索引名以 idx_ 为前缀（index）。

# MySql 数据库设计原则 (非强制性)

  1. 核心原则
    • 不在数据库做运算
    • cpu计算务必移至业务层
    • 控制列数量(字段少而精,字段数建议在20以内)
    • 平衡范式与冗余(效率优先；往往牺牲范式)
    • 拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch)
  2. 字段类原则
    • 用好数值类型(用合适的字段类型节约空间)
    • 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能)
    • 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效)
    • 少用text类型(尽量使用varchar代替text字段)
  3. 索引类原则
    • 合理使用索引(改善查询,减慢更新,索引一定不是越多越好)
    • 字符字段必须建前缀索引
    • 不在索引做列运算
    • innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了)
    • 不用外键(由程序保证约束)
  4. sql类原则
    • sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库)
    • 保持事务的简单
    • 避免使用trigger/func(触发器、函数),使用程序完成对应的功能
    • 不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性)
    • OR改写为IN(or的效率是n级别)
    • OR改写为UNION(mysql的索引合并很弱智)
       select id from t where phone = ’159′ or name = ‘john’;  
   =>  
   select id from t where phone=’159′  
   union  
   select id from t where name=’jonh’
    1
    2
    3
    4
    5
    • limit高效分页(limit越大，效率越低)
    • 使用union all替代union(union有去重开销)
    • 少用连接join
    • 使用group by
    • 使用同类型字段比较
    • 打散批量更新
  5. 性能分析工具
    • show profile
    • mysqlsla
    • mysqldumpslow
    • explain
    • show slow log
    • show processlist

Apifox 规范 →