值得收藏：一份非常完整的 MySQL 规范(一)

一、数据库命令规范

• 所有数据库对象名称必须使用小写字母并用下划线分割。

• 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）。

• 数据库对象的命名要能做到见名识意，并且最后不要超过3 2 个字符。

• 临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 ( 时间戳 ) 为后缀。

• 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。

二、数据库基本设计规范

1. 所有表必须使用 InnoDB 存储引擎没有特殊要求（即 InnoDB 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 InnoDB 存储引擎（MySQL 5.5 之前默认使用 Myisam，5.6 以后默认的为 InnoDB）InnoDB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

2. 数据库和表的字符集统一使用 UTF8兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效。

3. 所有表和字段都需要添加注释使用 comment 从句添加表和列的备注 从一开始就进行数据字典的维护。

4. 尽量控制单表数据量的大小，建议控制在 500 万以内

   500 万并不是 MySQL 数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。

5. 谨慎使用 MySQL 分区表

   分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据。

6. 尽量做到冷热数据分离，减小表的宽度

   MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节 减少磁盘 IO，保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）
   

7.禁止在表中建立预留字段预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定

8.禁止在数据库中存储图片，文件等大的二进制数据通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息。

9.禁止在线上做数据库压力测试

10.禁止从开发环境，测试环境直接连接生成环境数据库

三、数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型

• 原因

  列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多， 索引的性能也就越差
  

• 方法

将字符串转换成数字类型存储，如：

将IP地址转换成整形数据。

• MySQL 提供了两个方法来处理 IP 地址：

  inet_aton 把ip转为无符号整型(4-8位)
  inet_ntoa 把整型的ip转为地址
  

  插入数据前，先用 inet_aton 把 IP 地址转为整型，可以节省空间。 显示数据时，使用 inet_ntoa 把整型的 IP 地址转为地址显示即可

  2.对于非负型的数据（如自增 ID、整型 IP）来说，要优先使用无符号整型来存储
  因为无符号相对于有符号可以多出一倍的存储空间。

  SIGNED INT -2147483648~2147483647
  UNSIGNED INT 0~4294967295
  

  VARCHAR(N) 中的 N 代表的是字符数，而不是字节数。使用 UTF8 存储 255 个汉字 Varchar(255)=765 个字节。过大的长度会消耗更多的内存
  

2. 避免使用 TEXT、BLOB 数据类型，最常见的TEXT类型可以存储64k的数据

• 建议把 BLOB 或是TEXT列分离到单独的扩展表中

  MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。

  而且对于这种数据，MySQL 还是要进行二次查询，会使 SQL 性能变得很差，但是不是说一定不能使用这样的数据类型。

  如果一定要使用，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时一定不要使用 select * 而只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询。

• TEXT 或 BLOB 类型只能使用前缀索引

  因为 MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的。

3. 避免使用 ENUM 类型

• 修改 ENUM 值需要使用 ALTER 语句

• ENUM 类型的 ORDER BY 操作效率低，需要额外操作

• 禁止使用数值作为 ENUM 的枚举值

4. 尽可能把所有列定义为 NOT NULL

原因：

• 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间。

• 进行比较和计算时要对 NULL 值做特别的处理。

5. 使用 TIMESTAMP（4 个字节）或 DATETIME 类型（8 个字节）存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。

TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高，超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储。

经常会有人用字符串存储日期型的数据（不正确的做法）：

• 缺点 1：无法用日期函数进行计算和比较。

• 缺点 2：用字符串存储日期要占用更多的空间。

6. 同财务相关的金额类数据必须使用 decimal 类型

• 非精准浮点：float，double

• 精准浮点：decimal

Decimal 类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节。可用于存储比 bigint 更大的整型数据。

四、索引设计规范

1. 限制每张表上的索引数量，建议单张表索引不超过 5 个

索引并不是越多越好！索引可以提高效率同样也可以降低效率；索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

2. 禁止给表中的每一列都建立单独的索引

5.6 版本之前，一个 SQL 只能使用到一个表中的一个索引，5.6 以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

3. 每个 InnoDB 表必须有个主键

InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种 InnoDB是按照主键索引的顺序来组织表的。

不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引） 不要使用 UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）。主键建议使用自增 ID 值。

五、常见索引列建议

• 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列。

• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段。

• 并不要将符合 1 和 2 中的字段的列都建立一个索引，通常将 1、2 中的字段建立联合索引效果更好。

• 多表 JOIN 的关联列。