MySQLインデックスの構文は何ですか

インデックス定義

インデックスは、MySQL がデータを効率的に取得するのに役立つ順序付けされたデータ構造であり、MySQL 公式のインデックスの定義です。クエリの効率を向上させるために、インデックスはデータベース テーブルのフィールドに追加されるメカニズムです。データベース システムは、データに加えて、特定の検索アルゴリズムを満たすデータ構造も維持します。これらのデータ構造は、何らかの方法でデータを参照 (ポイント) するため、これらのデータ構造に高度な検索アルゴリズムを実装できます。このデータ構造は、索引。 。以下の図に示すように:

実際、簡単に言うと、インデックスはソートされたデータ構造です。

左側データ テーブルには合計 2 列と 7 つのレコードがあり、一番左はデータ レコードの物理アドレスです (論理的に隣接するレコードがディスク上で物理的に隣接しているとは限らないことに注意してください)。 Col2 の検索を高速化するために、右に示すようにバイナリ検索ツリーを維持できます。各ノードには、インデックス キー値と、対応するデータ レコードの物理アドレスへのポインタが含まれています。 なので、二分検索を使用して、対応するデータをすばやく取得できます。

インデックスの利点

• 検索とソートの速度を高速化し、データベースのIOコストを削減します。 CPU 消費量

• 一意のインデックスを作成すると、データベース テーブル内のデータの各行の一意性を確保できます。

インデックスの欠点

1. インデックスは実際には

   テーブルであり、主キー、インデックスのフィールド、ポイントが保存されます。エンティティへのクラス レコード自体がスペースを占有する必要があります

2. クエリの効率は向上しますが、追加、削除、変更のために、テーブルが変更されるたびにインデックスを更新する必要があります。追加: 当然、インデックス ツリーに存在する必要があります。 新しいノードの削除: インデックス ツリーでポイントされているレコードが無効になる可能性があります。これは、このインデックス ツリー内の多くのノードが無効であることを意味します。 変更:

   を指す インデックス ツリーのノードを変更する必要がある場合があります

しかし実際には、MySQL に保存するために

バイナリ検索ツリー を使用しません。なぜでしょうか?

二分探索ツリーでは、ここのノードは 1 つのデータのみを保存でき、ノードは MySQL のディスク ブロックに対応するため、毎回 1 つのディスク ブロックを読み取ることを知っておく必要があります。データは 1 つしか取得できず、効率が非常に悪いので、

B-tree 構造を使用して格納することを考えます。

インデックス構造

インデックスは、サーバー層ではなく、MySQL のストレージ エンジン層に実装されます。したがって、インデックスはストレージ エンジン間で異なる場合があり、すべてのエンジンがすべての種類のインデックスをサポートしているわけではありません。

• BTREE インデックス: 最も一般的なインデックス タイプで、ほとんどのインデックスが B ツリー インデックスをサポートします。

• HASH Index: メモリ エンジンによってのみサポートされており、使用シナリオは簡単です。

• R ツリー インデックス (空間インデックス) : 空間インデックスは MyISAM エンジンの特殊なインデックス タイプで、主に地理空間データ タイプに使用されますが、通常はあまり使用されません。特別な紹介は行いません。

• フルテキスト (フルテキスト インデックス) : フルテキスト インデックスも MyISAM の特殊なインデックス タイプで、主にフルテキスト インデックスに使用されます。 Mysql5.6 バージョンから全文インデックスをサポートします。

#MyISAM、InnoDB、および Memory ストレージ エンジンはさまざまなインデックス タイプをサポートします

##INDEXサポートサポートサポート済み##サポートされていません

#INNODB エンジン

#MYISAM エンジン

##メモリ エンジン

##BTREE インデックス

HASH インデックス

## サポートされていません

サポートされている

R ツリー インデックス

サポートされていません

サポート

サポート対象外

全文

バージョン 5.6 以降でサポートされます

サポートされます

サポートされません

明示的に指定しない限り、通常参照するインデックスは、B ツリー (多方向検索ツリー、必ずしもバイナリである必要はない) 構造を使用して編成されています。クラスター化インデックス、複合インデックス、プレフィックス インデックス、およびインデックスと呼ばれる一意のインデックスはすべて、デフォルトで B ツリー インデックスを使用します。

BTREE

マルチパスバランス検索ツリー、m 次 (m フォーク) BTREE は次の条件を満たします:

• 各ノードは最大でもm 人の子供数: ceil(m/2) から m キーワードの数: ceil(m/2)-1 から m-1

ceil は切り上げ、ceil を意味します。 (2.3)=3

#キーワード case の挿入

#m 次 B ツリーのプロパティを破壊しないことを保証

Due to 3 順序には最大 2 つのノードしか含めることができないため、最初は 26 と 30 が一緒になっていますが、その後 85 が分割され始めます。30 は上部中央の位置になり、26 は残り、85 は次の位置に移動します右

つまり:
中央の位置 上の位置は で、左側は古いノードに留まり、右側は新しいノード

になります。 70 を再度図に挿入すると、たまたま 70 が中央の上の位置になり、その後 62 が維持され、再び 85 になります 新しいノードを分割します

上のレベルの後、再び分割する必要があります

同じ理由で上方向に分割し続けます

比較利点

二分探索ツリーと比較して、高さ/深さが低く、自然なクエリ効率が高くなります。

B TREE

• B ツリーには 2 種類のノードがあります: 内部ノード (

  インデックス ノード とも呼ばれます) と 葉ノード## ＃。内部ノードは非リーフ ノードであり、内部ノードにはデータは格納されずインデックスのみが格納され、データはリーフ ノードに格納されます。

内部ノードのキーは、
• 小さいものから大きいものまで

  の順序で配置されています。内部ノードのキーの場合、左側のツリーのすべてのキーが小さくなります。それよりも、右側のサブツリーのキーはそれ以上です。リーフ ノード内のレコードもキー サイズに従って配置されます。

各リーフノードには隣接するリーフノードへのポインタが格納されており、リーフノード自体はキーワードのサイズに応じて小さいものから大きいものへと順番に接続されています。

• 親ノードには、

  right 子の最初の要素のインデックス
が格納されます。

比較利点

B Tree のクエリ効率は

より安定しています

。 B ツリーのリーフ ノードのみがキー情報を保存するため、キーをクエリするにはルートからリーフに移動する必要があるため、より安定しています。

• リーフ ノードをトラバースするだけで、ツリー全体をトラバースできます。

• MySQL の B ツリー

MySql インデックス データ構造は、従来の B ツリーを最適化します。元の B ツリーに基づいて、隣接するリーフ ノードを指す

リンク リスト ポインタ (全体の構造は二重リンク リストに似ています)

が形成され、シーケンシャル ポインタを備えた B ツリーが形成され、改善されます。インターバルアクセスのパフォーマンス。

注意深い生徒なら、この図と二分探索ツリー図の最大の違いは何であるかがわかるでしょうか?

二分探索ツリーから B ツリー

への移行における重要な変更点は、1 つのノードに複数のデータを保存できることです。これは 1 つのディスク ブロックに相当します。データにアクセスできるため、IO 時間が大幅に短縮されます。 ！

• B MySQL のツリー インデックス構造図:

##二分探索ツリー図:

インデックスの原理

BTree インデックス:

初期化の概要

水色のブロックはディスク ブロックと呼ばれ、各ディスクが表示されます。ブロックには複数のデータが含まれています項目 (濃い青で表示) とポインター (黄色で表示)

たとえば、ディスク ブロック 1 には、ポインター P1、P2、および P3 を含むデータ項目 17 と 35 が含まれています。

P1 は、17 ブロック未満のディスクを表します。 、P2 は 17 ～ 35 のディスク ブロックを表し、P3 は 35 より大きいディスク ブロックを表します。

実際のデータはリーフ ノードに存在します

つまり、3、5、9、10、13、15、28、29、36、60、75 、79、90、99。 `

• 非リーフ ノードには実際のデータは保存されません。17 や 35 などの 検索方向をガイドするデータ項目

  のみが実際にはデータ内に存在しません。テーブル。 `

検索プロセス

データ項目 29 を検索する場合、最初にディスク ブロック 1 がディスクからメモリにロードされ、この時点で IO が発生します。メモリ内で二分探索を使用して、29 が 17 ～ 35 の間にあることを確認し、ディスク ブロック 1 の P2 ポインタをロックします。メモリ時間は (ディスクの IO と比較して) 非常に短いため、無視できます。ディスクを使用します。ディスク ブロック 1 からディスク ブロック 3 の P2 ポインタのアドレスがディスクからメモリにロードされます。2 番目の IO が発生します。29 は 26 と 30 の間にあります。ディスク ブロック 3 の P2 ポインタはロックされています。ディスク ブロック 8 がロードされます。ポインタを介してメモリにアクセス 3 回目の IO が発生 同時にメモリが通過 二分検索が 29 に達してクエリが終了し、合計 3 回の IO が発生します。

実際の状況では、3 層の B ツリーは数百万のデータを表すことができます。数百万のデータ検索に 3 回の IO だけが必要な場合、パフォーマンスは大幅に向上します。インデックスがない場合、各データは項目ごとに IO が発生すると、合計で数百万回の IO が必要となり、明らかにコストが非常に高くなります。

インデックスの分類

インデックス構成テーブルとは、主キー順にインデックスとして格納されるテーブルで、InnoDB エンジンに適した方式です。 InnoDB は B ツリー インデックス モデルを使用するため、データは B ツリーに保存されます。

各インデックスは InnoDB の B ツリーに対応します。
主キー列を ID とするテーブルがあり、テーブル内にフィールド k があり、k にインデックスがあるとします。
このテーブルのテーブル作成文は次のとおりです:

mysql> create table T( 
  id int primary key, 
  k int not null,  
  name varchar(16), 
  index (k))engine=InnoDB; 
复制代码

テーブル内のR1~R5の(ID,k)値は(100,1)、(200,2)、 (300,3)、(500,5)、および (600,6)、2 つのツリーの図の例は次のとおりです。図からはわかりにくいですが、リーフ ノードの内容に応じて、インデックス タイプが主キー インデックスと非主キー インデックスに分けられます。

主キー インデックス

データ テーブルの主キー列は主キー インデックスを使用し、デフォルトで作成されます。これが、インデックス付けを学ぶ前に、先生がよく私たちにこう言ったのです。主キーに基づいたクエリです。高速になります。主キー自体にインデックスが付けられていることがわかります。

主キー インデックス

のリーフ ノードには、

データ行全体

が格納されます。 InnoDB では、主キー インデックスは

クラスター化インデックス
(クラスター化インデックス) とも呼ばれます。 補助インデックス補助インデックス のリーフ ノードの内容は、

主キー

の値です。 InnoDB では、補助インデックスは

Secondary Index

(セカンダリ インデックス) とも呼ばれます。 以下に示すように:
主キー インデックスには

データ行全体が格納されます

• auxiliary インデックスはそれ自体を保存するだけで、id 主キーはテーブル クエリに使用されます。

• 上記のインデックス構造に従って、次のことを説明します。質問:
主キー インデックスと補助インデックスに基づくクエリの違いは何ですか?

ステートメントが select * from T where ID=500 (主キー クエリ メソッド) の場合、ID の B ツリーを検索するだけで済みます。

ステートメントが select * from T where k=5 (通常のインデックス クエリ方法) の場合、最初に
• k インデックス ツリー

  を検索する必要があります。 ID 値 500 を取得し、

  次に ID インデックス ツリー
を 1 回検索します。このプロセスは、
• テーブルに戻る

  と呼ばれます。 言い換えると、補助インデックスに基づくクエリは、もう 1 つのインデックス ツリーをスキャンする必要があります。したがって、アプリケーションでは主キー クエリを使用するようにしてください。

  クエリしたいデータがたまたまインデックス ツリーに存在しない限り、現時点ではそれを
カバー インデックス

と呼びます。つまり、インデックス列には、クエリするすべてのデータが含まれています。問われる。

同時に、セカンダリ インデックスは次のタイプに分類されます (簡単に読み飛ばしてください。詳細については後ほど説明します):

Unique Key (Unique Key)
: 一意のインデックスも制約です。

一意のインデックスの属性列には重複データを含めることはできませんが、データは NULL にすることができます。テーブルでは複数の一意のインデックスを作成できます。

ほとんどの場合、一意のインデックスを確立する目的は、クエリの効率性ではなく、属性列内のデータの一意性のためです。

• 通常のインデックス (インデックス):

  通常のインデックスの唯一の機能は、データを迅速にクエリすることです。テーブルでは複数の通常のインデックスを作成できます。データが重複していて NULL であることを許可します。

• プレフィックス インデックス (Prefix): プレフィックス インデックスは文字列型データにのみ適用されます。プレフィックスインデックスはテキストの最初の数文字にインデックスを作成し、最初の数文字だけを取得するため、通常のインデックスに比べて作成されるデータが小さくなります。

• フル テキスト インデックス (フル テキスト): フル テキスト インデックスは、主に大きなテキスト データ内のキーワード情報を取得するために使用されます。検索エンジン、テクノロジー。 Mysql5.6 より前は、MYISAM エンジンのみがフルテキスト インデックス作成をサポートしていましたが、5.6 以降では、InnoDB もフルテキスト インデックス作成をサポートしました

拡張機能 -- インデックス プッシュダウン

いわゆるプッシュダウンは、その名前が示すように、実際には テーブルの戻り操作を延期します。MySQL はそれを許可しません。とてももったいないので簡単にテーブルに戻ります。それはどういう意味ですか？次の例を考えてみましょう。

複合インデックス (名前、ステータス、アドレス) を確立しました。これも、次の図のように、このフィールドに従って保存されます。

複合インデックス ツリー (インデックス列とアドレスのみを保存します)主キーはテーブルを返すために使用されます)

#Xiaomi 2112

我们执行这样一条语句：

SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ;
复制代码

1. 首先我们在复合索引树上，找到了第一个以小米开头的name -- 小米1

2. 此时我们不着急回表(回到主键索引树搜索的过程，我们称为回表)，而是先在复合索引树判断status是否=1，此时status=0，我们直接就不回表了，直接继续找下一个以小米开头的name

1. 找到第二个-- 小米2，判断status=1，则根据id=2去主键索引树上找，得到所有的数据

这种先在自身索引树上判断是否满足其他的where条件，不满足则直接pass掉，不进行回表的操作，就叫做索引下推。

最左前缀原则

所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1. 按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效

   

2. 出现跳跃的情况

• 直接第一层name都不走，当然都失效

  

• 走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有name成功）

  

• 同时，这个顺序并不是由我们where中的排列顺序决定，比如： where name='小米科技' and status='1' and address='北京市' where status='1' and name='小米科技' and address='北京市'

这两个尽管where中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的

其实是因为我们MySQL有一个Optimizer（查询优化器），查询优化器会将SQL进行优化，选择最优的查询计划来执行。

• 关于这个查询优化器，后续文章我们也会谈谈MySQL的逻辑架构与存储引擎

索引设计原则

针对表

1. 查询频次高，且数据量多的表

针对字段

1. 最好从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

其他原则

1. 最好用唯一索引，区分度越高，使用索引的效率越高

2. 不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

比如：

我们创建了三个单列索引，name，status，address

当我们where中根据status和address两个字段来查询时，数据库只会选择最优的一个索引，不会所有单列索引都使用。

最优的索引：具体是指所查询表中，辨识度最高(所占比例最少)的索引列，比如此处address中有一个辨识度很高的 '西安市'数据；

1. 使用短索引,索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

2. 利用最左前缀，比如有N个字段，我们不一定需要创建N个索引，可以用复合索引

也就是说，我们尽量创建复合索引，而不是单列索引

创建复合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;
复制代码

举个栗子

假设我们有这么一个表，id为主键，没有创建索引：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB
复制代码

如果要在此处建立复合索引，我们要遵循什么原则呢？

通过调整顺序，可以少维护一个索引

• 比如我们的业务需求里边，有如下两种查询方式： 根据name查询 根据name和age查询

如果我们建立索引（age,name），由于最左前缀原则，我们这个索引能实现的是根据age，根据age和name查询，并不能单纯根据name查询（因为跳跃了），为了实现我们的需求，我们还得再建立一个name索引；

而如果我们通过调整顺序，改成（name，age），就能实现我们的需求了，无需再维护一个name索引，这就是通过调整顺序，可以少维护一个索引。

考虑空间->短索引

• 比如我们的业务需求里边，有以下两种查询方式： 根据name查询 根据age查询 根据name和age查询

我们有两种方案：

1. 建立联合索引(name,age)，建立单列索引：age索引。

2. 建立联合索引(age,name)，建立单列索引：name索引。

这两种方案都能实现我们的需求，这个时候我们就要考虑空间了，name字段是比age字段大的，显然方案1所耗费的空间是更小的，所以我们更倾向于方案1。

何时建立索引

1. where中的查询字段

2. 查询中与其他表关联的字段，比如外键

3. 排序的字段

4. 统计或分组的字段

何时达咩索引

1. 表中数据量很少

2. 经常改动的表

3. 频繁更新的字段

4. 数据重复且分布均匀的表字段（比如包含了很多重复数据，那此时多叉树的二分查找，其实用处不大，可以理解为O(logn)退化了）

索引相关语法

创建索引

默认会为主键创建索引--primary

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
复制代码

查找索引

结尾加上\G，可以变成竖屏显示

select index from tbl_name\G;
复制代码

删除索引

drop INDEX index_name on tbl_name ;
复制代码

变更索引

1). alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL	
	
2). alter  table  tb_name  add  unique index_name(column_list);
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	该语句指定了索引为FULLTEXT， 用于全文索引
复制代码

查看索引使用情况

show status like 'Handler_read%';	 -- 查看当前会话索引使用情况

show global status like 'Handler_read%';	-- 查看全局索引使用情况
复制代码

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

name	##status	アドレス	id(主キー)
Xiaomi 1	0	1	1

以上がMySQLインデックスの構文は何ですかの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。