MySQLの通常インデックスとユニークインデックスの違いを詳しく解説

この記事では、MySQL の通常のインデックスと一意のインデックスの違いについて紹介します。非常に詳しく説明されているので、MySQL を学習している友人の参考になれば幸いです。

#MySQL における通常のインデックスと一意のインデックスの違いの詳細な説明

1. クエリとクエリの違いupdate

これら 2 種類のインデックスの間でクエリ機能に違いはありません。主に考慮すべき点は、更新パフォーマンスへの影響です。できるだけ通常のインデックスを選択することをお勧めします。

(無料の学習ビデオ チュートリアルの推奨: mysql ビデオ チュートリアル)

1.1 MySQL クエリ操作

■ 普通Index

条件を満たす最初のレコードを見つけたら、条件を満たさない最初のレコードが見つかるまで逆方向のトラバースを続けます。

#一意のインデックス

インデックスは一意性を定義するため、条件を満たす最初のレコードが見つかった後、検索は直接停止されます。

通常のインデックスはもう一度取得されますが、ほとんど影響はありません。 InnoDB データはデータ ページ単位で読み書きされるため、データを読み取る必要がある場合、レコードはディスクから直接読み取られるのではなく、まずデータ ページがメモリに読み込まれ、次にデータ ページから取得されます。

データ ページのデフォルトは 16 KB です。整数フィールドの場合、データ ページにはほぼ 1,000 個のキーを保持できます。読み取るデータがデータ ページの最後のレコードにない限り、別のデータ ページをこのような状況はまれであり、CPU の消費は基本的に無視できます。

したがって、データのクエリという点では、通常のインデックスと一意のインデックスに違いはありません。

1.2 MySQL 更新操作

更新操作はディスク内のデータを直接更新するのではなく、まずデータ ページをディスクからメモリに読み取ってから更新します。データのページ。

## 通常のインデックス

# データ ページをディスクからメモリに読み取り、データ ページを更新します。

## 一意のインデックス

# データ ページをディスクからメモリに読み取り、一意であるかどうかを判断して、データ ページを更新します。

MySQL には変更バッファ機構があるため、通常のインデックスと一意のインデックスの更新には一定の違いが生じます。

変更バッファの機能は、IO 操作を削減し、過度のシステム負荷を回避することです。変更バッファ内のデータ ページにデータを書き込むプロセスはマージと呼ばれます。

更新する必要のあるデータ ページがメモリ内にある場合は、データ ページが直接更新されますが、データがメモリ内にない場合は、更新操作が最初に変更バッファに記録されます。次回データ ページが読み取られると、データ ページにマージされます。 データ ページでは、変更バッファにも通常のマージ戦略があります。マージはデータベースの通常のシャットダウン中にもトリガーされます。

一意のインデックスの場合、更新する前にデータが一意であるかどうかを判断する必要があります (テーブル内のデータで同じことを繰り返すことはできません)。データ ページがメモリ内にある場合は、直接判断して更新できます。メモリにない場合は、ディスクに移動して読み取って、一意であるかどうかを確認し、一意である場合は更新します。変更バッファは使用されません。データ ページがメモリにない場合でも、データ ページを読み取る必要があります。

変更バッファはバッファ プール内のメモリを使用するため、無限に増やすことはできません。変更バッファのサイズは、パラメータ innodb_change_buffer_max_size を通じて動的に設定できます。このパラメータが 50 に設定されている場合、変更バッファのサイズはバッファ プールの最大 50% までしか占有することができないことを意味します。

結論: 変更バッファは一意のインデックスには使用できません。使用できるのは通常のインデックスのみです。

2. 変更バッファと REDO ログの違い

2.1 変更バッファの適用可能なシナリオ

変更バッファ 更新操作やキャッシュ更新操作の頻度を減らす機能です。これには、更新が適時に行われないという欠点があり、読み取り操作が頻繁に行われるテーブルの場合、変更バッファーの使用はお勧めできません。

更新操作が変更バッファに記録されたばかりであるため、テーブルが読み取られ、データ ページがメモリに読み込まれ、データはすぐにデータ ページにマージされました。これではパフォーマンスの消費が減らないだけでなく、変更バッファを維持するコストも増加します。

書き込みが多く読み取りが少ないテーブルに適しています。

2.2 変更バッファと REDO ログの違い

REDO ログと変更バッファを理解するために例を示します。次の SQL ステートメントを実行します。

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

(id1,k1) がデータ ページ ページ 1 にあり、(id2,k2) がデータ ページ ページ 2 にあるとします。そして、ページ 1 はメモリ内にありますが、ページ 2 はメモリ内にありません。

実行プロセスは次のとおりです:

ページ 1 に (id1,k1) を直接書き込みます;

変更内容に「ページ 2 に (id2) を書き込みます」と書き込みます。バッファ ,k2)"このメッセージ;

上記の 2 つのアクションを REDO ログに記録します。

上記を完了すると取引が完了します。この更新ステートメントの実行コストは非常に低く、2 つのメモリに書き込んでからディスクに書き込み (2 つの操作を合わせて 1 つのディスクに書き込みます)、それらは順次書き込まれます。

この更新ステートメントには、メモリー、REDO ログ (ib_log_fileX)、データ表スペース (t.ibd)、およびシステム表スペース (ibdata1) の 4 つの部分が含まれます。

如果要读数据的话，过程是怎样的？

mysql> select * from t where k in (k1, k2);

假设读操作在更新后不久，此时内存中还有 Page 1，没有 Page 2，那么读操作就和 redo log 以及 ibdata1 无关了。

从内存中获取到 Page 1 上的最新数据 (id1,k1)；

将数据页 Page 2 读入内存，执行merge 操作，此时内存中的 Page 2 也有最新数据(id2,k2)；

需要注意的是：

redo log中的数据，可能还没有 flush 到磁盘，磁盘中的 Page 1 和 Page 2 中并没有最新数据，但我们依然可以拿到最新数据（内存中的 Page 1 就是最新的，Page 2 虽然不是最新的，但是从磁盘读到内存中后，执行了merge操作，内存中的 Page 2 就是最新的了。）

如果此时 MySQL 异常宕机了，比如服务器异常掉电，change buffer 中的数据会不会丢？

change buffer 中的数据分为两部分，一部分是已经merge到ibdata1中的数据，这部分数据已经持久化，不会丢失。另一部分数据，还在 change buffer 中，没有merge 到ibdata1，分 3 种情况：

（1）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog中也没有fsync到磁盘，这部分数据会丢失；

（2）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog 已写入到磁盘，这部分不会多丢失，异常重启后会先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer；

（3）change buffer 写入数据到内存，redo log 和 binlog 都已经fsync，直接从redo log 恢复，不会丢失。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

更多MySQL相关教程，请关注PHP中文网！

以上がMySQLの通常インデックスとユニークインデックスの違いを詳しく解説の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。