MySQL开发规范 DB组-张浩

DB组做些什么事情？ 目前主要负责公司的mysql，redis，oracle，sql server等存储类业务。 DB故障处理支持

目录 命名规范 表设计规范 索引设计规范 SQL语句规范 操作规范

命名规范 总的原则是可读性强，容易维护【建议】 库名，表名，字段名，索引名统一使用小写字母，数字，以下划线分割【建议】 库名，表名，字段名不要超过30个字符长度，需见名知意【建议】 库名，表名，字段名不能单独使用DB的关键字,像lock,time,date,return,user等 【必须】 非唯一索引按照“idx_字段名称[_字段名称]”，唯一索引按照“uniq_字段名称 [_字段名称]”进行命名 【必须】 视图，存储过程，函数命名规范采用动宾结构(动词+名词) [vi|sp|fn]_动作_功能意义(其他同表的命名相同)【建议】 触发器命名规范，tri_表名_[insert|update|delete]_[before|after]，可以简写 【建议】

命名规范 数据库的名称为：业务名称_[业务模块]_db，eg:oms_db,oms_history_db 数据库表的名称为：[类别]_名称，eg:p_product,m_product_map_desc_attr, log_product_base等 业务系统使用数据库账号命名为:业务名称_[r|w],eg： oms_[m|s]_r,oms_[m|s]_w,oms_[m|s]_rw 个人使用数据库账号采用中文名字的拼音，zhanghao@10.37.%

表设计规范 数据库解决的核心问题 顺序，随机IO 1.读，写，事务 2.数据库的瓶颈在磁盘i/0,使sql语句产生尽可能少的i/o是我们关注的核心 顺序，随机IO 1.一次IO=(寻道时间+延迟时间) 2.数据库读数据是以数据块为单位的，mysql默认的数据块大小是16K,随机读一 次1个数据块，顺序读一次可以多个块，比如16个。

表设计规范 核心规则 表中需要有主键【必须】 表的存储引擎选择innodb【必须】 表的字符集选择utf8【必须】 表和字段要有COMMENT【必须】 表的字段数不要超过50个【建议】 控制单表的数据量在5KW以内【建议】

表设计规范 表样例

表设计规范 选择表的类型 普通表-支持事务或非事务【innodb,myisam】 分区表-登陆日志，流水类等【按照天分区，可以数据清理】 简单分表-每天(月)建一个新表统计排行榜[table_20150101] 分库分表-方便扩容，提高并发[table_[00-99]] DB分布式集群-对业务来说就一张逻辑表【mycat】

表设计规范 主键选择 选择主键总原则是小类型，有序【建议】 主键最好和业务无关【建议】 尽量不要选择字符串列【建议】 不能使用UUID,MD5作为主键【必须】 最简单的方法就是使用auto_increment列【建议】 如主键不能满足业务需求，可以使用unique索引约束业务【建议】

表设计规范 表字段设计 所有的字段必须定义为NOT NULL【必须】 字段需要有默认值。数字类型默认值0，字符类型默认''，逻辑类型默认为0， datetime 类型默认为'1970-01-01 00:00:00'，timestamp类型默认值为 CURRENT_TIMESTAMP【必须】 自增序列类型的字段只能使用INT或者BIGINT，使用UNSIGNED存储非负整数 【必须】 不在数据库中使用VARBINARY、BLOB存储图片、文件等【必须】 同一个字段在整个库中要名称一样，类型一样【必须】 表的数据不要直接删除掉，设置删除标记delete_flag字段（tinyint类型，默认值 为0），用来标识字段是否被逻辑删除；【必须】 每个表中默认有create_time,update_time字段,方便追溯【必须】

表设计规范 表字段COMMENT【必须】 1)每张表/视图要有注释（comment），格式为 用途|负责人|创建日期 ，例如： 记录用户身份证号码|张三|2016-03-25； 2)字段要有注释（comment），格式为 用途|负责人|创建日期 ， 例如：记录用 户性别@1:男@0:女@2:未知|李四|2016-03-25 3)如果字段为枚举类型，或普通数据类型当做枚举使用，需要列举枚举范围并说 明每个枚举值的含义 4)关联字段来源需要说明，来自哪张表的那个字段。#取自eload_user.user_id| 李四|2016-03-25； 5)| 用来分割不同项目 ， # 用来标识数据来源 ，@ 用来分割多个枚举 ，: 用来 分割枚举名称和值 ， 正常的描述中不要包含 | # @ :

表设计规范 尽量简单的使用数据类型【建议】 1)使用TINYINT来代替ENUM、SET类型，是否字段 2)存储精确浮点数使用DECIMAL替代FLOAT和DOUBLE 3)数据库中存放IP时，按功能确定字段类型，仅作展示功能的使用CHAR，作为 查询功能的应使用INT类型存放[inet_aton,inet_ntoa] 4)尽可能不使用TEXT、BLOB类型，如果确实需要将大字段拆分到其他表中

表设计规范 用尽量少的空间存储字段。【建议】 1) 能用int的就不用char或者varchar 2) 能用tinyint的就不用int 3) 能用varchar(20)的就不用varchar(255) 4) 年使用YEAR类型，存储日期使用字符类型，存储时间（精确到秒）使用 TIMESTAMP类型或INT【必须】

索引设计规范 索引类型 B+Tree索引，大部分都是，像主键，唯一索引，普通索引 Hash索引，memory引擎支持 全文索引，myisam，innodb5.6以上

索引设计规范 总的原则 索引是双刃剑，过多的索引会影响插入和更新的速度且影响整体性能，一般索 引数量不要超过5个 索引是双刃剑，过多的索引会影响插入和更新的速度且影响整体性能，一般索 引数量不要超过5个 在选择性高的字段是建索引，注意组合索引的顺利，利用索引的最左原则 尽量使用复合索引，而不是添加新的索引

索引设计规范 重要的sql必须使用索引【建议】 区分度最大的字段放在前面【建议】 核心sql优先考虑索引覆盖【建议】 UPDATE、DELETE语句的WHERE条件列 ORDER BY、GROUP BY、DISTINCT的字段 多表JOIN的字段 区分度最大的字段放在前面【建议】 核心sql优先考虑索引覆盖【建议】 直接从索引获取数据，不需要回表 select后面的字段和where条件的字段都是索引字段，一般是复合索引 在explain的Extra列出现Using Index提示时，就说明该select查询使用了覆盖 索引

索引设计规范 避免冗余索引【必须】 idx_a_b_c(a,b,c) idx_a(a) idx_a_b(a,b) 索引禁忌 不要在低选择性字段上建立索引，例如“性别”【建议】 不能在索引列上使用数学运算和函数运算【必须】 避免在索引列上用not、<>、!=反逻辑 不要在null的列上建索引

SQL语句规范 不能使用select *,select 后面只跟要使用的字段【必须】 避免索引失效【必须】 消耗CPU和IO、消耗网络带宽 无法使用覆盖索引 减少表结构变更带来的影响 避免索引失效【必须】 数据类型转换 使用like时，%不要放在首字符位置 不使用拼字符串的方式来完成where子句 表字段不能有表达式或是函数

SQL语句规范 充分利用前缀索引 减少与数据库的交互次数 避免使用存储过程、触发器、函数等 必须是最左前缀 不可能用到两个范围条件 INSERT ... ON DUPLICATE KEY UPDATE REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),() UPDATE … WHERE ID IN(A,B,C,…) 避免使用存储过程、触发器、函数等 让数据库做最擅长的事 降低业务耦合度 避开BUG

SQL语句规范 sql语句改写 拒绝大SQL，拆解成多条简单SQL，优先解决高并发的 SQL 同一字段，将or改写为in，不同字段，将or改为union all 用Where子句替换HAVING子句 NOT EXISTS代替关联及NOT IN效率更高，因为在子查询中执行了局部范围 扫描 拒绝大SQL，拆解成多条简单SQL，优先解决高并发的 SQL update和delete语句后面一定要带where条件[必须] 习惯性的使用explain分析sql语句

操作类规范 不在业务高峰期批量更新、查询数据库【必须】 对可能导致服务流量增长的线上活动，提前告知DBA进行 压力评估和扩容 【强烈建议】 对线上数据库结构的任何变更，均走正常流程 【必须】 任何数据库相关的BUG，及时告知DBA 【建议】 项目中详细注明常用SQL语句，便于DBA进行优化 【必须】 不能循环的执行条件相同的SQL【必须】