背景随着时间的推移，平台或系统上的用户数量增加，数据库操作往往会变慢；和数据库在Java应用程序开发中尤为重要。在大多数情况下，数据库的性能决定了程序的性能。如果前期埋的坑多了，后期数据库就会成为整个系统的瓶颈；因此，在开发中更规范地使用MySQL是必不可少的。1.MySQL数据库命名规范数据库中所有表前缀均使用项目名称的首字母缩写；数据库中的所有对象名称均使用小写字母，单词之间用下划线分隔；数据库中所有对象名禁止使用MySQL保留字和关键字，涉及关键字的SQL查询需要将关键字用单引号括起来；数据库中所有对象的名称不超过32个字符，命名遵循知名原则；数据库临时表必须以pro_tmp_为前缀，以日期20190917为后缀，备份表必须以pro_bac为前缀，以时间戳为后缀；（pro是项目名的首字母缩写）数据库中存储相同数据的所有列名和列类型必须一致。二、MySQL数据库基本设计规范1、如无特殊说明，建表时将使用Innodb存储引擎。选择合适的引擎可以提高数据库的性能，比如InnoDB和MyISAM。InnoDB和MyISAM是很多人在使用MySQL时最常用的两种表类型。这两种表类型各有优缺点，视具体应用而定；basic的区别是：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持；MyISAM类型表强调性能，执行速度比InnoDB类型快，但不提供事务支持，而InnoDB提供事务支持和外键等高级数据库功能；因此，它对事务处理的支持，对外键的支持，对崩溃恢复能力和并发控制的支持，是我们在建表时首选的存储引擎。2、数据库和表的字符集统一使用UTF8。数据库和表的字符集统一使用utf8。如果有字段需要存储emoji表情，将表或字段设置为utf8mb4；因为utf8被称为万能码，不需要转码，没有乱码风险，节省空间，而且utf8mb4向下兼容utf8。3、设计数据库时，所有的表和字段都必须进行注释。使用Comment子句为表和列添加注释，或者直接在数据库连接工具的注释列中添加注意数据字典的维护是从项目开始就进行的。使用Comment子句添加注释如：--1.创建表：CREATETABLEt1(idvarchar2(32)primarykey,nameVARCHAR2(8)NOTNULL,agenumber);--2.添加表注释：Commentontablet1is'personalinformation';--3.添加字段注释：commentoncolumnt1.idis'id';commentoncolumnt1.nameis'name';commentoncolumnt1.ageis'age';使用数据库连接工具添加注释：4、单表数据量控制在500万以内，尽量将单表数据量控制在500万以内，建议数据库大小控制在500万以内；500万不是MySQL数据库的限制，但数据过多不利于修改表结构、备份和恢复数据，适当采用分库分表等方式控制单表数据量的大小。5、使用MySQL分区表需谨慎。分区就是把一个表的数据按照一定的方式分成多个更小更便于管理的部分，比如按照时间的月份，但逻辑上还是一张表；partitiontable物理上表现为多个文件，但逻辑上仍表现为同一张表，需要慎重选择partitionkey；跨分区查询效率可能较低，建议使用物理分区表来管理大数据。6、尽量满足冷热数据分离，减少表的宽度。MySQL限制每张表最多存储4096列，每行数据大小不超过65535字节。为了减少磁盘IO线程的开销，需要对表进行适当的控制。宽度，因为表越宽，加载表到内存缓冲池时占用的内存越大，会消耗更多的IO线程；另外，为了保证热数据的内存缓存命中率，更有效的使用缓存避免读取无用的冷数据，尽量将经常使用的列放在同一个表中，避免不必要的关联操作。7.保留字段的设置需谨慎。有些朋友在设计数据库表的时候，不仅设计了当前需要的字段，还预留了几个字段备用。比如我设计了一个人事表（Person），里面添加了各种必填字段，包括姓名（Name）、性别（Sex）、出生日期（birthday）等；为了以防万一，比如以后Person表可能会涉及到毕业学校、工作单位、是否结婚、照片等信息，所以增加了5个varchar2类型的字段，分别叫做Text1、Text2...Text5;这种手动操作似乎是预防性的。其实也不一定，因为大量的保留字段会浪费空间，保留字段不能众所周知，保留字段不能确认存储的数据类型，修改字段类型也可能会导致诸如锁定表。对于这种情况，可以参考以下两种解决方案：如果数量较少，且信息性质与原表密切相关，则可以直接在原表中添加字段，更新相关数据；如果数字很大，或者不是原表对象的关键属性，那么可以添加一个新表，通过键值连接起来；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类型的ORDERBY操作效率低下；禁止使用数值作为ENUM的枚举值。4.所有列的默认值都定义为NOTNULL。数据库中所有的NULL列都需要额外的空间来存储，所以会占用更多的空间；数据库在进行比较和计算时需要对NULL值做特殊处理。5.使用TIMESTAMP（4字节）或DATETIME（8字节）来存储时间。TIMESTAMP中存储的时间范围为：1970-01-0100:00:01~2038-01-19-03:14:07；TIMESTAMP占用4个字节，与INT类型相同，但可读性高于INT类型。如果超出了TIMESTAMP的取值范围，则以DATETIME类型存储；使用字符串类型存储时间的缺点：不能使用日期函数进行比较计算，字符串存储占用空间较大。6、金融类金额数据必须使用decimal类型精度浮点数：decimal非精度浮点数：float，doubleDecimal类型为精确浮点数，计算时精度不会丢失；占用空间由定义的宽度决定，每4个字节可存放9位，小数点也占用一个字节；另外，Decimal类型可以用来存储大于bigint的数据类型。四、MySQL索引设计规范1、每张表的索引数不超过5个。索引可以提高查询效率，但是也会降低插入和更新的效率，甚至在某些情况下会降低查询效率，所以不是越多越好，要控制它的数量。2.每个Innodb表都必须有一个主键。Innodb是一个索引组织表，其数据存储的逻辑顺序与索引的顺序相同；每个表可以有多个索引，但是表的存储顺序只能是一个，Innodb按照主键索引的顺序组织表，所以不要使用频繁更新的列，UUID，MD5，HASH，字符串列作为主键。这些列不能保证数据增长的顺序。主键推荐使用自增ID值。3.尽量避免使用外键约束。不建议使用外键约束（foreignkey），但必须在表间关联键上建立索引；外键虽然可以保证数据的引用完整性，但是外键也会影响父表和子表的写操作，从而降低性能，并且会让表更加耦合。建议在业务端实现。五、MySQL数据库SQL开发规范1、推荐使用预编译语句进行数据库操作。可以重用预编译语句。同一条SQL语句可以一次解析多次使用，减少SQL编译所需时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。2.避免数据类型的隐式转换。隐式转换如：SELECT1+"1";数字+字符隐式转换可能会导致索引失败和一些意想不到的结果。3、充分利用表中已有的索引1）避免使用双%号查询条件，如WHEREfirst_namelike'%James%'，如果没有前置前缀%，只有后置前缀%，则该列执行SQL语句时会使用双%号上的索引，不会使用列上的索引。2）一条SQL语句只能使用复合索引中的一列进行范围查询。比如体重、年龄、性别三列的联合索引。如果查询条件中有weight列的范围查询，age和sex列上的索引将不会被使用；因此，在定义联合索引时，如果某列需要用于范围查询，则将该列放在联合索引的右侧。3）用notexists代替notin，因为在sql语句中执行notin会导致索引失效。4、不要使用SELECT*，必须使用SELECT
