MySQL开发规范 DB组-张浩.

Presentation on theme: "MySQL开发规范 DB组-张浩."— Presentation transcript:

1 MySQL开发规范 DB组-张浩

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

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

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

5 命名规范 数据库的名称为：业务名称_[业务模块]_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

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

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

8 表设计规范 表样例

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

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

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

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

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

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

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

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

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

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

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

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

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

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

23