选择优化的数据类型
Mysql支持的数据类型非常多,有个简单的选择原则:
- 更小的通常更好。一般情况下尽量使用可以正确存储数据的最小数据类型。会占用更少的磁盘、内存和CPU缓存。但是要确保没有低低估需要的存储范围,如果无法确定哪个数据类型是最好的,就选择你认为不会超过范围的最小类型
- 简单就好。简单数据类型的操作通常需要更少的CPU周期。
- 尽量避免使用NULL。NULL会使得索引、索引统计和值比较更复杂。并且占用更多的存储空间。当可以NULL的列被索引时,每个索引记录都需要一个额外的字节。在MYSIAM中甚至会使固定大小的索引变为可变大小的索引。虽然影响小。InnoDB使用单位的位存储NULL值,所以对于稀疏数据有很好的空间效率
整数类型
- 整数 :TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。整数类型有UNSIGNED属性,表示不允许负值。例如,TINYINT UNSIGNED可以存储的范围从 -128 ~ 127 变为 0 ~ 255。MySql可以为整数类型指定宽度,但是他们存储空间是一样的,只是表示显示字符的个数。例如INT(1)和INT(11),它们的存储和计算是一样的
- 实数类型 :DECIMAL类型用于存储精确的小数。MySQL5.0及以上,DECIMAL支持精确计算。因为CPU不支持低DECIMAL的直接计算,所以MYSQL服务器自身实现了DECIMAL的高精度计算。相对而言,CPU直接支持原生浮点计算,所以浮点运算比较快。FLOAT占用4个字节,DOUBLE占用8个字节。因为需要额外的空间和计算开销,尽量在只对小数进行精确计算时才使用DECIMAL。例如存储财务数据。
- 字符串类型:
- VARCHAR: 用于存储可变长字符串。比定长更节省空间。但是MySQL表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,会浪费空间。VARCHAR需要使用1或2个额外字节存储字符串的长度(小等于255占1个字节),由于长度是可变的,导致在UPDATE的时候如果长度变长久需要做额外的工作。如果在页内没有更多的空间存储,MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。MySQL5.0以上,存储会保留末尾的空格。
- CHAR:定长,Mysql会剔除末尾空格。适合存储很短的字符串,或者长度接近的值。例如密码的MD5值。对于需要经常变更的数据,CHAR也比VARCHAR更好,因为不容易产生碎片。
- BLOB 和TEXT类型:都是为了存储很大的数据,分别采用了二进制和字符方式存储。当BLOB和TEXT值太大时,InnoDB会使用1到4个字节存储一个指针,然后再外部存储区存储实际的值。BLOB没有排序规则或字符集,而TEXT类型有排序规则和字符集。MySQL只对每个列最前max_sort_length字节排序。MySQL不能将BLOB和TEXT列全部长度的字符串进行索引,也不能使用这些索引消除排序。
- 使用ENUM代替字符串类型:ENUM可以把一些不重复的字符串存储或预定义的集合。MYSQL在内部会将每个值在列表中的位置保存为整数,并且在*.frm文件中保存"数组 - 字符串"映射关系的查找表。同时ENUM是按照内部存储的整数而不是定义的字符串进行排序的*。但是在添加或修改内部的字符串时必须使用ALTER TABLE。因此对于未来可能会改变的字符串,ENUM并不合适,除非能接受只在列表的末尾添加元素。在特定的情况下,把CHAR/VARCHAR列与ENUM列进行关联可能会比直接关联CHAR/VARCHAR列更慢。
与CHAR和VARCHAR相似的还有BINARY和VARBINARY,他们存储的是二进制字符串,存储的是字节码而不是字符。
- 日期和时间类型
- DATETIME: 能保存大范围的值,从1001到9999年,精度为秒。把时间封装为整数,与时区无关,使用8个字节存储。
- TIMESTAMP: 4个字节的存储空间。范围小:1970 ~ 2038年。MYSQL提供了FROM_UNIXTIME()函数将UNIX时间戳转换为日期,UNIX_TIMESTAMP将日期转换为时间戳。依赖时区。
通常使用TIMESTAMP,空间效率更高。
- 位数据类型
- BIT : MYSQL5.0之前BIT是TINYINT的同义词。 可以使用BIT列存储一个或多个true/false值。BIT列的最大长度为64个位。MYISAM会打包存储所有的BIT列,所以17个单位的BIT列只需要17个位存储。InnoDB个Memory,为每个BIT列使用一个足够存储的最小整数类型来存放,所以不能节省存储空间。
- SET:存储很多false/true值。
- 选择标识符
- 整数类型 :通常是标识符最好的选择,很快并且可是使用AUTO_INCREMENT。
- ENUM和SET类型:适合有序的状态、产品类型、人的性别。
- 字符串类型:尽量避免,比较消耗空间,并且速度比整数慢。
- 特殊数据类型
MYSQL Schema 设计中的陷阱
- 太多的列 : MYSQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。操作代价非常高。
- 太多的关联:MYSQL限制了每个关联操作最多只能有61张表。如果希望查询执行的最快且并发性最好,单个查询最好在12个表以内做关联。
- 全能的枚举:避免过度使用枚举,除非在末尾添加,不然需要ALTER TABLE,会发生阻塞操作。
- 变相的枚举
- NULL的使用:除非必要,不然可以使用特殊值代替NULL。
范式和反范式
范式的优点和缺点:
- 范式化的更新操作通常比反范式要快
- 当数据范式化时,就只有很少或者没有重复的数据,所以更新只需要修改更少的数据
- 范式化的表通常更小,可以很好的放在内存中,所以执行速度更快
- 更少需要DISTINCT或者GROUP BY语句
反范式化的优缺点
- 避免了关联
- 避免了随机I/O
加快ALTER TABLE操作的速度
- 在一台不提供服务的机器上执行ALTER TABLE的操作,然后和提供服务的主库进行切换
- 用要求的表结构创建一张和原表无关的新表,然后通过重命名和删表操作交换两张表
只修改.frm文件
- 创建一张有相同结构的空表,修改需要的
- 执行FLUSH TABLES WITH READ LOCK。这会关闭所有正在使用的表,并且禁止任何表被打开
- 交换.frm文件
- 执行UNLOCK TABLES来释放第二步的读锁
快速创建MyISAM索引
先禁用索引,载入数据,然后重新启用索引
ALTER TABLE test.load_data DISABLE KEYS;
-- load data
ALTER TABLE test.load_data ENABLE KEYS;
由于构建索引的工作被延迟到数据完全载入以后,这个时候可以通过排序来构建索引。(对唯一索引无效,因为DISABLE KEYS只对非唯一索引有效)