MySQL のインデックスの深い理解 (使用、分類、照合方法)

この記事では、MySQL のインデックスについて深く理解し、インデックスの利点、用途、分類、専門用語、マッチング方法などを紹介します。

高度な開発では、多くの場合、複雑な SQL を作成する必要があるため、非効率な SQL を作成しないように、インデックス作成の基本的な知識を理解する必要があります。これらの基本的な知識を通じて、より効率的な SQL を作成できるようになります。 [関連する推奨事項: mysql ビデオ チュートリアル ]

01 インデックス作成の利点

• は次のとおりです。サーバーがスキャンする必要があるデータの量、つまり IO の量が大幅に削減されました。
• サーバーがソートや一時テーブルを回避できるようにします (ファイルのソートは避け、インデックス ソートを使用してください)
• ランダム IO をシーケンシャル IO に変える

02 インデックスの使用

##where 句に一致する行をすばやく検索する
• 可能であれば複数のインデックスの中から選択する場合、MySQL は通常、見つかった行が最も少ないインデックスを使用します。テーブルに複数列のインデックスがある場合、オプティマイザはインデックスの左端のプレフィックスを使用して検索できます。テーブル接続がある場合、他のテーブルから行データを取得します。
• 特定のインデックス列の最小値と最大値を検索します。
• ソートまたはグループ化を使用して実行できる場合は、インデックスの左端のプレフィックスを入力すると、テーブルが並べ替えられてグループ化されます。
• 場合によっては、データ行を見つけることなくデータ値を取得するようにクエリを最適化できます
• 03 インデックスの分類

デフォルトでデータベースによって作成されるインデックスは一意のキー用です 主キー インデックス (一意で空ではない)

一意のインデックス (空にすることができるのは 1 つだけです)

通常のインデックス (通常のフィールドのインデックス)
• フルテキスト インデックス (通常は varchar、char、text タイプで作成されます) 、めったに使用されません)
• 複合インデックス (複数の単語によって確立されたインデックス)
• 04 インデックスの専門用語

1. テーブルに戻る

#name フィールドは通常のインデックスです。name 列の B ツリーから主キーを見つけます。を実行し、主キーの B ツリーから最終データを見つけます。これがテーブルの戻り値です。 (主キー インデックスのリーフ ノードは列のすべてのデータを保存しますが、通常はすべてのリーフ ノードが対応する主キー ID を保存します)

select * from use where name='sun'

まず、sun に対応する主キー Id=2 が非主キー インデックス名を通じて検索され、次に行全体が検索されます。データは、id=2 を通じて主キー インデックスで見つかり、Return、これが返されるテーブルです。

2. カバーするインデックス

必要なものを非データベース上でクエリできます。主キー インデックス 再度クエリするためにテーブルに返す必要のないフィールドは、カバー インデックスと呼ばれます。

select id,name from user where name = "1"

です。id の値はすでに利用可能です。最初のステップでの非主キー インデックス ID に基づいて主キー インデックス内の行データをクエリする必要はありません。

3. 左端のマッチング

結合インデックスでは、最初に左側がマッチングされ、その後、後方マッチングが継続されます。 ; たとえば、user テーブルの名前と年齢で構成される結合インデックス、select * from user where name="Mr. Ji" and age = 18 は、左端の一致と一致し、インデックスとして使用できます。ただし、select * from user where age = 18 は満たされていないため、このインデックスは使用されません。

次の 2 つの SQL の場合のインデックスの作成方法

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

ログイン後にコピー

必要なのは 1 つだけです。結合インデックス年齢名を作成します。 Just

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

name age と age の方が優れています

注意!知らせ！知らせ！ :

多くの記事を読んでいると、左端の一致エラーの例をよく見かけます: インデックスが名前年齢の結合インデックスである場合、SQL は

これはインデックス化できないと思っている人が多いですが、実際には可能です。 mysql オプティマイザは調整シーケンスを最適化し、name="Mr. Ji"、年齢 = 18

##4 に調整します。インデックス プッシュダウン

#結合インデックスで可能な限りインデックス情報を使用して、テーブルの戻り数をできる限り削減します

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用，如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';