三十八、MySQL基础系列笔记之MySQL分区管理

作者: 温新

分类: 【MySql】

阅读: 2000

时间: 2021-01-16 15:24:11

MySQL提供了添加、删除、重庆义、合并、拆分、交换分区的命令用于管理分区的操作,这些命令都使用alter table实现。

RANGE与LIST分区管理

对于添加、删除、重定义,RANGE与LIST是相似的。

删除分区

命令:alter table 表名 drop partition

创建分区表并写入数据

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 创建分区表</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo10(</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    name <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">varchar</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">20</span>) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">)<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">by</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">range</span> (id) (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p0 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">30</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">maxvalue</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 写入数据</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> demo10 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'lisi'</span>),(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'wangwu'</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'zhoaliu'</span>),(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">13</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'sunwang'</span>),(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">31</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'meili'</span>);</span>

id为1,2的数据写入到了p0分区,10,13的数据写入到了p1分区;31的数据写入到p2分区。

删除p2分区

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo10 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">drop</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p2;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.02</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">Records: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span>  Duplicates: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span>  <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Warnings</span>: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span></span>

注意:

1)删除分区后,并没有显示实际从表中删除了多少数据,并非没有数据被删除,而只要在p2分区中的数据全部被删除了。

2)不同的是,删除分区后,LIST分区中不同包含已经被删除了的分区值列表,因此无法写入含有已经删除了的分区的值列表数据。

新增分区

命令:alter table 表名 add partition

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo10 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">add</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> (<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p4 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">maxvalue</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> demo10 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">33</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'zi'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

LIST分区

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo11(</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">)<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">by</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">list</span>(id) (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p0 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 新增list分区</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo11 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">add</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span>(<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">5</span>));</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1495</span> (HY000): Multiple definition of same constant <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">list</span> partitioning</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 成功</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo11 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">add</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span>(<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">5</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">6</span>));</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.01</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">Records: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span>  Duplicates: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span>  <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Warnings</span>: <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span></span>

注意:LIST分区,第一次新增(4,5)时失败,因为4已经存在了。因此可以看出,新增LIST分区时,必须指定且只能指定一个唯一的分区。

重定义分区

命令:alter table 表名 reorganize partition into

以之前创建的emp表为列:

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Table</span>: <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">CREATE</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`emp`</span> (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`id`</span> <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">NOT</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`name`</span> <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">varchar</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">30</span>) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">DEFAULT</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`store_id`</span> <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">NOT</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">ENGINE</span>=<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">InnoDB</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">DEFAULT</span> <span style="box-sizing: border-box;color: rgb(128, 203, 196) !important">CHARSET</span>=utf8</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important">/*!50100 PARTITION BY RANGE (`store_id`)</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important">(PARTITION p0 VALUES LESS THAN (15) ENGINE = InnoDB,</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(98, 151, 85) !important">PARTITION p1 VALUES LESS THAN (35) ENGINE = InnoDB,</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(98, 151, 85) !important">PARTITION p2 VALUES LESS THAN (55) ENGINE = InnoDB,</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(98, 151, 85) !important">PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

下面案例中对p3分区进行了重新定义,将其拆分成了2个分区。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> emp <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">reorganize</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">65</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p4 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">maxvalue</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span>

既然可以拆分那么也可以合并,如p0为15,p1为35,那么合并之后是35,而非50

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> emp <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">reorganize</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p0,p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> less than(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">35</span>)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span>

HASH与KEY分区管理

HASH分区与KEY分区类型,管理操作也是类似。RANGE与LIST可以删除分区,不同的是HASH或KEY分区不能从表中删除分区,而是合并分区。

命令:alter table 表名 coalesce partition

案例:创建一张表hash分区数量为4的表,然后将分区修改为2个

先来创建一个HASH分区

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo12 (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    name <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">varchar</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">20</span>) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">not</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">null</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">)<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">by</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">hash</span>(id) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span>;</span>

修改分区为2个

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo12 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">coalesce</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 增加分区会报错</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">alter</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo12 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">coalesce</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partition</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">9</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1508</span> (HY000): Cannot <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">remove</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">all</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">partitions</span>, <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">use</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">DROP</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> instead</span>

coalesce不能用来增加分区,否则会报错。

交换分区

命令:ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

含义:将分区表pt中的一个分区或者子分区p中的数据和普通表nt中的数据进行交换。

交换分区必须满足如下条件:

  • 表nt不能是分区表。由于交换分区不能通过分区对分区的方式进行,若需求如此,可用一个普通表作为中间表,通过交换两次分区来实现;
  • 表nt不能是临时表;
  • 表pt和nt的结构完全一致(包括索引名称、索引列),表分区除外;
  • 表nt上不能有外键,也不能有其他表的外键依赖nt;
  • nt表的所有数据,应在分区p定义的范围内。若数据都在限定范围内可通过WITHOUT VALIDTION来跳过逐行验证。

交换分区案例

1)创建分区表并插入数据

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">CREATE</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> demo13(</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> id <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">INT</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">NOT</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">    name <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">VARCHAR</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">20</span>)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">)<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">PARTITION</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">BY</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">RANGE</span>(id) (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">PARTITION</span> p0 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">VALUES</span> LESS THAN(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">50</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">PARTITION</span> p1 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">VALUES</span> LESS THAN(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">100</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">PARTITION</span> p2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">VALUES</span> LESS THAN(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">150</span>),</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">PARTITION</span> p3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">VALUES</span> LESS THAN <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">MAXVALUE</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">INSERT</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">INTO</span> demo13 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">VALUES</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">160</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'lisi'</span>),(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">180</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'wangwu'</span>),(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">15</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'meili'</span>),(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2021</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'ziru'</span>);</span>

2)创建普通表

<span style="color: rgb(255, 255, 255);">CREATE TABLE demo14 LIKE demo13;</span><br></br>

3)将demo14分区表修改为非分区表

命令:ALTER TABLE 表名 REMOVE PARTITIONING

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">ALTER</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> demo14 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">REMOVE</span> PARTITIONING;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 查看结果,已经不是分区表</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">show</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> demo14;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+--------+-----------------------------------------------------------------------------------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Table</span>  | <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Table</span>                                                                                                    |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+--------+-----------------------------------------------------------------------------------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| demo14 | <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">CREATE</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`demo14`</span> (</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`id`</span> <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">int</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">NOT</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span>,</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">  <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`name`</span> <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">varchar</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">20</span>) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">NOT</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">NULL</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">ENGINE</span>=<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">InnoDB</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">DEFAULT</span> <span style="box-sizing: border-box;color: rgb(128, 203, 196) !important">CHARSET</span>=utf8 |</span>

4)查看demo13分区表中的数据分布

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">SELECT</span> partition_name,table_rows <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">FROM</span> information_schema<span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">.partitions</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">WHERE</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table_name</span>=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'demo13'</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| PARTITION_NAME | TABLE_ROWS |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p0             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p1             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p2             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p3             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span> rows <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

5)执行交换分区并查看交换之后的数据分布

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 开始交换</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">ALTER</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> demo13 EXCHANGE <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">PARTITION</span> p0 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">WITH</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">TABLE</span> demo14;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 查看分区表数据分布</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">SELECT</span> partition_name,table_rows <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">FROM</span> information_schema<span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">.partitions</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">WHERE</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table_name</span>=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'demo13'</span>;       </span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| PARTITION_NAME | TABLE_ROWS |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p0             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p1             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p2             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| p3             |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">4</span> rows <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 查看非分区表</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> * <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">from</span> demo14;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----+-------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| id | name  |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----+-------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">15</span> | meili |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----+-------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

交换分区的使用,可以方便地完成对包含大量数据的分区、子分区的备份,迁移等工作。

使用交换分区注意点:

  • 交换分区不会触发任何被交换的表或分区上的触发器;

  • 表中自增列的值会被重置;

  • 交换分区的命令中,IGNORE关键字不会产生影响。

期待和优秀的你一起同行!

2021年01月16日

请登录后再评论