高性能MySQL学习笔记——第四章Schema与数据类型优化

4.1选择优化的数据类型

选择原则：

1. 更小的通常更好 ，占用更少的磁盘、内存和CPU缓存，处理更快；
2. 简单就好，目的与上一点差不多，为了更少的CPU周期，一般使用更简单的数据类型减少开销，尤其有内置类型则优先使用；
3. 尽量避免 NULL ，如果查询中包含可为NULL的列，对于MySQL很难优化，含有NULL的列使得索引、索引统计和值比较都更复杂，且占用空间大，被索引时还需要添加额外字节；
   尽管将NULL的列改为NOT NULL的列对性能提升不大，可以视实际情况做出改变，但还是尽量避免NULL吧

整数类型

整数类型：

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8，16,24,32,64位存储空间。

MySQL可以为正数类型指定宽度，例如INT(11)，但大多数应用这是没有意义的。对于存储和计算来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近视计算。DECIMAL类型用于存储精确的小数。

浮点类型在存储同样类型的范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储.DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将对应的值扩大N倍。

字符串类型

VARCHAR:

它比定长类型更节省空间，因为它仅使用必要的空间。

有额外字节记录长度。如果列的最大长度小于或等于255字节，则只使用1个额外字节表示

但是由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。

如果一个行占用的空间增长导致页内没有更多的空间可以存储，MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

MySQL在存储和检索时会保留末尾空格。
　　
CHAR:

该类型是定长的，当存储CHAR值时，MySQL会删除所有的末尾空格。

适合存储MD5值。

定长的CHAR类型不容易产生碎片，对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，VACHAR还有一个记录长度的额外字节。

BLOB和TEXT类型

为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

字符类型：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT

二进制类型：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB

缺点：查询使用BLOB或TEXT会使用MyISAM磁盘临时表，会导致严重的性能开销，最好尽量避免使用。如果无法避免 ，则尝试在所有用到BLOB字段的地方都使用SUBSTRING(column，length)将列值转换为字符串

日期和时间类型：

MySQL能存储的最小时间粒度为秒。但是MySQL 也可以使用微秒级的粒度进行临时运算。

DATETIME :

这个类型能保存大范围的值，从1001年到9999年，精度为秒。

它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中。默认显示格式为"2008-02-16 22:37:08"

它与时区无关。

TIMESTAMP：

保存从1970年1月1日午夜依赖的秒数，它和UNIX时间戳相同。只能表示从1970年到2038年。TIMESTAMP与DATETIME存储的数据类型一样，不过TIMESTAMP因为空间占用小，所以效率更高。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

它和时区有关。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

4.2 MySQL schema设计中的陷阱

1）太多的列：由于MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列的内容。

这个开销比较大，尤其MyISAM的变长行结构和InnoDB的行结构总是需要转换，代价依赖于列的数量！

2）太多关联：经验法则，单个查询尽量在12个表以内做关联

3）过多使用枚举：枚举字段通常按内部存储的整数进行排序，例如在使用数字作为枚举类型常量，有可能产生双重性，导致混乱；

最不好的就是枚举列表是固定的，每次进行添加或删除操作时，都需要动用ALTER TABLE，增加开销。

4）变相的枚举

枚举(ENUM)列允许在列中存储一组定义值中的单个值， 集合(SE T)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱.

4.3 范式与反范式

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

范式的优点和缺点：

优点：

范式化的更新操作通常比反范式化要快

当数据较好地范式化之后，只有更少或没有重复数据，这使得修改更少的数据

范式化的表通常表更小，可以更好地放在内存中，所以执行操作会更快

缺点：

通常需要关联查询，在索引中又排序又过滤，不仅代价昂贵，也可能使一些索引策略无效

反范式的优点和缺点：

优点：

反范式的schema对象是因为所有数据都在一张表中，因此很好的避免关联，更有效的使用索引策略

缺点：

范式的优点，就是反范式的缺点

4.4 缓存表与汇总表

我们用术语“缓存表” 来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢） 数据的表。

术语“汇总表”时，则保存的是使用GROUP BY语句聚合数据的表

4.5 加快ALTER TABLE 操作的速度

MySQL大多数修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这种操作比较耗时。

技巧一：只修改.frm文件

基本的技术是为想要的表结构创建一个新的.frm文件，然后替换掉已经存在的.frm文件：

1）创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。
2）执行FLUSH TABLES WITH READ LOCK。将会关闭所有正在使用的表，并且禁止任何表被打开。
3）交换.frm文件。
4）执行UNLOCK TABLES来释放第2步的读锁。

技巧二：快速创建MyISAM索引

先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。

4.6 总结

• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。

• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用 NULL 值。

• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。

• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。

• 尽扯使用整型定义标识列。

• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。

• 小心使用 ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。 最好避免使用 BIT。