Karp

[收藏] 别人公司DB 的 MYSQL数据库开发规范文档
原文地址 : https://www.cnblogs.com/lihaoyang/p/14168525.html规...
扫描右侧二维码阅读全文
19
2022/07

[收藏] 别人公司DB 的 MYSQL数据库开发规范文档

原文地址 : https://www.cnblogs.com/lihaoyang/p/14168525.html

规范 - mysql开发规范

MySQL安全

账号分类

  1. 管理员帐户(DBA专用)
  2. 运维类帐户(复制,监控,备份等)
  3. 程序用账户(根据业务命名,权限最小化原则.命名规则: 业务_rw, 业务_r)
  4. 只读用账户(根据内外网决定是否启用SSL)

开发权限

  1. 程序帐户权限:

*_rw账号权限: SELECT,INSERT,UPDATE,DELETE,SHOW VIEW
*_r账号权限: SELECT,SHOW VIEW,EXECUTE(不改写数据)

其它安全规范

  1. 禁止使用程序帐号通过客户端访问数据库,使用OPS系统
  2. 连接串中密码必须加密
  3. 生产环境限定帐户的Host为内网段IP。例: au_business_r@’10.10.7.%’
  4. 权限默认给5+2(可选)种: insert,delete,update,select,show view; execute,mysql.proc select权限
  5. DB服务器禁用外网IP。如果需要外网访问使用ip+port转发方式并使用SSL加密数据
  6. 禁用链接服务器(FEDERATED引擎)
  7. 禁止drop表,可以rename tablebackupdb数据库,后续删除
  8. 删除无用账号

SQL上线流程

脚本化

描述清楚本次上线的目的和步骤
必须以脚本的形式提供给DBA,无法脚本化时写出详细步骤
必须限定到某个数据库: ip+port+database
不同的mysql实例,不同的业务,拆分成多个脚本。目的:清晰,出错易排查,易回滚
DDLDML脚本分开。先执行DDL,后执行DML
需要按顺序执行的脚本,在脚本名或脚本中指定顺序
涉及到原子操作,必须显示声明事务(begin; … commit;)
对同一个表的多次DDL操作合并为一次操作(更改主键除外)

邮件模板

业务描述:XXX上线【备注和注意事项也写在这里】

执行时间:现在/2018-06-01 22:00/研发通知

mysql: 192.168.1.1:3306 【注:还可能有sqlserver等】

db: au_business 【数据库名称】

SQL(共3步): 【注: 如有步骤,请描述,需要考虑回滚方案】

第1步:检查xxx正确性,备份xxx表

select count(*) as cnt from tb_city where city_id in(1,2); #2条记录则正常

第2步:更新xxx的值

begin;

update tb_city set city_name='北京', city_type='5' where city_id = 1;

update tb_city set city_name='深圳', city_type='5' where city_id = 2;

commit;

第3步:增加xxx商品

insert into tb_city(city_name, city_type) values('上海', '5'), ('香港', '1'); #尽量批量提交

注:

  1. 附件较大时请压缩
  2. 附件名称可加1,2,3备注
  3. 数据库有多个时,SQL脚本中加上use dbname;

Review

  1. 先了解清楚脚本的目的和步骤
  2. 检查脚本是否符合数据库开发规范
  3. 检查脚本是否存在bug(主要针对复杂SQL)
  4. 提出合理建议,总结规范
  5. DDL语句的审核提前到开发,提测阶段

上线

  1. 了解Move in相关的数据库情况,确保不影响生产:库大小,表大小,是否存在replication,是否锁表等
  2. 耗时操作,尽量分段执行脚本,有意外情况,及时回滚
  3. 业务相关INSERT,DELETE,UPDATE,SELECT通过OPS平台操作,其余通过DBA执行
  4. OPS平台支持不了的功能,提交给DBA来完成

MysQL设计与开发规范

设计规范

1.【必须】库与库之间解耦,不允许跨库查询(dbname.tablename),同一DB实例上的库与库之间的查询写成多条SQL。数据库由于性能原因迁移时,程序只用修改连接串。
2.【必须】不同业务间的数据交互统一通过接口进行,不使用复制技术
3.【建议】复制技术仅用于读写分离,高可用,报表平台数据同步等

1.【必须】库名、表名、字段名,全部小写(mysql参数 :lower_case_table_names=1),使用26个英文字母,下划线,数字。只能以英文字母开头, 不超过32个字符。须见名知意,命名与业务、产品线等相关联。库命名:根据业务起名,表命名:业务名称_表作用。如:risk_service.tb_risk_config。测试环境库名需与线上保持一致,多套DB环境时,后缀加数据区分:risk_service_2
2.【必须】统一单数形式,如订单表:order,反例:orders
3.【强制】数据库表、字段必须加入中文注释
4.【必须】库名、表名、字段名禁止使用MySQL保留字
5.【建议】字段允许适当冗余,减少JOIN,遵循规则:1. 不是频繁修改的字段2.不是varchar超长字段
6.【建议】大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据
7.【建议】临时性的数据或者生命周期很短的数据等信息,不放在数据库
8.【建议】监控分析类日志数据不建议存储在MySQL上,优先考虑非关系型数据库或文件中,如需要与DBA评估使用压缩表存储字段

  1. 关键业务需要加history表。除了保存修改前的所有字段,还需要增加修改人,修改时间,修改类型(update,delete)等至少3个字段。
    10.【建议】单表超过500W行或容量超过2G, 才考虑分库分表,或归档处理

11.【建议】表设计时常问3个问题:表是否是核心业务数据?表读写频率?数据量大小,是否可归档?
12.【必须】临时库、表名必须以tmp为前缀,并以日期(20170101)为后缀,用完立刻删除。
13.【必须】备份库、表必须以bak为前缀,并以日期(20170101)为后缀,可以加上其它说明。
14.【必须】临时表,备份表存储到备份库(backupdb),定期清除。
15.【建议】总体原则:冷热分离,减少JOIN,读写状态,考虑并发

字段

1.【必须】所有字段必须not null + default约束,减少三值逻辑。特殊情况与DBA确认。
2.【必须】同一业务字段在不同的表中的类型必须一致,防止JOIN时发生类型转换。名称必须一致,自增列除外,自增列统一命名id。
3.【必须】字段占用字节越小越好,尽量用数字类型,用tinyint代替enum类型
4.【建议】Varchar长度不允许超过5000,如果超长,定义为text,需要与DBA确认
5.【建议】尽可能不用text,blob类型,确定需要找DBA确认
6.【必须】禁止使用float, double类型,用decimal, int等替代
7.【必须】根据业务情况定义varchar长度,尽量不超过255
8.【建议】禁止使用varchar类型作为主键语句设计
9.【必须】长度不变用char,否则统一用varchar,长度不超过255
10.【必须】status, type等字段类型,范围不超过正负255,统一用tinyint
11.【必须】表自增列名称必须为id,类型为int/bigint,步长为1。
12.【建议】不使用unsigned类型,统一使用有符号类型int/bigint
13.【建议】表必有create_time, 人为触发添加数据的表必须有create_user字段,有数据修改的表必须有update_time字段, 人为触发修改数据的表必须有update_user字段,类型为datetime, 更新数据表记录时,必须同时更新相关的update_time,update_user值。
14.【建议】字段必有注释,字段含义变更时需要维护字段注释。建议格式为: 1=正常; 2=异常/不可用; 3=删除

1.【必须】表必须有主键,可用自增列做主键。业务键作主键需要考虑性能
2.【必须】禁用外键约束,由程序实现数据完整性
3.【必须】业务上需要进行唯一性约束的,必须加唯一键

索引

1.【建议】一个索引中的字段数建议不超过5个,一张表中的索引数一般不超过10个
2.【建议】建表时加上可预见的索引
3.【必须】选择性高的字段放在前面,不在低基数列上建立索引(比如:性别字段)
4.【必须】优化复合索引中的字段顺序
5.【必须】避免冗余和重复索引
6.【必须】重要的SQL才加索引
7.【必须】仅调试时允许使用 FORCE INDEX
8.【必须】索引命名规范:索引:ix_field1_field2;唯一索引:uix_field1_field2;主键:默认(PRIMARY)
9.【建议】varchar字段创建索引,字段较长时需要指定索引长度: CREATE INDEX ix_name ON customer(name(10));
10.【建议】适当使用覆盖索引来优化查询,避免回表。主要针对高并发或查询数据量比较大的情景。
11.【注意】如果有 order by 的场景,请注意利用索引的有序性。反例:WHERE a>10 ORDER BY b; 索引a_b 无法排序

其它对象

1.【必须】禁止使用MySQL存储过程,函数,触发器,定时事件,视图。

开发规范

编程

1.【必须】SQL必须指定列名操作,禁止用。COUNT()除外。
2.【必须】注意SQL的数据类型,where条件左右两边数据类型需一致,不一致时,强制转换不走索引的那一端,以防出现隐式转换,导致索引不可用
3.【必须】SQL中同一字段OR条件改用IN(),IN包含的值少于50
4.【必须】应用程序应有捕获SQL异常的处理机制
5.【建议】需要走索引的条件,禁止在where 条件的过滤字段使用函数或表达式处理。where ltrim(name)=’test’; where date_format(now(), '%Y-%m-%d')= '2018-01-01'
6.【建议】不使用负向查询(NOT, <>)和%开头的模糊查询,如果需要走搜索引擎来解决
7.【建议】避免使用子查询
8.【建议】拒绝复杂SQL,将大的SQL拆分成多条简单SQL
9.【建议】书写格式统一缩进
10.【必须】使用表别名,SELECT列表中的列必须带上表别名
11.【必须】事务要简单,整个事务的时间长度不能太长
12.【必须】更新或删除时,先写SELECT语句,再改成update,delete语句
13.【必须】能用union all就不要用union,注意逻辑不一样
14.【必须】禁止一个update同时更新多张表
15.【必须】对同一个表的多次DDL操作合并为一次操作
16.【建议】不建议使用子查询,建议将子查询转换成JOIN查询
17.【必须】不要使用 count(列名)count(常量)来替代count()count()SQL92 定义的.标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关
18.【注意】count(distinct col) 计算该列除 NULL 之外的不重复行数, 注意 count(distinct col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0
19.【注意】当某一列的值全是 NULL 时, count(col)的返回结果为 0,但 sum(col)的返回结果为NULL,可以如下: SELECT IF(ISNULL(SUM(g)), 0, SUM(g)) FROM table_name;
20.【注意】NULL值与任何值比较结果都是NULL
21.【必须】线上程序中不建议使用truncate table语法。
22.【建议】不建议超过3张表的JOIN
23.【建议】能串行处理SQL,批量串行处理,不使用多线程。
24.【建议】关系型数据库适合批量处理数据,不建议一条一条处理数据。
25.【必须】只能使用inner/left/ JOIN … ON …写法,不使用tableA,tableB where…写法。
26.【必须】SQL即逻辑。

分页查询

1.【必须】精确分页:计算记录总条数与详细记录查询分两种SQL写。因为计算总条数时,只用count(*),且可以不用关联不必要的表。

2.【建议】精确分页:详细记录查询,先取出分页记录的id主键,再关联其它。正例:

select
 a.order_number,
 a.order_flag
from sale_order a
inner join
(
 select id
 from sale_order
 where order_time > '2017-01-01'
 limit 100000, 20
) b
 on a.id=b.id

3.【建议】在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句

性能规范

调优

1.在只读服务器(10.12.1.1)上使用explain调优SELECT语句,update&delete语句也可以改成select

故障

如果出现业务部门人为误操作,需要恢复数据,请在第一时间通知 DBA,并提供准确时间点,误操作语句,日志等信息。请提供日志文本,不要截图

DB运维规范

参数配置

1.必须使用InnoDB

  1. 符集必须使用UTF8或存储emoji表情时使用UTF8MB4

复制

  1. 复制帐户host限定为具体IP
  2. 复制master->slave的数据库名必须相同,slave上复制库只能存放复制对象
  3. 复制数据库必须同名,且slave上复制库中除了从master复制过来的对象,不能再有其它对象。
最后修改:2022 年 07 月 19 日 03 : 20 AM

发表评论