MySQL パーティション テーブル パーティションのパーティション フィールドをオンラインで変更します。パーティションについては後で学習します (1)
- 黄舟オリジナル
- 2017-02-17 13:10:181714ブラウズ
その会社はオンラインでパーティションを使用していますが、1 つのテーブルのパーティション フィールドが間違っているため、再構築する必要があります。主キー フィールドを変更したり、インデックス フィールドを変更したりするなど、SQL で直接行う方法がないことがわかりました。代わりに、一時テーブルを構築する必要があり、ダウンタイムが発生するため、ドキュメントを注意深く読み、パーティションの詳細を検討しました。
私の会社がオンライン化したとき、業務が少ないピーク時間帯の午前 1 時、私は次のことを実行しました:
一時テーブルを作成する
CREATE TABLE tbname_TMP (
SHARD_ID INT NOT NULL,
...
xxx_DATE DATETIME NOT NULL,
PRIMARY KEY (xxx_DATE,shard_id)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY LIST(MONTH(xxx_DATE)) (
PARTITION m1 VALUES IN (1),
PARTITION m2 VALUES IN (2),
PARTITION m3 VALUES IN (3),
PARTITION m4 VALUES IN (4),
PARTITION m5 VALUES IN (5),
PARTITION m6 VALUES IN (6),
PARTITION m7 VALUES IN (7),
PARTITION m8 VALUES IN (8),
PARTITION m9 VALUES IN (9),
PARTITION m10 VALUES IN (10),
PARTITION m11 VALUES IN (11),
PARTITION m12 VALUES IN (12)
);テーブル名を切り替え、テーブル構造を変更する
RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;
元のデータ
insert into xxx select * from xxx_DELETEDxxx_DELETED;
OK、すべてが完了しました。プロセス全体には 50 分かかります。MMM フェイルオーバー切り替え後のアウトライン操作テーブル構造の変更とデータのインポート。実際のダウンタイムには、テーブル構造のパーティション フィールドを変更する時間は含まれません。 、フェイルオーバーの切り替え時間だけが 30 秒です
MySQL パーティション、私が読んだ英語の公式情報、翻訳レベルは限られており、一部は中国語に翻訳されておらず、英語で直接掲載されています。
1 リストパーティションテーブル
mysql> CREATE TABLE `eh` (
-> `id` int(11) NOT NULL,
-> `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,
-> `ENTITLEMENT_ID` bigint(20) NOT NULL,
-> `USER_ID` bigint(20) NOT NULL,
-> `DATE_CREATED` datetime NOT NULL,
-> `STATUS` smallint(6) NOT NULL,
-> `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
-> `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
-> `DATE_MODIFIED` datetime NOT NULL,
-> PRIMARY KEY (`DATE_MODIFIED`,`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
-> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))
-> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,
-> PARTITION m2 VALUES IN (2) ENGINE = InnoDB,
-> PARTITION m3 VALUES IN (3) ENGINE = InnoDB,
-> PARTITION m4 VALUES IN (4) ENGINE = InnoDB,
-> PARTITION m5 VALUES IN (5) ENGINE = InnoDB,
-> PARTITION m6 VALUES IN (6) ENGINE = InnoDB,
-> PARTITION m7 VALUES IN (7) ENGINE = InnoDB,
-> PARTITION m8 VALUES IN (8) ENGINE = InnoDB,
-> PARTITION m9 VALUES IN (9) ENGINE = InnoDB,
-> PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
-> PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
-> PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.10 sec)
2 ラングパーティションテーブル
mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,c) (
-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
-> PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.15 sec)
3 文字数を減らして範囲を作成する
CREATE TABLE employees_by_lname (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);テーブル構造を変更し、新しいパーティションブロックを追加する
4 リスト列のパーティション化
ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN ('u'),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);character column
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)日付列
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),
PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo')
);CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)5 ハッシュパーティション化
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
); PARTITIONS 句を含めない場合、パーティションの数はデフォルトで 1 になります。以下:
int column,it can use digital function
CREATE TABLE employeesint (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(MOD(store_id,4))
PARTITIONS 4;日付列
CREATE TABLE employeestest (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id);すべてのデータ行を切り詰めます: alter table rcx truncate PARTITION;
6 LINE AR ASH パーティショニング
CREATE TABLE employees2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;リニアハッシュによるパーティション( YEAR(hired) )
PARTITIONS 4;
式 expr を指定すると、線形ハッシュが使用されるときにレコードが保存されるパーティションは、num 個のパーティションの中のパーティション番号 N になります。ここで、N は次のアルゴリズムに従って導出されます。
(1) num より大きい次の 2 の累乗を求めます。この値を V と呼びます。これは次のように計算できます。
V = POWER(2, CEILING(LOG(2, num)))
(num が 13 であると仮定します)次に、LOG(2,13) は 3.7004397181411 です。CEILING(3.7004397181411) は 4、V = POWER(2,4)、つまり 16 となります。)
(2) N = F(column_list) & (V - を設定します) 1).
(3) While N >= num:
Set V = CEIL(V / 2)
Set N = N & (V - 1)
【注意】SQLにおける&の計算原理例:
10 進数を 2 進数に変換すると、次のようになります。
http://www.php.cn/
まずは右揃え、例えば0011と1000になります。それぞれの桁の数字で判断し、両方とも1であれば、結果の対応する位置は1になります。それ以外の場合は0です
1011と1000の場合、結果は1000です
0110と1010の場合、結果は0010です
でも、3は0011で8は1000なので、3&8の結果は0です
CEILING(X ) CEIL(X): X 以上の最小の整数値を返します。
LOG(X) LOG(B,X): 1 つのパラメーターを指定して呼び出された場合、この関数は X の自然対数を返します。
POWER(X,Y): X を Y 乗した結果の値を返します。
データがどのタイルに分散されるかの計算方法:
線形ハッシュ分割を使用し、6 つのパーティションを持つテーブル t1 が次のステートメントを使用して作成されたとします:
CREATE TABLE employees_linear (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)次に、2 つのレコードを挿入するとします。 Col3 列値 '2003-04-14' および '1998-10-19' を持つ t1 にこれらの最初のパーティション番号は次のように決定されます:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6; 2 番目のレコードが保存される場合は、次のように計算されます:
V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 is FALSE: record stored in partition #3)
線形ハッシュによるパーティション化の利点は、パーティションの追加、削除、マージ、分割が大幅に高速化されることです。これは、次の内容を含むテーブルを扱うときに有益です。非常に大容量 (テラバイト) のデータが存在するという欠点があります。
そのデータは、通常のハッシュ パーティショニングを使用して得られる分散と比較して、パーティション間で均等に分散される可能性が低くなります。
質問の 1 つは、MySQL は一時テーブルを使用せずにパーティション フィールドを削除するために SQL をどのように使用するのでしょうか?パーティション化されたテーブルを通常のテーブルに変更しますか?
上記は、MySQL パーティション テーブルのパーティション フィールドのオンライン変更です。後で、パーティション (1) の内容について詳しく学習します。詳細については、PHP 中国語 Web サイト ( www.php.cn)!