前言:
高性能的基石:良好的逻辑、物理设计,根据系统要执行的查询语句设计schema
本章关注MySQL数据库设计,介绍mysql数据库设计与其他关系型数据库管理系统的区别
schema:【源】
schema就是数据库对象的集合,这个集合包含了各种对象如:表、视图、存储过程、索引等。为了区分不同的集合,就需要给不同的集合起不同的名字,默认情况下一个用户对应一个集合,用户的schema名等于用户名,并作为该用户缺省schema。所以schema集合看上去像用户名。
如果把database看作是一个仓库,仓库很多房间(schema),一个schema代表一个房间,table可以看作是每个房间中的储物柜,user是每个schema的主人,有操作数据库中每个房间的权利,就是说每个数据库映射的user有每个schema(房间)的钥匙。 SQL server和Oracle mysql有别
4.1选择优化的数据类型
原则:
1、更小的通过更好,尽量使用可正确存储数据的最小的数据类型(占更少的磁盘 内存 CPU缓存,处理时需要CPU周期更少:更快),但能罩得住数据,存不下就尴尬了
2、简单就好:简单类型(更少CPU周期),使用MySQL内建类型存时间,整型存ip,整型较字符代价低(字符集和校对排序规则使字符较复杂)
3、尽量避免null:最好指定为not null
*)null列使用更多的存储空间,mysql里需要特殊处理
*)null使索引、索引统计和值比较更复杂;可为null的列被索引时,每个索引记录需额外的字节
例外:InnoDB使用单独位bit存储null,so对于稀疏数据(很多值为null)有很好的空间效率,不适合MyISAM
4.1.1整数类型【参考】
整数whole number
tinyint(8位存储空间) smallint(16) mediumint(24) int(32) bigint(64)
1、存储值的范围:
,N是存储空间的位数
2、unsigned:可选、不容许负值,可使正数的上限提高一倍:tinyint unsigned 0~255,tinyint-128~127
3、有无符号使用相同的存储空间,相同的性能
可为整型指定宽度,例如INT(11),对于大多数应用无意义,不会限制值的合法范围,只是规定了交互工具显示字符的个数,对于存储和计算,int(1)和int(20)是相同的;
实数real number:带小数
float和double,mysql使用duble作为内部浮点计算的类型
decimal:存储精确的小数,mysql服务器自身实现,decimal(18,9)18位,9位小数,9个字节(前4后4点1)
尽量只在对小数进行精确计算时才使用(额外的空间和计算开销),如财务数据
数据量大时,考虑使用bigint代替,将需要存储的货币单位据小数的位数乘以相应的倍数
浮点:
建议:只指定类型、不定精度(mysql),这些精度非标准,mysql会悄选类型、或存时对值取舍
存储同样范围的值时,比decimal更少的空间,float4字节存 double8字节(更高精度范围)
4.1.3字符串类型
varchar和char:
前提:innodb和myisam引擎,最主要的字符串类型
磁盘存储:存储引擎存储的方式与在内存、磁盘上的不能不一样,所以mysql服务器从引擎取值需转格式
varchar:
1、存储可变字符串,比定长节省空间(仅使用必要的空间),但如果表使用row_format=fixed,行会定长存储
2、需使用1/2额外字节记录字符串长度;1)列max长度<=255字节,1字节表示,否2字节,2)采用latinl字符集,varchar(10)列需11个字节的存储空间,varchar(1000)1002字节,2字节存储长度信息
3、节省存储空间,利于性能;但在update可能使行变得比原来更长、需做额外工作
合适的情况:
1)字符串列最大长度比平均长度大很多;2)列的更新少(不担心碎片);3)使用UTF-8字符串,每个字符均使用不同的字节数存储
char:
1、定长,据长度分配空间,删除all末尾空格;长度不够、空格填充
2、存储空间上更有效率,char(1)来存储只有Y N的值 1个字节 ,varchar2字节,还有一个记录长度
适合的情况:
1)适合存储很短的字符串;2)或all值接近同一个长度;3)经常变更的数据,存储不易碎片
对应空格、存储:
char类型存储时末尾空格被删;数据如何存储取决于存储引擎,Memory引擎只支持定长的行(最大长度分配空间)
binary,varbinary:存储二进制字符串,字节码,长度不够、0来凑(不是空格)检索时不会去
慷慨不是明智的:varchar(5)和varchar(100)存储‘hell’空间开销一样,长的列消耗更多内存
blob和text:大数据
分别用二进制和字符方式存储,分别属于两组不同的数据类型:字符类型:tinytext、smalltext、text、mediumtext、longtext,对应的二进制类型是tinyblob、smallblob、blob、mediumblob、longblob,两类仅有的不同:blob类型存储的是二进制,无排序规则或字符集,text有字符串 排序规则;
MySQL会把每个blob和text当做独立的对象处理,存储引擎存储时会做特殊处理,当值太大,innoDB使用专门的外部存储区域进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储实际的值;
mysql对他们的列排序:只对每列前max_sort_length字节排序;且不能将列全部长度的字符串进行索引,也不能使用这些索引消除排序;
如果explain执行计划的extra包含using temporary:这个查询使用了隐式临时表
使用enum代替字符串类型
定义时指定取值范围,对1~255个成员的枚举需要1个字节存储;对于256~65535个成员,需要2个字节存储。最多可以有65535个成员,ENUM类型只能从成员中选择一个;和set相似
可把不重复的固定的字符串存储成一个预定义的集合,mysql在存储枚举时会据列表值的数量压缩到1/2字节中,在内部会将每个值在列表中的位置保存为整数(从1开始,必须进行查找才能转换为字符串,开销、列表小 可控),且在表的.frm文件中保持“数字-字符串”映射关系的“查找表”;
将一个数字存储到一个 ENUM 中,数字被当作为一个索引值,并且存储的值是该索引值所对应的枚举成员: 在一个 ENUM字符串中存储数字是不明智的,因为它可能会打乱思维;ENUM 值依照列规格说明中的列表顺序进行排序。(ENUM 值依照它们的索引号排序。)举例来说,对于 ENUM("a