Voyons pourquoi MySQL ne peut pas utiliser uuid comme clé primaire

Cet article vous apporte des connaissances pertinentes sur le fait que MySQL ne peut pas utiliser uuid comme clé primaire. MySQL recommande officiellement de ne pas utiliser d'identifiants de flocon de neige uuid ou discontinus et non répétitifs, mais recommande des identifiants de clé primaire à croissance automatique continue. pourquoi n'est-il pas recommandé d'utiliser uuid ? J'espère que cela sera utile à tout le monde.

Avant-propos

Lors de la conception de tables dans MySQL, MySQL recommande officiellement de ne pas utiliser d'identifiants de flocon de neige uuid ou discontinus et non répétitifs (incrément de forme longue et unique sur une seule machine), mais recommande une auto continue -increment La recommandation officielle pour l'identifiant de clé primaire est auto_increment, alors pourquoi n'est-il pas recommandé d'utiliser uuid ? Quels sont les inconvénients de l'utilisation de uuid ?

1. MySQL et exemples de programmes

1.1.1.1. Pour expliquer ce problème, nous créons d'abord trois tables

Ce sont user_auto_key, user_uuid, user_random_key, qui représentent la clé primaire à croissance automatique, et uuid est la clé primaire à croissance automatique. clé primaire. ,

La clé aléatoire est utilisée comme clé primaire, et nous gardons le reste complètement inchangé.

Selon la méthode des variables de contrôle, nous générons uniquement la clé primaire de chaque table en utilisant des stratégies différentes, tandis que les autres champs sont exactement la même chose, puis testez la vitesse d'insertion de la table et la vitesse de requête :

Remarque : la clé aléatoire ici fait en fait référence à l'identifiant non continu, non répétitif et irrégulier calculé par l'algorithme du flocon de neige : une chaîne de 18- valeurs un peu longues

1.2. La théorie seule ne suffit pas. Accédez simplement au programme et utilisez le jdbcTemplate de Spring pour implémenter des tests d'inspection supplémentaires :

Cadre technique : springboot+jdbcTemplate. +junit+hutool.Le principe du programme est de connecter votre propre base de données de test, puis d'utiliser la même base de données de test. Écrivez la même quantité de données dans l'environnement et analysez le temps d'insertion pour analyser de manière exhaustive son efficacité afin d'obtenir. l'effet le plus réaliste, toutes les données sont générées de manière aléatoire, telles que les noms, les adresses e-mail et les adresses.

package com.wyq.mysqldemo;
import cn.hutool.core.collection.CollectionUtil;
import com.wyq.mysqldemo.databaseobject.UserKeyAuto;
import com.wyq.mysqldemo.databaseobject.UserKeyRandom;
import com.wyq.mysqldemo.databaseobject.UserKeyUUID;
import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
import com.wyq.mysqldemo.util.JdbcTemplateService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StopWatch;
import java.util.List;
@SpringBootTest
class MysqlDemoApplicationTests {
    @Autowired
    private JdbcTemplateService jdbcTemplateService;
    @Autowired
    private AutoKeyTableService autoKeyTableService;
    @Autowired
    private UUIDKeyTableService uuidKeyTableService;
    @Autowired
    private RandomKeyTableService randomKeyTableService;
    @Test
    void testDBTime() {
        StopWatch stopwatch = new StopWatch("执行sql时间消耗");
        /**
         * auto_increment key任务
         */
        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
        List<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
        stopwatch.start("自动生成key表任务开始");
        long start1 = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false);
            System.out.println(insertResult);
        }
        long end1 = System.currentTimeMillis();
        System.out.println("auto key消耗的时间:" + (end1 - start1));
        stopwatch.stop();
        /**
         * uudID的key
         */
        final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
        stopwatch.start("UUID的key表任务开始");
        long begin = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);
            System.out.println(insertResult);
        }
        long over = System.currentTimeMillis();
        System.out.println("UUID key消耗的时间:" + (over - begin));
        stopwatch.stop();
        /**
         * 随机的long值key
         */
        final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
        stopwatch.start("随机的long值key表任务开始");
        Long start = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);
            System.out.println(insertResult);
        }
        Long end = System.currentTimeMillis();
        System.out.println("随机key任务消耗时间:" + (end - start));
        stopwatch.stop();
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }

1.3. Résultats de l'écriture du programme

On peut voir que lorsque la quantité de données est d'environ 100W, l'efficacité d'insertion de l'uuid est au bas et 130W de données sont ajoutées dans le post-commande, et le temps d'uudi est Il a encore chuté.

Le classement d'efficacité global basé sur l'utilisation du temps est : auto_key>random_key>uuid, uuid a l'efficacité la plus faible. Lorsque la quantité de données est importante, l'efficacité chute. Alors pourquoi cela arrive-t-il ? En cas de doute, discutons de ce problème :

2. Comparaison de la structure de l'index utilisant l'uuid et l'identifiant auto-croissant

2.1. Les valeurs de la clé primaire sont séquentielles, donc Innodb stocke chaque enregistrement après un enregistrement. Lorsque le facteur de remplissage maximum de la page est atteint (le facteur de remplissage maximum par défaut d'InnoDB est de 15/16 de la taille de la page, 1/16 de l'espace sera laissé pour les modifications futures) :

① L'enregistrement suivant sera écrit dans la nouvelle page, une fois les données chargées dans cet ordre, la page de clé primaire sera remplie d'enregistrements presque séquentiels, augmentant le taux de remplissage maximum de la page, et il n'y aura pas de gaspillage de page

② La ligne nouvellement insérée sera certainement dans l'original La ligne suivante de la plus grande ligne de données, le positionnement et l'adressage de MySQL sont très rapides, et aucune consommation supplémentaire n'est effectuée pour calculer la position de la nouvelle ligne

③Réduit la génération de fractionnements de page et de fragments

2.2. Utilisation de la structure d'index interne uuid

Parce que uuid est irrégulier par rapport à l'identifiant séquentiel à auto-incrémentation, la valeur de la nouvelle ligne n'est pas nécessairement supérieure à la valeur de la clé primaire précédente, donc innodb ne peut pas toujours ajouter le nouveau La ligne est insérée à la fin de l'index, mais il est nécessaire de trouver un nouvel emplacement approprié pour la nouvelle ligne afin d'allouer un nouvel espace.

Ce processus nécessite de nombreuses opérations supplémentaires. Les données désordonnées entraîneront une distribution dispersée des données, ce qui entraînera les problèmes suivants :

①La page cible écrite est susceptible d'avoir été vidée sur le disque et supprimée du cache. , ou n'a pas été chargé dans le cache, innodb doit trouver et lire la page cible du disque dans la mémoire avant de l'insérer, ce qui provoquera de nombreuses E/S aléatoires

② Parce que les écritures sont dans le désordre, innodb doit page fréquente Les opérations de fractionnement sont effectuées pour allouer de l'espace pour de nouvelles lignes. Le fractionnement de pages entraîne le déplacement d'une grande quantité de données. Au moins trois pages doivent être modifiées pour une insertion

.

③En raison des divisions de pages fréquentes, les pages deviendront clairsemées et remplies de manière irrégulière, ce qui finira par conduire à une fragmentation des données.

Chargez des valeurs aléatoires (uuid et identifiant de flocon de neige) dans l'index clusterisé (index par défaut innodb) À l'avenir. , vous devrez parfois faire une opération OPTIMEIZE TABLE pour reconstruire la table et optimiser le remplissage des pages, ce qui prendra un certain temps.

Conclusion : lorsque vous utilisez innodb, vous devez insérer autant que possible dans l'ordre d'augmentation automatique de la clé primaire et essayer d'utiliser des valeurs de clé de cluster croissantes de manière monotone pour insérer de nouvelles lignes

2.3 Inconvénients de l'utilisation de l'auto. -augmentation des identifiants

Alors, n'y a-t-il aucun mal à utiliser un identifiant auto-incrémenté ? Non, les identifiants auto-croissants auront également les problèmes suivants :

① Une fois que d'autres personnes auront exploré votre base de données, ils pourront obtenir des informations sur la croissance de votre entreprise en fonction des identifiants auto-croissants de la base de données, et il est facile d'analyser la situation de votre entreprise

② Pour les charges à haute concurrence, innodb provoquera un conflit de verrouillage évident lors de l'insertion par clé primaire. La limite supérieure de la clé primaire deviendra un point chaud pour les conflits, car toutes les insertions se produisent ici, et l'insertion simultanée provoquera des verrouillages d'espacement.

③ Le mécanisme de verrouillage Auto_Increment provoquera une concurrence pour les verrouillages à incrémentation automatique, entraînant certaines pertes de performances

Pièce jointe : Problème de compétition de verrouillage Auto_increment, si vous souhaitez l'améliorer, vous devez régler la configuration de innodb_autoinc_lock_mode

3 . Résumé

Ce blog commence par poser des questions au début, créer des tableaux et utiliser jdbcTemplate pour tester les performances de différentes stratégies de génération d'ID lors de l'insertion de grandes quantités de données. Il analyse ensuite la structure de l'index ainsi que les avantages et les inconvénients de celle-ci. différents mécanismes d'identification dans MySQL, avec une explication détaillée Pourquoi les ID uuid et aléatoires non répétitifs entraînent une perte de performances lors de l'insertion de données, ce problème est expliqué en détail.

Dans le développement réel, il est préférable d'utiliser un ID auto-croissant selon la recommandation officielle de MySQL. MySQL est large et profond, et il existe de nombreux points internes dignes d'optimisation que nous devons apprendre.

Apprentissage recommandé : Tutoriel vidéo mysql

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!