Mysql--パーティションテーブルの管理とメンテナンス

44841 ワード
MySQL
テーブルのパーティションスキームを変更するにはalter tableとpartition_を使用します.options句でいいです.パーティションテーブルの作成時のcreate table文に似ています
テーブルの作成
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005)
    );

挿入データストアの作成
delimiter $$
drop procedure if exists pr_trb3$$
create procedure pr_trb3(in begindate date,in enddate date,in tabname varchar(40))
begin
    while begindatedo
        set @s=concat_ws(' ','insert into',tabname,'values(1,''fanboshi'',''',begindate,''')');
        prepare stmt from @s;
        execute stmt;
        drop prepare stmt;
        set begindate = date_add(begindate,interval 1 day);
    end while;
end$$

delimiter ;

ストアド・プロシージャを呼び出してデータを挿入
call pr_trb3('1985-01-01','2004-12-31','trb3');

データ分散の表示
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.00 sec)

パーティションスキームの変更
mysql> ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 4;
Query OK, 7304 rows affected (0.07 sec)
Records: 7304  Duplicates: 0  Warnings: 0

データの表示
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='trb3';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0   | `id` | NULL  |       7472 |
| p1   | `id` | NULL  |          0 |
| p2   | `id` | NULL  |          0 |
| p3   | `id` | NULL  |          0 |
+------+------+-------+------------+
4 rows in set (0.00 sec)

mysql> select 1826*4;
+--------+
| 1826*4 |
+--------+
|   7304 |
+--------+
1 row in set (0.00 sec)

count(*)行数が一致し、データに問題がないことを示しますがinformation_schema.partitionsが間違っているのはなぜか分からない.
For partitioned InnoDB tables, the row count given in the TABLE_ROWS column of the INFORMATION_SCHEMA.PARTITIONS table is only an estimated value used in SQL optimization, and is not always exact. 
mysql> select count(*) from trb3;
+----------+
| count(*) |
+----------+
|     7304 |
+----------+
  count(*)  7304，

パーティションスキームの再変更
ALTER TABLE trb3 
    PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005)
    );

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.00 sec)

データをなくしました.訂正、実際になくしていません、このinformation_shcema.partitions表が遅れていますので、あとで調べてください
mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.00 sec)

公式文書によると、This has the same effect on the structure of the table as dropping the table and re-creating it using CREATE TABLE trb 3 PARTION BY KEY(id)PARTIONS 2;つまりALTER TABLE trb 3 PARTITION BYはdrop tableと再create table trb 3 partition by key(id)partitions 2と同じですね.
ストレージエンジンの変更は、通常のテーブルと変わらない
mysql> drop table trb3;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005)
    ->     );
Query OK, 0 rows affected (0.03 sec)

mysql> call pr_trb3('1985-01-01','2004-12-31','trb3');
Query OK, 0 rows affected (1.69 sec)

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.01 sec)

mysql> alter table trb3 engine=myisam;
Query OK, 7304 rows affected (0.02 sec)
Records: 7304  Duplicates: 0  Warnings: 0

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.01 sec)

mysql> show create table trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM) */
1 row in set (0.00 sec)

パーティション・テーブルから非パーティション・テーブルへのテーブルの変更
mysql> alter table trb3 remove partitioning;
Query OK, 7304 rows affected (0.01 sec)
Records: 7304  Duplicates: 0  Warnings: 0

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_name='trb3';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| NULL | NULL | NULL  |       7304 |
+------+------+-------+------------+
1 row in set (0.00 sec)

mysql> show create table trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Range Listパーティション管理
mysql> drop table trb3;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005)
    ->     );
Query OK, 0 rows affected (0.03 sec)

mysql> call pr_trb3('1985-01-01','2004-12-31','trb3');
Query OK, 0 rows affected (1.75 sec)

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.00 sec)

パーティションの追加
mysql> alter table trb3 add partition (partition p5 values less than(2010));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

パーティションのマージ
mysql> alter table trb3 reorganize partition p3,p5 into(partition p5 values less than(2010));
Query OK, 1826 rows affected (0.03 sec)
Records: 1826  Duplicates: 0  Warnings: 0

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_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 |
+------+------------------+-------+------------+
4 rows in set (0.00 sec)

ぶんれつぶんかつ
mysql> ALTER TABLE trb3 REORGANIZE PARTITION p5 INTO (
    ->     PARTITION p3 VALUES LESS THAN (2005),
    ->     PARTITION p4 VALUES LESS THAN (2010)
    -> );
Query OK, 1826 rows affected (0.04 sec)
Records: 1826  Duplicates: 0  Warnings: 0

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_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 |
+------+------------------+-------+------------+
5 rows in set (0.00 sec)

HASH KEY区分
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY hash( YEAR(purchased) ) 
    partitions 12;

mysql>call pr_trb3('1985-01-01','2004-12-31','trb3');

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_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 |
+------+------------------+-------+------------+
12 rows in set (0.00 sec)

パーティションを12～8個縮小
mysql> ALTER TABLE trb3 COALESCE PARTITION 4;
Query OK, 7304 rows affected (0.13 sec)
Records: 7304  Duplicates: 0  Warnings: 0

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_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 |
+------+------------------+-------+------------+
8 rows in set (0.00 sec)


mysql> select count(*) from trb3;
+----------+
| count(*) |
+----------+
|     7304 |
+----------+
1 row in set (0.00 sec)

データをなくしていない
収縮前の2004年はP 0
mysql> select mod(2004,12);
+--------------+
| mod(2004,12) |
+--------------+
|            0 |
+--------------+

収縮後の2004年はP 4
mysql> select mod(2004,8);
+-------------+
| mod(2004,8) |
+-------------+
|           4 |
+-------------+

Exchanging Partitions and Subpartitions with Tables
ぶんかつこうかん
 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

ptはパーティションテーブルであり、pはptのパーティションまたはサブパーティションであり、ntは非パーティションテーブルである.
制限条件:
1.テーブルntはパーティションテーブルではありません2.テーブルntは一時テーブルではありません3.テーブルptとnt構造は他の点で同じです4.テーブルnは外部キー制約もなく、他のテーブルが外部キーを参照していません5.テーブルntのすべての行はテーブルpのパーティション範囲内に含まれています(例えばp rangeパーティション最大values less than 10では、テーブルntは10以上の値を持つことはできません).
アクセス権:
ALTER、INSERT、and CREATE権限のほか、ALTER TABLE...EXCHANGE PARTITIONを実行するにはDROP権限が必要です.
その他の注意事項:
1.ALTER TABLEを実行…EXCHANGE PARTITIONはntテーブルとpテーブルのトリガを呼び出すことはありません2.交換テーブルのAUTO_INCREMENT列はreset 3.IGNOREキーワードでALTER TABLE…EXCHANGE PARTITIONを実行すると無効になります
完全なインスタンス文は次のとおりです.
ALTER TABLE pt 
    EXCHANGE PARTITION p 
    WITH TABLE nt;

一度のALTER TABLE EXCHANGE PARTITIONでは、1つのパーティションと1つの非パーティションテーブルしか交換できません複数交換したい場合は、ALTER TABLE EXCHANGE PARTITION任意のMySQLでサポートされているパーティションタイプを複数回実行して交換できます
交換インスタンス
CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES 
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

e構造と同じ非パーティションテーブルe 2を作成する
mysql> create table e2 like e;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table e2\G
*************************** 1. row ***************************
       Table: e2
Create Table: CREATE TABLE `e2` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> alter table e2 remove partitioning;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table e2\G
*************************** 1. row ***************************
       Table: e2
Create Table: CREATE TABLE `e2` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

eテーブルでのデータの分散を表示するには、次の手順に従います.
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='e'
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | 50       |          1 |
| p1   | id   | 100      |          0 |
| p2   | id   | 150      |          0 |
| p3   | id   | MAXVALUE |          3 |
+------+------+----------+------------+
4 rows in set (0.00 sec)

パーティションp 0をe 2テーブルと交換する:
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.01 sec)

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='e';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | 50       |          0 |
| p1   | id   | 100      |          0 |
| p2   | id   | 150      |          0 |
| p3   | id   | MAXVALUE |          3 |
+------+------+----------+------------+
4 rows in set (0.01 sec)

mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

実験をやり直して、今度交換する前に表e 2にいくつかのデータを挿入します
mysql> insert into e2 values(16,'FAN','BOSHI');
Query OK, 1 row affected (0.00 sec)

mysql> insert into e2 values(51,'DU','YALAN');
Query OK, 1 row affected (0.00 sec)

mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | FAN   | BOSHI |
| 51 | DU    | YALAN |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1737 (HY000): Found a row that does not match the partition

51がp 0の範囲を超えたため、新聞が間違っています.前にも言ったように、このときIGNOREを使っても始まらない
mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1737 (HY000): Found a row that does not match the partition

idを49に変更するとp 0の範囲になります
mysql> update e2 set id=49 where id=51;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.01 sec)

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_name='e';
+------+------+----------+------------+
| part | expr | descr    | table_rows |
+------+------+----------+------------+
| p0   | id   | 50       |          2 |
| p1   | id   | 100      |          0 |
| p2   | id   | 150      |          0 |
| p3   | id   | MAXVALUE |          3 |
+------+------+----------+------------+
4 rows in set (0.00 sec)

e2        p0 
mysql> select * from e partition(p0);
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | FAN   | BOSHI |
| 49 | DU    | YALAN |
+----+-------+-------+
2 rows in set (0.00 sec)

e p0           e2 
mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.01 sec)

交換サブパーティション
CREATE TABLE es (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id)
    SUBPARTITION BY KEY (lname)
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );


INSERT INTO es VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");


CREATE TABLE es2 LIKE es;


ALTER TABLE es2 REMOVE PARTITIONING;

各サブパーティションの名前を指定していませんがinformation_schema.partitionsテーブルサブパーティションの名前を取得
select 
  partition_name part, 
  subpartition_name, 
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_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 |
+------+-------------------+------+----------+------------+

次に、p 3 sp 0とesの交換を開始します
mysql> select * from es partition(p3sp0);
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from es partition(p3sp0);
Empty set (0.00 sec)

mysql> select * from es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

パーティション・テーブルにサブパーティションがある場合は、サブパーティションをパーティションとしてのみ交換できますが、サブパーティションの親パーティションを直接交換することはできません.
mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

EXCHANGE PARTITIONには、交換する2つのテーブルのカラム名、カラムの作成順序、カラムの数、インデックスが厳密に一致することが厳格に要求されています.もちろんストレージエンジンも一致しなければなりません
mysql> desc es2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fname | varchar(30) | YES  |     | NULL    |       |
| lname | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create index id_name on es2(id,fname);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
ERROR 1736 (HY000): Tables have different definitions

es 2のストレージエンジンを変更
mysql> drop index id_name on es2;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table es2 engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

パーティション・テーブルのメンテナンス
CHECK TABLE,OPTIMIZE TABLE,ANALYZE TABLE,and REPAIR TABLEは、パーティションテーブルのメンテナンスに使用することができる
Rebuilding partitions.は、パーティション内のデータdropを削除して挿入することに相当し、ディスクの破片を回避するのに有効ですExample:
ALTER TABLE t1 REBUILD PARTITION p0, p1;

Optimizing partitions.テーブルが追加された場合、大量のデータが削除されたり、大量のエッジ列の更新操作(VARCHAR、BLOB、or TEXT columns)が行われたりします.ではoptimize partitionは未使用のスペースを回収し、パーティションデータファイルを整理します.Example: 
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

OPTIMIZE PARTIONを運転するのはCHECK PARTION,ANALYZE PARTION,and REPAIR PARTIONに相当します
Some MySQL storage engines, including InnoDB, do not support per-partition optimization; in these cases, ALTER TABLE … OPTIMIZE PARTITION rebuilds the entire table. In MySQL 5.6.9 and later, running this statement on such a table causes the entire table to rebuilt and analyzed, and an appropriate warning to be issued. (Bug #11751825, Bug #42822) Use ALTER TABLE … REBUILD PARTITION and ALTER TABLE … ANALYZE PARTITION instead, to avoid this issue.
Analyzing partitions.パーティションのキー分布を読み込み保存Example:
ALTER TABLE t1 ANALYZE PARTITION p3;

破壊されたパーティションを修正する
ALTER TABLE t1 REPAIR PARTITION p0,p1;

Checking partitions.は、非パーティションテーブルに対してCHECK TABLEを使用するのとほぼ同じ方法でパーティションをチェックすることができる.Example: 
ALTER TABLE trb3 CHECK PARTITION p1;

このコマンドは、テーブルtrb 3のパーティションp 1のデータまたはインデックスが破壊されたかどうかを示すことができます.このような場合は、「ALTER TABLE…REPAIR PARTITION」を使用してパーティションを修正します.
以上の各コマンドは、パーティションをALLに変換することをサポートしています.
The use of mysqlcheck and myisamchk is not supported with partitioned tables.
mysqlcheckとmyisamchkはパーティションテーブルをサポートしていません
ALTER TABLE...TRUNCATE PARTION.を使用して、1つ以上のパーティションのデータを削除できます.たとえば、ALTER TABLE...TRUNCATE PARTION ALLはすべてのデータを削除します.
ANALYZE,CHECK,OPTIMIZE,REBUILD,REPAIR,and TRUNCATE操作はsubpartitionsをサポートしない.
Ajaxリクエストget方式
scrapy excel生成ツール(python 3)