Schema与数据类型优化
良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计SCHEMA,这往往需要权衡各种因素。如:反范式设计可以加快某些类型的查询,但同时可能会使另一些查询变慢和占用更多的存储空间。可能会导致表的维护成本提高。MYSQL独有的特性和实现细节对性能的影响也很大。
选择优化的数据类型MYSQL的数据类型非常多,选择正确的数据类型对获得高性能至关重要。不管存储哪种数据类型,以下几个简单原则有助于做楚更好的选择:
更小的通常更好:
一般情况下,应尽量使用可以正确存储数据的最小数据类型,因为更小的数据类型通常更快占用更少的磁盘、内存、和CPU缓存,并且处理需要的CPU周期也更少简单就好:
简单数据类型的操作通常需要更少的CPU周期。尽量避免NULL:
通常情况下最好指定列为NOT NULL,除非整的需要存储NULL的值;如果查询中包含可为NULL的列,对MySQL来说更难优化,因为NULL的列使得索引、索引统计和值都比较复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理;如果计划在列上建立索引,就应该尽量避免设计成可为NULL的列。
整数类型
有两种类型的数字:整数和实数;可以使用:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等几种整数类型。整数类型可选择UNSIGNED属性,表示不允许为负值。MySQL可以为整数类型指定宽度,但对大多数应用来说是无意义的,对于存储和计算来说,int(1)和int(20)是相同的。
实数类型
实数是带有小数部分的数字,DECIMAL类型用存储精确的小数。
字符串类型
VARCHAR和CAHR是两种最主要的字符串类型
VARCHAR:
该类型用于存储可变长字符串,它比定长类型更节省空间,varchar需要使用1或2个额外字节记录字符串的长度。CHAR:
该类型是定长的,它适合存储很短的字符串或所有值都接近同一个长度的字符串,对于经常变更的数据,CHAR比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。BLOG和TEXT类型:
BLOG和TEXT都是为了存储很大的数据而设计的字符串数据类型,BLOG采用二进制方式存储,没有排序规则或字符集;TEXT采用字符串方式存储,它有字符集和排序规则。枚举(EMUN)类型:
有时候可以使用枚举列表代替常用的字符串类型,枚举可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑,它会根据列表值的数量压缩到一个或两个字节中。
时间和日期类型
DATETIME:
这个类型能保存大范围的值,精度为秒,使用8个字节的存储空间。TIMESTAMP:
它和UNIX时间戳相同,只能表示从1970年到2038年,使用4个字节的存储空间。
MySQL schema设计中的陷阱
虽然有一些普遍的好的坏的设计原则,但有一些问题是由MySQL的实现机制导致的,意味着有可能犯一些只在MySQL下发生的特定错误。以下这些有可能会帮助避免这些错误:
太多的列
太多的关联:
MySQL限制了每个关联最多只能有61张表,如果希望查询执行得快且并发性好,单个查询最好在12个表内做关联(个人建议最好控制在5 个表内做关联)全能的枚举
变相的枚举
非此发明的NULL
范式和反范式
在范式化数据库中,每个事实数据会出现并且只出现一次;在反范式化数据库中,信息是冗余的,可能需要存储在多个地方。
范式化的优点与缺点
范式化带来的好处:
- 范式化的更新操作通常比反范式化要快。
- 当数据较好地范式化时,只有很少或者没有重复数据,所以只需修改更少的数据。
- 范式化的表通常更小,可以更好地放在内存里,所以执行更快
- 很少有多余的数据,意味着检索列表数据时不需要更多的DISTINCT或者GROUND BY语句。
范式化设计的缺点是通常需要关联,稍微复杂的一些查询语句在符合范式化的schema上可能需要至少一次关联,也许更多;这使得代价昂贵,也可能使一些索引策略无效。
反范式化的优点和缺点
优点:
- 反范式化设计的schema所有数据都在一张表中,可以很好地避免关联
- 单独的表可以使用更有效的索引策略。
反范式化的缺点:
表格内冗余数据比较多,在进行数据删除时可能会导致一些重要信息或有用信息丢失。建议:
范式化和反范式化的schema各有优劣,在实际应用中经常需要混用。范式是好的,但反范式(大多数情况下会重复数据)有时是必须的,并且能带来好处
加快ALTER TABLE操作速度
MySQL的ALTER TABLE操作性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。这样的操作可能需要花费很多时间,如果内存不足而表又很大,而且还有 很多索引的情况下尤其如此。
只修改 .frm 文件:
- 创建一张有相同结构的空表,并进行所需要的修改
- 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表,并且禁止任何表被打开
- 交换 .frm 文件
- 执行UNLOCK TABLES 来释放第二步的读锁
快速创建MyISAM索引:
(未完待续)