Mysql - 数据库开发使用规范
一. 设计规范
1. 基础规范
- 数据库引擎
• 数据库引擎默认使用Innodb。 - 数据库字符集
• 新建库需统一使用utf8mb4字符集编码，旧库逐渐取消其他编码类型。 - 基本规范
• 所有命名均使用小写字母，且命名（索引除外）长度不超过30字符
• 禁止使用Mysql保留关键词
• 命名需使用关联字符时，使用“_” 下划线分割。
• 单库不超过500个表
• 单表字段不超过30字符
• 表和字段需要添加注释
2. 命名规范
- 数据库命名规范：
• 根据业务线名称或者缩写进行命名：尽可能说明业务 - 表命名规范：
• 表命名尽可能表述清晰
• 日志类，homework_log_202010
• 分表命名,散表名+下划线+数字： homework_01。 - 字段命名规范：
• 字段名称简明 - 索引命名规范：
• 主键命名：pk_ 前缀 pk_uid
• 唯一键命名：uk_ 前缀 uk_uid
• 普通索引：idx_前缀 多索引“_”进行链接 idx_uid_dateline_age
3. 设计规避
- 禁止使用存储过程，触发器，视图，Event
• 业务逻辑与DB容易耦合，迁移等操作
• 存在隐藏的bug - 禁止使用VARBINARY，BLOB 存储文件和视频等
• 采用其他文件系统更为合适
• 性能低下 - 禁止在数据库中存储明文密码
• 需加密且不可解析 - 建议字段值定义为NOT NULL
• 用0或者’’以及其他默认值代替
• NULL被索引增加1字节，且统计不准确
• NULL尽量规避索引 - 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE
• 业务逻辑与DB容易耦合，迁移等操作
• 存在隐藏的bug - 建议使用UNSIGNED 存储非负数值
• 同样的字节数，存储的数值范围更大 - 建议使用INT UNSIGNED 存储IPV4
• 使用函数inet_ntoa和inet_aton来进行转化
• IPV6 用DECIMAL或者两个bigINT来存储 - 使用TINYINT来代替ENUM 类型
• ENUM隐藏弊端 - SQL 中避免出现now()、rand()、sysdate()、current_user() 等不确定结果的函数
• 引起主从不同步
• 不确定的函数，sql无法使用query cache
10.存储时间（精确到秒）建议使用TIMESTAMP类型或者int unsigned
• DATETIME 使用8个字节，TIMESTAMP使用4个字节
• #Timestamp 存储上限到2038年
• #存储日期使用DATE类型，存储年使用YEAR类型
11.VARCHAR(N)根据实际需要，N尽可能小
• N表示的是字符数不是字节数，最大255
• varchar最长65535个字节，排序和创建临时表一类的内存操作时，会使用N的长度申请内存
12.字段值过大时，需要单纯拆分为表
• 加快主表查询速度
二.使用规范
1. 索引使用规范
- 避免在低基数字段上使用索引，不在Null字段上使用索引
• 如：性别，值为null等 - 禁止%为前导的查询
• 如：like ‘%123’等 - 建议不使用Select *
- 禁止使用外键
- 建议单张表索引不超过5个，单个索引字段不超过5个
• 合理使用索引，禁止索引冗余
• 如：idx_a_b_c = idx_a & idx_a_b & idx_a_b_c - 禁止使用字符串前缀索引
- 组合索引顺序
• 把区分度高的字段放在前面
• 如：where a = 10000 and b=2 - 禁止在varchar过长的字段上建立索引
- 使用Explain 检测执行
2. SQL 使用规范：
- 建议使用预编译语句：
• prepared statement,只传参数，比sql效率高
• 一次解析，多次使用
• 避免sql注入 - Sql 语句中IN包含的值不能过多：
• 不能多个in
• in 值最大不能超过100 - Sql 语句中Select和Insert必须显示的指明字段名称：
• 不能使用select * 或者 insert table
• 会增加硬件资源消耗，减少索引覆盖的可能性 - 不在索引列进行函数或者数学运算：
• 如：where max(age) = 1 - 避免使用大表的Join
• 尽可能使用2张表join，小表在前，大表在后
• 避免多表join - Update,Delete语句中禁止使用limit
• 可能导致主从数据不一致
• 会记录到错误日志，导致日志占用大量空间 - Where 条件中使用合适类型，避免隐式转换
• 如：id=123 id=‘123’ - 减少数据库的交互次数
• INSERT … ON DUPLICATE KEY UPDATE
• REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
• UPDATE … WHERE ID IN(A,B,C,…)
• 根据具体情况而定 - 禁止SQL语句过大，拆分成小SQL
10.不同字段，将or改为union all，减少对不同字段进行 “or” 查询
• 用union all而不是union
• merge index 比较弱
11.复杂统计和查询不可使用线上库，原则上线上线下库隔离使用。
• 线下库联系大数据提供
12.程序端禁止使用set 全局设置语句
• set names、set sql_mode和set isolation_level 等
3. 查询使用规范
- Innodb 引擎表禁止使用Select count(*)
• 推荐使用 Select count(pkey),select count(1)
• 非实时统计建议使用单表，定期更新 - IN和OR的选择，推荐使用IN
• IN的效率更高，不能多个IN同时使用。
• IN的值尽可能少，最大不超过100个。 - Where 子句条件中，索引和组合索引前不能使用函数或算法
• 会导致索引失效 - Where 子句中禁止使用全模糊LIKE 查询，必须有其他索引条件查询。
- 禁止使用Order by rand
• 磁盘读取数据排序，消耗设备资源
• 建议在程序端解决rand问题 - 禁止多层嵌套子查询
• 建议写成顺序表链接格式 - 查询结果集不宜过大，最大不超过2000 条
• 结果集过大请分段获取。
• 超大数据请联系大数据处理。 - 合理使用LIMIT 分页，提高分页效果
• LIMIT 10000,10, 数据量过大时会导致IO问题
• 推荐使用方法（SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.，
SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as
t USING(id)）
• 其他程序端方法实现。 - 禁止在线上库中查询复杂业务
• 线下数据库中查询
4. 写入使用规范：
- 【避免】在INSERT|UPDATE|DELETE|REPLACE 等语句中进行多表操作。
• 如：insert …select from xxx,update xxx where xx in (select xxx) - INSERT 必须指明字段
• 如：insert (a,b) value (a,b) - INSERT,UPDTE,DELETE批量操作
• 每次操作行数不超过1000行，
• delete不超过100行
• 每次执行sleep 1s，（具体根据实际设备性能情况确定） - 批量操作规范：
- 业务中大表扫描或者全表导出，需要在备份库执行
- 【禁止】Load data 批量导入
• 小批量（1000）insert，间隔1s - INSERT,UPDTE,DELETE 批量操作
• 大批量操作在用户较少时操作
三.DB执行规范
- 不在业务高峰期批量更新、查询数据库【必须】
- 对重要的库表，提前沟通确定维护和备份的优先级
- 对可能导致服务流量增长的线上活动，提前告知进行压力评估和扩容 【强烈建议】
- 发生数据库数据被恶意篡改、数据丢失等情况，及时通知进行修复
- 对线上数据库结构的任何变更，均走正常流程 【必须】
- 禁止在从库上执行后台管理和统计类型功能的查询，必要时添加单独的统计类从库
四.流程执行规范
- 数据库用户名和权限规范
• 在线业务账号申请
A. 写账号：业务名 homework
• 跨业务线在线账号申请
B. 写账号：业务名+需要使用账号的业务 homework_ky - 业务用户只授权增、删、改、查操作权限
- 数据库相关账号不能出现在jira、微信，网络等公开的软件或者系统中。
五、大数据业务需求
1、所有新增字段必须添加在表的末尾
2 、【强制】表必备三字段：id, ctime, utime。
说明：
a. id必为主键 ,单表时自增、步长为1。
b. ctime,utime的类型均为date_time类型，前者为 CURRENT_TIMESTAMP，后者为CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP。
c. 禁止update set ctime/utime。ctime/utime作为每行的时间记录，只允许自动生成，禁止手动去
d. 推荐 utime字段建索引
3.【推荐】enum/tinyint在comment中详细注明所有的值，以及对应的含义。
4.【推荐】内容为json格式的，使用json字段类型可以避免脏数据。
3783
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
