mysqlインデックス最適化の使い方

通常、各本の最初の数ページは目次で、最後の数ページにはキーワード索引があります。

データベースの場合、システム テーブル (sysobjects など) はディレクトリであり、ターゲット フィールドのインデックスは本の巻末のキーワード インデックスのようなものです。

データベースにおけるディレクトリ(データディクショナリ)とインデックスの違い:ディレクトリは垂直方向、インデックスは水平方向です。

1. インデックスの機能に影響を与える要因

判別 (取得率)

データベースがインデックスに関する統計情報を収集しない場合、オプティマイザーは開始することができません。ステップごとに処理を進め、テーブル スキャン全体を通過してクエリを実行することしかできません。したがって、新しく作成されたインデックスでは統計を再実行する必要があります。そうしないと、インデックスが無効になります。

たとえば、テーブル TABLE1 があり、その中に「1」、「2」、「3」という 3 つの値を持つフィールド COL1 があります。統計を実行した結果、さまざまな値がデータベースに伝えられます。 TABLE1 のデータ内のフィールド COL1 の値の割合。表現は次のとおりです:

"1" – 12%;

"2" – 22%。

フィールド COL2 値があり、データの割合が次のとおりであるとします:

"A" - 50%;

"B" - 50%。

次に、クエリ ステートメント 1:

select * from TABLE1 where COL1 = "1" and COL2 = "A",

データベース オプティマイザーは、テーブル内のデータを見つけるためにフィールド COL1 のインデックスを優先します。 COL1 インデックスは、12% という狭い範囲内で結果セットを迅速に見つけることができます。逆に、クエリ ステートメント 2 の場合:

select * from TABLE1 where COL1 = "2" and COL2 = "A",

ステートメント 2 のクエリ条件では、データベースは COL2 のインデックスを優先します。 COL2 のインデックス 識別力が向上します。

上記からわかるように、データベース オプティマイザーは通常、識別性の高いインデックスを優先します (クエリ条件の場合、選択されるインデックスは条件によって異なる場合があります)。

データベース内のデータは変化するため、ある時点で収集された統計情報が時間が経つと古くなったり、データベースオプティマイザーに誤解を与えたりして、動作パフォーマンスの低下を引き起こす可能性があります。したがって、インデックスを最初に作成するときに統計を実行する必要があることに加えて、テーブル内のデータが変更されたときにも統計を実行する必要があります。経験: テーブル内のデータ量が 10% 変化すると、統計を再実行する必要があります。

2. 集計

レンジスキャン

テーブルサイズ:

小さなテーブル

中規模および大規模なテーブル

非常に大きなテーブル

ビジネスタイプ

OLTP および OLAP

関数とインデックス

関数、ステートメントのようなもの。 。 。

Substring(col_name,1, 3) vs. Substring(col_name, 3, 3)

like 'QQQ% vs like '%QQQ'

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

パフォーマンス武器

両刃の剣

インデックスペアの挿入操作の影響 (Oracle)

挿入操作に対するインデックスの影響 (MySQL)

パフォーマンスに対するインデックスとイネーブラーの影響の比較

インデックスの概要

インデックスを使用して、重要なデータへの効率的なアクセスを実現します。ただし、各インデックスがデータベース更新に追加のオーバーヘッドをもたらすことを知っておく必要があります。これは、非効率的なインデックスがデータベースに惨事をもたらす可能性があることを意味します。

データベースの場合、重要なデータの読み取りに重点を置き、最も効率的なアクセス パスを提供する必要があります。このための基本的な戦略は、インデックスを構築することです。インデックスは効率的なアクセスを提供しますが、追加のシステム オーバーヘッドももたらします。オーバーヘッドは、ディスク領域のオーバーヘッドとプロセッサーのオーバーヘッドに分けられます。次に、プロセッサのオーバーヘッドについて説明します。レコードがテーブルに挿入またはテーブルから削除されるたびに、それに応じてテーブル上のすべてのインデックスを調整する必要があります。この調整は、インデックス付きフィールドが更新されるたびに行われます。たとえば、インデックスのないテーブルへのデータの挿入に 100 単位の時間がかかる場合、インデックスが追加されるたびに 100 ～ 250 単位の時間が追加されます。興味深いことに、インデックスを維持するオーバーヘッドは、単純なトリガーのオーバーヘッドとほぼ同等です。

インデックス作成の最前線で最も人気のある情報の一部を紹介します。この情報は、developerWorks から提供されており、通常は参照する価値があると考えられるため、リストされています。

1. クエリが適切な時間内に終了する必要がある場合。インデックスを追加すると更新操作が遅くなり、追加の領域が消費されるため、インデックスの追加は避けてください。場合によっては、複数のクエリをカバーする大きなインデックスが存在する場合があります。

1. カーディナリティが大きい列はインデックス付けに非常に適しています。

3. 管理オーバーヘッドを考慮して、インデックスに 5 つを超える列を使用しないようにします。

4. 複数列インデックスの場合、クエリ内で最も参照される列を定義の先頭に置きます。

5. 既存のインデックスと同様のインデックスを追加しないでください。これにより、オプティマイザの作業が増加し、更新操作が遅くなります。代わりに、既存のインデックスを変更して追加の列を含める必要があります。たとえば、テーブル (c1,c2) にインデックス i1 があるとします。クエリで「wherec2=?」が使用されていることに気づき、(c2) にインデックス i2 を作成します。しかし、この同様のインデックスは何も追加せず、i1 の冗長性を提供するだけであり、追加のオーバーヘッドが発生します。

6. テーブルが読み取り専用で、多くの行が含まれている場合は、インデックスを定義し、CREATE INDEX で INCLUDE 句を使用して、クエリで参照されるすべての列をインデックスに含めることができます (INCLUDE 句に含まれる列は含まれません)。含まれます) インデックスの一部ではありませんが、追加のデータ フェッチを避けるために、単にインデックス ページの一部として格納されます)。

データ ウェアハウス (クエリ システム データベース) の場合、より多くのインデックスを確立できます (インデックスとデータの比率は 1:1 にすることができます)。

インデックスを使用するかどうかを決定するときは、検索率に注目できます。つまり、インデックスの有効性を判断する基準は、キー値を一意の条件として使用して検索されたデータの割合です。パーセンテージが低いほど、インデックスの効率が高くなります。この結論は、ディスク アクセスの相対的なパフォーマンスなど、いくつかの仮定に基づいています。

データはブロックを介して操作されるため、インデックスキー値に関連するレコードの物理的な位置が隣接しているかどうかも重要です。インデックス作成後、インデックスキーが指すレコードがテーブル全体に分散している場合、たとえテーブル内で占める割合が小さくても、テーブル全体に分散しているため、インデックスのパフォーマンスが大幅に低下します。ディスク。

また、関数と型変換によってインデックスが無効になる可能性があることにも注意してください。

以上がmysqlインデックス最適化の使い方の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。