高性能MySQL笔记 第4章 Schema与数据类型优化

4.1 选择优化的数据类型
 
通用原则
 
更小的通常更好
 
前提是要确保没有低估需要存储的值范围:因为它占用更少的磁盘、内存、CPU缓存,并且处理时需要的CPU周期也更少。
 
简单就好
 
简单数据类型的操作需要更少的CPU周期。
 
尽量避免NULL
 
值可为NULL的列使得索引、索引统计和值比较都更复杂化。可为NULL的列会使用更多的存储空间。
 
整数类型
 
TINYINT SMALLINT MEDIUMINT INT BIGINT。分别使用8,16,24,32,64位存储空间。他们可以存储的值的范围从 -2(N-1) 到 2(N-1)-1,其中N为存储空间的位数。
 
UNSIGNED 属性标识不允许负值,存储之的范围将变为0到2(N)-1。
 
MySQL 可以为整数类型指定宽度,例如INT(11),他不会限制值得合法范围,只是规定了MySQL的一些交互工具 用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。
 
实数类型
 
FLOAT DOUBLE DECIMAL。
 
字符串类型
 
VARCHAR CHAR
 
  • VARCHAR 类型用于存储可变长字符串。
 
  • CHAR 类型用于存储定长字符串。非常适用于存储MD5值。
 
  • VARCHAR 需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在update时可能使行变得比原来更长,这就导致需要做额外的工作,不同的存储引擎的处理方式是不一样的。例如,MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。
 
 
BLOB TEXT
 
  • BLOB采用二进制方式存储,没有排序规则或字符集。
 
  • TEXT采用字符方式存储,有字符集和排序规则。
 
  • TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT,其中TEXT=SMAILLTEXT。
 
  • TINYBLOB SMALLBLOB BLOB MEDUIMBLOB LONGBLOB,其中BLOB=SMALLBLOB。
 
使用枚举(ENUM)代替字符串类型
 
枚举列把一些不重复的字符串存储成一个预定义的集合。
 
MySQL在内部会将每个值在集合中的位置保存为整数。
 
日期和时间类型
 
DATETIME TIMESTAMP TIME DATE YEAR
 
特殊类型数据
 
IPv4地址,MySQL提供INET_ATON() 和INET_NTOA()函数来相互转换。
 
4.2 MySQL schema 设计中的陷阱
 
  • 太多的列
 
  • 太多的关联:最好小于12
 
  • 错误使用枚举
 
  • 非此发明的NULL
 
4.3 范式和反范式
 
三大范式
 
  • 第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。
 
  • 第二范式(2NF)要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识(主键)。
 
  • 第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
 
范式的优缺点
 
优点
 
  • 范式化的更新操作通常比反范式化要快;
 
  • 当数据较好地范式化时,就只要很少或者没有重复数据,所以只需要修改更少的数据;
 
  • 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快;
 
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
 
缺点
 
范式化设计的schema的缺点通常需要关联。这不但代价昂贵,也可能使一些索引策略无效。
 
反范式的优缺点
 
优点
 
  • 避免关联;
 
缺点
 
  • 冗余字段;
 
4.6 总结
 
  • 尽量避免过度设计,例如会导致极其负责查询的schema设计,或者有很多列的表的设计;
 
  • 使用小而简单的适合数据类型,除非真实数据模型镇南关有确切的需要,否则应该尽可能地避免使用NULL值;
 
  • 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列;
 
  • 注意可变长字符串,其在临时表中排序时可能导致悲观的按最大长度分配内存;
 
  • 尽量使用整形定义标识列;
 
  • 避免使用MySQL已经遗弃的特性,例如指定浮点数的精度,后者整数的显示宽度;
 
  • 小心使用ENUM和SET。虽然他们用起来很方便,但是不要滥用,否则有时候会变成陷阱。最好避免使用BIT;
 
 
 
 
posted @ 2016-04-26 16:37  jxlwqq  阅读(246)  评论(0编辑  收藏  举报