MYSQL インデックスのベスト プラクティス

あなたは賢明な選択をしました

インデックスを理解することは開発と DBA の両方にとって非常に重要です

貧弱なインデックスは製品の問題の責任のかなりの部分を占めています

インデックスはそれほど高度な問題ではありません

MySQL インデックスの概要

インデックスを理解する

アプリケーションに最適なインデックスを作成する

MySQL の制限を受け入れる

インデックスの概要

インデックスは何に使用されますか?

データベースからのデータの読み取りを高速化します

制約を強制する (一意のインデックス UNIQUE、外部key FOREIGN KEY)

クエリ ページはインデックスがなくても通常通り実行できます

しかし、実行には長い時間がかかる場合があります

聞いたことがあるかもしれないインデックス タイプ

BTREE インデックス – mysql の主要なインデックス タイプ

RTREE インデックス– GIS

HASH インデックスの場合、MyISAM でのみサポートされています – MEMORY、NDB がサポートされています

BITMAP インデックス – MySQL はサポートしていません

FULLTEXT インデックス – MyISAM、Innodb (MySQL 5.6 以降でサポートされています)

BTREE のようなインデックスファミリー

はい、さまざまな実装がたくさんあります

高速な操作で同じプロパティを共有します

ハードディスクよりもメモリの方が寿命が延びます

通常、B+Treeはハードディスクストレージに使用されます

データはリーフノードに保存されます

B+Treeの例

MyISAM、Innodb インデックスの比較

MyISAM

データ ポインターはデータ ファイル内の物理的な場所を指します

すべてのインデックスは同じです (物理的な場所を指します))

Innodb

主キー インデックス(明示的または暗黙的) - ポインターの代わりにインデックスのリーフ ノードにデータを直接保存します

セカンダリ インデックス – 主キー インデックスの値をデータ ポインターとして保存します

BTREE インデックスはどのような操作に使用できますか?

KEY=5 のすべてのレコードをクエリ (ポイント クエリ)

KEY>5 のすべてのレコードをクエリ (オープンおよびクローズ)

5

適用対象外: すべてのレコードをクエリKEY の最後の番号は 0 に等しいです

なぜなら、これは範囲クエリ操作として定義できないからです

文字インデックス

これは (そして数値的にも) 違いはありません... 本当のところ

照合は文字列に対して定義された照合です

例: "AAAA"

プレフィックス LIKE クエリは特殊な範囲クエリです

LIKE "ABC%" の意味:

"ABC[最小値]"

LIKE "%ABC" はインデックス クエリを使用できません。

ジョイント インデックス

は次のようにソートされ、最初の列、次に 2 番目の列、3 番目の列などを比較します。

KEY(col1,col2) ,col3)

(1,2,3) < ; (1,3,1)

レベルごとに個別の BTREE インデックスの代わりに BTREE インデックスを使用します

インデックスのオーバーヘッド

インデックスは高価ですので使用しないでください冗長インデックスを追加します

ほとんどの場合、新しいインデックスを追加するよりも優れています

書き込み - インデックスの更新がデータベース書き込み操作の主なコストとなることがよくあります

読み取り - ハードディスクとメモリのオーバーヘッド領域が必要です。クエリの最適化

インデックスコストの影響

長い主キー ランダムな順序で挿入されるインデックス – SHA1('password')

低識別インデックスは劣る – 性別フィールドに基づいて構築されたインデックス

関連インデックスは低コスト – insert_time は自動インクリメントされる ID に関連付けられます

Innodb テーブルのインデックス

データは主キーによってクラスター化されます

主キーとして最適なフィールドを選択してください

たとえば、コメント テーブル – (POST_ID,COMMENT_ID) は次のような適切な選択です主キー。これにより、単一の投稿のコメントが一緒にクラスター化されます

、または単一の BIGINT (フィールド) で「パック」されます

主キーはすべてのインデックスに暗黙的に付加されます

KEY (A) は本質的に KEY (A) 、ID)

ソートに適したインデックスをカバーする

MySQL でのインデックスの使用方法

クエリ

ソート

データの読み取りを避ける (インデックスの読み取りのみ)

その他の特殊な最適化

クエリにインデックスを使用する

SELECT * FROM EMPLOYEES WHERELAST_NAME = "Smith"

これは一般的なインデックス KEY (LAST_NAME) です

OK 複合インデックスを使用します

SELECT * FROM EMPLOYEES WHERELAST_NAME=”Smith” AND DEPT=”Accounting”

インデックス KEY(DEPT,LAST_NAME) が使用されます

複合インデックスはより複雑です

インデックス (A,B,C) - フィールド順序の問題

次の状況では、インデックスがクエリ (フル) に使用されます条件）

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B IN (2,3) AND C>5

以下条件はインデックスを使用しません

B>5 - 条件には B フィールドの前に A がありません

B=6 AND C=7 - 条件には B フィールドと C フィールドの前に A がありません

の一部を使用します次の場合のインデックス

A>5 AND B=2 - 最初のフィールド A に対する範囲クエリの結果、インデックス内のフィールド A の一部のみが使用されました

A=5 AND B>6 AND C=2 -フィールド B の範囲クエリの結果、インデックス内のフィールド A と B の 2 つだけが使用されました フィールドの一部

MySQL オプティマイザーの最初のルール

複合インデックスでは、MySQL は次の場合に残りの部分 (インデックス) の使用を停止します。戻りクエリ (,BETWEEN) が発生しますが、IN (...) を使用すると、インデックス (のより多くの部分) を使用して右に進むことができます

ソートに使用されるインデックス

SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10

はインデックス KEY(SCORE) を使用します

ではありません インデックスを使用すると、非常に高価な「ファイルソート」操作 (外部ソート) が実行されます

クエリには結合インデックスがよく使用されます

SELECT * FROM PLAYERS WHERE COUNTRY =“US” ORDER BY SCORE DESC LIMIT 10

最良の選択は KEY(COUNTRY, SCORE) です

効率的にソートされたジョイント インデックス

はより制限されます!

KEY(A,B)

次の状況では、並べ替え用のインデックス

ORDER BY A - インデックスの最初のフィールドを並べ替えます

A =5 ORDER BY B - 最初のフィールドでポイントクエリを実行し、2 番目のフィールドで並べ替えます

ORDER BY A DESC, B DESC - 並べ替え両方のフィールドを同じ順序で並べます

A>5 ORDER BY A - 最初のフィールドで範囲クエリを実行し、最初のフィールドを並べ替えます

次の場合、並べ替えにインデックスは使用されません

ORDER BY B - 並べ替えます2 番目のフィールド (最初のフィールドは使用されません)

A>5 ORDER BY B - 最初のフィールドで範囲クエリを実行し、2 番目のフィールドを並べ替えます

A IN(1,2) ORDER BY B - IN クエリを実行します最初のフィールドを使用し、2 番目のフィールドをソートします

ORDER BY A ASC, B DESC - 2 つのフィールドを異なる順序でソートします

MySQL はインデックスのソート規則を使用します

2 つのフィールドを異なる順序でソートすることはできません

ドットクエリ (=) のみが可能ですORDER BY 部分の一部ではないフィールドに使用されます – この場合、IN() も機能しません

データの読み取りを避けてください (インデックスのみを読み取ります)

「カバーされたインデックス」 – ここでは適切なインデックスを指しますインデックスの種類ではなく、特定のクエリの場合

読み取り専用 データを読み取る代わりにインデックスを取得します

SELECT STATUS FROM ORDERS WHERECUSTOMER_ID=123

KEY(CUSTOMER_ID,STATUS)

通常、インデックスはデータ自体よりも小さくなります

(インデックス) はより多くの順序で読み取られます – データを読み取ります ポインタは通常ランダムです

Min/Max の最適化

インデックスは MIN()/MAX() などの統計関数の最適化に役立ちますが、次のもののみです:

SELECT MAX(ID) FROM TBL;

SELECT MAX(SALARY) FROM EMPLOYEEGROUP BY DEPT_ID

KEY(DEPT_ID,SALARY) のメリットが得られます

「グループ化にインデックスを使用する」

ジョイント テーブル クエリでのインデックスの使用

MySQL は「ネストされたループ」を使用して「ジョイントテーブルクエリを実行します

SELECT * FROM POSTS,COMMENTS WHEREAUTHOR="Peter" AND COMMENTS.POST_ID=POSTS.ID

テーブル POSTS をスキャンして、複合条件を持つすべての投稿をクエリします

ループポストとテーブル内の各投稿を検索します。 COMMENTS すべてのコメント

各関連テーブル (関連フィールド) にインデックスを使用することが非常に重要です

インデックスはクエリ対象のフィールドでのみ必要です。POSTS.ID フィールドのインデックスは使用されませんこのクエリでは、

うまく機能しないすべてのインデックスのユニオンクエリを再設計します

複数のインデックスを使用します

MySQL は複数のインデックスを使用できます

「インデックスマージ」

SELECT * FROM TBL WHERE A=5 AND B=6 – インデックス KEY(A) と KEY(B) をそれぞれ使用できます

index KEY(A,B) の方が良い選択です

SELECT * FROM TBL WHERE A=5 OR B=6– 2 つのインデックスは次のとおりです同時に別々に使用されます

このクエリではインデックス KEY(A,B) は使用できません

プレフィックス インデックス

フィールドの左端のプレフィックスにインデックスを作成できます

ALTER TABLE TITLE ADD KEY(TITLE(20) ));

BLOB/TEXT型フィールドのインデックス付けが必要

スペースの使用量を大幅に削減できる

インデックスのカバーには使用できない

プレフィックスの長さの選択が問題になる

プレフィックスの長さの選択

プレフィックスには十分な区別が必要である

個別のプレフィックス、個別のフィールド全体の値を比較

mysql> select count(distinct(title)) total,count(distinct(left(title,10))) p10,count(distinct(left(title,20) ))) タイトルから p20;

セット内の 1 行 (44.19 秒)

外れ値を確認してください

同じプレフィックスを使用しているレコードが多くないことを確認してください

Titlemysql> の選択数を最も多く使用してください(*) cnt、タイトル tl tl によるタイトル グループからの cnt による順序 説明制限 3;

セット内の 3 行 (27.49 秒)

最も使用されているタイトル接頭辞 mysql> select count(*) cnt, left(title,20) tl from title group by tl order by cnt desc limit 3;

セット内の 3 行 (33.23 秒)

MySQL は使用するインデックスをどのように選択しますか?

各クエリの動的選択 – クエリ テキスト内の定数が重要です

特定のインデックスに対してクエリされる行数を評価します、テーブル内で「dive」を実行します

(dive) が実行できない場合は、統計に「カーディナリティ」を使用します - これは ANALYZE TABLE を実行するときに更新されます

インデックスのオプションが追加されました

スキャンされる行数を最小限に抑えるだけではありません

他の多くのヒューリスティック (試行) とハック – Innodb にとって主キーは非常に重要です

カバーされたインデックスの利点

すべてが等しいため、フル テーブル スキャンが高速になります (この文はあまり明確ではありません)

インデックスも使用できます 並べ替え

手順

MYSQL で使用される実際の実行プランを確認してください

定数とデータに基づいて動的に変更できることに注意してください

EXPLAIN を使用してください

EXPLAIN は、MYSQL がどのようにクエリを実行するかを確認するための優れたツールです

mysql> ; (season_nr) from title group byproduction_year;

http://dev.mysql.com/doc/refm...

実際のクエリは実行計画と異なる場合があることに注意してください

セット内の1行(0.01 秒)

MySQL Explain 101

「タイプ」を最良から最悪の順に並べ替えます: - system、const、eq_ref、ref、range、index、ALL

「行」に注意してください - 値が大きいほど、クエリが遅いことを意味します

"key_len" を確認します - インデックスのどの部分が実際に使用されているかを示します

"Extra" に注意してください

インデックスの使用 - 良い

ファイルソートの使用、一時的な使用 - インデックス キー パフォーマンス クエリの悪い

インデックス戦略

セット – 1 つずつ見るのではなく、全体として見てください

すべてのクエリ条件とテーブル条件にインデックスを使用するのが最善です – 少なくとも最も区別される部分は

一般的に言えば、可能であれば、代わりに Index を展開してください新しいインデックスの作成

変更するときはパフォーマンスへの影響を必ず確認してください

インデックス戦略の例

より多くのクエリをサポートできる順序でインデックスを作成します

SELECT * FROM TBL WHERE A=5 AND B=6

SELECT * FROM TBL WHERE A>5 AND B=6 – 2 つのクエリの場合、KEY(B,A) がより良い選択です

ポイント クエリであるすべてのフィールドをインデックスの先頭に置きます

追加しないでくださいパフォーマンスに重要ではないクエリのインデックス – インデックスが多すぎると MYSQL の速度が低下します

トリック #1: 列挙範囲

KEY (A,B)

SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5

のみインデックスの最初のフィールド部分を使用します

SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5

インデックスの両方のフィールド部分を使用します

トリック #2: false 条件を追加します

KEY ( GENDER,CITY)

SELECT * FROM PEOPLE WHERE CITY="NEWYORK"

インデックスはまったく必要ありません

SELECT * FROM PEOPLE WHERE GENDER IN("M","F") AND CITY="NEW" YORK"

インデックスを使用します

このトリックは、あまり区別されないフィールドによく使用できます

性別、ステータス、ブール型など

トリック #3: 仮想ファイルと実際のファイルソート

KEY(A,B)

SELECT * FROM TBL WHERE A IN (1,2) ORDER BYB LIMIT 5;

ソートにインデックスを使用できません

(SELECT FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT FROM TBL WHERE A= 2 ORDER BY) B LIMIT 5) ORDER BY B LIMIT 5;

はインデックスを使用し、「filesort」は 10 行以下のレコードにのみ使用されます