MySQL Sechma 和数据类型优化

terry 2年前 (2023-09-26) 阅读数 100 #数据库

1 选择优化的数据类型

选择正确数据类型的规则：

越小往往越好
简单就是好的

尽量避免 ‾ 通常，将NULLable列更改为NOT NULL带来的改进相当小，调优时没有必要更改它。如果您不打算在该列上创建索引，则应避免将其设置为 NULL。

例外：

InnoDB 使用单独的位来存储 NULL 值，这对于稀疏数据很有好处。

缺点：

查询带有NULL的列使MySQL变得更加困难，并且NULL使得索引、索引统计和值比较更加困难。
NULL 列使用更多存储空间。

1.1 整数类型

TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT 分别使用 8、16、24、32 和 64 位存储位置。
可选的UNSIGNED属性不允许负值，可以使正值存储数量加倍。
整数计算一般使用BIGINT整数
可以指定宽度，但不限制合法类型，仅用于表示字符数。

1.2 实数类型

带小数部分的数字也使用 DECIMAL 来存储比 BIGINT 更大的数字
支持明确定义和不明确定义的类型
使用 FLOAT (Float 8) 进行浮点数近似计算并支持确切的条件； DEMICAL存储精度小数，支持精度和精度计算，并定义了点前后最大可能的数字，允许数字最大为65
CPU直接支持原生浮点计算，MySQL服务器层自己实现。高级 DEMICAL 计算
使用 DOUBLE

1.3 字符串类型

VARCHAR 和 CHAR：由于固定长度类型而存储字符
您需要使用 1（如果最大列长度小于或等于255字节）或附加2个字节来记录字符串的长度。
如果UPDATE行变长并且页面上没有更多存储空间，MyISAM会将该行分割成不同的存储块，而InnoDB需要分割页面。
使用历史：
串列长度远大于平均长度；
栏目更新不多，划分不是问题；
使用UTF-8等复杂字符集，每个字符都用不同的编号存储。
5.0 及更高版本，存储和检索期间将保留尾随空格
InnoDB 会将过长的 VARCHAR 存储为 BLOB
VARCHAR(5) 和 VARCHAR(200) MySQL 存储固定内存块以保留内部值，所以后者会消耗更多的内存。使用内存临时表或磁盘临时表进行排序或操作尤其糟糕。因此，仅提供必要的空间。

CHAR：

固定长度，MySQL根据指定的字符串长度分配足够的空间。
MySQL将删除以下位置；如有必要，将予以填写，以便于比较。
适合存储短字节，或者所有值的长度都接近。例如：存储密码的MD5值；对于频繁变化的数据，固定长度的 CHAR 比 VARCHAR 更难整除；对于短列，CHAR 比 VARCHAR 在存储空间上更有效，并且 CHAR(1) 只能用于存储 Y 和 N 值。

BINARY和VARBINARY：存储二进制字符串，存储字节码而不是字符；对二进制数据使用字节码更简单、更高效。

BLOB和TEXT类型：

BLOB和TEXT易于存储（集合和字符）。
文本系列：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT； BOLB 家族：TINYBOLB、SMALLBOLB、BOLB、MEDIUMBLOB、LONGBOLB。
与其他类型不同，MySQL将每个BLOB和TEXT视为独立的对象，存储引擎在存储时进行特殊处理。如果该值太大，InnoDB将使用特殊的“外部”存储区域进行存储。每个值需要1~4个字节来存储一个指针，用于指示外部区域存储的值。
分类与其他物种不同。仅检索每列的前 max_sort_length （可配置）字节，而不是整个字符串。还可以使用ORDER BY SUSTRING(列,长度)
MySQL不支持对长字符串建立索引，无法使用。这些已被完全删除。
内存引擎不支持这两种类型。如果应用程序使用它并且需要隐式临时表，则将使用MyISAM磁盘临时表。这可能会导致头顶发生大量运动。因此，尽量避免使用这两种类型，或者在询问时使用SUBSTRING函数，但临时表的大小不要超过max_heap_table_size或tmp_table_size（这也适用于创建大型临时表以及在内存和磁盘中对文件进行排序）。

使用枚举 (ENUM) 而不是字符串类型

枚举列在预定义集合中存储唯一字符串。
MySQL 以紧凑的方式存储枚举，根据列表值的数量压缩为一个或两个字节；内部将列表中每个值的位置存储为整数并存储在“Lookup table”表的.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 令牌存储为整数，因为它们不适合处理并且不会有用；使用 DOUBLE 存储秒后的小数部分

DATETIME
- 存储一系列值，从 1001 到 9999 年，带秒。格式为 YYYYMMDDHHMMSS 的整数，与时区无关；使用8字节存储。
- 默认情况下，使用可选且为空的 ANSI 标准定义格式来显示，例如“2017-06-29 17:42:05”
TIMESTAMP
- 存储 1 月 1 日午夜的开始日期， 1970 年以来的秒数（格林威治标准时间），根据 UNIX 时间戳；它只使用4字节的存储空间，所以1970-2038
- MySQL提供了FROM_UNIXTIME()和UNIX_TIMESTAMP()来将UNIX日期和时间相互转换时间戳
- 显示的值取决于时间。区域
- 默认为 NULL。如果插入时未指定第一个 TIMESTAMP 列值，则该值将设置为当前时间。

1.5 位数据类型

技术上是一种字符串类型，与基本存储和处理格式无关

BIT：
- 使用 BIT 列在一列中存储一个或多个真/假值。
- 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类型
- 如果你想在较小的存储空间中存储真/假值，你可以创建一个可以为空的CHAR(0)列。通过存储空值（NULL）或无长度的字符（空字符串）可以有效地使用列
SET：
- 加载的位数组，可用于存储许多true / false的值
- 存储空间，以及 FIND_IN_SET() 和 FILED() 等函数
- 更改列的成本太高，需要 ALTER TABLE，这对于大型表来说是一个非常昂贵的操作
- 无法将指针传递到列 SET Find
按位执行对整数列的操作：
- 使用整数来绑定多个位而不是SET，例如将8位填充到TINYINT中并使用其进行按位操作，这可以在应用程序中为每个位定义常量名称。这个任务非常简单。
- 好处是不需要使用ALTER TABLE。缺点是请求语句比较难写和理解，识别列可以与其他值进行比较（组合运算中）
- 或者通过识别列来搜索其他列
- 都可以使用作为其他表中的外键，所以 相关表类型应选择相应列中相同的数据，并且需要完全匹配，包括 UNSIGNED 等属性
需要考虑的因素：
- 存储类型
- MySQL如何持久化，比较这种类型的存储
- 满足价值要求。并将其保存在未来增长的领域中，选择最小的数据类型。
选择提示：
- 整数类型：
  - 指示列是最好的选择，速度快，可以使用AUTO_INCRMENT
- ENUM和SET类型：♻通常应该是一个糟糕的选择。适用于仅包含状态或类型的静态“定义表”。
字符串类型：
不要使用它，因为它占用空间并且通常比数字类型慢。特别是在MyISAM中，字符串默认使用压缩索引，导致查询速度变慢。
注意绝对的“ice”字符串，例如由 MD5()、SHA1() 或 UUID() 生成的字符串。这些值会随机分布在很大的空间上，导致一些INSERT和SELECT语句变慢：
- 插入的值会随机写入索引中的不同位置，这会导致页分区，随机磁盘访问和集群存储引擎的集群索引分区，使 INSERT 语句变慢；
- 逻辑上连续的行将分布到磁盘和内存中的不同位置，使得 SELECT 查询变慢；
- 使缓存对所有类型的查询语句都无效，因为它会使缓存操作的本地访问规则无效。也就是说，如果整个数据集是“热”的，那么将数据的特定部分存储在内存中没有任何好处；如果工作集大于内存，就会出现很多缓存未命中和冲突。
如果存储UUID值（即使分布不一样，仍然有规律），应该去掉“-”符号，或者使用UNHEX()函数将其转换为16位字节数。并存储为 BINARY(16) ，并且可以使用 HEX() 函数将搜索转换为十六进制格式

注意自动生成的 schema：

写得不好的 schema 迁移程序，或者自动创建的程序一个模式。，会导致严重的问题性能问题
对象关系映射 (ORM) 系统以及使用它们的系统也会导致性能问题。小心映射并避免在空数据类型中存储空数据类型

1.7 特殊数据类型

某些数据类型不直接对应于内置类型。例如，我们之前推出了亚秒级基准测试；与 IPv4 地址一样，人们经常使用 VARCHAR(15) 列来存储 IP 地址。然而，它实际上是一个 32 位无符号整数，而不是字符串。小数点除法简单更容易读取，因此 IP 地址应存储为无符号整数。 MySQL提供了INET_ATON()和INET_NTOA()函数来转换这两个表达式。

2。 MySQL架构设计的陷阱

列太多
- 原因：MySQL存储引擎API工作时，需要在服务器层和存储引擎层之间以行缓冲区的形式转换数据，然后在。服务器层将缓冲区的内容转换为单独的列。将编码列从行缓冲区转换为行数据结构的操作非常昂贵。然而MyISAM的可变行结构（固定行结构与服务器层的行结构完全对应）和InooDB的行结构需要时刻改变，而转换价格取决于列数。。
太多关联：
- “实体 - 属性 - 值”（EVA）是一种常见（不好？？？）模型，因为它可能需要大量关联。任何与MySQL相关的工作都不能最多有61张表，而且事实上，如果数量小于这个数量，就会有分析和优化查询的成本。
- 单个查询最好连接到表12，从而提高请求和良好合约的执行速度。
多个枚举
- 写过度使用枚举
- 如果向枚举列添加新项，如果最后没有添加值，则需要ALTER TABLE，导致性能问题。
变相枚举
- 枚举列允许将指定值集中的单个值存储在列中，而（SET）列允许指定值集中的一个或多个值要存储在列中。如果没有多个值同时存在的场景，不要使用集合（SET）。
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及更高版本中，触发器可用于更新存储的值。
- 优点：
  - 避免非规范化的插入或删除问题
  - 可以正确配置。
  - 可用值可以被缓存。如果有用户表和消息表，可以在用户表中创建num_messages来显示用户发送的消息数量。？ ) 数据表，例如冗余数据。
  - 用于改进搜索和搜索查询。这些查询语句往往需要特殊的表结构和索引结构，与典型OLTP操作中使用的表有很大不同。
- 汇总表：
  - 存储使用 GROUP BY 语句收集数据的表。例如，数据在逻辑上不有效。
  - 避免昂贵的实时统计计算实现，因为这需要扫描表中的大部分数据，或者只能在某些参考中有效运行，并且通常会影响创新工作。
应用领域：
- 需要许多不同的索引集来加速不同类型的查询。这些相互冲突的要求有时需要创建一个包含主表中的一些列的缓存表。缓存表可以使用不同的存储引擎。虽然主表使用InnoDB，但使用MyISAM作为缓存表引擎会占用更小的空间，并且可以用于全文索引。
用途：
- 维护实时数据
- 持续恢复，节省资源，保持表不分区，创建完全组织的索引。
读快，写慢
- 为了提高查询速度，往往需要创建额外的索引，添加冗余列，或者创建缓存表和汇总表，这会增加查询负载，还需要额外的维护工作和甚至增加了开发复杂性。但提高了阅读效率。

4.1 Flexviews

MySQL不支持，所以可以使用开源工具Flexviews。

4.2 计数器表

在 Web 应用程序中，计数器表可用于记录用户的好友数量或下载量。可以创建一个单独的计数器表，使表更小、速度更快。

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...
  所有操作都会导致MODIF进行配置。 ALTER TABLE mytable ALTER COLUMN...
  直接编辑.frm文件，无需访问表数据，绕过建表功能，速度快。

5.1 仅编辑.frm 文件

不受官方支持，可能无法正常工作。请务必先备份数据

不需要重建表的操作：
- 从“属性”列中删除（而不是添加）AUTO_INCREMENT。
- 添加、删除或修改 ENUM 和 SET 常量。如果省略数据行使用的常量，查询将返回空值。
建议：
- 创建一个具有相同结构的空表并进行必要的更改。
- 执行带锁定读数的冲洗表。所有正在使用的桌子将被关闭，并且不会打开任何桌子。
- 交换.frm 文件。（.frm 文件仅存储表定义）
- 执行 UNLOCK TABLES 操作以释放表锁。？加载后，可以通过构建当前类型来创建它。所以这样更快，使得索引树可读性更差，组织性更强
- 但对单个索引无效，因为 DISABLE KEYS 仅对非唯一索引有效。 MyISAM 将在内存中建立唯一索引并检查每个完整行的唯一性。一旦索引大小超过有效内存，加载性能就会变慢。
类似于 InnoDB 的有效数据加载技术：
- 删除所有非唯一索引
- 添加新列
- 恢复已删除的索引使用必要的表结构创建表，但不带索引。
- 将数据输入表中以创建 .MYD 文件。
- 创建所需结构的空表，这次带有索引。这将创建必要的 .frm 和 .MYI 文件。
- 获取读锁并刷新表。
- 重命名第二个表中的.frm和.MYI文件，以便MySQL认为它们是第一个表中的文件。
- 释放读锁。
- 使用REPAIR TABLE重建表索引。此操作将通过排序构建所有索引，包括唯一索引。