tl; drこの記事では、最も選択的な属性を複合インデックスプレフィックスからサフィックスに移動する方が良い場合を検討します。

また、 pipeline何か、それを使用して既にソートされたselectデータをselect方法も検討しselect 。

ドメインの説明

システムXにはイベントロガーがあります。このシステムからのログデータを表示するには、アプリケーションを作成する必要があります。

システムには独自のエラーコードと対応するメッセージがあると想定されています。 これらのうち、約10kは一意です。

メッセージには3つのタイプがあります。

• 通知 -通知、
• 警告 -警告
• エラー -クラント。

ログ表：

 create table `log` ( `message` text not null, `datetime` datetime not null, `type` enum('notice', 'warning', 'error') not null default 'notice' ); create index `datetime_message` on `log`(`datetime`, `message`(150)); 

テーブルには1000万のエントリがあります。 日時は常に一意ですが、 メッセージフィールドには一意のフィールドが1万個しかありません。

レコードを生成するために、ランダムログを生成するプロシージャを作成しました。 datetimeは、ログが1秒に1回書き込まれるかのように変化します。

 delimiter // create procedure `generate_logs`(`amount` int, `amountOfUniqueMessages` int) not deterministic modifies sql data sql security invoker begin declare i int default 1; set @datetime = cast(current_date as datetime) - interval 9 year; --    input_params: begin if (amount <= 0 or amountOfUniqueMessages <= 0) then leave input_params; --   end if; end; start transaction; --  [amountOfUniqueMessages]     datetime --        , --   datetime  interval- while i < amount DO set @message = concat('message ', i % amountOfUniqueMessages); insert into `log`(`message`, `datetime`) values (@message, @datetime + interval i second); end while; commit; end; // delimiter ; 

実装

アプリケーションユーザーの最も頻繁な操作は特定の日のログ全体の出力であると仮定します 。
これは、次のようなクエリを介して行うのが最適です

 select `message`, `datetime` from `log` where `datetime` >= '2017-04-01 00:00:00' and `datetime` < '2017-04-02 00:00:00' order by `datetime`; 

upd ：日付に関するアドバイスをVolChに感謝し、 <= '2017-04-01 23:59:59'を< '2017-04-02 00:00:00'に修正しました。 投稿へのコメントの詳細。

つまり 特定の日付のすべてのレコードを選択してソートします。 さらに、複合インデックスで日付が最初に来る場合、ソートする必要さえありません。ソートされた形式で返されます。

このクエリの説明は良い結果を示しています。

  id: 1 select_type: SIMPLE table: log partitions: NULL type: range possible_keys: datetime_message key: datetime_message key_len: 5 ref: NULL rows: 172242 filtered: 100.00 Extra: Using index condition 

影響を受ける172kフィールド。 これは、ロガーが毎秒データベースに何かを書き込むかのようにデータが生成された場合に、期待される結果です。

asc / descによる注文

並べ替えがdescendingであっても、フェッチデータは既に並べ替えられているため、 filesortで並べ替える必要はありません。

• order by ... descendingリクエスト：
  

   select `message`, `datetime` from `log` where `datetime` >= '2009-03-24 00:00:00' and `datetime` < '2009-03-25 00:00:00' order by `datetime` desc; 

• その説明：
  

    id: 1 select_type: SIMPLE table: log partitions: NULL type: range possible_keys: datetime_message key: datetime_message key_len: 5 ref: NULL rows: 172242 filtered: 100.00 Extra: Using index condition 

filesortなし、 temporaryなし。 すべてが最初の場合とまったく同じです。
この現象はパイプラインと呼ばれます。これは、データが次々にチェーンで接続されているかのように保存されるためです。 また、開始リンク（ order by asc ）とorder by ascリンク（ desc ）の両方から開始して、すべての値をストレッチできます。

複合インデックスでメッセージがどのようにソートされるかを理解するために、学校のクラスを想像できます。 各クラスの生徒は、AからZに分類されます。

1 "a"からすべての生徒をselectした場合、 filesortまたはtemporaryを使用せずに既に並べ替えられた状態をfilesortます。 使用されたものに関係なく、 ascendingまたはdescending ：

 select `surname` from `schoolkids` where `class` = '1' and `liter` = ''; 

戻ります

ただし、 explainすぐに不気味なUsing filesortまたはUsing temporary Using filesort両方のクラスのすべての生徒を取得して並べ替えるだけです。

 select `surname` from `schoolkids` where `class` = '1' and `liter` in ('', '') order by `surname` 

パイプラインで値を取得できなくなったため、これは明らかに発生しました。したがって、DBMSは自分で値をソートする必要があります。

別の例を見てみましょう：以前のリクエストをmessageでソートする必要があります 。 さらに、属性は既にソートされていますが、すでにインデックスプレフィックスに関連しています。 datetimeに関連します。

 select `message`, `datetime` from `log` where `datetime` >= '2009-03-24 00:00:00' and `datetime` < '2009-03-25 00:00:00' order by `message` desc; 

説明する：

  id: 1 select_type: SIMPLE table: log partitions: NULL type: range possible_keys: datetime_message key: datetime_message key_len: 5 ref: NULL rows: 172242 filtered: 100.00 Extra: Using index condition; Using filesort 

なぜfilesortですか？ 学童の例を思い出してください：30人の学生（インデックスサフィックス）が同じクラス（インデックスプレフィックス）にある場合、それらはパイプラインでソートされます。 ただし、複数のクラスを選択する場合は、それらを手動で並べ替える必要があります（ジャーナルを選択し、新しい用紙に1年生全員の新しい並べ替えリストを作成します）。 原則はここでは同じですが、 datetimeが完全に一意の属性であるという事実に合わせて調整されています（各クラスで1人の学生のみが学習しているという事実に相当）。 そのため、DBMSは独立した並べ替えを行う必要があります。 したがって、このリクエストでは、 filesortはどこからでも取得できない標準です。

すべてが動作しますが、突然...

ただし、 logテーブルで行われた最も一般的なsqlクエリを分析した後、アプリケーションで実行される最も一般的な操作は、特定の時間間隔なしで特定のメッセージとタイプでログを検索することです。
たとえば、メッセージ「 message 183 」ですべてのエラーを検索します 。
このようなリクエストは最適ではなくなり、完了するまで約30秒かかります。

 select `datetime`, `message` from `log` where `message` = 'message 183' and `type` = 'error'; 

このクエリについて説明すると、次の図が生成されます。

  id: 1 select_type: SIMPLE table: log partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10010745 filtered: 3.33 Extra: Using where 

これで、インデックスがまったく使用されないことが明らかになりました。 理解できる：インデックスの接尾辞で情報を検索するには高すぎます。

メッセージが最初の場所にあるように、インデックスの構造を変更する必要があると判断します。

 drop index `datetime_message` on `log`; create index `message_datetime` on `log`(`message`(150), `datetime`); 

これで、前のインデックスを持つデータベースを削除するクエリは非常に最適に見えます。

  id: 1 select_type: SIMPLE table: log partitions: NULL type: ref possible_keys: message_datetime key: message_datetime key_len: 452 ref: const rows: 1000 filtered: 100.00 Extra: Using where 

ただし、特定の日付にメッセージを受信するという古い要求は最適ではありません。
ただし、めったに実行されない場合は 、メインの最適化タスクが完了しているため、 サブ最適化のままにしておくことができます。すべての頻繁なデータベースクエリが最適化されます。

まとめると

常に最も選択的な列が複合インデックスプレフィックスに含まれている必要はありません。
テーブル内に多数の繰り返しがある属性が最も頻繁に選択される状況があります。 また、検索操作を実行するとインデックスツリーが完全に検索されるため、右側に配置しても意味がありません。

最も選択的な列を左に置くのは神話だと考える人がいます。
これを神話と呼ぶことは困難です。実際には、最も選択的な列が他の列よりも検索においてより多くの利点を提供するからです。

選択性に加えて、サブジェクトエリア自体に注意を払い、ドライデータだけでなく、そのすべての要件を最初に構築する必要があります。

便利なリンク

• 最も選択的な列を左側に配置することが神話である理由
• インデックスを使用して、ルーク！ これはおそらく、データベース設計の必需品です。 インデックスの最適な使用、クエリを使用した多数の例、さまざまなDBMSのチップについては、非常によく説明されています。
• もう一度、左側の最も選択的な列が神話である理由について