MySQL数据库设计规范说明

目的

为了规范数据库设计，减少设计失误，提高数据安全及性能，特制订本规范。

适用范围

所有mysql数据库。原则上，数据库设计应遵循本规范说明，特殊情况可例外，但需跟DBA说明原因。

规范

 

命名

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

a)MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为 0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。

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

c)字段名显式区分大小写,但实际使用不区分,即不可以建立两个名字一样但大小写不一样的字段。

d)为了统一规范, 库名、表名、字段名使用小写字母。

 

库名、表名、字段名禁止超过32个字符

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

 

库名、表名、字段名禁止使用MySQL保留字

当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

 

表

原则上，业务范畴内的表都统一使用innodb存储引擎；如需使用其它存储引擎，需说明原因，并征得DBA同意。

innodb引擎是MySQL5.5版本以后的默认引擘,支持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、大内存、SSD等硬件支持更好,支持数据热备份等,因此INNODB相比MyISAM有明显优势。

myisam不够可靠，容易损坏丢失数据。

myisam不支持事务；如果存在myisam与innodb混用的话，事务数据的一致性存在风险。

myisam读写阻塞，不适用于高并发环境；innodb支持行级锁。

myisam不支持在线热备，配置复制时可能需要停机或者长时间锁表。

如果两种引擎混用，会给资源分配带来问题。因为有很多参数和设置需要停库才能修改，为了减少停库次数，我们目前都是采用预先最大化分配足额内存和磁盘空间的方式去做的。而myisam和innodb在内存和空间使用上都不一样，如果两者混用，那分配的时候就很难两方都照顾到。如果两者都按比例分配，很可能存在资源不能充分利用的情况；且随着业务的变化，需要调整的话，也会存在停机问题。

运维需要收集很多的运行状态数据，innodb有非常多的状态数据可收集；而对于myisam引擎，能收集到的数据非常有限，不利于我们做监控和容量规划。

 

使用外部引擎

目前常用的全文索引引擎有sphinx和lucene等，适合并发高，数据量大，业务逻辑复杂的场景。主要关注预热、增量更新及分片功能的实现。

异构同步引擎有mysql2redis、tair 等，主要解决数据项cache 的同步

 

禁止使用分区表

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

减少或避免使用临时表

为了切换后可以正常工作，无论主备，相同的表需使用相同的引擎。

相同的表在主备可以使用不同的引擎或者获得更好地性能，但是给管理带来复杂性，也给业务带来隐患。在我们的DB环境，应用程序切换连接DB是常态，因此，需要严格保持主备数据和表结构的一致性。

 

每一个表都需要设置主键

表没有主键,INNODB会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。

 

将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据

有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存命中率。

 

范式

表不要求一定满足第三范式，根据实际情况可适当添加冗余字段。

我们的原则是一个SQL最好只操作一个表，最多不能超过2个表的关联。如果实现一个常用的功能需要一个关联多表的查询，则需要重新考虑设计。

由程序保证冗余数据的维护。

 

约束

对于字典类型的表，因数据量小，修改少，影响面大，应依赖数据库约束来确保数据质量。

对于日志或流水型表，为了提升效率，可以释放放宽限制。

之所以分开，是从性能以及影响面考虑的。

对于字典类型的数据，因为修改少，约束给其性能带来的负面影响忽略。但是一个数据字段的数据错误，影响面非常广，因此，需要非常严谨。前段程序或者手工添加此类数据时，容易出现错误，因此需要通过约束来保证其数据的质量。

日志或者流水型表刚好相反，它一般只影响个别用户，但数据量较大，修改较为频繁，性能优先。

 

字段

对于字段设计，概况下来一个原则是：越简单越好，越小越好。

常用数据类型介绍：

数据类型

取值范围

占用空间(字节)

tinyint [unsigned]-128到127或0到255 1smallint [unsigned] -32768到32767或0到65536 2int [unsigned] -2147483648到2147483647或0到4294967295 4bigint [unsigned] -9223372036854775808到9223372036854775807
或
0到18446744073709551615 8date yyyy-mm-dd，精确到天 3datetime yyyy-mm-dd hh24:mi:ss 精确到秒 8timestamp yyyy-mm-dd hh24:mi:ss
精确到秒，只能存储1970到2037年之间的时间 4char(m) 0<M<=255 m个字符( 非字节)。具体空间与字符集相关varchar(m) 0<m<=（65532/n) m个字符。与字符集有关，最多不超过65532字节text 64K个字符 与字符集相关      

 

 选择最合适的数据类型，能用数字类型不用varchar类型；能用date/datetime类型不用varchar类型；避免使用char类型；不使用浮点数，可以通过乘以一个系数来转换为整型数据。

在大部分情况下，数据类型比varchar类型更省空间，计算性能更高。

char类型占用固定空间，在很多时候会造成空间浪费。

 

 尽量避免text/lob类型

text和lob在行内只存储指针，实际数据是在行外单独存储的。使用这些数据类型，查询时需要更多的IO。

如果SQL引用了text/lob字段，排序等操作需要用到隐式临时表时，只能用到disk临时表，不能用in-memory临时表。对性能影响巨大。

如果也许需要用text/lob类型，建议把这些字段和其他字段分离，放在单独的表中。

 

 字段长度定义遵循最小化原则，够用就行，不能贪图方便定义很大的长度。

过大的长度容错性高，容易出现低质量数据。

定义大长度会消耗更多的空间(需要用额外的字节存储长度)

在很多时候，mysql或其他程序（如mysqldump）会以列的长度定义来分配使用内存，过大的字段长度定义会消耗更多的内存。甚至还有可能原来可以在内存执行的排序变为磁盘排序了。

 

 字段实际值也遵循最小化原则，在满足业务需求的前提下，选择最合适的数据类型，写入最少的数据

ID一般无具体意义，优先使用数字来用于ID字段，杜绝滥用长字符串ID。

对于状态之类的字段，用0/1之类的数字代替valid/invalid等。

 

 对于字符类型，字段长度定义的是字符个数，而不是字节个数，所占空间与字符集相关。对于int类型，占用空间是固定的，指定长度的话只是制定了显示长度。

varchar(25000)不一定能存储25000个字符，如果都是汉字，且是utf8字符集的话，只能存储65532/3个字符。

int(1)和int(11)占用空间是一样的，1和11只是代表显示长度，与实际数据存储无关。另外，在允许的范围内，往int字段写入不同的值，如insert t values(1)和insert t values(9999)占用空间一样。

 

区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。

DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性。注意:在5.5和之前的版本中,如果一个表中有多个timestamp列,那么最多只能有一列能具有自动更新功能。

 

使用TIMESTAMP的自动赋值属性

a)自动初始化,并自动更新: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

b)只是自动初始化: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自动更新,初始化的值为0: column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值为0: column1 TIMESTAMP DEFAULT 0

 

 

一个表的字段个数控制在30-50个字段以内；如果字段超过50个，可考虑将字段按冷热程度分表。

这样做虽然会给应用带来更多的代码开发量，但对于热表来说，这样做可以提升buffer利用率，减少IO，提升查询的效率。

 

 严格禁止单条记录超过8K

目前我们的DB一个page大小都设置为16K，当一条记录拆过page的一半(8K)时，记录中的blob/varchar会在行外存储。存取时会有额外IO消耗。插入操作会锁住整个聚簇索引(X Index Lock)，直到插入完成才释放。

 

建议每一个重要的业务表都加上 create_time 和 modify_time 两个字段，数据类型为datetime；后续的所有更新都必须更新modify_time字段。

增加字段会带来额外的资源消耗，但考虑到它可能能带来的好处，还是值得添加的。

这两个字段可以与业务时间重合重用。

 

对于可能出现在where条件中的字段，尽量设置为非空(not null)

B*Tree索引不能索引空值(null)。字段值为空的话，在部分查询不能有效使用索引。

 

字段字符集与表保持一致，不单独设置字符集。

相同含义的字段在不同表中应使用相同的名称，数据类型及长度必须保持一致。

 

索引/主键设计

Innodb表每一个表都要设置主键。主键越短越好，最好是auto_increment类型；如果不能使用自增，则应考虑构造使用单向递增型主键；禁止使用随机类型值用于主键。

使用innodb，每一个表都必须有主键。如果没有指定主键，mysql会选择一个非空的唯一键作为主键；如果都没有，mysql会自动分配6-bytes长的全局的rowid隐藏列作为主键。所有InnoDB非主键表共享这一序列，并发性能较差，因此建议所有InnoDB表，指定主键。

所有的辅助索引都包含主键字段，因此，如果主键过大的话，会影响所有的索引大小。

递增型主键可以让索引更紧凑、空间利用更充分、数据插入更高效。随机数据会让空间大量浪费，且会导致索引分裂加剧，离散IO增多，极大影响DML性能。

 

主键最好由一个字段构成，最多不要超过3个，禁止超过3个字段的组合主键。如果业务要求，则可以创建一个自增字段作为主键，再添加一个唯一索引。

多列主键会导致其他索引体积膨胀，占用更多的空间，并降低性能。

使用多列业务主键的话，对主键来说，一般可认为是随机插入。

如果查询都是基于主键字段，且只有1个及以下辅助索引，则限制可以放宽。

 

如果一个业务上存在多个(组)唯一键，以查询最常用的唯一键作为主键。

索引会降低DML的性能，不是越多越好，只创建需要的索引，避免冗余索引。

创建索引需考虑返回数据量、对DML的影响，以及受其影响的查询SQL的执行频率。

 

选择作为主键的列必须在插入后不再修改或者极少修改，否则需考虑使用自增列作为主键。

创建组合索引/主键时，常用的字段放在前面；选择性高的字段放在前面。

两者冲突时，以常用为更优先选择。

 

创建组合索引时，如果where条件中过滤性不强，且需要排序分页操作，建议把排序字段也加到组合索引中，放在组合索引最后列。

如col1 + col 2可以过滤出来10000条数据，当有如下SQL时，建议把col3也加到索引中：

select col1,col2...coln from t where col1=xx and col2=xx order by col3 limit n;

 

对较长的字符数据类型的字段建索引，优先考虑前缀索引。

低选择性的列不加索引，例如”性别”

如性别字段加索引意义不大

状态字段一般不加索引。例外情况是，状态数据分布很倾斜，且查询条件中没有其他更适合索引的字段，如status=1占99%数据，status=2占1%数据，查询SQL为：select col1,col2... from t where status=2;

 

禁止冗余索引

索引是双刃剑,会增加维护负担,增大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。

 

在建表时，应充分考虑需要添加什么索引，尽量避免上线后添加索引

 

分库分表

 

如果数据有时效性，则建议按时间分表或者分区

如可以年、月、天分表。代表如提现单流水表。

分表的粒度应该和实际数据量结合，慎用按天分表。原则上，我们以单表1G作为分表的界限。

杜绝按天分库。

如果也许必须按天分表，考虑到一个文件下不应存放过多文件，建议可根据年份分库，然后把按天分的表放在对应的库中。

 

 如果所有数据热度相同，则建议根据hash或者其他手段分库分表

目前最常用的是根据分表方式是分百库十表，共1000个表。具体规则是：取分表字段(如ID)的后三位，其中后两位是库的ID(00-99)，倒数第三位是表的ID(0-9);

 

 杜绝只按月份，不考虑年份方式分表

目前发现有不少表是按月份分12张表，不同年份的数据会保存在一张表里。这种做法不利于历史数据的管理，且会降低buffer的利用率。

此种情况，建议按YYYYMM方式分表。

 

 如果多个独立业务需要共用一台DB的话，不同业务的表必须放在不同的database(schema)里

主要考虑管理便捷和安全。

 

 对于分库分表，每一个表的索引结构及名称都必须一致

 

其它

开发在提交数据库新增对象请求单时，需按附件模板提供足够的信息供DBA决策

根据这些信息，DBA可以做容量、性能评估，决定需要什么样的硬件支持。

 

示例：

表名

t_order_x

DDL详见req文件(如果少的话，可直接附在这里)用途用户订单表未来一年数据量预测每天增量200W条是否分表是分表方式百库十表活跃数据范围最近3月核心SQL及执行频率select col1,col2 from c2c_db_00.t_order_1 where flistid='xxxxx';每天执行400W次需求提出者developer name其他说明无