MySQLで、「ALTER権限だけ」でできることは何か?(2/2)
おさらい
前のエントリでは「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
なお、使用するユーザは前のエントリと同じです。
パーティション操作
パーティション操作をやるのに数値型の方が色々ラクなので、それ用のテーブルを別途つくることにします。
メインは「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)を必要とする処理が行われているかどうかが分かれ目です。
個人的には、この検証で初めて叩いたコマンド、何に使うんだこれ?ってコマンドもあったので、面白かったです。
- 作者: 鈴木啓修,山田奈緒子
- 出版社/メーカー: 技術評論社
- 発売日: 2014/12/18
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (3件) を見る