高性能MySQL第四章：Schema与数据类型优化zhangyunfeihhhh的博客-

良好的逻辑设计和物理设计是高性能的基石，前期的表结构设计质量，会直接影响基于表结构的业务SQL性能，所以，表结构的设计质量，直接关系到系统的运行情况，甚至是系统的用户体验，本章，主要对MySQL支持的数据进行讲解，说明数据类型的优缺点及适用场景

一、选择数据类型的基本原则

1. 更小的通常最好

一般情况下，应该尽量选择可以正确存储数据的最小数据类型，因为它们占用的磁盘、内存和CPU缓存更少，并且调用CPU周期也更短，但是如果由于前期选择的数据类型支持的范围不足以支持业务数据，需要修改数据类型以增加数据范围，这将需要一个非常耗时和痛苦的操作，所有在设计数据类型时，无法确认哪个数据类型是最好的，就选择你觉得不会超过范围的最小类型。

2. 简单就好

简单的数据类型的操作通常需要更少的CPU周期，例如整形比字符串代价更低、使用内建类型（datetime）而不是字符串来保持时间

3. 尽量避免NULL

null是MySQL数据列的默认属性，但是通常情况下，最好知道列为NOT NULL，除非系统真的需要保持NULL值，查询中包含NULL的列，对MySQL来说，更难优化，因为NULL会使得所有、索引统计更为复杂

二、数据类型

在为列选择数据类型时，第一步需要确定合适的大致类型，下一步就是选择具体的类型，有些数据，有几种数据类型都支持存储，只是它们的存储范围、精度不一样，这就需要我们根据业务来选择更加合适的类型，下面将对MySQL支持的数据类型进行介绍

1.整数类型

数据类型：
  1.TINYINT 8位
  2.SMALLINT16位
  3.MEDIUMINT24位
  4.INT32位
  5.BIGINT64位

特性：

1. UNSIGNED属性，表示不允许有负值，设置UNSIGNED会是整数的上限提高一倍，除此以外，相比于不设置，没有其他任何区别，包括存储空间和性能

tip：整数可以指定宽度，但是幷不能限制数据的范围，它只是规定了在交互工具上显式的字符个数，对于存储来说，INT(1)和INT（20）没有区别

2.实数整形

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

特性：
  1. FLOAT 和DOUBLE 在存储相同范围的值时，比DECIMAL使用空间更少
  2. DOUBLE（8字节）比FLOAT（4字节）有更高的精度和更大的范围
  3. DECIMAL支持对小数进行精确计算，但是需要额外的空间和计算开销，因为CPU不支持对DECIMAL的直接计算，需要MySQL内部自身的实现

tip：可以通过对要存储的高精度小数据数据乘以相应的倍数后用bigint来进行存储，因为bigint开销更，当然，只有在数据量比较大的时候才需要考虑这样做。

3.字符串类型

数据类型：
  1.CHAR 用于存储固定长度的字符串类型
  2. VARCHAR 用于存储不定长的字符串数据类型
  3. TEXT用于存储很大的数据而设计的字符串类型
  4.BINARY和CHAR 对应的二进制字符串
  5. VABINARY 和VARCHAR 对应的二进制字符串
  6.BLOB和TEXT对应的二进制字符串

特性：

1. VARCHAR 比CHAR 更节省空间，因为它只使用必要的空间
2. VARCHAR 由于变长，在UODATE时可能使行变得比原来长，可能会导致分裂页的操作
3. CHAR总是根据定义的长度分配对应的空间，不论数据实际长度是否比定义的短
4. CHAR会删除所有的末尾空格而最近版本的VARCHAR 不会
5. 定长的CHAR更不容易产生碎片
6. BINARY和VARCHAR 存储的是二进制，比较时，每次按一个字节进行比较，对大小写敏感，且更快
7. BLOB和TEXT在存储大值时，会使用专门的存储空间进行存储，而行数据存储的是指向实际存储空间的地址
8. BLOB和TEXT只对列最前面的max_sort_length字节进行排序

tip：1. 当使用VARCHAR 时，如果表使用ROW_FORMAT = FIXED,每一行都会使用定长存储，这回很浪费空间
9. VARCHAR (5)和VARCHAR （200）存储hello使用的空间开销是一样的，但是VARCHAR （200）会消耗更多的内存，因为MySQL会分配固定大小的内存来保持内部值，尤其是使用内存临时表进行操作时会很糟糕，所有尽量只分配真正需要的空间

4.日期和时间类型

数据类型：
  1.DATETIME 用于存储1001年到9999年的时间，精度为秒
  2. TIMESTAMP用于存储1970年1月1日午夜以来的秒数

特性：

1. TIMESTAMP与时区有关，它的显式会依赖时区设置，而DATETIME 不会
2. TIMESTAMP默认为NOT NULL
3. TIMESTAMP在更新或者插入时，如果未指定时间，会自动设置为当前时间
4. TIMESTAMP比DATETIME 的空间效率更高
   tip：TIMESTAMP最远只能表示到2038年的时间

5.位数据类型

数据类型：
  1.BIT用于在一个列存储多个true/false值
  2. SET用于合并存储很多的truefalse

6.特殊类型数据inte

IPv4地址，可以使用无符号整数存储IP地址，MySQL提供inet_ATON()和INET_NTOA()来转化ip地址

三、MySQL schema设计中的陷阱

1.太多的列

MySQL的存储引擎API在工作时，需要在服务器层和存储引擎层之间进行缓冲格式拷贝数据，然后在服务器层将缓存内容解码成各个列，而转换的代价依赖列的数量。

2.太多的关联

大多的关联，会导致解析和优化查询的代价成为问题，单个查询，最好在12个表以内做关联

3.全能的枚举

使用枚举，当需要在枚举列表中增加一个新的值时，需要做一次ALTER TABLE操作，这是全表的阻塞操作，操作起来会很麻烦

4.非此发明的NULL

避免使用NULL会带来很多好处，而且即使需要存储null值，也可以考虑使用其他的特殊值来代替，但是也不能走极端，当使用其他替代方案会导致不确定性，或者bug时，也许null幷不是不能接受的，而且MySQL会在索引中存储null的值，只是处理起来相比于not null 更复杂一点

三、范式和反范式

1.范式的优点和缺点

• 范式化的更新操作通常比反范式化要快
• 当数据更好的范式化时，就只有很少或者没有重复的数据，所以只需要修改很少的数据
• 范式化的表通常更小，可以更好的放到内存里，所以操作会更快
• 检索列表时，更少的需要使用DISTINCT或者GROUP BY语句

范式化设计的schema通常的缺点是需要关联，稍微复杂一点的语句在符合范式的schema上都可能需要至少一次关联

2.反范式的优点和缺点

• 避免过多的关联
• 更有效的索引策略

范式化和反范式化各有优缺点，在正常的情况下，我们应综合业务需要，混合使用二者，所谓，黑猫白猫，能抓老鼠的才是好猫。

四、缓存表和汇总表

有时，提升性能最好的办法是在同一张表中保存衍生的冗余数据，然而有时候也需要创建一张完全独立的汇总表和缓存表，汇总表，指的是对某些数据进行聚合而产生具有统计功能的表，缓存表用来存储那些可以比较简单获取但获取速度比较慢的数据。

汇总表
使用汇总表，相比于实时进行汇总计算，高效了很多，因为实时计算总是需要扫描大部分的数据，或者索引要求很高，但是有一个问题就是汇总表总是在使用前就多某一段的数据进行了汇总，导致实时性不高，但有时候也有一个折中的办法，就是一段时间的数据使用汇总表，而当前一小段的数据使用实时数据进行计算。

缓存表
有时，我们需要很多不同的的索引组合来加速各种类型的查询，这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表

1.物化视图

许多数据库管理系统都提供了一个叫物化视图的功能，物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新，MySQL并不支持物化视图，然而我们可以使用Jsitin swanhart的开源工具Flexviews，对比传统的维护汇总表和缓存表的方法，Flexviews通过提取对源表的更改，可以增量的重新计算物化视图的内容。

2.计数器表

计数器表最大的问题就是会遇到更新并发的问题，当多个线程对计数字段进行更新时，因为互斥的关系，这些更新需要串行化的执行，这会严重影响性能，这里有个和好的技巧可供参开，而且这个思路也可以在其他地方获益

比如，你要记录一个网站的点击次数，你可能会建一张表

create table hit_count(cnt int unsigned not null) engine = innoDB; 

每点击一次，就执行如下sql:

update hit_count set cnt -= cnt+1; 

但是当许多人点击时，这些事务只能串行的执行，这时，可以考虑如下修改:

create table hit_count(  slot tinyint unsigned not null primary key,  cnt int unsigned not null ) engine = innoDB; 

这时，在进行更新时，就可以随机对一行数据进行更新

update hit_count set cnt  = cnt+1 where slot = RAND()*100; 

当要获得统计结果时，需要使用下面这样的聚合查询

select sum(cnt) from hit_counter; 

更进一步，每隔一段时间开始一个新的计数器，可以做如下修改：

create table daily_hit_counter(  day date not null,  slot tinyint unsigned not null,  cnt int unsigned not null,  primary key(day, slot) )engine=InnoDB; 

更新则可使用如下语句

insert into daily_hit_counter(dat, slot, cnt)  values (current_date, rand()*100,1)  on duplicate key update cnt = cnt+1; 

五、加快ALTER TABLE 操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题，MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出素有数据插入新表，然后删除旧表，这样的操作可能需要花费很长的时间，如果内存不足而表又很大，而且需要很多索引的情况下尤其如此。

对于常见的场景，能使用的技巧有两种：
一、先在一台不提供服务的机器上执行ALTER TABLE 操作，然后和提供服务的主库进行切换。
二、使用影子拷贝，创建一张和源表结构一样的新表，然后通过重命名的和删表操作交换两张表

理论上，MYSQL可以跳过创建新表的步骤，列的默认值实际上存在表的.frm中，可以直接修改这个文件而不需要改动表本身，然而mysql还没有采取这种优化的方法，所以ALTER TABLE 操作都将导致表重建
另外一种方法是通过ALTER COLUMN操作来改变列的默认值：

ALTER TABLE sakila.film ALTER COLUMN rental_duration SET DEFAULT 5 

这个语句会直接修改.frm而不涉及表数据

tip：ALTER TABLE 允许使用ALTER COLUMN 、MODIFY COLUMN 、CHANGE COLUMN 语句修改列

只修改.frm文件

有时候mysql会在没有必要的时候重建表，如果愿意冒一点风险，可以让mysql做一些其他类型的修改而不用重建表。

• 移除一个列的AUTO_INCREMENT属性

1. 创建一张相同结构的空表，幷进行所需要的的修改
2. 执行FLUSH TABLE WITH READ LOCK,这将会关闭所有正在使用的表，并且禁止任何表被打开
3. 交换.frm文件
4. 4.执行UNLOCK TABLES 释放锁。

快速创建索引

1. 用需要的表结构创建一张表，但不包括索引
2. 载入数据到表中以创建.MYD
3. 按照需要的结构创建另一张空表，这次要包含索引，这会创建需要的.frm和.MYI文件
4. 获取读锁幷刷新表
5. 重命名第二张表的.frm和MYI,让MySQL认为是第一张表的文件。
6. 释放锁
7. 使用REPARE TABLE来重新建表的索引，该操作会通过排序来构件所有的索引，包括唯一索引

总结

良好的schema设计原则是普遍适用的，但是MySQL有他自己的实现细节要注意，概况来讲，尽可能保持任何东西小而简单总是好的。有以下简单的原则值得你去考虑使用

• 尽量避免过度设计
• 使用小而简单的合适数据类型，尽可能避免使用null
• 尽量使用相同的数据类型存储相似或者相关的值
• 注意可变长字符串，其在临时表和排序时可能按最大长度分配内存
• 尽量使用整形定义标识符