如何更规范化使用MySQL

共计 5666 个字符，预计需要花费 15 分钟才能阅读完成。

背景：一个平台或系统随着时间的推移和用户量的增多，数据库操作往往会变慢；而在 Java 应用开发中数据库更是尤为重要，绝大多数情况下数据库的性能决定了程序的性能，如若前期埋下的坑越多到后期数据库就会成为整个系统的瓶颈；因此，更规范化的使用 MySQL 在开发中是不可或缺的。

一、MySQL 数据库命名规范

1、数据库所有表前缀均使用项目名称首字母缩写；

2、数据库所有对象名称均使用小写字母，并且单词之间通过下划线分开；

3、数据库所有对象名称禁止使用 MySQL 保留字及关键字，涉及到关键字的 SQL 查询需要将关键字用单引号括起来；

4、数据库所有对象名称不超过 32 个字符，并且命名要遵循见名知意原则；

5、数据库临时表必须以 pro_tmp_ 为前缀并且以日期 _20190917 为后缀，备份表必须以 pro_bac_ 为前缀并以时间戳为后缀；(pro 为项目名称首字母缩写)

6、数据库所有存储相同数据的列名和列类型必须保持一致。

二、MySQL 数据库基本设计规范

1、若无特殊说明，建表时一律采用 Innodb 存储引擎。

      选择合适的引擎可以提高数据库性能，如 InnoDB 和 MyISAM，InnoDB 和 MyISAM 是许多人在使用 MySQL 时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定；基本的差别为：MyISAM 类型不支持事务处理等高级处理，而 InnoDB 类型支持；MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快，但是不提供事务支持，而 InnoDB 提供事务支持以及外部键等高级数据库功能；因此，其支持事务处理、支持外键、支持崩溃修复能力和并发控制是我们建表时首选的存储引擎。

2、数据库和表的字符集统一使用 UTF8

      数据库和表的字符集统一使用 utf8，若是有字段需要存储 emoji 表情之类的，则将表或字段设置成 utf8mb4；因为，utf8 号称万国码，其无需转码、无乱码风险且节省空间，而 utf8mb4 又向下兼容 utf8。

3、设计数据库时所有表和字段必须添加注释

      使用 Comment 从句添加表和列的备注，或直接在数据库连接工具的注释栏添加注释，从项目开始就进行数据字典的维护。

使用 Comment 从句 添加注释 如：

   1、创建表：CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);
  2、添加表注释：Comment on table t1 is '个人信息';
  3、添加字段注释：comment on column t1.id is 'id';
  comment on column t1.nameis '姓名';
  comment on column t1.age is '年龄'; 

使用数据库连接工具添加注释：

图 1. 数据库连接工具添加注释

4、单个表的数据量大小控制在 500 万以内

      尽量控制单表数据量的大小，建议控制在 500 万以内；500 万并不是 MySQL 数据库的极限，但数据量太多不利于对表结构进行修改、备份和恢复数据，适当采用分库分表等手段来控制单表数据量的大小。

5、使用 MySQL 分区表需谨慎

      分区是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分，但是逻辑上仍是一个表；分区表在物理上表现为多个文件，在逻辑上仍表现为同一个表，需要谨慎选择分区键；跨分区查询效率可能会更低，建议使用物理分区表等方式管理大数据。

6、尽量满足冷热数据分离，减小表等宽度

      MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不超过 65535 字节，为了减少磁盘 IO 线程的开销，就要适当控制表的宽度，因为表越宽，把表装载进内存缓冲池时所占用的内存也就越大，就会消耗更多的 IO 线程；除此之外，为了保证热数据的内存缓存命中率，更有效的利用缓存，避免读入无用的冷数据，尽量把经常使用到的列放到同一个表中，避免不必要的关联操作。

7、建立预留字段需谨慎

      部分友人在设计数据库表时，不仅设计了当前所需要的字段，而且还在其中留出几个字段作为备用。比方说，我设计了一个人员表（Person），其中已经添加了各种必要的字段，包括姓名（Name）、性别（Sex）、出生年月日（birthday）等等；为了以防万一，比如之后可能 Person 表会涉及到毕业院校、工作单位、是否婚配和相片等信息，于是就加入 5 个 varchar2 型的字段，分别叫做 Text1、Text2……Text5；这一手操作看似防范于未然，其实也并不见得，因为大量预留字段会浪费空间、预留字段不能做到见名知意、预留字段无法确认存储的数据类型且修改其字段类型还可能会造成锁表等问题。

针对此等情况可以参考以下两点解决方案：

1. 如果数量很少，而且信息的性质与原表密切相关，那么就可以直接在原表上增加字段，并将相关的数据更新进去；
2. 如果数量较大，或者并非是原表对象至关重要的属性，那么就可以新增一个表，然后通过键值连接起来；

8、数据库中禁止存储图片、文件等大的二进制数据

      若往数据库表中存储文件，而文件通常很大，当数据库进行读取操作时，会进行大量的随机 IO 操作，大文件使得 IO 操作很耗时耗性能，造成短时间内数据量快速增长；所以，通常将图片、文件存储在文件服务器中，数据库只用于存储文件地址信息。

三、MySQL 数据库字段设计规范

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

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

2、避免使用 TEXT、BLOB 数据类型

      避免使用 TEXT 和 BLOB 数据类型，其中最常见的 TEXT 类型可以存储 64K 数据，MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，若查询中包含这样的数据，在执行排序等操作时就不能使用内存临时表，必须使用磁盘临时表执行操作；TEXT 和 BLOB 类型只能使用前缀索引(当索引是很长的字符序列时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了；所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，所以我们还必须要判断前缀索引的重复率；)，因为 MySQL 对索引字段长度是有限的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的；若需要使用，建议把 BLOB 或 TEXT 列分离到单独的的扩展表中，且查询时一定不要使用 select *，只需取出必要的列即可。

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 类型存储；

用字符串类型存储时间的缺点：无法使用日期函数进行比较计算、字符串存储占有更多的空间。

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

精准浮点：decimal

非精准浮点：float、double

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

四、MySQL 索引设计规范

1、每张表的索引数量不超过 5 个

      索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下还会降低查询效率，因此并不是越多越好，要控制其数量。

2、每个 Innodb 表必须有一个主键

Innodb 是一种索引组织表，其数据存储的逻辑顺序和索引的顺序是相同的；

每张表可以有多个索引，但表的存储顺序只能有一种，Innodb 是按照主键索引的顺序来组织表的，因此不要使用更新频繁的列、UUID、MD5、HASH 和字符串列作为主键，这些列无法保证数据的顺序增长，主键建议使用自增 ID 值。

3、尽量避免使用外键约束

不建议使用外键约束(foreign key)，但一定要在表与表之间的关联键上建立索引；

外键虽然可以保证数据的参照完整性，但外键也会影响父表和子表的写操作从而降低性能，还会使得表更耦合，建议在业务端实现。

五、MySQL 数据库 SQL 开发规范

1、建议使用预编译语句进行数据库操作

      预编译语句可以重复使用，相同的 SQL 语句可以一次解析，多次使用，减少 SQL 编译所需要的时间，提高处理效率；此外，还可以有效解决动态 SQL 带来的 SQL 注入问题。

2、避免数据类型的隐式转换

      隐式转换如：SELECT 1 + “1”；数值型 + 字符型 的隐式转换 有可能会导致索引失效 ，以及一些 意想不到的结果 等。

3、充分利用表中存在的索引

1）避免使用双 % 号的查询条件

      如 WHERE first_name like ‘%James%’，若无前置 %，只有后置 %，则执行 SQL 语句时会用到列上的索引，双 % 号则不会使用列上的索引。

2）一条 SQL 语句只能使用复合索引中的一列进行范围查询

      例如有 weight、age、sex 三列的联合索引，在查询条件中有 weight 列的范围查询，则在 age 和 sex 列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。

3）使用 not exists 代替 not in

      因为 not in 在 SQL 语句中执行时会导致索引失效。

4、杜绝使用 SELECT *，必须使用 SELECT < 字段列表 > 查询

      因为使用 SELECT * 查询会消耗更多的 CPU、IO 和网络宽带资源，并且查询时无法使用覆盖索引。

5、禁止使用不含字段列表的 INSERT 语句

      如：INSERT into table_name values (‘1′,’2′,’3’); 改为带字段列表的 INSERT 语句：INSERT into table_name(‘c1′,’c2′,’c3’) values (‘1′,’2′,’3’);

6、避免使用子查询，可以把子查询优化为 join 关联操作

但是，通常子查询在 in 子句中，且子查询中为简单 SQL(即不包含 union、group by、order by、limit 从句)时，才可以把子查询转化为 join 关联查询进行优化；

子查询性能差的原因：

1）子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；

2）由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

7、避免使用 JOIN 关联太多表

1）在 Mysql 中，对于同一个 SQL 关联（join）多个表，每个 join 就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大；

2）如果程序中大量的使用了多表关联的操作，同时 join_buffer_size(MySQL 允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；

3）此外，对于关联操作来说，会产生临时表影响查询效率，而 Mysql 最多允许关联 61 个表，建议不超过 5 个；

8、对同一列对象进行 or 判断时，使用 in 替代 or

      in 的值只要涉及不超过 500 个，则 in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

9、禁止使用 order by rand() 进行随机排序

10、禁止在 WHERE 从句中对列进行函数转换和计算

      因为在 WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。

No 推荐：

where date(end_time)='20190101'

推荐：

where end_time >= '20190101' and end_time < '20190102'

11、在明显不会有重复值时使用 UNION ALL 而不是 UNION

1）UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；

2）UNION ALL 不会再对结果集进行去重操作；

12、把复杂、较长的 SQL 拆分为为多个小 SQL 执行

1）大 SQL 在逻辑上比较复杂，是需要占用大量 CPU 进行计算一条 SQL 语句；

2）在 MySQL 中，一条 SQL 语句只能使用一个 CPU 进行计算；

3）SQL 拆分后可以通过并行执行来提高处理效率。

六、MySQL 数据库行为规范

1、超过 100 万行数据的批量操作(update delete insert)，分多次进行

大批量操作可能回造成严重的主从延迟；

binlog 日志为 row 格式时会产生大量的日志；

避免产生大事物操作。

2、对于大表使用 pt-online-schema-change 修改表结构

1）避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；

2）pt-online-schema-change 它首先会建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器；然后，把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉，其是把原来一个 DDL 操作，分解成多个小的批次执行。

3、禁止给程序使用的账号授予 super 权限

      当达到最大连接数限制时，还运行 1 个有 super 权限的用户连接 super 权限只能留给 DBA 处理问题的账号使用。

4、对于程序连接数据库账号，遵循权限最小原则

      程序使用数据库账号只能在一个数据库下使用，且程序使用的账号原则上不授予 drop 权限。

：