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
行业拓展
分享一个面向研发人群使用的前后端分离的低代码软件——JNPF。
基于 Java Boot/.Net Core 双引擎,它适配国产化,支持主流数据库和操作系统,提供五十几种高频预制组件,内置了常用的后台管理系统使用场景和实用模版,通过简单的拖拉拽操作,开发者能够高效完成软件开发,提高开发效率,减少代码编写工作。
JNPF 基于 SpringBoot+Vue.js,提供了一个适合所有水平用户的低代码学习平台,无论是有经验的开发者还是编程新手,都可以在这里找到适合自己的学习路径。
此外,JNPF 支持全源码交付,完全支持根据公司、项目需求、业务需求进行二次改造开发或内网部署,具备多角色门户、登录认证、组织管理、角色授权、表单设计、流程设计、页面配置、报表设计、门户配置、代码生成工具等开箱即用的在线服务。
评论