MySQLクエリーの最適化に最適なインデックスを選択

私たちの多くのユーザー、開発者、データベース管理者は、EverSQLに関するインデックス推奨アルゴリズムをチームに問い合わせ続けています.
だから、私たちはこの方面の内容を書くことにしました.
このチュートリアルでは、アルゴリズムのすべての内部特性を詳細に説明するのではなく、インデックスの最も重要な側面を簡単に説明します.さらに、最も重要なのは、推測ではなくルールに基づいて実用的な例を提供し、テーブルとクエリーを正確にインデックスすることです.
このチュートリアルでは、MySQL、MariaDB、PerconaDBデータベースに注目します.これらの情報は、他のデータベース・ベンダーにも関係する可能性がありますが、場合によってはできない場合があります.
SQLクエリーのインデックスを作成しますか?
通常の経験則に従って、SQLクエリーを最適化しようとすると、次の手順に従って複合インデックスを構築できます.

まず、クエリーで使用されているすべてのテーブルをリストし、クエリー内のサブクエリーごとに独立したリストを作成します.2つのSELECTサブクエリを含むSELECTクエリがある場合は、参照されているテーブルを含む3つのリストを作成する必要があります.このプロシージャが終了すると、各クエリー・リストにテーブルごとにカラムのリストを追加することができます.

インデックスの一番左の列は、age=25などのクエリと等しく比較する必要があります.すべてのカラムが定数と比較される限り、複数のカラムを追加できます.

では、「範囲列(range column)」になる列を選択します.MySQLでは、インデックスごとに1つの範囲列しかサポートされていません.したがって、範囲演算子(<>,>)を使用して、テーブルにインデックスの次の列として追加する必要があります.範囲列の前に等しい列を追加する理由(スライドはMySQL最適化チームのメンバーによって作成されます).

クエリーに範囲列が存在しない場合は、GROUP BY句の列を追加できます.

クエリーに範囲列が存在せず、GROUP BY句がない場合は、ORDER BY句の列を追加できます.

場合によっては、MySQLが使用を選択することがあるため、ORDER BY句の列を保存するために独立したインデックスを作成することは意味があります.それでも、インデックスには、ORDER BY句のすべての列が含まれている必要があります.これらの列は、ORDER BY句で同じ順序(ASC/DESC)で指定されている必要があります.これは、WHERE複合インデックスではなく、データベースのオプティマイザがこのインデックスを選択することを保証するものではありませんが、試してみる価値があります.

最後に、SELECT句から関連する列を追加します.これにより、MySQLがインデックスを上書きインデックスとして使用できるようになります.上書きインデックスは、フィルタとクエリーの句のすべての列を含むインデックスです.このようなインデックスを使用すると、データベースはインデックスのみを使用してクエリーを実行でき、テーブルにアクセスする必要はありません.多くの場合、この方法は明らかに速い.

例を挙げて説明します.

SELECT id, first_name, last_name, age from employees where first_name = ‘John’ AND last_name = ‘Brack’ and age > 25 ORDER BY age ASC;

このクエリについては、
first_name
および
last_name
列が開始され、等号演算子と比較されます.次に、範囲条件と比較する
age
列.ここでは、age列がインデックスに含まれているため、ORDER BY句インデックスは必要ありません.最後に同様に重要なのは、SELECT句から
id
インデックスに移動してcoveringインデックスを生成します.
このクエリを正しくインデックスするには、employees(first_name,last_name,age,id)のインデックスを追加する必要があります.
以上は非常に簡略化された擬似コードアルゴリズムで、かなり簡単なSQLクエリーのために簡単なインデックスを構築することができます.このプロセスの自動化を実現する方法を探しており、独自のインデックスアルゴリズムとクエリー最適化のメリットを強化したい場合は、EverSQL Query Optimizerを試してみてください.
インデックス(またはSQLクエリーの作成)では何をすべきではありませんか?
クエリーとインデックス・テーブルの作成中にプログラマとデータベース管理者が遭遇する最も一般的なエラーを収集しました.
テーブル内の各カラムを個別にインデックス化
ほとんどの場合、MySQLはクエリ内で各テーブルに複数のインデックスを使用できません.したがって、テーブルの各カラムに個別のインデックスを作成する場合、データベースはインデックスを使用して検索操作の1つしか実行できませんが、残りの部分はインデックスを使用して実行できないため、著しく遅くなります.
単一カラムインデックスではなく、複合インデックス(後述)を使用することをお勧めします.
filtering条件のOR演算子
次のクエリ文を考慮します.

SELECT a, b FROM tbl WHERE a = 3 OR b = 8

多くの場合、MySQLはOR条件を適用するためにインデックスを使用できないため、このクエリはインデックスできません.したがって、このOR条件を回避し、クエリーを2つの部分に分割し、UNIODISTINCTと組み合わせて使用することをお勧めします(または、重複した結果がないことを知らないようにUNIOALLを使用することが望ましいです).
インデックス内のカラムの順序は非常に重要です
例えば、私は私の連絡先の電話帳をあなたに渡して、電話帳は連絡先の名前によって並べ替えて、電話帳の中でどれだけの人が「ジョン」であるかを見つけるように要求します.電話帳に出て「大丈夫」と言います.ジョンで始まるすべての名前を含むページが見つかり、ここからカウントされます.
今、私がタスクを変更して、連絡先の姓順の電話帳をあげたとしますが、「John」という名前のすべての連絡先を統計するように要求します.どうするの?同じように、データベースもこの場合は困ります.ここでは、MySQLオプティマイザを使用する場合と同じ動作を示すSQLクエリーを見てみましょう.

SELECT first_name, last_name FROM contacts WHERE first_name = ‘John’;

インデックスを持つ連絡先(first_name,last_name)は、インデックスがフィルタ条件から始まり、SELECT句で別の列で終わるので、ここで理想的です.
ただし、逆インデックスを持つ連絡先(last_name,first_name)は、データベースがインデックスフィルタリングを使用できないため、カラムとしてインデックスの2番目であり、1番目ではないことが必要です.
この例の結論は,インデックス内のカラム順序が非常に重要である.
冗長インデックスの追加
SQLクエリーを最適化しようとすると、インデックスは非常に意味があり、パフォーマンスを大幅に向上させることができます.
しかし、これには不利な面もある.作成したインデックスは、更新を維持し、データベースで変更が発生したときに同期を維持する必要があります.したがって、データベース内のINSERT/UPDATE/DELETEごとに、すべての関連インデックスを更新する必要があります.この更新には、特に大きなテーブル/インデックスの場合、時間がかかる場合があります.
インデックスを作成しないでください.
また、削除可能な冗長インデックスを検索するために、データベースを一定期間分析することを強くお勧めします.