インデックスを設計する際の原則は何ですか? インデックスの障害を回避するにはどうすればよいでしょうか?

これまで、インデックス作成とインデックス クエリの原則について詳しく説明してきました。諺にあるように、仕事をうまくやり遂げたいなら、まずはツールを磨かなければなりません。学習段階では、この知識を段階的に習得する必要があります。あまり野心的になりすぎないでください。忍耐強く、学習した後は各知識ポイントを一度に習得し、適用するよう努めなければなりません。

前回の記事では、インデックスの設計はWHERE条件とORDER BYおよびGROUP BY具体的な理由については、前回の記事「MySQL インデックスの原則」で詳しく説明しています。ここで簡単に概要を説明します。

MySQL は、主キー インデックスの B+ ツリー構造を維持します。これをクラスター化インデックスと呼びます。主キー以外のキー (通常は結合インデックス) の場合、インデックス フィールドは順番にソートされ、最初のフィールド値から比較が開始されます。最初のフィールド値が同じ場合は、次のフィールド値が比較され、これが繰り返されます。

結合インデックス内のフィールド値が同じ場合は、主キーに従ってソートされます。また、クラスター化インデックス（主キーインデックス）の B+ ツリーにはレコード行のすべての情報が格納されますが、非クラスター化インデックス（非主キーインデックス）にはインデックスフィールド値と主キーフィールド値のみが格納されます。

さて、インデックスの原則の見直しについては以上です。この記事では引き続き、MySQL設定の基本原則を紹介します。これもわかりやすいです。インデックスを設計して構築するときにどのような原則に従う必要があるか、そして「標準」に従ってインデックスを構築することについてです。今日はインデックス設計のすべての原則を一度に説明します。

この知識ポイントについてもう少し説明しましょう。面接中、私は応募者にこの質問をよくします。単に専門用語を暗唱するのではなく、インデックスを本当に理解しているかどうかを確認するためです。

主キーインデックス

主キーインデックスは実は一番シンプルなのですが、ここで注意すべき点がいくつかあるので、改めて説明したいと思います。

主キーを設計するときは、自動増分にする必要があります。UUID UUID主キーとして使用しないことを強くお勧めします。

なぜ？ UUIDは順序付けされていないため、MySQL はクラスター化インデックスを維持するときにデータを主キーの順序でソートします。つまり、各データ ページのデータは、主キーに従って小さいものから大きいものの順にソートする必要があります。さらに、データは一方向のリンク リストを介して相互に接続されます。前のデータ ページの最大の主キーの値は、次のデータ ページの最小の主キーの値よりも小さくなければなりません。データ ページは、双方向のリンク リストを介して維持されます。

私たちは今でも古いルールに従い、みんなが理解できるように絵を描きます

主キーが自動増分の場合、MySQL は主キー ディレクトリを使用するだけで、新しいレコードを挿入する場所をすばやく見つけることができます。主キーが自動増分でない場合は、毎回最初から開始し、正しい位置を見つけてからレコードを挿入する必要があります。これは効率に重大な影響を与えるため、主キーは自動増分になるように設計する必要があります。

また、ユニーク インデックスは主キー インデックスに似ていますが、ユニーク インデックスは必ずしも自己増加するわけではないため、ユニーク インデックスを維持するためのコストは主キー インデックスよりも確実に大きくなります。

ただし、ユニーク インデックスの値はユニークであるため (ユニーク インデックスは NULL 値を持つことができます)、インデックス フィールドを通じてレコードをより迅速に特定できますが、テーブル クエリを実行する必要がある場合があります (テーブル クエリの詳細については、前の記事で詳しく説明しているので、ここでは説明しません)。

頻繁にクエリされるフィールドのインデックスを作成する

インデックスを作成する際には、クエリ条件としてよく使用されるフィールドに対してインデックスを作成する必要があります。これにより、テーブル全体のクエリ速度が向上します。

ただし、クエリ条件は通常単一のフィールドではないため、通常は複数の結合インデックスが作成されます。

また、クエリ条件には「like」などのあいまいなクエリがよくあります。 あいまいなクエリの場合は、左端のプレフィックスクエリの原則に従うのが最適です。

大きなフィールドのインデックス作成を避ける

言い換えれば、データ量の少ないフィールドをインデックスとして使用するようにしてください。

たとえば、2つのフィールドがあり、1つはvarchar(5)で、もう1つはvarchar(200)であるとします。この場合、 MySQLインデックスを維持するときにフィールド値を一緒に維持するため、 varchar(5)フィールドにインデックスを作成することをお勧めします。これにより、必然的にインデックスがより多くのスペースを占め、ソート時の比較に時間がかかります。

varchar(100)のインデックスを作成したい場合はどうすればよいでしょうか?次に、データをいくつか取得します。たとえば、 addressタイプがvarchar(200)の場合、インデックスを作成するときに次のように記述できます。

tbl_address インデックスをdual(address(20))に作成します。

インデックスとして識別力の高い列を選択する

これはどういう意味ですか？例を挙げれば、誰でもすぐに理解できると思います。

「性別」フィールドがあり、そこに格納されているデータの値が男性または女性のいずれかである場合、そのようなフィールドはインデックスとして適していません。

このようなフィールドの値の主な特徴は、識別力が十分に高くないことであり、識別力の低いフィールドはインデックス作成に適していません。なぜでしょうか?

値が出現する確率がほぼ等しい場合、どの値を検索しても、取得されるデータは半分になる可能性が高いからです。

このような場合、MySQL にはクエリ オプティマイザもあるため、インデックスを使用しない方がよいでしょう。クエリ オプティマイザは、特定の値がテーブル内のデータ行の高割合に出現することを検出すると、通常はインデックスを無視してテーブル全体のスキャンを実行します。

慣例的なパーセンテージカットオフは「30%」です。 (一致するデータの量が一定の制限を超えると、クエリはインデックスの使用を中止します (これもインデックスが失敗するシナリオの 1 つです)。

理由はこうです。これを読んだ後、誰もが、カーディナリティの小さいフィールドをインデックスとして使用しないようにする必要がある理由がわかるはずです。実際、これにはMySQL用語が関係しています[カーディナリティ（インデックスカーディナリティ）はMySQLインデックスの非常に重要な概念です]

ORDER BYとGROUP BYに従ってフィールドのインデックスを作成してみてください

インデックスの作成後に B+ ツリー内のレコードがソートされることが既にわかっているため、クエリ時に再度ソートする必要がないように、 Order Byの後のフィールドのインデックスを作成します。

GROUP BY 和ORDER BYは実際には似ているため、一緒に説明します。

GROUP BY使用する場合、まずGROUP BYに続くフィールドをソートしてから集計操作を実行する必要があります。

GROUP BYの後のフィールドがソートされていない場合、MySQL は最初にそれらをソートする必要があり、これにより一時テーブル、ソートされた一時テーブルが生成され、その後一時テーブルで集計操作が実行されます。これはもちろん非常に非効率的です。GROUP GROUP BYの後のフィールドにインデックスが付けられている場合、 MySQLそれらを再度ソートする必要がなく、一時テーブルは生成されません。

ただし、難しいのは、 GROUP BY列がORDER BY列と異なる場合、両方にインデックスがあっても一時テーブルが生成されること。実際、オンラインで検索したところ、そのようなケースがたくさんあるようです。ここでリストします。正直なところ、これらは標準ですが、実際のシナリオはそれほど単純で純粋ではないため、この標準を実装するのは難しいようです。

1. GROUP BY 列にインデックスがない場合、一時テーブルが作成されます。
2. GROUP BY 中の SELECT ステートメントに複数の GROUP BY 列があり、GROUP BY 列が主キーでない場合は、一時テーブルが生成されます。
3. GROUP BY 列にインデックスがあり、ORDER BY 列にインデックスがない場合は、一時テーブルが作成されます。
4. GROUP BY 列が ORDER BY 列と異なる場合、両方にインデックスがあっても一時テーブルが生成されます。
5. GROUP BY または ORDER BY の列が JOIN ステートメントの最初のテーブルからのものではない場合は、一時テーブルが生成されます。
6. DISTINCT 列と ORDER BY 列にインデックスがない場合、一時テーブルが作成されます。
7. GROUP BY と ORDER BY の列が同じで主キーであるが、SELECT 列に GROUP BY 列以外の列が含まれている場合は、一時テーブルも生成されます。

条件文で関数を使用しない

確立されたインデックスのフィールドに対して関数操作を実行すると、そのインデックスは使用できません。

何故ですか？

MySQLがインデックス用に維持する B+ ツリーは、フィールドの元のデータに基づいているためです。使用中に関数が追加された場合、 MySQLこれを元のフィールドとは見なさず、インデックスを使用しません。

しかし、もし頑固な人がいたら、その機能を使いたいときはどうすればいいのでしょうか？インデックス作成のためだけにビジネスを変えることはできませんよね？ MySQL内部関数の使用によりインデックスが無効になる場合は、インデックス作成時に関数も一緒に作成できます。

これはどういう意味ですか？ ageというフィールドがあり、それにインデックスが作成されているとします。しかし、それが使用されると、次のようになります。

SELECT * FROM student WHERE round(age) = 2;

現時点ではインデックスは使用されていません。round round(age)をインデックス化したい場合は、次のようにインデックスを作成できます。

test(round(age)) にインデックス stu_age_round を作成します。

このとき、上記の方法でクエリを実行すると、インデックスが有効になります。これは誰でも理解できると思います。

インデックスを作成しすぎない

MySQL ではインデックスを維持するためにスペースが必要となり、パフォーマンスが消費されるため、MySQL はインデックス フィールドごとに B+ ツリーを維持します。

したがって、インデックスが多すぎると、MySQL の負担が間違いなく増加します。

頻繁に追加、削除、または変更されるフィールドにはインデックスを作成しないでください。

これは簡単に理解できます。すでに紹介したように、フィールドが変更されるとMySQLインデックスを再管理する必要があるためです。

フィールドが頻繁に変更されると仮定すると、インデックスを頻繁に再構築する必要があることを意味し、必然的に MySQL のパフォーマンスに影響します。ここではこれ以上は言いません。

ここで話したことのほとんどは、設計時に注意する必要があるいくつかの原則です。実際には、実際の原則は実際のビジネスに応じて変更する必要があります。いわゆる「公式」はありません。実際のビジネスシナリオに適した設計が最適です。したがって、「最適化」を追求しすぎないでください。これは裏目に出ることが多いからです。結局のところ、ビジネスを考慮せずにテクノロジーについて語ることは、ただの不良行為です。

さて、インデックスが失敗する状況を詳しく見てみましょう。 （追記：この記事は基本的に理論のみです。絵を描いて表現したいと思ったのですが、全然着手できないことが分かりました。みなさん頑張ってください。もうすぐ完成します。）

インデックス障害の一般的なシナリオ

1. ORキーワードを使用すると、インデックスが無効になります。ただし、OR を使用していてインデックスを無効にしたくない場合は、 or条件の各列にインデックスを作成する必要があります。これは明らかに、インデックスを作成しすぎないようにするという上記のアドバイスに反しています。
2. 結合インデックスが最左接頭辞の原則に従わない場合、インデックスも無効になります。
3. ファジークエリを使用する場合、% で始まるとインデックスが失敗することもあります (前回の記事で触れたので、ここでは理由は繰り返しません。もう一度思い出すのに役立つためです)
4. インデックス列が暗黙的な変換を使用する場合、インデックスも無効になります。

フィールドage型がint型であると仮定すると、通常は次のようにクエリします。

学生から * を選択 WHERE 年齢 = 15

上記の状況ではインデックスを使用できますが、次のように記述すると

SELECT * FROM 学生 WHERE 年齢='15'

この場合、インデックスは使用できません。つまり、 age列のインデックスは無効です。

フィールドのカーディナリティが小さい場合、インデックスが失敗する可能性もあります。これについては、この記事の前半で詳しく説明しました。これは、 MySQLクエリ オプティマイザーによって発生します。

その他の原則については、インデックスの原則とクエリの基本原則をお読みください。事前の準備がなければ、これらは少し空虚に感じられるかもしれません。したがって、インデックスを段階的に学習してください。これは基本的に、 MySQL使用する際のコアとなる知識ポイントです。

上記は、「インデックス設計の原則は何ですか？インデックスの失敗を回避するには？」の詳細な内容です。インデックス設計の原則の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。