如何更规范化使用MySQL

如何更规范化使用mysql

 

背景：一个平台或系统随着时间的推移和用户量的增多，数据库操作往往会变慢；而在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 权限。