一、 表设计规范
1、库名、表名、字段名必须使用小写字母,“_”分割。
2、库名、表名、字段名必须不超过12个字符。(字符长度越长查询速度越慢)
3、库名、表名、字段名见名知意,建议使用名词而不是动词
4、建议使用InnoDB存储引擎
(1)、存储引擎:存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)
(2)、innodb存储引擎,特点支持外键、行锁、非锁定读(默认情况下读取不会产生锁)、mysql-4.1开始支持每个innodb引擎 的表单独放到一个表空间里。innodb通过使用MVCC来获取高并发性,并且实现sql标准的4种隔离级别,同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术
5、存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
(1)、decimal:数字型,128bit,不存在精度损失,常用于银行帐目计算。(28个有效位)
(2)、double:双精度实型,含字节数为8,64bit数值范围-1.7E308~1.7E308(15个有效位)
(3)、float:浮点型,含字节数为4,32bit,数值范围为-3.4E38~3.4E38(7个有效位)
(4)、float,double等非标准类型,在DB中保存的是近似值,而Decimal则以字符串的形式保存数值。
(5)、float,double类型是可以存浮点数(即小数类型),但是float有个坏处,当你给定的数据是整数的时候,那么它就以整数给你处理。
(6)、数值存储范围越小的精度越高,存储数值范围越大,精度就越不准确,如果存储正常金额的情况下,使用money,好处在于可以存储不指定的小数点位数的数 值,比较真实。如果对于既要求精度,又固定小数点位数的数值存储,采用decimal(numeric),优点在于可以自定义小数点位数,精度高。如特殊情况,如数值范围巨大只能用float(real)类型了,此类型一般不提倡使用
6、建议使用UNSIGNED存储非负数值
(1)、整型的每一种都有无符号(unsigned)和有符号(signed)两种类型(float和double总是带符号的)
(2)、在默认情况下声明的整型变量都是有符号的类型(char有点特别),如果需声明无符号类型的话就需要在类型前加上unsigned
(3)、无符号版本和有符号版本的区别就是无符号类型能保存2倍于有符号类型的正整数数据,比如16位系统中一个int能存储的数据的范围为-32768~32767,而 unsigned能存储的数据范围则是0~65535
(4)、由于在计算机中,整数是以补码形式存放的。根据最高位的不同,如果是1,有符号数的话就是负数;如果是无符号数,则都解释为正数
7、建议使用INT UNSIGNED存储IPV4
(1)、如果用CHAR(15)来存储,则需要15个字节(VARCHAR则需要16个字节)。将IP用无符号整数存储,只需要4个字节,能节省空间
(2)、php存入时:$ip = ip2long($ip);
(3)、mysql取出时:SELECT INET_ATON(ip) FROM table ...
(4)、php取出时,多一步:$ip = long2ip($ip);
8、整形定义中不添加长度,比如使用INT,而不是INT(4)
(1)、int是整型,(11)是指显示字符的长度,但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001 ~~~00000000010,当字符 的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
(2)、这个可选的宽度规格说明是用于在数值显示时,对某些值的宽度短于该列宽度的值进行左填补显示的,而不是为了限制在该列中存储值的宽度,也 不是为了限制那些超过该列指定宽度的值的可被显示的数字位数
(3)、一个列被定义为 INT(5) ZEROFILL,插入的值 4 被检索出来时为 00004。注意,如果在一个整型列中存储一个超过显示宽度的更大值时,当 MySQL 为某些复杂的联结(join)生成临时表时,你可能会遇到问题,因为在这种情况下,MySQL 信任地认为所有的值均适合原始的列宽度
9、使用短数据类型,比如取值范围为0-80时,使用TINYINT UNSIGNED
(1)、tinyint : 1 字节 (-128,127) (0,255) 小整数值
(2)、SMALLINT : 2 字节 (-32 768,32 767) (0,65 535) 大整数值
(3)、MEDIUMINT : 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
(4)、INT或INTEGER : 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
(5)、BIGINT : 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
(6)、mysql 数据库中要加入字段,并设置默认值为零,数据库设计原则所占的储存空间越少越好,够用就行,基于节省存储空间的考虑所以用了tinyint类型,
而且在tinyint的使用中,MYSQL中没有布尔类型,但是如果你定义了布尔类型,它会自动给你转换成Tinyint,保存BOOLEAN值时用1代表TRUE,0代表FALSE,boolean在MySQL里的类型为tinyint(1),MySQL里有四个常量:true,false,TRUE,FALSE,它们分别代表1,0,1,0。也就是说如果需要建立一张大量存储0和1的字段的表,可以充分考虑tinyint了。
10、不建议使用ENUM类型,使用TINYINT来代替
(1)、假如一个设计不合理的ENUM字段,给程序员带来的就完全是梦魇了,比如一个enum字段的范围是('0','1','2','3','4','5'),而enum的枚举值对应的索引是从1开始的,因此,insert into table (enum)values(1),插入的并不是1,而是0
(2)、另外假如你在设计好enum的枚举字段范围并使用了一段时间后,再到字段范围中加一个枚举值,并且不是加在最后,那么也就相当于把原来的范围都改变了索引值,也就是当你在查询的时候直接查询值(并加上单引号),将不会使用enum自身隐藏的索引值来获取结果了
(3)、如果是纯数值型,还是建议采用tinyint字段吧,毕竟它也只占一个字节,即使出现赃数据,也可以被接受,不象enum,如果纯数字型范围,更改了索引,你就不知道你查询的值是否正确了
(4)、如果字段是字符串,并且长度固定,可以尝试用char,如果是数值型,还是用tinyint吧,比较安全稳定,而且即使迁移,也不会出现太多问题
11、尽可能不使用TEXT、BLOB类型
(1)、索引排序问题,只能使用max_sort_length的长度或者手工指定ORDER BY SUBSTRING(column,length)的长度来排序
(2)、Memory引擘不支持text,blog类型,会在磁盘上生成临时表
(3)、可能浪费更多的空间
(4)、可能无法使用adaptive hash index
(5)、 导致使用where没有索引的语句变慢
12、VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N
(1)、
13、VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存
(1)、VARCHAR(M),如果M<256时会使用一个字节来存储长度,如果M>=256则使用两个字节来存储长度
14、表字符集选择UTF8
(1)、排序规则名称由两部份构成,
前半部份是指本排序规则所支持的字符集,
后半部份即后缀
_BIN 二进制排序
_CI(CS) 是否区分大小写,CI不区分,CS区分
_AI(AS) 是否区分重音,AI不区分,AS区分
_KI(KS) 是否区分假名类型,KI不区分,KS区分
_WI(WS) 是否区分宽度 WI不区分,WS
(2)、mysql 中文字段排序( UTF8按拼音首字母排序)
15、使用VARBINARY存储变长字符串
二进制字节流,不存在编码问题
16、存储年使用YEAR类型
YEAR类型占用1字节,并且在定义时可以指定显示的宽度为YEAR(4)或YEAR(2)
17、存储日期使用DATE类型
DATE 用于表示 年月日,如果实际应用值需要保存 年月日 就可以使用 DATE
18、存储时间(精确到秒)建议使用TIMESTAMP类型,因为TIMESTAMP使用4字节,DATETIME使用8个字节
DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性
19、建议字段定义为NOT NULL
not null的效率比null高
20、将过大字段拆分到其他表中
执行效率更快
21、禁止在数据库中使用VARBINARY、BLOB存储图片、文件等
采用分布式系统更高效
22、表结构变更需要通知DBA审核