运维必知：请保留这份MySQL操作规范指南

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