MySQL—表优化

分区表

基本介绍

分区表是将大表的数据按分区字段分成许多小的子集，每个子集称为一个分区。通过分区，用户可以提高查询性能、简化数据管理并提高可维护性。分区表可以使数据更容易管理，尤其是当表中的数据量非常大时。

分区的优点

• 性能提升：查询特定分区的数据时，MySQL 可以跳过不相关的分区，从而减少扫描的数据量，提高查询效率。
• 管理方便：对于数据的归档和清理，分区表可以通过简单的添加或删除分区来实现，而无需对整张表进行操作。
• 并行处理：MySQL 可以对不同的分区进行并行处理，提高数据的读取和写入效率。
• 优化存储：通过不同的分区策略，可以根据数据访问频率对数据进行优化存储。例如，较少访问的数据可以放在较慢的存储介质上。

分区类型

• RANGE 分区：按指定范围的值来划分，例如按日期范围。
• LIST 分区：按特定的列表值进行分区，适合需要分类的数据。
• HASH 分区：使用哈希函数将数据均匀分配到不同的分区，适合数据分布较为均匀的情况。
• KEY 分区：类似于哈希分区，但使用 MySQL 的内置函数。

分区策略

打开表行为：在 MySQL 中，当首次访问分区表时，系统需要遍历所有分区。如果分区数量超过 open_files_limit 参数（默认值为 1024），这可能会导致在访问表时打开的文件数量超过上限，从而引发错误。这种情况下，优化分区的数量和管理方式显得尤为重要。

通用分区策略：MyISAM 引擎采用的通用分区策略在性能上存在一些不足。每次访问分区时，操作由 Server 层控制，文件管理和表管理的实现较为粗糙，因此在处理大规模数据时会出现显著的性能问题。

本地分区策略：从 MySQL 5.7.9 开始，InnoDB 引擎实现了本地分区策略，这意味着它内部管理打开分区的行为。当打开的文件数量超过 innodb_open_files 时，InnoDB 会关闭一些之前打开的文件。因此，即使分区数量大于 open_files_limit，也不会出现错误。

自 MySQL 8.0 版本起，只允许创建支持本地分区策略的引擎的分区表，当前支持的引擎包括 InnoDB 和 NDB。

Server 层行为

在 Server 层，分区表被视为单一表：

Session A：

SELECT * FROM t WHERE ftime = '2018-4-1';

Session B：

ALTER TABLE t TRUNCATE PARTITION p_2017; -- blocked

现象

• 阻塞：Session B 尝试对分区 p_2017 执行 TRUNCATE 操作，但由于 Session A 持有整个表 t 的 MDL 读锁，导致 Session B 的 ALTER 语句无法获取 MDL 写锁而被阻塞。

分区表特点

• 第一次访问需要遍历所有分区：这可能会影响初始查询的性能。
• 共用 MDL 锁：在 Server 层，所有分区共用同一个元数据锁（MDL），因此任何对表的结构修改（如 ALTER 或 DROP）都会导致阻塞。
• 引擎层视为不同表：虽然在 Server 层视为同一张表，但在执行过程中，根据分区表的规则，MDL 锁之后的执行只访问需要的分区，提升了效率。

应用场景

分区表的优点

• 业务透明：相比于分表，使用分区表能够使业务代码更加简洁，用户在操作时无需关注底层数据的分布。
• 方便清理历史数据：按照时间分区的分区表，可以通过 ALTER TABLE t DROP PARTITION 快速删除过期分区，速度更快且对系统的影响更小，优于使用 DELETE 语句逐条删除数据。

注意事项

• 分区数量：不建议创建过多的分区。对于单表或单分区数据量达到千万行，现代硬件已足够处理，不必过于细分。
• 预留分区：不要提前创建过多的分区。建议根据实际数据增长情况逐步创建，比如按月分区，每年年底时再添加下一年度的 12 个分区。
• 管理历史分区：对于没有数据的历史分区，应该及时使用 DROP 语句清理，以避免无效的存储占用和管理复杂性。

临时表

基本介绍

临时表分为内部临时表和用户临时表：

内部临时表：由系统自动创建，用于优化 SQL 查询，例如在连接或去重查询中。

用户临时表：由用户手动创建，例如：

CREATE TEMPORARY TABLE temp_t LIKE table_1;

临时表可以是内存表或磁盘表：

• 内存表：使用 Memory 引擎，数据存储在内存中，重启后数据清空，表结构仍然保留。
• 磁盘表：使用 InnoDB 或 MyISAM 引擎，数据存储在磁盘上。

特点

• 会话专属：每个临时表只能被创建它的会话访问，不同会话的临时表可以重名。
• 同名冲突：临时表可以与普通表同名，但在同一会话中，操作会优先访问临时表。
• 不显示：使用 SHOW TABLES 命令不会列出临时表。
• 自动回收：会话结束时，临时表会自动删除，不必担心数据残留。

重名原理

创建临时表时，MySQL 为 InnoDB 表创建一个 .frm 文件和一个 .ibd 文件，文件名格式为 #sql{进程 ID}_{线程 ID}_序列号.frm。不同线程的临时表可以重名，因为 table_def_key 还包含 server_id 和 thread_id，使其唯一。

在每个会话结束时，会对链表中的临时表执行 DROP TEMPORARY TABLE 操作，确保清理。

执行 RENAME TABLE 语句无法修改临时表，因为它根据普通表的文件名规则查找文件，而临时表的命名方式不同。

主备复制

在主库创建临时表时，备库的同步线程也会执行相应的创建语句。主库在会话结束时自动删除临时表，但备库需要额外的 DROP TEMPORARY TABLE 语句进行同步。

binlog 规则：

• binlog_format=row：与临时表相关的语句不记录到 binlog。
• binlog_format=statement/mixed：会记录临时表的操作，包括 DROP TEMPORARY TABLE。

主库的多个线程可以创建同名临时表，但备库只有一个线程，因此 binlog 中记录了主库线程 ID，以构造备库的 table_def_key。

跨库查询

在分库分表系统中，使用临时表避免线程间重名冲突。通常通过中间层进行 SQL 解析，确定路由到特定分表。

在处理没有使用分区字段的查询时，可以采用以下两种方式：

1、Proxy 层处理：在 proxy 端内存中进行排序，但可能导致性能瓶颈。

2、汇总库操作：

• 创建临时表 temp_ht 包含必要字段。
• 从各分库查询数据并插入 temp_ht。
• 在 temp_ht 上执行最终查询以得到结果。