数据库使用规范

数据库命名规范

• 所有的数据库对象名称（包括库名、表名、列名等）建议以小写字母命名，每个单词之间用下划线分隔。
• 所有的数据库对象名称禁止使用RDS for MySQL保留关键字。
  • MySQL官方保留字与关键字（MySQL 8.0）：https://dev.mysql.com/doc/refman/8.0/en/keywords.html
  • MySQL官方保留字与关键字（MySQL 5.7）：https://dev.mysql.com/doc/refman/5.7/en/keywords.html
• RDS for MySQL的保留关键字在兼容社区MySQL8.0的基础上，新增了部分保留关键字，需要在业务使用中避免使用保留关键字来命名。

  表1 RDS for MySQL新增的保留关键字

  保留字	相关场景
  RECYCLE_BIN	回收站

• 数据库对象的命名要能做到见名知意，并且不超过32个字符。
• 数据库中用到的临时表以“tmp”为前缀并以日期为后缀。
• 数据库中用到的备份表以“bak”为前缀并以日期为后缀。
• 在不同的库或表中，要保证所有存储相同数据的列名和列类型必须一致。

数据库基本设计规范

• 所有表如果没有特殊需求，都要使用InnoDB存储引擎。InnoDB存储引擎支持事务、行级锁、具有更好的恢复性、高并发下性能更强。
• 数据库和表的字符集统一使用UTF8字符集，避免由于字符集的转换产生乱码。
• 所有的表和字段都需要添加注释。使用comment从句添加表和列的备注，从设计初期维护好数据字典。
• 表单行长度不得超过1024字节。
• 谨慎使用RDS for MySQL分区表，避免跨分区查询，否则查询效率会降低。分区表在逻辑上表现为一个表，但是在物理层面上将数据存储在多个文件。
• 表中的列不要太多，尽量做到冷热数据分离，减小表的宽度，以便在一页内存中容纳更多的行，进而减少磁盘IO，更有效的利用缓存。
• 经常一起使用的列尽量放到一个表中，避免过多的关联操作。
• 禁止在表中建立预留字段，否则修改列的类型会导致锁表，修改一个字段类型的成本要高于增加一个字段。
• 禁止在数据库中存储图片、文件等大的二进制数据。
• 不建议使用全文索引，社区MySQL全文索引局限性较多。
• 表数量不建议超过2万张。
• 客户端连接保持时长不建议超过8小时。
• 为避免高并发场景下数据库实例OOM，建议tmp_table_size、innodb_buffer_pool_size、max_connections、sort_buffer_size、read_buffer_size、read_rnd_buffer_size、join_buffer_size、thread_stack、binlog_cache_size等参数配置不要超过默认值。

数据库字段设计规范

• 控制单表字段数量，字段上限50左右。
• 优先为表中的每一列选择符合存储需要的最小的数据类型。优先考虑数字类型，其次为日期或二进制类型，最后是字符类型。列的字段类型越大，建立索引占据的空间就越大，导致一个页中的索引越少，造成IO次数增加，从而影响性能。
• 整数型选择能符合需求的最短列类型，如果为非负数，声明需是无符号（UNSIGNED）类型。
• 每个字段尽可能具有NOT NULL属性，int等数字类型默认值推荐给0，varchar等字符类型默认值给空字符串。
• 避免使用ENUM类型，可以用TINYINT类型替换。

  修改ENUM值需要使用ALTER语句，ENUM类型的ORDER BY操作效率低，需要额外操作。

  如果定义了禁止ENUM的枚举值是数值，可使用其他数据类型（如char类型）。

• 实数类型使用DECIMAL，禁止使用FLOAT和DOUBLE类型。

  FLOAT和DOUBLE在存储的时候，存在精度损失的问题，很可能在值的比较时，得到错误的结果。

• 使用datetime、timestamp类型来存储时间，禁止使用字符串替代。
• 使用数字类型INT UNSIGNED存储IP地址，用INET_ATON、INET_NTOA可以在IP地址和数字类型之间转换。
• VARCHAR类型的长度应该尽可能短。VARCHAR类型虽然在硬盘上是动态长度的，但是在内存中占用的空间是固定的最大长度。
• 使用VARBINARY存储大小写敏感的变长字符串，VARBINARY默认区分⼤小写，没有字符集概念，速度快。

数据库索引设计规范

• 每个InnoDB表强烈建议有一个主键，且不使用更新频繁的列作为主键，不使用多列主键。不使用UUID、MD5、字符串列作为主键。建议选择值的顺序是连续增长的列作为主键，所以建议选择使用自增ID列作为主键。
• 限制每张表上的索引数量，建议单张表索引不超过5个。索引并不是越多越好，索引可以提高查询的效率，但会降低写数据的效率。有时不恰当的索引还会降低查询的效率。
• 禁止给表中的每一列都建立单独的索引。设计良好的联合索引比每一列上的单独索引效率要高出很多。
• 建议在下面的列上建立索引：
  • 在SELECT，UPDATE，DELETE语句的WHERE从句上的列。
  • 在ORDER BY，GROUP BY，DISTINCT上的列。
  • 多表JOIN的关联列。
• 索引列顺序：
  • 区分度最高的放在联合索引的最左侧。区分度=列中不同值的数量/列的总行数。
  • 尽量把字段长度小的列放在联合索引的最左侧。因为字段长度越小，一页能存储的数据量越大，IO性能也就越好。
  • 使用最频繁的列放到联合索引的左侧。这样可以比较少的建立一些索引。
• 避免冗余的索引，如：primary key(id)，index(id)，unique index(id)
• 避免重复的索引，如：index(a,b,c)，index(a,b)，index(a)，重复的和冗余的索引会降低查询效率，因为RDS for MySQL查询优化器无法选择使用目标索引。
• 在VARCHAR字段上建立索引时，需指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

  一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。

• 对于频繁查询优先考虑使用覆盖索引。

  覆盖索引指包含了所有查询字段的索引，不仅仅是WHERE从句GROUP BY从句中的列，也包含SELECT查询的列组合，避免InnoDB表进行索引的二次查询。

• 外键约束：

  建立外键关系的对应列的字符集必须保持一致或者存在外键关系的子表父表的字符集保持一致。

数据库SQL开发规范

• 在程序中，建议使用预编译语句进行数据库操作。预编译只编译一次，以后在该程序中就可以调用多次，比SQL效率高。
• 避免数据类型的隐式转换，隐式转换会导致索引失效。

  禁止在where从句中对列进行函数转换和计算，会导致索引失效。

• 避免使用双%号或前置%号的查询条件，这样无法利用到索引。
• 禁止在查询中使用select *语句。原因如下：
  • 使用select *会消耗更多的CPU和IP以及网络带宽资源。
  • 使用select *无法使用覆盖索引。
  • 不使用select *可以减少表结构变更对代码带来的影响。
• 避免使用子查询，子查询会产生临时表，临时表没有任何索引，数据量大时严重影响效率。建议把子查询转化成关联查询。
• 避免使用JOIN关联太多的表，建议不要超过5个表的JOIN操作。需要JOIN的字段，数据类型必须一致。

  每JOIN一个表会多占用一部分内存（由“join_buffer_size”控制），会产生临时表操作，影响查询效率。避免使用自然连接（natural join）。

• 尽量减少同数据库的交互次数，数据库更适合处理批量操作。
• 使用IN代替OR，IN操作可以有效地利用索引，IN的值不要超过500个。
• 不使用反向查询，如：NOT IN、NOT LIKE
• 禁止使用ORDER BY RAND()进行随机排序。

  该操作会把表中所有符合条件的数据装载到内存中进行排序，消耗大量的CPU和IO及内存资源。

  推荐在程序中获取一个随机值，然后根据随机值从数据库获取数据。

• 在不需要去重的情况下，要使用UNION ALL代替UNION。

  UNION ALL不需要对结果集再进⾏排序。

• 合并多个相同操作到一起，可以提高处理效率，数据库更适合处理批量操作。

  通过批量操作减少同数据库交互次数。

• 超过100万行的批量写操作，要分批多次进行操作。

  大批量写操作可能会造成严重的主从延迟。

• 如果有ORDER BY的场景，请注意利用索引的有序性。
  • ORDER BY最后的字段是组合索引的一部分，并且放在索引组合顺序的最后。
  • 避免出现file_sort的情况，影响查询性能。

  正例：where a=? and b=? order by c;，索引：a_b_c

  反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b;，索引a_b无法排序。

• 尽量使用ANSI SQL标准语法进行DML操作，而不是用MySQL扩展的SQL语法。常见的MySQL扩展SQL语法有：
  • REPLACE INTO
  • INSERT ... ON DUPLICATE KEY UPDATE
• 不建议使用存储过程，存储过程难以调试和扩展，更没有移植性。
• 不建议使用触发器，事件调度器（event scheduler）和视图实现业务逻辑，这些业务逻辑应该在业务层处理，避免对数据库产生逻辑依赖。
• 不建议使用大事务，业务允许的情况下，事务里包含SQL语句越少越好，尽量不超过5个。因为过长的事务会导致锁数据较久，以及MySQL内部缓存、连接消耗过多等问题。
• TRUNCATE TABLE比DELETE速度快，且使用的系统和日志资源少，如果删除的表上没有TRIGGER，且进行全表删除，建议使用TRUNCATE TABLE。
• 建议不要频繁执行flush logs，可能会导致Binlog自动清理失败。
• 不建议事务持续时间超过180秒，大于30秒的事务并发数不建议超过十个。
• 不建议单个事务修改行数超过100万行。
• 不建议执行系统自动生成的较大文本的SQL语句。例如9 MB的SELECT语句文本，数据库执行阶段内存增长约37 MB，内存增长约为SQL的4倍。

数据库不建议修改的会话级参数

• foreign_key_checks

  参数解释：bool类型，默认全局值为ON。在创建外键的时候，如果设置为ON，则会检查外键的规范性，例如外键不能reference同一张表中的键等。

  不建议修改的原因：

  • foreign_key_checks值为ON时，无法通过该参数检查的外键，本身就是不规范的使用方法，建议优化SQL，规范数据库使用。
  • 如果该参数线程级设置为了OFF，一些不规范的外键在主机被创建出来，但由于备机仍为ON，这些外键创建的DDL语句会在备机复制时执行不通过，导致复制异常。详见外键使用不规范导致实例重启失败或执行表操作报错ERROR 1146: Table 'xxx' doesn't exist。
  • 单机实例也建议遵循foreign_key_checks为ON的外键规范性检查。

• innodb_strict_mode

  参数解释：bool类型，默认全局值为ON。如果设置为ON，那么一些不规范的InnoDB表操作，会直接报错而非warning，例如：InnoDB页面为16KB时，单行的大小超过了8KB。

  不建议修改的原因：

  • 最常见的一个异常场景是，如果主机线程级设置成了OFF，使用Alter Table DDL加列时，如果字段数较多，单行长度超过了8KB，在主机能执行成功，但因为备机该参数仍为ON，导致备机该Alter DDL执行直接报错，出现复制异常。
  • 如果有业务需求，建议Global级别主备一起修改。

• default_storage_engine

  参数解释：枚举值类型，只能填可用的存储引擎名，默认全局值为InnoDB。在CREATE/ALTER TABLE DDL语句未显式指定存储引擎时，选择的默认存储引擎。

  不建议修改的原因：

  • 由于RDS for MySQL产品主备复制使用的是开源社区基于Binlog的复制模式，建表语句中，如果没有显式指定存储引擎的CREATE/ALTER TABLE DDL语句，不会把默认的存储引擎记录到Binlog（与社区行为一致）。而在备机复制，执行该DDL时，会使用备机复制线程的default_storage_engine（为全局的InnoDB）。
  • 如果主机session级别修改该参数为非InnoDB的存储引擎，执行CREATE/ALTER DDL语句就会出现主机创的表定义为非InnoDB引擎，而备机的表定义为InnoDB的不一致情况。

• unique_checks

  参数解释：bool类型，默认全局值为ON。如果设置为ON，对InnoDB表中二级索引唯一键进行唯一性检查。

  不建议修改的原因：

  如果主机session级设为OFF，则不会进行二级索引的唯一键的唯一性检查，二级索引唯一键重复的DML语句会执行成功。但是在备机，同样的DML会因为唯一性检查执行失败，导致复制异常。

• sql_log_bin

  参数解释：默认ON，控制当前会话的SQL是否计入Binlog。

  不建议修改的原因：

  RDS for MySQL产品主备复制使用的是开源社区基于Binlog的复制模式。如果主机单个会话的参数值设为OFF，则对表的修改不会计入Binlog，该修改无法同步到备机，导致主备数据不一致。

• old_alter_table

  参数解释：bool值，默认OFF。当值为ON时，在ALTER TABLE语句中使用基于COPY临时表实现的算法，会影响数据库性能，一般情况下不建议开启。

  不建议修改的原因：

  • 涉及ALTER IGNORE选项在备机可能会回放失败。
  • 基于COPY实现的ALTER TABLE性能较差。