1. 标准 SQL 语言没有规范以下功能

  • ID自示增长
  • 分页
  • 函数
  • 编程语言
  • 服务端的数据软件

2. 数据库命名规范

  1. 所有数据库对象名称必须使用小写字母并用下划线分割；
  2. 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）；
  3. 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符；
  4. 临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀；
  5. 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索 引失效，导致查询效率降低）。

3. SQL 语句规范

  • SQL 语句可以单行或多行书写，以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • 同样可以使用/**/的方式完成注释
  • MySQL 数据库的 SQL 语句不区分大小写，建议关键字使用大写，自定义的使用小写，例如：
1

SELECT * FROM user;

4. 命名规范

  • 数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写
  • 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。
    • 说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
  • 表名使用单数形式。如：员工表使用 EMPLOYEE，而不要使用 EMPLOYEES
  • 采用有意义的名字，一般不超过三个英文单词，单词之间使用下划线分隔
  • 数据库、表、字段的命名禁用保留字，如desc、range、match之类
  • 对象的名字应该能够描述它所表示的对象。
    • 表的名称应该能够体现表中存储的数据内容，最好是遵循“业务名称_表的作用”；
    • 对于存储过程存储过程应该能够体现存储过程的功能。
    • 库名与应用名称尽量一致。
  • 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名
  • 表达是与否概念的字段，应该使用is_xxx的方式命名，数据类型是unsigned tinyint（1 表示是，0 表示否）。

5. 数据库基本设计规范

  1. 所有表必须使用Innodb存储引擎
  • 没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。
  1. 数据库和表的字符集统一使用UTF8
  • 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效。
  1. 所有表和字段都需要添加注释
  • 使用comment从句添加表和列的备注 从一开始就进行数据字典的维护。
  1. 尽量控制单表数据量的大小，建议控制在500万以内
  • 500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。
  • 可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。
  1. 谨慎使用MySQL分区表
  • 分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据。
  1. 尽量做到冷热数据分离，减小表的宽度
  • MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO，保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）。
  1. 禁止在表中建立预留字段
  • 预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定。
  1. 禁止在数据库中存储图片，文件等大的二进制数据
  • 通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时，通常存储于文件服务器，数据库只存储文件地址信息。
  1. 禁止在线上做数据库压力测试
  2. 禁止从开发环境，测试环境直接连接生成环境数据库

6. 数据库表字段类型设计规范

6.1. 基本原则

6.1.1. 优先选择符合存储需要的最小的数据类型（更小的通常更好）

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

一些优化的示例：

  • 将字符串转换成数字类型存储，如：将IP地址转换成整形数据。mysql提供了两个方法来处理ip地址：
    • inet_aton：把ip转为无符号整形（4-8位）
    • inet_ntoa：把整型的ip转成地址
    • 插入数据前，先用inet_aton把ip地址转为整型，可以节省空间。显示数据时，使用inet_ntoa把整型的ip地址转为地址显示即可。
  • 对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储。因为，无符号相对于有符号可以多出一倍的存储空间
    • VARCHAR(N)中的N代表的是字符数，而不是字节数
    • 使用UTF8存储255个汉字Varchar(255)=765个字节。过大的长度会消耗更多的内存

6.1.2. 简单的数据类型更好

简单数据类型的操作通常需要更少的CPU周期。例如：

  • 整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。
  • 应该使用 MySQL 内建的类型而不是字符串来存储日期和时间

6.1.3. 尽量避免 NULL

尽可能把所有列定义为NOT NULL。原因如下：

  • 索引NULL列需要额外的空间来保存，所以要占用更多的空间；
  • 进行比较和计算时要对NULL值做特别的处理。

6.2. 整数类型（int）

MySQL存储整数的数据类型如下表：

整数类型存储空间（位）字节数
TINYINT81
SMALLINT162
MEDIUMINT243
INT324
BIGINT648

同时整数类型有可选的 UNSIGNED 属性，表示不允许负值可以使正数的上限提高一倍。如：TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128~127。

有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。

MySQL 可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如 MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

在整数字段类型选择上，遵循着更小的通常更好的原则，在业务许可的情况下，尽量选择位数小的

6.3. 实数类型

实数是带有小数部分的数字。MySQL 既支持精确浮点类型的存储DECIMAL类型，也支持不精确浮点类型存储FLOAT和DOUBLE类型。

  • DECIMAL 类型用于存储精确的小数，本质上 MySQL 是以字符串形式存放的。所以CPU不支持对DECIMAL类型的直接计算，而MySQL会实现了DECIMAL的高精度计算。相对而言，CPU 直接支持原生浮点计算，所以浮点运算明显更快。
  • 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE使用8个字节，所以DOUBLE比FLOAT有更高的精度和更大的范围。

浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中（每 4 个字节存 9 个数字)。例如，DECIMAL(18,9)小数点两边将各存储 9 个数字，一共使用 9 个字节：小数点前的数字用 4 个字节，小数点后的数字用 4 个字节，小数点本身占 1 个字节。

一般涉及财务相关的金额类数据必须使用decimal类型。因为Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。MySQL 5.0 和更高版本中的DECIMAL类型允许最多65个数字。在精度不敏感和需要快速运算的时候，选择FLOAT和DOUBLE。

实际项目运用中，如果在数据量比较大的而且要求精度时，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

6.4. 字段串类型

MySQL 支持多种字符串类型，包括VARCHAR和CHAR类型、BLOB和TEXT类型、ENUM（枚举）和SET类型。

6.4.1. VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR 是两种最主要的字符串类型。

VARCHAR

VARCHAR 类型用于存储可变长字符串，它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。

在内部实现上，VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度，如果列的最大长度小于或等于 255 字节，则只使用 1 个字节表示，否则使用 2 个字节。

VARCHAR 节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在 UPDATE 时新值比旧值长时，使行变得比原来更长，这就肯能导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM 会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。

CHAR

CHAR 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL 会删除所有的末尾空格，CHAR 值会根据需要采用空格进行填充以方便比较。

适合使用VARCHAR的情况

  • 字符串列的最大长度比平均长度大很多。
  • 列的更新很少；
  • 使用了像 UTF-8 这样复杂的字符集，每个字符都使用不同的字节数进行存储。

适合使用CHAR的情况

  • 适合存储很短的字符串，或者所有值定长或都接近同一个长度。如存储密码MD5值，因为它是定长的
  • 适合长度非常短的列。如CHAR(1)来存储只有 Y 和 N 的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

使用VARCHAR(5)和VARCHAR(200)存储’hello’在磁盘空间上开销是一样的。应该使用更短的列。最好的策略是只分配真正需要的空间。因为更长的列会消耗更多的内存，MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。

6.4.2. BLOB 和 TEXT 类型

BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理，存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要 1~4 个字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB 和 TEXT 之间的区别是 BLOB 类型存储的是二进制数据，没有排序规则或字符集，而 TEXT 类型有字符集和排序规则。

BLOB和TEXT类型使用规范

  • BLOB和TEXT值会引起一些性能问题，所以尽量避免使用BLOB和TEXT类型。最常见的TEXT类型可以存储64k的数据。
  • 如必须使用此两种类型，建议把 BLOB 或 TEXT 的列分离到单独的表中。
    • Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。
    • 而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。
  • 在不必要的时候避免检索大型的 BLOB 或 TEXT 值。避免在包含此两种类型的表使用select *查询，导致网络上传输大量的值。建议可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索 BLOB 或 TEXT 值；
  • 可以使用合成的(Synthetic)索引来提高大文本字段(BLOB 或 TEXT)的查询性能。合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行。注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的)。
  • TEXT或BLOB类型只能使用前缀索引。因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的。

6.5. ENUM 类型

枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中，MySQL 在内部会将每个值在列表中的位置保存为整数，这样的话可以让表的大小大为缩小。

1
2

CREATE TABLE enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL);
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

枚举类型的使用规范：

  • 一般是避免使用ENUM类型。因为修改ENUM值需要使用ALTER语句；ENUM类型的ORDER BY操作效率低，需要额外操作
  • 必要时可使用枚举代替字符串。如果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型。
  • 禁止使用数值作为ENUM的枚举值。因为枚举列实际存储为整数，而不是字符串，所以不要使用数字作为ENUM枚举常量，这种双重性很容易导致混乱，例如ENUN('1','2''3')。
  • 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列。

6.6. 日期和时间类型

MySQL 可以使用许多类型来保存日期和时间值，分别是：DATETIME、DATE、TIMESTAMP、YEAR、TIME。MySQL 能存储的最小时间粒度为秒。

大部分时间类型都没有替代品，因此没有什么是最佳选择的问题。唯一就是DATETIME和TIMESTAMP比较相似，需要做些适当的选择

  • TIMESTAMP（占用空间是4个字节），存储的时间范围1970-01-01 00:00:01 ~ 2038-01-19 03:14:07。TIMESTAMP显示的值也依赖于时区。从空间效率来说，TIMETAMP 比 DATETIME 更高。
  • DATETIME（占用空间是8个字节），存储的时间范围从 1001 年到 9999 年，精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。

日期时间类型使用规范：

  • TIMESTAMP 占用4字节和INT相同，但比INT可读性高。超出TIMESTAMP取值范围的使用DATETIME类型存储。
  • 如果需要存储比秒更小粒度的日期和时间值，MySQL目前没有提供合适的数据类型，但是可以使用自定义的存储格式：可以使用 BIGINT 类型存储微秒级别的时间截，或者使用 DOUBLE 存储秒之后的小数部分。
  • 不应该用字符串存储日期型的数据。
    • 缺点1：无法用日期函数进行计算和比较
    • 缺点2：用字符串存储日期要占用更多的空间

6.7. 其他规范

  • 字段名称不能包含数据类型，不能使用关键字
  • 建议使用 INT UNSIGNED 类型存储 IPV4 地址。用 UNSINGED INT 存储 IP 地址占用 4 字节，CHAR(15) 则占用 15 字节。另外，计算机处理整数类型比字符串类型快。

Tips: MySQL 提供函数 inet_ntoa 和 inet_aton 来对 ip 地址在 int 与 char 之间相互转化。IPv6 地址目前没有转化函数，需要使用 DECIMAL 或两个 BIGINT 来存储。

7. 索引设计规范

7.1. 索引的数量

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

  • 索引并不是越多越好！索引可以提高效率同样可以降低效率。
  • 索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。
  • 因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

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

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

7.3. 主键索引的注意事项

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

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

8. 数据库 SQL 开发规范

  1. 建议使用预编译语句进行数据库操作
    • 预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的 SQL 注入的问题 只传参数，比传递SQL语句更高效 相同语句可以一次解析，多次使用，提高处理效率。
  2. 避免数据类型的隐式转换
    • 隐式转换会导致索引失效。如：select name,phone from customer where id = '111';
  3. 充分利用表上已经存在的索引
    • 避免使用双%号的查询条件。如 a like '%123%'（如果无前置%，只有后置%，是可以用到列上的索引的）
    • 一个SQL只能利用到复合索引中的一列进行范围查询
      • 如：有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到，在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧。
      • 使用left join或 not exists来优化not in操作，因为not in 也通常会使用索引失效。
  4. 数据库设计时，应该要对以后扩展进行考虑
  5. 程序连接不同的数据库使用不同的账号，进制跨库查询
    • 为数据库迁移和分库分表留出余地
    • 降低业务耦合度
    • 避免权限过大而产生的安全风险
  6. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询。原因如下：
    • 消耗更多的CPU和IO以网络带宽资源
    • 无法使用覆盖索引
    • 可减少表结构变更带来的影响
  7. 禁止使用不含字段列表的INSERT语句
    • 如：insert into values (‘a’,‘b’,‘c’);
    • 应使用insert into t(c1,c2,c3) values (‘a’,‘b’,‘c’);
  8. 避免使用子查询，可以把子查询优化为join操作
    • 通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。
    • 子查询性能差的原因：
      • 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能 会受到一定的影响；
      • 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
      • 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
  9. 避免使用JOIN关联太多的表
    • 对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。
    • 在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。
    • 如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
    • 同时对于关联操作来说，会产生临时表操作，影响查询效率Mysql最多允许关联61个表，建议不超过5个。
  10. 减少同数据库的交互次数
    • 数据库更适合处理批量操作 合并多个相同的操作到一起，可以提高处理效率
  11. 对应同一列进行or判断时，使用in代替or
    • in的值不要超过500个in操作可以更有效的利用索引，or大多数情况下很少能利用到索引。
  12. 禁止使用order by rand()进行随机排序
    • 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。
    • 推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。
  13. WHERE从句中禁止对列进行函数转换和计算
    • 对列进行函数转换或计算时会导致无法使用索引。
    • 不推荐：where date(create_time)='20190101'
    • 推荐：where create_time >= '20190101 and create_time < '20190102'
  14. 在明显不会有重复值时使用UNION ALL而不是UNION
    • UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
    • UNION ALL不会再对结果集进行去重操作
  15. 拆分复杂的大SQL为多个小SQL
    • 大SQL：逻辑上比较复杂，需要占用大量CPU进行计算的SQL
    • MySQL：一个SQL只能使用一个CPU进行计算
    • SQL拆分后可以通过并行执行来提高处理效率

9. 数据库操作行为规范

  1. 超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作
    • 大批量操作可能会造成严重的主从延迟
      • 主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
    • binlog日志为row格式时会产生大量的日志
      • 大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因。
    • 避免产生大事务操作
      • 大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响。
      • 特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批。
  2. 对于大表使用pt-online-schema-change修改表结构
    • 避免大表修改产生的主从延迟
    • 避免在对表字段进行修改时进行锁表
      • 对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。
      • pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。
      • 把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。
      • 把原来一个DDL操作，分解成多个小的批次进行。
  3. 禁止为程序使用的账号赋予super权限
    • 当达到最大连接数限制时，还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用。
  4. 对于程序连接数据库账号，遵循权限最小原则
    • 程序使用数据库账号只能在一个DB下使用，不准跨库 程序使用的账号原则上不准有drop权限。