Explication détaillée de la différence entre les index ordinaires et les index uniques dans MySQL

Cet article présente la différence entre les index ordinaires et les index uniques dans MySQL. L'explication est très détaillée. J'espère qu'elle sera utile aux amis qui apprennent MySQL !

Explication détaillée de la différence entre l'index ordinaire et l'index unique dans MySQL

1 La différence entre la requête et update

Il n'y a aucune différence dans les capacités de requête entre ces deux types d'index. La principale considération est l'impact sur les performances de mise à jour. Il est recommandé de choisir autant que possible des index ordinaires.

(Tutoriel vidéo d'apprentissage gratuit recommandé : Tutoriel vidéo mysql)

1.1 Opération de requête MySQL

Après l'index ordinaire

trouve le premier enregistrement qui remplit la condition, il continue de parcourir en arrière jusqu'au premier enregistrement qui ne remplit pas la condition.

weight Index unique

Puisque l'index définit l'unicité, après avoir trouvé le premier enregistrement qui remplit les conditions, la recherche sera directement arrêtée.

Les index ordinaires seront récupérés une fois de plus, avec presque aucun impact. Étant donné que les données InnoDB sont lues et écrites en unités de pages de données, lorsque les données doivent être lues, les enregistrements ne sont pas lus directement à partir du disque. Au lieu de cela, les pages de données sont d'abord lues dans la mémoire, puis récupérées à partir des pages de données.

Une page de données est par défaut de 16 Ko. Pour les champs entiers, une page de données peut contenir près d'un millier de clés. À moins que les données à lire ne soient le dernier enregistrement de la page de données, vous devez lire une autre page de données. . Cette situation est rare et la consommation CPU est fondamentalement négligeable.

Par conséquent, en termes d'interrogation de données, il n'y a aucune différence entre les index ordinaires et les index uniques.

1.2 Opération de mise à jour MySQL

L'opération de mise à jour ne met pas directement à jour les données sur le disque. Elle lit d'abord la page de données du disque dans la mémoire, puis la met à jour. les données.

Index ordinaire

Lire la page de données du disque en mémoire et mettre à jour la page de données.

Index unique

Lisez la page de données du disque vers la mémoire, déterminez si elle est unique, puis mettez à jour la page de données.

Comme il existe un mécanisme de tampon de changement dans MySQL, il y aura une certaine différence dans la mise à jour des index ordinaires et des index uniques.

Le but du tampon de modification est de réduire les opérations d'E/S et d'éviter une charge excessive du système. Le processus d'écriture de données sur la page de données dans le tampon de modification est appelé fusion.

Si la page de données qui doit être mise à jour est dans la mémoire, la page de données sera mise à jour directement ; si les données ne sont pas dans la mémoire, l'opération de mise à jour sera d'abord enregistrée dans le tampon de modification, et lors de la prochaine lecture de la page de données, elle sera fusionnée dans la page de données, le tampon de modification a également une stratégie de fusion régulière. La fusion sera également déclenchée lors de l'arrêt normal de la base de données.

Pour un index unique, vous devez déterminer si les données sont uniques avant la mise à jour (elles ne peuvent pas dupliquer les données dans le tableau). Si la page de données est en mémoire, vous pouvez la déterminer et la mettre à jour directement. S'il n'est pas dans la mémoire, vous devez accéder au disque et vérifier s'il est unique. Si tel est le cas, mettez-le à jour. Le tampon de modification n'est pas utilisé. Même si la page de données n'est pas en mémoire, elle doit quand même être lue.

Le tampon de modification utilise la mémoire du pool de tampons, il ne peut donc pas être augmenté à l'infini. La taille du tampon de modification peut être définie dynamiquement via le paramètre innodb_change_buffer_max_size. Lorsque ce paramètre est défini sur 50, cela signifie que la taille du tampon de modification ne peut occuper que jusqu'à 50 % du pool de tampons.

Conclusion : l'index unique ne peut pas utiliser le tampon de modification, seul l'index ordinaire peut être utilisé.

2. La différence entre le tampon de changement et le journal redo

2.1 Scénarios applicables du tampon de changement

changement buffer La fonction est de réduire la fréquence des opérations de mise à jour et des opérations de mise à jour du cache. Cela présente un inconvénient, c'est-à-dire que les mises à jour ne sont pas opportunes. Pour les tables avec des opérations de lecture fréquentes, il n'est pas recommandé d'utiliser le tampon de modification.

Comme l'opération de mise à jour vient d'être enregistrée dans le tampon de modification, la table a été lue, la page de données a été lue dans la mémoire et les données ont été immédiatement fusionnées dans la page de données. Non seulement cela ne réduira pas la consommation de performances, mais cela augmentera le coût de maintenance du tampon de modification.

Convient aux tables avec plus d'écritures et moins de lectures.

2.2 La différence entre le tampon de changement et le tampon de changement

Donnons un exemple pour comprendre le tampon de changement et le tampon de changement. Nous exécutons l'instruction SQL suivante :

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

Supposons que (id1,k1) se trouve dans la page de données page 1 et (id2,k2) se trouve dans la page de données page 2. Et la page 1 est en mémoire, la page 2 ne l’est pas.

Le processus d'exécution est le suivant :

Écrivez (id1,k1) directement sur la page 1 ;

Écrivez "Écrivez (id2) sur la page 2" dans le changement. buffer ,k2)"Ce message ;

Enregistrez les deux actions ci-dessus dans le journal de rétablissement.

Après avoir terminé ce qui précède, la transaction sera complétée. Le coût d'exécution de cette instruction de mise à jour est très faible, c'est-à-dire l'écriture sur deux emplacements mémoire puis l'écriture sur un disque (les deux opérations combinées écrivent sur un disque), et elles sont écrites séquentiellement.

Cette instruction de mise à jour comprend quatre parties : la mémoire, le journal de rétablissement (ib_log_fileX), l'espace table de données (t.ibd) et l'espace table système (ibdata1).

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

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中文网！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!