Mysql分区表的管理与维护
改变一个表的分区方案只需使用altertable加partition_options子句就可以了。和创建分区表时的createtable语句很像。
创建表
CREATETABLEtrb3(idINT,nameVARCHAR(50),purchasedDATE) PARTITIONBYRANGE(YEAR(purchased))( PARTITIONp0VALUESLESSTHAN(1990), PARTITIONp1VALUESLESSTHAN(1995), PARTITIONp2VALUESLESSTHAN(2000), PARTITIONp3VALUESLESSTHAN(2005) );
创建插入数据存储过程
delimiter$$
dropprocedureifexistspr_trb3$$
createprocedurepr_trb3(inbegindatedate,inenddatedate,intabnamevarchar(40))
begin
whilebegindate<enddate1=""begindate="date_add(begindate,interval"delimiter=""do=""drop=""end=""execute=""from=""insert=""pre=""prepare=""s="concat_ws('"set=""stmt=""><p>调用存储过程插入数据</p><preclass="brush:sql;">callpr_trb3('1985-01-01','2004-12-31','trb3');</pre>
<p>查看数据分布</p>
<preclass="brush:sql;">select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p3|YEAR(purchased)|2005|1826|
+------+------------------+-------+------------+
4rowsinset(0.00sec)</pre>
<p>改变分区方案</p>
<preclass="brush:sql;">mysql>ALTERTABLEtrb3PARTITIONBYKEY(id)PARTITIONS4;
QueryOK,7304rowsaffected(0.07sec)
Records:7304Duplicates:0Warnings:0</pre>
<p>查看数据</p>
<preclass="brush:sql;">select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='trb3';
+------+------+-------+------------+
|part|expr|descr|table_rows|
+------+------+-------+------------+
|p0|`id`|NULL|7472|
|p1|`id`|NULL|0|
|p2|`id`|NULL|0|
|p3|`id`|NULL|0|
+------+------+-------+------------+
4rowsinset(0.00sec)
mysql>select1826*4;
+--------+
|1826*4|
+--------+
|7304|
+--------+
1rowinset(0.00sec)</pre>
<p>count(*)行数一致，说明数据没出问题，但是information_schema.partitions查出来的不对<del>，这就不知道为什么了</del></p>
<blockquote>
<p>ForpartitionedInnoDBtables,therowcountgivenintheTABLE_ROWScolumnoftheINFORMATION_SCHEMA.PARTITIONStableisonlyanestimatedvalueusedinSQLoptimization,andisnotalwaysexact.</p>
</blockquote>
<preclass="brush:sql;">mysql>selectcount(*)fromtrb3;
+----------+
|count(*)|
+----------+
|7304|
+----------+
但是count(*)还是7304，什么鬼</pre>
<p>再次改变分区方案</p>
<preclass="brush:sql;">ALTERTABLEtrb3
PARTITIONBYRANGE(YEAR(purchased))(
PARTITIONp0VALUESLESSTHAN(1990),
PARTITIONp1VALUESLESSTHAN(1995),
PARTITIONp2VALUESLESSTHAN(2000),
PARTITIONp3VALUESLESSTHAN(2005)
);
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|0|
|p3|YEAR(purchased)|2005|0|
+------+------------------+-------+------------+
4rowsinset(0.00sec)</pre>
<p><del>丢数据了。。</del><br>
更正，实际没丢，这个information_shcema.partitions表有延迟，过一会再查就好了</p>
<preclass="brush:sql;">mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p3|YEAR(purchased)|2005|1826|
+------+------------------+-------+------------+
4rowsinset(0.00sec)</pre>
<p>官方文档说：<br>
Thishasthesameeffectonthestructureofthetableasdroppingthetableandre-creatingitusingCREATETABLEtrb3PARTITIONBYKEY(id)PARTITIONS2;<br>
就是说ALTERTABLEtrb3PARTITIONBY与droptable然后重新createtabletrb3partitionbykey(id)partitions2一样呢。</p>
<h3id="改存储引擎和普通表没啥区别">改存储引擎，和普通表没啥区别</h3>
<preclass="brush:sql;">mysql>droptabletrb3;
QueryOK,0rowsaffected(0.01sec)
mysql>CREATETABLEtrb3(idINT,nameVARCHAR(50),purchasedDATE)
->PARTITIONBYRANGE(YEAR(purchased))(
->PARTITIONp0VALUESLESSTHAN(1990),
->PARTITIONp1VALUESLESSTHAN(1995),
->PARTITIONp2VALUESLESSTHAN(2000),
->PARTITIONp3VALUESLESSTHAN(2005)
->);
QueryOK,0rowsaffected(0.03sec)
mysql>callpr_trb3('1985-01-01','2004-12-31','trb3');
QueryOK,0rowsaffected(1.69sec)
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p3|YEAR(purchased)|2005|1826|
+------+------------------+-------+------------+
4rowsinset(0.01sec)
mysql>altertabletrb3engine=myisam;
QueryOK,7304rowsaffected(0.02sec)
Records:7304Duplicates:0Warnings:0
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p3|YEAR(purchased)|2005|1826|
+------+------------------+-------+------------+
4rowsinset(0.01sec)
mysql>showcreatetabletrb3\G
***************************1.row***************************
Table:trb3
CreateTable:CREATETABLE`trb3`(
`id`int(11)DEFAULTNULL,
`name`varchar(50)DEFAULTNULL,
`purchased`dateDEFAULTNULL
)ENGINE=MyISAMDEFAULTCHARSET=utf8
/*!50100PARTITIONBYRANGE(YEAR(purchased))
(PARTITIONp0VALUESLESSTHAN(1990)ENGINE=MyISAM,
PARTITIONp1VALUESLESSTHAN(1995)ENGINE=MyISAM,
PARTITIONp2VALUESLESSTHAN(2000)ENGINE=MyISAM,
PARTITIONp3VALUESLESSTHAN(2005)ENGINE=MyISAM)*/
1rowinset(0.00sec)</pre>
<h3id="将表由分区表改为非分区表">将表由分区表改为非分区表</h3>
<preclass="brush:sql;">mysql>altertabletrb3removepartitioning;
QueryOK,7304rowsaffected(0.01sec)
Records:7304Duplicates:0Warnings:0
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------+-------+------------+
|part|expr|descr|table_rows|
+------+------+-------+------------+
|NULL|NULL|NULL|7304|
+------+------+-------+------------+
1rowinset(0.00sec)
mysql>showcreatetabletrb3\G
***************************1.row***************************
Table:trb3
CreateTable:CREATETABLE`trb3`(
`id`int(11)DEFAULTNULL,
`name`varchar(50)DEFAULTNULL,
`purchased`dateDEFAULTNULL
)ENGINE=MyISAMDEFAULTCHARSET=utf8
1rowinset(0.00sec)</pre>
<h3id="range-list分区管理">RangeList分区管理</h3>
<preclass="brush:sql;">mysql>droptabletrb3;
QueryOK,0rowsaffected(0.01sec)
mysql>CREATETABLEtrb3(idINT,nameVARCHAR(50),purchasedDATE)
->PARTITIONBYRANGE(YEAR(purchased))(
->PARTITIONp0VALUESLESSTHAN(1990),
->PARTITIONp1VALUESLESSTHAN(1995),
->PARTITIONp2VALUESLESSTHAN(2000),
->PARTITIONp3VALUESLESSTHAN(2005)
->);
QueryOK,0rowsaffected(0.03sec)
mysql>callpr_trb3('1985-01-01','2004-12-31','trb3');
QueryOK,0rowsaffected(1.75sec)
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p3|YEAR(purchased)|2005|1826|
+------+------------------+-------+------------+
4rowsinset(0.00sec)</pre>
<h4id="增加分区">增加分区</h4>
<preclass="brush:sql;">mysql>altertabletrb3addpartition(partitionp5valueslessthan(2010));
QueryOK,0rowsaffected(0.00sec)
Records:0Duplicates:0Warnings:0</pre>
<h4id="合并分区">合并分区</h4>
<preclass="brush:sql;">mysql>altertabletrb3reorganizepartitionp3,p5into(partitionp5valueslessthan(2010));
QueryOK,1826rowsaffected(0.03sec)
Records:1826Duplicates:0Warnings:0
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p5|YEAR(purchased)|2010|1826|
+------+------------------+-------+------------+
4rowsinset(0.00sec)</pre>
<h4id="分裂分区">分裂分区</h4>
<preclass="brush:sql;">mysql>ALTERTABLEtrb3REORGANIZEPARTITIONp5INTO(
->PARTITIONp3VALUESLESSTHAN(2005),
->PARTITIONp4VALUESLESSTHAN(2010)
->);
QueryOK,1826rowsaffected(0.04sec)
Records:1826Duplicates:0Warnings:0
select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|1990|1826|
|p1|YEAR(purchased)|1995|1826|
|p2|YEAR(purchased)|2000|1826|
|p3|YEAR(purchased)|2005|1826|
|p4|YEAR(purchased)|2010|0|
+------+------------------+-------+------------+
5rowsinset(0.00sec)</pre>
<h3id="hash-key分区">HASHKEY分区</h3>
<preclass="brush:sql;">CREATETABLEtrb3(idINT,nameVARCHAR(50),purchasedDATE)
PARTITIONBYhash(YEAR(purchased))
partitions12;
mysql>callpr_trb3('1985-01-01','2004-12-31','trb3');
select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|NULL|731|
|p1|YEAR(purchased)|NULL|365|
|p2|YEAR(purchased)|NULL|365|
|p3|YEAR(purchased)|NULL|365|
|p4|YEAR(purchased)|NULL|366|
|p5|YEAR(purchased)|NULL|730|
|p6|YEAR(purchased)|NULL|730|
|p7|YEAR(purchased)|NULL|730|
|p8|YEAR(purchased)|NULL|732|
|p9|YEAR(purchased)|NULL|730|
|p10|YEAR(purchased)|NULL|730|
|p11|YEAR(purchased)|NULL|730|
+------+------------------+-------+------------+
12rowsinset(0.00sec)</pre>
<h4id="缩建分区从12个到8个">缩建分区从12个到8个</h4>
<preclass="brush:sql;">mysql>ALTERTABLEtrb3COALESCEPARTITION4;
QueryOK,7304rowsaffected(0.13sec)
Records:7304Duplicates:0Warnings:0
select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='trb3';
+------+------------------+-------+------------+
|part|expr|descr|table_rows|
+------+------------------+-------+------------+
|p0|YEAR(purchased)|NULL|732|
|p1|YEAR(purchased)|NULL|1095|
|p2|YEAR(purchased)|NULL|1095|
|p3|YEAR(purchased)|NULL|1095|
|p4|YEAR(purchased)|NULL|1097|
|p5|YEAR(purchased)|NULL|730|
|p6|YEAR(purchased)|NULL|730|
|p7|YEAR(purchased)|NULL|730|
+------+------------------+-------+------------+
8rowsinset(0.00sec)
mysql>selectcount(*)fromtrb3;
+----------+
|count(*)|
+----------+
|7304|
+----------+
1rowinset(0.00sec)</pre>
<p>没丢数据</p>
<p>收缩前2004年在P0</p>
<preclass="brush:sql;">mysql>selectmod(2004,12);
+--------------+
|mod(2004,12)|
+--------------+
|0|
+--------------+</pre>
<p>收缩后2004年在P4</p>
<preclass="brush:sql;">mysql>selectmod(2004,8);
+-------------+
|mod(2004,8)|
+-------------+
|4|
+-------------+</pre>
<h3id="exchanging-partitions-and-subpartitions-with-tables">ExchangingPartitionsandSubpartitionswithTables</h3>
<h3id="分区子分区交换">分区(子分区)交换</h3>
<preclass="brush:sql;">ALTERTABLEptEXCHANGEPARTITIONpWITHTABLEnt</pre>
<p>pt是一个分区表，p是pt的分区或子分区，而nt是一个非分区表</p>
<h4id="限制条件">限制条件:</h4>
<p>1.表nt不是分区表<br>
2.表nt不是临时表<br>
3.表pt和nt结构在其他方面是相同的<br>
4.表n没有外键约束，也没有其他表引用它的列为外键<br>
5.表nt的所有行都包含在表p的分区范围内(比如prange分区最大valueslessthan10，那么表nt不能有大于等于10的值)</p>
<h4id="权限">权限:</h4>
<p>除了ALTER,INSERT,andCREATE权限外，你还要有DROP权限才能执行ALTERTABLE…EXCHANGEPARTITION.</p>
<h4id="其他注意事项">其他注意事项:</h4>
<p>1.执行ALTERTABLE…EXCHANGEPARTITION不会调用任何在nt表和p表上的触发器<br>
2.在交换表中的任何AUTO_INCREMENT列会被reset<br>
3.IGNORE关键字在执行ALTERTABLE…EXCHANGEPARTITION时会失效</p>
<h4id="完整实例语句如下">完整实例语句如下:</h4>
<preclass="brush:sql;">ALTERTABLEpt
EXCHANGEPARTITIONp
WITHTABLEnt;</pre>
<p>在一次ALTERTABLEEXCHANGEPARTITION中，只能有一个分区和一个非分区表被交换<br>
想交换多个，就执行多次ALTERTABLEEXCHANGEPARTITION<br>
任何MySQL支持的分区类型都可以进行交换</p>
<h4id="交换实例">交换实例</h4>
<preclass="brush:sql;">CREATETABLEe(
idINTNOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30)
)
PARTITIONBYRANGE(id)(
PARTITIONp0VALUESLESSTHAN(50),
PARTITIONp1VALUESLESSTHAN(100),
PARTITIONp2VALUESLESSTHAN(150),
PARTITIONp3VALUESLESSTHAN(MAXVALUE)
);
INSERTINTOeVALUES
(1669,"Jim","Smith"),
(337,"Mary","Jones"),
(16,"Frank","White"),
(2005,"Linda","Black");</pre>
<p>创建一个与e结构一样的非分区表e2</p>
<preclass="brush:sql;">mysql>createtablee2likee;
QueryOK,0rowsaffected(0.01sec)
mysql>showcreatetablee2\G
***************************1.row***************************
Table:e2
CreateTable:CREATETABLE`e2`(
`id`int(11)NOTNULL,
`fname`varchar(30)DEFAULTNULL,
`lname`varchar(30)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8
/*!50100PARTITIONBYRANGE(id)
(PARTITIONp0VALUESLESSTHAN(50)ENGINE=InnoDB,
PARTITIONp1VALUESLESSTHAN(100)ENGINE=InnoDB,
PARTITIONp2VALUESLESSTHAN(150)ENGINE=InnoDB,
PARTITIONp3VALUESLESSTHANMAXVALUEENGINE=InnoDB)*/
1rowinset(0.00sec)
mysql>altertablee2removepartitioning;
QueryOK,0rowsaffected(0.02sec)
Records:0Duplicates:0Warnings:0
mysql>showcreatetablee2\G
***************************1.row***************************
Table:e2
CreateTable:CREATETABLE`e2`(
`id`int(11)NOTNULL,
`fname`varchar(30)DEFAULTNULL,
`lname`varchar(30)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)</pre>
<p>查看数据在e表中的分布:</p>
<preclass="brush:sql;">select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='e'
+------+------+----------+------------+
|part|expr|descr|table_rows|
+------+------+----------+------------+
|p0|id|50|1|
|p1|id|100|0|
|p2|id|150|0|
|p3|id|MAXVALUE|3|
+------+------+----------+------------+
4rowsinset(0.00sec)</pre>
<p>将分区p0与e2表进行交换:</p>
<preclass="brush:sql;">mysql>ALTERTABLEeEXCHANGEPARTITIONp0WITHTABLEe2;
QueryOK,0rowsaffected(0.01sec)
select
partition_namepart,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='e';
+------+------+----------+------------+
|part|expr|descr|table_rows|
+------+------+----------+------------+
|p0|id|50|0|
|p1|id|100|0|
|p2|id|150|0|
|p3|id|MAXVALUE|3|
+------+------+----------+------------+
4rowsinset(0.01sec)
mysql>select*frome2;
+----+-------+-------+
|id|fname|lname|
+----+-------+-------+
|16|Frank|White|
+----+-------+-------+
1rowinset(0.00sec)</pre>
<p>重做实验，这次在交换前在表e2中插入一些数据</p>
<preclass="brush:sql;">mysql>insertintoe2values(16,'FAN','BOSHI');
QueryOK,1rowaffected(0.00sec)
mysql>insertintoe2values(51,'DU','YALAN');
QueryOK,1rowaffected(0.00sec)
mysql>select*frome2;
+----+-------+-------+
|id|fname|lname|
+----+-------+-------+
|16|FAN|BOSHI|
|51|DU|YALAN|
+----+-------+-------+
2rowsinset(0.00sec)
mysql>ALTERTABLEeEXCHANGEPARTITIONp0WITHTABLEe2;
ERROR1737(HY000):Foundarowthatdoesnotmatchthepartition</pre>
<p>报错了，因为51超出了p0的范围。<br>
如之前所说，此时使用IGNORE也无济于事</p>
<preclass="brush:sql;">mysql>ALTERIGNORETABLEeEXCHANGEPARTITIONp0WITHTABLEe2;
ERROR1737(HY000):Foundarowthatdoesnotmatchthepartition</pre>
<p>修改id为49，这样就属于p0的范围了</p>
<preclass="brush:sql;">mysql>updatee2setid=49whereid=51;
QueryOK,1rowaffected(0.00sec)
Rowsmatched:1Changed:1Warnings:0
mysql>ALTERTABLEeEXCHANGEPARTITIONp0WITHTABLEe2;
QueryOK,0rowsaffected(0.01sec)
mysql>select
->partition_namepart,
->partition_expressionexpr,
->partition_descriptiondescr,
->table_rows
->frominformation_schema.partitionswhere
->table_schema=schema()
->andtable_name='e';
+------+------+----------+------------+
|part|expr|descr|table_rows|
+------+------+----------+------------+
|p0|id|50|2|
|p1|id|100|0|
|p2|id|150|0|
|p3|id|MAXVALUE|3|
+------+------+----------+------------+
4rowsinset(0.00sec)
e2的数据被交换到了p0中
mysql>select*fromepartition(p0);
+----+-------+-------+
|id|fname|lname|
+----+-------+-------+
|16|FAN|BOSHI|
|49|DU|YALAN|
+----+-------+-------+
2rowsinset(0.00sec)
e的p0分区中的数据被交换到了e2中
mysql>select*frome2;
+----+-------+-------+
|id|fname|lname|
+----+-------+-------+
|16|Frank|White|
+----+-------+-------+
1rowinset(0.01sec)</pre>
<h4id="交换subpartition">交换subpartition</h4>
<preclass="brush:sql;">CREATETABLEes(
idINTNOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30)
)
PARTITIONBYRANGE(id)
SUBPARTITIONBYKEY(lname)
SUBPARTITIONS2(
PARTITIONp0VALUESLESSTHAN(50),
PARTITIONp1VALUESLESSTHAN(100),
PARTITIONp2VALUESLESSTHAN(150),
PARTITIONp3VALUESLESSTHAN(MAXVALUE)
);
INSERTINTOesVALUES
(1669,"Jim","Smith"),
(337,"Mary","Jones"),
(16,"Frank","White"),
(2005,"Linda","Black");
CREATETABLEes2LIKEes;
ALTERTABLEes2REMOVEPARTITIONING;</pre>
<p>尽管我们没有显示的指定每个子分区的名字，我们仍可以通过information_schema.partitions表获取到子分区的名字</p>
<preclass="brush:sql;">select
partition_namepart,
subpartition_name,
partition_expressionexpr,
partition_descriptiondescr,
table_rows
frominformation_schema.partitionswhere
table_schema=schema()
andtable_name='es';
+------+-------------------+------+----------+------------+
|part|subpartition_name|expr|descr|table_rows|
+------+-------------------+------+----------+------------+
|p0|p0sp0|id|50|1|
|p0|p0sp1|id|50|0|
|p1|p1sp0|id|100|0|
|p1|p1sp1|id|100|0|
|p2|p2sp0|id|150|0|
|p2|p2sp1|id|150|0|
|p3|p3sp0|id|MAXVALUE|3|
|p3|p3sp1|id|MAXVALUE|0|
+------+-------------------+------+----------+------------+</pre>
<p>接下来，开始将p3sp0和es进行交换</p>
<preclass="brush:sql;">mysql>select*fromespartition(p3sp0);
+------+-------+-------+
|id|fname|lname|
+------+-------+-------+
|1669|Jim|Smith|
|337|Mary|Jones|
|2005|Linda|Black|
+------+-------+-------+
3rowsinset(0.00sec)
mysql>ALTERTABLEesEXCHANGEPARTITIONp3sp0WITHTABLEes2;
QueryOK,0rowsaffected(0.00sec)
mysql>select*fromespartition(p3sp0);
Emptyset(0.00sec)
mysql>select*fromes2;
+------+-------+-------+
|id|fname|lname|
+------+-------+-------+
|1669|Jim|Smith|
|337|Mary|Jones|
|2005|Linda|Black|
+------+-------+-------+
3rowsinset(0.00sec)</pre>
<p>如果一个分区表有子分区，那么你只能以子分区为粒度进行交换，而不能直接交换子分区的父分区</p>
<preclass="brush:sql;">mysql>ALTERTABLEesEXCHANGEPARTITIONp3WITHTABLEes2;
ERROR1704(HY000):Subpartitionedtable,usesubpartitioninsteadofpartition</pre>
<p>EXCHANGEPARTITION有着严格的要求<br>
两个将要交换的表的列名，列的创建顺序，列的数量，以及索引都要严格一致。当然存储引擎也要一致</p>
<preclass="brush:sql;">mysql>desces2;
+-------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+-------------+------+-----+---------+-------+
|id|int(11)|NO||NULL||
|fname|varchar(30)|YES||NULL||
|lname|varchar(30)|YES||NULL||
+-------+-------------+------+-----+---------+-------+
3rowsinset(0.00sec)
mysql>createindexid_nameones2(id,fname);
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0
mysql>ALTERTABLEesEXCHANGEPARTITIONp3sp0WITHTABLEes2;
ERROR1736(HY000):Tableshavedifferentdefinitions</pre>
<p>改变es2的存储引擎</p>
<preclass="brush:sql;">mysql>dropindexid_nameones2;
QueryOK,0rowsaffected(0.00sec)
Records:0Duplicates:0Warnings:0
mysql>altertablees2engine=myisam;
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0
mysql>ALTERTABLEesEXCHANGEPARTITIONp3sp0WITHTABLEes2;
ERROR1497(HY000):ThemixofhandlersinthepartitionsisnotallowedinthisversionofMySQL</pre>
<h3id="分区表的维护">分区表的维护</h3>
<p>CHECKTABLE,OPTIMIZETABLE,ANALYZETABLE,andREPAIRTABLE可以被用于维护分区表</p>
<p>Rebuildingpartitions.相当于将分区中的数据drop掉再插入回来,对于避免磁盘碎片很有效<br>
Example:</p>
<preclass="brush:sql;">ALTERTABLEt1REBUILDPARTITIONp0,p1;</pre>
<p>Optimizingpartitions.如果你的表增加删除了大量数据，或者进行了大量的边长列的更新操作(VARCHAR,BLOB,orTEXTcolumns)。那么optimizepartition将回收未使用的空间，并整理分区数据文件。<br>
Example:</p>
<preclass="brush:sql;">ALTERTABLEt1OPTIMIZEPARTITIONp0,p1;</pre>
<p>运行OPTIMIZEPARTITION相当于做了CHECKPARTITION,ANALYZEPARTITION,andREPAIRPARTITION</p>
<blockquote>
<p>SomeMySQLstorageengines,includingInnoDB,donotsupportper-partitionoptimization;inthesecases,ALTERTABLE…OPTIMIZEPARTITIONrebuildstheentiretable.InMySQL5.6.9andlater,runningthisstatementonsuchatablecausestheentiretabletorebuiltandanalyzed,andanappropriatewarningtobeissued.(Bug#11751825,Bug#42822)UseALTERTABLE…REBUILDPARTITIONandALTERTABLE…ANALYZEPARTITIONinstead,toavoidthisissue.</p>
</blockquote>
<p>Analyzingpartitions.读取并保存分区的键分布<br>
Example:</p>
<preclass="brush:sql;">ALTERTABLEt1ANALYZEPARTITIONp3;</pre>
<p>Repairingpartitions.修补被破坏的分区<br>
Example:</p>
<preclass="brush:sql;">ALTERTABLEt1REPAIRPARTITIONp0,p1;</pre>
<p>Checkingpartitions.可以使用几乎与对非分区表使用CHECKTABLE相同的方式检查分区。<br>
Example:</p>
<preclass="brush:sql;">ALTERTABLEtrb3CHECKPARTITIONp1;</pre>
<p>这个命令可以告诉你表trb3的分区p1中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTERTABLE…REPAIRPARTITION”来修补该分区。</p>
<h4id="以上每个命令都支持将分区换成all">以上每个命令都支持将分区换成ALL</h4>
<blockquote>
<p>Theuseofmysqlcheckandmyisamchkisnotsupportedwithpartitionedtables.</p>
</blockquote>
<p>mysqlcheck和myisamchk不支持分区表</p>
<p>你可以使用ALTERTABLE…TRUNCATEPARTITION.来删除一个或多个分区中的数据<br>
如:ALTERTABLE…TRUNCATEPARTITIONALL删除所有数据</p>
<p>ANALYZE,CHECK,OPTIMIZE,REBUILD,REPAIR,andTRUNCATE操作不支持subpartitions.</p>
</enddate>
以上所述是小编给大家介绍的Mysql分区表的管理与维护，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！