百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

第37期:适当的使用 MySQL 原生表分区

wptr33 2025-03-04 14:22 13 浏览

MySQL 数据库现在主要用的引擎是 InnoDB ，InnoDB 没有类似于 MERGE 引擎这样的原生拆表方案，不过有原生分区表，以水平方式拆分记录集，对应用端透明。

分区表的存在为超大表的检索请求、日常管理提供了一种额外的选择途径。分区表使用得当，对数据库性能会有大幅提升。

分区表主要有以下几种优势：

  1. 大幅提升某些查询的性能。
  2. 简化日常数据运维工作量、提升运维效率。
  3. 并行查询、均衡写 IO 。
  4. 对应用透明，不需要在应用层部署路由或者中间层。

接下来我们用实际例子来让前两种优势体现更新清晰。

  1. 针对检索来讲：

优化查询性能（范围查询）

拆分合适的分区表，对同样的查询来讲，扫描的记录数量要比非分区表少很多，性能远比非分区表来的高效。

以下示例表 t1 为非分区表，对应的分区表为 p1 ，两张表有相同的纪录数，都为 1KW 条。

localhost:ytt> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


localhost:ytt> show create table p1\G
*************************** 1. row ***************************
       Table: p1
Create Table: CREATE TABLE `p1` (
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (5000000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (6000000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (7000000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (8000000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (9000000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

localhost:ytt> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.94 sec)

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.92 sec)

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

localhost:ytt> explain format=tree select count(*) from t1 where id < 1000000\g 1. row explain: -> Aggregate: count(0)
    -> Filter: (t1.id < 1000000 cost='407495.19' rows='2030006)' -> Index range scan on t1 using PRIMARY  (cost=407495.19 rows=2030006)

1 row in set (0.00 sec)

localhost:ytt> explain format=tree select count(*) from p1 where id < 1000000\g 1. row explain: -> Aggregate: count(0)
    -> Filter: (p1.id < 1000000 cost='99980.09' rows='499369)' -> Index range scan on p1 using PRIMARY  (cost=99980.09 rows=499369)

1 row in set (0.00 sec)

表 t1 对比表 p1 的执行计划，从成本，扫描记录数来讲，前者比后者多了几倍，明显分区表比非分区表性能来的更加高效。

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

localhost:ytt> explain format=tree select count(*) from t1 where id != 2000001\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (t1.id <> 2000001)  (cost=1829866.58 rows=9117649)
        -> Index range scan on t1 using PRIMARY  (cost=1829866.58 rows=9117649)

1 row in set (0.00 sec)

localhost:ytt> explain format=tree select count(*) from p1 where id != 2000001\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (p1.id <> 2000001)  (cost=1002750.23 rows=4993691)
        -> Index range scan on p1 using PRIMARY  (cost=1002750.23 rows=4993691)

1 row in set (0.00 sec)

对于这样的低效率 SQL 来讲，从执行计划结果来看，分区表从成本、扫描记录数等均比非分区表有优势。

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

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

下面为等值过滤的更新场景下，非分区表与分区表的执行计划对比：仅仅看扫描行数即可，分区表扫描记录数比非分区表要来的更少。

localhost:ytt> explain update t1 set r1 = date_sub(current_date,interval ceil(rand()*5000) day) where id between 1000001 and 2990000\G
*************************** 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
1 row in set, 1 warning (0.00 sec)

localhost:ytt> explain update p1 set r1 = date_sub(current_date,interval ceil(rand()*5000) day) where id between 1000001 and 2990000\G
*************************** 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)
  1. 针对运维来讲：

分区表数据与非分区数据进行交换。

分区表的特定分区数据可以很方便的导出导入，能够快速的与非分区表数据进行交换。

创建一张表 t_p1 ，用来和表 p1 的分区 p1 交换数据。

localhost:ytt> create table t_p1 like t1;
Query OK, 0 rows affected (0.06 sec)

分区 p1 本身包含了 100W 行记录。使用分区表原生数据交换功能来交换数据，只花了 0.07 秒。

localhost:ytt> alter table p1 exchange partition p1 with table t_p1;
Query OK, 0 rows affected (0.07 sec)

查看交换后的数据， 表 p1 少了 100W 行记录，分区 p1 被清空，表 t_p1 多了 100W 行记录。

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
|  9000000 |
+----------+
1 row in set (0.79 sec)

localhost:ytt> select count(*) from t_p1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.13 sec)

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

localhost:ytt> alter table p1 exchange partition p1 with table t_p1;
Query OK, 0 rows affected (0.77 sec)

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)

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

  1. 选择需要交换的互换表。
  2. 从原始表选出数据导入到互换表。
  3. 删除原始表涉及到的数据。

如果此时需要把换出去的数据重新换入原始表，则需要以上步骤反着再来一遍，增加运维难度并且操作低效。

分区表置换还有一个最大的优点，就是比非分区表记录的日志量要小的多。我们来重新把上面的置换操作做一次。删除所有二进制日志。

localhost:ytt>reset master;

Query OK, 0 rows affected (0.02 sec)

做一次分区置换

localhost:ytt>alter table p1 exchange partition p2 with table t_p1;
Query OK, 0 rows affected (2.42 sec)

再次做置换删除表 t_p1 数据

localhost:ytt>alter table p1 exchange partition p2 with table t_p1;
Query OK, 0 rows affected (0.45 sec)

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

localhost:ytt>show master status;
...
 ytt1.000001 ： 47d6eda0-6468-11ea-a026-9cb6d0e27d15:1-2

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

localhost:ytt>flush logs;
Query OK, 0 rows affected (0.01 sec)


localhost:ytt>insert into t_p1 select * from p1 partition (p2) ;
Query OK, 934473 rows affected (5.25 sec)
Records: 934473  Duplicates: 0  Warnings: 0


localhost:ytt>show master status;
...
 ytt1.000002 ： 47d6eda0-6468-11ea-a026-9cb6d0e27d15:1-3

来看看具体的日志文件，ytt1.000001 只占了588个字节，而 ytt1.000002 记却要占用 7.2M 。

root@ytt-pc:/var/lib/mysql/3306# ls -sihl ytt1.00000*
2109882 4.0K -rw-r----- 1 mysql mysql  588 7月  23 11:13 ytt1.000001
2109868 7.2M -rw-r----- 1 mysql mysql 7.2M 7月  23 11:14 ytt1.000002

快速清理单个分区数据。

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

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

localhost:ytt> alter table p1 truncate partition p0;
Query OK, 0 rows affected (0.07 sec)

localhost:ytt> select count(*) from p1;
+----------+
| count(*) |
+----------+
|  9000001 |
+----------+
1 row in set (0.92 sec)

非分区表只有 truncate 整张表的功能，所以无法对部分数据进行快速清理，只能根据过滤条件来 delete 数据，那这个性能就差了很多。同样的操作，比非分区表慢几十倍。

localhost:ytt> delete from t1 where id < 1000000;
Query OK, 999999 rows affected (26.80 sec)

总结：

MySQL 分区表在很多场景下使用非常高效，本篇介绍了分区表在简单检索与运维方面的基础优势，后续我们逐个来讨论更多场景下的分区表应用。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

相关推荐

每天一个编程技巧!掌握这7个神技，代码效率飙升200%

“同事6点下班，你却为改BUG加班到凌晨？不是你不努力，而是没掌握‘偷懒’的艺术！本文揭秘谷歌工程师私藏的7个编程神技，每天1分钟，让你的代码从‘能用’变‘逆天’。文末附《Python高效代码模板》，...

Git重置到某个历史节点(Sourcetree工具)

前言Sourcetree回滚提交和重置当前分支到此次提交的区别？回滚提交是指将改动的代码提交到本地仓库，但未推送到远端仓库的时候。...

git工作区、暂存区、本地仓库、远程仓库的区别和联系

很多程序员天天写代码，提交代码，拉取代码，对git操作非常熟练，但是对git的原理并不甚了解，借助豆包AI，写个文章总结一下。Git的四个核心区域（工作区、暂存区、本地仓库、远程仓库）是版本控制的核...

解锁人生新剧本的密钥:学会让往事退场

开篇：敦煌莫高窟的千年启示在莫高窟321窟的《降魔变》壁画前，讲解员指着斑驳色彩说："画师刻意保留了历代修补痕迹，因为真正的传承不是定格，而是流动。"就像我们的人生剧本，精彩章节永远...

Reset local repository branch to be just like remote repository HEAD

技术背景在使用Git进行版本控制时，有时会遇到本地分支与远程分支不一致的情况。可能是因为误操作、多人协作时远程分支被更新等原因。这时就需要将本地分支重置为与远程分支的...

Git恢复至之前版本（git恢复到pull之前的版本）

让程序回到提交前的样子：两种解决方法：回退（reset）、反做（revert）方法一：gitreset...

如何将文件重置或回退到特定版本（怎么让文件回到初始状态）

技术背景在使用Git进行版本控制时，经常会遇到需要将文件回退到特定版本的情况。可能是因为当前版本出现了错误，或者想要恢复到之前某个稳定的版本。Git提供了多种方式来实现这一需求。...

git如何正确回滚代码（git命令回滚代码）

方法一，删除远程分支再提交①首先两步保证当前工作区是干净的，并且和远程分支代码一致$gitcocurrentBranch$gitpullorigincurrentBranch$gi...

[git]撤销的相关命令:reset、revert、checkout

基本概念如果不清晰上面的四个概念，请查看廖老师的git教程这里我多说几句：最开始我使用git的时候，我并不明白我为什么写完代码要用git的一些列指令把我的修改存起来。后来用多了，也就明白了为什么。gi...

利用shell脚本将Mysql错误日志保存到数据库中

说明:利用shell脚本将MYSQL的错误日志提取并保存到数据库中步骤:1)创建数据库,创建表CreatedatabaseMysqlCenter;UseMysqlCenter;CREATET...

MySQL 9.3 引入增强的JavaScript支持

MySQL，这一广泛采用的开源关系型数据库管理系统（RDBMS），发布了其9.x系列的第三个更新版本——9.3版，带来了多项新功能。...

python 连接 mysql 数据库（python连接MySQL数据库案例）

用PyMySQL包来连接Python和MySQL。在使用前需要先通过pip来安装PyMySQL包：在windows系统中打开cmd,输入pipinstallPyMySQL ...

mysql导入导出命令（mysql 导入命令）

mysql导入导出命令mysqldump命令的输入是在bin目录下.1.导出整个数据库　　mysqldump-u用户名-p数据库名>导出的文件名　　mysqldump-uw...

MySQL-SQL介绍（mysql sqlyog）

介绍结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统,可以使用相同...

MySQL 误删除数据恢复全攻略:基于 Binlog 的实战指南

在MySQL的世界里，二进制日志（Binlog）就是我们的"时光机"。它默默记录着数据库的每一个重要变更，就像一位忠实的史官，为我们在数据灾难中提供最后的救命稻草。本文将带您深入掌握如...

一周热门
最近发表
标签列表
Copyright Your WebSite.Some Rights Reserved.