MySQL インデックスの正しい使い方とインデックスの原理の詳細な説明

1. はじめに

なぜインデックスが必要なのでしょうか?

一般的なアプリケーション システムでは、読み取りと書き込みの比率は約 10:1 であり、挿入操作や一般的な更新操作でパフォーマンス上の問題が発生することはほとんどありません。実稼働環境では、最も一般的で最も問題となる操作は依然として複雑なクエリ操作であるため、クエリ ステートメントの最適化が最優先事項であることは明らかです。クエリの高速化に関しては、インデックスについて言及する必要があります。

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

インデックスは、MySQL では「キー」とも呼ばれ、ストレージ エンジンがレコードをすばやく検索するために使用するデータ構造です。インデックスはパフォーマンス向上に重要

特にテーブル内のデータ量が増加すると、インデックスがパフォーマンスに与える影響はますます重要になります。

インデックスの最適化は、クエリのパフォーマンスを最適化するための最も効果的な手段です。インデックスを使用すると、クエリのパフォーマンスを簡単に数桁向上できます。

索引は辞書の発音表に相当します。ある単語を調べたい場合、発音表を使わないと何百ページもの辞書を一つ一つ調べる必要があります。

 30

 10 40

 5 15 35 66

1 6 11 19 21 39 55 100

インデックス作成に関して誤解はありませんか?

インデックス作成は、アプリケーションの設計と開発の重要な側面です。インデックスが多すぎると、アプリケーションのパフォーマンスが低下する可能性があります。ただし、インデックスが少なすぎると、クエリのパフォーマンスに影響します。バランスを見つけることは、アプリケーションのパフォーマンスにとって非常に重要です。開発者の中には、インデックスの追加を後から考える人がいますが、これは間違った開発モデルに起因していると私は常に信じてきました。データがどのように使用されるかわかっている場合は、最初から必要な場所にインデックスを追加する必要があります。開発者は、SQL 文やストアド プロシージャの作成など、アプリケーション レベルでデータベースを使用することが多いです。開発者はインデックスの存在すら知らない場合や、後から関連する DBA にインデックスの追加を依頼できると考えている場合もあります。 DBA はビジネス データ フローを十分に理解していないことが多く、インデックスを追加するには、問題を見つけるために多数の SQL ステートメントを監視する必要があります。この手順は、最初にインデックスを追加するのに必要な時間よりもはるかに長くかかるため、一部のインデックスが失われる可能性があります。もちろん、インデックスは多ければ多いほど良いです。私はかつて次のような問題に遭遇しました。MySQL サーバーの iostat では、ディスク使用率が常に 100% を示していました。分析の結果、開発者がインデックスを追加しすぎたためであることがわかりました。不要なインデックスをいくつか削除すると、ディスク使用率はすぐに 20% に下がりました。インデックスの追加も非常に技術的であることがわかります。

2. インデックスの原則

1. インデックスの原則

インデックスの目的はクエリの効率を向上させることです。これは、書籍の検索に使用するカタログと同じです。まず章を見つけ、次に章の下のサブセクションを見つけ、最後にページ番号を見つけます。同様の例としては、辞書を調べる、電車のスケジュールやフライトスケジュールを確認するなどが挙げられます。

本質は、取得するデータの範囲を継続的に絞り込むことで最終的な目的の結果をフィルタリングし、同時にランダムなイベントを連続したイベントに変換することです。言い換えれば、このインデックスメカニズムを使用すると、常に同じ検索方法を使用してデータをロックできます。

データベースでも同じことが言えますが、等価クエリだけでなく、範囲クエリ (>、<、between、in)、あいまいクエリ (like)、結合クエリ (or) なども発生するため、明らかにはるかに複雑です。データベースは、すべての問題に対処するためにどのように選択すればよいでしょうか?辞書の例をもう一度考えてみましょう。データをセグメントに分割し、セグメントごとにクエリを実行することはできますか?最も簡単な方法は、1,000 個のデータを 1 から 100 までの数字を含む最初のセクション、101 から 200 までの数字を含む 2 番目のセクション、201 から 300 までの数字を含む 3 番目のセクションに分割することです。次に、250 番目のデータを確認するには、3 番目のセクションを見つけるだけでよいため、無効なデータの 90% を一度に排除できます。しかし、レコードが 1,000 万件ある場合は、いくつのセグメントに分割すればよいのでしょうか?アルゴリズムの基礎知識を持つ学生は、平均複雑度が lgN でクエリ パフォーマンスが優れている検索ツリーについて考えるでしょう。しかし、ここで重要な問題を見落としています。複雑性モデルは、毎回同じ操作のコストに基づいています。データベースの実装は比較的複雑です。一方では、データはディスクに保存されます。他方では、パフォーマンスを向上させるために、データの一部を毎回計算のためにメモリに読み込むことができます。ディスクへのアクセスのコストはメモリへのアクセスの約 10 万倍であることがわかっているため、単純な検索ツリーでは複雑なアプリケーション シナリオに対応することが困難です。

2. ディスクIOと事前読み取り

ディスクアクセスについては先ほど触れたので、ここではディスクIOと事前読み取りについて簡単に紹介します。ディスクの読み取りは機械的な動きに依存しています。各データの読み取りにかかる時間は、シーク時間、回転遅延、転送時間の3つに分けられます。シーク時間は、磁気アームが指定されたトラックに移動するのに必要な時間を指し、主流のディスクは通常5ms以下です。回転遅延は、よく耳にするディスクの回転速度です。たとえば、7200rpmのディスクは、1分間に7200回回転できることを意味し、1秒間に120回回転できることを意味します。回転遅延は1/120/2 = 4.17msです。転送時間は、ディスクからデータを読み取ったり書き込んだりするのにかかる時間を指し、通常は10分の1ミリ秒程度で、最初の2回と比較すると無視できます。したがって、ディスクにアクセスする時間、つまりディスク IO の時間は、約 5+4.17 = 9 ミリ秒になります。これは良いように思えますが、500 MIPS (Million Instructions Per Second) のマシンは 1 秒あたり 5 億の命令を実行できることを知っておく必要があります。命令は電気の性質に依存するため、言い換えると、IO の実行時間は約 450 万の命令を実行できます。データベースには、数十万、数百万、さらには数千万のデータが含まれることがよくあります。毎回 9 ミリ秒というのは明らかに大惨事です。次の図は、参考までにコンピューター ハードウェアの遅延の比較です。

ディスク IO は非常にコストのかかる操作であることを考慮して、コンピューターのオペレーティング システムではいくつかの最適化が行われています。IO 中は、現在のディスク アドレスのデータだけでなく、隣接するデータもメモリ バッファーに読み込まれます。これは、ローカル事前読み取りの原理により、コンピューターがアドレスのデータにアクセスすると、そのアドレスに隣接するデータにもすばやくアクセスされるためです。 IO によって毎回読み取られるデータはページと呼ばれます。ページの具体的なサイズはオペレーティング システムによって異なりますが、通常は 4k または 8k です。つまり、ページ内のデータを読み取る場合、実際に発生する IO は 1 つだけです。この理論は、インデックス データ構造の設計に非常に役立ちます。

3. インデックスデータ構造

先ほど、インデックスの基本原則、データベースの複雑さ、およびオペレーティング システムに関する関連知識について説明しました。目的は、データ構造がどこからともなく現れるわけではなく、その背景と使用シナリオがあるはずだということを皆さんに理解してもらうことです。では、このデータ構造に何が必要なのかをまとめてみましょう。実際、それは非常に単純です。つまり、データを検索するたびに、ディスク IO 回数を非常に小さな桁、できれば一定の桁に制御するのです。では、高度に制御可能な多方向検索ツリーがニーズを満たすことができるかどうか疑問に思います。このようにして、b+ ツリーが誕生しました (B+ ツリーは、バイナリ サーチ ツリー、バランス バイナリ ツリー、B ツリーから進化しました)。

上の図に示すように、これは b+ ツリーです。b+ ツリーの定義については、B+ ツリーを参照してください。ここでは、いくつかの重要な点についてのみ説明します。水色のブロックはディスク ブロックと呼ばれます。各ディスク ブロックには、いくつかのデータ項目 (濃い青で表示) とポインター (黄色で表示) が含まれていることがわかります。たとえば、ディスク ブロック 1 にはデータ項目 17 と 35 が含まれており、ポインター P1、P2、および P3 が含まれています。P1 は 17 未満のディスク ブロックを表し、P2 は 17 から 35 の間のディスク ブロックを表し、P3 は 35 より大きいディスク ブロックを表します。実際のデータは、リーフ ノード 3、5、9、10、13、15、28、29、36、60、75、79、90、および 99 に存在します。非リーフ ノードには実際のデータは保存されず、検索方向を指示するデータ項目のみ保存されます。たとえば、17 と 35 は実際にはデータ テーブルに存在しません。

###b+ ツリー検索プロセス

図に示すように、データ項目 29 を検索する場合、最初にディスク ブロック 1 がディスクからメモリにロードされます。このとき、IO が発生します。メモリ内でバイナリ検索を使用して、29 が 17 と 35 の間にあることを判別します。ディスク ブロック 1 の P2 ポインターはロックされています。メモリ時間は非常に短いため (ディスク IO と比較して) 無視できます。ディスク ブロック 3 は、ディスク ブロック 1 の P2 ポインターのディスク アドレスを介してディスクからメモリにロードされます。2 番目の IO が発生します。29 は 26 と 30 の間にあります。ディスク ブロック 3 の P2 ポインターはロックされています。ディスク ブロック 8 は、ポインターを介してメモリにロードされます。3 番目の IO が発生します。同時に、メモリ内でバイナリ検索を実行して 29 を見つけ、クエリが終了します。合計 3 つの IO が実行されます。実際には、3 層の B+ ツリーは数百万のデータを表すことができます。数百万のデータの検索に 3 つの IO しか必要ない場合、パフォーマンスは大幅に向上します。インデックスがない場合、各データ項目に IO が必要になり、合計で数百万の IO が必要になりますが、これは明らかに非常にコストがかかります。

###b+ツリープロパティ

1. インデックスフィールドはできるだけ小さくする必要があります。上記の分析により、IO回数はb+numberの高さhに依存することがわかります。現在のデータテーブルのデータがNで、各ディスクブロックのデータ項目数がmであると仮定すると、h = ㏒(m+1)Nです。データ量Nが一定の場合、mが大きいほどhは小さくなります。m = ディスクブロックサイズ/データ項目サイズです。ディスクブロックサイズはデータページのサイズで、固定されています。データ項目が占めるスペースが小さく、データ項目の数が多い場合、ツリーの高さは低くなります。このため、各データ項目、つまりインデックス フィールドは、できるだけ小さくする必要があります。たとえば、int は 4 バイトを占めますが、これは bigint の 8 バイトの半分です。このため、b+ ツリーでは、実際のデータを内部ノードではなくリーフ ノードに配置する必要があります。内部ノードに配置すると、ディスク ブロックのデータ項目が大幅に減少し、ツリーの高さが増加します。データ項目が 1 に等しい場合、線形リストに退化します。

2. インデックスの左端のマッチング機能：b+ツリーのデータ項目が（名前、年齢、性別）などの複合データ構造である場合、b+ツリーは左から右の順に検索ツリーを構築します。たとえば、（張さん、20、F）などのデータが取得されると、b+ツリーは最初に名前を比較して次の検索方向を決定します。名前が同じ場合は、年齢と性別が順番に比較され、最終的に取得されたデータを取得します。ただし、（20、F）などの名前のないデータが来ると、b+ツリーは次にどのノードをチェックすればよいかわかりません。これは、名前が検索ツリーが確立されるときに最初の比較要素となるため、次にどこを照会すればよいかを知るために、まず名前に従って検索する必要があるためです。たとえば、(Zhang San, F) のようなデータを取得する場合、b+ ツリーは名前を使用して検索方向を指定できますが、次のフィールド age が欠落しているため、名前が Zhang San と同じデータのみを検索し、その後、性別が F のデータと一致させます。これは非常に重要なプロパティであり、インデックスの最も左の一致機能です。

4. クラスター化インデックスと補助インデックス

データベースでは、B+ ツリーの高さは通常 2 ～ 4 層です。つまり、特定のキー値の行レコードを見つけるのに最大 2 ～ 4 回の IO しかかからないため、非常に優れています。現在の一般的な機械式ハードディスクは少なくとも 1 秒あたり 100 回の IO を実行できるため、2 ～ 4 回の IO であればクエリ時間はわずか 0.02 ～ 0.04 秒しかかかりません。

データベース内の B+ ツリー インデックスは、クラスター化インデックスとセカンダリ インデックスに分けられます。

クラスター化インデックスと補助インデックスの共通点は、どちらも B+ ツリーの形式、つまり高さがバランスが取れていて、リーフ ノードにすべてのデータが格納されていることです。

クラスター化インデックスとセカンダリ インデックスの違いは、リーフ ノードが情報の行全体を格納するかどうかです。

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

#InnoDB ストレージ エンジンはインデックス構成テーブルを意味します。つまり、テーブル内のデータは主キーの順序で格納されます。クラスター化インデックスは、各テーブルの主キーに従って B+ ツリーを構築します。リーフ ノードには、テーブル全体の行レコード データが格納されます。クラスター化インデックスのリーフ ノードは、データ ページと呼ばれます。クラスター化インデックスのこの特性により、インデックス構成表のデータもインデックスの一部であることが決定されます。

B+ ツリー データ構造と同様に、各データ ページは二重リンク リストを通じてリンクされます。 #主キーが定義されていない場合、MySQL は空でない列 (NOT NULL) のみを含む最初の一意のインデックス (unique) を主キーとして取得し、InnoDB はそれをクラスター化インデックスとして使用します。 #そのような列がない場合、InnoDB は自動的にこのような ID 値を生成します。これは 6 バイトで非表示になっており、クラスター化インデックスになります。 #実際のデータ ページは 1 つの B+ ツリーに従ってのみソートできるため、各テーブルには 1 つのクラスター化インデックスのみを含めることができます。多くの場合、クエリ オプティマイザーはクラスター化インデックスを優先します。

クラスター化インデックスは、B+ ツリー インデックスのリーフ ノード上でデータを直接見つけることができるためです。さらに、クラスター化インデックスはデータの論理的な順序を定義するため、範囲値を対象とするクエリへの特に高速なアクセスを提供できます。

クラスター化インデックスの利点の 1 つは、主キーをすばやく並べ替えて検索できることと、リーフ ノード内のデータがユーザーがクエリしたいデータとまったく同じであることです。たとえば、ユーザーがテーブルを検索してユーザー情報の最後の 10 桁を照会する必要がある場合、B+ ツリー インデックスは双方向リンク リストであるため、ユーザーは最後のデータ ページをすばやく見つけて 10 件のレコードを取得できます。

クラスター化インデックスの 2 番目の利点は、範囲クエリです。つまり、主キーの特定の範囲内でデータを検索する場合、リーフ ノードの上位中間ノードを通じてページ範囲を取得し、データ ページを直接読み取ることができます。

2. 補助索引

クラスター化インデックスを除き、テーブル内の他のすべてのインデックスはセカンダリ インデックス (非クラスター化インデックスとも呼ばれます) です。クラスター化インデックスとの違いは、セカンダリ インデックスのリーフ ノードに行レコードのすべてのデータが含まれていないことです。

キー値に加えて、各リーフ ノード インデックス行にはブックマークも含まれます。ブックマークは、インデックスに対応する行データがどこにあるかを InnoDB ストレージ エンジンに伝えるために使用されます。

InnoDB ストレージ エンジンはインデックス構成テーブルであるため、InnoDB ストレージ エンジンの補助インデックスのブックマークは、対応する行データのクラスター化インデックス キーになります。下記の通り

補助インデックスの存在はクラスター化インデックス内のデータの構成には影響しません。そのため、各テーブルには複数の補助インデックスを設定できますが、クラスター化インデックスは 1 つしか設定できません。補助インデックスを通じてデータを検索する場合、InnoDB ストレージ エンジンは補助インデックスをトラバースし、リーフ レベルのポインタを通じて主キー インデックスの主キーを取得し、主キー インデックスを通じて完全な行レコードを検索します。

たとえば、高さが 3 の補助インデックス ツリーでデータを検索する場合、指定された主キーを見つけるには補助インデックス ツリーを 3 回トラバースする必要があります。クラスター化インデックス ツリーの高さも 3 の場合、完全な行データが配置されているページを最終的に見つけるには、クラスター化インデックス ツリーを 3 回検索する必要があります。したがって、最終的なデータ ページを取得するには、合計 6 回の論理 IO アクセスが必要です。

5. MySQL インデックス管理

1. 機能

1. インデックスの機能は検索を高速化することです

2. MySQL の主キー、ユニーク、ジョイントユニークもインデックスです。検索を高速化するだけでなく、これらのインデックスには制約もあります。

2. MySQLでよく使われるインデックス

通常のインデックスINDEX: 検索を高速化

ユニーク インデックス: - 主キー インデックス: 検索の高速化 + 制約 (空ではない、繰り返し不可) - ユニーク インデックス UNIQUE: 検索の高速化 + 制約 (繰り返し不可)

結合インデックス: -PRIMARY KEY(id,name): 結合主キーインデックス -UNIQUE(id,name): 結合ユニークインデックス -INDEX(id,name): 結合共通インデックス

3. 2つの主要なインデックスの種類: ハッシュとBツリー

#上記のインデックスを作成するときに、インデックスの種類を指定できます。これは2つのカテゴリに分かれています。
ハッシュインデックス: 単一クエリは高速、範囲クエリは低速
Btree 型インデックス: B+ ツリー。レイヤーが増えるほど、データ量が指数関数的に増加します (InnoDB がデフォルトでサポートしているため、これを使用します)

#異なるストレージエンジンは異なるインデックスタイプをサポートします
InnoDB は、トランザクション、行レベルのロック、B ツリー、フルテキスト、およびその他のインデックスをサポートしますが、ハッシュ インデックスはサポートしません。
MyISAM はトランザクションをサポートしていませんが、テーブル レベルのロック、B ツリー、フルテキスト、およびその他のインデックスをサポートしていますが、ハッシュ インデックスはサポートしていません。
メモリはトランザクションをサポートしていませんが、テーブル レベルのロック、B ツリー、ハッシュ、およびその他のインデックスをサポートしていますが、フルテキスト インデックスはサポートしていません。
NDB はトランザクション、行レベルのロック、ハッシュ インデックスをサポートしますが、B ツリー、フルテキスト、その他のインデックスはサポートしません。
アーカイブはトランザクションをサポートしていませんが、テーブル レベルのロックはサポートしています。B ツリー、ハッシュ、フルテキスト、およびその他のインデックスはサポートしていません。

4. インデックスの作成/削除の構文

#方法1: テーブルを作成する CREATE TABLE テーブル名 (
 フィールド名 1 データ型 [整合性制約…]、
 フィールド名 2 データ型 [整合性制約...]、
 [ユニーク | フルテキスト | 空間] インデックス | キー
 [インデックス名] (フィールド名 [(長さ)] [ASC | DESC]) 
 );


#方法 2: CREATE は既存のテーブルにインデックスを作成します CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX index name ON table name (field name [(length)] [ASC | DESC]);


#方法3: ALTER TABLE 既存のテーブルにインデックスを作成します ALTER TABLE テーブル名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
 インデックス名(フィールド名[(長さ)] [ASC |DESC]);
 
#インデックスを削除します: DROP INDEX インデックス名 ON テーブル名;

#方法1 テーブルt1を作成します(
 id int、
 名前文字、
 年齢 int、
 性別列挙型('男性','女性'),
 ユニークキー uni_id(id)、
 index ix_name(name) #indexにキーがありません
);


#方法2 t1(age) にインデックス ix_age を作成します。

#方法3 alter table t1 add index ix_sex(sex);

#Viewmysql> show create table t1;
| t1 | テーブル `t1` を作成する (
 `id` int(11) デフォルト NULL,
 `name` char(1) デフォルト NULL,
 `age` int(11) デフォルト NULL,
 `sex` enum('男性','女性') デフォルト NULL,
 ユニークキー `uni_id` (`id`)、
 キー `ix_name` (`name`),
 キー `ix_age` (`age`),
 キー `ix_sex` (`sex`)
) エンジン=InnoDB デフォルト文字セット=latin1

6. テストインデックス

1. 準備

#1. テーブルを準備する create table s1(
id int、
名前varchar(20),
性別文字(6)
メールアドレスvarchar(50)
);

#2. バッチでレコードを挿入するストアドプロシージャを作成します。区切り文字は $$ です。#ストアドプロシージャの終了記号を $$ と宣言します。
プロシージャ auto_insert1() を作成する
始める
 i int をデフォルトで 1 と宣言します。
 i<3000000の場合
 s1 に値 (i、'duoduo'、'male'、concat ('duoduo'、i、'@oldboy')) を挿入します。
 i=i+1 と設定します。
 終了しながら;
END$$ #$$区切り記号の終了; #セミコロンを終了記号として再宣言します#3. ストアド プロシージャを表示します show create procedure auto_insert1\G 

#4. ストアド プロシージャ call auto_insert1(); を呼び出します。

#マシンのパフォーマンスを確認するには、一定時間待ちます

ヒント: テーブルの作成にかかる時間はマシンのパフォーマンスによって異なりますので、しばらくお待ちください。

2. インデックスなしでクエリ速度をテストする

#インデックスなし: MySQL は、ID が 333333333 に等しいレコードがあるかどうかわかりません。データ テーブルを最初から最後までスキャンすることしかできません。この時点では、ディスク ブロックと同じ数の IO 操作があるため、クエリ速度は非常に遅くなります。mysql> select * from s1 where id=333333333;
空セット（0.33秒）

3. テーブルにすでに大量のデータが存在する場合、フィールドセグメントのインデックスの作成には非常に時間がかかります。

4. インデックスが確立された後、フィールドをクエリ条件として使用すると、クエリ速度が大幅に向上します。

追伸:

1. MySQL はまず B+ ツリー検索原理に基づいてインデックス テーブルを検索し、ID が 333333333 のレコードが存在しないことをすぐに検出します。IO が大幅に削減されるため、速度が大幅に向上します。

2. mysql のデータ ディレクトリにテーブルがあり、それが多くのハード ディスク領域を占有していることがわかります。

3. 以下の点にご注意ください

V. 結論

#1. 検索条件フィールドのインデックスを必ず作成してください。たとえば、select * from s1 where id = 333; のように、idのインデックスを追加する必要があります。

#2. テーブルにすでに大量のデータがある場合、インデックスの作成には非常に時間がかかり、ハードディスクの容量を消費します。インデックスが作成されると、クエリの速度が速くなります。たとえば、create index idx on s1(id); は、テーブル内のすべてのデータをスキャンし、id をデータ項目として使用してインデックス構造を作成し、ハードディスク上のテーブルに格納します。
構築後はクエリが非常に高速になります。

#3. innodb テーブルのインデックスは s1.ibd ファイルに保存されますが、myisam テーブルのインデックスは別のインデックス ファイル table1.MYI に保存されることに注意してください。

MySAM インデックス ファイルとデータ ファイルは別々であり、インデックス ファイルにはデータ レコードのアドレスのみが保存されます。
InnoDB では、テーブル データ ファイル自体は B+Tree (BTree、Balance True の略) に従って編成されたインデックス構造であり、このツリーのリーフ ノード データ フィールドには完全なデータ レコードが格納されます。
このインデックスのキーはデータ テーブルの主キーであるため、InnoDB テーブル データ ファイル自体が主インデックスになります。
InnoDB のデータ ファイルは主キーに従ってクラスター化されるため、InnoDB ではテーブルに主キーが必要です (MyISAM では必要ありません)。
明示的に定義されていない場合、MySQL システムは、データ レコードを主キーとして一意に識別できる列を自動的に選択します。
この列が存在しない場合、MySQL は InnoDB テーブルの暗黙的なフィールドを主キーとして自動的に生成します。このフィールドの長さは 6 バイトで、型は長整数です。

7. インデックスを正しく使用する

1. インデックスミス

インデックスを作成すれば必ずクエリが高速化されるというわけではありません。インデックスを使用して期待されるクエリ速度の向上効果を得たい場合は、インデックスを追加するときに次の点に注意する必要があります。

1. スコープの問題、または条件が明確ではありません。条件に次の記号またはキーワードが含まれています: >、>=、<、<=、!=、between...and...、like、

より大きい、より小さい

等しくない! =

...と...の間

のように

2 インデックスとして識別度の高い列を選択するようにしてください。識別度の計算式は count(distinct col)/count(*) で、重複しないフィールドの比率を示します。比率が大きいほど、スキャンするレコード数が少なくなります。一意のキーの識別度は 1 ですが、ビッグ データでは、ステータス フィールドや性別フィールドの識別度は 0 になる場合があります。この比率に経験的な価値があるのか​​と疑問に思う人もいるかもしれません。この値は、さまざまな使用シナリオで判断するのが困難です。通常、結合するフィールドの値は 0.1 以上である必要があります。つまり、平均して 1 回のスキャンで 10 件のレコードが取得されます。

# まずテーブル内のすべてのインデックスを削除して、識別の問題に集中できるようにします。mysql> desc s1;
+--------+-------------+------+------+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------+-------------+------+------+--------+-------+
| id | int(11) | はい | MUL | NULL | |
| 名前 | varchar(20) | はい | | NULL | |
| 性別 | char(5) | YES | | NULL | |
| 電子メール | varchar(50) | はい | MUL | NULL | |
+--------+-------------+------+------+--------+-------+
セット内の行数 (0.00 秒)

mysql> s1 のインデックス a を削除します。
クエリは正常、影響を受けた行は 0 行 (0.20 秒)
レコード: 0 重複: 0 警告: 0

mysql> s1 のインデックス d を削除します。
クエリは正常、影響を受けた行は 0 行 (0.18 秒)
レコード: 0 重複: 0 警告: 0

mysql> desc s1;
+--------+-------------+------+------+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------+-------------+------+------+--------+-------+
| id | int(11) | はい | | NULL | |
| 名前 | varchar(20) | はい | | NULL | |
| 性別 | char(5) | YES | | NULL | |
| 電子メール | varchar(50) | はい | | NULL | |
+--------+-------------+------+------+--------+-------+
セット内の行数 (0.00 秒)

まず、テーブル内のすべてのインデックスを削除し、差別の問題に焦点を当ててみましょう。

テーブル s1 にバッチ レコードを追加するストアド プロシージャを作成します。名前フィールドの値は duoduo です。これは、名前フィールドの識別度が低いことを意味します (後で処理する性別フィールドについても同様です)。

b+ ツリーの構造を思い出してください。クエリ速度はツリーの高さに反比例します。ツリーの高さを非常に低く保つには、次の点に注意する必要があります。特定のレイヤーで、データ項目が左から右、小さいものから大きいものの順に並べられていること、つまり、左 1 < 左 2 < 左 3 < ...

識別力の低いフィールドの場合、値がすべて等しいため、サイズ関係を見つけることはできません。間違いなく、これらの等しい値のデータを b+ ツリーに格納したい場合は、ツリーの高さを増やすしかありません。フィールドの識別力が低いほど、ツリーの高さは高くなります。極端な場合、インデックス フィールドの値が同じになると、b+ ツリーはほぼスティックになります。この例では極端なケースです。名前フィールドのすべての値は「duoduo」です。

#これで結論が出ました。識別力の低いフィールドにインデックスを作成すると、インデックス ツリーの高さが非常に高くなりますが、具体的にどのような影響があるのでしょうか。 ? ?

＃1：条件がname = 'xxxx'の場合、'xxxx'がインデックスツリーにないことがすぐに判断できます（ツリー内のすべての値が'duoduo'であるため）。そのため、クエリ速度が非常に速くなります。

#2: 条件が name='duoduo' の場合、クエリを実行すると、ツリー内の特定の位置から明確な範囲を取得できず、下、下、下のみを調べることができます。 。 。これはテーブル全体のスキャンのIO時間とあまり変わらないので遅いです

分析する

3. = および in は任意の順序にすることができます。たとえば、a = 1、b = 2、c = 3 です。(a、b、c) インデックスは任意の順序で作成でき、MySQL クエリ オプティマイザはインデックスが認識できる形式に最適化するのに役立ちます。

4. インデックス列は計算に使用できません。列を「クリーン」な状態に保ってください。たとえば、from_unixtime(create_time) = '2014-05-29' の場合、インデックスは使用できません。理由は簡単です。b+ ツリーはフィールド値をデータ テーブルに格納します。ただし、検索時にはすべての要素を関数と比較する必要があり、明らかにコストがかかりすぎます。したがって、ステートメントはcreate_time = unix_timestamp('2014-05-29')と記述する必要があります。

5. および/または

#1、and と or の論理条件 1 かつ 2: 真とみなされるには、すべての条件が満たされている必要があります。 1 つの条件が満たされていない場合、最終結果は真になりません。 1 または 2: 1 つの条件が満たされている限り、最終結果は真になります。

#2、ANDの動作原理と条件：
a = 10 かつ b = 'xxx' かつ c > 3 かつ d = 4
索引：
ジョイントインデックス（d、a、b、c）を作成する
動作原理:
複数の連続ANDの場合：MySQLは、結合インデックスに従って左から右に高い識別性を持つインデックスフィールドを検索します（これにより、小さな範囲をすばやくロックできます）。つまり、d->a->b->cの順序でクエリを高速化します。

#3、orの動作原理条件：
a = 10 または b = 'xxx' または c > 3 または d = 4
索引：
ジョイントインデックス（d、a、b、c）を作成する

動作原理:
複数の連続したORの場合: MySQLは条件の順序に従って左から右に判断します。つまり、a->b->c->dです。

左の条件は満たされているが、インデックスフィールドの識別力が低い場合（名前、クエリの高速化）

6 左端のプレフィックス一致の原則は非常に重要な原則です。結合インデックスの場合、MySQL は範囲クエリ (>、<、between、like) に遭遇するまで右方向に一致を続け、その後一致を停止します (範囲が広く、インデックス速度が遅いことを意味します)。たとえば、(a、b、c、d) の順序で a = 1、b = 2、c > 3、d = 4 を作成した場合、d はインデックスで使用されません。(a、b、d、c) の順序でインデックスを作成した場合、それらはすべて使用でき、a、b、d の順序は任意に調整できます。

7. その他の状況

- 関数 select * from tb1 where river(email) = 'duoduo'; を使用します。
 
- 型の不一致 列が文字列型の場合、入力条件は引用符で囲む必要があります。それ以外の場合は...
 tb1 から * を選択します (email = 999)。
 
#ソート条件がインデックスの場合、選択フィールドもインデックスフィールドである必要があります。そうでない場合はヒットしません - order by
 s1 から名前を選択、電子メールで順序を指定、desc;
 インデックスで並べ替える場合、選択クエリ フィールドにインデックスが付いていないと、速度は依然として遅くなります。select email from s1 order by email desc;
 特別: 主キーをソートすると、それでも非常に高速になります。
 tb1 から * を選択し、nid desc で並べ替えます。
 
- 結合インデックスの左端のプレフィックス 結合インデックスが次の場合: (名前、メール)
 name と email -- インデックス名にヒット -- インデックスにヒットします。email -- インデックスがありません。- count(1) または count(column) の代わりに count(*) を指定しても、mysql では違いはありません。- create index xxxx on tb(title(19)) #テキスト タイプ、長さを指定する必要があります

その他の考慮事項

- select *の使用を避ける
- count(*) の代わりに count(1) または count(column)
- テーブルを作成するときは、varcharではなくcharを使用するようにしてください。
- テーブル内のフィールドの順序は固定長フィールドが最初です
- 複数の単一列インデックスの代わりに複合インデックス（クエリに複数の条件が頻繁に使用される場合）
- 短いインデックスを使用するようにしてください
- サブクエリの代わりにJOINを使用する
- テーブルを結合するときは、条件タイプが一貫していることを確認してください
- インデックスハッシュ値（重複が少ない）はインデックス作成に適していません。例：性別は適していません

8. ジョイントインデックスとカバーインデックス

1. 共同インデックス

結合インデックスとは、テーブル上の複数の列を 1 つのインデックスに結合することを指します。ジョイントインデックスを作成する方法は、単一のインデックスを作成する方法と同じです。唯一の違いは、次のように複数のインデックス列があることです。

mysql> テーブル t( を作成します。
 -> 整数、
 -> b 整数、
 -> 主キー(a)、
 -> キー idx_a_b(a,b)
 -> );
クエリは正常、影響を受けた行は 0 行 (0.11 秒)

では、共同インデックスはいつ使用する必要があるのでしょうか?この問題について議論する前に、共同インデックス内の結果を見てみましょう。本質的には、ジョイント インデックスは B+ ツリーですが、ジョイント インデックス内のキー値の数が 1 ではなく、>= 2 であるという違いがあります。次に、2 つの整数列で構成される結合インデックスについて説明します。図に示すように、2 つのキー値の名前が a と b であると仮定します。

これは、前に見た単一キーの B+ ツリーと何ら変わらないことがわかります。キー値はソートされており、すべてのデータはリーフ ノードを通じて論理的に順番に読み取ることができます。上記の例では、(1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2) で、データは (a,b) の順序で格納されます。

したがって、クエリ select * from table where a=xxx and b=xxx の場合、結合インデックス (a,b) を使用できることは明らかです。単一の列 a に対するクエリ select * from table where a=xxx の場合、インデックス (a,b) も使用できます。

しかし、列 b のクエリ、select * from table where b=xxx では、(a,b) インデックスは使用できません。実際、理由を見つけるのは難しくありません。リーフ ノードの b の値は 1、2、1、4、1、2 であり、明らかにソートされていません。したがって、列 b のクエリには (a,b) インデックスは使用できません。

結合インデックスの 2 つ目の利点は、最初のキーが同じ場合、2 番目のキーがすでにソートされていることです。たとえば、多くの場合、アプリケーションはユーザーのショッピング履歴を照会し、時間でソートし、最後に最新の 3 つの購入記録を取得する必要があります。この場合、結合インデックスを使用すると、次に示すように、インデックス自体がリーフ ノードですでにソートされているため、余分なソート操作を回避できます。

#===========テーブルを準備する==============
テーブル buy_log を作成します(
 ユーザーID int unsigned not null、
 buy_date 日付
);

buy_log値に挿入
(1,'2009-01-01')、
(2,'2009-01-01')、
(3,'2009-01-01')、
(1,'2009-02-01')、
(3,'2009-02-01')、
(1,'2009-03-01')、
(1,'2009-04-01');

テーブル buy_log を変更し、キー(ユーザーID) を追加します。
テーブル buy_log を変更し、キー (userid、buy_date) を追加します。

#==========検証===============
mysql> テーブル buy_log を作成します。
| buy_log | テーブル `buy_log` を作成します (
 `userid` int(10) 符号なし NOT NULL,
 `buy_date` 日付 デフォルト NULL、
 キー `userid` (`userid`),
 キー `userid_2` (`userid`,`buy_date`)
) エンジン=InnoDB デフォルト文字セット=utf8 |

# ここで、possible_keys には単一のインデックス userid と結合インデックス userid_2 という 2 つのインデックスが使用可能であることがわかりますが、インデックスのリーフ ノードには単一のキー値が含まれているため、オプティマイザは最終的にキー userid を使用することを選択します。理論的には、1 つのページにさらに多くのレコードを保存できます。mysql> explain select * from buy_log where userid=2;
+----+-------------+---------+-------+-----------------+---------+-------+-------+-------+-------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------+-------+-----------------+---------+-------+-------+-------+-------+
| 1 | SIMPLE | buy_log | ref | userid、userid_2 | userid | 4 | const | 1 | |
+----+-------------+---------+-------+-----------------+---------+-------+-------+-------+-------+
セット内の行数 (0.00 秒)

#次に、userid 1 の最新の 3 件の購入記録を取得するとします。このインデックスでは、userid=1 の場合、buy_date がすでにソートされているため、結合インデックス userid_2 を使用します。mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+----+-------------+---------+-------+-----------------+----------+-------+-------+--------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------+-------+-----------------+----------+-------+-------+--------------------------+
| 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid_2 | 4 | const | 4 | where の使用; index の使用 |
+----+-------------+---------+-------+-----------------+----------+-------+-------+--------------------------+
セット内の行数 (0.00 秒)

#ps: 追加のソートに「ファイルソートを使用」と表示されている場合、データが見つかった後に二次ソートが必要であることを意味します。#結合インデックス (a, b) の場合、次のステートメントは二次ソートなしでインデックスを直接使用できます。select ... from table where a=xxx order by b;

#次に、結合インデックス (a,b,c) の場合、次のステートメントでもインデックスを介して直接結果を取得できます。select ... from table where a=xxx order by b;
a=xxx かつ b=xxx のテーブルから ... を選択し、c で順序付けします。

#ただし、結合インデックス (a、b、c) の場合、次のステートメントはインデックスを介して直接結果を取得できず、インデックス (a、c) はソートされていないため、ファイルソート操作を自分で実行する必要があります。select ... from table where a=xxx order by c;

2. カバーインデックス

InnoDB ストレージ エンジンは、カバーリング インデックス (またはインデックス カバーリング) をサポートしています。つまり、クラスター化インデックス内のレコードをクエリせずに、補助インデックスからクエリ レコードを取得できます。

カバーリング インデックスを使用する利点の 1 つは、補助インデックスには行レコード全体のすべての情報が含まれていないため、サイズがクラスター化インデックスよりもはるかに小さくなり、大量の IO 操作が削減されることです。

注:カバーリング インデックス テクノロジは、InnoDB プラグインで初めて完成し、実装されました。つまり、InnoDB バージョン 1.0 未満または MySQL データベース バージョン 5.0 未満の場合、InnoDB ストレージ エンジンはカバーリング インデックス機能をサポートしません。

InnoDB ストレージ エンジンの補助インデックスには主キー情報が含まれているため、そのリーフ ノードに格納されるデータは (主キー 1、主キー 2、...、キー 1、キー 2、...) となります。例えば

select age from s1 where id=123 and name = 'duoduo'; #id フィールドにはインデックスがありますが、name フィールドにはインデックスがありません。この SQL はインデックスにヒットしますが、それをカバーしません。クラスター化インデックスで詳細情報を探す必要があります。
最良のケースは、インデックス フィールドがすべてをカバーし、インデックス作成のプロセス全体を使用してクエリを高速化し、結果を取得することです。mysql> desc s1;
+--------+-------------+------+------+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------+-------------+------+------+--------+-------+
| id | int(11) | いいえ | | NULL | |
| 名前 | varchar(20) | はい | | NULL | |
| 性別 | char(6) | YES | | NULL | |
| 電子メール | varchar(50) | はい | | NULL | |
+--------+-------------+------+------+--------+-------+
セット内の行数 (0.21 秒)

mysql> explain select name from s1 where id=1000; #インデックスなし+----+-------------+---------+-----------+---------+---------------+-------+----------+----------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+-------+----------+----------+------------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2688336 | 10.00 | where の使用 |
+----+-------------+--------+-----------+---------+---------------+-------+----------+----------+------------+-------------+
セット内の行、警告 1 (0.00 秒)

mysql> create index idx_id on s1(id); #インデックスクエリの作成は成功、0行が影響を受けました (4.16 秒)
レコード: 0 重複: 0 警告: 0

mysql> explain select name from s1 where id=1000; #補助インデックスはヒットしますが、インデックスはカバーされていません。クラスター化インデックスから名前を見つける必要もあります。
+----+-------------+--------+-----------+--------+---------------+---------+-------+-------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+---------+-------+-------+------+------+------+
| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+-----------+--------+---------------+---------+-------+-------+------+------+------+
セット内の行、警告 1 (0.08 秒)

mysql> explain select id from s1 where id=1000; #すべての情報は補助インデックスにあります。インデックスを使用するということは、インデックスをカバーすることを意味します+----+-------------+----------+-----------+---------+--------+---------------+----------+----------+-----------+-------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+---------+--------+----------+-----------+-------------+
| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | インデックスを使用 |
+----+-------------+--------+-----------+---------+---------------+---------+--------+----------+-----------+-------------+
セット内の行、警告 1 (0.03 秒)

インデックスをカバーするもう 1 つの利点は、特定の統計問題の場合です。前回の要約で作成したテーブル buy_log に基づくクエリ プランは次のようになります。

mysql> buy_log から count(*) を選択して説明します。
+----+-------------+---------+--------+---------------+---------+-------+------+------------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------+--------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | buy_log | index | NULL | userid | 4 | NULL | 7 | インデックスを使用 |
+----+-------------+---------+--------+---------------+---------+-------+------+-------------+
セット内の行数 (0.00 秒)

InnoDB ストレージ エンジンは、クラスター化インデックスをクエリして統計を実行することを選択しません。 buy_log テーブルにはセカンダリ インデックスがあり、セカンダリ インデックスはクラスター化インデックスよりもはるかに小さいため、セカンダリ インデックスを選択すると IO 操作を削減できます。したがって、オプティマイザーは上記のキーとして userid セカンダリ インデックスを選択します。

形式 (a, b) の結合インデックスの場合、通常、b でいわゆるクエリ条件を選択することはできません。しかし、それが統計的な操作であり、カバーインデックスである場合、オプティマイザは次のようにインデックスを使用することを選択します。

# 結合インデックス userid_2 (userid, buy_date)。通常、buy_date に応じてこのインデックスを使用することはできません。ただし、クエリ ステートメントが統計操作であり、カバー インデックスである特別なケースでは、buy_date がクエリ条件として使用されている場合に、この結合インデックスを使用することもできます。mysql> explain select count(*) from buy_log where buy_date >= '2011-01-01' and buy_date < '2011-02-01';
+----+-------------+---------+--------+---------------+----------+-------+-------+--------+---------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------+--------+---------------+----------+-------+------+---------------------------+
| 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | where の使用; index の使用 |
+----+-------------+---------+--------+---------------+----------+-------+------+---------------------------+
セット内の行数 (0.00 秒)

9. クエリ最適化ツール - 説明

explain コマンドは皆さんもよくご存知だと思います。具体的な使用方法とフィールドの意味については、公式 Web サイトの explain-output を参照してください。ここで強調しておきたいのは、行がコア インジケータであるということです。行数が小さいステートメントのほとんどは、非常に高速に実行する必要があります (例外もありますが、これについては後述します)。したがって、最適化ステートメントは基本的に行を最適化します。

実行計画: MySQL に実行操作を推定させる (通常は正確)
すべて < インデックス < 範囲 < インデックスマージ < 参照またはヌル < 参照 < eq_ref < システム/定数
ID、メールアドレス

遅い：
userinfo3 から name='alex' の * を選択します

select * from userinfo3 where name='alex' の説明
タイプ: ALL (フルテーブルスキャン)
userinfo3 から * を選択し、制限 1 を指定します。
素早い：
userinfo3 から email='alex' の * を選択します
型: const (go インデックス)

参考: https://www.jb51.net/article/140759.htm

10. 低速クエリの最適化の基本手順

0. 最初に実行して本当に遅いかどうかを確認し、SQL_NO_CACHEを設定します
1. Where条件の単一テーブルクエリで、最小の戻りレコードテーブルをロックします。この文は、返されるレコード数が最も少ないテーブルにクエリ ステートメントの where 句を適用し、テーブルの各フィールドを個別にクエリして、どのフィールドの識別度が最も高いかを確認することを意味します。
2. 実行計画が 1 の期待と一致しているかどうかを確認する方法を説明します (ロックされたレコードが少ないテーブルからクエリを開始します)。
3. order by limit形式のSQL文は、ソートされたテーブルを最初に検索します。
4. ビジネス側の使用シナリオを理解する
5. インデックスを追加するときは、インデックス作成の主要な原則を参照してください。
6. 結果を観察します。期待どおりでない場合は、0 から分析を続けます。

11. ログ管理が遅い

スローログ
- 実行時間 > 10
- インデックスが見つかりませんでした
- ログファイルのパス

構成：
- メモリ
'%query%' のような変数を表示します。
'%queries%' のような変数を表示します。
グローバル変数名 = 値を設定します
- 設定ファイル
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'

my.conf の内容:
slow_query_log = オン
slow_query_log_file = D:/....

注意: 設定ファイルを変更した後は、サービスを再起動する必要があります。

MySQL ログ管理

========================================================

エラー ログ: MySQL サーバーの起動、シャットダウン、および操作エラーを記録します。バイナリ ログ: binlog ログとも呼ばれ、SELECT 以外のデータベースの操作をバイナリ ファイルに記録します。クエリ ログ: クエリ情報を記録します。スロー クエリ ログ: 実行に指定された時間よりも長い時間がかかる操作を記録します。リレー ログ: スタンバイ データベースは、マスター データベースのバイナリ ログを独自のリレー ログにコピーし、ローカルで再生できるようにします。一般ログ: どのアカウント、どの期間、およびどのイベントが実行されたかを監査します。トランザクション ログまたは redo ログ: トランザクションの実行時間、チェックポイントなどの Innodb トランザクション関連の情報を記録します。
========================================================

1. バイナリログ

1. 有効にする

# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[ファイル名]]
# サービスmysqldを再起動します

2. 一時停止

//現在のセッションのみ SET SQL_LOG_BIN=0;
SQL_LOG_BIN=1 を設定します。

3. 表示

すべて表示:

# mysqlbinlog mysql.000002
時間別:
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
＃mysqlbinlog mysql.000002  -  stop-dateTime = "2012-12-05 11:02:54"
＃mysqlbinlog mysql.000002  - -start-dateTime = "2012-12-05 10:02:56"  - ストップ-dateTime = "2012-12-05 11:02:54"

バイトによる：

＃mysqlbinlogmysql.000002  -  Start-Position = 260
＃mysqlbinlogmysql.000002 -stop-position = 260
＃mysqlbinlogmysql.000002  -  Start-Position = 260  -  Stop-Position = 930

4。bin-logを切り捨てる（新しいbin-logファイルを生成）

a

b。

5. bin-logファイルを削除します

＃mysql -uroot -p123 -e 'リセットマスター'

2。クエリログ

一般的なクエリログを有効にします

＃vim /etc/my.cnf
[mysqld]
log [= dir \ [filename]]
# サービスmysqldを再起動します

3.スロークエリログ

スロークエリログを有効にします

＃vim /etc/my.cnf
[mysqld]
log-slow-queries [= dir \ [filename]]
long_query_time = n
# サービスmysqldを再起動します

mysql 5.6：

slow-query-log = 1
slow-query-log-file = slow.log
長いクエリ時間=3

スロークエリログを表示する

テスト：

ベンチマーク（count、expr）
ベンチマーク（50000000,2*3）を選択します。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。