高パフォーマンスMySQL - 高パフォーマンスインデックス作成の詳細説明（画像とテキスト）

この記事は、インデックスの作成についてです:
(1) インデックスの種類
(2) インデックスのメリット
(3) インデックスを最適化するための戦略

インデックス作成のマインドマップは次のとおりです:

インデックスは、レコードを迅速に検索するためにストレージ エンジンによって使用されるデータ構造です。インデックスは、クエリのパフォーマンスを最適化するための最も効果的な手段です。インデックスを使用すると、クエリのパフォーマンスを簡単に数桁向上させることができます。インデックス付け 通常、特定の列にインデックスを追加します。

ストレージ エンジンは、まずインデックス内の対応する値を検索し、次に、一致するインデックス レコードの ROWID に基づいて対応するデータ行を検索します。たとえば、次のクエリ ステートメントを実行します。

SELECT first_name from actor where actor_id=5;

actor_id 列にインデックスがある場合、MySQL はそのインデックスを使用して、actor_id 5 に対応する行を検索します。つまり、MySQL は最初に、インデックスを取得し、この値のデータ行を含むすべての項目を返します。

インデックスに 1 つ以上の列の値を含めることができます。インデックスに複数の列が含まれる場合、MySQL はインデックスの左端のプレフィックス列のみを効率的に使用できるため、列の順序も非常に重要になります。 2 つの列を含むインデックスを作成することと、1 つの列を含む 2 つのインデックスを作成することには大きな違いがあります。

1. インデックス データ構造の種類:

最も一般的なインデックスは、B ツリー インデックスとハッシュ インデックスです。

(1) B-Tree ツリーインデックス

一般に、インデックスとは、B-Tree データ構造を使用してデータを格納する B-Tree インデックスを指します。実際、これは B+Tree に基づいて実装されています。各リーフ ノードには次のリーフ ノードへのポインタが含まれています。

B-Tree は、たとえば、name 属性 の場合、すべての値が a-z の順序で格納されることを意味します。 B ツリー インデックスを使用すると、ストレージ エンジンは必要なデータを取得するためにテーブル全体のスキャンを実行する必要がなくなり、代わりにインデックスのルート ノードから検索し、最終的な結果として、対応する値が見つかるか、または見つかります。レコードは存在しません。これにより、データへのより高速なアクセスが可能になります。

B-Tree はインデックス列を順番に整理して格納するため、範囲データの検索に非常に適しています。 (たとえば、I-k で始まる名前の検索は非常に効率的です)

B ツリー インデックスに適したクエリ タイプ
(1) 完全値一致: インデックス内のすべての列と一致します。

(2) 左端のプレフィックスと一致する: 複数の列を含むインデックスの場合、インデックスの最初の列のみが使用されます。

(3) 列プレフィックスの一致: 特定の列の値の先頭と一致します。 (たとえば、名前フィールドを照合する場合、J で始まる名前のみが照合されます。) ここでは、インデックスの最初の列のみが使用されます。

(4) 範囲値の一致: フィールドが特定の範囲内にあるレコードと一致します。ここではインデックスの最初の列のみが使用されます。

(5) 特定の列に完全に一致し、別の列に範囲が一致する: たとえば、インデックスに複数のフィールドが含まれる場合、最初の列は完全に一致し、2 番目の列の範囲は一致します。

(6) インデックスのみにアクセスするクエリ: レコード内の残りのフィールドのデータ行にはアクセスせずに、インデックス行にアクセスします。

上記の範囲マッチングは主に、インデックスがインデックス列を順番に格納するためであり、これが範囲マッチングの高効率につながります。

B ツリー インデックスにもいくつかの制限があります:
(1) インデックスは左端の列からのみ検索できます

(2) クエリ内の特定の列に範囲検索がある場合、右側のすべての列インデックスの最適化は使用できません。

上記 2 つの制限を見ると、インデックスに複数の列が含まれる場合、インデックス列の順序が非常に重要であることが理解できるはずです。

(2) ハッシュ ハッシュ インデックス

ハッシュ インデックスはハッシュ テーブルに基づいて実装されており、インデックスのすべての列と完全に一致するクエリのみが有効です。データの各行に対して、データ ストレージ エンジンはすべてのインデックス列のハッシュ コードを計算します。ハッシュ コードはより小さい値であり、キー値が異なる行に対して計算されるハッシュ コードも異なります。

1) ハッシュインデックスはハッシュ値と行ポインターのみを保存し、特定のフィールド値を保存しないため、行を読み取るプロセスが必要です。

2) ハッシュインデックスはインデックス値の順に格納されないため、ソートには使用できません。

3) ハッシュ インデックスは等価比較クエリのみをサポートし、範囲比較クエリをサポートしません。これはハッシュ テーブルの特性に関連しています。

4) ハッシュ インデックスにはハッシュ競合の問題があります。ハッシュ競合データの場合、リンクされたリスト内のすべての行ポインターをトラバースする必要があります。

上記の制限があるため、ハッシュ インデックスは特定の場合にのみ適していますが、ハッシュ インデックスに適したものになると、パフォーマンスが特に高くなります。

ハッシュインデックスを使用する場合、通常は次のようにクエリ条件のハッシュの前に値を追加する必要があります:

mysql>select * from words where crc=crc32(‘gnu’) and word=’gnu’;

这里crc字段就是word字段哈希之后的值，因为hash之后可能存在冲突，带上原本的值做上二次比较，就可以精确定位。

2.索引的优点：

索引可以让服务器快速定位到表的指定位置。但是这不是唯一的作用，比如：
（1）对于B-Tree索引，由于B-Tree是按照顺序存储数据的，所以用来做order by 操作或则是 group by操作的效率很高。

（2）因为索引中存储了实际的列值，所以某些查询只需要索引就可以完成全部查询。

总结来说就是3点：
（1）索引大大减少服务器需要扫描的数据量；

（2）索引可以帮助服务器避免排序和临时表；

（3）索引可以将随机IO变为排序IO。

3.高性能的索引策略

先概括一下索引的策略：
 1）单列索引
 2）多列索引
 3）前缀索引
 4）聚簇索引
 5）覆盖索引

单列索引

所谓单列索引是指：使用数据表字段中的某一列作为索引。但是索引列不能是表达式的一部分，也不能是函数的参数。

比如对于下面的一个例子：

select actor_id from actor where actor_id+1=5;

对于这样的一个SQL，where语句后面 是一个表达式，其实很明显是actor_id=4的条件，但是MySQL却无法解析，索引无法正却使用索引。

还有一种是函数参数：也是无法正常的使用索引的

select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

多列索引以及选择合适的索引顺序

注意这里要区分：为每个列创建独立的索引和为多个列创建一个索引的区别。

比如下面这种情况：

CREATE TABLE t{
c1 int,
c2 int,
c3 int,key(c1),key(c2),key(c3)
}

这一种就是为表中的3个列都创建了索引。

但是多个列创建索引就是：创建了一个索引，包含customer_id,和staff_id

alter table payment add KEY(customer_id, staff_id);

上面这个索引其实是包含了两个索引，一个是customer_id这个索引，还有一个是(customer_id，staff_id)。注意staff_id并不能作为单独的索引使用。

---

对于多列索引，最重要的就是怎么选择索引列的顺序，其实这一点与实际的查询需求有关。主要是为了满足排序和分组。

先从数据结构层次来分析，我们知道索引是以B-Tree的形式存储的，在一个多列索引列中，索引的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以对于一个多列索引，如果以第二列或则第三列直接作为索引，基本是没有用到索引。由于索引的有序性很好的满足了order by、group by和distinct等子句的查询需求。

从上面的分析我们就能认识到多列索引中列的顺序是多么重要。关于多列索引中有一点经验法则：
（1）在不需要考虑排序和分组时，通常情况下将选择性最高的列放在索引最前列。(这时候索引只需要优化where查询条件，能够很快过滤出需要的行)

索引列的选择性定义：不重复的索引值和数据表的记录总数的比值。索引的选择性越高也就是查询效率越高。比如对于人员信息表，phone这一字段的选择性是很高的，几乎为1，但是对于sex性别这一字段的选择性是非常低的，因为只有两个选择男或则是女，几乎为0。

（2）实际情况下也与数据的分布有很大关系。
以下面的查询为例：

SELECT * FROM item WHERE staff_id=2 AND customer_id=584;

这时候应该创建（staff_id, customer_id）的索引还是应该创建（customer_id，staff_id）的索引呢？这时候就应该确认一下那个字段的选择性更高，先查询一下staff_id和customer_id的总数，哪个小就将哪个放在前面。

前缀索引

前缀索引：有时候需要索引的列可能会很长，这时候会导致索引大而且很慢，我们可以只索引列开始的部分（也就是只索引某一列的前面几个字符），这样可以大大节省索引空间也能加快索引的速度，但是也会降低索引的选择性（也就是索引查出来的结果会变多）。

使用的技巧在于：选择足够长的前缀保证较高的选择性，同时又不能太长，避免占用太多的存储空间。

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

クラスター化インデックスは、別個のインデックス タイプではなく、データの保存方法です。ここでは主に、クラスター化インデックスを説明する例として InnoDB を使用します。

InnoDB のクラスター化インデックスは、実際には、B ツリー インデックスとデータ行を同じ構造に保存します。テーブルにクラスター化インデックスがある場合、そのデータ行は実際にはインデックスのリーフ ページに格納されます。クラスタリングの意味は、実際には、隣接する B ツリー内のデータ行とキー値がコンパクトにまとめて格納されることです。データ行は 1 つの場所にのみ保存できるため、クラスタ化インデックスは 1 つだけ存在できます。

以下は、説明するための図の例です。インデックス列は整数値であり、リーフ ページには行のすべてのデータが含まれますが、ノード ページにはインデックス列 (下図の整数値) のみが含まれます。

MySQL の現在のバージョンでは、InnoDB のクラスター化インデックスは、データをクラスター化するための主キーの使用のみをサポートします。主キーが定義されていない場合、InnoDB は代わりに空でない一意のインデックスを選択します。

クラスター化データのメリット:
(1) 関連するデータをまとめて保存できる。たとえば、電子メール アドレスをクエリする場合、ユーザー ID が主キーとして使用され、データはユーザー ID によってクラスター化されます。この方法では、ユーザーのすべての電子メールを、データベースから少数のデータ ページを読み取るだけで取得できます。ディスク。

(2) データアクセスが高速になります。クラスター化インデックスはインデックスとデータを B ツリーに保存するため、通常、クラスター化インデックスからのデータの取得は、同じインデックスを検索するよりも高速です。 (もちろん、検索列がインデックス列である場合もあります)

(3) カバリングインデックススキャンを使用したクエリは、ページノードの主キーを直接使用できます。

上記の利点により、テーブルのクエリや設計時のパフォーマンスが大幅に向上しますが、いくつかの欠点もあります:
(1) クラスター化されたデータにより、IO 集約型アプリケーションのパフォーマンスが大幅に向上しますが、すべてのデータはメモリに配置され、アクセスできません。順序は重要ではないため、クラスター化インデックスの利点は失われます。

(2) 挿入速度は挿入順序に大きく依存します。

(3) クラスター化インデックス列の更新は非常にコストがかかり、InnoDB 内の更新された各行を新しい場所に移動する必要があります。

カバーインデックス

インデックスにクエリが必要なすべてのフィールドの値が含まれている (またはカバーされている) 場合、それをカバーインデックスと呼びます。

インデックスの場合、カバリング インデックスは非常に便利なツールです。インデックスをスキャンするだけで、テーブルにクエリを戻す必要がなく、インデックスのリーフ ノード内のすべてのデータを取得できるため、パフォーマンスが大幅に向上します。多くの利点もあります:

(1) インデックスのエントリは、通常、データ行のサイズよりもはるかに小さくなります。インデックスを読み取るだけでよい場合、MySQL はデータ アクセスの量を大幅に削減します。これは、データベースにとって非常に重要です。キャッシュの負荷。

(2) インデックスは列値の順序で格納されるため、IO 集中型の範囲検索は、ディスクからデータの各行をランダムに読み取るよりもはるかに少ない IO を必要とします

並べ替えにはインデックス スキャンを使用します

MySQL には 2 つの機能があります。順序付けされた結果を生成する方法:
(1) ソート操作による順序付け

(2) インデックス順にスキャン
説明されている型の値がインデックスの場合、MySQL がインデックス スキャンを使用してソートを実行することを意味します。

インデックス スキャン自体は非常に高速です。ただし、インデックス列がすべてのクエリ フィールドをカバーできない場合は、インデックス レコードをスキャンするたびに、そのレコードに戻る必要があります。テーブルを作成してクエリを実行すると、そのパフォーマンスは直接のシーケンシャルフルテーブルスキャンほど良くありません。

可能な限り、並べ替えと検索の両方を満たすように同じインデックスを設計してください。

4. インデックスのデメリット

(1) 挿入、更新、削除の操作では、インデックスを同期的に更新する必要があるため、速度が遅くなります。

(2) インデックスは多くのストレージを占有します。

以上が高パフォーマンスMySQL - 高パフォーマンスインデックス作成の詳細説明（画像とテキスト）の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。