Grund für den Test
Ein Entwicklungskollege hat ein Framework erstellt, in dem der Primärschlüssel uuid ist. Ich schlug ihm vor, automatisch inkrementierende Primärschlüssel zu verwenden. Er sagte, dass dies nicht unbedingt der Fall sei Ich sagte, dass die Indexfunktion von innodb zur automatischen Inkrementierung von IDs am effizientesten ist. Um ihn anhand tatsächlicher Fälle zu überzeugen, habe ich mich auf einen detaillierten Test vorbereitet.
Als Internetunternehmen muss es eine Benutzertabelle geben, und die Benutzertabelle UC_USER hat im Grunde Millionen Daher wird der Test auf Basis der auf dieser Tabelle basierenden Quasi-Testdaten durchgeführt.
Der Testprozess ist derzeit ein vielschichtiger und häufig verwendeter SQL-Test, an den ich natürlich denke Möglicherweise ist es nicht perfekt. Jeder kann gerne eine Nachricht hinterlassen, um einen umfassenderen Testplan oder eine Test-SQL-Anweisung vorzuschlagen.
CREATE TABLE `UC_USER` ( |
UC_USER_PK_VARCHAR-Tabelle, String-ID als Primärschlüssel, unter Verwendung von uuid
`ID` varchar(36) ZEICHENSATZ utf8mb4 NICHT NULL STANDARD '0' KOMMENTAR 'Primärschlüssel', „USER_NAME“ varchar(100) DEFAULT NULL COMMENT „Benutzername“, „USER_PWD“ varchar(200) DEFAULT NULL COMMENT „Passwort“, „BIRTHDAY“ datetime DEFAULT NULL COMMENT „Geburtstag“, `NAME` varchar(200) DEFAULT NULL COMMENT 'Name', `USER_ICON` varchar(500) DEFAULT NULL COMMENT 'Avatar picture', `SEX` char(1) DEFAULT NULL KOMMENTAR 'Geschlecht, 1: Männlich, 2: Weiblich, 3: Vertraulich', `NICKNAME` varchar(200) DEFAULT NULL COMMENT 'Spitzname', `STAT` varchar(10) DEFAULT NULL COMMENT ' Benutzerstatus, 01: normal, 02: eingefroren', `USER_MALL` bigint(20) DEFAULT NULL COMMENT 'Current MALL', `LAST_LOGIN_DATE` datetime DEFAULT NULL COMMENT 'Letzte Anmeldezeit', /> `LAST_LOGIN_IP` varchar(100) DEFAULT NULL COMMENT 'Letzte Login-IP', `SRC_OPEN_USER_ID` bigint(20) DEFAULT NULL COMMENT 'Quelle der gemeinsamen Anmeldung', `EMAIL` varchar( 200) DEFAULT NULL COMMENT „Mailbox“, „MOBILE“ varchar(50) DEFAULT NULL COMMENT „Mobiltelefon“, „IS_DEL“ char(1) DEFAULT „0“ COMMENT „Ob gelöscht werden soll“, `IS_EMAIL_CONFIRMED` char(1) DEFAULT '0' COMMENT 'Ob eine E-Mail-Adresse gebunden werden soll', `IS_PHONE_CONFIRMED` char(1) DEFAULT '0' COMMENT 'Ob ein Mobiltelefon gebunden werden soll', `CREATER` bigint (20) DEFAULT NULL COMMENT 'Ersteller', `CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Registrierungszeit', `UPDATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Änderungsdatum', `PWD_INTENSITY` char(1) DEFAULT NULL COMMENT 'Passwortstärke', `MOBILE_TGC` char(64) DEFAULT NULL COMMENT 'Mobile phone login ID', `MAC` char(64 ) STANDARD NULL KOMMENTAR 'MAC-Adresse' , `SOURCE` char(1) STANDARD '0' KOMMENTAR '1:WEB,2:IOS,3:ANDROID,4:WIFI,5:Managementsystem, 0:Unbekannt ', `ACTIVATE `char(1) DEFAULT '1' COMMENT 'Aktivierung, 1: aktiviert, 0: nicht aktiviert', `ACTIVATE_TYPE` char(1) DEFAULT '0' COMMENT 'Aktivierungstyp , 0: automatisch, 1: manuell ', PRIMARY KEY (`ID`), UNIQUE KEY `USER_NAME` (`USER_NAME`), KEY `MOBILE` (`MOBILE`) , SCHLÜSSEL `IDX_MOBILE_TGC ` (`MOBILE_TGC`,`ID`), SCHLÜSSEL `IDX_EMAIL` (`EMAIL`,`ID`), SCHLÜSSEL `IDX_CREATE_DATE` (`CREATE_DATE`, `ID`), KEY `IDX_UPDATE_DATE` (`UPDATE_DATE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User table'; |
Bestimmen Sie das Datenvolumen der beiden Tabellen
|. count( 1) | +---------+ | 5720112 | +------- ---+1 Zeile im Satz (0,00 Sek.) mysql> # Tabelle mit UUID als primär keymysql> select count(1) from UC_USER_PK_VARCHAR_1;
|
Primärschlüsseltyp | Datendateigröße | Belegte Kapazität strong> |
Selbstinkrementierende ID | -rw -rw---- 1 mysql mysql 2.5G 11. August 18:29 UC_USER.ibd | 2.5 G |
UUID | -rw-rw---- 1 mysql mysql 5.4G 15. Aug. 15: 11 UC_USER_PK_VARCHAR_1.ibd | 5,4 G |
Primärschlüsseltyp | SQL-Anweisung td> | Ausführungszeit (Sekunden) |
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` ='14782121512'; | 0,118 |
|
|
||
UUID | SELECT SQL_NO_CACHE t.* FROM test. `UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` ='14782121512'; | 0,117 |
|
||
Automatische Inkrementierung ID | SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` IN( '14782121512','13761460105'); | 0,049 |
UUID td> | SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` IN('14782121512','13761460105'); | |
|
||
Auto-Inkrement-ID | SELECT SQL_NO_CACHE t .* FROM test.`UC_USER` t WHERE t.`CREATE_DATE`='2013-11-24 10:26:36' ; | 0,139 |
UUID | SELECT SQL_NO_CACHE t.* FROM test .`UC_USER_PK_VARCHAR_1` t WHERE t.`CREATE_DATE`='2013-11-24 10:26:43' ; | 0,126 |
|
SQL-Anweisung | Ausführungszeit (Sekunden) | |||||||||||||||||||||||||||||||||||||||
(1) Fuzzy-Bereichsabfrage 1000 Teile der Daten ist die Leistung der selbsterhöhenden ID besser als die der UUID | |||||||||||||||||||||||||||||||||||||||||
Selbsterhöhende ID | SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1000; | 1,784 | |||||||||||||||||||||||||||||||||||||||
UUID | SELECT SQL_NO_CACHE t.* FROM test. `UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1000; | 3.196 td> | |||||||||||||||||||||||||||||||||||||||
(2) Datumsbereichsabfrage 20 Datenelemente, die automatisch Inkrement-ID ist etwas schwächer als UUID td> | |||||||||||||||||||||||||||||||||||||||||
Auto-Inkrement-ID | SELECT SQL_NO_CACHE t.* FROM test.` UC_USER` t WHERE t.`CREATE_DATE` > '2016-08-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 20; | 0,601 | |||||||||||||||||||||||||||||||||||||||
UUID | SELECT SQL_NO_CACHE t .* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`CREATE_DATE` > '2016-08-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 20;0,543 | ||||||||||||||||||||||||||||||||||||||||
(3) Bereichsabfrage für 200 Daten, die Leistung der automatisch inkrementierten ID ist besser als die von UUID | |||||||||||||||||||||||||||||||||||||||||
Auto-Inkrement-ID | SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`CREATE_DATE` > 2016-07-01 10:26:36' ORDER BY t.`UPDATE_DATE ` DESC LIMIT 200; | 2.314 | tr>|||||||||||||||||||||||||||||||||||||||
UUID | SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t. `CREATE_DATE` > '2016-07-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 200; | 3.229 | |||||||||||||||||||||||||||||||||||||||
Bereichsabfrage Gesamtmenge, Auto-Inkrement-ID ist besser als UUID | |||||||||||||||||||||||||||||||||||||||||
Auto-Inkrement-ID | SELECT SQL_NO_CACHE COUNT(1) FROM test.`UC_USER` t WHERE t.`CREATE_DATE` > ' 2016-07-01 10:26:36' ; | 0,514 | |||||||||||||||||||||||||||||||||||||||
UUID | SELECT SQL_NO_CACHE COUNT(1 ) FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`CREATE_DATE` > '2016-07 -01 10:26:36' ; | 1.092 |
PS: Bei Vorhandensein eines Caches gibt es keinen kleinen Unterschied in der Ausführungseffizienz zwischen den beiden.
SQL-Anweisung |
Ausführungszeit (Sekunden) |
|
||
|
|
ID automatisch inkrementieren | UPDATE test.`UC_USER` t SET t.`MOBILE_TGC`='T2' WHERE t.`CREATE_DATE` > ' 2016-05-03 10:26:36' AND t.`CREATE_DATE` <'2016-05- 04 00:00:00' ;||
1.419 | UUID | UPDATE test.`UC_USER_PK_VARCHAR_1` t SET t.`MOBILE_TGC`='T2' WHERE t.`CREATE_DATE` > ; '2016-05-03 10:26:36' AND t.`CREATE_DATE` <'2016- 05-04 00:00:00' ; | ||
5.639 | ||||
| Auto-Inkrement-ID | INSERT INTO test.`UC_USER`( ID, `USER_NAME`, `USER_PWD`, `BIRTHDAY`, `NAME`, `USER_ICON `, `SEX`, `NICKNAME`, `STAT`, `USER_MALL`, `LAST_LOGIN_DATE`, ` LAST_LOGIN_IP`, `SRC_OPEN_USER_ID`, `EMAIL`, `MOBILE`, `IS_DEL`, `IS_EMAIL _CONFIRMED`, `IS_PHONE_CONFIRMED`, `CREATER`, `CREATE_DATE`, `UPDATE_DATE`, `PWD_INTENSITY`, `MOBILE_TGC`, `MAC `, `SOURCE`, `ACTIVATE`, `ACTIVATE_TYPE` ) SELECT NULL, `USER_NAME `,8 ), `USER_PWD`, `BIRTHDAY`, `NAME`, `USER_ICON`, `SEX`, `NICKNAME`, `STAT `, `USER_MALL`, `LAST_LOGIN_DATE`, `LAST_LOGIN_IP`, `SRC_OPEN_USER_ ID`, `EMAIL`, CONCAT('110',TRIM(`MOBILE`)), `IS_DEL`, `IS_EMAIL_CONFIRMED`, `IS_PHONE_CONFIRMED`, ` CREATER`, `CREATE_DATE`, `UPDATE_DATE`, `PWD_INTENSITY`, `MOBILE_TGC`, `MAC`, `SOURCE`, `ACTIVATE`, `ACTIVATE_TYPE` FROM `test`.`UC_USER_1` LIMIT 100; | ||
0,105 | UUID | INSERT INTO test.`UC_USER_PK_VARCHAR_1`( ID, `USER_NAME`, `USER_PWD`, `BIRTHDAY`, NAME`, `USER_ICON`, `SEX`, `NICKNAME`, `STAT` ; `, `CREATE_DATE`, `UPDATE_DATE`, ` PWD_INTENSITY`, `MOBILE_TGC`, `MAC`, `SOURCE`, `ACTIVATE`, `ACTIVATE_TYPE` ) SELECT UUID(), CONCAT('110',`USER_NAME`,8), `USER_PWD`, `BIRTHDAY`, ` NAME`, `USER_ICON`, `SEX`, `NICKNAME`, `STAT`, `USER_MALL`, `LAST_LOGIN_DATE`, `LAST_LOGIN_IP`, `SRC_OPEN_USER_ID`, `EMAIL`, CONCAT('110',TRIM(`MOBILE` )) `, `ACTIVATE_TYPE` FROM `test`.`UC_USER_1` LIMIT 100; | 0,424 |
主键类型 | SQL语句 | 执行时间 (秒) |
Mysqldump备份 | ||
自增ID | time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_500> UC_USER_500.sql | 28.59秒 |
UUID |
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_PK_VARCHAR_500> UC_USER_PK_VARCHAR_500.sql |
31.08秒 |
MySQL恢复 | ||
自增ID |
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_500.sql | 7m36.601s |
UUID | time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_500.sql | 9m42.472s |
|
|
|
<🎜>
Mysqldump备份<🎜><🎜><🎜>
MySQL-Datenbank
Unter dem Test der 500-W-Datensatztabelle:
(1) Für den normalen Abruf einzelner oder etwa 20 Datensätze ist die UUID der Primärschlüssel. Die Effizienz ist fast gleich;
(2) Bei Bereichsabfragen, insbesondere bei Hunderten oder Tausenden von Datensätzen, ist die Effizienz der automatischen Inkrementierung von IDs größer als bei
(3). Bei Bereichsabfragen ist die Effizienz der selbsterhöhenden ID größer als die von uuid. (4) In Bezug auf den Speicher beträgt der von der selbsterhöhenden ID belegte Speicherplatz die Hälfte von uuid.
(5) In Bezug auf Sicherung und Wiederherstellung ist der automatisch inkrementierende ID-Primärschlüssel etwas besser als UUID.
4
#
Selbstinkrement id Tabelle als Primärschlüssel mysql>
4.2 Einzelne Daten werden nach Index abgefragt, automatische Inkrementierung id und uuid beträgt: (2~3):1
4.3 BereichwieAbfrage, automatische Inkrementierung ID schneidet besser ab als UUID, Verhältnis(1,5~2):1
4.4 Test schreiben, automatische Inkrementierung ID Es ist effizienter als UUID und das Verhältnis beträgt (3~10): 1
4.5、备份和恢复,自增ID性能优于UUID
<🎜> Mysqldump备份<🎜><🎜><🎜>
|
MySQL-Datenbank
5, 1000WZusammenfassungUnter dem Test der 1000-W-Datensatztabelle: (1) Für den normalen Abruf einzelner oder etwa 20 Datensätze beträgt die Effizienz der automatischen Inkrementierung des Primärschlüssels das Zwei- bis Dreifache die des UUID-Primärschlüssels; (2) Bei Bereichsabfragen, insbesondere für Hunderte oder Tausende von Datensätzen, ist die Effizienz der automatischen Inkrementierung von IDs jedoch größer als bei (3). Bei der statistischen Zusammenfassung für Bereichsabfragen beträgt die Effizienz des automatisch inkrementierenden ID-Primärschlüssels das 1,5- bis 2-fache der Effizienz des UUID-Primärschlüssels Die automatisch inkrementierende ID beträgt die Hälfte der UUID. (5) In Bezug auf das Schreiben beträgt die Effizienz des automatisch inkrementierenden ID-Primärschlüssels das Drei- bis Zehnfache des UUID-Primärschlüssels ist offensichtlich, insbesondere wenn Daten in einem kleinen Bereich aktualisiert werden. (6) In Bezug auf Sicherung und Wiederherstellung ist der selbsterhöhende ID-Primärschlüssel etwas besser als UUID.
6, MySQLVerteilte Architektur KompromisseDie verteilte Architektur bedeutet, dass die Einzigartigkeit des Primärschlüssels einer Tabelle in mehreren Instanzen beibehalten werden muss. Derzeit ist der gewöhnliche Primärschlüssel mit einer selbsterhöhenden ID für eine einzelne Tabelle nicht geeignet, da bei mehreren MySQL-Instanzen das Problem der globalen Eindeutigkeit des Primärschlüssels auftritt.
6.1, automatische Inkrementierung ID Primärschlüssel + Schrittgröße, geeignet für mittelgroße verteilte Szenarien Auf dem Master jeder Clusterknotengruppe, Stellen Sie (auto_increment_increment) ein, versetzen Sie den Startpunkt jedes Clusters um 1 und wählen Sie eine Schrittgröße, die größer ist als die Anzahl der geteilten Cluster, die in Zukunft grundsätzlich nicht mehr erreicht werden kann, um den Effekt einer relativen Segmentierung der zu erfüllenden ID zu erzielen der globale einzigartige Effekt.Die Vorteile sind: einfache Implementierung, einfache Nachwartung und Transparenz für die Anwendung.
Der Nachteil ist: Die erste Einrichtung ist relativ kompliziert, da für die zukünftige Geschäftsentwicklung ausreichende Schrittweiten berechnet werden müssen ;Planung: Wenn beispielsweise insgesamt N Knotengruppen geplant sind, dann ist die i- te Knotengruppe Die Konfiguration von my.cnf ist: auto_increment_offset i auto_increment_increment N
Wenn 48 Knotengruppen geplant sind, ist N 48, Konfigurieren Sie nun die 8. Knotengruppe, dieses i ist 8, die Konfiguration in my.cnf der 8. Knotengruppe lautet: auto_increment_offset 8 auto_increment_increment 48
6.2, UUID, geeignet für kleine verteilte Umgebungen Bei einer geclusterten Primärschlüssel-Engine wie InnoDB werden die Daten nach dem Primärschlüssel sortiert. Aufgrund der Unordnung der UUID erzeugt InnoDB einen enormen E/A-Druck und weil der Index und die Daten zusammen gespeichert werden , strings Der Primärschlüssel verdoppelt den Speicherplatz.Während des Speicherns und Abrufens sortiert innodb die Primärschlüssel physisch, was eine gute Nachricht für auto_increment_int ist, da die Position des später eingefügten Primärschlüssels immer am Ende liegt. Für UUID ist dies jedoch eine schlechte Nachricht, da UUID chaotisch ist und die Position des Primärschlüssels jedes Mal ungewiss ist. Wenn der Primärschlüssel physisch sortiert ist, führt dies zwangsläufig dazu Eine große Anzahl von E/A-Vorgängen beeinträchtigt die Effizienz. Wenn die Datenmenge weiter wächst, insbesondere wenn die Datenmenge mehrere zehn Millionen Datensätze überschreitet, sinkt die Lese- und Schreibleistung dramatisch. Vorteile: Der Aufbau ist relativ einfach und erfordert nicht die Eindeutigkeit des Primärschlüssels. Nachteile: Nimmt doppelt so viel Speicherplatz in Anspruch (es kostet 2mal mehr für nur ein Stück Cloud-Speicher) und später Die Lese- und Schreibleistung sinkt drastisch.
6.3, Snowflake-Algorithmus erstellt globale Selbst- erstellt ID hinzugefügt, geeignet für verteilte Szenarien in Big-Data-Umgebungen Der von Twitter angekündigte Open-Source-Algorithmus für verteilte IDs Snowflake (Java-Version)IdWorker.java:
|