MySQL データベースの最適化: インデックスの実装原則と使用状況の分析

この記事では、例を使用して、MySQL データベースの最適化のためのインデックス実装の原則と使用方法を説明します。ご参考までに、詳細は以下の通りです。

索引

インデックスとは何か

インデックスは、特定の値を持つレコードをすばやく見つけるために使用されます。すべての MySQL インデックスは B ツリーの形式で保存されます。インデックスがない場合、クエリを実行するときに、MySQL は最初のレコードから始めて、要件を満たすレコードが見つかるまでテーブル全体のすべてのレコードをスキャンする必要があります。テーブル内のレコード数が多いほど、この操作にかかるコストは高くなります。検索条件として使用される列にインデックスが作成されている場合、MySQL はレコードをスキャンせずに対象レコードの場所をすばやく取得できます。テーブルに 1000 件のレコードがある場合、インデックスを使用してレコードを検索すると、レコードを順番にスキャンするよりも少なくとも 100 倍高速になります。

インデックス分類

主キーインデックス

主キーは一意のインデックスですが、「PRIMARY KEY」として指定する必要があります。 AUTO_INCREMENT 列を使用したことがある場合は、主キーなどの概念にすでに精通している可能性があります。主キーは通常、テーブルを作成するときに指定されます。たとえば、「CREATE TABLE tablename ([…], PRIMARY KEY (列リスト));」のようになります。ただし、「ALTER TABLE tablename ADD PRIMARY KEY (column list);」のようにテーブルを変更して主キーを追加することもできます。各テーブルには主キーを 1 つだけ設定できます。

主キーインデックスを作成する

テーブルが列を主キーとして設定すると、その列は主キーインデックスになります。

テーブル aaa を作成
(id int unsigned 主キー auto_increment,
名前 varchar(32) nullでないデフォルト '');

これは主キーインデックスである id 列です。

テーブル bbb (id int 、name varchar(32) not null default '') を作成します。

テーブルの作成時に主キーインデックスを指定しなかった場合は、テーブルの作成後に次のコマンドで追加することもできます。

例：

テーブル名を変更し、主キー (列名) を追加します。

主キーインデックスを削除する

テーブル記事を変更し、主キーを削除します。

クエリインデックス

desc テーブル名; インデックス名は表示できません テーブル名からインデックスを表示 テーブル名からキーを表示

全文索引

テーブル構造を作成する

CREATE TABLE記事（
    id INT UNSIGNED AUTO_INCREMENT NOT NULL 主キー、
    タイトル VARCHAR(200)、
    本文テキスト、
    FULLTEXT (タイトル、本文)
   )エンジン=myisam 文字セット utf8;
記事 (タイトル、本文) に値を挿入
   (「MySQL チュートリアル」、「DBMS は DataBase の略です...」)、
   (「MySQL をうまく使う方法」、「... を経験した後」)、
   (「MySQL の最適化」、「このチュートリアルでは次の内容を紹介します...」)、
   (「1001 MySQL トリック」、「1. mysqld を root として実行しないでください。2. ...」)、
   ('MySQL vs. YourSQL','次のデータベース比較では...'),
   ('MySQL セキュリティ','適切に構成されている場合、MySQL ...');

誤った使用法:

select * from articles where body like '%mysql%';間違った使用法のインデックスは有効になりません

正しい使い方:

select * from articles where match(title,body) against ( 'database')

例:

1. MySQLでは、フルテキストインデックスはMyISAMに対してのみ有効です。
2. MySQLが提供するフルテキストは英語に有効です -> Sphinx（coreseek）テクノロジープロセス中国語
3. 使用方法は、match (フィールド名...) against ('キーワード') です。
4. 全文索引: ストップワード。テキストに索引を作成する回数は無限にあるため、一般的な単語や文字は作成されません。これらの単語はストップワードと呼ばれます。たとえば、(a、b、mysql、the)

mysql> select match(title,body) against ('database') from articles; (出力は各行とデータベース間の一致度です)

ユニークインデックス

このタイプのインデックスは基本的に以前の「通常のインデックス」と同じですが、1 つの違いがあります。インデックス列内のすべての値は 1 回しか出現できず、つまり一意である必要があります。一意のインデックスは次の方法で作成できます。

インデックスを作成します。たとえば、CREATE UNIQUE INDEX <インデックス名> ON tablename (列リスト) です。

テーブルを変更します。たとえば、ALTER TABLE tablename ADD UNIQUE [インデックス名] (列リスト) を実行します。

テーブルを作成するときにインデックスを指定します。例: CREATE TABLE tablename ([…], UNIQUE [インデックス名] (列リスト));

テーブル構造を作成する

テーブル ddd(id int primary key auto_increment 、 name varchar(32) unique) を作成します。

知らせ

一意のフィールドは NULL にすることができ、複数の NULL を持つことができますが、特定のコンテンツの場合は繰り返すことはできません。

しかし、空の文字列を繰り返すことはできません。

通常のインデックス

通常のインデックス (キーワード KEY または INDEX によって定義されたインデックス) の唯一のタスクは、データへのアクセスを高速化することです。したがって、クエリ条件 (WHEREcolumn=) またはソート条件 (ORDERBYcolumn) で最も頻繁に出現する列に対してのみインデックスを作成する必要があります。可能な限り、インデックスを作成するには、最も整然としたコンパクトなデータ (整数列など) を持つ列を選択する必要があります。

テーブルcccを作成(
id int 符号なし、
名前varchar(32)
）
テーブル (列 1、列名 2) にインデックス インデックス名を作成します。

インデックスの仕組み

データベースインデックスは、データベーステーブル内のデータの迅速なクエリと更新を支援するためにデータベース管理システム内でソートされたデータ構造です。インデックスは通常、B ツリーとそのバリエーションである B+ ツリーを使用して実装されます。
データベースシステムでは、データに加えて、特定の検索アルゴリズムを満たすデータ構造も保持されます。これらのデータ構造は、何らかの方法でデータを参照 (ポイント) するため、これらのデータ構造に高度な検索アルゴリズムを実装できます。このデータ構造はインデックスです。
テーブルにインデックスを設定するには、代償が伴います。まず、データベースのストレージ容量が増加し、次に、データの挿入と変更に時間がかかります (インデックスもそれに応じて変更する必要があるため)。

上の図は、インデックス作成の 1 つのアプローチを示しています。左側は 2 つの列と 7 つのレコードを持つデータテーブルです。左端はデータレコードの物理アドレスです (論理的に隣接するレコードが、必ずしもディスク上で物理的に隣接するとは限らないことに注意してください)。 Col2 の検索を高速化するために、右に示すようにバイナリ検索ツリーを維持できます。各ノードには、インデックスキー値と、対応するデータレコードの物理アドレスへのポインターが含まれています。このようにして、バイナリ検索を使用して、O(log2n) の複雑さ内で対応するデータを取得できます。

インデックスを作成すると、システムのパフォーマンスが大幅に向上します。

まず、一意のインデックスを作成することにより、データベーステーブル内の各データ行の一意性が保証されます。
2 番目に、データの取得速度が大幅に向上します。これは、インデックスを作成する主な理由でもあります。
3 番目に、テーブル間の接続を高速化できます。これは、データの参照整合性を実装する際に特に重要です。
4 番目に、データ取得にグループ化句と並べ替え句を使用すると、クエリでのグループ化と並べ替えにかかる時間も大幅に短縮されます。
5 番目に、インデックスを使用すると、クエリプロセス中に最適化されたクエリを使用して、システムパフォーマンスを向上させることができます。

インデックスを追加すると多くの利点があるので、テーブル内のすべての列にインデックスを作成しないのはなぜかと疑問に思う人もいるかもしれません。なぜなら、インデックスを追加することには多くの欠点もあるからです。

まず、インデックスの作成と維持には時間がかかり、データの量が増えるにつれてこの時間も長くなります。
2 番目に、インデックスには物理的なスペースが必要です。データテーブルが占めるデータスペースに加えて、各インデックスも一定量の物理的なスペースを占有します。クラスター化インデックスを作成する場合、必要なスペースはさらに大きくなります。
3 番目に、テーブル内のデータを追加、削除、変更するときに、インデックスも動的に維持する必要があり、データのメンテナンスの速度が低下します。

インデックスは、データベーステーブル内の特定の列に作成されます。インデックスを作成するときは、どの列にインデックスを付けることができ、どの列にインデックスを付けることができないかを考慮する必要があります。一般的に、インデックスは次の列に作成する必要があります。検索を高速化するために、頻繁に検索される列に作成します。列の一意性を強制し、テーブル内のデータ構造を編成するために主キーとして機能する列に作成します。結合を高速化するために、主に外部キーである結合で頻繁に使用される列に作成します。範囲に基づいて頻繁に検索される列にインデックスを作成します。これは、インデックスがすでにソートされており、指定された範囲が連続しているためです。頻繁にソートされる列にインデックスを作成します。これは、インデックスがすでにソートされているため、クエリでインデックスのソートを利用してソートクエリ時間を高速化できるためです。条件の決定を高速化するために、WHERE 句で頻繁に使用される列にインデックスを作成します。

同様に、インデックスを作成してはいけない列もいくつかあります。一般的に、インデックスを作成すべきでない列には次の特性があります。

まず、クエリでほとんど使用または参照されない列にインデックスを作成しないでください。これらの列はほとんど使用されないため、インデックスを作成しても作成しなくてもクエリ速度は向上しないからです。逆に、インデックスを追加すると、システムのメンテナンス速度が低下し、必要なスペースが増加します。
次に、データ値が非常に少ない列にはインデックスを追加しないでください。これは、人事テーブルの性別列など、これらの列には非常に少ない値があるためです。クエリ結果では、結果セットのデータ行がテーブル内のデータ行の大部分を占めます。つまり、テーブルで検索する必要があるデータ行の割合が非常に大きいということです。インデックスを追加しても、検索速度は大幅に向上しません。
3 番目に、テキスト、イメージ、ビットデータ型として定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に多いか、値が非常に少ないためです。
4 番目に、変更パフォーマンスが検索パフォーマンスよりもはるかに大きい場合は、インデックスを作成しないでください。これは、修正パフォーマンスと検索パフォーマンスが互いに矛盾しているためです。インデックスを追加すると、検索パフォーマンスは向上しますが、変更パフォーマンスは低下します。インデックスの数を減らすと、変更パフォーマンスは向上しますが、取得パフォーマンスは低下します。したがって、変更パフォーマンスが検索パフォーマンスよりもはるかに高い場合は、インデックスを作成しないでください。

データベースの機能に応じて、データベースデザイナーで、一意のインデックス、主キーインデックス、クラスター化インデックスの 3 種類のインデックスを作成できます。

ユニークインデックス

一意のインデックスとは、その中の 2 つの行が同じインデックス値を持つことを許可しないインデックスです。
ほとんどのデータベースでは、既存のデータに重複するキー値がある場合、新しく作成された一意のインデックスをテーブルに保存することはできません。データベースは、テーブル内に重複するキー値を作成する新しいデータの追加を防止する場合もあります。たとえば、従業員テーブル内の従業員の姓 (lname) に一意のインデックスが作成されると、2 人の従業員が同じ姓を持つことはできません。主キーインデックスデータベーステーブルには、多くの場合、テーブル内の各行を一意に識別する値を持つ列または列の組み合わせがあります。この列はテーブルの主キーと呼ばれます。データベースダイアグラム内のテーブルの主キーを定義すると、特定の種類の一意のインデックスである主キーインデックスが自動的に作成されます。このインデックスでは、主キーの各値が一意である必要があります。また、クエリで主キーインデックスを使用すると、データへの高速アクセスも可能になります。クラスター化インデックスクラスター化インデックスでは、テーブル内の行の物理的な順序は、キー値の論理的な (インデックス) 順序と同じです。テーブルにはクラスター化インデックスを 1 つだけ含めることができます。
インデックスがクラスター化インデックスでない場合、テーブル内の行の物理的な順序はキー値の論理的な順序と一致しません。通常、クラスター化インデックスは非クラスター化インデックスよりもデータへのアクセスが高速になります。

局所性原理とディスクの事前読み取り

ストレージメディアの特性上、ディスクアクセス自体はメインメモリよりはるかに遅くなります。機械的な動作消費に加え、ディスクアクセス速度はメインメモリの数百分の一になることがよくあります。したがって、効率を向上させるには、ディスクI/Oを最小限に抑える必要があります。この目標を達成するために、ディスクは厳密にオンデマンドで読み取られるのではなく、毎回事前に読み取られることがよくあります。必要なのが 1 バイトだけの場合でも、ディスクはこの位置から開始し、一定の長さのデータを順番に逆方向にメモリに読み込みます。この理論的根拠は、コンピュータサイエンスにおける有名な局所性原理です。つまり、あるデータが使用されると、通常、近くのデータがすぐに使用されるということです。プログラム実行中に必要なデータは通常集中しています。
ディスクの順次読み取りは非常に効率的であるため (シーク時間は必要なく、回転時間もわずかしか必要ありません)、事前読み取りによって局所性を持つプログラムの I/O 効率を向上させることができます。
事前読み取りの長さは、通常、ページの整数倍になります。ページは、コンピュータ管理メモリの論理ブロックです。ハードウェアとオペレーティングシステムは、多くの場合、メインメモリとディスクストレージ領域を同じサイズの連続ブロックに分割します。各ストレージブロックはページと呼ばれます (多くのオペレーティングシステムでは、ページサイズは通常 4k です)。メインメモリとディスクは、ページ単位でデータを交換します。プログラムが読み取ろうとするデータがメインメモリにない場合、ページフォールト例外が発生します。このとき、システムはディスクに読み取り信号を送信します。ディスクはデータの開始位置を見つけ、1つまたは複数のページを連続的に読み取り、メモリにロードします。その後、例外が返され、プログラムは引き続き実行されます。

B-/+ツリーインデックスのパフォーマンス分析

これで、ようやく B-/+ ツリーインデックスのパフォーマンスを分析できるようになりました。
前述のように、インデックス構造の品質を評価するには、通常、ディスク I/O の数が使用されます。まず、B ツリー分析から始めましょう。B ツリーの定義によれば、検索には最大 h 個のノードにアクセスする必要があります。データベースシステムの設計者は、ディスクの事前読み取り原理を巧みに利用して、ノードのサイズをページと同じサイズに設定し、各ノードを 1 回の I/O だけで完全にロードできるようにしました。この目標を達成するには、B-Tree の実際の実装で次の技術が必要です。
新しいノードが作成されるたびに、ページのスペースが直接要求され、ノードが物理的にページに格納されることが保証されます。さらに、コンピューターのストレージ割り当てはページごとに調整されるため、ノードに必要な I/O は 1 つだけです。
B ツリーでの検索には最大で h-1 回の I/O が必要であり (ルートノードはメモリ内に存在します)、漸近的な複雑度は O(h)=O(logdN) です。一般的な実際のアプリケーションでは、出次数 d は非常に大きな数値であり、通常は 100 を超えるため、h は非常に小さくなります (通常は 3 以下)。
赤黒木構造の場合、h は明らかにはるかに深くなります。論理的に近いノード (親と子) は物理的に離れている場合があり、局所性を活用できないため、赤黒木の漸近的 I/O 複雑度も O(h) となり、B ツリーよりも効率が大幅に低下します。

要約すると、インデックス構造として B ツリーを使用することは非常に効率的です。

B ツリーと B+ ツリーのデータ構造について学ぶ時間を取る必要があります。

1) Bツリー

B ツリーの各ノードには、キー値と、キー値に対応するデータオブジェクトのアドレスへのポインターが含まれているため、オブジェクトの検索を正常に行うには、ツリーのリーフノードに到達する必要はありません。
成功した検索には、ノード内の検索とパスに沿った検索が含まれます。成功した検索に必要な時間は、キーが配置されているレベルとノード内のキーの数によって異なります。
B ツリーで特定のキーワードを検索する方法は、まずルートノードを取得し、ルートノードに含まれるキーワード K1、…、kj で特定のキーワードを検索します (シーケンシャル検索またはバイナリ検索を使用できます)。指定された値に等しいキーワードが見つかった場合、検索は成功です。そうでない場合、検索するキーワードは特定の Ki または Ki+1 の間にあると判断できるため、Pi が指す次のレベルのインデックスノードブロックを取得し、見つかるまで検索を続けます。ポインタ Pi が空の場合は検索が失敗します。

2) B+ツリー

B+ツリーの非リーフノードに格納されているキーコードは、データオブジェクトのアドレスポインタを示すものではありません。非リーフノードはインデックス部分のみです。すべてのリーフノードは同じレイヤーにあり、対応するデータオブジェクトのすべてのキーコードとストレージアドレスポインターが含まれており、リーフノードはキーコードに従って昇順にリンクされます。実際のデータオブジェクトがキー番号ではなく追加された順序で格納されている場合、リーフノードインデックスは密なインデックスである必要があります。実際のデータがキーの順序で格納されている場合、リーフノードインデックスは疎なインデックスです。
B+ ツリーには 2 つのヘッドポインターがあり、1 つはツリーのルートノードであり、もう 1 つは最小のキーコードを持つリーフノードです。
したがって、B+ ツリーには 2 つの検索方法があります。
1 つは、リーフノード自体によってプルアップされたリンクリストの順序で検索することです。
1 つはルートノードから検索を開始する方法で、これは B ツリーに似ています。ただし、非リーフノードのキーコードが指定された値と等しい場合、検索は停止せず、リーフノードのキーコードが見つかるまで右ポインタに沿って続行されます。したがって、検索が成功するかどうかに関係なく、ツリーのすべてのレベルが調べられます。
B+ ツリーでは、データオブジェクトはリーフノード上でのみ挿入および削除されます。
インデックスを処理するためのこれら 2 つのデータ構造の違いは次のとおりです。
a. 同じキー値は B ツリーに複数回出現することはなく、リーフノードまたは非リーフノードに出現する可能性があります。 B+ ツリーのキーはリーフノードに出現する必要があり、B+ ツリーのバランスを維持するために非リーフノードで繰り返される場合があります。
b. B ツリーキーの位置は固定されておらず、ツリー構造全体で 1 回しか表示されないため、ストレージスペースを節約できますが、挿入および削除操作の複雑さが大幅に増加します。 B+ ツリーの方がより良い妥協案です。
c. B ツリーのクエリ効率は、ツリー内のキーの位置に関係します。最大時間計算量は B+ ツリーと同じ (リーフノード) で、最小時間計算量は 1 (ルートノード) です。 B+ ツリーの場合、特定の構築されたツリーの複雑さは固定されます。 2の累乗をスキャンできます。

インデックス作成のコスト

ディスク容量使用中

DML（更新、削除、挿入）ステートメントの効率への影響

インデックスを再編成する必要があるため、追加、削除、および変更はインデックスに影響します。

ストレージエンジンで許可されるインデックスの種類
マイサム・ビーツリー
InnoDB Bツリー
メモリ/yeap ハッシュ、btree

インデックスを追加するのに適した列はどれですか?

① クエリ条件として使用されるクエリフィールドにはインデックスを作成する必要があります。 ② 一意性が低いフィールドは、頻繁に使用される場合でも、単独でインデックスを作成することには適していません。

性別が「男性」であるempから*を選択します

③フィールドを頻繁に更新し、インデックスを定義しない。
④ where文に表示されないフィールドにはインデックスを作成しない

概要: インデックスは、次の条件を満たすフィールドに対してのみ作成する必要があります。

① where条件で頻繁に使用されること ② フィールドの内容がユニークな値が少ないこと ③ フィールドの内容が頻繁に変更されないこと

インデックス作成に関する注意事項

テーブルを作成する

部門データを追加

PROCEDURE insert_dept(in start int(10),in max_num int(10)) を作成します。
始める
 i int DEFAULT 0 を宣言します。
 自動コミットを0に設定します。
 繰り返す
 i=i+1 と設定します。
 dept値に挿入します（（start+i）、rand_string（10）、rand_string（8））;
 i = max_numになるまで
 繰り返し終了;
 専念;
終わり
insert_dept(100,10); 呼び出しを実行します。

主キーインデックスを作成する

テーブル名を変更し、主キー (列名) を追加します。

共同インデックスを作成する

alter table dept add index my_ind (dname,loc); // dname は左側の列、loc は右側の列です

知らせ：

1. 作成された複数列インデックスの場合、最初の部分が使用されないと、インデックスは作成されません。
説明 select * from dept where loc='aaa'\G
インデックスは使用されません
2. 「like」の前にパーセント記号があると、あいまい検索は失敗します。
3. 条件に or がある場合、条件にインデックスがあっても使用されません。つまり、使用する必要があるすべてのフィールドにインデックスを付ける必要があります。またはキーワードの使用はできるだけ避けることをお勧めします。
4. 列の型が文字列の場合は、条件内のデータを必ず引用符で囲んでください。それ以外の場合、インデックスは使用されません。 (追加する場合、文字列は '' である必要があります)、つまり、列が文字列型の場合は '' で囲む必要があります。
5. MySQL がフルテーブルスキャンの方がインデックスよりも高速であると判断すると、インデックスは使用されません。

使用率を照会する

'handler_read%' のようなステータスを表示します。

誰もが注目できる点:

handler_read_key: 値が高いほど良いです。値が高いほど、インデックスがクエリに使用される回数が多くなります。
handler_read_rnd_next: この値が高いほど、クエリの効率は低くなります。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキルの概要」、および「MySQL データベースロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: