选择优化的数据类型

1、选择优化的数据类型

简单的几个原则：

• 更小的通常更好：尽量使用可以正确存储数据的最小数据类型，因为他们占用更小的磁盘、内存和 cpu缓存
• 简单就好：使用简单的数据类型，通常需要较少的 CPU 周期
  • 应该使用 MySQL内建的数据类型来存储时间和日期，而不是字符串
  • 应该用整型存储 IP 地址
• 尽量避免 NULL：如果查询中包含可为 NULL 的列，对 MySQL 来说可能更难优化，
  • 因为可为 NULL 的列使得索引、索引统计和值比较都更为复杂。
  • 可为 NULL 的列会使用更多的存储空间，在 MySQL 中也要特殊处理。
  • 在索引时，每个索引记录需要一个额外的字节。
  • 通常把可为 NULL的列改为 NOT NULL 带来的性能提升比较小。
  • 如果计算在这列上建索引，那么在设计的时候就要避免设计成可以 NULL 的列。

在选择数据类型时

• 第一步是确定合适的大类型：数字、字符串、时间等
• 第二步是选择具体类型

数字类型

1、整数类型 ：

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

分别使用8，16，24，32，64位存储空间，值范围 -2^(N-1) 到 2^(N-1)-1 ，N 是位数

• UNSINGED 属性可以使正数上限提高一倍，TINYINT-128-127，TINYINT UNSIGNED0-255
• 无符号和有符号的类型使用相同的存储空间和性能
• MySQL 内部整数计算一般选择64位的 BIGINT，你选择的只是存储类型
• MySQL 可以为整数类型指定宽度，但是只是规定了一些交互工具的显示字符个数，对于存储和计算没有区别

2、实数类型：实数一方面是为了存储小数，一方面是为了存储比整数更大的值

FLOAT, DOUBLE, DECIMAL

• FLOAT 和 DOUBLE 支持标点的浮点运算进行近似计算
• DECIMAL 用于存储精确的小数，现在最多65个数字
• 浮点数 FLOAT 存储需要4个字节，DOUBLE 占用8个字节，MySQL 内部浮点计算一般使用 DOUBLE
• 浮点数和 DECIMAL 都可以指定精度，DECIMAL(18,9)表示小数点两边各存储9个数字，一共使用9个字节，小数使用4个字节，整数使用4个字节，小数点使用1个字节
• CPU 不支持 DECIMAL 的直接计算，而支持浮点数的直接计算，所以浮点数计算明显更快。MySQL 内部实现了 DECIMAL 的高精度计算
• DECIMAL 需要额外的空间和计算开销，所以只在需要对小数进行精确计算的时候才用
• 在数据量比较大的时候，可以考虑用 BIGINT 代替 DECIMAL，这样可以避免浮点计算不准确和 DECIMAL 精确计算代价高的问题

字符串类型

1、VARCHAR：用于存储可变长字符串，比定长类型节省空间

• 需要使用额外的1-2个字节记录字符串长度，255以内1个字节，超过255两个字节
• 如果行变长的时候，页内没有更多空间存储，那么 InnoDB 会分裂页来使行可以放进页内
• 当字符串最大长度比平均长度大很多，列的更新很少，或者使用了 UTF-8时，适合 VARCHAR
• InnoDB 会把过长的VARCHAR 存储为 BLOB

2、CHAR：定长的

• CHAR 适合存储很短的字符串，或者所有值都接近一个长度。比如密码的 MD5值
• 因为不容易产生碎片，所以也比 VARCHAR 适合存储经常变的值
• 对于非常短的列，比 VARCHAR 更有效率
• 当存储 CHAR时，MySQL 会删除所有的末尾空格
• 字符串长度定义是字符数，不是字节数，多字节字符集会需要更多的空间存储单个字符

二进制类型：BINARY 和 VARBINARY

• 存储二进制字符串，规则和常规字符串相似
• 存储的是字节码，而不是字符
• MySQL 填充 BINARY 采用的是\0零字节，在检索时也不会去掉
• 二进制比较是按每次一个字节进行比较的，速度比字符更快

最好的策略是只分配真正需要的空间，MySQL 通常会分配固定大小的内存块来保存内部值。

所以更长的列会消耗更多的内存，在使用内存临时表进行排序或操作时会特别糟糕。利用磁盘临时表也同样。

BLOB和 TEXT 类型

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

字符：TINYTEXT, SMALLTEXTTEXT , MEDIUMTEXT, LONGTEXT

二进制：TINYBLOB, SMALLBLOBBLOB, MEDIUMBLOB, LONGBLOB

• 当 BLOB 和 TEXT 太大时，InnoDB 会使用专门的外部存储区域来进行存储，此时每个值在行内需要1—4个字节存储一个指针，在外部存储区域存储实际值
• BLOB 没有排序规则和字符集
• MySQL 只对 BLOB 和 TEXT 的每个列最前 max_sort_length字节进行排序
• MySQL 不能将 BLOB 和 TEXT 的全部长度的字符串进行索引，也不能使用这些索引消除排序

使用枚举（ENUM） 代替字符串类型

• 枚举可以把一些不重复的字符串存储成一个预定义的集合

• MySQL 在存储枚举时非常紧凑，会根据列表值数量压缩到一个或者两个字节中

• MySQL 会在内部将每个值在列表中的位置保存为整数，并在表的.fm文件中保存“数字—字符串”映射关系的 “查找表”
• 枚举字段是按照内部存储的 id 值进行排序的
• 修改枚举字符串，需要用到Alter table
• ENUM 关联查询会比较快，关联 VARCHAR 会比较慢
• 转换成 ENUM 可以减少表的大小甚至主键大小

日期和时间类型

1、DATETIME

• 这个类能保存1001年到9999年，精度为 seconds
• 格式是 YYYYMMDDHHMMSS，需要8个字节的存储空间

2、TIMESTAMP

• 保存了从1970年1月1日以来的秒数，和 UNIX 时间戳相同
• 使用4个字节的存储空间，范围是1970-2038年
• 和时区，MySQL 服务器以及操作系统有关
• 默认插入没有指定第一个 TIMESTAMP 的值的时候，MySQL 会设置这个列的值为当前时间
• 默认 NOT NULL

• 应该尽量用 TIMESTAMP，因为它比 DATETIME 空间效率更高
• 需要更小粒度的时间的话，可以用 BIGINT 存储时间戳

位数据类型

1、BIT

• 可以使用 BIT 存储 一个或者多个 true/false 值
• InnoDB 会为每个 BIT 列使用一个足够小的整数类型来存放
• MySQL 把 BIT 当成字符串，而不是数字。检索 BIT(1)的值，结果是一个包含二进制0或1的字符串
• 可以使用 CHAR(0) 来代替BIT存储 true/false 值

2、SET

• 如果需要保存很多 true/false 值，可以考虑合并这些列到一个 SET
• 在 MySQL 内部是以一系列打包的位的集合来表示的，这样就有效利用了存储空间
• 缺点是改变列需要 ALTER TABLE，无法在 SET 列上通过索引查找
• 可以用 TINYINT包装一系列的位来替代 SET，好处是可以不改表结构，坏处是 sql 复杂

选择标识符 主键序列

• 为标识列选择数据类型时，应该选择跟关联表中对应列一样的类型
• 选择数据类型时，不仅要考虑存储类型，还要考虑 MySQL 内部是怎么计算和比较的
• 要确保在所有的关联表中都使用同样的类型，不同的类型可能导致性能问题，和比较时隐式类型转换的问题
• 在满足需求的情况下，应该选择最小的数据类型
• 整数是标识列最好的选择， 因为快速而且可以自增
• 字符串作为标识列会很消耗空间，而且比数字类型慢
• 完全随机的字符串也会导致 INSERT 和 SELECT 变得很慢

特殊数据类型

• IPv4地址，实际上是32位无符号数，应该用无符号整数存储

2、MySQL schema 设计中的陷阱

1、太多的列

• 如果一张表有数千个字段，但实际只有一小部分会用到的时候，转换的代价就很高
• 因为 MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后再服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。

2、太多的关联

• MySQL 限制了每个关联操作最多只能有61张表
• 最好在12个表以内做关联

3、全能的枚举

• 应该用整数做外键关联到字典表或者查找表来代替枚举

4、变相的枚举

• 如果 true、false 不会同时出现，应该高绿用枚举列代替 set

5、NOT INVENT HERE

• 避免使用 NULL，可以用0、空字符串或者特殊值来代替 NULL
• 当用其他值代替会更复杂时，请用 NULL

3、范式和反范式

MySQL 的设计三范式：

第一范式:确保每列的原子性(强调的是列的原子性，即列不能够再分成其他几列).
    如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
    例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。


第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关(一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的部分)
    如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
    例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。

第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖).
    如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
    为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，
依赖A-〉C是传递依赖。
    例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客
编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。

1、范式

• 更新操作快，需要改动的数据少，体量更小，执行操作更快
• 复杂的语句需要关联操作，代价昂贵而且会使索引失效

2、反范式

• 所有数据都在一张表，可以避免关联，避免随机 IO，可以使用有效的索引策略

3、实际中使用混用范式化和反范式化

4、缓存表和汇总表

缓存表：表示存储那些可以简单的从 schema 其他表获取数据，但是获取速度较慢的表

汇总表：保存的是使用 GROUP BY语句聚合数据的表

• 汇总表比计算所有行扫描全表要快的多

• 缓存表对优化搜索和检索查询语句很有效

• 影子表是指一张在真实表背后创建的表，可以通过一个原子的重命名操作切换影子表和原表

  1、物化视图

物化视图：预先计算并存储在磁盘上的表，可以通过各种各样的策略刷新和更新

• MySQL 原生并不支持，可以用开源工具Flexviews 实现物化视图
• Flexviews 通过提取对源表的更改，可以增量的重新计算物化视图的内容，不需要通过查询原始数据来更新视图

2、计数器表

• 要获得更高的并发更新性能，可以将计数器保存在多行中，每次随机选择一行进行更新
• 各种情况下的示例

5、加快 ALTER TABLE 操作的速度

MySQL 执行大部分修改表结构操作的方法是用心的结构创建一个空表，然后将旧表中的数据插入到新表，然后删除旧表。

MySQL5.1以上的版本在修改过程中不需要锁表。

大部分 ALTER 操作会导致 MySQL 服务中断。有一些技巧可以优化：

• 在不提供服务的机器上进行 ALTER TABLE，然后和主服务器进行切换
• 另一种是影子拷贝，重新创建一张和原表无关的新表，然后通过重命名和删表交换两张表
• ALTER COLUMN 操作直接修改.frm文件而不涉及表数据，所以速度很快

只修改.frm文件: 为想要的表结构创建一个新的.frm文件，然后用他替换掉已经存在的.frm文件

• 移除 AUTO_INCREMENT和增删改 ENUM 和 SET 可以通过替换.frm文件来实现，从而不用重新建表