MySQL学习笔记--Schema与数据类型优化

一.选择优化的数据类型

更小的通常更好：占用更少的磁盘、CPU、内存，处理时需要的CPU周期更少，需要注意的是增加数据类型的范围是很耗时的

简单的更好：简单数据类型的处理通常需要更少的CPU周期，整型比字符串操作代价更少，使用MySQL内建类型来存储日期和时间而不是使用字符串，使用整型存储IP地址

尽量避免NULL：NULL是列的默认属性，通常应指定NOT NULL；包含NULL的列，MySQL更难优化，应为使索引、索引统计和值比较更复杂；可为NULL的列会占用更多的空间，也需要特殊处理；可为NULL的列被索引时，每个索引记录需要额外的字节，在MyISAM引擎中，可能会导致固定大小的索引变成可变大小的索引。

在为列选择数据类型时，首先选定大类型（整型、字符串、日期等）；其次，选择具体类型，精度、长度等；

1.整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别占用8(1字节)、16(2字节)、24(3字节)、32(4字节)、64(8字节)位，存储范围为-2^n-1-----2^n-1-1，其中n是位数。

制定int(11)对大多数应用来说是无意义的，与int(1)占用空间是一样的。

2.实数类型/浮点

MySQL既支持精确类型，也支持不精确类型。

FLOAT(4字节)和DOUBLE(8字节)支持使用标准的浮点运算进行近似计算。

DECIMAL用于存储精确的小数，需要额外的空间和计算开销，多用来储存财务数据。在5.0及以上版本，该类型支持精确计算。允许最多65个数字。可把数字扩大相应倍数，使用BIGINT存储DECIMAL。

3.字符串

VARCHAR：存储可变长字符串。比定长字符串更省空间，仅使用必要的空间，越短字符串占用空间越少。若表使用ROW_FORMAT=FIXED，每一行都会使用定长。使用1~2个字节存储字符串长度，若列长度小于或等于255使用1个字节，否则使用两个字节；例：使用latin1字符集，VARCHAR(10)占用11个字节，VARCHAR(1000)占用1002个字节；由于行为变长，更新时可能需要做额外的工作，若行占用的空间增长，不同的引擎处理方式不一样，MyISAM拆成不同的片段存储，InnoDB则需要分裂页将行放入一页内；存储时保留末尾空格

CHAR：存储定长字符串。适合存储很短的字符串或长度基本一致的字符串。存储时删除末尾空格；数据如何存储决定于存储引擎；Memory引擎只支持定长的行

与CHAR跟VARCHAR相似的还有BINARY跟VARBINARY，他们存储的是二进制字符串，存储的是字节码而不是字符，填充的是\0而不是空格

BLOB和TEXT：分别采用二进制方式和字符方式存储很大的字符串数据。TINYTEXT/SMALLTEXT/TEXT/MEDIUMTEXT/LONGTEXT，TINYBLOB/SMALLBLOB/BLOG/MEDIUMBLOG/LONGBLOB。BLOB是SMALLBLOB同义词，TEXT是SMALLINT同义词。MySQL把每个TEXT和BLOB当作一个独立的对象处理；存储引擎存储时会做特殊处理；值太大时，InnoDB会使用外部存储区域，行内存储的为1~4个字节的指针；BLOB没有排序规则或字符集；TEXT有排序规则和字符集；只对每列最前的max_sort_length长度进行排序，可以修改此设置或使用ORDER BY SUSTRING(column,length)；不能将全部长度进行索引，也不能使用这些索引消除排序；

ENUM：可使用此类型代替常用的字符串类型以减少空间占用。避免使用ENUM存储数字；按照内部存储的整数而不是定义的字符串进行排序；可以在查询中显式的使用field(column,'value1','value2')；修改ENUM中值需要ALTER TABLE，可能耗时很长；MySQL把枚举值保存为整数，因此取列值时需进行查找才能转换为字符串，所以枚举有一些开销；

DATETIME和TIMESTAMP：DATETIME保存范围较大1001年~9999年，精度到秒，把日期封装到YYYYMMDDHHMMSS格式的整数中，与时区无关，使用8字节；TIMESTAMP保存的为1970年1月1日午夜以来的秒数，使用4个字节，存储1970~2038年日期，显示依赖于时区；可使用FROM_UNIXTIME()把时间戳转换为时间；UNIX_TIMESTAMP()把日期转换为时间戳；TIMESTAMP效率更高；

位数据：

        BIT，bit(1)表示1位，bit(2)表示2位，最大64位；MyISAM会打包所有的BIT列，所以15个单独的BIT列只占15位；InnoDB为每个BIT列使用一个足够存储的最小整数来存储，因此不能减少空间占用；

        SET，类似于可以有多个值的ENUM，可以使用find_in_filed()，field()这样的函数进行查询

4.选择标识符

为标识列选择合适的数据类型非常重要。更有可能使用标识列与其他列进行比较，比如关联操作，或通过标识列查找其他列；应跟关联表中的列中数据类型一致；类型要精确匹配，包括UNSIGNED；在满足当前及未来需要的前提下，应选择最小类型；整数类型通常是标识列最好的数据类型，速度快并且可以使用AUTO_INCREMENT；不要使用ENUM和SET类型；避免使用字符串类型；

二.Schema设计陷阱

• 太多的列：例如ORM

太多的关联：MySQL限制每个关联操作最多61个表，单个查询最好在12个以内做关联；

全能的枚举：过度枚举

变相的枚举：使用SET代替ENUM

三.范式与反范式

范式化：每个事实数据出现并且只出现一次。

反范式化：数据冗余，出现多次。

范式优点：

• 更新操作比反范式快
• 表更小，占用内存少
• 因数据很少重复，更少需要distinct和GROUP BY

范式缺点：

• 通常需要关联表

反范式优点：

• 可以很好的避免关联

反范式缺点：

• 如不需要关联查询，效率低

在实际涉及中，混用两种方式