MySQL开发规范

Transcription

1 1/ MySQL 数据库设计与开发规范 20 年年 04 月 20 日

2 2/ 变更更记录 日期状态描述作者 20/04/20 创建 v1.0 焦朝阳 版本号 :v1.0

3 3/ 目录 1. 目的 4 2. 范围 4 3. 术语和缩略略语 4 4. 权限管理理规范 5 5. 基础操作规范 5 6. 数据库设计规范 6 7. 表设计规范 6 8. 索引设计规范 8 9.SQL 编写规范 其他 FAQ 表设计规范 FAQ 索引设计规范 FAQ SQL 编写规范 FAQ 16

4 1. 目的 1 规范化和标准化 MySQL 的开发设计 4/ 2 指导数据库开发员, 数据库管理理员合理理使 用 MySQL, 发挥 MySQL 最佳性能 2. 范围 MySQL 数据库的开发和管理理 人员 3. 术语和缩略略语 术语和缩略略语覆盖索引 QPS TPS 描述 SQL 语句句只 用查询 二级索引即可获取查询结果 平均每秒 SQL 语句句执 行行次数 平均每秒事务数 DML 数据操纵语 言, 它们是 SELECT UPDATE INSERT DELETE, 就象它的名字 一样, 这 4 条命令是 用来对数据库 里里的数据进 行行操作的语 言 DDL 数据定义语 言,DDL 比 DML 要多, 主要的命令有 CREATE ALTER DROP 等,DDL 主要是 用在定义或改变表 (TABLE) 的结构, 数据类型, 表之间的链接和约束等初始化 工作上, 他们 大多在建 立表时使 用

5 5/ 4. 权限管理理规范 1 账号申请统 一由运维 人员处理理, 账户名以姓名全拼命名, 默认都为只读账 户, 如需读写权限需单独申请 2 禁 止 root 账号权限在应 用程序总访问数据库 3 按需授权, 一遍应 用程序只授予 DML 权限 4 授权对象最 小化原则, 一般的应 用程序访问按照库级别授权 5. 基础操作规范 1 数据库访问 高峰原则上禁 止做 DDL 操作 2 大表做 DDL 操作尽量量使 用 ONLINE_DDL( 不不低于 MySQL5.6) 或 工具 ( 如 pt) 3 数据库访问 高峰期禁 止对 大表做全表扫描操作 4 更更新和删除操作, 控制单次操作记录数, 建议 一次在 1 万条以下, 单次更更新或删除量量超过 1 万条的建议将 SQL 语句句拆分成多个较 小的语句句, 再提交 5 禁 止开发 人员在 生产系统上做开发测试 备注 : 线上数据库 高峰时段早上 08:30 至 09:30 中午 11:30 至 14:30 下午 :30 至 10:30

6 6/ 6. 数据库设计规范 1 通过业务场景分析和数据库访问 ( 包括数据库读写的 QPS,TPS, 存储空间 ) 的预估, 合理理规划数据库使 用的资源 2 建库原则就是同 一类业务的表放 一个库, 不不同业务的表不不允许公 用 一个库, 禁 止在程序中执 行行跨库关联操作 3 避免将业务逻辑和 DB 耦合在 一起, 数据库存储数据为主, 业务逻辑尽量量通过应 用层实现, 尽可能减少对存储过程, 触发器器, 函数,event, 试图等 高级功能等使 用 4 对读压 力力较 大, 且 一致性要求低 ( 接受数据秒级延迟 ) 的业务场景, 建议使 用数据库读写分离策略略 5 数据库中表的存储引擎类型要 一直, 存储引擎混合使 用会导致主从数据库不不 一致或主从同步中断, 请尽快使 用 Innodb 存储引擎 7. 表设计规范 1 库名, 表名, 字段名尽量量使 用 小些字 母, _ 分隔 FAQ 2 库名, 表名, 字段名尽量量不不超过 12 个字符 FAQ 3 库名, 表名, 字段名 见名知其意 FAQ 4 试图的命名以 v_ 开头 5 建议使 用 InnoDB 存储引擎 FAQ 6 表 一定要有主键

7 7 存储精度数值必须使 用 DECIMAL, 禁 止使 用 FLOAT 和 DOUBLE FAQ 8 建议使 用 UNSIGNED 存储 非负整数 FAQ 9 建议使 用 INTUNSIGNED 存储 IPV4 FAQ 10 整型定义中不不添加 长度, 比如使 用 INT, 而不不是 INT(4) FAQ 11 使 用使 用短数据类型, 比如取值范围在 0-80 时, 使 用 TINYINT UNSIGNED 12 不不建议使 用 EUNUM 类型, 使 用 TINYINT 来代替 FAQ 13 尽可能不不使 用 TEXT,BLOB 类型, 如果 一定要使 用将 TEXT,BLOB 拆分到扩展表 FAQ 7/ 14 VARCHAR(N),N 表示的是字符串串 而不不是字节数, 比如 VARCHAR(255), 可以最 大可存储 255 个汉字, 需要根据实际的宽度来选择 N 15 VARCHAR(N),N 的定义尽可能 小, 因为 MySQL 一个表中的所有 VARCHAR 字段最 大产股是 个字节, 继续排序和创建临时表 一类的内存操作时, 会使 用 N 的 长度申请内存 FAQ 16 表字符集选择 UTF8 或是 GBK FAQ 为每个表中设计两个字段 create_time 和 update_time, 添加时写 入两个字段, 更更新时更更新 update_time, 系统当前时间 18 建议字段定义为 NOT NULL, 并设默认值 DEFAULT FAQ 19 表和字段必须有 comment 中 文注释 20 禁 止在数据库中使 用存储图 片, 文件, 视频等 21禁 止使 用外键 22禁 止使 用临时表

8 8. 索引设计规范 1 非唯 一索引必须按照 idx_ 字段名称 _ 字段名称 [_ 字段名 ] 进 行行命名 8/ 2 唯 一索引必须按照 uniq_ 字段名称 _ 字段名称 [_ 字段名 ] 进 行行命名 3 索引名称必须使 用 小写 4 索引中的字段数建议不不超过 5 个 5 单张表的索引数量量控制在 5 个以内 FAQ 6 唯 一键由 3 个以下字段组成, 并且字段都是整型时, 使 用唯 一键作为主键 7 避免使 用重复索引, 如同 一列列既有主键索引 又有唯 一索引 8 索引字段的顺序需要考虑字段值去重后的个数, 个数多的放在前 面 9 ORDER BY,GROUP BY,DISTINCT 的字段考虑加 入索引避免排序 10 使 用 EXPLAIN 判断 SQL 语句句是否合理理使 用索引, 尽量量避免 extra 列列出现 : Using File Sort,Using Temporary FAQ 11 UPDATE,DELETE 语句句要根据 WHERE 条件添加索引 12 对 长度过 长的 VARCHAR 字段建 立索引时, 添加 crc32 或 MD5 Hash 字段, 对 Hash 字段建 立索引 FAQ 13 合理理创建联合索引 ( 避免冗余 ),(a,b,c) 相当于 (a)(a,b) (a,b,b) 14 合理理利利 用覆盖索引 FAQ 9.SQL 编写规范

9 9/ 1 SQL 语句句中 IN 包含对值不不应过多, 建议不不要超过 1000 个值 2 UPDATE,DELETE 语句句不不使 用 LIMIT FAQ 3 WHERE 条件中必须使 用合适的类型, 避免 MySQL 进 行行隐式类型转化 FAQ 4 SELECT 语句句只获取需要的字段 5 SELECT,INSERT 语句句必须显示的指定字段名称, 不不使 用 SELECT *, 不不使 用 INSERT INTO table_name VALUES() FAQ 6 使 用 SELECT column_name1,column_name2 FROM table WHERE [condition] 而不不是 SELECT column_name1 FROM table WHERE [conditon] 和 SELECT column_name2 FROM table WHERE [conditon] 7 WHERE 条件中的 非等值查询条件 (IN,BETWEEN,<,<=,>,>=) 会导致后 面的条件使 用不不了了索引 8 避免在 SQL 语句句进 行行数学运算或者函数运算 9 INSERT 语句句使 用 batch 提交 (INSERT INTO table VALUES(),(),()..),values 的个数不不应过多 10 避免使 用三表以上的 JOIN 连接查询 11 使 用合理理的 SQL 语句句减少与数据库的交互次数 FAQ 12 不不适应 ORDER BY RAND(), 使 用其他 方法替换 FAQ 13 统计表中的记录数时使 用 COUNT(*), 而不不是 COUNT(primary_key) 和 COUNT(1) 14 不不建议使 用 % 前缀模糊查询, 例例如 LIKE %abc 如果有 大量量的模糊查询, 也最好有覆盖索引 15 避免 大量量的使 用 COUNT(*) 操作, 计数统计实时要求较强的使 用 memcache 或者 redis, 非实时统计可以使 用单独的统计表, 定时更更新

10 10/ 16 在多表连接的查询中, 驱动表必须要选择届国际 比较 小的表 禁 止写成多层 子查询嵌套的 SQL 语句句, 推荐写成表顺序连接的格式 18 禁 止在 INSERT UPDATE DELETE REPLACE 语句句中进 行行多表连接操作 19 SQL 语句句禁 止使 用 SQL 提示, 如 force index,ignore index, straight_join,sql_no_cache 等 20 尽量量避免使 用复杂的 SQL, 将 大 SQL 拆分成多条简单的 SQL 21禁 止使 用物理理删除, 所有操作都应该使 用标志作为删除标记 10. 其他 1 导 入数据时, 尽可能使 用批量量的 方式, 而不不是逐条 INSERT 2 进 行行 大批量量导 入, 导出, 更更新, 删除操作前, 需要通知 DBA( 相关 人员 ) 进 行行审核, 并在执 行行过程中观察服务 3 有些时候, 适当的冗余是必要的, 是为了了能获得更更好的性能和设计 4 业务部 门程序出现问题 bug 等影响数据库的问题, 请及时通知 DBA( 相关 人员 ), 便便于维护服务稳定 11.FAQ 11.1 表设计规范 FAQ

11 11/ 7-1. 库名, 表名, 字段名必须使 用 小写字 母, _ 分隔 a) MySQL 有配置参数 lower_case_table_names, 不不可动态更更改,Linux 系统默认为 0, 即库表名以实际情况存储, 大 小写敏敏感 如果是 1, 以 小写存储, 大 小写不不敏敏感 如果是 2, 以实际情况存储, 但 MySQL 内部对表名单 比较是转成 小写的, 即不不区分 大 小写 b) 如果 大 小写混合 用, 可能存在 abc,abc,abc 等多个表共存, 容易易导致混乱 c) 字段名显示区分 大 小写, 但实际使 用不不区分, 既不不可以建 立两个名字 一样但 大 小写不不 一样的字段 d) 为了了统 一规范, 库名, 表名, 字段名使 用 小些字 母 7-2. 库名, 表名, 字段名建议不不超过 12 个字符 库名, 表名, 字段名 支持最多 64 个字符, 但为了了统 一规范, 易易于辨识以及减少传输 量量, 建议不不超过 12 个字符 7-3. 库名, 表名, 字段名 见名知其意, 建议使 用名词 而不不是动词 a) 用户评论可以 用表名 user_comment 或者 comment b) 库表是 一种客观存在的事物, 一种对象, 所以建议使 用名词 7-5. 建议使 用 InnoDB 存储引擎 a) 5.5 以后的默认引擎为 innodb, 支持事务, 行行级锁, 更更好的恢复性, 高并发下性 能更更好, 对多核, 大内存,ssd 等硬件 支持更更好 b) 具体 比较可参考官 方 白 皮书 7-7. 存储精确浮点数必须使 用 DECIMAL 替代 FLOAT 和 DOUBLE a) mysql 中的数值类型 ( 不不包括整型 ): IEEE754 标准 : 浮点数 :float( 单精度 ),double 或 real ( 双精度 ), 定点数 : decimal 或 numerical 单精度浮点数对有效数字 二进制是 24 位, 按 十进制来说, 是 8 位 ; 双

12 12/ 精度浮点数的有效数字 二进制是 53 位, 按 十进制来说, 是 16 位 b) IEEE754 标准的计算机浮点数, 在内部是 二进制表示的, 但在将 一个 十进制数转 换位 二进制浮点数时也会造成误差, 原因是不不是所有的数都能转换成有限 长度的 二进制数 即 一个 二进制可以精确转换成 十进制, 但是 一个 小书的 十进制不不 一定能够精确低 用 二进制来 表示 实例例 : drop table if exist; create table t(value float(10,2)); insert into values(( ),( )); select value from t; + -+ value 建议使 用 UNSIGNED 存储 非负数值 255 同样的字节数, 存储的数值范围更更 大, 如 tinyint 有符号位 , 无符号为 如何使 用 INT UNSIGNED 存储 ip 使 用 INT UNSIGNED 而不不是 char(15) 来存储 ipv4 地址, 通过 MySQL 函数 inet_ntoa 和 inet_aton 来进 行行转化 Ipv6 地址 目前没有转换函数, 需要使 用 DECIMAL 或者两个 bigint 来存储 例例如 : SELECT INET_ATON ( );

13 / SELECT INET_NTOA ( ); INT[M],M 值代表什什么含义 注意数值类型括号后 面的数字知识表示宽度 而跟存储范围没有关系, 比如 INT(3) 默认 显示 3 位, 空格补 齐, 超出时正常显示,python,java 客户端等不不具备这个功能 不不建议使 用 ENUM 类型, 使 用 TINYINT 代替 a) ENUM, 有三个问题 : 添加新的值需要做 DDL, 默认值问题 ( 将 一个 非法插 入 ENUM( 也就是说, 允许的值之外的字符串串 )), 将插 入空字符串串以作为特殊错误值 ), 索引值问题 ( 插 入数字实际是插 入索引对应的值 ) 实例例 : drop table if exist t; create table t(sex enum( 0, 1 )); insert into t values(1); insert into t values( 3 ); select * from t; + -+ sex 尽可能不不使 用 TEXT,BLOB 类型 a) 索引排序问题, 只能使 用 max_sort_length 的 长度或 手 工指定 ORDER BY SUBSTRING(column,length) 的 长度来排序

14 14/ b) Memory 引擎不不 支持 text,blog 类型, 会在磁盘上 生成临时表 c) 可能浪费更更多的空间 d) 可能 无法使 用 adaptive hash index e) 导致使 用 where 没有索引的语句句变慢 VARCHAR 中会产 生额外内存吗 VARCHAR(M), 如果 M < 256 时会使 用 一个字节来存储 长度, 如果 M >= 256 则使 用 两个字节来存储 表字符集选择 UTF8,GBK a) 使 用 utf8 字符集, 如果是汉字, 占 3 个字节, 但 ASCII 码字符串串还是 一个字节 b) 字符集编码统 一, 不不会有产 生转换产 生乱码 风险 c) 其他地区的 用户 ( 美国, 印度, 台湾 ) 无需安装简体中 文 支持, 就能正常看您的 文字, 并且不不会出现乱码 d) ISO 编码 (latin1) 使 用但字节内的所有空间, 在 支持 ISO 的系统中传输和存储其他任何编码的字节流都不不会被抛弃, 即把其他任何编码的字节流当作 ISO 编码看待都没问题, 保存的是原封不不动的字节流 建议字段定义为 NOT NULL, 并设默认值 a) 如果 null 字段被索引, 需要额外的 1 字节 b) 使索引, 索引统计, 值的 比较变得更更复杂 c) 可 用 0, 代替 d) 如果是索引字段, 一定要定义为 NOT NULL

15 11.2 索引设计规范 FAQ 15/ 8-5 为什什么 一张表中不不能存在过多的索引 InnoDB 的 secondary index 使 用 b+tree 来存储, 因此 UPDATE DELETE INSERT 的时候需要对 b+tree 进 行行调整, 过多的索引会减慢更更新的速度 8-10 EXPLAIN 语句句 EXPLAIN 语句句 ( 在 MySQL 客户端中执 行行 ) 可以获得 MySQL 如何执 行行 SELECT 语句句的信息, 通过对 SELECT 语句句执 行行 EXPLAIN, 可以知晓 MySQL 执 行行该 SELECT 语句句时候使 用了了索引, 全表扫描, 临时表, 排序等信息, 尽量量避免 Mysql 进 行行全表扫描, 使 用临时表, 排序等, 详 见官 方 文档 8-12 如何对超 长的 VARCHAR 字段建 立索引 高效率 ) 下 面的表增加 一列列 url_crc32, 然后对 url_crc32 建 立索引, 减少索引字段的 长度, 提 CREARE TABLE url( url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 BIGINT UNSIGNED NOT NULL DEFAULT 0, index idx_url(url_crc32) 8-14 什什么是覆盖索引 InnoDB 存储引擎中,secondary index( 非主键索引 ) 中没有直接存储 行行地址, 存储主键值 如果 用户需要查询 secondary index 中所不不包含的数据列列时, 需要先通过 secondary index 查到主键值, 然后再通过主键哈下到其他数据列列, 因此需要查两次 覆盖索引的概念就是查询可以通过 一个索引中完成, 覆盖索引效率 比较 高, 主键查询是天然

16 16/ 的覆盖索引 合理理的创建索引以及合理理的使 用查询语句句, 当使 用到覆盖索引时可以获得性能 的提升 11.3 SQL 编写规范 FAQ 9-2 UPDATE,DELETE, 语句句不不使 用 LIMIT 当主备复制采 用 statement 模式时, 可能导致主备数据不不 一致 ( 复制采 用 ROW 模 式, 可以避免此问题 ) 和更更新, 删除不不可控 9-3 为什什么需要避免 MySQL 进 行行隐式类型转化 因为 MySQL 进 行行隐式类型转化之后, 可能会将索引字段类型转化成 = 号右边的类型, 导致使 用不不到索引, 原因和避免在索引字段中使 用函数是类似的 9-5 为什什么不不加 一使 用 SELECT * 增加很多不不必要的消耗 (cpu,io, 内存, 网络宽带 ); 减少了了使 用覆盖索引的可能性 ; 当表结构改变时, 前段代码可能也需要更更新 9-11 减少与数据库的交互次数 使 用下 面的语句句来减少和 db 的交互次数 INSERT INTO values(),() REPLACE 9-12 为什什么不不能使 用 ORDER BY rand() 因为 ORDER BY rand() 会将从数据从磁盘中读取, 进 行行排序, 会消耗 大量量的 IO 和 CPU, 可以在程序中获取 一个 rand 值, 然后通过从数据库中获取对应的值 执 行行 9-20 为什什么避免使 用复杂的 SQL, 将 大 SQL 拆分成多条简单的 SQL 分步

17 / 因为简单的 SQL 容易易使 用到 MySQL 的 query cache; 可以提 高并发度 ; 且更更容易易使 用到索引, 减少锁表时间