MySQL の単一列インデックスと結合インデックスの概要

この記事では、mysql に関する関連知識を提供します。主に、単一列インデックスと結合インデックスに関連する問題を紹介します。インデックスに追加の列を使用すると、検索範囲を狭めることができますが、インデックスの使用2 つの列を使用することは、2 つの別々のインデックスを使用することとは異なります。一緒に見てみましょう。皆様のお役に立てれば幸いです。

推奨される学習: mysql ビデオ チュートリアル

1. はじめに

インデックス内の追加の列を使用すると、検索を絞り込むことはできますが、2 つの列を持つインデックスを使用することは、2 つの別々のインデックスを使用することとは異なります。

ジョイント インデックスの構造は電話帳の構造と似ています。人の名前は姓と名で構成されます。電話帳は最初に姓で並べ替えられ、次に名で並べ替えられます。同じ姓の人たち。電話帳は、姓がわかっている場合には非常に便利で、姓と名の両方がわかっている場合はさらに役立ちますが、姓のみで姓がわからない場合は役に立ちません。

したがって、結合インデックスを作成するときは、列の順序を慎重に考慮する必要があります。ユニオン インデックスは、インデックス内のすべての列を検索する場合、または最初の数列のみを検索する場合に役立ちますが、後続の列を検索する場合には役に立ちません。

2. 単一列インデックス

複数条件クエリに複数の単一列インデックスが使用される場合、オプティマイザは最適なインデックス戦略を優先します。使用できるインデックスは 1 つだけです。または、複数のインデックスをすべて使用することもできます。ただし、複数の単一列インデックスを使用すると、下部に複数の B インデックス ツリーが作成され、領域が占有され、検索効率がある程度無駄になるため、複数条件の結合クエリのみがある場合は、結合インデックスを構築することをお勧めします。

3. 左端のプレフィックスの原則

名前が示すとおり、左端の優先順位です。左端から始まる任意の連続インデックスが一致します。最初のフィールドが範囲クエリの場合は、次のことが必要です。インデックスの場合、結合インデックスを作成する場合、ビジネス ニーズに応じて、where 句で最も頻繁に使用される列を一番左に配置する必要があります。この場合、スケーラビリティが向上します。たとえば、ユーザー名はクエリ条件としてよく使用されますが、年齢はあまり使用されないため、ユーザー名をジョイント インデックスの最初の位置、つまり左端に配置する必要があります。 。

1. 複合インデックスを作成します

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2. 複合インデックスの左端の特性を満たします (たとえ一部であっても、複合インデックスは有効になります)

SELECT * FROM employee WHERE NAME='哪吒编程'

3. 左のフィールドが表示されません。すると、左端の特性が満たされず、インデックスが無効になります。

SELECT * FROM employee WHERE salary=5000

4. すべての複合インデックスが使用され、名前、給与が左の順に表示され、インデックスが有効になります

SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000

5. 左端の特性に違反していますが、MySQL は SQL 実行時に最適化を実行し、最下層は逆最適化を実行します

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'

6. 理由

複合インデックスは結合インデックスとも呼ばれます。(k1, k2,k3) などの結合インデックスを作成すると、(k1)、(k1,k2)、(k1,k2) の 3 つのインデックスを作成するのと同じになります。 ,k3).これは一番左のマッチング原則です。

結合インデックスは左端の原則を満たしていないため、通常、インデックスは失敗します。

4. ジョイント インデックスと単一列インデックスが同時に存在します (フィールドが繰り返されます)。この時点で、インデックスは MySQL へのクエリにどのように使用されますか?

これには MySQL 自体のクエリ オプティマイザー戦略が関係します。テーブルに複数のインデックスがある場合、MySQL はクエリ ステートメントのコストに基づいてどのインデックスを使用するかを選択します。

一部の人々はクエリがどこにあるかを言う順序は左から右なので、遮蔽力が最も強い条件が最初に配置されます。 Baidu Online にはこのステートメントがありますが、私は個人的にテストしました。MySQL 実行オプティマイザーがそれを最適化します。インデックスが考慮されていない場合、where 条件の順序は効率に影響しません。実際に影響があるのは、インデックスが使用済み！

5. ジョイント インデックスの本質

**(a, b, c) ジョイント インデックスを作成する場合、(a) 単一列インデックス、(a, b) を作成するのと同じです。ジョイント インデックスと (a、b、c) ジョイント インデックス、インデックスを効果的にしたい場合、使用できる組み合わせは 3 つだけです。もちろん、a と c の組み合わせも使用できることを上記でテストしましたが、実際には、a のインデックスのみが使用され、c は使用されません。

6. インデックスの失敗

1. サブクエリと同様に、先頭に % を付けます;

2. 非 null 判定は null ではありません; 前後にインデックスは使用されていませんまたは同時にステートメント。 or の左側と右側のクエリ フィールドのいずれか 1 つだけがインデックスである場合、インデックスは無効になります。これは、or の左側と右側のクエリ フィールドの両方がインデックスである場合にのみ有効になります。

3. or は、or の左側と右側のクエリ フィールドの両方がインデックスである場合にのみ有効になります。ステートメント (前後にインデックスがある場合のみ、SQL の最適化が必要です。ステートメントの記述は避けてください);

4. データ型の暗黙的な変換があります。 varchar が一重引用符で囲まれていない場合、自動的に int 型に変換され、インデックスが無効になり、テーブル全体のスキャンが発生する可能性があります。

7. その他の知識ポイント

1. インデックスを作成する必要があるフィールドは、where 条件に含める必要があります

2. データ量が少ないフィールドは、インデックスを作成する必要はありませんインデックス作成には一定のオーバーヘッドがあり、データ量が少ない場合はインデックスを作成する必要がなく、速度範囲も遅くなります。

3. 結合インデックスは、作成するインデックスの数が増えるほど占有されるディスク領域が増え、データの更新速度が遅くなるため、各列にインデックスを構築するよりも多くの利点があります。複数列インデックスの作成、順序 スクリーニングをより強力かつ効率的に行うために、厳密なインデックス作成を最初に配置する必要があることに注意することも重要です。

八、MySQL存储引擎简介

1、InnoDB

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交和回滚。

2、MyISAM

插入速度快，空间和内存使用比较低。如果表主要是用于插入新纪录和读取记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发要求比较低，也可以使用。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

九、索引结构（方法、算法）

在mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。

1、Hash

Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。

Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据，不像BTree索引需 要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是对于范围查询的话，就需要进行全表扫描了。

但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：

• Hash索引仅仅能满足“=”,“IN”,“”查询，不能使用范围查询。

• 联合索引中，Hash索引不能利用部分索引键查询。 对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。

• Hash索引无法避免数据的排序操作 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

• Hash索引任何时候都不能避免表扫描 Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。

• Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高 对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

2、B+ Tree

B+Tree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,

例如：

select * from user where name like 'jack%'; select * from user where name like 'jac%k%';

如果一通配符开头，或者没有使用常量，则不会使用索引，

例如：

select * from user where name like '%jack'; select * from user where name like simply_name;

3、 B+/-Tree原理

在数据库中，数据量相对较大，多路查找树显然更加适合数据库的应用场景，接下来我们就介绍这两类多路查找树，毕竟作为程序员，心里没点B树怎么能行呢？

B树：B树就是B-树，他有着如下的特性：

• B树不同于二叉树，他们的一个节点可以存储多个关键字和多个子树指针，这就是B+树的特点；

• 一个m阶的B树要求除了根节点以外，所有的非叶子子节点必须要有[m/2,m]个子树；

• 根节点必须只能有两个子树，当然，如果只有根节点一个节点的情况存在；

• B树是一个查找二叉树，这点和二叉查找树很像，他都是越靠前的子树越小，并且，同一个节点内，关键字按照大小排序；

• B树的一个节点要求子树的个数等于关键字的个数+1；

B+树就是B树的plus版

• B+树将所有的查找结果放在叶子节点中，这也就意味着查找B+树，就必须到叶子节点才能返回结果；

• B ツリーの各ノード内のキーワードの数は、サブツリー ポインターの数と同じです。

• B 非ノード内のキーワードの数は、サブツリー ポインターの数と同じです。ツリーの -leaf ノードは A ポインターに対応し、キーはサブツリーの最大値または最小値です。

前のセクションで B ツリーを最適化します。 B ツリーのリーフ ノードはキー値情報のみを格納します。各ディスク ブロックが 4 つのキー値とポインタ情報を格納できると仮定すると、B ツリーとなり、その構造は次のようになります。

# 通常、2 つのポインターがあり、1 つはルート ノードを指し、もう 1 つは最小のキーを持つリーフ ノードを指し、すべてのリーフ ノード (つまり、データ ノード) の間にはチェーン リング構造があります。したがって、B Tree では、主キーの範囲検索とページング検索、およびルート ノードから開始するランダム検索の 2 つの検索操作を実行できます。

おそらく、上記の例ではデータ レコードが 22 しかなく、B Tree の利点がわかりません。計算は次のとおりです:

InnoDB ストレージ エンジンのページ サイズは 16KB、一般テーブルの主キーのタイプは INT (4 バイトを占有) または BIGINT (8 バイトを占有) で、ポインタのタイプは通常 4 または 8 バイトです。つまり、ページ (B ツリー内のノード) はおそらく

16KB/(8B 8B)=1K

のキー値を格納します(推定なので計算の都合上、ここでのKの値は〖10〗^3とします)。

つまり、深さ 3 の B ツリー インデックスは、10^3 * 10^3 * 10^3 = 10 億 レコードを維持できます。
実際の状況では、各ノードが完全に埋まっていない可能性があるため、データベースでは、B ツリーの高さは通常 2 ～ 4 層になります。 MySQL の InnoDB ストレージ エンジンは、ルート ノードがメモリ内に常駐するように設計されています。つまり、特定のキー値の行レコードを見つけるのに必要なディスク I/O 操作は 1 ～ 3 回だけです。

データベースの B ツリー インデックスは、クラスター化インデックス (クラスター化インデックス) と補助インデックス (セカンダリ インデックス) に分類できます。上記の B ツリーの例の図は、クラスター化インデックスとしてデータベースに実装されており、クラスター化インデックスの B ツリー内のリーフ ノードには、テーブル全体の行レコード データが格納されます。補助インデックスとクラスター化インデックスの違いは、補助インデックスのリーフ ノードには行レコードのすべてのデータが含まれるのではなく、対応する行データを格納するクラスター化インデックス キー、つまり主キーが含まれることです。セカンダリ インデックスを通じてデータをクエリする場合、InnoDB ストレージ エンジンはセカンダリ インデックスを走査して主キーを見つけ、その後主キーを通じてクラスタ化インデックス内の完全な行レコード データを見つけます。

推奨学習:

mysql ビデオ チュートリアル

以上がMySQL の単一列インデックスと結合インデックスの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。