Mysqlインデックスと最適化

6152 ワード
索引 mysql
インデックスとは
インデックスは何ですか?みんな辞書を使ったことがあると信じています.どうやって厚い新華辞書から見つけたの?どのようにして本から必要な章に迅速に位置付けますか?私たちはすべて本の中のディレクトリを通じて、それからディレクトリのページ番号に基づいて私たちの情報に位置します.
同じmysqlでもこのようにディレクトリを用意してくれました.sql文でクエリーするときは、インデックスを使用しないで、インデックスを使用する方法を使用します.あなたのクエリーにかかる時間を決めました.
私たちのアプリケーションを最適化する際には、まずインデックスを使用することを考慮し、他の方法でパフォーマンスを向上させようとすると、時間の無駄になります.インデックスを使用してパフォーマンスを最大限に向上させ、他のテクノロジーが使用できるかどうかを考慮する必要があります.
だから、インデックスは何ですか.--は、ソートされたディレクトリテーブルです.
インデックスの利点
インデックスのないデータテーブル:
インデックスのないデータテーブルは、無秩序なデータ行の集合です.私たちはそこから条件に合った1行の記録を見つけなければなりません.表全体をスキャンして、質問する必要があります.あなたはそうですか.はい、残ります.いいえ、次を探しています.もちろん、sqlでクエリーされたデータ行も集合です.
インデックスが存在するデータテーブル:
まずデータシートを見てみましょうIdにインデックスを追加すると、生成されたインデックスファイルはこのようになる可能性があります.
インデックスが存在するデータテーブルは、クエリー中にテーブル全体のデータ行をスキャンする必要はありません.たとえば、Idにインデックスを追加し、検索する条件がId=13である場合、インデックスのスキャンを開始し、条件に合致する3つのレコード行を見つけました.スキャンがIdが14のデータ行に到達すると、この値は私たちが検索した値より高い.インデックスは分類されているので、14を含むレコードを読み取ると、Id=13に一致するデータがなくなり、スキャンが行われなくなることがわかります.
インデックス・ファイルをクエリーするときは、リニア・スキャン(1番目から、その後が条件に合わないと判断した最後まで)を使用します.もう1つの方法は、線形走査を経ずに最初のマッチング項目に直接位置決めできる位置決めアルゴリズムの使用である.これにより、検索時間が大幅に短縮されます.さまざまなデータベースでは、インデックス値を迅速に見つけるためにさまざまなテクノロジーが使用されています.インデックスというツールが何に使われているのか、どのように使えばいいのかを知る必要があります.
上記の例では、インデックスを使用しない場合とインデックスを使用してクエリーを行う場合の違いがわかります.まだ迫力を与えられない場合は、例を挙げます.
              ：t1、t2、t3。    1000    。                         ：

SELECT t1.i1,t2.i2,t3.i3
FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.i1 = t2.i2 AND t2.i2 = t3.i3;


          1000    ，     3       。              ，t1             
t2        ，  t2       ，      t3       。
                ：1000x1000x1000(10 ) ，       100  。

                      ，            ：

1.     t1         ，             。
2.     t2    ，     t1         ，       t3    ，     t1        
3.     t1              ，     t1        。

      t1          ，     t2、t3        。            ，                   100  。

Mysqlインデックスの使用方法
  • クエリー操作において、WHERE句で与える条件に一致するデータ行を、
    • にできるだけ早く探し出す.
  • 関連操作で他のデータテーブルに一致するデータ行をできるだけ早く探し出す
    • .
  • は、MIN()またはMAX()のような集約関数を使用する場合、データ列にインデックスがある場合、その最小値および最大値は、全テーブルスキャン
    • を必要とすることなく、迅速に見つけることができる.
  • mysqlは、ORDER BY句およびGROUP BY句の分類およびパケット操作をインデックスを使用して行うことが多い.
  • mysqlは、インデックスを使用してクエリーがインデックスファイルをスキャンしてからデータ行を読み出すことを避けることができます.MyISamデータテーブルのインデックス付きデータ列から値を選択した場合、他のデータ列を読み込むつもりはありません.この場合、Mysqlがこのインデックスファイルからインデックス値を読み込むと、実際にこの値が取得されます.この値は、データ行を読み込むことで得られるはずです.ここで2回値を読み込む必要はありません.これも私たちがSELECT * FROM tableNameという文を書くことを提案しない理由です.第1に、フィールド値を1つだけ取得する必要があり、この値にインデックスがある場合、例えばユーザのIdプライマリ・キー・インデックスは、2回のテーブルをクエリーする必要がなく、直接取得すればよい.2つ目は、文章の内容など、不要なフィールドのようなスペースの大きいフィールドでは、ネットワーク帯域幅が大きくなります.

    • インデックスのデータベースへの格納方法
    異なるストレージエンジンでは、インデックス実装の詳細が異なります.MyISAMデータテーブルにとって、データテーブルのデータ行はデータファイルにあります.インデックス値はインデックスファイルにあります.1つのデータ・テーブルに複数のインデックスがあり、すべてのインデックスが同じインデックス・ファイルに格納されます.インデックスファイル内の各インデックスは、分類されたキーレコード配列から構成されます.これらの配列は、データテーブルファイルにすばやくアクセスするために使用されます.InnoDBストレージエンジンは、InnoDB型のすべてのデータテーブルのデータとインデックスのストレージを管理するテーブルスペースを使用します.また、InnoDBエンジンを使用する各データテーブルに独自のテーブルスペースを作成するように構成することもできます.
    インデックスの欠点
    インデックスの欠点については、インデックスとは何かを最初に紹介したときに挙げた例に戻ります.カタログと内容がある本があります.この本に新しい文章を追加するには、次の手順に従います.
  • この文章に追加する必要がある場所を見つけて、挿入します.
  • この本のカタログを更新して、読者がこの文章に迅速に位置づけることができます.

    • 新しい文章が増えていくと、ディレクトリも厚くなります.そうだ~インデックスは一定のディスク領域を占有する.
    また、新しい文章を追加するたびに、次のディレクトリを更新しなければなりません.一定の時間がかかります.インデックスも同様なので、インデックスのあるデータテーブルの挿入、削除、更新などの操作にかかわると、インデックスはこれらの操作のパフォーマンスを低下させます.このような状況が発生するのは、1つのデータ行を挿入するため、データテーブルのデータ行だけでなく、変更したデータ行のインデックスを求めて変更するためです.1つのデータ・テーブルにインデックスが多ければ多いほど、変更が必要になり、平均パフォーマンスが低下します.
    ほとんどの数のテーブルは、読み取り操作が書き込み操作よりも多くなりますが、書き込み操作の回数が多いテーブルでは、インデックス更新のオーバーヘッドが非常に大きい場合があります.
  • MyISAMデータテーブルにとって、大量のインデックス1つのデータテーブルは、インデックスファイルがデータファイルよりも速く最大サイズに達する可能性があります.
  • InnoDB共有テーブル空間に格納されたInnoDBすべてのデータテーブルは、1つの記憶空間を共有する.インデックスを追加すると、テーブルスペースに格納されるスペースがより速く減少します.MyISAMデータテーブルとは異なり、InnoDBデータテーブルの共有テーブル空間はオペレーティングシステムファイルサイズに制限されない.InnoDBエンジンを使用するデータテーブルごとに独自のテーブルスペースを使用するように構成されている場合、データとインデックスが1つのファイルに保存され、インデックスが増加すると、データテーブルのサイズがファイルの最大長に近づくのが速くなります.

    • 上記の時間と空間の欠点がありますが、カタログがない本を見たことがありますか.だからインデックスを使うときはバランスを考えて、使わなければならないのではないでしょうか.何度も基準テストをすることができます.もちろん、時間が許すなら.
    インデックスの選択
    検索、分類、またはグループ化に使用するデータ列のインデックスを作成します.
    どのフィールドにインデックスを追加してmysqlのオプティマイザに見つけて使用できるようにするデータテーブルがありますか?
           、             。                  。              
    ：
    1.     WHERE        
    2.               ：SELECT * FROM aTable INNER JOIN bTable ON aTable.Id = bTable.Id; 
    3.   ORDER BY   GROUP BY          。

   SELECT                            。

    データ列の次元を考慮
    データ列の次元は、その格納される非繰返し値の数に等しい.たとえば、1,5,19,75,5,1というデータ列があります.その次元は4です(重複値を除いた後).データ列の次元の最大値は、テーブル内のデータ行の数に等しい.データ列の次元値が高いほど重複値が少なくなり、インデックスの使用効果も向上します.
    以前、テーブルを設計したとき、データテーブルのStatus（ ）にインデックスを付けるかどうか悩んだことがあります.このフィールドはWHERE文によく現れるからです.実際には、ENUMデータ型のフィールドを使用して、データテーブル内のデータ行の出現頻度が30%を超えると、mysqlのクエリー・オプティマイザは通常、インデックスをスキップして全テーブル・スキャンを行います.現在のオプティマイザはより複雑で、他の要因を考慮することができます.パーセントはmysqlがインデックスを使用せずに全テーブルスキャンを行うことを決定する唯一の根拠ではありません.したがって、データ列の次元があまり底をついていないと思う場合は、DESCまたはEXPLAINを使用して、オプティマイザがインデックスに使用されているかどうかを検証することができます.
    複合インデックスについてn個のデータ列の複合インデックスを作成すると、実際にmysqlで使用できるn個のインデックスが作成されます.何と言いますか.例:
    
KEY `indexName` (`column1`,`column2`,`column3`);

    インデックスの順序に注意してください.クエリーで使用できるインデックスの順序は次のとおりです.
    
column1， column2， column3
column1， column2
column1

    mysqlでは、一番左のインデックスフィールドが含まれていないインデックスは使用できません(column1).例えば:column2，column3.column1,column3を使えば?mysqlはインデックスを使用できますか?答えはいいですが、column1というインデックスしか使えません.column3というインデックスでは使えません.すなわちmysqlはcolumn1まで使用してマッチングの範囲を縮小することができるが、このインデックスはこの値の組合せには使用できない.
    IBUSY
    キャッシュされた動作:例れい