MYSQL の大きなテーブルでフィールドの変更が遅いという問題を解決する方法

大きなテーブルの場合、MYSQL の ALTER TABLE 操作のパフォーマンスが大きな課題になります。 MYSQL がほとんどのテーブル構造変更操作を実行する方法は、新しいテーブル構造で空のテーブルを作成し、古いテーブルからすべてのデータを取得し、それを新しいテーブルに挿入し、その後古いテーブルを削除することです。メモリが不十分で、テーブルが大きく、多くのインデックスがある場合、この操作は非常に時間がかかる可能性があります。 ALTER TABLE 操作は完了するまでに数時間、場合によっては数日かかることが多く、これは多くの人が経験したことのある状況です。

通常、ほとんどの ALTER TABLE 操作は MYSQL サービスを停止します。一般的なシナリオでは、使用できる手法は 2 つだけです。

• 1 つは、まずサービスを提供しないマシン上で ALTER TABLE 操作を実行し、次にメイン マシンと通信することです。サービスを提供するライブラリ Switch;

• もう 1 つの手法は「シャドウ コピー」です。シャドウ コピー手法には、新しいテーブルを作成し、目的のテーブル構造を操作してから、名前変更およびテーブルの削除操作を通じて 2 つのテーブルを交換するプロセスが含まれます。

すべての ALTER TABLE 操作でテーブルが再構築されるわけではありません。列のデフォルト値を変更または削除するには 2 つの異なる方法があり、1 つは高速で、もう 1 つは低速です。

映画のデフォルトのレンタル期間を 3 日間から 5 日間に変更するとします。以下は非常に遅い方法です:

mysql> ALTER TABLE film modify column rental_duration tinyint(3) not null default 5;

1000 回の読み取りと 1000 回の挿入の操作数は、「SHOW STATUS」から取得できます。つまり、列のデータ型、サイズ、NULL 属性を含むテーブル全体を新しいテーブルにコピーします。

理論的には、MYSQL は新しいテーブルを作成するステップをスキップできます。テーブルのデフォルト値は .frm ファイルに保存されるため、テーブル自体を変更せずにそのファイルを直接編集できます。この最適化方法は実現可能ですが、MYSQL は現在この方法を使用していないため、列の操作を変更するにはテーブルを再構築する必要があります。

もう 1 つの方法は、ALTER COLUMN 操作を通じて列のデフォルト値を変更することです。

mysql> ALTER TABLE film ALTER COLUMN rental_duration set DEFAULT 5;

このステートメントは、テーブル データを関与させずに .frm ファイルを直接変更します。したがって、この操作は非常に高速です。

.frm ファイルのみを変更する

テーブルの .frm ファイルを変更するのは非常に高速ですが、MySQL はテーブルを不必要に再構築することがあります。上記の例から学ぶことができます。ある程度のリスクを冒すことで、MYSQL はテーブルを再構築せずに他のタイプの変更を行うことができるようになります。

以下に示す手法は正式にサポートまたは文書化されていないため、正しく動作しない可能性があることに注意してください。これらの手法は自己責任で使用してください。 > 実行前にデータをバックアップすることをお勧めします。

次の操作ではテーブルの再構築が必要ない場合があります:

• 列の AUTO_INCREMENT 属性を削除します (追加ではありません)。

• ENUM と SET を常にオンに追加、削除、または変更します。すでに行で使用されている定数値を削除すると、クエリは空の文字列を返します。

手順:

• 同じ構造を持つ空のテーブルを作成し、必要な変更を加えます (例: ENUM 定数の追加)。

• 読み取りロックを使用してフラッシュ テーブルを実行します。これにより、使用中のすべてのテーブルが閉じられ、テーブルが開かれなくなります。

• .frm ファイルを交換します。

• UNLOCK TABLES を実行して、2 番目のステップで読み取りロックを解放します。

次は、映画テーブルの評価列に定数を追加する例です。現在の列は次のようになります:

mysql> SHOW COLUMNS FROM film LIKE 'rating';

Field	Type	Null	Key	デフォルト	Extra
評価	enum('G','PG','PG-13','R','NC - 17')	YES		G

#仮説映画に対してより慎重な親のために、PG-14 の映画評価を追加する必要があります。

mysql> CREATE TABLE film_new like film;
mysql> ALTER TABLE film_new modify column rating ENUM('G','PG','PG-13','R','NC-17','PG-14') DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;

定数リストの最後に新しい値を追加していることに注意してください。たとえば、PG-13 の後に新しい値を中央に置くと、既存のデータの意味が変更されます。既存の R 値は PG-14 になり、既存の NC-17 は R になります。お待ちください。

次に、オペレーティング システム コマンドを使用して .frm ファイルを交換します:

/var/lib/mysql/sakila# mv film.frm film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

Mysql コマンド ラインに戻り、テーブルのロックを解除して、変更の効果を確認できます:

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM film like 'rating'\G

****************** 1.行**********************

フィールド: 評価

タイプ: enum('G','PG','PG-13','R','NC-17','PG-14')

最後に行う必要があるのは、この操作を完了するために作成された補助テーブルを削除することです:

mysql> DROP TABLE film_new;

以上がMYSQL の大きなテーブルでフィールドの変更が遅いという問題を解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。