MySQLはどのように大きい表に索引をプラスします

最近、スロー・クエリー・ログでスローSQLが見つかりました.関連するビジネス・テーブルはデータの増加に伴って千万レベルに達し、インデックスを追加してクエリーを最適化する必要があります.
大きなテーブルにインデックスを付けたり、フィールドを付けたりするのはDDL(データ定義言語)操作であり、MySQLの大きなテーブルに対するDDL操作は警戒に値する.そうしないと、ロックテーブルを引き起こしたり、Waiting for meta data lockを間違えたりして、業務がクラッシュする可能性が高い.では、大きなテーブルにインデックスを付けるにはどうすればいいですか?
初期DDL原理
さらに、テーブルにインデックスを追加する方法について説明する前に、MySQL DDL操作がなぜテーブルをロックするのかについて説明します.この問題については、まずMySQL 5を理解する必要があります.6.7以前の初期DDL原理.
初期のDDL動作はcopy tableとinplaceの2つの方式に分けられる.
copy table方式

元のテーブルと同じテンポラリ・テーブルを作成し、テンポラリ・テーブルでDDL文

を実行します.

元のテーブルをロックし、DML(データ操作言語)を許可せず、クエリー

を許可する.

元のテーブルのデータを1行ずつテンポラリ・テーブルにコピーする(プロセスはソートされていない)

.

原表アップグレードロック、読み書き禁止、すなわち原表一時停止サービス

renameアクション、テンポラリ・テーブルの名前を元のテーブル

に変更
inplace方式(fast index creation、インデックスの作成と削除のみをサポート)

frm(テーブル構造定義ファイル)テンポラリファイル

を作成する

元のテーブルをロックし、DML(データ操作言語)を許可せず、クエリー

を許可する.

は、集約インデックス順に従って新しいインデックス項目を構築し、新しいインデックスページ

を順番に挿入する.

原表アップグレードロック、読み書き禁止、すなわち原表一時停止サービス

rename操作、元のテーブルのfrmファイル

を置き換える
早期copy VS inplace方式?
Inplace方式は、copy方式に比べて、inplaceではテンポラリ・テーブルが生成されず、データ・コピーが発生しないため、I/Oリソースの占有量が減少します.
Inplaceはインデックスの作成と削除にのみ適用され、他のクラスのDDL文には適用されません.
初期のcopyでも初期のinplace方式のDDLでも、ロックテーブル操作が行われ、DML操作は許可されず、クエリーのみが許可されます.
DDLの仕組みがわかり、次は「大きなテーブルをインデックス操作する方法」を理解します!
シナリオ1:「シャドウポリシー」
この方法は、『高性能MySQL』という本のシナリオから来ています.
方案の構想

元のテーブル(tb)と同じ構造の新しいテーブル(tb_new)

を作成する.

新しいテーブルにインデックスを作成する

元のテーブルを別のテーブル名(tb=>tb_tmp)に名前変更し、新しいテーブルを元のテーブル名(tb_new=>tb)に名前変更すると、新しいテーブル(tb)がビジネス

を担当する.

は元のテーブル(tb_tmp)のための新規インデックス

である.

交換表、新表は当初の名称(tb=>tb_new)に、原表は当初の名称(tb_tmp=>tb)に、原表(tb)は業務を再担当

新しいテーブルデータを元のテーブル(すなわち、新しいテーブルを業務期間中に生成するデータと元のテーブルに負担する)

にインポートする.
実践方法
SQL実装:

#   sql      

create table tb_new like tb;

alter table tb_new add index idx_col_name (col_name);

rename table tb to tb_tmp, tb_new to tb;

alter table tb_tmp add index idx_col_name (col_name);

rename table tb to tb_new, tb_tmp => tb;

insert into tb (col_name1, col_name2) select col_name1, col_name2 from tb_new;

シャドウポリシーにはどのような問題がありますか?
ステップ3以降、新しいテーブルが元のテーブル名に変更された後(tb)に業務を引き受け始め、ステップ3から終了までの間に新たに生成されたデータはすべて新しいテーブルに存在するが、古いデータを修正または削除する業務があると実現できないため、ステップ3から締めくくりまでの間にデータ(更新と削除)が失われる可能性がある.
シナリオ2:pt-online-schema-change
PERCONAはMySQLを維持するいくつかの小さなツールを提供し、pt-online-schema-change(pt-oscと略称)は大きなテーブルを比較的安全にDDL操作するために使用することができる.
pt-online-schema-changeスキームは、3つのトリガ(DELETEUPDATEINSERTトリガ)を用いて「シャドウポリシー」に存在する問題を解決し、新しい古いテーブルのデータ同期時に発生するデータ変動も同期させることができる.
さぎょうげんり

元のテーブル構造と同じ新しいテーブルを作成する

新しいテーブルに対するDDL操作(インデックス付けなど)

元のテーブルに3つのトリガ(DELETEUPDATEINSERT)を作成し、元のテーブルが新しいテーブルにコピーされたとき(ステップ4)のデータ変更時の同期

に使用する.

元のテーブルデータを新しいテーブル

にデータブロックとしてコピーする

テーブル交換、元のテーブル名をoldテーブル、新しいテーブル名を元のテーブル名

に変更

古いテーブルを削除し、トリガ

を削除する.
使用方法
pt-online-schema-changeツールを使用してテーブルをロックせずにオンラインでMySQLテーブル構造を変更する文を参照
問題の疑惑
pt-online-schema-changeの原理解析と応用説明-問題解答参照
シナリオ3:ONLINE DDL
MySQL5.6.7以前はDDL実現メカニズムの限界のため、よく「影戦略」とpt-online-schema-change方案でDDL操作を行い、相対的な安全性を保証した.MySQL 5.6.7バージョンでは、オンラインDDL機能が新たに発売され、「ロックなし」DDLをサポートしています.5.7バージョンは成熟しているので、5.7以降はONLINE DDL機能を直接利用できます.
ONLINE DDLでのinplace方式は、rebuild tableとno-rebuild tableに分かれています.
Online DDL実行フェーズ
基本的には、初期化、実行、コミットの3つのフェーズに分けられます.
Initializationフェーズ
このフェーズでは、MDLリード・ロックが使用され、他の同時スレッドによるテーブル構造の変更を禁止します.このフェーズでは、ストレージ・エンジンの機能、文で指定された操作、およびユーザーが指定したALGORITHMおよびLOCKオプションを考慮して、操作中に許可される同時実行数を決定します.
Executionフェーズ
このフェーズを2つのステップに分けてPrepared and ExecutedこのフェーズでMDL書き込みロックが必要かどうかはInitializationフェーズ評価の要因に依存します.MDL書き込みロックが必要な場合は、Preparedプロシージャのみが短いMDL書き込みロックを使用します.最も時間がかかるのはExcutedプロシージャです.
Commit Table Definitionフェーズ
このフェーズでは、MDLリード・ロックがMDLライト・ロックにアップグレードされます.このフェーズは一般的に速いため、排他ロックの時間も短く、古いテーブル定義を新しいテーブル定義で置き換えます(rebuild tableの場合).
ONLINE DDLプロセス

操作テーブルに対応するMDL(metadata lock)書き込みロック

を取得する.

MDLライトロックは、MDLリードロック

に降格する.

DDL操作を本格的に行う

MDLリード・ロックは、MDLライト・ロック

にアップグレードされる

MDLロック

を解放する.
ステップ3ではDDL操作時にロックテーブルは行われず、DML操作が可能です.しかし、DMLの書き込みロックを持っている間にロックされている可能性があります.文章MySQL・ソース読解・白話Online DDLを参照してください.
ONLINE DDLサポート状況まとめ
記事MySQL・ソース読解・白話Online DDL参照
[おすすめ読書]
MySQL Online DDLについて MySQL・ソース読解・白話Online DDL
[参考]
大表索引付けスキーム pt-online-schema-changeツールを使用してテーブルをロックせずにオンラインでMySQLテーブル構造を変更する pt-online-schema-changeの原理解析と応用説明-問題解答 MySQL Online DDLの改善と応用 MySQL Online DDLについて MySQL・ソース読解・白話Online DDL MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations