MySQL规范

约定不等于承诺〃 2022-12-06 05:21 133阅读 0赞

### 文章目录 ###

*   *  目的
     *  适用范围
     *  规范
     *   *  命名
         *   *  例子
         *  表
         *  范式
         *  约束
         *  字段
         *  索引/主键设计
         *  分库分表
         *  读写分离
     *  其他

## 目的 ##

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

## 适用范围 ##

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

## 规范 ##

### 命名 ###

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

> 1.  MySQL有配置参数lower\_case\_table\_names,不可动态更改,linux系统默认为  
>     0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。
> 2.  如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。
> 3.  字段名显式区分大小写,但实际使用不区分,即不可以建立两个名字一样但大小写不一样的字段。
> 4.  为了统一规范, 库名、表名、字段名使用小写字母。

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

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

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

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

#### 例子 ####

<table> 
 <thead> 
  <tr> 
   <th>对象类型</th> 
   <th>命令</th> 
   <th>解释</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>表</td> 
   <td>xxx_ddd</td> 
   <td>xxx：业务子系统缩写 <br> ddd：表名</td> 
   <td>表名应该由有实际意义的简短英语单词构成</td> 
  </tr> 
  <tr> 
   <td>视图</td> 
   <td>xxx_ddd_v</td> 
   <td>xxx：业务子系统缩写 <br>ddd：表名<br> v：表示视图</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>索引</td> 
   <td>xxx_ddd_tn</td> 
   <td>xxx：业务子系统<br> ddd：表名<br> t = u：表示唯一索引<br> n：表示非唯一索引 <br> n：序号</td> 
   <td>po_order_summary_n2 表示在po_order_summary表上的第二个非唯一性索引</td> 
  </tr> 
 </tbody> 
</table>

> 必须有注释  
> 表名列名必须有注释（必须加上COMMENT ‘<字段扼要解说>’），表结构变更须由库表  
> OWNER所在团队发起，ＤＢＡ来执行

### 表 ###

*  原则上，业务范畴内的表都统一使用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,同时保证热数据常驻内存提高缓存命中率。

*  不允许不同业务模块的表连接查询

> 表分模块很可能在后续进行业务的垂直拆分到不同的业务库里面去，如果做连接查询后面拆分就比较麻烦，系统的模块化结构也不清晰

*  单表10G以下

> 每张表数据量建议控制在千万级别行以下（单表10G以下），为此设计阶段需考虑数据的归档或拆分

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

> 密码存储应该使用不可逆加密算法将密码、用户名及盐值组合加密后存储

*  禁止将数据字典表进行连接查询

> 数据字典表可以查询到cache或者在内存中对相关列进行替换

### 范式 ###

> 表不要求一定满足第三范式，根据实际情况可适当添加冗余字段。  
> 我们的原则是一个SQL最好只操作一个表，最多不能超过2个表的关联。如果实现一个常用的功能需要一个关联多表的查询，则需要重新考  
> 虑设计。  
> 由程序保证冗余数据的维护。

### 约束 ###

*  对于字典类型的表，因数据量小，修改少，影响面大，应依赖数据库约束来确保数据质量。
 *  对于日志或流水型表，为了提升效率，可以释放放宽限制

> 之所以分开，是从性能以及影响面考虑的。  
> 对于字典类型的数据，因为修改少，约束给其性能带来的负面影响忽略。但是一个数据字段的数据错误，影响面非常广，因此，需要非常  
> 严谨。前段程序或者手工添加此类数据时，容易出现错误，因此需要通过约束来保证其数据的质量。  
> 日志或者流水型表刚好相反，它一般只影响个别用户，但数据量较大，修改较为频繁，性能优先

### 字段 ###

对于字段设计，概况下来一个原则是：越简单越好，越小越好。  
常用数据类型介绍：

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

*  能用数字类型不用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的自动赋值属性

> 1.  自动初始化,并自动更新: column1 TIMESTAMP DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP
> 2.  只是自动初始化: column1 TIMESTAMP DEFAULT CURRENT\_TIMESTAMP
> 3.  自动更新,初始化的值为0: column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT\_TIMESTAMP
> 4.  初始化的值为0: column1 TIMESTAMP DEFAULT 0

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

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

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

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

*  跟踪业务数据的更改变化，强烈建议业务关键数据TABLE中必须包含以下特定的WHO字段；后续的所有更新都必须更新update\_time字段

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>字段类型</th> 
   <th>NULL?</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>created_by</td> 
   <td>bigint(20)</td> 
   <td>not null</td> 
   <td>创建人id</td> 
  </tr> 
  <tr> 
   <td>create_time</td> 
   <td>datetime</td> 
   <td>not null</td> 
   <td>创建时间</td> 
  </tr> 
  <tr> 
   <td>updated_by</td> 
   <td>bigint(20)</td> 
   <td>not null</td> 
   <td>最后修改人id</td> 
  </tr> 
  <tr> 
   <td>update_time</td> 
   <td>datetime</td> 
   <td>not null</td> 
   <td>最后修改时间</td> 
  </tr> 
 </tbody> 
</table>

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

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

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

*  字段字符集与表保持一致，不单独设置字符集。
 *  同含义的字段在不同表中应使用相同的名称，数据类型及长度必须保持一致。
 *  如果字段只有true or false，请使用tinyint（数值范围-128~127）

> 如模块分类：1订单 2商品；删除标志 0正常，1删除；状态1为可选，2为不可选等等

*  时间字段

> 存储时间（精确到秒）在mysql 5.6版本来说，TIMESTAMP和datetime类型都可以，其中TIMESTAMP使用4字节，DATETIME使用5个字节(mysql 5.6之前占用8个字节)，TIMESTAMP和datetime 类型，都具有自动赋值以及自动更新的特性。

*  null  
    每个字段在设计时建议不要允许为null，而应该设计为not null，可以给字段加上默认值，数字类型default 0，字符类型default ‘’；

> 设计为null，在Mysql 需要多余一个字节去存储null值，同时对sql索引有影响，也就是索引字段 is null，是走不了索引的

### 索引/主键设计 ###

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

> 1.  使用innodb，每一个表都必须有主键。如果没有指定主键，mysql会选择一个非空的唯一键作为主键；如果都没有，mysql会自动分配6-bytes长的全局的rowid隐藏列作为主键。 所有InnoDB非主键表共享这一序列，并发性能较差，因此建议所有InnoDB表，指定主键
> 2.  所有的辅助索引都包含主键字段，因此，如果主键过大的话，会影响所有的索引大小
> 3.  递增型主键可以让索引更紧凑、空间利用更充分、数据插入更高效。随机数据会让空间大量浪费，且会导致索引分裂加剧，离散IO增多，  
>     极大影响DML性能。

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

> 1.  多列主键会导致其他索引体积膨胀，占用更多的空间，并降低性能
> 2.  使用多列业务主键的话，对主键来说，一般可认为是随机插入
> 3.  如果查询都是基于主键字段，且只有1个及以下辅助索引，则限制可以放宽

*  分片表主键,若单机应用不考虑分片（sharding），建议统一由Auto-Increment字段生成整型（int unsigned 或bigintunsigned），不建议使用组合主键。若表是分片表，建议由应用端的全局发号器来生成每个分片表的数字主键，而不是用uuid这种来生成

> Mysql Innodb索引组织表，聚集索引，相比字符串主键，自增长数字主键，性能更高，可以减少页分裂，同时可以减少表的二级索引的占用空间，还可  
> 以使用自增ID来做分页优化

*  索引会降低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),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担  
> 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

*  索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面，就是数据分布
 *  在建表时，应充分考虑需要添加什么索引，尽量避免上线后添加索引
 *  使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

### 分库分表 ###

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

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

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

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

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

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

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

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

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

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

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

### 读写分离 ###

需在设计阶段考虑如果访问量非常大（比如对外平台），且不做scale out表拆分的话，需做数据库访问的读写分离；若应用开发平台本身能实现client端的读写分离，建议由应用开发平台自己实现（优点是没有代理端的流量瓶颈问题，部署架构简单），若应用开发平台不能实现，考虑采用数据库访问中间件来实现；

> 对外平台指面向大众的应用，比如电商，手机app等，需要考虑读写分离。对内平台指公司内部应用的平台，如果数据库并发访问量比较小，不需要考虑读写分离。

## 其他 ##

开发在提交数据库新增对象请求单时，需按附件模板提供足够的信息供DBA决策  
根据这些信息，DBA可以做容量、性能评估，决定需要什么样的硬件支持。

示例：

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

> 禁用event  
> 不建议使用Event来做mysql的定时任务，event在不同拓扑结构（主从，pxc等）下会有复杂的特性，容易导致主从数据重复执行，甚至出  
> 现bug或者不执行的情况，建议由应用端Schedule 去实现定时任务。