MySQL的故事——Schema与数据类型优化

Schema与数据类型优化

---

一、选择优化的数据类型

更小的通常更好
应该尽量使用可以正确存储数据的最小类型，更小的数据类型通常更快，因为他们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期更少
简单就好
更简单的数据类型的操作通常需要更少的CPU周期。例如 ，整型数字比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较相对整型数字比较更复杂。比如，应使用INTERGER存储IP地址
尽量避免NULL
通常情况下，最好指定列为NOT NULL。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较非常复杂，可为NULL的列会使用更多的存储空间，当可为NULL的列被索引时，每个索引记录需要一个额外的字节。但是可为NULL的列改成NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该避免设计成可为NULL的列。

4.1.1 整数类型

整数类型 占用空间 范围
TINYINT 8 [-2^7,27-1]
SMALLINT 16 [-2^15,215-1]
MEDIUMINT 24 [-2^23,223-1]
INT 32 [-2^31,231-1]
BIGINT 64 [-2^63,263-1]

INT(11)只是指定显示字符的范围。不会限制值的范围

4.1.2 实数类型
实数是带有小数部分的数字，可以使用DECIMAL存储比BIGINT还大的整数。
DECIMAL类型用于存储精确的小数，支持精确计算。

4.1.3 字符串类型
varchar类型用于存储可变长字符串，比定长更节省空间。
char定长字符串，MySQL在存储时会去除char尾部的空格。
blob采用二进制的方式存储，没有排序规则和字符集。包含tinyblob,blob,mediumblob,longblob
text采用字符串的方式存储，有排序规则和字符集，包含 tinytext,text,mediumtext,longtext

4.1.4 日期和时间类型
DATETIME和TIMESTAMP
现在推荐使用DATETIME，范围更大，与时区无关，占8个字节
datetime:大范围的值 1001-9999年 YYYYMMDDhhmmss 与时区无关 8字节
timestamp: 1970-2038年 存的是1970.1.1以来的秒数 和时区相关 4字节

二、MySQL模式设计的陷阱

太多的列
太多的关联
NULL值

三、范式和反范式

在范式化的数据库中，每个事实数据只会出现一次
反范式化的数据库中，信息是冗余的，可能会存储在多个地方

范式优点：
范式化的更新操作更快，只需要更改较少的数据。
范式化的表更小，可以更好的放在内存里，执行操作会更快。
没有多余的数据，可以减少distinct或GROUP BY的操作
范式缺点：
通常需要表关联，，关联代价昂贵，也可能使一些索引策略无效。
反范式优点：
所有的数据都在一张表中，可以避免关联。
不关联的时候即使全表扫描，也是顺序IO。
反范式缺点：
冗余的多余数据，更新更慢。
表大，放到内存中，占用大，容易挤出热数据

四、缓存表和汇总表

缓存表：存储比较简单可以从schema其它表获取（但是获取速度比较慢）数据的表（例如，逻辑上冗余的数据）
汇总表：保存的是使用GROUP BY聚合数据的表（例如，数据不是逻辑上冗余的）

五、加快Alter table速度

Alter table操作对特大表来说，是个大问题。
可以只修改frm(表结构)文件来加快速度

---