MySQL开发规范

开发设计篇

1   命名规范

库名、表名、字段名必须使用小写字母，禁止使用MySQL保留字，并采用下划线分割。

1.1   数据库命名规范

数据库名使用项目名或项目名缩写小写英文及下划线和环境类型组成

备份数据库名使用正式库名加上备份时间组成,如:

dbname_20170406

数据库创建时，显示指定字符集

1.2   数据库表命名规范

数据表名使用小写英文以及下划线组成(项目名+表信息)，并且创建数据库时，显示指定默认字符集

备份数据表名使用正式表名加上备份时间组成,如:

1.3   字段命名规范

字段名称使用单词组合小写完成,单词之间用“_”分隔，最好是带表名前缀

自增id最好创建与业务无关。

表与表之间的相关联字段要用统一类型和名称

1.4   索引命名规范

索引名称为idx_表名_列名缩写，唯一索引名称为unidx_表名_列名缩写,其中表名和关联字段名如果过长，可以取表名、关联字段名的前5 个字母，如果表名、关联字段为多个单词组合，可以取前一个单词，外加后续其它单词的首字母作为字段名

 

注意事项：

单个索引长度不超过4KB

单个表索引个数不超过整张表字段数的20%左右。

1.5   数据库应用用户命名规范

用户命名规则，分为四段，每段规则如下

第一段：环境名(d:develop,t:test,u:uat,p:product,a:awr)

第二段：项目名首字母

第三段：连接源(pc:桌面终端，web:程序)

第四段：权限(sel:select，dml:select，insert，update，delete)

2   建表规范

1)  创建表时需添加表级别和列级别注释，显示指定存储引擎、默认字符集、自增开始值，id列无特殊要求，建议使用int值；

2)  如无说明，则表中的第一个id字段一定是主键且为自动增长；

解读：

Ø  主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用；

Ø  主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率；

Ø  无主键的表删除，在row模式的主从架构，会导致备库夯住。

3)  如无说明，则建表字符集统计一使用UTF8；

解读：

Ø  UTF8字符集存储汉字占用3个字节，存储英文字符占用一个字节；

Ø  校对字符集使用默认的 utf8_general_ci；

Ø  连接的客户端也使用utf8，建立连接时指定charset或SETNAMES UTF8;

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

 

4)  如无说明，则数值类型的字段请使用UNSIGNED属性；

解读：

Ø  相比不使用 unsigned，可以扩大一倍使用数值范围。

 

5)  如无说明，所有字段都设置NOT NULL，并设置默认值；

解读：

Ø  null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化

Ø  null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多

Ø  null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识

Ø  对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

Ø  如果不能保证insert时一定有值过来，定义时使用default ‘’ 或 0

 

6)  如无说明，建表时一律采用innodb引擎；

解读：

Ø  支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

 

7)  所有的数字类型字段，都必须设置一个默认值0；

8)  单表字段数上限30个左右，再多请考虑垂直分表，即：冷热数据分离存放；

9)  禁止使用外键，如果有外键完整性约束，需要应用程序控制

解读：

Ø  外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先。

 

10) InnoDB表行记录物理长度不超过8KB

解读：

Ø  InnoDB的data page默认是16KB，基于B+Tree的特点，一个data page中需要至少存储2条记录。因此，当实际存储长度超过8KB（尤其是TEXT/BLOB列）的大列（large column）时会引起“page-overflow存储”，类似ORACLE中的“行迁移”。

Ø  如果必须使用大列（尤其是TEXT/BLOB类型）且读写频繁的话，则最好把这些列拆分到子表中，不要和主表放在一起存储。

建表示例：

CREATE TABLE `dbname_user` (

 `id` int(20) unsigned NOT NULL AUTO_INCREMENT,

 `staff_id` int(11) NOT NULL COMMENT '操作人员id',

 `staff_name` varchar(50) NOT NULL COMMENT '人员名称',

 `url` varchar(200) NOT NULL COMMENT '操作路径',

 `method` varchar(10) NOT NULL COMMENT '操作方式',

 `params` varchar(10) NOT NULL COMMENT '操作参数',

 `ip` int(20) unsigned NOT NULL COMMENT 'ip',

 `time` int(11) NOT NULL COMMENT '操作时间',

 PRIMARY KEY (`id`),

 KEY `idx_staff_id_ct` (`staff_id `)

) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;

注：提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化。

3   字段设计规范

3.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），对它的使用需要多一次寻址，没有默认值。一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。

5)  text和blob上面一般不建索引，而是利用sphinx之类的第三方全文搜索引擎，如果确实要创建（前缀）索引，可能会影响性能。

6)  尽量避免使用大字段blob，text，longtext

Ø  如特别需要大字段，不要集中存放在一个表中，分多表存放。查询语句where条件不要落在大字段上

Ø  BLOB可以看出varbinary的扩展版本，内容以二进制字符串存储，无字符集，区分大小写。

Ø  禁止使用BLOB类型在数据库中存放图像、文件等大对象，可将图像、文件等大对象放在文件系统中，数据库中只存放存放图像的URL地址。

 

7)  varchar大于某些数值的时候，其会自动转换为text。

Ø  大于varchar（255）变为tinytext；

Ø  大于varchar（500）变为 text；

Ø  大于varchar（20000）变为mediumtext。

 

3.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(9,2)。float默认只能能精确到6位有效数字。

5)       对于货币、金额这样的类型，使用int，小数容易导致钱对不上。

3.3   用尽量少的存储空间来存数一个字段的数据

1)  能用int的就不用char 或者varchar。

2)  能用varchar(20)的就不用varchar(255)。

3)  时间戳字段尽量用int型。

4)  用int unsigned存储IPV4地址，用INET_ATON()、INET_NTOA()进行转换，基本上没必要使用CHAR(15)来存储。

5)  使用varchar(20)存储手机号。

Ø  涉及到区号或者国家代号，可能出现+-()

Ø  varchar可以支持模糊查询，例如：like“138%”。

 

3.4   timestamp与datetime选择

1)  datetime 和timestamp类型所占的存储空间不同，5.6.4之前datetime 占8个字节，5.6.4后占5个字节，timestamp占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)  MySQL 5.6.4之后datetime 和timestamp都可以在insert/update行时，自动更新时间字段（如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP），但一个表只能有一个这样的定义。

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

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

3.5   同一意义的字段定义必须相同

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

4    SQL设计规范

1)  杜绝SELECT * 读取全部字段，只获取必要的字段，需要显示说明列属性

解读：

Ø  SELECT * 容易将内存中热数据刷出内存；

Ø  读取不需要的列会增加CPU、IO、NET消耗；

Ø  不能有效的利用覆盖索引；

Ø  使用SELECT *容易在增加或者删除字段后出现程序BUG；

Ø  即使需要所有字段，减少网络带宽消耗，能有效利用覆盖索引。

 

2)  能确定返回结果只有一条时，使用 limit1。

解读：

Ø  在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。

 

3)  小心隐式类型转换

解读：

Ø  会造成索引失效；

Ø  两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换；

Ø  两个参数都是字符串，会按照字符串来比较，不做类型转换；

Ø   两个参数都是整数，按照整数来比较，不做类型转换；

Ø  十六进制的值和非数字做比较时，会被当做二进制串；

Ø  有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp；

Ø  有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较。

 

4)  禁止在where条件列上使用函数

解读：

Ø  where条件列上使用函数会导致索引失效，如lower(email)，f_qq % 4。可放到右边的常量上计算。

 

5)  使用like模糊匹配，%不要放首位

解读：

Ø  会导致索引失效，有这种搜索需求时，考虑将%放在索引字段后面，或使用sphinx全文搜索。

 

6)  少用子查询，改用join

解读：

Ø  小于5.6版本时，子查询效率很低，不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化

 

7)  考虑使用union all，少使用union，注意考虑去重

解读：

Ø  union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all；

Ø  如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，各自加上limit。

 

8)  OR查询是不能命中索引，必须改为IN查询，且IN的内容尽量不超过200

解读：

Ø  超过200个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟。

 

9)  拒绝大事务

解读：

Ø  在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。

 

10)避免使用is null, is not null这样的比较；

11) order by .. limit的优化

Ø  这种查询更多的是通过索引去优化，但order by的字段有讲究，比如主键id与f_time都是顺序递增，那就可以考虑order by id而非 f_time 。

 

12)  c1 < a order by c2的优化

Ø  与上面不同的是，order by之前有个范围查询，由前面的内容可知，用不到类似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。

 

13)SQL中禁止出现now()、rand()、sysdate()、current_user()等不确定结果的函数。

Ø  建议不确定的时间在程序层取出时间，语句级复制场景下,引起主从数据不一致; 不确定值的函数,产生的SQL语句无法利用。

 

14)insert语句指定具体字段名称，不要写成insert..values(..);

15)DML语句必须有where条件，且使用索引查找。

16) 禁止在数据库中存储明文密码

Ø  如果需要存储MySQL密码可以用MySQL内置函数password()对明文密码进行MD5进行加密。

17) 禁止在列上进行运算

Ø  在列上运算将导致Mysql索引失效而进行全表扫描。

18) 建议不要使用子查询（视情况而定）

Ø  对于子查询，mysql会对子查询结果返回给外部表，并对外部表进行全表扫描

5   其他设计技巧

1)   避免使用存储过程、触发器、视图、自定义函数等,这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、SHARDING等变得更加困难。

2)   分区表对分区键有严格要求;分区表在表变大后,执行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。

3)   使用常用英语(或者其他任何语言)而不要使用拼音首字母缩写

4)   将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据, 有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存

5)   禁止有super权限的应用程序账号存在, 安全第一。super权限会导致readonly失效,导致较多诡异问题而且很难追踪。

6)   不要在MySQL数据库中存放业务逻辑, 数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。

 

架构篇

1. 数据库版本选择

MySQL数据库统一选择Percona-Server-5.6.X，Percona Server为 MySQL 代码做了优化，在性能和稳定性上较 MySQL 有着很显著的提升，且提供了MySQL官方企业版线程池的功能，可以看做是免费的官方企业版且可以和官方MySQL相互迁移。

2. 安装目录的规范

数据文件存放在/data/mysql_330X/data下(X=6,7,…)；

日志存放在/log/mysql_330X下，日志存放磁盘和数据文件分开，如下所示：

socket = /data/mysql_3306/run/mysql.sock

pid-file = /data/mysql_3306/tmp/mysqld.pid

datadir = /data/mysql_3306/data

log-error = /log/mysql_3306/error.log

#binlog存放目录：

log-bin = /log/mysql_3306/binlog/master-bin.log

log-bin-index = /log/mysql_3306/binlog/master-bin.index

#中继日志存放目录：

relay-log = /log/mysql_3306/relay-log

relay_log_index = /log/mysql_3306/relay-log.index

3. 架构设计

1)   主从结构：一个主节点，1到N个从节点，只有主节点可以端同时为客户提供读写操作，从节点只能提供读操作。

适合场景：比较适合读多写少且可不考虑从库实时性服务的场景。

Ø  优点：搭建简单，很方便在线快速扩展。

Ø  缺点：主节点宕机后需要手工切换，且从库的数量越多，手工切换的工作量就越大。

2)   双主架构：一般由两个节点组成，从字面意思可以看出两个节点都是主，都可以同时为客户端提供读写操作；且可延伸成一主一备多从的架构，也非常推荐使用这种架构，很方便做业务拆分（主库主要负责写和对实时性要求非常高的极少量的读，备库负责不同业务的写或与主库业务相关的少量的写，从库只负责读）。

Ø  适合场景：适合所有高可用的场景。

Ø  硬性要求：所有表都必须有自增列主键，两个主库的自增步长量（auto_increment_increment）都要求设置为2，自增起始值（auto_increment_offset）不同，一般一个为1，另一个为2。

Ø  优点：搭建简单，扩展方便，结合keepalive可实现两个主节点任何一个宕机自动切换，不需要人工干预，运维成本低。

Ø  缺点：两个主节点不能同时对相同的SQL更新不同的值，否则会出现更新丢失的现象；且两个主节点同时写数据库时会对集群的性能造成一定的影响，具体性能的下降程度与两个主库的繁忙程度成正比。

3)   PXC：全称Percona XtraDB Cluster，是基于Galera协议的高可用方案，官方要求至少3个节点组成（其实2个节点也可搭成），建议不超过8个节点，否则影响性能。集群中的每个节点都可以对客户端同时提供读写操作，节点自动配置，故障节点自动清除，新加入节点自动复制。

Ø  适合场景：适合于对实时性、一致性要求非常高，但对性能要求不高的场景。

Ø 硬性要求：binlog_format必须为row格式，所有表都必须有自增列主键，如集群是由三个节点组成，三个节点的自增起始值为1、2、3，步长都为3，同一业务更新和写入都最好在一个节点上操作，否则会报：Error： 1213  SQLSTATE: 40001。

Ø  优点：强一致性、无同步延迟，每个节点都可以对客户端同时提供读写操作，节点自动配置

Ø 缺点：性能比其他架构都低且性能由集群中性能最差的节点决定；不支持lock /unlock tables，加入新节点，开销大，需要复制完整的数据。

4)   MHA：全称Master High Availability，官方要求至少3个节点组成（其实2个节点也可搭成），该集群由两部分组成：MHAManager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个Master-Slave集群，也可以部署在一台Slave节点上。MHANode运行在每台MySQL服务器上，MHA Manager会定时探测集群中的Master节点，当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master，然后将所有其他的Slave重新指向新的Master。整个故障转移过程对应用程序完全透明，在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

Ø 适用场景：多数场景都比较适合，更倾向于对性能要求比较高，对事务的一致性和实时性要求不太高的场景。

Ø 硬性要求：需要有SSH认证配置，各从库一定要写在管理节点的配置中，否则故障切换时会出现“脑裂”的现象。

Ø 优点：自动监控Master和故障转移，可在0~30秒之内自动完成数据库的故障切换，且不需要人工干预。

Ø 缺点：需要SSH认证配置，存在一定的安全隐患；官方提供的脚本不完善，需要重新改写；部署复杂度稍高。

4. 各环境架构选择

1)   开发环境：单节点数据库即可。

2)   测试环境：一般单节点数据库，如有读写分离测试的需求可增加从节点。

3)   UAT环境：一般单节点数据库，如有读写分离测试的需求可增加从节点。

4)   预发布环境：如有该环境，数据架构与生库尽量保持一致。

5)   生产环境：一般双主结构，如有特殊需求可加从节点或换MHA或PXC架构。

注：生库环境需与其它环境隔离。

安全篇

1.  数据库初始化后安全加固

mysql -e "delete frommysql.user where host not in ('localhost')"

Ø  删除主机不是本地登录的用户，防止root用户可以从任何IP登录

mysql -e "delete from mysql.user where user = ''"

mysql -e "drop database test"

mysql -e "truncate mysql.db"

mysqladmin -uroot password

Ø  为root用户设置符合复杂度的口令

2.  生产库权限最小化

一般给应用用户授予：select、insert、updata、delete权限即可，严禁授予allprivileges权限及file、super权限。

如无特殊审批，严禁给数据库运维人员以外的用户授予select权限，严格限制MySQL数据库权限。

3.  严格控制update、delete SQL语句

update和delete语句一定要加where条件，否则会导致全表数据被更新或删除。