MySQLDB规范目录简介..................................................................................................................................................3目的..................................................................................................................................................3适用范围..........................................................................................................................................3数据库设计......................................................................................................................................3引擎及版本选择......................................................................................................................3基础规范..................................................................................................................................3命名规范..................................................................................................................................5库表设计规范..........................................................................................................................5字段设计..................................................................................................................................6常用数据类型:..............................................................................................................6数据类型使用建议:......................................................................................................6索引规范..................................................................................................................................8索引准则..................................................................................................................................8索引禁忌..................................................................................................................................8不使用外键..............................................................................................................................9SQL设计.................................................................................................................................10简介介绍在使用mysql中各种注意事项和优化细节目的供开发人员参考,合理利用MySQL特性,开发出更高效的代码减少后端数据库压力,让整个系统高效稳定运行适用范围业务数据库使用的是MySQL的数据库。数据库设计实现目标:业务功能实现、数据的扩展性、普遍性适用性业务中80%+的性能优化是来自架构设计的优化引擎及版本选择根据业务特性选择合适的存储引擎,默认选择InnoDB存储引擎,原因如下(MyISAM与InnoDB比较):特点MyISAMInnoDB存储限制256T64TB事务安全支持锁机制表锁行锁B树索引支持支持哈希索引支持全文索引支持集聚索引支持数据缓存支持索引缓存支持支持数据可压缩支持空间使用低高内存使用低高批量插入的速度高支持外键低MySQL版本选择建议选择官方或者MySQL5.6+之后的版本基础规范所有库表默认使用INNODB存储引擎,MyISAM适用场景非常少库表字符集使用UTF8,原因如下:使用utf8字符集,如果是汉字,占3个字节,但ASCII码字符还是1个字节;统一不会有转换产生乱码风险;其他地区的用户(美国、印度、台湾)无需安装简体中文支持,就能正常看您的文字,并且不会出现乱码。UTF-8最大的一个特点,就是它是一种变长的编码方式。它可以使用1~4个字节表示一个符号,根据不同的符号而变化字节长度。UTF-8的编码规则很简单,只有二条:1)对于单字节的符号,字节的第一位设为0,后面7位为这个符号的unicode码。因此对于英语字母,UTF-8编码和ASCII码是相同的。2)对于n字节的符号(n1),第一个字节的前n位都设为1,第n+1位设为0,后面字节的前两位一律设为10。剩下的没有提及的二进制位,全部为这个符号的unicode码。所有表和字段都需要添加注释,以方便其它开发及dba了解单表数据量纯int型建议控制在1000w以内,含char型的建议500w以内,行平均长度控制在16KB以内,单表20GB以内不在数据库中存储图片、文件等大数据.原因如下:1、对数据库的读写速度永远赶不上文件系统的处理速度2、数据库备份会变的很臃肿,备份很耗时间3、对文件的访问需要通过你的应用和数据库临时短命数据尽量不要存到数据库中,建议存放于前端的memcache、redis等nosql中,减少后端数据库压力禁止在线上做压力测试禁止从测试、开发环境直接连接线上数据库用数据库来持久化存储以及保证事务一致性,不是运算器,在应用层实现计算读写分离,主库只写和少量实时读取请求,使用从库来查询。采用队列方式合并多次写请求,持续写入避免瞬间压力超长text/blob进行垂直拆分,并先行压缩冷热数据进行水平拆分(如6个月前后数据),LRU原则快速更新频繁和大数据表禁止直接运行count(*)统计压力分散,在线表和归档表(日志表)分开存储;不重要的非实时查询日志不要存数据库,以文件方式在应用端统计分析。禁止明文存储机密数据,需至少两次加密(部分数据可逆运算)命名规范库名、表名、字段名必须使用小写字母,不同含义采用下划线分割库名、表名、字段名禁止超过32个字符,名称必须是易于理解,见名知意,能表达表的功能的英文单词或缩写英文单词,多个用_来连接库名、表名、字段名禁止使用MySQL保留字如:create、alter、add、desc、delete,union,sleep等mysqlcreatedatabasecreate;ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'create'atline1#使用了MySQL保留字mysql临时库、临时表须以tmp为前缀或后缀,以日期结尾。如:tmp_sub_order备份库须以bak_日期结尾(如:test_order_bak_20140430)对象命名汇总表对象名前后缀范例描述表(table)tbl_系统功能简写_tbl_order_main临时表用tmp_前缀视图(view)vw_系统功能简写_vw_commodity_age_prop一般索引(normalindex)idx_idx_order_sub_create_time表名或字段名过长,采用简写唯一索引(uniqueindex)udx_udx_login_name主键(primarykey)pk_pk_login_idMysql不支持可以注释实现库表设计规范禁止使用分区表用HASH进行散表,表名后缀使用十进制数,下标从0开始按日期时间分表需符合YYYY[MM][DD][HH]对于无法按日期时间分表的建议按id最后一位或最后两位分库分表存储采用合适的分库分表策略。如:十库百表、百库百表等字段设计拆分大字段和访问频率低的字段,按访问频率把字段拆分开,如:大字段和访问频率低的字段放一起。订单类的可按按日期存放,数据分离冷热数据。常用数据类型:类型长度(字节)范围tinyint1有符号-128~127无符号0~255smallint2有符号-32768~32767无符号0~65535mediumint3有符号-8388608~8388607无符号0~1677215int\integer4有符号-2147483648~2147483647无符号0~4294967295bigint8有符号-9223372036854775808~9223372036854775807无符号0~18446744073709551615float4最小值+-1.175494351Estate-38最大值+-3.42823466E+38double8最小值+-2.225073855072014E-308最大值+-1.7976931348623157E+308decimal(m,d)m+2字节m+2最大取值与double相同,给定decimal的有效取值由m和d决定char(m)mM为0~255之间的整数,在存储数据时会删除尾部空格varchar(m)mM为0~65535之间的整数,值的长度+1个字节。tinyblob值+1允许长度0~255字节blob值+1允许长度0~65535字节(可存二进制文件)拒绝使用mediumblob值+3允许长度0~167772150字节longblog值+4允许长度0~4294967295字节tinytext值+2允许长度0~255字节Text值+2允许长度0~65535字节mendiumtext值+4允许长度0~167772150字节varbinary(m)值+1允许长度0~M个字节的变长字节字符串binary(m)m允许长度0~M个字节的定长字节字