作成、追加、mysqlテーブルパーティションの削除

1、テストパーティションの追加と削除

###    range  
(1)      ：
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by range columns(from_date)
(partition p01 values less than ('1985-12-31'),
partition p02 values less than ('1990-12-31'),
partition p03 values less than ('1995-12-31'),
partition p04 values less than ('2000-12-31'),
partition p05 values less than ('2005-12-31'),
partition p06 values less than ('2010-12-31'),
partition p07 values less than ('2015-12-31'),
partition p08 values less than ('2020-12-31'),
partition p09 values less than ('2025-12-31'),
partition p10 values less than ('2030-12-31')
);
    
mysql> source titles.sql
 
(2)    ：
  ：    p04   ，        ，            
         n01 n02
 alter table titles
 reorganize partition p04 into(
 partition n01 values less than('1997-12-31'),
 partition n02 values less than('1998-12-31'),
 partition p04 values less than('2000-12-31')
 );
 
(3)    ：
         。
mysql> select count(*) from titles where from_date alter table titles drop partition p01;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> select count(*) from titles where from_date source titles.sql
 
(2)    
mysql> alter table titles add partition(partition p7 values in('CEO'));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
(3)    ：
mysql> alter table titles drop partition p0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
###    hash  
(1)  hash  
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by hash(emp_no)
partitions 4;
    ：
mysql> source titles.sql
 
(2)  hash  
4        ，4-2=2
mysql> alter table titles coalesce partition 2;
Query OK, 443308 rows affected (12.41 sec)
Records: 443308  Duplicates: 0  Warnings: 0
 
(3)  hash  
   5 ，2+3=5
mysql> alter table titles add partition partitions 3;
Query OK, 443308 rows affected (11.54 sec)
Records: 443308  Duplicates: 0  Warnings: 0

 ：  hash   ，mysql        ，        hash     ，           。

###    key  
(1)  key  
CREATE TABLE titles (
    emp_no      INT NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    KEY         (emp_no),
    PRIMARY KEY (emp_no,title, from_date)
) partition by key(emp_no)
partitions 4;
    
mysql> source titles.sql
 
(2)    ， hash
mysql> alter table titles coalesce partition 2;
Query OK, 443308 rows affected (7.86 sec)
Records: 443308  Duplicates: 0  Warnings: 0
 
(3)    ， hash
mysql> alter table titles add partition partitions 3;
Query OK, 443308 rows affected (6.17 sec)
Records: 443308  Duplicates: 0  Warnings: 0

2、すべてのデータがあるパーティションを問い合わせる

explain partitions
select * from user where id= 1001;

3、パーティションクエリーの指定

select * from user PARTITION(p0);