MySQL查询建表规范

因为之前一直再查找一些比较好的数据库规范，以方便在开发时连接 MySQL 进行查询/建表的时候，能根据规范来执行，达到提高 查询速度 / 执行 SQL 的性能 和提升 MySQL 的整体性能， 这里主要是存放一些比较好的一些数据库设计规范（主要用了公司某位同事整理的数据库规范，已得到该大佬的授权），我在此基础上增补了部分规则。
1. 基本规范

• 使用 innodb 存储引擎 后续使用 TokuDB 存储引擎
  
• 表字符集使用 UTF8mb4
  
• 所有表和表字段必须添加备注，表备注格式为：创建人，创建时间，表的业务说明
  
• 所有表必须添加主键和自增 id，自增列无业务意义也需添加
  
• 生产 mysql 数据库不允许存储图片和文件等大数据
  
• 禁止在线上数据库进行压力测试
  
• 数据量较大的表建议建分区表
  
• 禁止使用外键 允许逻辑意义上的外键依赖
  
• 能用代理账号链接数据库的尽量使用代理账号链接，方便运维那边以后做数据库迁移工作
  
• 如果只涉及到查询操作的，使用只查权限的账号建立链接，例如：Superset 等bi系统建立报表的查询
  
• 一般情况下线上业务使用的数据库账号为写的账号，因为线上业务一般不涉及到对字段/表定义更改操作，管理权限权限账号一般只用于自己开发本地连接
  
• 申请/创建 数据库的时候一般使用主主配置即可，即一个库作为写主库，一个库作为读主库
  
• 修改测试库 ddl 级别的 需要提交脚本至升级文档 升级时一起执行脚本 确保测试库和正式库统一
  

2. 命名规范

• 表名建议带上业务英文名的，第一个字母 +"_" 开头，例如:data_service 业务，表名应该都是以 d_开头，控制在 15 个字符以内
  
• 表名字符禁止超过 32 个字符
  
• 库名、表名、字段名禁止使用 MySQL 保留字
  
• 临时库、表名必须以 tmp 为前缀，并以日期为后缀
  
• 备份库、表必须以 bak 为前缀，并以日期为后缀
  
• 操作日志类型的库、表建议以log为后缀
  
• 普通索引命名：idx_开头 + 字段名（单阶字段用全字段名，双阶或者三阶使用前阶首字母加最后阶的全拼，例如：create_time 索引命名为：idx_ctime）
  
• 唯一索引命名：uniq_开头 + 字段名 方法参考二级索引命名
  

3. 数据表设计规范

• 自增 id 必须添加 unsigned
  
• 字段设置 not null 的必须有默认值
  
• 少用 text 类型，尽可能改用 varchar，禁止使用 blob 类型，实在避免不了 blob，请拆表
  
• 存储非负整数需添加 unsigned
  
• 禁止使用 FLOAT 和 DOUBLE 存储浮点数，改用 DECIMAL 存储
  
• 尽可能不使用 default null，非必要使用 null，建议改用 default 0 或 default '' (以免使用查询时候null 值情况查询需要另外处理)，同时也不建议使用 default ''
  
• 所有 type 和 status 等业务字段使用 TINYINT 类型，禁止使用 varchar，避免不了 varchar 的情况下则使用 enum 类型
  
• 使用 datetime 存储时间（mysql5.6 以后）
  
• 用好数值类型字段，如果数值字段没那么大，就不要用 bigint
  
• 禁止在数据库中存储明文密码，把密码加密后存储
  
• 能使用分区表尽量使用分区表
  
• 表设计前必须评估字段的字长，避免没必要的空间浪费和性能损耗
  
• 建议单表字段数控制在 20 个以内，再多建议垂直分表
  
• 外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制
  
• 所有表必须有 create_time 和 update_time，并添加触发器（CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP ON UPDATE）
  

四。索引规范

• 表必须有主键（聚集索引）
  
• 不使用 UUID MD5 HASH 这些作为主键 (数值太离散)
  
• 默认使非空的唯一键作为主键
  
• 一般自增列为主键，分区表的分区键可以与自增列做复合索引
  
• 单索引字段数不超过 5 个
  
• 优先考虑覆盖索引
  
• 避免冗余和重复索引
  
• 避免使用基数低的字段做索引，例如：性别、类型、状态
  
• 低基数字段建议根据 where 谓词条件建立复合索引
  
• ORDER BY、GROUP BY、DISTINCT、WHERE 后字段需根据实际情况建立索引
  
• 索引字段的默认值不能为 NULL
  
• 能使用唯一索引就要使用唯一索引，但要求是字长不能超 8 位和不离散的字段，超 8 位的和离散字段，使用前缀索引
  

5. SQL 开发规范

• 代码中禁止使用 select *
  
• 不使用标量子查询，尽可能改外连接
  
• 表关联控制在 3 个表内
  
• 表关联字段尽可能使用主键
  
• 禁止大表做子查询
  
• 表关联排序字段必须在驱动表内
  
• 表关联字段必须有索引
  
• 不使用标量子查询，尽可能改为 left join 或者 right join
  
• 不使用 in/not in/exists/not exists 子查询，尽可能改为 inner join
  
• 禁止大表与大表做 join
  
• OR 改写为 IN () 或者 UNION
  
• 使用 union all 替代 union
  
• 分页需改写为如下：select id,text from test limit 10000,10; --> select b.id,b.text from (select id from test a limit 10000,10) left join test b on a.id=b.id
  
• 不建议使用前缀是 % 的 like（无法使用索引）
  
• 不建议 where 条件里 in （子查询），即使 in 的是索引字段  参考
  

6. 建表实例

1_ddl_新建 demo 表.sql

1. CREATE TABLE `c_demo` (
2.   `id` bigint (11) unsigned NOT NULL AUTO_INCREMENT,
3.   `name` varchar (50) NOT NULL default '' COMMENT ' 名称 ',
4.   `creator` varchar (50) NOT NULL  default ''  COMMENT ' 创建者 ',
5.   `is_deleted` tinyint (2) NOT NULL DEFAULT '0' COMMENT ' 是否逻辑删除 0. 否 1. 是 ',
6.   `create_time` datetime NOT NULL DEFAULT '2020-01-13 00:00:00' COMMENT ' 创建时间 ', -- 这里是为了兼容MySQL 5.6 之前的版本，5.6 之前的版本不能同时建两个 带有 CURRENT_TIMESTAMP 触发的字段
7.   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' 更新时间 ',
8.   PRIMARY KEY (`id`),
9.   KEY `idx_name_deleted` (`name`,`is_deleted`)
10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='zhangsan-2020.1.3 - 测试案例  '

复制代码

参考

• 知乎上关于“数据库设计时的一些细节的东西如何处理?”的问答
  
• 阿里的 Java开发手册（嵩山版）.pdf
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！