MySQL InnoDB 分区表优势_第37期：适当的使用 MySQL 原生表分区

当前 MySQL 数据库普遍采用特定引擎，不存在类似 MERGE 引擎这类内置的表拆分功能，不过系统支持原生分区表，能够通过横向切分数据记录，且对应用程序端保持完全透明。

划分区域的方法为海量数据表的查询任务和常规维护工作开辟了另一条可选路径，合理运用分区技术，能够显著增强数据库的运行效率。

分区表具备诸多长处：能显著加快部分数据检索速度，有助于减轻日常管理负担，并提高操作效能，支持同时处理多个请求且合理分配写入负载，对使用者而言无需在软件层面添加额外配置或组件，下面将通过具体案例来更明确地展示前两个优点，以检索为例：能够改进数据查找效率（针对特定条件筛选）

划分恰当的分区表，针对相同的查询，其检索的记录数远少于非分区表，运作效率也显著超越非分区表。

这个示例展示了 t1 不是分区表，它的对应分区表是 p1 ，这两个表包含相同数量的记录，总共有 1KW 条。

本地主机：ytt> 显示创建表 t1\G
横线下的句子被分割成了好几个独立的短句，每个短句之间用逗号隔开，并且没有使用原文中出现的词汇，句子的结构也进行了调整，以保持与原文相似的风格，同时确保了句意的完整保留。横线下的句子现在看起来更加清晰，每个分句都表达了明确的意思，整体上更加易于理解。横线下的句子被重新组织成了多个小分句，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句分隔开来，这种表达方式使得句子的结构更加清晰，同时也避免了使用原文中已经出现过的词汇。横线下的句子现在由多个小分句组成，每个分句都通过逗号与其他分句
       Table: t1
建立表格：构造一个名为`t1`的表格，包含以下内容，
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
数据库引擎采用InnoDB, 默认字符集为utf8mb4, 排序规则为utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
本地主机：ytt> 显示创建表 p1\G
*************************** 1. row ***************************
       Table: p1
建立表格：构造一个名为p1的表格，包含以下字段，
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
将数据按id值进行范围划分，实现分区功能
将数据表分割为特定部分，该部分包含所有值小于一百万的记录，并指定其存储引擎为InnoDB
分区p1，数据量小于两千零十万，存储引擎选用InnoDB
分区的数据量小于三百万，采用InnoDB存储引擎进行管理
划分 p3，数据量小于四百万，采用 InnoDB 引擎
分区p4，数据量小于五百万，采用InnoDB引擎进行存储，
划分 p5, 值小于 6000000, 使用 InnoDB 引擎
分区p6的数据量小于7000000，其存储引擎选用InnoDB类型
将数据表分区p7, 其中数据值小于8000000的部分, 采用InnoDB存储引擎进行管理
划分 p8, 数据量小于九百万, 选用 InnoDB 引擎
分区的名称是p9, 数据存储在小于最大值的范围内, 使用的存储引擎是InnoDB
1 row in set (0.00 sec)
本地主机：ytt，查询 t1 表中的记录总数。
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.94 sec)
本地主机：ytt，查询表 p1，统计记录总数，结果如下：
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.92 sec)

我们来分别对两张表做范围检索，以下为执行计划：

本地主机：ytt，说明格式为树形，查询 t1 表，统计 id 字段，返回数量< 1000000\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (t1.id < 1000000)  (cost=407495.19 rows=2030006)
        ->对t1执行索引范围扫描，采用主键索引，成本为407495.19，预计返回2030006行
1 row in set (0.00 sec)
本地主机：ytt，说明格式为树形结构，查询统计 p1 表中符合条件的记录数量，条件是 id 字段满足要求。< 1000000\G
*************************** 1. row ***************************
说明：总计：计数为零
    -> Filter: (p1.id < 1000000)  (cost=99980.09 rows=499369)
        ->在p1上执行索引范围扫描，通过主键，成本为99980.09，预计返回499369行
1 row in set (0.00 sec)

表 t1 和表 p1 的执行计划相比较，从成本方面看，前者明显更高，比后者高出很多倍；从扫描记录数量分析，前者的数据量也大幅超越了后者，这说明分区表在性能上确实优于非分区表。

再来看看对两张表做不等于检索的执行计划：

本地主机：ytt> 说明格式为树形展示，查询 t1 表中 id 不等于 2000001 的记录数量，结果如下
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (t1.id <>耗费了一千八百二十万八千六百六十六点五八元，处理了九十一万一千七百六十四九个数据
对t1执行索引范围扫描，通过主键，成本为1829866.58，预计返回9117649行
1 row in set (0.00 sec)
localhost:ytt> 说明格式为树状结构选择统计数量从表p1条件为id不等于2000001结束
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (p1.id <>耗费资金一千零二万七千五百点二三，处理数据四百九十九万三千六百九十一条
对p1执行索引区间扫描，以主键为依据，成本为1002750.23，预计返回4993691行数据
1 row in set (0.00 sec)

就这种执行效率欠佳的 SQL 而言，通过分析其执行方案可以发现，采用分区表的方案在多个方面展现出更优表现，例如成本更低，且扫描的数据记录数量更少，对比非分区表具有明显好处。

优化写入性能（带过滤条件的 )。

对于这类更新请求，分区表同样要比非分区表来的高效。

在等值过滤的更新情境里，对比非分区表和分区表的执行方案，重点观察扫描数据行数，分区表检索到的数据条目比非分区表要少得多。

本地服务器：ytt> 说明修改表t1将列r1的值设为当前日期减去一个随机天数，这个天数范围是0到5000天之间，筛选条件是id号在1000001到2990000之间记录，结果如下
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 3938068
     filtered: 100.00
        Extra: Using where
查询结果返回一行数据，同时产生一条警告信息，整个过程耗时零点零零秒
localhost:ytt> 说明 更新 p1 表 将 r1 字段 设置为 当前日期 减去 随机生成的 5000 天整数倍 的值 其中 条件是 id 在 1000001 到 2990000 之间
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: p1
   partitions: p1,p2
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 998738
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

针对运维来讲：分区表数据与非分区数据进行交换。

特定分区的数据能够轻易进行迁移和接收，可以迅速地与未分区表的数据进行转换。

建立一个名为 t_p1 的表格，这个表格需要与 p1 表的分区 p1 进行数据交换。

本地主机：ytt，建立表 t_p1，依照 t1 的结构来创建。
查询成功，没有数据被改动，耗时零点零六秒。

该分区 p1 内含有一百万行数据记录，借助分区表的本地数据置换机制进行数据交换，整个过程耗时仅零点零七秒。

本地主机：ytt，修改表 p1，将分区 p1 与表 t_p1 进行交换；
查询成功，没有数据被改动，耗时零点零七秒。

检查交换后的信息，表 p1 缺少了 100 万条数据，分区 p1 被完全删除，表 t_p1 增加了 100 万条数据。

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
|  9000000 |
+----------+
1 row in set (0.79 sec)
本地服务器：ytt端口，查询表t_p1中的记录总数，结果如下：
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.13 sec)

可以随时把数据交换回来，被交换的表清空。

localhost:ytt> alter table p1 exchange partition p1 with table t_p1;
查询成功，没有数据被改动，耗时零点七七秒
localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.91 sec)
localhost:ytt> select count(*) from t_p1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

对比下非分区表的数据交换，步骤为：

挑选出待替换的对照表，从初始表格中提取信息填充到对照表，移除初始表格里关联的数据。

倘若此刻要将调换出去的信息再次调入初始表格，就必须把先前流程倒转执行一遍，这样会加大管理工作的复杂程度，并且导致工作方式不够高明。

分区表替换有一个显著长处，就是其产生的日志数据远少于未分区表的记录量。现在我们再执行一次之前的替换动作。务必将全部二进制日志文件移除掉。

localhost:ytt>reset master;
查询成功，没有数据被改动，用时零点零二秒

做一次分区置换

本地主机：ytt>修改表 p1，将分区 p2 与表 t_p1 进行交换；
查询成功，没有数据被改动，耗时2.42秒

再次做置换删除表 t_p1 数据

localhost:ytt>alter table p1 exchange partition p2 with table t_p1;
查询成功，没有数据被改动，耗时零点四五秒。

此时两次置换操作记录到二进制日志 ytt1. 里。

本地主机：ytt>查询主状态信息；
...
那个特定编号，就是47d6eda0-6468-11ea-a026-9cb6d0e27d15，它从1变到了2，这整个过程持续了大约47天，期间经过了一个极其微小的数值调整，具体是0.000001

重刷日志，非分区表置换记录。

localhost:ytt>flush logs;
查询成功，没有数据被改动，耗时零点零一秒。
本地主机执行插入操作，目标表为t_p1，数据来源通过选择语句获取，选取p1表全部内容，并针对p2分区进行筛选，完成数据迁移过程。
查询成功，影响了九十三万四千四百七十三行，耗时五点二五秒。
记录数量：九十三万四千四百七十三条，重复项：零项，提示信息：无
localhost:ytt>show master status;
...
ytt1.000002，47d6eda0-6468-11ea-a026-9cb6d0e27d15，这个标识符，包含三个部分，分别是前缀，主体码和序列号，整体结构清晰，便于识别和管理，符合规范要求

查看详细记录文档，ytt1. 仅消耗588字节数据，但 ytt1. 的存储需求达到7.2兆字节。

根号下个字母y特特普卡斜杠变斜杠立可思爱米下边数字三三零六井号字母 Ess撇号艾恩大写西奥恩下边字母y特特一号点零零零零星号
2109882 文件大小为四千字节, 属于可读写但不可执行的普通文件, 属于用户mysql, 组为mysql, 占用空间为588字节, 创建于七月二十三日, 十一点十三分, 文件名为ytt1.000001
2109868 7.2M 限制读写执行属主 mysql 组 mysql 7.2M 7月 23日 上午11时14分 文件名 ytt1.000002

快速清理单个分区数据。

删除单个分区数据性能要优于非分区表删除某个范围内的数据。

比如，要清空分区表 p1 分区 p0 ，直接 单个分区。

本地主机执行命令，针对表p1，清空分区p0的数据，完成分区重置操作。
Query OK, 0 rows affected (0.07 sec)
localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
|  9000001 |
+----------+
1 row in set (0.92 sec)

非分区表仅具备整张表的操作能力，因此无法对部分数据实施迅速清除，只能依照筛选标准进行数据，导致效率大幅降低，相同操作，其速度比非分区表慢数十倍。

本地主机：ytt，删除表t1中的记录，条件是id字段< 1000000;
Query OK, 999999 rows affected (26.80 sec)

总结：

MySQL的分区表在诸多情形中表现相当出色，本文阐述了分区表在基础查询与日常管理上的核心益处，接下来将逐一探讨分区表在各类复杂环境中的具体运用。

有关 MySQL 的专业知识，各位还有哪些疑问呢？请尽快在评论区向编辑咨询！