MySQL之 Sechma与数据类型优化

1 选择优化的数据类型

选择正确数据类型的原则：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

• 更小的通常更好
• 简单就好
• 尽量避免NULL
  • 通常把可为NULL的列改为NOT NULL带来的提升比较小，调优时没必要进行修改。除非计划在列上建立索引，就应该尽量避免设计成NULL。
  • 例外：
    • InnoDB使用单独的位bit存储NULL值，对与稀疏数据有很好的空间效率。
  • 缺点：
    • 查询包含NULL的列使MySQL更难优化，NULL使索引、索引统计和值比较都更复杂。
    • 可为NULL的列会使用更多的存储空间。

1.1 整数类型

• TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。
• 可选的UNSIGNED属性，不允许出现负值，可以使正值存储范围扩大一倍。
• 整数计算一般使用BIGINT整数
• 可以指定宽度，但不会限制类型的合法范围，只用来显示字符的个数。

1.2 实数类型

• 带有小数部分的数字，也用DECIMAL存储比BIGINT还大的整数
• 支持精准及不精准类型
• FLOAT(4个字节)和DOUBLE(8个字节)支持标准的浮点运算进行近似计算，支持指定精度；DEMICAL存储精确的小数，支持精确计算和指定精度，并指定小数点前后所允许的最大位数，允许最多65个数字
• CPU直接支持原生浮点计算，MySQL服务器层自身实现了对DEMICAL的高精度计算
• 内部浮点计算使用DOUBLE

1.3 字符串类型

• VARCHAR和CHAR类型：存储字符
  • VARCHAR
    • 存储可变长字符串，比定长类型更节省空间
    • 需使用1个(如列的最大长度小于等于255字节)或2个额外字节记录字符串长度。
    • 如果UPDATE的行变长，而且页内没有更多的存储空间，MyISAM会将行拆成不同的存储片段，InnoDB则需要分裂页。
    • 使用场景：

      字符串列的最大长度比平均长度大很多；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

      列的更新很少，碎片不是问题；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

      使用了像UTF-8等复杂的字符集，每个字符使用不同的字节数进行存储。

    • 5.0及以后版本，存储和检索时会保留末尾空格
    • InnoDB会把过长的VARCHAR存储为BLOB
    • VARCHAR(5)和VARCHAR(200)存储‘hello’，MySQL会分配固定大小的内存块来保存内部值，所以后者会消耗更多的内存。尤其使用内存临时表或磁盘临时表进行排序或操作会特别糟糕。因此，只分配真正需要的空间.
  • CHAR：
    • 定长，MySQL根据定义的字符串长度分配足够的空间。
    • MySQL会删除末尾空格；会根据需要采用空格进行填充以方便比较。
    • 适合存储很短的字节，或者所有的值都接近同一个长度。例如：存储密码的MD5值；经常变更的数据，定长的CHAR比VARCHAR不容易产生碎片；非常短的列，CHAR比VARCHAR在存储空间更有效率，可用CHAR(1)来存储只有Y和N的值。
• BINARY和VARBINARY：存储二进制字符串，存储字节码而不是字符；二进制数据用字节码比较会简单，效率高.
• BLOB和TEXT类型：
  • BLOB和TEXT为存储很大的数据而设计，分别采用二进制(没有排序规则和字符集)和字符(有排序规则和字符集)方式存储。
  • TEXT家族：TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；BOLB家族：TINYBOLB，SMALLBOLB，BOLB，MEDIUMBLOB，LONGBOLB。
  • 与其它类型不同，MySQL把每个BLOB和TEXT当作一个独立的对象处理，存储引擎在存储时做特殊处理。如值太大，InnoDB会使用专门的“外部”存储区进行存储，每个值需要1~4个字节存储一个指针，用于指向存储在外部区域的值
  • 排序与其他类型不同，只对每个列最前面的max_sort_length(可配置)字节而不是整个字符串排序，也可使用ORDER BY SUSTRING(column, length)
  • MySQL不支持全部长度的字符串进行索引，也不能使用这些所以消除排序。
  • Memory引擎不支持这两中类型，如果查询使用到了且需要隐式临时表，将会使用MyISAM磁盘临时表。这会导致严重的性能开销。因此因尽量避免使用这两种类型，或者在查询的时候使用SUBSTRING函数，但不能使临时表的大小超过max_heap_table_sizs或tmp_table_size(对内存和磁盘创建大临时表和文件排序也适用)。
• 使用枚举(ENUM)代替字符串类型
  • 枚举列把一些不重复的字符串存储成一个预定义的集合。
  • MySQL存储枚举非常紧凑，会根据列表值的数量压缩到一个或两个字节中；在内部将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
  • ENUM所在的行存储的是整数而不是字符串，可通过数字上下文环境检索看到这个双重属性。

    SELECT column + 0 FROM enum_table

  • 尽量避免用数字作为ENUM枚举常量。
  • 枚举字段按内部存储的整数而不是定义的字符串进行排序。绕过此机制方式：
    • 按照需要的顺序来定义枚举列
    • 查询中使用FIELD()函数显式制定排序顺序，但会导致MySQL无法利用索引消除排序。
  • 缺点：
    • 字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此对一系列未来会改变的字符串，不建议使用枚举，除非能接受在列表末尾添加元素(5.1后版本不用重建整个表)。
    • 由于枚举值是整数，必须经过查找才能转成字符串，所以有一些开销。
    • 特定情况下，把CHAR/VARCHAR列与ENUM列进行关联会比直接管理CHAR/VARCHAR列更慢。ENUM与ENUM关联最快。因此，尽量采用整数主键而避免采用基于字符串的值进行关联。
  • 优点：
    • ENUM列互相关联查询速度快
    • 占用空间较小，如果ENUM是主键，也会减少相应空间，让其他非主键索引变得更小。

1.4 日期和时间类型

尽量使用TIMESTAMP，比DATETIME空间效率更高；不建议将UNIX时间戳保存为整数，因为不方便处理也不会带来任何收益文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

存储比秒更小粒度的时间：使用BIGINT存储微秒级时间戳；使用DOUBLE存储秒之后的小数部分文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

• DATETIME
  • 保存大范围的值，从1001~9999年，精度为秒。格式为YYYYMMDDHHMMSS的整数中，与时区无关；使用8个字节的存储空间。
  • 默认使用一种可排序的、无歧义的ANSI标准定义格式显示，如"2017-06-29 17:42:05"
• TIMESTAMP
  • 保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，与UNIX时间戳一致；只是用4个字节存储空间，因此范围在1970-2038
  • MySQL提供FROM_UNIXTIME()和UNIX_TIMESTAMP()函数互相转换日期和UNIX时间戳
  • 显示的值依赖于时区
  • 默认为NULL，插入时没有制定第一个TIMESTAMP列的值，则设置该值为当前时间。

1.5 位数据类型

不管底层存储格式和处理方式如何，从技术上来说都是字符串类型文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

• BIT：
  • 使用BIT列在一列中存储一个或多个true/false值。
  • BIT(N)定义一个包含N个位的字段，最大长度是64个位
  • MyISAM会打包存储所有的BIT列，N个单独的BIT列只需要N个位存储(假设没有NULL)，可节省存储空间；其他存储引擎如Memory和InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，不能节省存储空间
  • MySQL把BIT当作字符串类型，但在数字上下文环境检索，会将字符串转为数字

  mysql> CREATE TABLE bittest(a bit(8));
  mysql> INSERT INTO bittest VALUES(b'00111001');
  mysql> SELECT a, a + 0 FROM bittest;
  +------+-------+
  | a    | a + 0 |
  +------+-------+
  | 9    |    57 |
  +------+-------+
  -- ASCII码57为字符"9"

  • 谨慎使用BIT类型
  • 如果想在一个bit的存储空间存储一个true/false值，可以创建一个可以为空的CHAR(0)列，该列可以通过保存空值(NULL)或者长度为零的字符(空字符串)
• SET:
  • 以一系列打包的位的集合，可用来保存很多true/false值
  • 有效利用存储空间，并且有FIND_IN_SET()和FILED()这样的函数
  • 改变列的代价太高，需要ALTER TABLE，这对大表来说是非常昂贵的操作
  • 无法在SET列上通过索引查找
• 在整数列上进行按位操作：
  • 使用一个整数包装一系列的的位来代替SET，比如把8个位包装到一个TINYINT，并按位操作来使用，可以在应用中为每个位定义名称常量来简化这个工作。
  • 好处是可以不使用ALTER TABLE，缺点是查询语句更难写更难理解

1.6 选择标识符(identifier)

• 为标识类选择合适的数据类型的重要性：
  • 标识列可能与其他值进行比较(在关联操作中)
  • 或通过标识列寻找其他列
  • 可能在另外的表作为外键使用，所以应该选择与关联表对应列一样的数据类型，而且需要精准匹配，包括像UNSIGNED这样的属性
• 考虑因素：
  • 存储类型
  • MySQL对这种存储类型怎么执行继续和比较
  • 满足值的范围需求，并且预留未来增长空间的前提下，选择最小的数据类型。
• 选择技巧：
  • 整数类型：
    • 标识列最好的选择，速度快，可以使用AUTO_INCREMENT
  • ENUM和SET类型：
    • 通常是一个糟糕的选择，大部分情况下应避免。适合某些只包含固定状态或者类型的静态“定义表”。
  • 字符串类型：

    避免使用，因为很消耗空间，通常比数字类型慢。尤其在MyISAM中，默认对字符串使用压缩索引，导致查询慢得多。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

    注意完全“随机”的字符串，如MD5()，SHA1()或者UUID()产生的字符串，这些值会任意分布在很大的空间内，导致INSERT和一些SELECT语句变慢：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

    • 插入值会随机地写到索引不同的位置，这会导致页分裂，磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片，使得INSERT语句变慢；
    • 逻辑上相邻的行会分布在磁盘和内存的不同地方，导致SELECT查询变得更慢；
    • 导致缓存对所有类型的查询语句效果都很差，因为会使缓存赖以工作的访问局部性原理失效，即如果整个数据集一样”热“，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存会有很多刷新和不命中。

    如果存储UUID值(虽然分布也不均匀，但还是有一定顺序)，应该移除"-"符号，或者用UNHEX()函数转换为一个16字节的数字，存储在BINARY(16)中，而检索可以通过HEX()函数来格式化为16进制格式

• 当心自动生成的schema：
  • 写得很烂的schema迁移程序，或者自动生成schema的程序，都会导致严重的性能问题
  • 对象关系映射(ORM)系统以及使用它们的框架，也可能会导致性能问题。严格注意它们的映射，避免存储任意类型的数据到任意类型的后端数据存储中

1.7 特殊数据类型

某些类型的数据并不直接与内置类型一致。如低于秒级精度的时间戳，前文已介绍；如IPv4地址，人们经常用VARCHAR(15)列来存储IP地址，然而它实际上是32位无符号整数而不是字符串，小数点的分割只是方便阅读，因此应该用无符号整数来存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法转换。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

2. MySQL schema设计中的陷阱

• 太多的列
  • 原因：MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲的形式拷贝数据，然后在服务器层将缓冲内容解码成各个列。而从行缓冲中将编码过的列转换成行数据结构的操作代价非常高。而像MyISAM的变长行结构(定长行结构与服务器层的行结构正好匹配)和InooDB的行结构总是需要转换，转换的代价依赖于列的数量。
• 太多的关联：
  • ”实体Entity——属性Attribute——值Value“(EVA)是一个常见的(糟糕的？？？)的设计模型，因为它可能需要很多自关联。而MySQL的每个关联操作最多只能有61张表，而且实际上在小于这个数量的情况下，也有解析和优化查询的代价。
  • 单个查询最好在12个表内做关联，从而提升查询执行速度和良好的并发性。
• 全能的枚举
  • 防止过度使用枚举
  • 如果在枚举列新添一个元素，如果不是在末尾增加值，会需要ALTER TABLE，导致性能问题。
• 变相的枚举
  • 枚举列允许在列中存储一组定义值中的单个值，集合(SET)列则允许在列中存储一组定义中的一个或多个值。如果不会有多个值同时出现的场景，不要用集合(SET)。
• 非此发明(Not Invent Here)的NULL
  • 尽量避免使用Null，尽可能考虑替代方案。
  • 确实需要表示未知值时，不要害怕使用NULL。

    CREATE TABLE ...(
      dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
    )
    -- 伪造的全0值可能会导致很多问题

  • MySQL会在索引中存储NULL值，Oracle则不会。

3. 范式和反范式

• 范式：
  • 第一范式：确保每列保持原子性，每列都是不可分割的。
  • 第二范式：确保表中的每列都和主键相关，而不能只与主键的某一部分相关(针对联合主键而言)。
  • 第三范式：确保每列都和主键直接关联，而不是间接关联。
• 范式的优点缺点：
  • 优点：
    • 更新操作比反范式快
    • 数据较好地范式化时，就只有很少或没有重复的数据，所以只需要修改更少的数据。
    • 范式化的表通常更小，可以更好的存放在内存里，执行操作更快。
    • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者group by语句。
  • 缺点:
    • 需要关联，可能导致代价高昂，使一些索引策略无效。
• 反范式的优点缺点：
  • 优点：
    • 避免关联。对大部分查询最坏的情况——表没有使用索引，是全表扫描时，当数据比内存大时可能比关联快得多，因为这样避免了随机I/O。(PS:全表扫描基本上是顺序I/O)
    • 可以使用更有效的索引策略
• 混用范式化和反范式化
  • 方法：复制或缓存，在不同的表中存储相同的特定列。在5.0及以后的版本，可以使用触发器更新缓存值。
  • 好处：
    • 避免了完成反范式化的插入或删除问题
    • 可以高效的排序。
    • 可以缓存衍生值。如有一个user表和message表，可在user表建num_messages显示用户发了多少信息。

4. 缓存表和汇总表

• 累积表(Roll-Up Table):
  • 缓存表：
    • 存储可以比较简单地从schema其他表获取(但是每次获取的速度比较慢)数据的表，例如逻辑上冗余的数据。
    • 用于优化搜索和检索查询语句。这些查询语句经常需要特殊的表结构和索引结构，跟普通OLTP操作用的表有一定区别。
  • 汇总表：
    • 保存的是使用GROUP BY语句聚合数据的表，例如，数据不是逻辑上冗余的。
    • 避免实时计算统计值的昂贵操作，因为这需要扫描表中的大部分数据，或是只能在某些特定索引上才能有效运行，而这类特定索引一般对update操作有影响。
• 应用场景：
  • 需要很多不同的索引组合来加速各种类型的查询，这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。可对缓存表使用不同的存储引擎，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文索引。
• 使用方式：
  • 实时维护数据
  • 定期重建，节省资源，保持表不会有很多碎片，并且有完全的顺序组织索引。
• 更快地读，更慢地写
  • 为了提升查询速度，经常需要创建一些额外的索引，增加冗余列，甚至是创建缓存表和汇总表，这些会增加查询的负担，也需要额外的维护任务，甚至增加开发难度。但是显著地提高了读操作性能。

4.1 物化视图

MySQL并不原生支持，可使用开源工具Flexviews。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

4.2 计数器表

在web应用中，可能会有个计数器表用于缓存一个用户的朋友数或文件下载次数。可以创建一个独立的计数器表，使得表小且快。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

CREATE TABLE hit_counter(
  cnt int unsigned not null
)ENGINE=InnoDB;
UPDATE hit_counter SET cnt = cnt + 1;
-- 如果有多个事务要更新，这条记录上有一个全局的互斥锁，使得事务串行执行。因此想要获得更高的并发性能，可以将计数器保存在多行，每次随机选择一行执行。
CREATE TABLE hit_counter(
  slot tinyint unsigned not null primary key,
  cnt int unsigned not null
)ENGINE=InnoDB;
UPDATE hit_counter 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;
-- 可以不用预先生成行，而用ON DUPLICATE KEY UPDATE代替，只在MySQL存在
INSERT INTO daily_hit_counter(day, slot, cnt)
VALUES(CURRENT_DATE, RAND()*100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
-- 如果希望减少表的行数，以避免表太大，可以写一个周期执行的任务，合并所有结果到0号槽。
UPDATE daily_hit_counter as c
    INNER JOIN(
      SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
      FROM daily_hit_counter
      GROUP BY day
    )AS x USING(day)
SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot = x.mslot, 0, c.slot);
DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

5 加快ALTER TABLE操作的速度

• ALTER操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这可能会导致性能问题，尤其在内存不足而表又很大，而且还有很多索引。
• 常用技巧:
  • 先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
  • “影子拷贝”，用要求的表结构创建一张与原表无关的新表，然后通过重命名和删除操作交换两张表。
• 修改列(ALTER TABLE允许使用ALTER|MODIFY|CHANGE COLUMN修改列，作用都不一样)：
  • ALTER TABLE mytable MODIFY COLUMN ...

    所有的MODIFY操作都将导致表重建

  • ALTER TABLE mytable ALTER COLUMN ...

    直接修改.frm文件而不涉及表数据，跳过创建表的操作，速度快。

5.1 只修改.frm文件

不受官方支持，可能不能正常工作，一定要先备份数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/954.html

• 不需要重建表的操作：
  • 移除(不是增加)一个列的AUTO_INCREMENT属性。
  • 增加、移除或更改ENUM和SET常量，如果移除的是已有行数据用到的常量，查询会返回一个空值。
• 技巧：
  • 创建一张有相同结构的空表，并进行所需要的修改。
  • 执行FLUSH TABLE WITH READ LOCK。将会关闭所有正在使用的表，并且禁止任何表被打开。
  • 交换.frm文件。(.frm文件只存放表的定义)
  • 执行UNLOCK TABLES操作来释放锁。

5.2 快速创建MyISAM索引

• 高效载入数据到MyISAM表的技巧，先禁用索引，载入数据，然后重新启用索引：

  mysql> ALTER TABLE test.load_data DISABLE KEYS;
  -- load the data
  mysql> ALTER TABLE test.load_data ENABLE KEYS;

  • 发挥作用的原因：构建索引的工作被延迟到数据完全载入之后，这个时候可以通过构建排序来创建所以，这样做速度更快，使得索引树的碎片更少，更紧凑
  • 但是对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。MyISAM会在内存中构造唯一所以，并且为载入的每一行检查唯一性，一旦索引的大小超过了有效内存，载入的操作会变慢。
• InnoDB中类似的高效载入数据技巧：
  • 删除所有的非唯一索引
  • 增加新的列
  • 重新创建删除掉的索引
• 操作步骤：
  • 用需要的表结构创建一张表，但是不包括索引。
  • 载入数据到表中以构建.MYD文件。
  • 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYI文件。
  • 获取读锁并刷新表。
  • 重命名第二张表的.frm和.MYI文件，让MySQL以为是第一张表的文件。
  • 释放读锁。
  • 使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有的索引，包括唯一索引。

6. 总结

• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则避免使用NULL。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
• 注意可变长字符串，其在临时表或排序时可能导致悲观的按最大长度分配内存。
• 进行使用整形定义标识列
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度
• 小心使用ENUM和SET。虽然用起来方便，但是不要滥用，否则有时候会成为陷阱。避免使用BIT。
• 范式是好的，但反范式(大多数情况下意味着重复数据)有时是必需的
• ALTER TABLE可能会导致性能问题，它都会锁表并且重新建表。