MySQL インデックスの原則を学ぶにはどうすればよいですか?私自身のインデックス作成経験の要約

インデックスとは何ですか? Baidu Encyclopedia では、次のように説明されています。 インデックスは、テーブル内のデータ行の検索を高速化するために作成された分散データの結果であり、テーブル内の各行以外のインデックス ページで構成されます。インデックス ページには、物理​​データの取得を高速化するための論理ポインタが含まれています。 MySQL インデックスの原則を学ぶ方法については、この記事で詳しく説明します。

要約: MySQL インデックスについて話しましょう。 インデックスとは何ですか? Baidu Encyclopedia では、次のように説明されています。インデックスは、テーブル内のデータ行の取得を高速化するために作成された分散データの結果であり、テーブルのデータ ページ以外のインデックス ページで構成されます。インデックス ページには、物理​​データの取得を高速化するための論理ポインタが含まれています。実際、インデックス作成の概念は誰もが知っており、インデックス作成によってクエリの効率が向上することは知っています。しかし、ほとんどの子供向けシューズでは、インデックスの作成方法と、誤解: 新しいテーブルを作成するときにインデックスを作成する必要はありません。単純な SQL にはインデックスが必要ではなく、結合クエリのみが必要です。結合インデックスの順序は、where 条件の後のフィールドの順序になります。これは、ステータス、性別、その他のフィールドなどのフィールドに対しても作成されます。

MySQL インデックスについて一緒に話しましょう。

インデックスとは何ですか?

Baidu Encyclopedia では次のように説明されています。

インデックスは、テーブル内のデータ行の取得を高速化するために作成された分散データの結果であり、データ ページの構成外に構築されます。 、インデックス ページの各行には、物理​​データの取得を高速化するための論理ポインターが含まれています

実際、インデックス作成の概念については誰もが非常に明確であり、インデックス作成によってクエリの効率が向上することもわかっていますが、ほとんどの子供たちはどうやってインデックスを構築しますか? どのフィールドに基づいて構築するかについては、次のようなよくある誤解があります:

新しいテーブルを作成するときにインデックスを作成する必要はなく、インデックスは後で追加されます

where 条件の後のフィールドにはすべてインデックスが付けられます

単純な SQL にはインデックスは必要ありません。インデックスが必要なのはジョイント クエリのみです

ジョイント インデックスの順序は、where 条件の後のフィールドの順序です。

小さな区別があるフィールドに対しても新しいインデックスが作成されます。ステータス、性別、その他のフィールドとして。

インデックスの区別

上記の問題について話す前に、まず別の概念である差別について見てみましょう。

Distinction: データベース内のフィールドの非重複率を指します

Distinction は、新しいインデックスを作成するときに非常に重要な参照値です。MySQL では、差別化の計算ルールは次のとおりです。

重複排除後。フィールド数 合計数とテーブル全体のレコードの合計数の商。

例:

select count(distinct(name))/count(*) from t_base_user;

結果は次のようになります:

count(distinct(name))/count(* )

1.0000

区別の最大値は 1.000、最小値は 0.0000 です。区別の値が大きいほど、つまりデータの非重複率が高いほど、主キーに対する新しいインデックスの効果が高くなります。一意のキーは 1.0000 です。ステータスや性別などの項目の識別値が最も小さい。 (これはデータ量によって異なります。データが数個しかない場合、識別度は非常に高くなります。データ量が多い場合、識別度は基本的に 0.0000 になります。つまり、これらのフィールドにインデックスを追加した後)効果は良くありません)

次のことに注意してください: テーブルにレコードがない場合、判別値はヌル値になります。 0.0000 ～ 1.0000。

インデックスの構築方法

(1): 識別
以下の理由により、インデックスを構築するときは、最初にフィールドの識別を計算することを強くお勧めします:

1. 単一列インデックス
を確認できます。分野の区別。区別の大きさに基づいて、この分野の新しい指標が効果的かどうか、またどの程度効果的であるかを大まかに知ることもできます。識別が大きければ大きいほど、インデックスの効果はより明白になります。

2. 複数列インデックス (結合インデックス)
実際には、複数列インデックスのフィールドの順序にも問題があり、一般に、より微分性の高いものが最初に配置されます。より効果的な例:

select * from t_base_user where name="" and status=1;

上記のステートメントと同様に、結合インデックスが構築される場合は、次のようにする必要があります:

alter table t_base_user addindex idx_name_status(name, status);

And Not:

alter table t_base_user addindex idx_status_name(status,name);

(2) 左端のプレフィックスマッチング原則
MySQL は、範囲クエリ (>、<、 between、like ) で一致を停止します。

select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (このステートメントはデモンストレーションのみです)

上記のステートメントでは、status MySQL が

select * from t_base_user where type=10 and status=1 and created_at<" 2017-11-03」

ステータスインデックスが使用できます。

(3) 関数演算
インデックス列に対して関数演算を行わないでください。インデックスが無効になります。なぜなら、b+ ツリーはすべてのフィールド値をデータ テーブルに保存しますが、取得するときに、比較するすべての要素に関数を適用する必要があるため、明らかにコストがかかりすぎます。

(4) 拡張が先です
拡張が先です。新しいインデックスを作成せず、既存のインデックスを変更してみてください。次のように:

select * from t_base_user where name="andyqian" and email="andytohome"

idx_name インデックスはテーブル t_base_user テーブルにすでに存在します。idx_name_email のインデックスを追加する必要がある場合は、新しいインデックスを作成する代わりに、idx_name インデックスを作成します。

誤解の修正

新しいインデックスの作成方法については上で説明しましたが、最初のステップで誤解に答えることができます。

誤解 1: 新しいテーブルを作成するときにインデックスを作成する必要はなく、後でインデックスを追加します

答え: 優れたデータ テーブル設計では、後で問題が発生するまで待つのではなく、最初にインデックスの作成を考慮する必要があります。ビジネス利用に影響を与えるため、この状況を救うために新しいインデックスが作成されましたが、その後のインデックス作成のコストは比較的高くなります。 (これは、生産事故が根を下ろして芽を出す機会を残すためです)

誤解 2: where 条件の後のフィールドはすべてインデックス付けされます

答え: この誤解は比較的よくありますが、where 条件の後のフィールドにはインデックスを付ける必要はありませんインデックスを作成しすぎると、インデックス ファイルが急激に増加し、望ましい効果が得られなくなります。詳細については、上記のインデックスの作成に関するセクションを参照してください。

誤解 3: 単純な SQL にはインデックス作成は必要ありませんが、結合クエリにはインデックス作成が必要です

答え: この誤解は、今日では、特に B/S アーキテクチャの下では、コードからビジネス ロジックが取り除かれているため、慎重に説明する必要があります。最終的な SQL レベルでは、実際には、いくつかの接続クエリとより多くの単一テーブル操作だけを備えた単純な SQL です (C/S アーキテクチャでは、SQL レベルで多くのロジックが記述されています)。シンプルですか？

誤解 4: 結合インデックスの順序は、where 条件の後のフィールドの順序です。

答え: 結合インデックスの順序は、左端の接頭辞の原則と微分度に基づいていると言いました。 where 条件の後のフィールドとは異なります。順序は関係ありません。

誤解 5: 微分度の低いフィールドにインデックスを作成する

回答: 微分度が低いフィールドに新しいインデックスを作成することは基本的に効果がなく、インデックス ファイルの数も大量に増加します。利益を得る価値がないと思いますか？

インデックスは重要ですか?

上記では、MySQL インデックスの概念と、新しいインデックスを作成する際のヒントを紹介しました。このような理論的なことですが、使用されていない、または比較的めったに使用されない子供用の靴については、現時点ではインデックス作成の重要性がそれほど直感的に理解されていない可能性があります。そこで、インデックス作成で私が経験した損失と落とし穴について話しましょう。インデックスが構築されないという一般的な問題もあります。

0. クエリが遅くなる
この問題は、インデックスを作成しない場合によくある問題です (暗黙的な型変換など、ここにも多くの詳細があります)

1. サービス タイムアウトが発生する

シナリオ:
オンライン化する際には、サービスプロバイダーとしてビジネス関係者にサービスを提供します。最初は簡単なサービスを提供しているだけだと思っていましたが、テストが完了して、今日は早く家に帰ることができて、今でも密かに喜んでいます。

説明:
実際にオンラインになるとすぐに、ビジネスパーティが本番環境で呼び出しをリクエストすると、各リクエストがタイムアウトになり、この時点では、最終的にコードを確認することしかできませんでした。実際には、このステートメントが単一テーブルの WHERE 条件付きクエリ ステートメントであるとは想像もできないほど、10 秒以上かかるクエリが原因で問題が発生していることがわかりました。このような理由でサービスが利用できなくなるということですが、あなたは正しいですか、間違っていますか? (これが、優れたデータ テーブル設計のためには最初から新しいインデックスを考慮する必要があると言っている理由です)。

2. データベースサーバーのCPUが100%

クエリ頻度が比較的高いSQLでは、インデックスが構築されていないためにクエリが遅い場合、データベースサーバーのCPUが100%となり、システム全体に影響を及ぼします。

概要
インデックスが確立されていないことによって引き起こされる問題には、クエリが遅くなり、システム効率に影響を与えるものから、CPU が 100% になり、システム全体の使用に影響を与えるものまで、さまざまな種類があります。こちらを参照してください。インデックスって言いましたよね それは重要ですか？

最後に

上で簡単に説明しましたが、インデックスとは何ですか?その用途、インデックスを作成する際のヒント、およびインデックス作成の重要性についても説明します。インデックス作成は非常に重要ですが、日常のコーディングでインデックス作成を回避するにはどうすればよいでしょうか?以下は私の個人的な提案です:

1. テーブルを構築するときは、外部キー フィールドなどのインデックスの追加を検討する必要があります。

2. SQLを記述したら、必ず実行計画を確認してください。テーブル全体のスキャンは避けるようにしてください。

3. 既存のテーブルにインデックスを追加する場合は、最初にフィールドの区別を計算する必要があります。

4. ジョイントインデックス、最大の区別を前面に置きます。

5. MySQL の左列プレフィックス優先原則に従います

[2]H. Berenson、P. Bernstein、J. Gray、J. Melton、E. O'Neil、および P. O'Neil の批評。 ANSI SQL 分離レベルの説明。SIGMOD 国際会議データ管理議事録、1 ～ 10 頁、1995 年 5 月。

[3]Michael J. Cahill、Uwe Röhm、Alan D.Fekete、2008 年。スナップショット データベースのシリアル化可能な分離。 SIGMOD '08: データ管理に関する 2008 年の ACM SIGMOD 国際会議の議事録、米国ニューヨーク州ニューヨーク [4] Michael James Cahill、シドニー デジタル。論文 : シドニー大学情報技術学部 [5] A. Fekete、D. Liarokapis、E. O'Neil、P.O'Neil、および D. Shasha。ACM トランザクション内。データベース システム、第 39 巻(2)、ページ 492–528、2005 年 6 月。

関連ビデオ:

インデックスの簡単な紹介 - 6 日間で MySQL を理解できるビデオ チュートリアル

以上がMySQL インデックスの原則を学ぶにはどうすればよいですか?私自身のインデックス作成経験の要約の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。