Schema与数据类型优化
需要优化的数据类型
- 更小的通常更好
- 简单就好
- 尽量避免NULL
整数类型
存储整数,有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别使用8、16、24、32、64位空间,表示范围为-2^(N-1)到2^(N-1)-1。
整数类型有可选的UNSIGNED属性,表示不允许负值。
INT(11)指定了宽度,但不会限制值的合法范围,只规定了显示的字符个数。
实数类型
带有小数部分的数字,FLOAT占4个字节,DOUBLE占8个字节,DECIMAL占用字节较多但支持高精度运算。
字串类型
VARCHAR存储可变长字串,需要1-2个字节记录字串的长度,若列最大长度超过255字节,使用2个字节记录长度。
CHAR定长,总是根据定义长度分配空间,很适合存储MD5值,相比VARCHAR不容易产生碎片。
类似的还有BINARY和VARBINARY,同时要注意即使是可变长字串,最好也分配更小的空间。
BLOB和TEXT类型
都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
字符类型分别为TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT和TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。
MySQL将他们当作独立对象存储,不会直接插入到页中,只能根据指定长度头进行索引,不能全文索引。
使用枚举(ENUM)代替字符串类型
MySQL会将枚举值保存为整数,在外部单独维护这样一个映射表,但枚举不适合经常修改的表。
日期和时间类型
DATETIME保存大范围时间,从1001年到9999年,精度为秒,它将日期封装到YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节空间。
TIMESTAMP保存从1970年1月1日午夜以来的秒数,与UNIX时间戳相同,只使用4个字节空间,范围小,只能表示1970到2038年。
尽量使用TIMESTAMP。
位数据类型
从技术来说都是字串类型。
BIT,存储多个bool值,最大长度64位。最好少用这个类型,因为转码与显示问题,比较复杂。
SET保存更多的bool值,修改操作代价较高。
选择标识符
- 整数通常是最好的选择,因为很快并且可以使用自增;
- ENUM和SET,可行但最好不要;
- 字符串,绝对不要,占空间速度慢。
Schema设计陷阱
- 太多的列;
- 太多的关联(单个查询在12表以内作关联);
- 全能的枚举(不要过度使用枚举);
- 变相的枚举(SET有时可以用作枚举,但最好用ENUM枚举);
- NULL(尽量不要用,但该用的时候也要用)。
范式和反范式
范式意味着分表、关联、外键等等,因为单个表或者说数据被拆分了,查询修改性能都会得到提升,但也会导致更多关联操作。
反范式意味着数据都在一张表里,避免了关联操作。
完全的范式和反范式不存在的,正常工作中都是混用的。
缓存表和汇总表
这两个是术语而不是真实存在的表,缓存表表示那些查询缓慢的表,汇总表则是使用了GROUP BY的表。
加快ALTER TABLE操作
常见操作有两种,一是在一台不提供服务的机器上执行ALTER TABLE然后与主库进行切换,二是影子拷贝(重命名与删除)。
修改表定义文件
这种操作是不受官方支持的,因此如果确实要进行,请先备份。
创建新的.frm文件,然后替换已存在的文件。
- 创建相同结构的表,执行需要的更改;
FLUSH TABLES WITH READ LOCK;
关闭所有正在使用的表,禁止任何表被打开;- 交换.frm文件
- 执行
UNLOCK TABLES;
释放读锁; - 删除辅助表。