高性能MySQL(2)——Schema与数据类型的优化

2022-12-01 21:07:34 浏览数 (1)

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计 schema,这往往需要权衡各种因素。

一、选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管 存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

  • 更小的通常更好 更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
  • 简单就好 简单数据类型的操作通常需要更少的CPU周期。
  • 尽量避免NULL 如果査询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使 得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在 MySQL里也需要特殊处理。

在为列选择数据类型时：

第一步需要确定合适的大类型：数字、字符串、时间等；

第二步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储 的长度和范围不一样、允许的精度不同，或者需要的物理空间不同（相同大类型的不同子类型数据有时也有一些特殊的行为和属性）。

1.1、整数类型

有两种类型的数字：整数和实数。

整数类型：

  • TINYINT 1字节、【-128，127】、【0，255】
  • SMALLINT 2字节、【-32768，32767】、【0，65535】
  • MEDIUMINT 3字节、【-2147483648，2147483647】、【0，4294967295】
  • INT 4字节、【-2147483648，2147483647】、【0，4294967295】
  • BIGINT 8字节、【-263，263-1】、【0，264-1】

整数类型有可选的UNSIGNED属性，表示不允许为负数，大致可以使得正数的上限提高一倍。

有符号和无符号具有相同的存储空间和性能，根据实际情况选择合适的类型。

Tips:整数计算一般使用 64位的BIGINT整数，即使在32位环境也是如此，不同的数据可以决定的只有MySQL是怎么在内存和磁盘中保存数据的。 例如INT(11),对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具(例如MySQL命令行客户端) 用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的

2.2、实数类型

实数是带有小数部分的数字。

  • FLOAT 单精度，4字节
  • DOUBLE 双精度，8字节
  • DECIMAL 存储精确的小数

FLOAT和DOUBLE使用标准的浮点运算进行近似运算，如果需要知道浮点运算是 怎么计算的，则需要研究所使用的平台的浮点数的具体实现。

DECIMAL支持精确计算，但CPU不支持对DECIMAL的直接计算，MySQL自己实现了DECIMAL的高精度计算，所以DECIMAL在性能上要弱一些。

Tips:DECIMAL需要额外的空间和计算消耗，当数据量比较大时，可以考虑使用BITINT来代替，将存储的数据根据小数的位数乘以相应的倍数即可。这样可以解决浮点类型计算不准确，DECIMAL计算开销太大的问题。

2.3、字符串类型
VARCHAR

VARCHAR用于存储可变长的字符，比定长更节省空间，越短的字符占用空间越少。 但有一种例外：当表使用“ROW_FORMAT=FIXED”创建时，每一行都会使用定长存储，浪费空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或 等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集， 一个VARCHAR(IO)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息

VARCHAR节省了存储空间，对性能也有帮助。但是由于行是变长的，所以UPDATE时可能比原来更长，这就需要MySQL为其额外再分配存储空间，导致UPDATE时开销比定长类型要大。

CHAR

CHAR类型是定长的，MySQL会根据定义的长度去分配存储空间，所以不会有VARCHAR进行UPDATE时的额外开销。 CHAR类型存储时，会自动去除末尾的空格，这一点需要注意。

CHAR适合存储短的，长度固定的字符，例如MD5值，UUID等…

由于UPDATE时没有额外的开销，对于经常变更的数据，CHAR的性能也比VARCHAR更好。

BLOB和TEXT类型

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

它们分别属于不同的数据类型家族：

字符类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。 二进制类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

与其他类型不同，MySQL会将BLOB和TEXT当做单独的对象处理。

当值太大时，MySQL会使用专门的存储区域来存储数据，行内使用1~4字节来存储一个指针，指向对应的大文本字符。

BLOB和TEXT的不同之处在于：由于BLOB是二进制，所以没有字符集和排序规则，但是TEXT有。

即使TEXT有排序规则，MySQL对其进行排序时，也不会对整个文本进行排序，只会对前max_sort_length字节进行排序，可以通过修改max_sort_length进行配置。

MySQL不能将BLOB和TEXT全部长度的字符进行索引。

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

枚举可以把一些不重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，会根据列表值压缩到1到2个字节中。

MySQL在内部会将列中的枚举值保存为整数，在.frm文件中保存一个“数字->字符串”的映射关系，通过数字快速的查找到具体的枚举值。

枚举字段排序时，并不会按照给定的字符串排序，而是根据内部的整数排序，所以建议列举枚举时按照预想的顺序给出。

日期和时间类型

MySQL提供了多种类型来保存时间和日期，例如：YEAR、DATE、DATETIME。 MySQL能存储的最小时间粒度为秒（有的第三方存储引擎支持微秒）。

MySQL提供了两种相似的事件类型：DATETIME和TIMESTAMP。

DATETIME

用来保存大范围的时间，从1001年到9999年，精度为秒。 它把时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节来存储。

TIMESTAMP

保存了从1970年1月1日凌晨以来的秒数，和UNIX时间戳相同。 使用4个字节来保存，比DATETIME节省空间，具有更高的性能。 但是范围比DATETIME要小得多，只能存储1970年到2038年。

TIMESTAMP显示的值依赖于时区，MySQL服务器，操作系统以及客户端的连接都有时区的设置。

除了特殊行为之外，应该尽量使用TIMESTAMP，它比DATETIME空间效率要高。

如果需要存储比秒更小粒度的时间，MySQL目前没有提供合适的数据类型，可以考虑使用BIGINT来存储微秒级别的时间戳。

2.4、位数据类型

可以使用BIT列存储一个或多个true/false值，BIT(1)包含单个位的字段，最多可包含64个位。

MySQL将BIT当做字符串类型，而不是数字类型。 当查询BIT(1)时，结果是一个包含二进制0或1的字符串，而不是ASCII码中的“0”或“1”。

BIT列进行比较时，MySQL会将位字符串转换为十进制数字进行比较。 例如：‘111’ = 7。

对于大部分应用，最好慎用BIT类型。

2.5、选择标识符

为标识列选择合适的数据类型十分重要。

一般来说标识列很可能用来在不同的表之间进行比较，甚至作为外键来使用。 合适的数据类型可以提升系统的整体性能，减少数据比较的系统开销。

一旦选定了类型，一定要确保关联表中也是相同的数据类型，混用不同的数据类型会带来很多麻烦。 例如：将字符串与整形做比较，会导致严重的性能问题。

一般来说，在没有特殊要求的情况下，整型 通常是标识列最好的选择，因为它很快，而且可以自动递增。

如果可以的话，应该尽量避免使用字符串当做标识列，它很消耗空间，而且比整型慢。

很多人喜欢用随机的字符串来作为标识列，例如：UUID。 由于生成的字符没有规律，会导致INSERT和SELECT语句变得很慢：

  • 插入的值会随机的写入到索引的不同位置，使得INSERT更慢。这会导致页分裂，磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
  • SELECT语句变慢，因为逻辑上相邻的数据会分布在磁盘的不同地方。
  • 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问“局部性原理”失效。缓存无法命中，加载到内存中也是徒劳。

Tips：如果需要使用UUID当做标识列，那么应该移除“-”这种没有意义的字符。 最好的解决方案是：用UNHEX()将UUID转换为16位的二进制数据，没有字符集，没有排序，而且占用更少的磁盘空间，可以很好的提升性能。

2.6、特殊类型数据

有些类型的数据并不直接与MySQL的内置类型一致，微秒型的时间戳就是个例子。

还有例如：IPv4地址，应该使用无符号的整数来保存，而非字符串。 MySQL内置的函数INET_ATON和INET_NTOA可以很好的转换。

二、Schema设计中的陷阱

虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的， 这意味着有可能犯一些只在MySQL下发生的特定错误。

了解讨论设计MySQL的 schema的问题。这也许会帮助我们避免这些错误，并且选择在MySQL特定实现下工作得更好的替代方案。

  • 太多的列 MySQL存储引擎工作时，需要在服务器层和存储引擎层之间做行缓冲格式拷贝数据，然后在服务器层之间将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。 如果单张表的列太多，就应该要考虑做表的拆分。
  • 太多的关联 MySQL限制了每个关联最多只能61张表，一个粗略的经验法则：如果希望査询执行得快速且并发性好,单个查询最好在12张表以内做关联。
  • 全能的枚举 注意防止过度使用枚举(ENUM)，在MySQL 5.0以及更早的版本中ALTER TABLE是一 种阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也会一样需 要ALTER TABLE 。
  • Not Invent Here 的 NULL 我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存 储一个事实上的“空值”到表中时，也不一定非得使用NULLO也许可以使用0、某个特殊值，或者空字符串作为代替。 但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL在一些场景中，使用NULL可能会比某个神奇常数更好。

三、范式和反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中。

设计关系型数据库时，需要遵从不同的规范，设计合理的关系型数据库，不同的规范被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余约小。在关系型数据库中有六中范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），BCNF，第四范式（4NF），第五范式(5NF)。一般数据库设计到第三范式就行了

这里简单介绍一下三大范式：

  • 第一范式 确保数据表中每列（字段）的原子性。 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
  • 第二范式 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。 如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。
  • 第三范式 在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。通过第三张表（中间表）来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。
  • 反范式化 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。
3.1、范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集 的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：

  • 范式化更新操作通常比反范式化要快。
  • 当数据较好的范式化时，就只有很少或者没有重复数据，所以，只需要修改更少的数据。
  • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据更少需要distinct或者group by 语句。

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策 略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属 于同一个索引。

3.2、反范式的优点和缺点

反范式的优点：

  • 可以很好地避免关联。
  • 如果不需要关联表，对大部分查询最差情况，即没有使用索引，全表扫描。当数据币内存大时这可能比关联要快很多， 这样避免了随机I/O。

在真实环境中很少会极端地使用范式化或者反范式化的schema。而是可能使用部分范式化的schema、缓存表、以及其它技巧。最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定的列。

3.3、混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实 世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的 schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在 MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更 简单。

四、加快ALTER TABLE操作的速度

MySQL 对于大表的ALTER操作是非常慢的，因为 MySQL 对于ALTER操作的的方法是创建一个新结构的表，然后将旧结构表中的数据复制过去，最后将旧表删除。如此操作对于海量数据的表来说花费的时间是非常长的。

一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL 操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧 只有两种：

一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服 务的主库进行切换；

另外一种技巧是影子拷贝，影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

4.1、只修改.frm文件

如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重 建表。

下面这些操作是有可能不需要重建表的：

  • 移除（不是增加）一个列的AUTO_INCREMENT属性。
  • 增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量， 查询将会返回一个空字串值。

基本的技术是为想要的表结构创建一个新的**.frm文件，然后用它替换掉已经存在的那张 表的.frm**文件，像下面这样：

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

为了高效地载入数据到MylSAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引，这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入以后，这个时候 已经可以通过排序来构建索引了。这样做会快很多，并且使得索引树注”的碎片更少、更紧凑。

不幸的是，这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。 MylSAM会在内存中构造唯一索引，并且为载入的每一行检査唯一性。一旦索引的大小<33 超过了有效内存大小，载入操作就会变得越来越慢。

下面是操作步骤：

  1. 用需要的表结构创建一张表，但是不包括索引。 注：如果使用的是LOAD DATA FILE,并且要载入的表是空的，MylSAM也可以通过排序来构造索引。
  2. 载入数据到表中以构建.M阳 文件。
  3. 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的斤％和.心以 文件。
  4. 获取读锁并刷新表。
  5. 重命名第二张表的为“和文件，让MySQL认为是第一张表的文件。
  6. 释放读锁。
  7. 使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一 索引。

五、总结

良好的schema设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。概括来 说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人 应该也同样会喜欢简单的原则：

  • 尽量避免过度设计，例如会导致极其复杂査询的schema设计，或者有很多列的表设 计（很多的意思是介于有点多和非常多之间）。
  • 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可 能地避免使用NULL值。
  • 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
  • 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
  • 尽量使用整型定义标识列。
  • 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
  • 小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成 陷阱。最好避免使用BITO

范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带 来好处。

参考：

《高性能 MySQL 第三版》
Schema与数据类型优化
MySQL三大范式和反范式
[MySQL中范式与反范式的优缺点](
云数据库SQLServer 存储 数据库 sql 缓存

0 人点赞