InnoDBのクラスター化インデックスとクエリの最適化

最近、ネットワークはしばしばInnoDBおよびMySQLテーブルのクラスター化インデックスについて書き込みますが、それにもかかわらず、実際に使用されることはほとんどありません。

この記事では、クラスター化インデックスの機能の理解に基づいて、非常に複雑なBadooシステムを最適化した2つの実際の例を示します。



クラスタ化インデックス -テーブルをファイルに編成する形式。 InnoDBでは、データは通常のB-TREEキーと同じツリー内のツリーに保存されます。 InnoDBテーブル自体はすでに大きなB-TREEです。 キー値はクラスター化インデックスです。 ドキュメントによると、PRIMARY KEYがクラスター化インデックスとして選択されています。 主キーがない場合、最初の一意のキーが選択されます。 そうでない場合は、内部の6バイトコードが使用されます。



このようなディスク上のデータの組織から何が起こりますか？

1. キーを再構築する必要があるため、テーブルの中央に挿入すると時間がかかる場合があります。
2. 行のクラスター化インデックス値を更新すると、ディスク上の情報が物理的に転送されるか、断片化されます。
3. テーブルにすばやく挿入するために、クラスタ化インデックスの増え続ける値を使用する必要性。 最も最適なのは自動インクリメントフィールドです。
4. 各行には、一意の識別子値であるクラスター化インデックスがあります。
5. 二次キーは、単にこれらの一意の識別子を参照します。
6. 実際、KEY `key`（a、b、c）という形式の2次キーは、構造KEY` key`（a、b、c、clustered_index）を持ちます。
7. ディスク上のデータはクラスター化インデックスで並べ替えられます（SSDの例は考慮していません）。

この詳細については、 MySQL マニュアルを参照してください 。



スクリプトの作業を大幅に高速化するのに役立つ2種類の最適化について説明します。

テスト環境

調査の結果に対するキャッシュの影響を軽減するには、サンプルにSQL_NO_CACHEを追加します。また、各リクエストの前にファイルシステムキャッシュをフラッシュします。 そして、なぜなら データを実際にディスクからプルする必要がある最悪のケースに興味があります。各リクエストの前にMySQLを再起動します。



装備品

• インテル®Pentium®デュアルCPU E2180 @ 2.00GHz
• RAM DIMM 800 MHz 4Gb
• Ubuntu 11.04
• MySQL 5.5
• HDD Hitachi HDS72161

使用したスクリプトはGitHubで取得できます。

ディープオフの最適化

たとえば、ユーザー通信を含む抽象テーブルメッセージを考えます。

  CREATE TABLEメッセージ（ 
             message_id int not null auto_increment、 
             user1 intはnullではありません。 
             user2 intはnullではありません。 
             tsタイムスタンプnull以外のデフォルトcurrent_timestamp、 
            本文のロングテキストがヌルではありません。 
            主キー（message_id）、 
             KEY（user1、user2、ts） 
         ）エンジン= InnoDB 

InnoDBのリストされた機能を考慮して、この表を検討してください。

ここのクラスター化インデックスは、PRIMARY KEYと一致し、自動インクリメントフィールドです。 各行には4バイトの識別子があります。 テーブルに新しい行を挿入するのが最適です。 セカンダリキーは実際にはKEY（user1、user2、ts、message_id）であり、これを使用します。



テーブルに1億のメッセージを追加します。 これは、InnoDBの必要な機能を識別するのに十分です。 システムには10人のユーザーしかないため、対談者の各ペアには平均100万のメッセージがあります。



これらの10人のテストユーザーが多くのメッセージを交換し、しばしば古い通信を読み直したと仮定します。インターフェイスを使用すると、非常に古いメッセージのあるページに切り替えることができます。 そして、このインターフェースの背後には単純なリクエストがあります：



SELECT * FROM messages WHERE user1=1 and user2=2 order by ts limit 20 offset PageNumber*20







実際、最も一般的な要求。 オフセットの深さに応じて、実行時を見てみましょう。

オフセット	実行時間（ミリ秒）
100	311
1000	907
5000	3372
10,000	6176
20000	11901
30000	17057
40,000	21997
50,000	28268
60,000	32805

確かに多くの人が線形成長を期待しています。 しかし、6万件のレコードで33秒を取得するのは多すぎます！ 時間がかかることを説明するのは非常に簡単です。MySQL実装の機能の1つに言及するだけです。 実際、このクエリを読み取るMySQLは、ディスクから行のオフセット+制限を引き、それらから制限を返します。 これで状況は明らかです。この間、MySQLは6万件の不要な行をディスクから読み取っていました。 同様の状況で何をすべきか？ これには多くの異なる解決策が必要です。 ところで、ここに、これらのオプションに関する興味深い記事があります。



非常に簡単なソリューションが見つかりました。最初のクエリはクラスター化インデックス値のみを選択し、それらから排他的に選択しました。 クラスター化インデックス値はセカンダリキーの最後に存在することがわかっているため、リクエスト内の*をmessage_idに置き換えると、キーのみで機能するリクエストを取得します。そのようなリクエストの速度は高速です。



それは：

  mysql>は、user1 = 1およびuser2 = 2で、ts制限20オフセット20000によるメッセージからのselect *を説明します。
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- -------- +
 |  id |  select_type | テーブル| タイプ|  possible_keys | キー|  key_len |  ref | 行| エクストラ|
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- -------- +
 |  1 | シンプル| メッセージ|  ref |  user1 |  user1 |  8 |  const、const |  210122 |  whereを使用する|
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- -------- +
セット内の1行（0.00秒） 

次のようになりました：

  mysql>は、user1 = 1およびuser2 = 2のメッセージからselect message_idを説明します。
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- --------------------- +
 |  id |  select_type | テーブル| タイプ|  possible_keys | キー|  key_len |  ref | 行| エクストラ|
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- --------------------- +
 |  1 | シンプル| メッセージ|  ref |  user1 |  user1 |  8 |  const、const |  210122 |  whereを使用します。 インデックスを使用する|
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- --------------------- +
セット内の1行（0.00秒） 

この場合にインデックスを使用すると、MySQLはセカンダリキーからすべてのデータを取得でき、クラスター化インデックスにアクセスできなくなります。 詳細については、 こちらをご覧ください。



そして今、クエリで文字列値を直接抽出するだけです

SELECT * FROM messages WHERE message_id IN (....)







このソリューションの生産性を見てみましょう。

オフセット	実行時間（ミリ秒）
100	243
1000	164
5000	213
10,000	337
20000	618
30000	756
40,000	971
50,000	1225
60,000	1477

達成された結果は全員に適しているため、それ以上の検索を行わないことに決定しました。 さらに、履歴自体を操作する手順を変更せずに、原則としてこのデータに高速にアクセスできるかどうかは不明です。 タスクは、データ構造自体ではなく、特定のクエリを最適化することでした。

大きなテーブルを更新する手順を最適化する

1日1回、ユーザーに関する関連データを1つの大きなテーブルに収集する必要があるときに、2番目の最適化の必要性が生じました。 当時、1億3千万人のユーザーがいました。 すべてのデータベースをバイパスして新しいデータを収集するスクリプトは、30分で実行され、3000万の変更された行を選択します。 スクリプトの結果は、ハードドライブにシリアル化された新しい値を持つ数万のテキストファイルです。 各ファイルには、数百人のユーザーに関する情報が含まれています。



これらのテキストファイルからデータベースに情報を転送します。 ファイルを順番に読み取り、行を数千のパックにグループ化して更新します。 スクリプトの実行時間は3〜20時間です。 当然、このスクリプトの動作は受け入れられません。 さらに、プロセスを最適化する必要があることは明らかです。



最初に疑われたのは、データベースサーバーのディスク上の「寄生」負荷でした。 しかし、多数の観察からこの仮説の証拠は明らかにされていません。 私たちは、問題はデータベースの腸にあるという結論に達しました。そして、それをどう修正するかを考える必要があります。 データはディスク上にどのように配置されますか？ このデータを更新するには、OS、MySQL、およびハードウェアは何をしなければなりませんか？ これらの質問に答えている間に、データが収集されたのと同じ順序で更新されることに気付きました。 これは、各要求がこの大きなテーブル内の完全にランダムな場所を更新することを意味し、ディスクヘッドの配置時間の損失、ファイルシステムキャッシュの損失、データベースキャッシュの損失を伴います。



MySQLの各行を更新するプロセスは、値の減算、古い値と新しい値の比較、値の書き込みの3つの段階で構成されていることに注意してください。 これは、クエリの結果として、MySQLが一致した行数と実際に更新された行数に応答するという事実からも見ることができます。



次に、テーブル内で実際に変更される行の数を調べました。 3000万行のうち、変更されたのは300万行のみです（これは論理的です。なぜなら、テーブルにはユーザーに関する非常に切り捨てられた情報が含まれているからです）。 これは、MySQLが更新ではなく校正に費やす時間の90％を意味します。 ソリューションは単独で提供されました。クラスター化インデックスへのランダムアクセスがシーケンシャルインデックスにどのように失われるかを確認する必要があります。 結果は、テーブルを更新する場合に一般化できます（更新する前に、減算と比較が行われます）。



この手法は非常に単純です-クエリの実行速度の違いを測定します

SELECT * FROM messages where message_id in ($values)





ここで、値は10K要素の配列を渡します。 ランダムアクセスをチェックするには、要素値を完全にランダムにします。 シーケンシャルアクセスをテストするには、ランダムバイアスから始めて10K要素をシーケンシャルに作成する必要があります。

 関数getValuesForRandomAccess（）{ 
     $ arr = array（）; 
     foreach（範囲（1、10000）として$ i）{ 
         $ arr [] = rand（1,100000000）; 
     } 
     return arr; 
 } 

関数getValuesForSequencialAccess（）{ 
     $ r = rand（1、100000000-10000）; 
    戻り範囲（$ r、$ r + 10000）; 
 } 

ランダムおよびシーケンシャルリクエストの実行時間：

N	ランダム	シーケンシャル
1	38494	171
2	40409	141
3	40868	147
4	37161	138
5	38189	137
6	36930	134
7	37398	176
8	38035	144
9	39722	140
10	40720	146

ご覧のとおり、実行時間の差は200倍です。 したがって、私たちはそれのために戦わなければなりません。 実行を最適化するには、ソースデータを主キーで並べ替える必要があります。 ファイル内の3000万の値をすばやく整理できますか？ 答えは明確です-できます！



この最適化の後、スクリプトの実行時間は2.5時間に短縮されました。 3000万行の事前ソートには30分かかります（また、gzipはほとんどの時間がかかります）。

同じ最適化、ただしSSD

記事を書いた後、1つの余分なSSDを見つけ、その上でテストしました。



ディープオフセットサンプリング：

オフセット	実行時間（ミリ秒）
100	117
1000	406
5000	1681
10,000	3322
20000	6561
30000	9754
40,000	13039
50,000	16293
60,000	19472

最適化されたディープオフセットサンプリング：

オフセット	実行時間（ミリ秒）
100	101
1000	21
5000	24
10,000	32
20000	47
30000	94
40,000	84
50,000	95
60,000	120

ランダムアクセスとシーケンシャルアクセスの比較：

N	ランダム	シーケンシャル
1	5321	118
2	5583	118
3	5881	117
4	6167	117
5	6349	120
6	6402	126
7	6516	125
8	6342	124
9	6092	118
10	5986	120

これらの図は、SSDが従来のドライブよりも優れていることを示していますが、その使用は最適化の必要性を排除しません。



そして、この記事の結論として、データサンプリングレートを20倍に増やすことができたと言えます。 テーブルの実際の更新を最大10倍に加速しました（代理テストは200倍に加速しました）。 実験は、キャッシュが無効になっているシステムで実行されたことを思い出してください。 実際のシステムでのゲインはそれほど印象的ではありませんでした（キャッシュは状況をよく修正します）。



前述の結論は表面にあります。使用するソフトウェアの長所と短所を知るだけでは十分ではありません。この知識を実践できることが重要です。 MySQLの内部構造を知っていると、クエリを数十倍高速化できる場合があります。



Alexey alexxz Eremikhin、Badoo開発者