完整的MySQL开发规范，进大厂必看！

1.数据库命令规范1.所有数据库对象名必须使用小写字母，并用下划线分隔2.所有数据库对象名禁止使用mysql保留关键字（如果表名包含关键字查询，需要括起来用单引号括起来）3.数据库对象的名称要能从名字中看出，最后不能超过32个字符4.临时数据库表必须以tmp_为前缀，以date为后缀，并且backuptable必须以bak_为前缀，以date(时间戳)为后缀5.所有存储相同数据的列名和列类型必须一致（一般为关联列，如果查询时关联列类型不一致，则数据类型隐式会自动进行转换，这会导致列上的索引无效，导致查询效率较低）二、数据库的基本设计规范1.所有表必须使用Innodb存储引擎。如果没有特殊要求（即Innodb不能满足的功能，如：列存储，存储空间数据等），所有表必须使用Innodb存储引擎（mysql5.5之前默认使用myisam，Innodb是5.6后默认使用）Innodb支持事务，支持行级锁，恢复更好，高并发下性能更好2.数据库和表统一使用字符集UTF8兼容性更好，统一字符集可以避免乱码字符集转换引起的。不同的字符集在比较前需要进行转换，会导致索引失效。3.所有的表和字段都需要注释。使用注释子句添加表和列。备注：从一开始就维护数据字典4.尽量控制单表数据的大小。建议数据量控制在500万以内。500万不是MySQL数据库的极限。如果太大，会导致表结构修改、备份和恢复。对于大问题，可以采用历史数据归档（应用于日志数据）、分库分表（应用于业务数据）等手段来控制数据量。5.谨慎使用MySQL分区表。分区表在物理上代表多个文件。从逻辑上讲，表现为一张经过精心选择分区键的表，跨分区查询效率可能会较低。建议使用物理分区来管理大数据。6、尽量做到冷热数据分离，减少表的宽度。MySQL限制每个表的最大值。存储4096列，每行数据大小不能超过65535字节，减少磁盘IO，保证热数据的内存缓存命中率（表越宽，表加载到内存缓冲池时占用的内存越大,而且还会消耗更多的IO)更有效的利用缓存,避免读取无用的冷数据,将经常使用的列放在一张表中(避免更多的关联操作)7.禁止在表中建立保留字段tableforreservation字段的命名很难看出名字的意思。保留字段无法确定存储的数据类型，因此无法为保留字选择合适的类型。段类型的修改会锁定表。8、禁止在数据库中存储图片。文件等大型二进制数据通常都有大文件，这会导致数据量在短时间内迅速增加。从数据库读取数据库时，通常会进行大量的随机IO操作。当文件较大时，IO操作比较耗时。通常存储在文件服务器中，数据库只存储文件地址信息。9、禁止在线进行数据库压力测试。10、禁止从开发环境和测试环境直接连接生产环境数据库三、数据库字段设计规范1、优先选择满足存储要求的最小数据类型。原因列的字段越大，索引需要的空间越大，所以一页可以存放的索引节点数越来越少，遍历时需要的IO次数越多，性能越差指数。方法1）将字符串转为数字类型存储，如：将IP地址转为整型数据mysql提供了两种处理ip地址的方法：inet_aton将ip转为无符号整数（4-8位）inet_ntoa将整型ip转在插入数据之前寻址，使用inet_aton将ip地址转换为整数，可以节省空间。显示数据时，使用inet_ntoa将整型ip地址转换成地址显示。2）对于非负数的数据（比如自增ID，整型IP），需要先用无符号整数来存储，因为：与有符号相比，无符号可以增加一倍的存储空间SIGNEDINT-2147483648~2147483647UNSIGNEDINT0~中的N~4294967295VARCHAR(N)代表的是字符数，不是字节数。使用UTF8存储255个汉字Varchar(255)=765字节。过长会消耗更多的内存2.避免使用TEXT和BLOB数据类型。最常见的TEXT类型可以存储64k的数据。建议将BLOB或TEXT列分隔到单独的扩展表中。mysql内存临时表不支持TEXT、BLOB等大数据类型，如果查询中包含此类数据，则不能使用临时内存表进行排序等操作，必须使用临时磁盘表进行操作。而且对于这种数据，Mysql仍然需要进行二次查询，这会让SQL的性能很差，但是不代表一定不能使用这种数据类型。如果一定要用，建议把BLOB或TEXT列单独放到一个单独的扩展表中。查询时不要用select*，只需要取出必要的列即可。当您不需要TEXT列中的数据时，不要查询该列。TEXT或BLOB类型只能使用前缀索引，因为MySQL对索引字段的长度有限制，所以TEXT类型只能使用前缀索引，TEXT列不能有默认值。3.避免使用ENUM类型1.修改ENUM值需要使用ALTER语句。2、ENUM类型的ORDERBY操作效率低，需要额外操作。3.禁止使用数值作为ENUM的枚举值。NULL的原因：1、索引NULL列需要额外的空间来保存，所以占用空间多；2.比较计算时，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.与金融相关的金额类数据必须使用小数类型1、非精度浮点数：float、double2、精确浮点数：decimalDecimal类型是精确浮点数，计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可存放9位数字，小数点占用1个字节。可用于存储大于bigint的整数数据。推荐：37个MySQL数据库技巧！四、索引设计规范1、限制每张表的索引数量。建议单表索引不超过5个。越多越好！索引可以提高效率也可以降低效率。索引可以提高查询效率，但也会降低插入和更新的效率，在某些情况下甚至会降低查询效率。因为mysql优化器在选择如何优化查询时，会根据统一的信息对各个可用的索引进行评估，生成最佳的执行计划。如果有多个索引可以同时用于查询，会增加mysql优化器生成执行计划的时间，也会降低查询性能。2、禁止为表中的每一列创建单独的索引。在5.6版本之前，一条SQL只能在一张表中使用一个索引。5.6之后，虽然有了合并索引的优化方法，但是离使用一个联合索引的查询方法还差得很远。推荐：MySQL索引B+树的原理，索引的几种原理。3.每个Innodb表都必须有一个主键。Innodb是一个索引组织表：数据存储的逻辑顺序和索引的顺序是一样的。每个表可以有多个索引，但表只能有一个存储顺序。Innodb根据主键索引的顺序组织表。不要使用频繁更新的列作为主键，多列主键不适用（相当于联合索引）。不要使用UUID、MD5、HASH、string列作为主键（不能保证数据的顺序增长）。主键推荐使用自增ID值。五、常用索引列的建议1、SELECT、UPDATE、DELETE语句的WHERE子句中出现的列2、ORDERBY、GROUPBY、DISTINCT中包含的字段不要创建匹配1和2中字段的列For一个索引，通常最好为1和2中的字段创建一个联合索引。3.多表连接的关联列6.如何选择索引列的顺序建立索引的目的是：希望使用索引搜索数据，减少随机IO。为了提高查询性能，索引过滤掉的数据越少，从磁盘读取的数据就越少。1.将区分度最高的放在联合索引的最左边（区分度=列中不同值的个数/列中的总行数）；2、尽量将字段长度最小的列放在联合索引的最左边（因为字段长度越小，一页可以存储的数据量越大，IO性能越好）；3.最常用的列放在联合索引的左侧（这样可以少建索引）。7.避免创建冗余索引和重复索引，因为这会增加查询优化器生成执行计划的时间。重复索引示例：primarykey(id),index(id),uniqueindex(id)冗余索引示例：index(a,b,c),index(a,b),index(a)八、优先覆盖对于频繁的查询，索引优先使用覆盖索引。覆盖索引：是覆盖索引，包括所有查询字段（where、select、orderyby、groupby字段）。索引覆盖索引的好处：避免Innodb表索引的二次查询。Innodb是按照聚集索引的顺序存储的。对于Innodb来说，二级索引存储的是叶子节点中行的主键信息。如果使用二级索引查询数据，找到对应的键值后，必须通过主键进行二级查询才能获得。我们真正需要的数据。在覆盖索引中，所有的数据都可以在二级索引的key值中获取，避免了主键的二次查询，减少IO操作，提高查询效率。可以把随机IO变成顺序IO来加快查询效率。由于覆盖索引是按照键值顺序存储的，对于IO密集型范围搜索，数据IO要比从磁盘随机读取每一行要少得多。因此，使用覆盖索引也可以在访问时将磁盘的随机读IO转化为索引查找的顺序IO。九、索引SET规范尽量避免使用外键约束1、不建议使用外键约束（foreignkey），但必须在表间关联键上建立索引；2、可以使用外键来保证数据引用的完整性，但建议在业务端实现；3、外键会影响父表和子表的写操作，从而降低性能。十。数据库SQL开发规范1、推荐使用预编译语句进行数据库操作。预编译语句可以重用这些计划，减少SQL编译所需的时间，也可以解决动态SQL带来的SQL注入问题。只传递参数，比传递SQL语句更高效同一条语句可以解析一次，多次使用，提高处理效率。推荐：MySQL数据库开发的36条军规！2.避免数据类型的隐式转换。隐式转换会导致索引失效。如：selectname,phonefromcustomerwhereid='111';3、充分利用表已有的索引，避免使用双%号查询条件。比如像'%123%'，（如果没有前导%，只有尾随%，可以使用列上的索引）一条SQL只能使用复合索引中的一列进行范围查询，如：有一个，b列和c列的联合索引，如果查询条件中有a列的范围查询，则不会使用b列和c列的索引。在定义联合索引时，如果a列需要使用范围搜索，则需要将a列放在联合索引的右侧。使用leftjoinornotexists来优化notin操作，因为notin通常也使用索引失效。4、设计数据库时，要考虑未来的扩展。5.程序连接不同数据库，使用不同账号，基础系统跨库查询1.为数据库迁移和分库分表留有余地2.降低业务耦合度3,避免权限过大带来的安全风险6.禁止SELECT*必须使用SELECT查询原因：1.消耗较多的CPU和IO以及网络带宽资源2.不能使用覆盖索引3.可以减少表结构变化的影响7.是禁止使用没有字段列表的INSERT语句，如：insertintovalues('a','b','c');插入t(c1,c2,c3)值（'a','b','c'）;8.避免使用子查询，可以将子查询优化为连接操作。通常子查询在in子句中，子查询是简单的SQL（不包括union、groupby、orderby、limit子句），可以将子查询转化为关联查询进行优化。子查询性能差的原因：1、子查询的结果集不能使用索引。通常，子查询的结果集会存储在一个临时表中。内存临时表和磁盘临时表都不会有索引，查询性能会下降。受到一定程度的影响；3.尤其对于返回大结果集的子查询，对查询性能的影响更大；3、由于子查询会产生大量的临时表，没有索引，会消耗过多的CPU和IO资源，导致大量的慢查询。9、避免使用JOIN关联过多的表对于Mysql来说，有一个关联缓存，缓存的大小可以通过join_buffer_size参数来设置。在Mysql中，同一条SQL连接多张表，会多分配一个关联缓存。一条SQL关联的表越多，占用的内存就越大。如果程序中大量使用多表关联操作，join_buffer_size设置不合理，很容易造成服务器内存溢出，影响服务器数据库性能的稳定性。同时对于关联操作，会产生临时表操作，影响查询效率。Mysql最多允许关联61张表，建议不要超过5张。10.减少与数据库的交互次数。数据库更适合处理批量操作。将多个相同的操作合并在一起，可以提高处理效率。11.对应同一列执行or判断时，用in代替orin。orin的值在运算中不要超过500。更高效地使用索引，或者在大多数情况下很少使用索引。12、禁止使用orderbyrand()进行随机排序。表中所有符合条件的数据都会加载到内存中，然后所有数据在内存中按照随机生成??的值进行排序，每行可能生成一行。随机值，如果满足条件的数据集非常大，会消耗大量的CPU、IO和内存资源。建议在程序中获取一个随机值，然后从数据库中获取数据。13、WHERE子句中禁止对列进行函数转换和计算。在对列进行函数转换或计算时，不能使用索引。不推荐：wheredate(create_time)='20190101'推荐：wherecreate_time>='20190101'andcreate_time<'20190102'14.当明显没有重复值时，使用UNIONALL而不是UNION1。UNION会将两个结果集的所有结果合并到临时表中，然后进行去重操作。2.UNIONALL不会去重结果集。15、将复杂的大SQL拆分成多个小SQL1。大SQL：逻辑上比较复杂，需要占用大量CPU计算的SQL2、MySQL：一条SQL只能用一个CPU计算3.SQL拆分后可以并行执行，提高处理效率十一、数据库操作行为规范1、100万行以上的批量写入（DELETE、INSERT）操作必须批量多次执行。大规模操作可能会导致严重的主从延迟。在主从环境下，大规模的操作可能会造成严重的主从延迟。一般大规模的写操作需要执行一定的时间，只有在主库上执行完成后，才会在其他从库上执行，所以会造成主库之间的延迟很长而从库就以行格式记录每一行的数据修改，我们一次修改的数据越多，产生的日志就越多，日志传输和恢复所需要的时间也就越长，这也是主从延迟的原因之一。避免大规模的事务操作，大量修改数据，必须在一个事务中执行，这会导致表中大量数据被锁定，导致大量阻塞，这将有一个非常对MySQL的性能影响很大。特别是长期阻塞会占用数据库的所有可用连接，这会导致生产环境中的其他应用程序无法连接到数据库。因此需要注意大规模写操作的批处理。2.对于大表，使用pt-online-schema-change修改表结构1.避免大表修改造成主从延迟2.避免修改表字段时锁表。修改大表的数据结构时要小心，会造成严重的表锁操作，尤其是在生产环境中，更不能容忍。pt-online-schema-change会先新建一张与原表结构相同的表，并在新表上修改表结构，然后将原表中的数据复制到新表中，并在原表中添加一些触发器。将原表中新增的数据复制到新表中。复制行中所有数据后，将新表命名为原表，删除原表。将原来的DDL操作分解成多个小批量。3、禁止给程序使用的账号授予super权限。当达到最大连接数时，仍然有super权限的用户运行连接。super权限只能保留给DBA处理问题的账号。4、对于程序连接的数据库账号，遵循最小权限原则。程序使用的数据库账号只能在一个DB下使用，原则上不允许程序使用的账号有drop权限。