数据库命名规范
- 所有数据库对象名称必须使用小写字母并用下划线进行分割。
MySql 是对大小写敏感的，数据库、表、字段的名称，如果在命名时大小写混写，会对之后的开发带来不必要的麻烦。
- 所有数据库对象名称禁止使用 MySql 保留关键字
SELECT id,username,from age FROM tb_user
上面的语句中，
- 数据库对象的命名尽量要做到见名识意，并且最好不要超过 32 个字符。
MySql 并没有对命名有明确的要求，32 个也不是最大的限制，一切都是为了规范。
-
所有的临时表必须以 tmp 为前缀，以日期为后缀。
-
备份库，备份表必须以 bak 为前缀并以日期为后缀。
以上两条，均可以更好的表明该数据库对象的意图，并方便管理。
- 所有存储相同数据的列名和列类型必须一致。
比如 orders 表和 contracts 表中，均有 customer_id 字段，那么要求在这两个表中，customer_id 字段的数据类型必须一致。
作为关系型数据库，有些表中的字段是关联字段，如果关联字段的类型不一样，在查询的过程中，需要进行数据类型的转换，不仅降低了性能还会造成索引失效。
数据库基本设计规范
- 使用 InnoDB，而不是 MyISAM 作为存储引擎。
在 Mysql 的 5.6 版本之后，开始使用 InnoDB 作为默认的存储引擎，InnoDB 支持事物、行级锁，有更好的恢复性，在高并发下性能更好。
- 数据库和表的字符集统一使用 UTF8。
统一字符集可以避免由于字符集转换产生的乱码，UTF8 字符集对中文字符支持较好。MySql 中 UTF8 字符集汉字点占用 3 个字节，ASCII 码占用 1 个字节。
-
所有的表和字段都需要添加注释。
-
尽量控制单表数据量的大小，建议控制在 500 万行以内。
500 万并不是 MySql 数据库的限制，InnoDB 并没有明确显示单表中行数的限制，这取决于文件系统的限制。
可以使用历史数据归档，分库分表等手段来控制数据量的大小。
- 谨慎使用 MySql 分区表。
分区表在物理上表现为多个文件，在逻辑上表现为一个表。谨慎选择分区键，跨分区查询效率可能更低。建议采用物理分表的方式管理大数据。
- 尽量做到冷热数据分离，减少表的宽度。
MySql 对单表列的宽度限制是 4096, 单个列的数据长度 65535。
为了减少磁盘 IO，保证热数据的内存缓存命中率。
更有效地利用缓存，避免读入无用的冷数据。
经常使用的列放到一个表中。
- 禁止在表中建立预留字段。
预留字段的命名很难做到见名识义。
预留字段无法确认存储的数据类型，所以无法选择合适的类型。
对预留字段类型的修改，会对表进行锁定。修改一个表的字段类型，代价甚至高于增加一个字段。
- 禁止在数据库中存储图片，文件等二进制数据。
这种文件通常要使用 blob 等数据类型，影响数据库的性能。IO 操作频繁时，压力较大。
-
禁止在线上做数据库压力测试。
-
禁止从开发环境、测试环境直接生产环境数据库。
索引设计规范
索引对数据库的查询性能来说是非常重要的，不能滥用索引，以防对性能造成不好的影响。
- 限制每张表上的索引数量，建议单张表的索引数量不超过 5 个。
索引不是越多越好，所以可以提高效率同样也可以减低效率。因为索引可以增加查询效率，但同样也会降低插入和更新的效率。
禁止给表中的每一列都建立单独的索引。
InnoDB 是索引组织表，这意味着数据的逻辑存储顺序和物理存储于顺序是一直的，表必须有一个主键。
不使用更新频繁的列作为主键，不使用多列主键。
不使用 UUID、MD5、HASH、字符串作为主键，因为这种数据的值是不可预估的。
主键建议使用自增 id 值。
- 常见索引列建议
SELECT、UPDATE、DELETE 等查询语句中 WHERE 从句中的列。
包含在 ORDER BY、GROUP BY、DISTINCT 中的字段。
多表 JOIN 的关联列。
- 如何选择索引列的顺序
区分度最高的列放在联合索引的最左侧。
尽量把字段长度小的列放在联合索引的最左侧。
使用最频繁的列放到联合索引的左侧。
- 避免建立冗余所以和重复索引。
primary key(id) index(id) unique index(id) //重复
index(a,b,c) index(a,b) index(a) //冗余
- 对于频繁的查询，优先考虑使用覆盖索引
避免 InnoDB 表进行索引的二次查找。
可以把随机 IO 变为顺序 IO 加快查询效率。
- 尽量避免使用外键约束。
不建议使用外键约束，但一定在表与表之间的关联键上建立索引。外键可用于保证数据的参照完整性，但建议在业务端实现。外键会影响父表和子表的写操作从而降低性能。
数据库字段设计规范
- 优先选择符合存储需要的最小的数据类型。
将字符串转化为数字类型存储，如 IP 地址可以转换为整型存储。
inet_aton('255.255.255.255') = 4294967295
inet_ntoa(4294967295) = '255.255.255.255'
对于非负数，优先使用无符号整型数的类型。无符号相对于有符号合一多出一倍的存储空间。
-
VARCHAR(N) 中的 N 代表的是字符数，而不是字节数，这可能与其他的关系型数据库不同。
-
使用 UTF8 存储汉字，每个汉字占 3 个字节，那么 VARCHAR(255) = 765 个字节。
-
过大的长度会消耗更多的内存，物理存储时是按照实际字节长度存储的，但是读到内存确实按照定义的字节数分配的。
-
尽量避免使用 TEXT、BLOB 数据类型，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。TEXT 或 BLOB 类型只能使用前缀索引。
-
避免使用 ENUM 数据类型。
修改 ENUM 值需要使用 ALTER 语句。
ENUM 类型的 ORDER BY 操作效率低，需要额外操作。
禁止使用数值作为 ENUM 的枚举值。
- 尽可能把所有列定为 NOT NULL
索引 NULL 需要额外的空间来保存，所以要占用更多的空间。
进行比较和计算时要对 NULL 值做特别的处理，会造成索引的失效。
- 避免使用字符串存储日期型的数据，使用 TIMESTAMP(4字节) 或 DATETIME(8字节)存储时间
无法用日期函数进行计算和比较。
用字符串存储日期要占用更多的空间。
- 同财务相关的金额类数据，必须使用 decimal 类型。
decimal 类型为精准浮点数，在计算时不会丢失精度。
占用空间由定义的宽度决定。
可用于存储比 bigint 更大的整型数据。
数据库 SQL 开发规范
- 建议使用预编译语句进行数据库操作
只传参数，比传递 SQL 语句更高效。相同语句可以一次解析，多次使用，提高处理效率。
可以有效地避免 sql 注入。
mysql> PREPARE stmt1
-> FROM `SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse`;
mysql> SET @a=3;
mysql> SET @b=4;
mysql> EXECUTE stmt1 USING @a,@b;
mysql> EXECUTE stmt1 USING @a,@b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set
mysql> deallocate prepare stmt1;
Query OK, 0 rows affected
- 避免数据类型的隐式转换
隐式转换会导致索引失效。
充分利用表上已经存在的索引，避免使用双 % 号的查询条件，后者只有前置 %。后置的 % 是可以利用索引的。
一个 SQL 只能利用到复合索引中的一列进行行范围查询。
使用 left join 或 not exists 来优化 not in 操作，not in 操作会造成索引失效。
-
程序连接不同的数据库，需使用不同的账号，禁止跨库查询。
-
禁止使用 SELECT * ，必须书提 SELECT <字段表列> 查询。
消耗更多的 CPU 和 IO 以及网络带宽资源。
无法使用覆盖索引。
可减少表结构变更带来的影响（好处）。
-
禁止使用不含字段列表的 INSERT 语句，可减少表结构的变更带来的影响。
-
避免使用子查询，可以把子查询优化为 join 操作。
子查询的结果集无法使用索引。子查询会产生临时操作表，如果子查询数据量大则严重影响效率。消耗过多的 CPU 及 IO 资源。
- 避免使用 JOIN 关联太多的表。
每 JOIN 一个表会多占用一部分内存（join_buffer_size）。
会产生临时表操作，影响查询效率。
MySql 最多允许关联 61 个表，建议不超过 5 个。
- 减少同数据库的交互次数，因为数据库更适合处理批量操作。
合并多个相同的操作到一次，可以提高处理效率。
-
使用 in 代替 or，in 操作可以有效的利用索引，in 的值不要超过 500 个。
-
禁止使用 order by rand() 进行随机排序。
会把表中所有符合条件的数据装载到内存中进行排序，会消耗大量的 cpu 和 io 及内存资源，推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。
- WHERE 从句中禁止对列进行函数转换和计算
对列进行函数转换或转换会无法在该列上使用索引。
-
在明显不会有重复值时使用 UNION ALL 而不是 UNION，UNION 操作会把所有的数据放到临时表中后再进行去重操作，而 UNION ALL 不会再对结果集进行去重操作。
-
拆分复杂的大 SQL 为多个小 SQL
MySql 中，一个 SQL 只能使用一个 CPU 进行计算。
SQL 拆分后可以通过并行来提高执行效率。
数据库操作行为规范
- 超 100 万行的批量写操作，要分批多次进行操作。
大批量的写操作，可能会造成严重的主从延迟。
binlog 日志为 row 格式时会产生大量的日志。
避免产生大量事务操作，以免造成长时间的阻塞。
-
对大表数据结构的修改一定要谨慎，会造成严重的表锁操作。尤其是生产环境，是不能忍受的。对于大表，使用 pt-online-schema-change 修改表结构。
-
禁止为程序使用的账号赋予 super 权限。
当达到最大连接数量限制时，还允许 1 个有 super 权限的用户连接，因此，super 权限只能留给 DBA 处理问题的账号使用。
- 对于程序连接数据库账号，遵循权限最小原则。
程序使用数据库账号只能在一个 DB 下使用，不准跨库。
程序使用的账号原则上不准有 drop 权限。
