最近涉及到很多数据库相关的操作，公司现有的规范不是太全面。我根据网上各种大神的相关规范，整理了一些规范供自己使用。用法，望指正。数据库环境dev：开发环境可读写，可修改表结构。开发人员可以随意修改表结构和其中的数据，但需要保证其他开发同事不受影响。test：测试环境开发为可读可写，开发者可以通过工具修改表结构。online：不允许在线环境下的开发者直接在在线环境下进行数据库操作。如果需要操作，必须请DBA进行相应的操作和记录。禁止进行压力测试。关键问题是每个环境下mysql服务器对应的用户权限必须划分清楚，可识别，能够具体区分业务场景。命名约定基本命名规则使用下划线分隔的有意义的英文单词。（不要用拼音）只能用英文字母、数字、下划线、以英文字母开头。库、表、字段全部小写，不使用驼峰命名。避免使用ORACLE和MySQL的保留字，如desc，以及index等关键字。名称中不得超过32个字符。请参考名字的含义。建议使用名词而不是动词。对于数据库，所有数据表必须使用前缀Temporarylibrary，表名必须以tmp为前缀，备份库必须以date为后缀，表必须以bak为前缀，以date为后缀。为什么库、表、字段都是小写的？在MySQL中，数据库和表对应于这些目录中的目录和文件。因此，操作系统的敏感性决定了数据库和表名的大小写敏感性。Windows不区分大小写。Linux下数据库名和表名的大小写规则是严格区分大小写的；表别名严格区分大小写；列名和列别名在所有情况下都不区分大小写；变量名也严格区分大小写；如果已设置驼峰案例，如何解决？需要在MySQL配置文件my.ini中加入lower_case_table_names=1。表名同一模块中的表尽量使用相同的前缀，表名尽量表达意思。所有日志表的命名字段都以log_开头的表示其实际含义的英文单词或缩写。布尔字段以is_为前缀，后跟动词的过去分词。表之间具有相同含义的字段应具有相同的名称。表间含义相同的字段以表名_字段名命名，不带模块前缀。外键字段使用表名_字段名来表示其关联关系。表的主键一般约定为id，自增类型，其他表的外键用xxx_id表示。索引命名非唯一索引必须按照“idx_fieldname_fieldname[_fieldname]”命名唯一索引必须按照“uniq_fieldname_fieldname[_fieldname]”命名约束命名主键约束：pk_表名。唯一约束：uk_表名_字段名。（应用需要同时具备唯一性校验逻辑）表设计规范表引擎根据实际应用场景而定；日志和报表表推荐使用myisam，交易、审计、金额相关的表推荐使用innodb引擎。如果没有说明，innodb引擎在建表时会默认使用utf8mb4字符集，数据库排序规则会使用utf8mb4_general_ci。如果不选择utf8，则排序规则不使用utf8_general_ci。utf8编码的MySQL无法保存占位符为4字节的Emoji表情。为了让后端工程完全支持客户端输入的Emoji表情，将编码升级为utf8mb4是最好的解决方案。针对上述配置后JDBC连接字符串的characterEncoding设置为utf8或者不能正常插入emoji数据的情况，需要在代码中指定连接字符集为utf8mb4。所有的表和字段都应该使用注释列属性来描述这个表，字段所代表的真正含义，比如枚举值，建议定义字段中使用的所有内容。如果没有说明，表中第一个id字段必须是主键且必须是自增的，禁止在非事务数据传输中作为context作为条件。禁止使用varchar类型作为主键语句设计。如果没有描述，表中必须包含create_time和modify_time字段，即表中必须包含记录创建时间和修改时间字段。如果没有描述，表中必须包含is_del，用于标记数据是否被删除。原则上不允许对数据库数据进行物理删除。使用尽可能少的存储空间来存储一个字段的数据。如果可以使用int，则不需要char或varchar。如果可以使用tinyint，则不需要int。使用UNSIGNED存储非负值。不推荐使用ENUM和SET类型。使用TINYINT而不是使用短数据类型。例如取值范围为0-80时，使用TINYINTUNSIGNED存储精确浮点数。您必须使用DECIMAL而不是FLOAT和DOUBLE时间字段，并在特殊情况下使用int。要记录unix_timestamp存储年份，请使用YEAR类型。使用DATE类型存储日期。推荐使用TIMESTAMP类型存储时间（精确到秒），因为TIMESTAMP占用4个字节，DATETIME占用8个字节。建议使用INTUNSIGNED存储IPV4。尽量不要使用TEXT和BLOB类型。数据库中禁止使用VARBINARY和BLOB存储图片和文件。建议使用其他存储方式（TFS/SFS），MySQL只保存指针信息。单条记录的大小禁止超过8k（列长（中文）_3（UTF8）+列长（英文）_1）datetime和timestamp有什么区别？相同点：TIMESTAMP列的显示格式与DATETIME列相同。显示宽度固定为19个字符，格式为YYYY-MM-DDHH:MM:SS。区别：TIMESTAMP以4字节存储，时间范围：1970-01-0108:00:01~2038-01-1911:14:07值以UTC格式保存，涉及时区转换，和当前时间zone在存储时存储做转换，在检索时转换回当前时区。Datetime以8字节存储，时间范围为：1000-01-0100:00:00~9999-12-3123:59:59实际格式存储不分时区。如何使用TIMESTAMP的自动赋值属性？使用当前时间作为ts的默认时间：tsTIMESTAMPDEFAULTCURRENT_TIMESTAMP。更新行时，更新ts的值：tsTIMESTAMPDEFAULT0ONUPDATECURRENT_TIMESTAMP。可以组合1和2：tsTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP。如何使用INTUNSIGNED存储ip？使用INTUNSIGNED代替char(15)来存储ipv4地址，并通过MySQL函数inet_ntoa和inet_aton进行转换。IPv6地址目前没有转换功能，需要用DECIMAL或者两个bigINT来存储。如果没有注释，则所有字段必须设置为NOTNULL，并设置默认值；禁止在数据库中存储明文密码。如果没有评论，所有的布尔值字段，如is_hot和is_deleted，必须设置为默认值，设置为0；如果没有说明，排序字段order_id在程序中默认是降序排列；长度未添加到整形定义中，例如使用INT而不是INT[4]INT[M]。M值是什么意思？注意数字类型括号后的数字只表示宽度，与存储范围无关。很多人认为INT(4)和INT(10)的取值范围分别是(-9999到9999)和(-9999999999到9999999999)。这种理解是错误的。实际上，当整数中的M值与ZEROFILL属性结合使用时，列值可以等宽。无论INT[M]中M的值是多少，其取值范围仍然是（有符号时为-2147483648至2147483647），（无符号时为0至4294967295）。显示宽度不限制列内可以容纳的值的范围，也不限制超过列指定宽度的值的显示。当与可选的扩展属性ZEROFILL结合使用时，默认填充的空格将替换为零。示例：对于声明为INT(5)ZEROFILL的列，值4检索为00004。请注意，如果将值存储在超过显示宽度的整数列中，MySQL将无法为复杂连接生成临时表，因为在这些情况MySQL认为数据将适合原始列宽度。如果为数字列指定ZEROFILL，MySQL会自动为该列添加UNSIGNED属性。使用VARBINARY存储区分大小写的可变长度字符串。什么时候用CHAR，什么时候用VARCHAR？CHAR和VARCHAR类型相似，但它们的存储和检索方式不同。它们在最大长度以及是否保留尾随空格方面也有所不同。CHAR和VARCHAR类型的声明长度表示要存储的最大字符数。例如，CHAR(30)可以使用30个字符。CHAR列的长度固定为创建表时声明的长度。长度可以是0到255之间的任何值。存储CHAR值时，它们会用空格向右填充到指定的长度。检索CHAR值时删除尾随空格。在存储或检索期间不进行大小写转换。VARCHAR列中的值是可变长度的字符串。长度可以指定为0到65,535之间的值。（VARCHAR的最大有效长度由最大行大小和使用的字符集决定。总的最大长度为65,532字节）。与CHAR相比，VARCHAR值在保存时只保存需要的字符数，再加上一个字节来记录长度（如果列声明的长度超过255，则使用两个字节）。VARCHAR值在没有填充的情况下保存。保存和检索值时保留尾随空格，符合标准SQL。char适合存储用户密码的MD5散列值，其长度始终相同。对于频繁变化的值，char也比varchar好，因为固定长度的行不容易产生碎片，而且对于很短的列，char也比varchar效率更高。char(1)字符串对于单字节字符集只会占用一个字节，但是varchar(1)将占用2个字节，因为1个字节用于存储长度信息。索引设计规范MySQL的查询速度取决于良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度（包括UPDATE和DELETE的速度，MySQL会将包含该行的页加载到内存中，然后执行UPDATE或DELETE操作），不合理的索引会减慢速度。MySQL索引搜索类似于新华字典中的拼音和部首搜索。当拼音和部首索引不存在时，只能逐页翻页查找。当MySQL查询不能使用索引时，MySQL会进行全表扫描，会消耗大量IO。索引的目的：去重、加速定位、避免排序、覆盖索引。什么是覆盖索引在InnoDB存储引擎中，二级索引（非主键索引）不直接存储行地址，而是存储主键值。如果用户需要查询未包含在二级索引中的数据列，需要先通过二级索引找到主键值，再通过主键查询其他数据列，所以需要两次查询。覆盖索引的概念是查询可以在一个索引中完成，覆盖索引会更高效。主键查询是一个自然覆盖索引。合理创建索引和合理使用查询语句可以在使用覆盖索引时提高性能。比如SELECTemail,uidFROMuser_emailWHEREuid=xx，如果uid不是主键，可以在适当的时候加上索引为index(uid,email)来提高性能。索引基本规范控制索引个数，单表索引数不超过5个，单个索引字段数不超过5个。综合评价数据密度和分布考虑查询和更新比为什么可以一张表中的索引太多了吗？InnoDB的二级索引使用b+tree来存储，所以在UPDATE、DELETE、INSERT时需要调整b+tree。索引太多会减慢更新速度。对字符串使用前缀索引。前缀索引的长度不超过8个字符。建议优先使用前缀索引。如有必要，您可以添加伪列并创建索引。不索引blob/text等字段，不索引大字段。这样做会使索引占用过多的存储空间。什么是前缀索引？前缀索引说白了就是在文本的前几个字符上建立索引（具体是建索引时指定了多少个字符），这样建的索引越小，查询越快。前缀索引可以有效减小索引文件的大??小，提高索引速度。但是前缀索引也有其缺点：MySQL不能在ORDERBY或GROUPBY中使用前缀索引，也不能作为覆盖索引（CoveringIndex）。创建前缀索引的语法：ALTERTABLEtable_nameADDKEY(column_name(prefix_length));主键准则表必须有主键不使用频繁更新的列尽量不选择字符串列不使用UUIDMD5HASH默认使用非空唯一键推荐选择自增或发布者的重要SQL必须是索引，核心SQL优先对UPDATE和DELETE语句的WHERE条件列进行覆盖索引。多表JOIN字段的ORDERBY、GROUPBY、DISTINCT字段区分度最高的字段放在最前面。选择过滤性较好的字段放在最前面，比如单号、userid等，type、status等过滤属性一般不建议放在最前面。根据左前缀原则，在创建联合索引(a,b,c)时，查询条件只能使用包含(a)或(a,b)或(a,b,c)的索引。当使用(a,c)作为条件时，只能使用a列的索引，所以这时候需要保证a的返回列不能太多，否则语句设计不合理,(b,c)不能合理利用索引创建联合索引（避免冗余）,(a,b,c)等价于(a),(a,b),(a,b,c)索引禁忌不要在低基数的列上建立索引，比如“性别”不要对索引列进行数学运算和函数运算不要索引常用的小表尽量不要使用外键为了保护参照完整性，可以在业务方认为对父表和子表的操作会相互影响，降低可用性。INNODB本身对在线DDL有限制。MYSQL对索引有限制。MYISAM存储引擎索引的总长度不能超过1000字节的BLOB并且只能为TEXT类型的列创建前缀索引。目前MYSQL不支持函数索引。当不等于（!=或<>）时，MYSQL不能使用索引。过滤字段使用函数操作后（如abs（列）），MYSQL无法使用索引。当join语句中的join条件字段类型不一致时，MYSQL无法使用索引。使用LIKE操作时，如果条件以通配符开头（如'%abc...'），MYSQL无法使用索引。在使用非等价查询时，MYSQL不能使用Hash索引。语句设计规范使用预编译语句只传递参数，比传递SQL语句效率更高。一次解析，多次使用，降低SQL注入概率，避免隐式转换导致索引失效。充分利用前缀。索引必须是最左边的前缀。不可能同时使用两者。不使用%前导查询的范围条件，比如like"%ab"不使用否定查询，比如notin/like不能使用索引，导致全表扫描。全表扫描导致缓冲池利用率降低。避免使用存储过程和触发器Server、UDF、事件等让数据库做它最擅长的事情，减少业务耦合，并为sacleout和sharding留出空间，避免使用大表的bug和JOIN。MySQL最擅长单表主键/二级索引查询JOIN消耗大量内存，生成临时表，避免数据库中的数学运算。MySQL不擅长数学运算和逻辑判断。它不能使用索引来减少与数据库的交互次数。INSERT...ONDUPLICATEKEYUPDATEREPLACEINTO,INSERTIGNORE,INSERTINTOVALUES(),(),()UPDATE...WHEREIDIN(10,20,50,...)合理使用分页限制显示页数分页只能点击上一页，下一页使用延迟关联如何正确使用分页？如果有类似如下的分页语句：SELECT*FROMtableORDERBYidLIMIT10000,10因为MySQL中LIMITOFFSET的处理方式是把OFFSET+LIMIT的数据全部取出来，然后取出OFFSET，返回限制在底部。因此，当OFFSET值很大时，MySQL的查询性能会很低。可以用id>n的方法解决：id>n的方法有局限性。对于id不连续的问题，可以通过在翻页的时候同时传入最后一个id来解决。http://example.com/page.php?last=100select*fromtablewhereid<100orderbyiddesclimit10//上一页http://example.com/page.php?first=110select*fromtablewhereid>110orderbyiddesclimit10这个方法缺点比较大是的，如果在浏览过程中有插入/删除操作，翻页不会更新，仍然可能根据新的count(*)计算总页数，最终可能有些记录无法访问。为了修复这个问题，可以继续介绍当前页码以及最后翻页后是否有插入/删除、缓存等影响记录总数的操作select*fromtablewhereid>=(selectidfromtableorderbyidlimit#offset#,1)拒绝大SQL，拆分成小SQL充分利用QUERYCACHE充分利用多核CPU用in代替or，in的值不超过1000。禁止使用orderbyrand()使用EXPLAIN诊断避免生成临时表EXPLAIN语句（在MySQL客户端中执行）可以获取有关MySQL如何执行SELECT语句的信息。通过对SELECT语句执行EXPLAIN，可以知道MySQL在执行SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。MySQL中尽量避免全表扫描、使用临时表、排序等。有关详细信息，请参阅官方文档。使用unionall而不是unionunionall与union有什么区别？union和unionall关键字都是将两个结果集合并为一个，但两者在用法和效率上有所不同。union会在联表后过滤掉重复记录，所以联表后会对生成的结果集进行排序，删除重复记录并返回结果。例如：select*fromtest_union1unionselect*fromtest_union2这条SQL在运行时先取两张表的结果，然后使用排序空间排序删除重复记录，最后返回结果集。如果表的数据量很大，可以使用磁盘进行排序。而unionall只是将两个结果结合起来并返回。这样，如果两个返回的结果集中有重复的数据，则返回的结果集中就会包含重复的数据。从效率上来说，unionall比union快很多，所以如果能确认两个合并后的结果集不包含重复数据，那就用unionall，如下：select*fromtest_union1unionallselect*fromtest_union2程序应该捕获SQL异常了处理机制禁止单个SQL语句同时更新多个表。不要使用选择*。SELECT语句只获取必填字段，消耗CPU和IO，消耗网络带宽。覆盖索引不能用来减少表结构变化的影响。因为大，select/join可以生成临时表UPDATE,DELETE语句不要使用LIMITINSERT语句必须显式指定字段名，不要使用INSERTINTOtable()INSERT语句使用批量提交(INSERTINTOtableVALUES(),(),()...),values的个数不超过500个。统计表中的记录数时，使用COUNT(*)代替COUNT(primary_key)和COUNT(1)。禁止使用跨库查询，禁止使用子查询进行数据更新。对于varchar类型字段的程序处理，建议将子查询转换为关联查询。请验证用户输入，不要超过其预设长度；分表规格单表一分二如果年数据量超过500w或者数据容量超过10G，可以考虑分表。需要提前考虑历史数据迁移或应用自行删除历史数据。可分为等量平衡表或按业务规则。数据表要??分表一定要和DBA讨论分表策略。使用HASH分散表。表名后缀使用十进制数。下标从0开始，按照日期和时间划分表格。它必须符合YYYY[MM][dd][HH]格式。适当的分库分表策略。例如，禁止使用千库十表、十库百表的分区表。分区表对分区键有严格的要求。分区表变大后，DDL、SHARDING、单表恢复变得更加困难。拆分大字段和访问频率低的字段，冷热数据分离行为规范批量导入导出数据必须提前通知DBA协助观察禁止从数据库在线进行后台管理和统计查询禁止申请超级账号现有产品的权限如果出现非数据库原因的故障，及时通知DBA协助调查促销活动或上线新功能。必须提前通知DBA对数据库数据丢失进行流量评估，并及时联系DBA进行恢复。对单个表的多个更改操作必须合并为一个操作，而不是在MySQL数据库中。对于存储业务逻辑的重大项目的数据库方案的选择和设计，必须提前通知DBA参与特别重要的数据库表，提前与DBA沟通确定维护备份的优先级，不更新业务高峰期批量，查询数据库，提交其他规范提交在线建表修改需求，所有相关SQL语句都要详细说明。其他标准化的日志数据不建议存储在MySQL上。HBase或OceanBase是首选。如果需要存储，请找DBA评估压缩表存储的使用。
