MySQL パフォーマンスの最適化: インデックスを効率的かつ正しく使用する方法

実践こそが真実をテストする唯一の方法です。この記事では、インデックスの全体的な使用法についてのみ説明しています。記事全体を読んで具体的な例と組み合わせたり、過去に使用された場所を思い出したりするだけで、全体を包括的に理解し、インデックスの仕組みを理解できます。今後インデックスを使用したり最適化したりするときに、これらの側面から始めると、インデックスの正確かつ効率的な使用をさらに深めることができます。

1. インデックスの失敗

インデックスの失敗はよく話題になります。データベースの最適化とインデックスの使用について言及する限り、インデックスが失敗するシナリオ、使用できないもの、使用すべきでないものを一挙に挙げることができます。ここではそれらを一つずつ挙げることはしません。

インデックス障害とは、テーブル内のフィールドに対してインデックスが作成されたものの、SQL ステートメントの記述が不適切であったためにインデックスが無効になった状況を指します。

SQL ステートメントでは、インデックス列が式の一部として使用されたり、関数や数学演算に使用されたりすると、インデックスは無効になります。

たとえば、次のクエリでは age 列のインデックスを使用できません。

age + 1 = 7 となる t_user から id、name、age を選択します。

where の式が実際には age=8 と同等であることは簡単にわかりますが、MySQL はこの式を自動的に解析できません。これは完全にユーザーの操作です。

(前回の記事では、MySQL が最初にインデックス上の値で検索し、次にインデックス値に対応するデータ行を返すことがわかりました。インデックス列が操作されると、対応するデータ行が正しく見つからないため、クエリの比較のためにテーブル全体が行ごとにスキャンされます)

2. プレフィックスインデックスとインデックスの選択性

非常に長いコンテンツを持つ列をインデックス列として使用すると、インデックスが非常に大きくなり、速度が遅くなる場合があります。この列にインデックスを追加する必要がある場合、解決策は前の記事で説明したシミュレートされたハッシュインデックスです。

通常、最初の数文字をインデックス化できるため、インデックススペースを大幅に節約し、インデックスの効率を向上させることができますが、インデックスの選択性も低下します。

インデックスの選択性は、一意のインデックス値の数（カーディナリティとも呼ばれる）とテーブルデータ内のレコードの総数 T の比率を指し、1/T から 1 の範囲になります。インデックスの選択性が高いほど、クエリの効率が高くなります。これは、選択性の高いインデックスを使用すると、MySQL が検索時により多くの行をフィルター処理できるためです。

ユニークインデックスの選択性は 1 であり、これは最高のインデックス選択性であり、最高のパフォーマンスを実現します。

BLOB、TEXT、または大きな VARCHAR タイプの列をクエリ条件として使用する場合 (原則として、このような操作は避けるべきですが、必要な場合もあります)、クエリパフォーマンスを向上させるために、列でプレフィックスインデックスを使用する必要があります。 MySQL ではこれらの列の全長のインデックス作成が許可されていないためです。

3. 複数列インデックス

複数列インデックスとは、各列ごとに独立したインデックスを作成することを意味します。

SQL を最適化するときに、クエリのパフォーマンスを最適化するために、where 条件内のすべての列にインデックスを付ける人もいます。しかし、実際には、このような最適化は非常に間違っています。最良の場合でも、「1 つ星」のインデックスしか得られず、そのパフォーマンスは、真に最適なインデックスよりも数データレベル低くなる可能性があります。場合によっては、「3 つ星」インデックスを設計できない場合は、where 句を無視してインデックス列の順序の最適化に重点を置くか、完全にカバーするインデックスを作成する方が適切です。

3 つ星インデックス: Lahdenmaki と Leach の共著『Relational Database Index Design and the Optimizers』では、インデックスがクエリに適しているかどうかを評価するための「3 つ星システム」について言及されています。インデックスが関連レコードをまとめている場合は「1 つ星」、インデックス内のデータの順序が検索の並べ替え順序と一致している場合は「2 つ星」、インデックス内の列にクエリに必要なすべての列が含まれている場合は「3 つ星」となります。

ほとんどの場合、複数の列に独立した単一列インデックスを作成しても、MySQL クエリのパフォーマンスは向上しません。これも間違ったアプローチです。

MySQL 5.0 以降のバージョンでは、テーブル上の複数の単一列インデックスを使用して、指定された行をある程度特定できるインデックスマージ戦略が導入されました。以前のバージョンの MySQL では、単一列インデックスの 1 つしか使用できませんでしたが、この場合、単一列インデックスはあまり効果的ではありませんでした。

インデックスのマージ戦略は最適化の結果である場合もありますが、多くの場合、テーブル上のインデックスが適切に構築されていないことを示しています。

1) 複数のインデックスに対して交差演算 (通常は複数の AND 条件を使用) が行われる場合、通常は、複数の独立した単一列インデックスではなく、関連するすべての列を含む複数列インデックスが必要であることを意味します。

2) 複数のインデックスを組み合わせる必要があるとき (通常は複数の OR 条件を使用)、アルゴリズムのキャッシュ、ソート、およびマージ操作で大量の CPU およびメモリリソースが消費されるのが一般的です。特に、一部のインデックスの選択性があまり高くなく、スキャンによって返された大量のデータをマージする必要がある場合に当てはまります。

3) オプティマイザーはこれらを「クエリコスト」に計算しません。オプティマイザーはランダムページ読み取りのみを考慮します。これにより、クエリコストが「過小評価」され、実行プランが直接のフルテーブルスキャンよりも悪くなります。これを行うと、CPU とメモリのリソースがさらに消費されるだけでなく、クエリの同時実行性にも影響する可能性があります。ただし、このようなクエリを個別に実行する場合、同時実行性への影響は無視されることがよくあります。

実行プラン EXPLAIN にインデックスマージが表示される場合は、クエリとテーブル構造が最適かどうかを確認する必要があります。また、optimizer_switch パラメータを使用してインデックスマージ機能をオフにしたり、IGNORE INDEX ヒントを使用してオプティマイザが特定のインデックスを無視するようにしたりすることもできます。

複数列のインデックスの場合、クエリ条件で左端の列が使用されている限り、通常、インデックスは無効になりません。

以下に例をいくつか示します。

テーブル t_user は、次のように複数列のインデックス (id、name) を作成します。

mysql> show テーブル t_user を作成します。
+--------+---------------+
| テーブル | テーブルの作成 |
+--------+---------------+
| t_user | テーブル `t_user` を作成します (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) デフォルト NULL,
`age` int(11) デフォルト NULL,
キー `idx` (`id`,`name`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=8 デフォルト CHARSET=utf8 |
+--------+------------------------------------------+
セット内の1行

次のように ID でクエリします。

mysql> explain select * from t_user where id = 1;
+----+-------------+---------+-----------+-------+---------------+-----+--------+-------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+-------+---------------+-----+--------+-------+------+------+------+
| 1 | SIMPLE | t_user | NULL | ref | idx | idx | 4 | const | 1 | 100 | NULL |
+----+-------------+---------+-----------+-------+---------------+-----+--------+-------+------+------+------+
セット内の1行

実行プランのタイプから、インデックスが有効であることがわかります。ただし、名前でクエリを実行すると、次のようにインデックスは失敗します (フルテーブルスキャン)。

mysql> explain select * from t_user where name = 'xcbeyond';
+----+-------------+---------+-----------+--------+---------------+-------+--------+----------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+--------+---------------+-------+--------+----------+------------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | where の使用 |
+----+-------------+---------+-----------+--------+---------------+-------+--------+----------+------------+-------------+
セット内の1行

4. 適切なインデックス列の順序を選択する

インデックス列の順序は非常に重要です。正しい順序は、インデックスを使用するクエリによって異なり、並べ替えとグループ化のニーズをより適切に満たす方法も考慮する必要があります (B ツリーインデックス、ハッシュ、またはその他のインデックスがデータを順番に格納しない場合にのみ適用されます)。

複数列の B ツリーインデックスでは、インデックス列の順序は、インデックスが最も左端の列から順にソートされることを意味します。したがって、列の順序に準拠する order by、group by、distinct などの句のクエリ要件を満たすために、インデックスを昇順または降順でスキャンできます。

したがって、複数列インデックス内の列の順序は非常に重要です。インデックス内の列の順序を選択する際の経験則は、最も選択性の高いインデックスを最初に配置することです。この経験はいくつかのシナリオでは非常に役立ちますが、通常はランダム IO とソートを回避することほど重要ではなく、問題をより包括的に検討する必要があります。

並べ替えやグループ化を気にする必要がない場合は、選択度が最も高い列を最初に配置するのが適切な場合がよくあります。このとき、インデックスの役割は、where 条件の検索を最適化することだけです。この場合、このように設計されたインデックスは、必要な行を可能な限り迅速にフィルタリングすることができ、また、where 句でインデックスのプレフィックス列のみを使用するクエリに対して選択性が高まります。ただし、パフォーマンスはすべてのインデックス列の選択性だけでなく、クエリ条件の特定の値、つまり値の分布にも依存します (この場合、インデックス列の選択性が最も高くなるように、最も頻繁に実行されるクエリに応じてインデックス列の順序を調整する必要があります)。

5. クラスター化インデックス

クラスター化インデックスは、独立したインデックスタイプではなく、データストレージとインデックスを組み合わせたデータストレージ方法です。インデックスページを見つけると、データが見つかります。正確な詳細は実装方法によって異なりますが、 InnoDBのクラスター化インデックスは実際には B ツリーインデックスとデータ行を同じ構造で格納します。

非クラスター化インデックス: データストレージとインデックスは別々に保存され、インデックス構造のリーフノードはデータの対応する行を指します。データにアクセスする必要がある場合（インデックス経由）、メモリ内で直接インデックスを検索し、その後、インデックスを介してディスク上の対応するデータを見つけます。これが、キーバッファーでインデックスがヒットしない場合に速度が遅くなる理由です。

テーブルにクラスター化インデックスがある場合、そのデータ行は実際にはインデックスのリーフページに格納されます。「クラスター化」とは、データ行と隣接するキー値がコンパクトにまとめて保存されることを意味します。データ行を同時に 2 つの異なる場所に格納することは不可能であるため、テーブルにはクラスター化インデックスを 1 つしか持つことができません。

クラスター化インデックスの設定:

デフォルトは主キーです。主キーが定義されていない場合、 InnoDB代わりに一意の空でないインデックスを選択します。そのようなインデックスがない場合、 InnoD暗黙的に主キーをクラスター化インデックスとして定義します。 InnoDB 、隣接するキー値を含むページが離れている場合でも、同じページにあるレコードのみをクラスター化します。

(これを見ると、B-Tree インデックス構造に精通していれば、[キー、データ] が 1 つのノードにタプルとして格納される理由がわかります)

クラスター化された主キーはパフォーマンスの向上に役立ちますが、重大なパフォーマンスの問題を引き起こす可能性もあります。したがって、特にテーブルのストレージエンジンを InnoDB から別のエンジンに変更する場合 (またはその逆) は、クラスター化インデックスを慎重に検討する必要があります。

クラスター化インデックスの利点:

関連データをまとめて保存できます。たとえば、電子メールメールボックスを実装する場合、ユーザー ID に基づいてデータを集約できるため、ユーザーのすべての電子メールを取得するには、ディスクから少量のデータを読み取るだけで済みます。
より高速なデータアクセス。クラスター化インデックスはインデックスとデータを同じ B ツリーに配置するため、クラスター化インデックスからデータを取得する方が、非クラスター化インデックスからデータを取得するよりも高速です。
カバーリングインデックススキャンを使用するクエリでは、ページノード内の主キー値を直接使用できます。

クラスター化インデックスの欠点:

I/O 集約型アプリケーションのパフォーマンスは最大化されますが、すべてのデータがメモリに格納されている場合、アクセスの順序はそれほど重要ではなく、クラスター化インデックスには利点がありません。
挿入速度は挿入順序に大きく依存します。主キーの順序で挿入するのが、InnoDB テーブルにデータをロードする最も速い方法です。ただし、データが段階的にロードされない場合は、ロードが完了した後に OPTIMIZE TABLE を使用してテーブルを再編成するのが最適です。
クラスター化インデックス列の更新にはコストがかかります。これは、InnoDB に更新された各行を新しい場所に移動させるように強制するためです。
クラスター化インデックスに基づくテーブルでは、新しい行が挿入されたとき、または主キーが更新されて行が移動したときに、「ページ分割」の問題が発生する可能性があります。行の主キー値により、行をページ全体に挿入する必要がある場合、ストレージエンジンは行を収容するためにページを 2 つのページに分割します。これはページ分割操作であり、テーブルがより多くのディスク領域を占有することを意味します。
クラスター化インデックスは、特に行がまばらな場合や、ページ分割によりデータが連続して保存されていない場合に、テーブル全体のスキャンの速度を低下させる可能性があります。
セカンダリインデックス (非クラスター化インデックス) は、予想よりも大きくなる可能性があります。セカンダリインデックスのリーフノードには、参照される行の主キー列が含まれているためです。
セカンダリインデックスアクセスでは、1 回ではなく 2 回のインデックス検索が必要です。

6. カバーインデックス

通常、クエリの where 条件に基づいて適切なインデックスが作成されますが、これはインデックス最適化の 1 つの側面にすぎません。適切に設計されたインデックスでは、where 条件だけでなく、クエリ全体を考慮する必要があります。インデックスは確かにデータを見つける効率的な方法ですが、MySQL ではインデックスを使用して列データを直接取得することもできるため、データ行を読み取る必要がなくなります。インデックスにクエリする必要があるすべてのフィールド値が含まれている場合、それを「カバーリングインデックス」と呼びます。つまり、インデックスは where 条件のすべての列をカバーします。

インデックスをカバーする利点は次のとおりです。

通常、インデックスエントリはデータ行のサイズよりもはるかに小さいため、インデックスのみを読み取る必要がある場合、MySQL はデータアクセスの量を大幅に削減できます。これは、応答時間の大部分がデータのコピーに費やされるキャッシュされたワークロードにとって特に重要です。カバーリングインデックスはデータよりも小さく、メモリに簡単に配置できるため、I/O を集中的に使用するアプリケーションにも役立ちます。
インデックスは列の値の順序で保存されるため、I/O 集約型の範囲クエリでは、ディスクから各データ行をランダムに読み取る場合よりも I/O が大幅に少なくなります。 MyISAMやPercona XtraDBなどの一部のストレージエンジンでは、 POTIMIZEコマンドを使用してインデックスを完全にソートすることも可能です。これにより、単純な範囲クエリで完全にソートされたインデックスアクセスを使用できるようになります。
MyISAMなどの一部のストレージエンジンは、インデックスのみをメモリにキャッシュします。データはオペレーティングシステムによってキャッシュされるため、データにアクセスするにはシステムコールが必要です。これは、特にシステムコールがデータアクセスにおける最大のコストを占めるシナリオでは、重大なパフォーマンスの問題を引き起こす可能性があります。
カバーリングインデックスは、 InnoDBのクラスター化インデックスのため、InnoDB テーブルに特に役立ちます。 InnoDB のセカンダリインデックスは、行のプライマリキー値をリーフノードに格納するため、セカンダリプライマリキーがクエリをカバーできる場合は、プライマリキーインデックスのセカンダリクエリを回避できます。

すべてのタイプのインデックスがカバーインデックスになるわけではありません。カバーリングインデックスはインデックス列を格納する必要がありますが、ハッシュインデックス、空間インデックス、および全文インデックスはインデックス列の値を格納しないため、MySQL は B-Tree を使用してのみカバーリングインデックスを作成できます。また、ストレージエンジンによってカバーリングインデックスの実装方法が異なり、すべてのエンジンがカバーリングインデックスをサポートしているわけではありません。

7. インデックススキャンを使用してソートする

MySQL には、ソート操作による方法とインデックス順でのスキャンによる方法の 2 つの方法で、順序付けられた結果セットを生成できます。 EXPLAINの type 列の値index場合、MySQL はインデックススキャンを使用してソートを実行することを意味します。

インデックス自体のスキャンは、1 つのインデックスレコードから次のレコードに移動するだけなので高速です。ただし、インデックスがクエリに必要なすべての列をカバーしていない場合は、インデックスレコードをスキャンするたびに、テーブルに戻って対応する行をクエリする必要があります。これは本質的にランダム I/O であるため、インデックス順序でデータを読み取ると、特に I/O 集中型のワークロードでは、順次的なフルテーブルスキャンよりも遅くなるのが一般的です。

MySQL では、ソートと行の検索の両方に同じインデックスを使用できます。したがって、可能であれば、インデックスは両方の状況を同時に満たすように設計する必要があります。つまり、インデックス列をソート列として使用します。

MySQL は、インデックス列の順序がorder by句の順序とまったく同じであり、すべての列のソート方向が同じである場合にのみ、インデックスを使用して結果をソートできます。
クエリで複数のテーブルを結合する必要がある場合、 order by句によって参照されるすべてのフィールドが最初のテーブルのものである場合にのみ、インデックスを並べ替えに使用できます。 order by句の制限は検索クエリの制限と同じです。インデックスの左端のプレフィックスを満たす必要があります。そうでない場合、MySQL は順次操作を実行する必要があり、インデックスのソートを使用できません。

8. 冗長なインデックスと重複したインデックス

重複インデックスとは、同じ列に同じ順序で作成された同じタイプのインデックスです。このような重複インデックスの作成は避け、発見次第すぐに削除する必要があります。

例えば：

テーブルテストを作成する{
 id int not null 主キー、
 nullではない整数、
 b int は null ではない、
 ユニーク(ID)
 インデックス(ID)
}エンジン=InnoDB;

経験の浅い人は、主キーを作成し、一意制約(unique(id))，を追加してから、クエリで使用するためのインデックス(index(id))を追加する場合があります。ただし、一意制約と主キー制約の両方がインデックスを通じて使用されるため、上記のステートメントは実際には同じ列に 3 つの重複インデックスを作成します。通常、異なるクエリ要件を満たすために同じ列に異なるタイプのインデックスを作成する場合を除き、これを行う理由はありません。

冗長インデックスと重複インデックスにはいくつかの違いがあります。たとえば、インデックス（A,B）を作成してから（A）を作成した場合、 A は前のインデックスのプレフィックスインデックスであるため、冗長インデックスになります。インデックス（A,B）は A と同じように使用できます。しかし、インデックス（B,A）を作成した場合、それは冗長インデックスではなく、インデックス B も同様です。 B はインデックス(A,B）の左端のプレフィックスインデックスではないためです。さらに、ハッシュインデックスやフルテキストインデックスなど、他の異なるタイプのインデックスは、B-Tree の冗長インデックスにはなりません。

冗長なインデックスは通常、テーブルに新しいインデックスを追加するときに発生します。たとえば、既存のインデックス（A）を拡張する代わりに、新しいインデックス（A,B）を追加する場合があります。別のケースでは、インデックスを（A,ID）に拡張します。ここで、 ID は主キーです。 InnoDBの場合、主キー列はすでにセカンダリインデックスに含まれているため、これも冗長です。

ほとんどの場合、冗長なインデックスは必要なく、新しいインデックスを作成するのではなく、既存のインデックスを拡張する必要があります。ただし、既存のインデックスを拡張するとインデックスが大きくなりすぎて、そのインデックスを使用する他のクエリのパフォーマンスに影響するため、パフォーマンス上の理由から冗長なインデックスが必要になる場合があります。たとえば、非常に長いvarchar列を整数列インデックスに追加すると、パフォーマンスが大幅に低下する可能性があります。特に、このインデックスをカバーするインデックスがある場合、またはこれがMyISAMテーブルであり、範囲クエリが多数ある場合。

冗長なインデックスと重複したインデックスの解決方法は非常に簡単で、削除するだけです。しかし、最初にやるべきことは、そのようなインデックスを見つけることです。 information_schemaテーブルにアクセスする複雑なクエリをいくつか記述することでそれらを見つけることができますが、 Shlomi Noachのcommon_schema (common_schema は、サーバーにインストールできる共通ストレージとビューのセットです) のいくつかのビューを使用してそれらを見つけるより簡単な方法が 2 つあります。もう 1 つの方法は、 Percona Toolkitのpt_duplicate-key-checker使用することです。これは、テーブル構造を分析して冗長なインデックスと重複するインデックスを検出します。

9. 未使用のインデックス

冗長インデックスと重複インデックスに加えて、サーバーが決して使用しないインデックスが存在する場合もあります。このようなインデックスは完全に冗長なので、直接削除することをお勧めします。

Performance_schemaのtable_io_waits_summary_by_index_usageテーブルを使用して、次の情報を検索できます。

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name, index_name;

10. インデックスとロック

インデックスを使用すると、クエリでロックする行数が少なくなります。クエリが不要な行にアクセスしない場合は、ロックされる行が少なくなり、2 つの点でパフォーマンスが向上します。

まず、 InnoDBの行ロックは非常に効率的でメモリをほとんど使用しませんが、行をロックするときには追加のオーバーヘッドが発生します。次に、必要以上に多くの行をロックすると、ロックの競合が増加し、同時実行性が低下します。

11. まとめ

上記の長いテキストは、インデックスを効率的に使用し、誤った使用を避ける方法を説明するために使用されます。インデックス作成はシンプルに見えますが、実際に使用するのは非常に複雑です。実際に使いこなすには、継続的な練習が必要です。実践こそが真実をテストする唯一の方法です。この記事では、インデックスの全体的な使用法についてのみ説明しています。記事全体を読んで具体的な例と組み合わせたり、過去に使用された場所を思い出したりするだけで、全体を包括的に理解し、インデックスの仕組みを理解できます。今後インデックスを使用したり最適化したりするときに、これらの側面から始めると、インデックスの正確かつ効率的な使用をさらに深めることができます。

インデックスの通常の使用では、次の要約と提案があります。

識別度の高いフィールドにインデックスを作成すると、インデックスを効果的に使用できます。識別度が低すぎると、インデックスを効果的に使用できず、すべてのデータページをスキャンする必要がある場合があります。この場合、インデックスを使用しない場合とほとんど違いはありません。
結合インデックスの場合、左端の一致原則に注意してください。一致は左から右に行う必要があります。MySQL は、範囲クエリ(>、<、between、like)に遭遇して一致を停止するまで、右に一致し続けます。たとえば、(a、b、c、d) の順序でa = 1 and b = 2 and c > 3 and d = 4が作成された場合、d は使用されません。(a、b、d、c) の順序でインデックスが作成された場合、それらはすべて使用できます。a、b、d の順序は任意に調整できます。
レコードをクエリするときは、* の使用頻度を減らし、インデックスカバレッジを使用してテーブル戻り操作を減らし、効率を向上させます。
一部のクエリでは結合インデックスを使用でき、これによりインデックスプッシュダウンを使用でき、テーブルを返す操作を減らして効率を向上させることもできます。
インデックスフィールドで関数または演算子を使用することは禁止されています。これによりインデックスが無効になります。
文字列フィールドを数値と比較すると、インデックスが無効になります。
ファジークエリ'%值%'はインデックスを無効にして完全なテーブルスキャンに変換しますが、 '值%'インデックスを効果的に利用できます。
並べ替え時にインデックスフィールドを使用するようにしてください。これにより、並べ替えが減り、クエリの効率が向上します。

上記は、MySQL のパフォーマンス最適化のためにインデックスを効率的かつ正しく使用する方法の詳細です。MySQL インデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: