MySQLオンラインDDLの使用に関する詳細な説明

文章

オンライン DDL は MySQL 5.6 でのみサポートされています。バージョン 5.5 以前では、alter table/create index などのコマンドを使用してテーブル構造を変更するとテーブルがロックされますが、これは明らかに実稼働環境では受け入れられません。

MySQL 5.7では、オンラインDDLはパフォーマンスと安定性の面で継続的に最適化されています。パフォーマンス上の利点が大きく、業務負荷への影響が少なく、ダウンタイムを制御できます。pt-osc/gh-ostと比較して、サードパーティの依存パッケージのインストールは不要です。また、インプレースアルゴリズムのオンラインDDLもサポートしています。テーブルをコピーする必要がないため、必要なディスク容量も小さくなります。

まず、一般的な DDL ステートメントを見てみましょう。

ALTER TABLE tbl_name ADD PRIMARY KEY (列)、ALGORITHM=INPLACE、LOCK=NONE;

このうち、LOCK は DDL 操作時の同時実行の度合いを記述し、ALGORITHM は DDL の実装方法を記述します。

LOCKパラメータ

1. LOCK=NONE: 同時クエリとDML操作を許可する
2. LOCK=SHARED: 同時クエリを許可しますが、DML操作はブロックします
3. LOCK=DEFAULT: システムによって決定される、可能な限りの同時実行性 (同時クエリ、DML、またはその両方) を許可します。 LOCK句を省略した場合は、LOCK=DEFAULTを指定するのと同じになります。
   
4. LOCK=EXCLUSIVE: 同時クエリと DML 操作をブロックします。
   

アルゴリズムパラメータ

1. ALGORITHM=COPY: pt-osc/gh-ost と同様に、コピー メソッドを使用してテーブルを変更します。
2. ALGORITHM=INPLACE: エンジン層のデータのみを変更する必要があり、サーバー層は関係ありません。
   

COPY TABLE プロセス

1. まず、ALTAR TABLE によって構造が変更された一時テーブルを作成します。
2. 元のテーブルのデータを一時テーブルにインポートします（サーバー層によって一時テーブルが作成され、IBDファイルが表示されます）
3. 元のテーブルを削除する
4. 一時テーブルの名前を元のテーブル名に変更します
   

同時に、この処理中にデータの一貫性を保つために、中間データのコピー（Copy Table）中はテーブルがロックされ、読み取り専用になります。書き込み要求が来た場合、サービスが提供できなくなり、接続数が爆発的に増加します。

IN-PLACEプロセス

1. 一時ファイルを作成し、元のテーブルの主キーのすべてのデータページをスキャンします。
2. データ ページ内の元のテーブル レコードを使用して B+ ツリーを生成し、一時ファイルに保存します (innodb_temp_data_file_path 一時テーブルスペースに一時ファイルを作成します)
3. 一時ファイルを生成する過程で、元のテーブルに対するすべての操作がログファイル（rowlog）に記録されます。
4. 一時ファイルが生成されたら、ログ ファイル内の操作を一時ファイルに適用して、データの点で元のテーブルと同一のテーブルを取得します。
5. データファイル（ログファイルは操作を記録および再生します）
6. 元のテーブルデータファイルを一時ファイルに置き換えます
   

このプロセスでは、ALTER ステートメントは開始時に MDL 書き込みロックを取得しますが、この書き込みロックは、データが実際にコピーされる前に読み取りロックに退化します。つまり、最も時間のかかる一時ファイルにデータをコピーするプロセス中に、元のテーブルを DML で操作できます。ロックされるのは、古いテーブルと新しいテーブル間の最終切り替えフェーズ中のみです。この名前変更プロセスは非常に高速です。

DMLとDDLの同時操作を許可する

• セカンダリインデックスを作成/追加する
• セカンダリインデックスの名前を変更する
• セカンダリインデックスの削除
• インデックスタイプの変更 ({BTREE | HASH} を使用)
• 主キーを追加する（コストが高い）
• 主キーを削除して別の主キーを追加します (コストが高い) (ALTER TABLE tbl_name DROP PRIMARY KEY、ADD PRIMARY KEY (column)、ALGORITHM=INPLACE、LOCK=NONE;)
• 新しいコラム（高額な費用）
• 列を削除する（コストが高い）
• 列の名前を変更する
• 列の並べ替え（コストが高い）
• 列のデフォルト値の変更
• 列のデフォルト値を削除する
• 列の自動増分値を変更する
• 列属性を null/not null に設定する (コストが高い)
• 列挙型またはコレクション列の定義を変更する
• ROW_FORMATを変更する
• キーブロックサイズを変更する
  

オンライン DDL は、コストが高いとマークされた操作には使用できますが、サーバーの IO と CPU に大きな負荷がかかります。また、レプリケーションのブロックも発生し、スレーブ レプリケーションの遅延という別の形を引き起こします。したがって、テーブルが大きい場合は、オフピーク時に実行することをお勧めします。

同時DMLを許可しないDDL操作

• 全文インデックスを追加する
• 空間インデックスの追加
• 主キーの削除
• 列のデータ型の変更
• 自動増分列を追加します (新しい列を追加 -> 自動増分列に変更)
• テーブル文字セットの変更
• データ型の長さを変更する
  • 特殊なケース: varchar 文字の長さが 10 から 255 未満に変更された場合、インプレース メソッドを使用するとテーブルはロックされません。ただし、255 から 10 に変更された場合はテーブルがロックされます。

上記はMySQL Online DDLの使用に関する詳細な説明の詳細な内容です。MySQL Online DDLの使用に関する詳細については、123WORDPRESS.COMの他の関連記事に注目してください。