Lassen Sie uns darüber sprechen, warum MySQL uuid nicht als Primärschlüssel verwenden kann

Dieser Artikel vermittelt Ihnen relevante Erkenntnisse darüber, dass MySQL keine UUID als Primärschlüssel verwenden kann. MySQL empfiehlt offiziell, keine UUID oder nicht kontinuierliche und sich nicht wiederholende Schneeflocken-IDs zu verwenden, empfiehlt jedoch kontinuierliche, sich selbst erhöhende Primärschlüssel-IDs Warum wird die Verwendung von UUID nicht empfohlen? Ich hoffe, dass es für alle hilfreich ist.

Vorwort

Beim Entwerfen von Tabellen in MySQL empfiehlt MySQL offiziell, keine UUID oder diskontinuierliche und sich nicht wiederholende Schneeflocken-IDs (lange Form und eindeutig, Einzelmaschineninkrement) zu verwenden, empfiehlt jedoch kontinuierliches Auto -inkrementieren Die offizielle Empfehlung für die Primärschlüssel-ID lautet auto_inkrementieren. Warum wird die Verwendung von uuid nicht empfohlen? Welche Nachteile hat die Verwendung von uuid?

1. MySQL- und Programmbeispiele

1.1. Um dieses Problem zu erklären, erstellen wir zunächst drei Tabellen

Sie sind user_auto_key, user_uuid, user_random_key, die den automatisch wachsenden Primärschlüssel darstellen, und uuid ist der Primärschlüssel. ,

Zufallsschlüssel wird als Primärschlüssel verwendet, und wir lassen den Rest völlig unverändert.

Gemäß der Kontrollvariablenmethode generieren wir nur den Primärschlüssel jeder Tabelle mit unterschiedlichen Strategien, während dies für die anderen Felder der Fall ist Genau das Gleiche, und testen Sie dann die Einfügegeschwindigkeit der Tabelle und die Abfragegeschwindigkeit:

Hinweis: Der Zufallsschlüssel bezieht sich hier tatsächlich auf die nicht kontinuierliche, sich nicht wiederholende und unregelmäßige ID, die mit dem Snowflake-Algorithmus berechnet wurde: eine Zeichenfolge von 18- etwas lange Werte

1.2. Gehen Sie einfach zum Programm und verwenden Sie Springs jdbcTemplate, um zusätzliche Inspektionstests zu implementieren:

Technisches Framework: springboot+jdbcTemplate +junit+hutool. Das Prinzip des Programms besteht darin, Ihre eigene Testdatenbank zu verbinden und dann dieselbe Datenmenge in die Umgebung zu schreiben und die Einfügezeit zu analysieren, um deren Effizienz zu erreichen Für den realistischsten Effekt werden alle Daten zufällig generiert, z. B. Namen, E-Mail-Adressen und Adressen.

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. Ergebnisse beim Programmschreiben

Es ist ersichtlich, dass bei einer Datenmenge von etwa 100 W die Einfügungseffizienz von uuid am unteren Ende liegt und 130 W an Daten hinzugefügt werden Die Nachbestellung und die Zeit von Uudi sind erneut eingebrochen.

Die Gesamteffizienzbewertung basierend auf der Zeitnutzung ist: auto_key>random_key>uuid, uuid hat die niedrigste Effizienz. Wenn die Datenmenge groß ist, sinkt die Effizienz. Warum passiert das? Lassen Sie uns mit Zweifeln dieses Problem besprechen:

2. Vergleich der Indexstruktur mit UUID und selbsterhöhender ID

2.1. Die interne Struktur mit selbsterhöhender ID

Automatisch erhöhend Die Werte des Primärschlüssels sind sequentiell, sodass Innodb jeden Datensatz nach dem anderen speichert. Wenn der maximale Füllfaktor der Seite erreicht ist (der standardmäßige maximale Füllfaktor von InnoDB beträgt 15/16 der Seitengröße, 1/16 des Platzes bleibt für zukünftige Änderungen übrig):

① Der nächste Datensatz wird geschrieben Auf der neuen Seite wird die Primärschlüsselseite nach dem Laden der Daten in dieser Reihenfolge mit nahezu sequentiellen Datensätzen gefüllt, wodurch die maximale Füllrate der Seite erhöht wird und keine Seitenverschwendung entsteht

② Die neu eingefügte Zeile wird definitiv im Original sein Die nächste Zeile der größten Datenzeile, MySQL-Positionierung und -Adressierung erfolgen sehr schnell und es wird kein zusätzlicher Verbrauch für die Berechnung der Position der neuen Zeile erhoben

③Reduziert die Erzeugung von Seitenaufteilungen und -fragmenten

2.2. Verwenden der internen Indexstruktur von uuid

Da die uuid im Verhältnis zur sequentiellen automatisch inkrementierenden ID unregelmäßig ist, ist der Wert der neuen Zeile nicht unbedingt größer als der Wert des vorherigen Primärschlüssels, sodass innodb nicht immer hinzufügen kann das neue Die Zeile wird am Ende des Index eingefügt, es ist jedoch erforderlich, einen neuen geeigneten Ort für die neue Zeile zu finden, um neuen Speicherplatz zuzuweisen.

Dieser Prozess erfordert viele zusätzliche Vorgänge. Die ungeordneten Daten führen zu einer verstreuten Datenverteilung, was zu den folgenden Problemen führt:

①Die geschriebene Zielseite wurde wahrscheinlich auf die Festplatte geleert und aus dem Cache entfernt. oder nicht in den Cache geladen wurde, muss innodb vor dem Einfügen die Zielseite finden und von der Festplatte in den Speicher lesen, was zu vielen zufälligen E/A-Vorgängen führt Aufteilungsvorgänge werden durchgeführt, um Platz für neue Zeilen zu reservieren. Die Aufteilung von Seiten führt zum Verschieben einer großen Datenmenge. Für eine Einfügung müssen mindestens drei Seiten geändert werden

③Aufgrund häufiger Seitenaufteilungen werden die Seiten spärlich und unregelmäßig gefüllt, was schließlich zu einer Datenfragmentierung führt.

Laden Sie in Zukunft zufällige Werte (UUID und Snowflake-ID) in den Clustered-Index (Innodb-Standardindex). , müssen Sie manchmal eine OPTIMEIZE TABLE ausführen, um die Tabelle neu aufzubauen und die Seitenfüllung zu optimieren, was eine gewisse Zeit in Anspruch nehmen wird.

Fazit: Wenn Sie innodb verwenden, sollten Sie so viel wie möglich in der automatisch ansteigenden Reihenfolge des Primärschlüssels einfügen und versuchen, monoton ansteigende Clusterschlüsselwerte zum Einfügen neuer Zeilen zu verwenden

2.3 Nachteile der Verwendung von Auto -Erhöhende IDs

Ist die Verwendung einer automatisch inkrementierten ID also nicht schädlich? Nein, bei sich selbst erhöhenden IDs treten auch die folgenden Probleme auf:

① Sobald andere Ihre Datenbank crawlen, können sie Ihre Geschäftswachstumsinformationen basierend auf den sich selbst erhöhenden IDs der Datenbank abrufen, und es ist einfach, Ihre Geschäftssituation zu analysieren

② Bei Lasten mit hoher Parallelität führt InnoDB beim Einfügen durch den Primärschlüssel zu einem offensichtlichen Sperrkonflikt

③ Der Auto_Increment-Sperrmechanismus führt zu einem Wettbewerb um Auto-Inkrement-Sperren, was zu bestimmten Leistungseinbußen führt

Anhang: Auto_increment-Sperren-Wettbewerbsproblem. Wenn Sie es verbessern möchten, müssen Sie die Konfiguration von innodb_autoinc_lock_mode optimieren

3 . Zusammenfassung

Dieser Blog beginnt zunächst damit, Fragen zu stellen, Tabellen zu erstellen und jdbcTemplate zu verwenden, um die Leistung verschiedener ID-Generierungsstrategien beim Einfügen großer Datenmengen zu testen. Anschließend werden die verschiedenen ID-Mechanismen in der Indexstruktur von MySQL analysiert Vor- und Nachteile, mit einer ausführlichen Erklärung, warum UUID und zufällige, sich nicht wiederholende IDs beim Einfügen von Daten zu Leistungseinbußen führen, wird dieses Problem ausführlich erläutert.

In der tatsächlichen Entwicklung ist es am besten, die automatische Inkrementierungs-ID gemäß der offiziellen Empfehlung von MySQL zu verwenden. Es gibt viele interne Punkte, die einer Optimierung wert sind und die wir lernen müssen.

Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonLassen Sie uns darüber sprechen, warum MySQL uuid nicht als Primärschlüssel verwenden kann. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!