MySQL数据库设计与开发规范

MySQL数据库设计与开发规范

1.目的：

1.规范化和标准化MySQL的开发设计
2.指导数据库开发员，数据库管理理员合理理使 用MySQL，发挥MySQL最佳性能

2. 范围:

MySQL数据库的开发和管理理 人员

3.术语和缩略语:

术语和缩略略语	描述
覆盖索引	SQL语句句只 用查询 二级索引即可获取查询结果
QPS	平均每秒SQL语句句执 行行次数
DML	数据操纵语 言，它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字 一样，这4条命令是 用来对数据库操作
DDL	数据定义语 言，DDL 比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是 用在定义或改变表（TABLE）的结构

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
下午 17:30 至 10:30

6.数据库设计规范:

通过业务场景分析和数据库访问（包括数据库读写的QPS，TPS，存储空间）的预估，合理理规划数据库使 用的资源
1.建库原则就是同 一类业务的表放 一个库，不不同业务的表不不允许公 用 一个库，禁 止在程序中执 行行跨库关联操作
2.避免将业务逻辑和DB耦合在 一起，数据库存储数据为主，业务逻辑尽量量通过应 用层实现，尽可能减少对存储过程，触发器 ，函数，event，试图等 高级功能等使 用
3.对读压 力力较 大，且 一致性要求低（接受数据秒级延迟）的业务场景，建议使 用数据库读写分离策略略
4.数据库中表的存储引擎类型要 一直，存储引擎混合使 用会导致主从数据库不不 一致或主从同步中断，请尽快使 用 Innodb 存储引擎

7.表设计规范:

1.库名，表名，字段名尽量量使 用 小些字 母，“_”分隔【FAQ】
2.库名，表名，字段名尽量量不不超过12个字符【FAQ】
3.库名，表名，字段名 见名知其意【FAQ】
4.试图的命名以 v_开头
5.建议使 用InnoDB 存储引擎【FAQ】
6.表 一定要有主键
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】
VARCHAR(N)，N表示的是字符串长度 而不是字节数， 比如VARCHAR(255)，可以最 大可存储255个汉字，需要根据实际的宽度来选择 N
VARCHAR(N)，N的定义尽可能小，因为MySQL 一个表中的所有VARCHAR
字段最 大产股是 65535个字节，继续排序和创建临时表 一类的内存操作时，会使 用 N 的 长度申请内存【FAQ】
14.表字符集选择UTF8 或是 GBK【FAQ】
15.为每个表中设计两个字段 create_time和update_time，添加时写 入两个字段，更更新时更更新update_time，系统当前时间
16.建议字段定义为NOT NULL，并设默认值 DEFAULT【FAQ】
17.表和字段必须有 comment 中 文注释
18.禁 止在数据库中使 用存储图 片， 文件，视频等
禁止使用外键
禁止使用临时表

8.索引设计规范:

1.非唯 一索引必须按照 “idx_字段名称字段名称[字段名]” 进行命名
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编写规范:

1.SQL语句句中 IN 包含对值不不应过多，建议不要超过 1000 个值
2.UPDATE，DELETE 语句句不不使 用LIMIT【FAQ】
3.WHERE 条件中必须使 用合适的类型，避免 MySQL进 行行隐式类型转化【FAQ】
4.SELECT 语句句只获取需要的字段
5.SELECT，INSERT 语句句必须显示的指定字段名称，不不使 用 SELECT * ，
6.不使用 INSERT INTO table_name VALUES()【FAQ】
7. 使 用 SELECT column_name1,column_name2 FROM table WHERE [condition] 而不不是 SELECT column_name1 FROM table WHERE [conditon] 和 SELECT column_name2 FROM table WHERE [conditon]
8.WHERE条件中的 非等值查询条件（IN ，BETWEEN，< ，<=，>，>=）会导致后 面的条件使 用不不了了索引
9. 避免在SQL语句句进 行行数学运算或者函数运算
10.INSERT 语句句使 用 batch 提交（INSERT INTO table VALUES(),(),()…..）,values的个数 不不应过多
11.避免使 用三表以上的 JOIN 连接查询
12. 使 用合理理的 SQL 语句句减少与数据库的交互次数【FAQ】
13. 不不适应ORDER BY RAND（），使 用其他 方法替换【FAQ】
14. 统计表中的记录数时使 用 COUNT(*)， 而不不是 COUNT(primary_key)和COUNT（1）
15.不不建议使 用%前缀模糊查询，例例如 LIKE “%abc”。如果有 大量量的模糊查询，也最好有覆盖索引
16.避免大量量的使 用COUNT(*)操作，计数统计实时要求较强的使 用 memcache 或者 redis , 非实时统计可以使 用单独的统计表，定时更更新
17.在多表连接的查询中，驱动表必须要选择届国际 比较 小的表
18.禁止写成多层 子查询嵌套的 SQL语句句，推荐写成表顺序连接的格式
19.禁止在INSERT UPDATE DELETE REPLACE 语句句中进 行行多表连接操作
20.SQL语句句禁 止使 用 SQL提示，如 force index ，ignore index， straight_join,sql_no_cache 等
21.尽量量避免使 用复杂的SQL，将 大SQL拆分成多条简单的SQL
禁止使 用物理理删除，所有操作都应该使 用标志作为删除标记

10.其他:

1.导入数据时，尽可能使 用批量量的 方式， 而不不是逐条 INSERT
2.进 行行 大批量量导 入，导出，更更新，删除操作前，需要通知 DBA（相关 人员）进 行行审核，并在执 行行过程中观察服务
3.有些时候，适当的冗余是必要的，是为了了能获得更更好的性能和设计
4.业务部 门程序出现问题 bug 等影响数据库的问题，请及时通知DBA（相关 人员），便便于维护服务稳定

11.FAQ:

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位双

精度浮点数的有效数字 二进制是 53位，按 十进制来说，是16位。

b) IEEE754 标准的计算机浮点数，在内部是 二进制表示的，但在将 一个 十进制数转换位 二进制浮点数时也会造成误差，原因是不不是所有的数都能转换成有限 长度的 二进制数。即 一个 二进制可以精确转换成 十进制，但是 一个 小书的 十进制不不 一定能够精确低 用 二进制来表示

实例例：

drop table if exist;

create table t(value float(10,2));

insert into values((131072.67),(131072.68));

select value from t;

+———-+

| value |

+———+

| 131072.67 |

| 131072.69 |

+———+

7-8.建议使 用 UNSIGNED 存储 非负数值

同样的字节数，存储的数值范围更更 大，如 tinyint 有符号位 -128 - 127， 无符号为 0 -

255

7-9.如何使 用 INT UNSIGNED 存储 ip

使 用 INT UNSIGNED 而不不是 char(15)来存储 ipv4 地址，通过 MySQL 函数

inet_ntoa 和 inet_aton 来进 行行转化。Ipv6地址 目前没有转换函数，需要使 用 DECIMAL 或者

两个 bigINT 来存储。例例如：

SELECT INET_ATON ( ‘209.207.224.40’ );

13!/17!

3520061480

SELECT INET_NTOA ( 3520061480 );

209.207.224.40

7-10.INT[M]，M 值代表什什么含义

注意数值类型括号后 面的数字知识表示宽度 而跟存储范围没有关系， 比如INT(3)默认显示 3 位，空格补 齐，超出时正常显示，python，java 客户端等不不具备这个功能

7-12.不不建议使 用 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 |

+———+

| 0 |

| |

+———+

7-13.尽可能不不使 用 TEXT ，BLOB 类型

a) 索引排序问题，只能使 用 max_sort_length 的 长度或 手 工指定 ORDER BY

SUBSTRING（column，length）的 长度来排序

b) Memory 引擎不不 支持text，blog类型，会在磁盘上 生成临时表

c) 可能浪费更更多的空间

d) 可能 无法使 用 adaptive hash index

e) 导致使 用 where 没有索引的语句句变慢

7-15. VARCHAR 中会产 生额外内存吗

VARCHAR(M)，如果 M < 256 时会使 用 一个字节来存储 长度，如果M >= 256 则使 用两个字节来存储

7-16. 表字符集选择 UTF8 ，GBK

a) 使 用 utf8 字符集，如果是汉字，占3个字节，但 ASCII码字符串串还是 一个字节

b) 字符集编码统 一，不不会有产 生转换产 生乱码 风险

c) 其他地区的 用户（美国，印度，台湾） 无需安装简体中 文 支持，就能正常看您的 文字，并且不不会出现乱码

d) ISO-8895-1 编码（latin1）使 用但字节内的所有空间，在 支持 ISO-8895-1 的系统中传输和存储其他任何编码的字节流都不不会被抛弃，即把其他任何编码的字节流当作

ISO-8895-1 编码看待都没问题，保存的是原封不不动的字节流

7-18. 建议字段定义为 NOT NULL，并设默认值

a) 如果 null 字段被索引，需要额外的 1 字节

b) 使索引，索引统计，值的 比较变得更更复杂

c) 可 用 0 ，’’ 代替

d) 如果是索引字段， 一定要定义为 NOT NULL

15!/17!

11.2 索引设计规范FAQ

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 查到主键值，然后再通过主键哈下到其他数据列列，因此需要查两次。
覆盖索引的概念就是查询可以通过 一个索引中完成，覆盖索引效率 比较 高，主键查询是天然
的覆盖索引。合理理的创建索引以及合理理的使 用查询语句句，当使 用到覆盖索引时可以获得性能的提升。

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分步执行
因为简单的SQL容易易使 用到 MySQL的 query cache；可以提 高并发度；且更更容易易使 用到索引，减少锁表时间