规范 - 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 table到backupdb数据库，后续删除
  8. 删除无用账号

SQL上线流程

脚本化

  1. 描述清楚本次上线的目的和步骤
  2. 必须以脚本的形式提供给DBA，无法脚本化时写出详细步骤
  3. 必须限定到某个数据库: ip+port+database
  4. 不同的mysql实例，不同的业务，拆分成多个脚本。目的：清晰，出错易排查，易回滚
  5. DDL和DML脚本分开。先执行DDL，后执行DML
  6. 需要按顺序执行的脚本，在脚本名或脚本中指定顺序
  7. 涉及到原子操作，必须显示声明事务(begin; … commit;)
  8. 对同一个表的多次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评估使用压缩表存储字段
  9. 关键业务需要加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

故障

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

DB运维规范

参数配置

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

复制

  1. 复制帐户host限定为具体IP
  2. 复制master->slave的数据库名必须相同，slave上复制库只能存放复制对象
  3. 复制数据库必须同名，且slave上复制库中除了从master复制过来的对象，不能再有其它对象。

​
欢迎关注公众号交流学习，会分享更多项目实践以及学习资料：