Detaillierte Analyse, warum MySQL die Verwendung von UUID oder Snowflake-ID als Primärschlüssel nicht empfiehlt

Vorwort: Beim Entwerfen einer Tabelle in MySQL empfiehlt MySQL offiziell, keine UUID oder nicht kontinuierliche und nicht wiederkehrende Snowflake-IDs (lang und eindeutig) zu verwenden, sondern kontinuierliche, selbstinkrementierende Primärschlüssel-IDs. Die offizielle Empfehlung lautet auto_increment. Warum wird UUID also nicht empfohlen? Was sind die Nachteile der Verwendung von UUID? In diesem Blog analysieren wir dieses Problem und untersuchen die internen Gründe.

1: MySQL und Programmbeispiele

1.1: Um dieses Problem zu veranschaulichen, erstellen wir zunächst drei Tabellen, nämlich user_auto_key, user_uuid und user_random_key, die jeweils den automatisch zunehmenden Primärschlüssel, uuid als Primärschlüssel und random key als Primärschlüssel darstellen. Die anderen lassen wir völlig unverändert. Gemäß der Kontrollvariablenmethode generieren wir nur den Primärschlüssel jeder Tabelle mit unterschiedlichen Strategien, während die anderen Felder genau gleich sind, und testen dann die Einfügegeschwindigkeit und Abfragegeschwindigkeit der Tabelle:

Hinweis: Der Zufallsschlüssel bezieht sich hier tatsächlich auf die vom Snowflake-Algorithmus berechnete ID, die nicht kontinuierlich, wiederholt oder unregelmäßig ist: eine Zeichenfolge mit 18 Bit langen Werten

ID generiert automatisch Tabelle:

Benutzer-UUID-Tabelle

Zufällige Primärschlüsseltabelle:

1.2: Theorie allein reicht nicht, gehen wir direkt zum Programm und verwenden Springs jdbcTemplate, um den inkrementellen Test zu implementieren:

Technischer Rahmen: Springboot + JDBC-Template + Junit + Hutool. Das Prinzip des Programms besteht darin, eine Verbindung zu Ihrer eigenen Testdatenbank herzustellen und dann dieselbe Datenmenge in dieselbe Umgebung zu schreiben, um die Einfügezeit zu analysieren und ihre Effizienz zu synthetisieren. Um einen möglichst realistischen Effekt zu erzielen, werden alle Daten wie Name, E-Mail-Adresse und Adresse zufällig generiert. Das Programm wurde von Gitee hochgeladen und die Adresse befindet sich am Ende des Artikels.

Paket com.wyq.mysqldemo;
importiere cn.hutool.core.collection.CollectionUtil;
importiere com.wyq.mysqldemo.databaseobject.UserKeyAuto;
importiere com.wyq.mysqldemo.databaseobject.UserKeyRandom;
importiere com.wyq.mysqldemo.databaseobject.UserKeyUUID;
importiere com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
importiere com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
importiere com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
importiere com.wyq.mysqldemo.util.JdbcTemplateService;
importiere org.junit.jupiter.api.Test;
importiere org.springframework.beans.factory.annotation.Autowired;
importiere org.springframework.boot.test.context.SpringBootTest;
importiere org.springframework.util.StopWatch;
importiere java.util.List;
@SpringBootTest
Klasse MysqlDemoApplicationTests {

  @Autowired
  privater JdbcTemplateService jdbcTemplateService;

  @Autowired
  privater AutoKeyTableService autoKeyTableService;

  @Autowired
  privater UUIDKeyTableService uuidKeyTableService;

  @Autowired
  privater RandomKeyTableService randomKeyTableService;


  @Prüfen
  void testDBTime() {

    Stoppuhr Stoppuhr = neue Stoppuhr("SQL ausführen, Zeitverbrauch");


    /**
     * auto_increment-Schlüsselaufgabe */
    endgültige Zeichenfolge insertSql = "INSERT INTO user_key_auto(Benutzer-ID, Benutzername, Geschlecht, Adresse, Stadt, E-Mail, Bundesland) VALUES(?,?,?,?,?,?,?,?,?)";

    Liste<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
    stopwatch.start("Schlüsseltabelle automatisch generieren - Aufgabenstarts");
    lang start1 = System.currentTimeMillis();
    wenn (CollectionUtil.isNotEmpty(insertData)) {
      boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false);
      System.out.println(insertResult);
    }
    langes Ende1 = System.currentTimeMillis();
    System.out.println("Durch die Auto-Taste verbrauchte Zeit: " + (end1 - start1));

    Stoppuhr.Stopp();


    /**
     * uudID-Schlüssel
     */
    endgültige Zeichenfolge insertSql2 = "INSERT INTO user_uuid(id, Benutzer-ID, Benutzername, Geschlecht, Adresse, Stadt, E-Mail, Bundesland) VALUES(?,?,?,?,?,?,?,?,?,?)";

    Liste<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
    stopwatch.start("UUID-Schlüsseltabellenaufgabe startet");
    lang begin = System.currentTimeMillis();
    wenn (CollectionUtil.isNotEmpty(insertData)) {
      boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);
      System.out.println(insertResult);
    }
    lange über = System.currentTimeMillis();
    System.out.println("Vom UUID-Schlüssel verbrauchte Zeit: " + (over - begin));

    Stoppuhr.Stopp();


    /**
     * Zufälliger Schlüssel mit langem Wert
     */
    endgültige Zeichenfolge insertSql3 = "INSERT INTO user_random_key(id, Benutzer-ID, Benutzername, Geschlecht, Adresse, Stadt, E-Mail, Bundesland) VALUES(?,?,?,?,?,?,?,?,?,?)";
    Liste<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
    stopwatch.start("Zufällige Aufgabe mit langen Schlüsselwerten startet");
    Langer Start = System.currentTimeMillis();
    wenn (CollectionUtil.isNotEmpty(insertData)) {
      boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);
      System.out.println(insertResult);
    }
    Langes Ende = System.currentTimeMillis();
    System.out.println("Zeitaufwand für zufällige Tastenaufgabe: " + (Ende - Start));
    Stoppuhr.Stopp();


    String-Ergebnis = Stoppuhr.prettyPrint();
    System.out.println(Ergebnis);
  }

1.3: Ergebnisse des Programmschreibens

user_key_auto Schreibergebnis:

User_random_key Schreibergebnis:

Das Ergebnis des Schreibens der user_uuid-Tabelle:

1.4: Ergebnisse des Effizienztests

Wenn das vorhandene Datenvolumen 1,3 Millionen beträgt, lassen Sie uns testen, ob wir 100.000 Daten einfügen, um zu sehen, was dabei herauskommt:

Es ist ersichtlich, dass die Einfügungseffizienz der UUID am geringsten ist, wenn das Datenvolumen etwa 1 Million beträgt, und dass die Zeit für die UUID stark abnimmt, wenn in der nachfolgenden Sequenz 1,3 Millionen Daten hinzugefügt werden. Die Gesamteffizienzrangfolge basierend auf der Zeitnutzung lautet: auto_key>random_key>uuid. UUID hat die niedrigste Effizienz. Bei großen Datenmengen sinkt die Effizienz stark. Warum also tritt dieses Phänomen auf? Wenn Sie Zweifel haben, wollen wir dieses Problem untersuchen:

2: Vergleich von Indexstrukturen mittels UUID und Auto-Increment-ID

2.1: Verwenden der internen Struktur der Auto-Increment-ID

Die Werte des automatisch inkrementierten Primärschlüssels sind sequenziell, daher speichert Innodb jeden Datensatz hinter einem Datensatz. Wenn der maximale Füllfaktor der Seite erreicht ist (der standardmäßige maximale Füllfaktor von InnoDB beträgt 15/16 der Seitengröße, und 1/16 des Speicherplatzes wird für zukünftige Änderungen reserviert):

① Der nächste Datensatz wird auf eine neue Seite geschrieben. Sobald die Daten in dieser Reihenfolge geladen sind, wird die Primärschlüsselseite mit nahezu sequenziellen Datensätzen gefüllt, was die maximale Füllrate der Seite erhöht und Seitenverschwendung vermeidet.

②Die neu eingefügte Zeile wird unter der ursprünglich größten Zeile platziert. MySQL findet und adressiert die Zeile schnell und benötigt keine zusätzliche Zeit, um die Position der neuen Zeile zu berechnen.

③Reduzieren Sie die Seitenaufteilung und -fragmentierung

2.2: Interne Indexstruktur mithilfe der UUID

Da UUID im Vergleich zu sequentiellen Auto-Increment-IDs völlig unregelmäßig ist, muss der Wert einer neuen Zeile nicht unbedingt größer sein als der Wert des vorherigen Primärschlüssels. Daher kann InnoDB neue Zeilen nicht immer am Ende des Index einfügen. Stattdessen muss es eine neue geeignete Position für die neue Zeile finden, um neuen Speicherplatz zuzuweisen. Dieser Vorgang erfordert viele zusätzliche Vorgänge. Die fehlende Ordnung der Daten führt zu einer verstreuten Datenverteilung, was zu folgenden Problemen führt:

①: Die zu schreibende Zielseite wurde möglicherweise auf die Festplatte geschrieben und aus dem Cache entfernt oder noch nicht in den Cache geladen. InnoDB muss die Zielseite vor dem Einfügen von der Festplatte in den Speicher finden und lesen, was zu einer großen Menge zufälliger E/A führt

②: Da Schreibvorgänge nicht in der richtigen Reihenfolge erfolgen, muss InnoDB Seiten häufig aufteilen, um Platz für neue Zeilen zuzuweisen. Seitenaufteilungen führen dazu, dass große Datenmengen verschoben werden und für eine Einfügung mindestens drei Seiten geändert werden müssen.

③: Durch häufiges Aufteilen der Seiten werden die Seiten spärlich und unregelmäßig gefüllt, was letztendlich zu einer Datenfragmentierung führt

Nach dem Laden der Zufallszahlen (UUID und Snowflake-ID) in den Clustered-Index (der Standardindextyp von InnoDB) ist es manchmal erforderlich, eine OPTIMEIZE TABLE auszuführen, um die Tabelle neu zu erstellen und die Seitenfüllung zu optimieren, was einige Zeit in Anspruch nimmt.

Fazit: Wenn Sie InnoDB verwenden, sollten Sie Zeilen möglichst in der automatischen Inkrementreihenfolge des Primärschlüssels einfügen und möglichst monoton zunehmende Clustering-Schlüsselwerte verwenden, um neue Zeilen einzufügen.

2.3: Nachteile der Verwendung der Auto-Increment-ID

Ist es also nicht schädlich, eine automatisch inkrementierende ID zu verwenden? Nein, es gibt auch die folgenden Probleme mit selbstinkrementierenden IDs:

①: Sobald jemand Ihre Datenbank durchsucht, kann er anhand der selbstinkrementierenden ID der Datenbank Informationen zum Wachstum Ihres Unternehmens erhalten und so Ihre Geschäftssituation einfach analysieren

②: Bei hohen gleichzeitigen Lasten verursacht InnoDB beim Einfügen über den Primärschlüssel offensichtliche Sperrkonflikte. Die Obergrenze des Primärschlüssels wird zum Hotspot für Konflikte, da alle Einfügungen hier erfolgen. Gleichzeitige Einfügungen verursachen Lückensperrkonflikte.

③: Der Auto_Increment-Sperrmechanismus führt zum Entsperren der Auto-Increment-Sperre, was zu einem gewissen Leistungsverlust führt

Anhang: Um das Sperrkonfliktproblem von Auto_increment zu verbessern, müssen Sie die Konfiguration von innodb_autoinc_lock_mode optimieren

Drei: Zusammenfassung

Dieser Blog beginnt mit der eingangs gestellten Frage, dem Erstellen einer Tabelle und dem Verwenden von jdbcTemplate, um die Leistung verschiedener ID-Generierungsstrategien beim Einfügen großer Datenmengen zu testen. Anschließend werden die verschiedenen ID-Mechanismen in der MySQL-Indexstruktur sowie ihre Vor- und Nachteile analysiert und ausführlich erklärt, warum UUID und zufällige, sich nicht wiederholende IDs beim Einfügen von Daten Leistungsverluste aufweisen, und dieses Problem wird ausführlich erläutert. Bei der tatsächlichen Entwicklung ist es am besten, die Auto-Increment-ID gemäß der offiziellen Empfehlung von MySQL zu verwenden. MySQL ist tiefgreifend und es gibt noch viele Punkte darin, die es wert sind, optimiert zu werden, und die wir lernen müssen.

Anhang: Diese Blog-Demo-Adresse: https://gitee.com/Yrion/mysqlIdDemo

Damit ist dieser Artikel mit der ausführlichen Analyse, warum MySQL nicht empfiehlt, UUID oder Snowflake-ID als Primärschlüssel zu verwenden, abgeschlossen. Weitere relevante Inhalte zu MySQL UUID oder Snowflake-ID als Primärschlüssel finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!