MySQL の高パフォーマンスのインデックスを作成する方法

1 インデックスの基本

1.1 インデックス関数

MySQL では、データを検索するときに、まずインデックス内の対応する値を見つけます。次に、一致するインデックス レコードに従って、対応するデータ行を検索します。次のクエリ ステートメントを実行する場合:

SELECT	* FROM  USER  WHERE uid = 5;

uid に構築されたインデックスがある場合、MySQL は最初にそのインデックスを使用して行を検索します。 uid 5 の場合、つまり、MySQL は最初にインデックスの値で検索し、次にその値を含むすべての行を返します。

1.2 MySQL インデックスで一般的に使用されるデータ構造

MySQL インデックスは、サーバーではなくストレージ エンジン レベルで実装されます。したがって、統一されたインデックス標準は存在せず、ストレージ エンジンが異なればインデックスの動作も異なります。

1.2.1 B ツリー

ほとんどの MySQL エンジンは、この種のインデックス B ツリーをサポートしています。複数のストレージ エンジンが同じタイプのインデックスをサポートしている場合でも、基礎となる実装は異なる場合があります。たとえば、InnoDB は B Tree を使用します。

ストレージ エンジンは、さまざまな方法で B ツリーを実装し、さまざまなパフォーマンスと利点を備えています。たとえば、MyISAM はプレフィックス圧縮テクノロジを使用してインデックスを小さくしますが、InnoDB は元のデータ形式に従ってデータを保存します。MyISAM インデックスはデータの物理的な場所によってインデックス付けされた行を参照しますが、InnoDB はインデックス付けされた行をデータの物理的な場所に従って適用します。成分。

すべての B ツリー値は順番に格納され、各リーフ ページからルートまでの距離は同じです。次の図は、InnoDB インデックスの仕組みを大まかに示していますが、MyISAM で使用される構造は異なります。ただし、基本的な実装は似ています。

図の説明例:

各ノードは 1 つのディスク ブロックを占有し、1 つのノードには 2 つの昇順ソート キーと 3 つのポインティング サブツリーがあります。ルート ノード。子ノードが配置されているディスク ブロックのアドレスが保存されます。 ２つのキーワードで分割された３つの範囲フィールドは、３つのポインタが指すサブツリーのデータの範囲フィールドに対応する。ルートノードを例にとると、キーワードは 16 と 34、P1 ポインタが指すサブツリーのデータ範囲は 16 未満、P2 ポインタが指すサブツリーのデータ範囲は 16 ～ 34、データはP3 ポインタが指すサブツリーの範囲が 34 を超えています。キーワード検索プロセス:

• ルート ノードに基づいてディスク ブロック 1 を検索し、メモリに読み込みます。 [ディスク I/O 操作 1 回目]

• 比較キーワード 28 区間 (16,34) で、ディスク ブロック 1 のポインタ P2 を見つけます。

• P2 ポインタに基づいてディスク ブロック 3 を見つけ、それをメモリに読み取ります。 [ディスク I/O 操作 2 回目]

• 比較キーワード 28 区間 (25,31) で、ディスク ブロック 3 のポインタ P2 を見つけます。

• P2 ポインタに基づいてディスク ブロック 8 を見つけ、それをメモリに読み取ります。 [ディスク I/O 操作 3 回目]

• ディスク ブロック 8 のキーワード リストにキーワード 28 が見つかりました。

#欠点:

• 各ノードにはキーがあり、データも含まれており、各ページには記憶域があります。データが比較的大きい場合、各ノードに格納されるキーの数は少なくなり、格納されるデータの量が多い場合、深さは大きくなり、増加します。クエリ中のディスク IO 回数は、クエリのパフォーマンスに影響します。

• 1.2.2 B ツリー インデックス
  B ツリーは、B ツリーのバリアントです。 B ツリーとの違い: B ツリーはリーフ ノードにデータのみを格納し、非リーフ ノードはキー値とポインタのみを格納します。

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

B* ツリーは B 番号と似ていますが、違いは、B* 番号も葉以外のノード間にチェーン リング構造があることです。

1.2.3 ハッシュ インデックス

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

MySQL では、デフォルトのインデックス タイプであるメモリのみがハッシュ インデックスとして使用され、メモリは B ツリー インデックスもサポートします。同時に、メモリ エンジンは非一意のハッシュ インデックスをサポートしており、複数の列のハッシュ値が同じ場合、インデックスはリンク リスト内の同じハッシュ エントリに複数のポインタを格納します。ハッシュマップに似ています。

利点

:インデックス自体は対応するハッシュ値を格納するだけでよいため、インデックスの構造は非常にコンパクトであり、ハッシュ検索を非常に高速化します。

欠点:

ハッシュストレージを使用する場合、すべてのデータファイルをメモリに追加する必要があるため、より多くのメモリスペースを消費します;

• ハッシュ インデックス データは順序どおりに格納されないため、並べ替えには使用できません;

• すべてのクエリが同等のクエリである場合、ハッシュ化は確かに非常に高速ですが、企業環境や実際の作業環境では、同等のクエリよりも多くのデータが範囲内で検索されるため、ハッシュ化はあまり適していません。

• ハッシュの競合が多い場合、インデックスの保守作業のコストも非常に高くなりますが、これは後の段階で赤黒ツリーを追加することで解決されるハッシュの競合の問題でもあります。 HashMap の;

2 高パフォーマンスのインデックス戦略

2.1 クラスター化インデックスと非クラスター化インデックス

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

は別個のインデックス タイプではなく、データ ストレージ方式であり、InnoDB ストレージ エンジンでは、クラスター化インデックスは実際にはキー値とデータ行を同じ構造に格納します。テーブルにクラスター化インデックスがある場合、そのデータ行は実際にはインデックスのリーフ ページに格納されます。データ行を異なる場所に同時に格納できないため、テーブル内に存在できるクラスター化インデックスは 1 つだけです (インデックス カバレッジにより、複数のクラスター化インデックスの状況をシミュレートできます)。

クラスター化インデックスの利点:

関連データをまとめて保存できる; インデックスとデータが同じツリーに保存されるため、データ アクセスが高速になる; を使用したクエリカバー インデックス スキャンでは、ページ ノードの主キー値を直接使用できます。

欠点:

クラスタ化されたデータにより、IO 集約型アプリケーションのパフォーマンスが最大化されます。データがすべてメモリ内にある場合、クラスター化インデックスには利点がありません。挿入速度は挿入順序に大きく依存し、主キーの順序で挿入するのが最も速い方法です。更新された各行は強制的に移動されるため、クラスター化インデックス列の更新は非常にコストがかかります。新しい場所、クラスター化インデックスに基づくテーブルは、新しい行が挿入されるとき、または主キーが更新されて行を移動する必要があるときにページ分割が発生する可能性があります。クラスター化インデックスにより、テーブル全体のスキャンが遅くなる可能性があります (特に行の比較がスパース)。ページ分割によりデータ ストレージが不連続な場合;

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

データ ファイルとインデックス ファイルは別々に保存されます

2.2 プレフィックス インデックス

場合によっては非常に長い文字列のインデックスを作成する必要があるため、インデックスが大きくなり遅くなります。通常、文字列の一部を列の先頭​​に使用すると、インデックスのスペースが大幅に節約され、インデックスの効率が向上しますが、インデックスの選択性が低下します。インデックス選択性とは、データ テーブル レコードの総数に対する一意のインデックス値 (カーディナリティとも呼ばれます) の比率を指し、範囲は 1/#T から 1 です。インデックスの選択性が高いほど、MySQL は検索時により多くの行を除外できるため、クエリ効率も高くなります。

一般に、特定の列プレフィックスの選択性はクエリのパフォーマンスを満たすのに十分な高さがありますが、BLOB、TEXT、および VARCHAR 型の列については、MySQL ではインデックス作成が許可されていないため、プレフィックス インデックスを使用する必要があります。カラムの全長 この方法のコツは、高い選択性を確保するのに十分な長さのプレフィックスを選択することですが、長すぎないことです。

例

テーブル構造とデータは、MySQL 公式 Web サイトまたは GitHub からダウンロードします。

city テーブルの列

##country_id国 IDlast_update:作成時間または最終更新時間

--计算完整列的选择性
select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了。由此最佳创建前缀索引长度为7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表时设置的主键索引，如果没有设置MySQL自动将第一个非空唯一值作为索引，如果还是没有InnoDB会创建一个隐藏的row-id作为索引（oracle数据库row-id显式展示，可以用于分页）；普通索引就是给普通列创建的索引。普通列索引在叶子节点中存储的并不是整行数据而是主键，当按普通索引查找时会先在B+树中查找该列的主键，然后根据主键所在的B+树中查找改行数据，这就是回表。

2.4 覆盖索引

覆盖索引在InnoDB中特别有用。MySQL中可以使用索引直接获取列的数据，如果索引的叶子节点中已经包含要查询的数据，那么就没必要再回表查询了，如果一个索引包含（覆盖）所有需要查询的字段的值，那么该索引就是覆盖索引。简单的说：不回表直接通过一次索引查找到列的数据就叫覆盖索引。

表信息

CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

举例

--将uid设置成主键索引后通过下面的SQL查询 在explain的Extra列可以看到“Using index”
explain select uid from t_user where uid = 1;

覆盖索引在组合索引中用的比较多，举例

explain select age,uname from t_user where age = 10 ;

当不建立组合索引时，会进行回表查询

设置组合索引后再次查询

create index index_user on t_user(age,uname);

2.5 索引匹配方式

2.5.1 最左匹配

在使用组合索引中，比如设置(age,name)为组合索引，单独使用组合索引中最左列是可以匹配索引的，如果不使用最左列则不走索引。例如下面SQL

--走索引
explain select * from t_user where age=10 and uname='zhang';

下面的SQL不走索引

explain select * from t_user where  uname='zhang';

2.5.2 匹配列前缀

可以匹配某一列的值的开头部分，比如like 'abc%'。

2.5.3 匹配范围值

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

explain select * from t_user where age>18;

2.5.4 精确匹配某一列并范围匹配另外一列

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

explain select * from t_user where age=18 and uname like 'zhang%';

2.5.5 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

explain select age,uname,update_time from t_user 
            where age=18 and uname= 'zhang' and update_time='123';

3 索引优化最佳实践

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。

--推荐
select uid,age,uname from t_user where uid=1;

--不推荐
select uid,age,uname from t_user where uid+9=10;

2. 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

3. 使用前缀索引参考2.2 前缀索引
4. 使用索引扫描排序mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。
mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。
举例表结构及数据MySQL官网或GItHub下载。

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental表在rental_date,inventory_id,customer_id上有rental_date的索引。使用rental_date索引为下面的查询做排序

--该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀
explain select rental_id,staff_id from rental 
where rental_date='2005-05-25' order by inventory_id desc

--下面的查询不会利用索引
explain select rental_id,staff_id from rental 
where rental_date>'2005-05-25' order by rental_date,inventory_id

5. union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范围列可以用到索引范围条件是：<、<=、>、>=、between。范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

7. 更新十分频繁，数据区分度不高的字段上不宜建立索引

• 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能；

• 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算；

8. 创建索引的列，不允许为null，可能会得到不符合预期的结果

9.当需要进行表连接的时候，最好不要超过三张表，如果需要join的字段，数据类型必须一致

10. 能使用limit的时候尽量使用limit

11. 单表索引建议控制在5个以内

12. 单索引字段数不允许超过5个（组合索引）

13. 创建索引的时候应该避免以下错误概念

• 索引越多越好

• 过早优化，在不了解系统的情况下进行优化

4 索引监控

show status like 'Handler_read%';

フィールド名	意味
city_id	都市主キー ID
city	都市名

参数	说明
Handler_read_first	读取索引第一个条目的次数
Handler_read_key	通过index获取数据的次数
Handler_read_last	读取索引最后一个条目的次数
Handler_read_next	通过索引读取下一条数据的次数
Handler_read_prev	通过索引读取上一条数据的次数
Handler_read_rnd	从固定位置读取数据的次数
Handler_read_rnd_next	从数据节点读取下一条数据的次数

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