なからなLife

おさらい

前のエントリでは「ALTER権限だけ」でできることをひたすら確認してきました。
こちらのエントリで検証する「パーティション操作以外」でいうと、「RENAME」以外のALTER TABLE操作は全部できる！という状況でした。

では、続きまして、パーティション操作についてのALTER TABLEが、どこまでできるか確認していきます。

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
（略）
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

なお、使用するユーザは前のエントリと同じです。

パーティション操作

パーティション操作をやるのに数値型の方が色々ラクなので、それ用のテーブルを別途つくることにします。
メインは「alttest_tbl_prt」で、EXCHANGE_PARTATIONの対象として「alttest_tbl_prt_ex」を用意します。

mysql> CREATE TABLE `alttest_tbl_prt` (
    ->   `col_1` int COLLATE utf8mb4_bin NOT NULL,
    ->   `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
    ->   PRIMARY KEY (`col_1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO alttest_tbl_prt (col_1,col_2) values (1,'aaaa'),(101,'bbbb'),(201,'cccc'),(301,'dddd');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from alttest_tbl_prt;
+-------+-------+
| col_1 | col_2 |
+-------+-------+
|     1 | aaaa  |
|   101 | bbbb  |
|   201 | cccc  |
|   301 | dddd  |
+-------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE `alttest_tbl_prt_ex` (
    ->   `col_1` int COLLATE utf8mb4_bin NOT NULL,
    ->   `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
    ->   PRIMARY KEY (`col_1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from alttest_tbl_prt_ex;
Empty set (0.01 sec)

このテーブルに対し、ALTERしか権限を持っていないユーザで、ALTER TABLEでパーティション操作系のコマンドを投げてみることにします。

mysql> ALTER TABLE alttest_tbl_prt
    -> PARTITION BY RANGE (col_1) (
    -> PARTITION p0 VALUES LESS THAN (100),
    -> PARTITION p1 VALUES LESS THAN (200),
    -> PARTITION p2 VALUES LESS THAN (300),
    -> PARTITION p3 VALUES LESS THAN (400));
Query OK, 4 rows affected (0.21 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (col_1)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt ADD PARTITION (PARTITION p4 VALUES LESS THAN (500));
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (col_1)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt TRUNCATE PARTITION p4;
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt ANALYZE PARTITION p4; 
ERROR 1142 (42000): SELECT, INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt CHECK PARTITION p4; 
ERROR 1142 (42000): SELECT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt OPTIMIZE PARTITION p4; 
ERROR 1142 (42000): SELECT, INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt REBUILD PARTITION p4; 
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE alttest_tbl_prt REPAIR PARTITION p4; 
ERROR 1142 (42000): SELECT, INSERT command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt EXCHANGE PARTITION p4 WITH TABLE alttest_tbl_prt_ex;
ERROR 1142 (42000): INSERT, CREATE, DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt DROP PARTITION p4; 
ERROR 1142 (42000): DROP command denied to user 'alttest'@'localhost' for table 'alttest_tbl_prt'

mysql> ALTER TABLE alttest_tbl_prt REORGANIZE PARTITION p0 INTO(
    ->         PARTITION n0 VALUES LESS THAN (50),
    ->         PARTITION n1 VALUES LESS THAN (100)
    -> );
Query OK, 1 row affected (0.13 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY RANGE (col_1)
(PARTITION n0 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (400) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (500) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt REMOVE PARTITIONING; 
Query OK, 4 rows affected (0.17 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt PARTITION BY HASH(col_1)  PARTITIONS 8;
Query OK, 4 rows affected (0.31 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY HASH (col_1)
PARTITIONS 8 */
1 row in set (0.00 sec)

mysql> ALTER TABLE alttest_tbl_prt COALESCE PARTITION 2;
Query OK, 4 rows affected (0.30 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl_prt\G
*************************** 1. row ***************************
       Table: alttest_tbl_prt
Create Table: CREATE TABLE `alttest_tbl_prt` (
  `col_1` int(11) NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY HASH (col_1)
PARTITIONS 6 */
1 row in set (0.00 sec)

パーティション操作では、
できるのは、
PARTITION BY：新規構成。非パーティション→パーティション。
REBUILD：再構成。データ再格納（デフラグ効果）
ADD：パーティションの追加
REORGANIZE：分割。データを失わずに、パーティションを分ける。
REMOVE：解除。パーティション構造自体の取りやめ
COALESCE：合体。HASHパーティションの分割数を減らす。

できないのは、
TRUNCATE：切り捨て。DROP権限が必要。
ANALYZE：キー分布を読み取って格納。SELECT権限と、その結果を保持するためのINSERT権限が必要。
CHECK：パーティションのエラーをチェック。読み取りのためのSELECT権限が必要。
OPTIMIZE：未使用領域を解放したりパーティションデータファイルをデフラグ。SELECT権限と、綺麗にしたデータのINSERT権限が必要。
REPAIR：修復。読み取って修復して保存する、ということで、SELECT, INSERT権限が必要。
EXCHANGE：パーティションとテーブルの交換。INSERT, CREATE, DROPが必要。
DROP：削除（データごと消す）。DROP権限が必要。

ということになりました。

ENGINE

最後に、ALTER TABLEのリファレンスページ上に記載がないけど重要な、ストレージエンジンの変更、および、デフラグに使用する「ENGINE」句。

mysql>  ALTER TABLE alttest_tbl ENGINE=MyISAM;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql>  ALTER TABLE alttest_tbl ENGINE=INNODB;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table alttest_tbl\G
*************************** 1. row ***************************
       Table: alttest_tbl
Create Table: CREATE TABLE `alttest_tbl` (
  `col_1` char(10) COLLATE utf8mb4_bin NOT NULL,
  `col_2` char(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

あっさり動きました。

これこそ、「裏でテーブル作ってデータの格納構造を変更したあとに、ポインタを切り替えている処理」だと思うのですが、DROP＆CREATEの処理とはまた異なるようです。

まとめ

ALTER」権限単独では、「RENAME」と「PARTITIONの一部」が実行不可能です。
内部的に他の権限（SELECT・INSERT・DROP・CREATE）を必要とする処理が行われているかどうかが分かれ目です。

個人的には、この検証で初めて叩いたコマンド、何に使うんだこれ？ってコマンドもあったので、面白かったです。

MariaDB&MySQL全機能バイブル

作者: 鈴木啓修,山田奈緒子
出版社/メーカー: 技術評論社
発売日: 2014/12/18
メディア: 単行本（ソフトカバー）
この商品を含むブログ (3件) を見る

なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLで、「ALTER権限だけ」でできることは何か？（2/2）

おさらい

パーティション操作

ENGINE

まとめ