mysql数据库开发规范【推荐】

最近一段时间一边在线上抓取SQL来优化，一边在整理这个开发规范，尽量减少新的问题SQL进入生产库。今天也是对公司的开发做了一次培训，PPT就不放上来了，里面有十来个生产SQL的案例。因为规范大部分还是具有通用性，所以也借鉴了像去哪儿和赶集的规范，但实际在撰写本文的过程中，每一条规范的背后无不是在工作中有参照的反面例子的。如果时间可以的话，会抽出一部分或分析其原理，或用案例证明。

一. 命名规范

1.库名、表名、字段名必须使用小写字母，并采用下划线分割

(1)MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。

(2)如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。

(3)字段名显示区分大小写，但实际使⽤时不区分，即不可以建立两个名字一样但大小写不一样的字段。

(4)为了统一规范， 库名、表名、字段名使用小写字母。

2.库名以 d 开头，表名以 t 开头，字段名以 f_ 开头

(1)比如表 t_crm_relation，中间的 crm 代表业务模块名

(2)视图以view_开头，事件以event_开头，触发器以trig_开头，存储过程以proc_开头，函数以func_开头

(3)普通索引以idx_col1_col2命名，唯一索引以uk_col1_col2命名（可去掉f_公共部分）。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)

3.库名、表名、字段名禁止超过32个字符，需见名知意

库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符

4.临时库、表名须以tmp加日期为后缀

如 t_crm_relation_tmp0425。备份表也类似，形如 _bak20160425 。

5.按日期时间分表须符合_YYYY[MM][DD]格式

这也是为将来有可能分表做准备的，比如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了这种规范。
不具有时间特性的，直接以 t_tbname_001 这样的方式命名。

二. 库表基础规范

1.使用Innodb存储引擎

5.5版本开始mysql默认存储引擎就是InnoDB，5.7版本开始，系统表都放弃MyISAM了。

2.表字符集统一使用UTF8

(1)UTF8字符集存储汉字占用3个字节，存储英文字符占用一个字节

(2)校对字符集使用默认的 utf8_general_ci

(3)连接的客户端也使用utf8，建立连接时指定charset或SET NAMES UTF8;。（对于已经在项目中长期使用latin1的，救不了了）

(4)如果遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集

3.所有表都要添加注释

(1)尽量给字段也添加注释

(2)类status型需指明主要值的含义，如”0-离线，1-在线”

4.控制单表字段数量

(1)单表字段数上限30左右，再多的话考虑垂直分表，一是冷热数据分离，二是大字段分离，三是常在一起做条件和返回列的不分离。

(2)表字段控制少而精，可以提高IO效率，内存缓存更多有效数据，从而提高响应速度和并发能力，后续 alter table 也更快。

5.所有表都必须要显式指定主键

(1)主键尽量采用自增方式，InnoDB表实际是一棵索引组织表，顺序存储可以提高存取效率，充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。

(2)需要全局唯一主键时，使用外部发号器ticket server（建设中）

(3)如果没有主键或唯一索引，update/delete是通过所有字段来定位操作的行，相当于每行就是一次全表扫描

(4)少数情况可以使用联合唯一主键，需与DBA协商

6.不强制使用外键参考

即使2个表的字段有明确的外键参考关系，也不使用 FOREIGN KEY ，因为新纪录会去主键表做校验，影响性能。

7.适度使用存储过程、视图，禁止使用触发器、事件

(1)存储过程（procedure）虽然可以简化业务端代码，在传统企业写复杂逻辑时可能会用到，而在互联网企业变更是很频繁的，在分库分表的情况下要升级一个存储过程相当麻烦。又因为它是不记录log的，所以也不方便debug性能问题。如果使用过程，一定考虑如果执行失败的情况。

(2)使用视图一定程度上也是为了降低代码里SQL的复杂度，但有时候为了视图的通用性会损失性能（比如返回不必要的字段）。

(3)触发器（trigger）也是同样，但也不应该通过它去约束数据的强一致性，mysql只支持“基于行的触发”，也就是说，触发器始终是针对一条记录的，而不是针对整个sql语句的，如果变更的数据集非常大的话，效率会很低。掩盖一条sql背后的工作，一旦出现问题将是灾难性的，但又很难快速分析和定位。再者需要ddl时无法使用pt-osc工具。放在transaction执行。

(4)事件（event）也是一种偷懒的表现，目前已经遇到数次由于定时任务执行失败影响业务的情况，而且mysql无法对它做失败预警。建立专门的 job scheduler 平台。

a.单表数据量控制在5000w以内

b.数据库中不允许存储明文密码

三. 字段规范

1.char、varchar、text等字符串类型定义

(1)对于长度基本固定的列，如果该列恰好更新又特别频繁，适合char

(2)varchar虽然存储变长字符串，但不可太小也不可太大。UTF8最多能存21844个汉字，或65532个英文

(3)varbinary(M)保存的是二进制字符串，它保存的是字节而不是字符，所以没有字符集的概念，M长度0-255（字节）。只用于排序或比较时大小写敏感的类型，不包括密码存储

(4)TEXT类型与VARCHAR都类似，存储可变长度，最大限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储（row_format=dynamic），对它的使用需要多一次寻址，没有默认值。

一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。

网上部分文章说要避免使用text和blob，要知道如果纯用varchar可能会导致行溢出，效果差不多，但因为每行占用字节数过多，会导致buffer_pool能缓存的数据行、页下降。另外text和blob上面一般不会去建索引，而是利用sphinx之类的第三方全文搜索引擎，如果确实要创建（前缀）索引，那就会影响性能。凡事看具体场景。

另外尽可能把text/blob拆到另一个表中

(5)BLOB可以看出varbinary的扩展版本，内容以二进制字符串存储，无字符集，区分大小写，有一种经常提但不用的场景：不要在数据库里存储图片。

2.int、tinyint、decimal等数字类型定义

(1)使用tinyint来代替 enum和boolean
ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆
tinyint使用1个字节，一般用于status,type,flag的列

(2)建议使用 UNSIGNED 存储非负数值
相比不使用 unsigned，可以扩大一倍使用数值范围

(3)int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别

(4)使用Decimal 代替float/double存储精确浮点数
对于货币、金额这样的类型，使用decimal，如 decimal(9,2)。float默认只能能精确到6位有效数字

3.timestamp与datetime选择

(1)datetime 和 timestamp类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范围比 DATATIME 要小。

(2)timestamp可以在insert/update行时，自动更新时间字段（如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但一个表只能有一个这样的定义。

(3)timestamp显示与时区有关，内部总是以 UTC 毫秒 来存的。还受到严格模式的限制

(4)优先使用timestamp，datetime也没问题

(5)where条件里不要对时间列上使用时间函数

4.建议字段都定义为NOT NULL

(1)如果是索引字段，一定要定义为not null 。因为null值会影响cordinate统计，影响优化器对索引的选择

(2)如果不能保证insert时一定有值过来，定义时使用default ‘' ，或 0

5.同一意义的字段定义必须相同

比如不同表中都有 f_user_id 字段，那么它的类型、字段长度要设计成一样

四. 索引规范

1.任何新的select,update,delete上线，都要先explain，看索引使用情况

尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线。
explain解读

(1)type：ALL,index,range,ref,eq_ref,const,system,NULL（从左到右，性能从差到好）

(2)possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

(3)key：表示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

(4)ref：表示选择 key 列上的索引，哪些列或常量被用于查找索引列上的值

(5)rows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

(6)Extra

a.Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
b.Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

1.索引个数限制

(1)索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的

(2)单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。

2.避免冗余索引

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!