MySQL のインデックス作成

今日は MySql インデックスについて説明します。この記事では主に、InnoDB のインデックスのデータ構造、インデックスの仕組み、インデックスを有効に使用して効率を向上させる方法について説明します。

1. インデックスとは何ですか?

データベース インデックスは、データベース管理システム内のソートされたデータ構造で、データの迅速なクエリと更新を支援します。データベーステーブル。以前使用した新華辞典の目次と同じように、特定の単語をすばやく検索するのに役立ちます。

2. インデックスの分類

分類の角度	インデックス名
データ構造	B ツリー、ハッシュ インデックス、R ツリーなど
ストレージ レベル	クラスター化インデックス、非クラスター化インデックス
#論理レベル	主キーインデックス、通常インデックス、複合インデックス、一意インデックス、空間インデックスなど

# #

3. インデックス インスタンスの分析 (InnoDB を例にします)

3.1 InnoDB のインデックス構造

InnoDB では、テーブルは主キーの順序に従ってインデックスの形式で格納されます。このデータ格納方法はクラスター化インデックスとも呼ばれます。「クラスタリング」とは、データ行と隣接するキー値がコンパクトにまとめて格納されること、つまりデータ行が実際にはインデックスのリーフ ページに格納されることを意味します。 InnoDB の下にインデックス構造を実際に示すテーブルを作成します。テーブル作成ステートメントは次のとおりです:

create table person(id int primary key, 
age int not nullindex (age)engine=InnoDB;

次に、5 つのデータを挿入します: (1,15)、(2,17)、(6 ,20 )、(10,18)、(19,21) のインデックス ツリー構造は次のとおりです:

上の図は、コンテンツの 2 つの部分を示しています。最初の画像はクラスターです。クラスター インデックス (主キー インデックス) の内容から、データが ID のサイズに従って並べ替えられ、対応するインデックスにはインデックスのデータ行全体が含まれることがわかります。

2 番目の図は、非クラスター化インデックス (非主キー インデックス) である年齢をインデックスとして使用したインデックス構造図を示しています。インデックスが年齢によってソートされていることがわかりますが、主キー インデックスとは異なります。 key インデックス、age インデックスは Id に対応するため、非主キー インデックス レコードの内容が主キー インデックスの値であることがわかります。

学生の中にはここで質問があるかもしれませんが、テーブルを作成するときに主キーを指定しない場合、インデックス構造はどうなりますか?実際、InnoDB では、主キーが定義されていない場合、代わりに空でない一意のインデックスが選択されます。そのようなインデックスがない場合は、暗黙的に主キーがクラスター化インデックスとして定義されます。したがって、主キーを設定するかどうかに関係なく、InnoDB は上記の図の形式でデータのインデックスを作成するのに役立ちます。次に、インデックス クエリのプロセスを分析します。

3.2 インデックス クエリ分析

クエリ ステートメント select * from person where ID = 6 を実行するとします。主キー ID クエリが直接使用されるため、主キー インデックスが使用されます。主キー インデックス 行全体のすべてのデータは直接関連付けられているため、エンジンは結果をクエリするために 1 回実行するだけで済みます。

実行された SQL ステートメントが非主キー インデックスの場合

select * from person where age = 18

通常のインデックスから主キー インデックスを見つけてデータをクエリするプロセスは、テーブル リターンと呼ばれます。テーブルを返すにはさらに 1 つのクエリが必要になるため、主キー インデックスが通常のインデックスより高速であるため、できる限り主キー クエリを使用するようにしてください。 上記のステートメントでは、年齢の通常のインデックスが使用されます。インデックスは、最初に年齢に基づいて 18 に等しいインデックス レコードを検索し、ID=10 のレコードを見つけてから、主キー インデックスを 1 回検索してから、クエリする必要があるデータ。

3.3 カバーインデックス

通常、クエリの where 条件に基づいてインデックスを作成しますが、これは単なる通常の方法です。上記の分析に基づいて、次のことがわかります。高いクエリ効率を実現したい場合は、まず主キー インデックスを使用し、次にテーブルを返さないようにします。つまり、インデックス内の目的のデータをできるだけ取得できます。インデックスにクエリが必要なフィールドが含まれている場合、それを「カバーインデックス」と呼びます。

それでは、カバーインデックスを作成するにはどうすればよいでしょうか?答えは、結合インデックスを通じてこれを実現することであり、クエリ対象のフィールドは結合インデックスのフィールドでカバーされ、インデックス カバレッジの効果が得られます。

我们把上面的建表语句改造下，来分析下如何实现覆盖索引。

 CREATE TABLE `person` (  `id` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  `sex` varchar(1) DEFAULT NULL,

上面我创建了一个name和age的联合索引，索引结构图表示如下：

我们根据图可以知道，联合索引是和创建索引字段顺序有关的，上面这个例子就是先以name排序，然后name相同再以age为标准排序。那么我们建表后该如何达到覆盖索引的效果呢？相信有些同学已经知道了怎么写sql可以达到覆盖索引效果，sql如下：

select name,age from person where name = "Barry"

因为我们需要查询的字段name和age，都在索引中可以直接查询到了，所以不需要查找到主键ID，然后再回表了。

看到这里，肯定有同学会说，既然这样的话，我把所有需要查询的字段组合都建上联合索引不就行了吗？答案是：不行。因为索引也是需要消耗空间的，而且维护索引也是需要成本的，这一点我会在后面的优缺点中提到。那么有没有别的方式可以尽可能的实现不回表的效果呢？这里我们就要引入MySql的最左前缀原则了。

什么叫最左前缀原则呢？就是在索引的匹配中，可以以索引的最左N个字段,也可以是字符串索引的最左N个字符。比如在上图中，要查询以A开头的名字，查询语句就是

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select name from person where name like &#39;A%&#39;</span><br/>

这个时候就可以满足最左前缀规则来使用索引查询了，这里就会依赖索引查询到第一个首字母是A的名字，然后向后遍历，直到不满足条件为止。

那么最左N个字段是什么意思呢？意思就是索引（name,age),可以直接利用 name来当做单独索引使用，可以只使用联合索引的部分字段，但是必须是顺序一致，比如索引（a,b,c)，如果要想使用最左前缀规则，可以使用索引a,ab。

我们也可以利用该规则来少维护一个或多个索引，比如我们需要 a,ab,abc的查询，那就只需要(a,b,c)联合索引就满足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一个新特性，叫做“索引条件推送（index condition pushdown)”，这也称为索引下推。那么索引下推是这个什么东东呢？其实从“索引条件推送”这个名字就可以表明，这个特性是可以在索引中的字段进行条件判断，然后过滤不满足条件的记录，减少回表的次数。

比如以上图中的数据为准，sql如下：

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select * from person where name like &#39;A%&#39; and age =19;</span><br/>

那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。

在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。

3.5 索引的优点与缺点

说了这么多关于索引的内容，我们来谈谈索引的优缺点。

优点：

减少服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机IO变为顺序IO

缺点

索引会占用额外的存储空间索引的维护需要一定的成本，插入数据后需要保证原来的索引有序，所以也会影响一定的数据库性能。

五、总结

このブログ投稿では、主にインデックスの定義、インデックスの分類、さまざまな観点に応じた一般的なインデックスの種類について説明しました。次に、InnoDB でインデックス付けされたインデックスのデータ構造に注目しました。主キー インデックスと非主キー インデックスの違いは、主キー インデックスをクエリするとデータを直接返すことができることです。非主キー インデックスでは、最初に主キー ID をクエリしてから、データをクエリする必要があります。このプロセスはテーブル リターンと呼ばれます。 。インデックスをカバーすることでテーブルが返される数を減らすことができ、それによってパフォーマンスが向上します。 mysql5.6以降、InnoDBはインデックスプッシュダウンをサポートするようになり、ジョイントインデックスを使用する場合、インデックス内で条件が判定できれば、条件を満たさない行がインデックス内でフィルタリングされ、テーブルリターン数が削減されます。

6. 参考文献

「ハイパフォーマンスMySql」第3版

「MySql45講義」コラム

【おすすめコース: MySQL ビデオ チュートリアル ]

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