mysql使用规范

1.命名规范(1)库名、表名、字段名必须使用小写字母,并采用下划线分割。(2)库名、表名、字段名禁止超过32个字符。(3)库名、表名、字段名必须见名知意。命名与业务、产品线等相关联。(4)库名、表名、字段名禁止使用MySQL保留字。（保留字列表见官方网站）(5)临时库、表名必须以tmp为前缀,并以日期为后缀。例如tmp_test01_20130704。(6)备份库、表必须以bak为前缀,并以日期为后缀。例如bak_test01_20130704。2.基础规范(1)使用INNODB存储引擎。(2)表字符集使用使用UTF8MB4字符集。(3)所有表都需要添加注释;除主键外的其他字段都需要增加注释。推荐采用英文标点,避免出现乱码。(4)禁止在数据库中存储图片、文件等大数据。(5)每张表数据量建议控制在5000W以内。(6)禁止在线上做数据库压力测试。(7)禁止从测试、开发环境直连数据库。3.库表设计(1)禁止使用分区表。(2)将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。(3)推荐使用HASH进行散表,表名后缀使用十进制数,数字必须从0开始。(4)按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2013071601。年份必须用4位数字表示。例如按日散表user_20110209、按月散表user_201102。(5)采用合适的分库分表策略。例如千库十表、十库百表等。4.字段设计(1)建议使用UNSIGNED存储非负数值。(2)建议使用INTUNSIGNED存储IPV4。(3)用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。例如与货币、金融相关的数据。(4)INT类型固定占用4字节存储,例如INT(4)仅代表显示字符宽度为4位,不代表存储长度。(5)区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型。例如取值范围为0-80时,使用TINYINTUNSIGNED。(6)强烈建议使用TINYINT来代替ENUM类型。(7)尽可能不使用TEXT、BLOB类型。(8)禁止在数据库中存储明文密码。(9)使用VARBINARY存储大小写敏感的变长字符串或二进制内容。(10)使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。(11)区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。(12)所有字段均定义为NOTNULL。5.索引规范(1)单张表中索引数量不超过5个。(2)单个索引中的字段数不超过5个。(3)索引名必须全部使用小写。(4)非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。(5)唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。(6)组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。(7)表必须有主键,推荐使用UNSIGNED自增列作为主键。(8)唯一键由3个以下字段组成,并且字段都是整形时,可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。(9)禁止冗余索引。(10)禁止重复索引。(11)禁止使用外键。(12)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。(13)不在低基数列上建立索引,例如“性别”。(14)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。(15)对字符串使用前缀索引,前缀索引长度不超过8个字符。(16)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。(17)合理创建联合索引,(a,b,c)相当于(a)、(a,b)、(a,b,c)。(18)合理使用覆盖索引减少IO,避免排序。6.SQL设计(1)使用preparedstatement,可以提升性能并避免SQL注入。(2)使用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。(3)禁止隐式转换。数值类型禁止加引号;字符串类型必须加引号。(4)避免使用JOIN和子查询。必要时推荐用JOIN代替子查询。(5)禁止在MySQL中进行数学运算和函数运算。(6)减少与数据库交互次数,尽量采用批量SQL语句。(7)拆分复杂SQL为多个小SQL,避免大事务。(8)获取大量数据时,建议分批次获取数据,每次获取数据少于2000条,结果集应小于1M。(9)使用UNIONALL代替UNION。(10)统计行数使用COUNT(*)。(11)SELECT只获取必要的字段,禁止使用SELECT*。(12)SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。13)INSERT语句必须指定字段列表,禁止使用INSERTINTOTABLE()。(14)禁止单条SQL语句同时更新多个表。(15)禁止使用存储过程、触发器、视图、自定义函数等。(16)建议使用合理的分页方式以提高分页效率。(17)禁止在从库上执行后台管理和统计类功能的QUERY,必要时申请统计类从库。(18)程序应有捕获SQL异常的处理机制,必要时通过rollback显式回滚。(19)重要SQL必须被索引:update、delete的where条件列、orderby、groupby、distinct字段、多表join字段。(20)禁止使用%前导查询,例如:like“%abc”,无法利用到索引。(21)禁止使用负向查询,例如notin、!=、notlike。(22)使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:UsingFileSort、UsingTemporary。(23)禁止使用orderbyrand()。8.FAQ1.库名、表名、字段名必须使用小写字母,并采用下划线分割。a)MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。b)如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。c)字段名显式区分大小写,但实际使用不区分,即不可以建立两个名字一样但大小写不一样的字段。d)为了统一规范,库名、表名、字段名使用小写字母。2.库名、表名、字段名禁止超过32个字符。库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符。3.使用INNODB存储引擎。INNODB引擎是MySQL5.5版本以后的默认引擘,支持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、大内存、SSD等硬件支持更好,支持数据热备份等,因此INNODB相比MyISAM有明显优势。4.库名、表名、字段名禁止使用MySQL保留字。当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。5.禁止使用分区表。分区表对分区键有严格要求;分区表在表变大后,执行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。6.建议使用UNSIGNED存储非负数值。同样的字节数,非负存储的数值范围更大。如TINYINT有符号为-128-127,无符号为0-255。7.建议使用INTUNSIGNED存储IPV4。UNSINGEDINT存储IP地址占用4字节,CHAR(15)则占用15字节。另外,计算机处理整数类型比字符串类型快。使用INTUNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。例如:SELECTINET_ATON('209.207.224.40');3520061480SELECTINET_NTOA(3520061480);209.207.224.408.强烈建议使用TINYINT来代替ENUM类型。ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较大;ENUM列值如果含有数字类型,可能会引起默认值混淆。9.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。VARBINARY默认区分大小写,没有字符集概念,速度快。10.INT类型固定占用4字节存储,例如INT(4)仅代表显示字符宽度为4位,不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系,比如INT(3)默认显示3位,空格补齐,超出时正常显示,python、java客户端等不具备这个功能。11.区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性。注意:在5.5和之前的版本中,如果一个表中有多个timestamp列,那么最多只能有一列能具有自动更新功能。如何使用TIMESTAMP的自动赋值属性?a)自动初始化,并自动更新:column1TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPb)只是自动初始化:column1TIMESTAMPDEFAULTCURRENT_TIMESTAMPc)自动更新,初始化的值为0:column1TIMESTAMPDEFAULT0ONUPDATECURRENT_TIMESTAMPd)初始化的值为0:column1TIMESTAMPDEFAULT012.所有字段均定义为NOTNULL。a)对表的每一行,每个为NULL的列都需要额外的空间来标识。b)B树索引时不会存储NULL值,所以如果索引字段可以为NULL,索引效率会下降。c)建议用0、特殊值或空串代替NULL值。13.将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存命中率。14.禁止在数据库中存储明文密码。采用加密字符串存储密码，并保证密码不可解密，同时采用随机字符串加盐保证密码安全。防止数据库数据被公司内部人员或黑客获取后，采用字典攻击等方式暴力破解用户密码。15.表必须有主键,推荐使用UNSIGNED自增列作为主键。表没有主键,INNODB会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。16.禁止冗余索引。索引是双刃剑,会增加维护负担,增大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。17.禁止重复索引。primarykeya;uniqindexa;重复索引增加维护负担、占用磁盘空间,同时没有任何益处。18.不在低基数列上建立索引,例如“性别”。大部分场景下,低基数列上建立索引的精确查找,相对于不建立索引的全表扫描没有任何优势,而且增大了IO负担。19.合理使用覆盖索引减少IO,避免排序。覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省IO。INNODB存储引擎中,secondaryindex(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。如果用户需要查询secondaryindex中所不包含的数据列,则需要先通过secondaryindex查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据,因此效率