Processus détaillé de comparaison des avantages et des inconvénients de l'utilisation de la clé primaire d'ID à incrémentation automatique et de l'UUID comme clé primaire dans MySQL (test sur un million à dix millions d'enregistrements de table)
黄舟
Libérer： 2017-02-16 11:35:54
original
1794 Les gens l'ont consulté
Raison du test
Un collègue de développement a créé un cadre dans lequel la clé primaire est uuid. Je lui ai suggéré que MySQL ne devrait pas utiliser uuid et que les clés primaires à incrémentation automatique sont plus efficaces. Il a dit que ce n'est pas nécessairement élevé. . J'ai dit que la fonctionnalité d'indexation d'innodb conduit à une incrémentation automatique des identifiants. Créer la clé primaire est le plus efficace. Afin de le convaincre avec des cas réels, je me suis préparé à faire un test détaillé.
En tant que société Internet, il doit y avoir une table utilisateur, et la table utilisateur UC_USER en compte essentiellement des millions des enregistrements. Par conséquent, le test est effectué sur la base des données de quasi-test basées sur ce tableau.
Le processus de test est actuellement un multi-facettes et couramment utilisé plusieurs types de tests SQL auxquels je pense bien sûr. n'est peut-être pas parfait. Tout le monde est invité à laisser un message pour proposer un plan de test plus complet ou une instruction de test SQL.
1. Préparer les tableaux et les données
UC_USER, ID d'auto-incrémentation comme clé primaire, la structure de la table est similaire à la suivante :
CREATE TABLE `UC_USER_PK_VARCHAR_1` ( `ID` varchar(36) JEU DE CARACTÈRES utf8mb4 NON NULL PAR DÉFAUT '0' COMMENTAIRE 'Clé primaire', `USER_NAME` varchar(100) COMMENTAIRE NULL PAR DÉFAUT 'Nom d'utilisateur', `USER_PWD` varchar(200) COMMENTAIRE NULL PAR DÉFAUT 'Mot de passe', `BIRTHDAY` datetime COMMENTAIRE NULL PAR DÉFAUT 'Anniversaire', `NOM` varchar(200) COMMENTAIRE NULL PAR DÉFAUT 'Nom', `USER_ICON` varchar(500) COMMENTAIRE NULL PAR DÉFAUT 'Image d'avatar', `SEX` char(1) NULL PAR DÉFAUT COMMENT 'Sexe, 1 : Homme, 2 : Femme, 3 : Confidentiel', `NICKNAME` varchar(200) DEFAULT NULL COMMENT 'Pseudo', `STAT` varchar(10) DEFAULT NULL COMMENT ' Statut de l'utilisateur, 01 : normal, 02 : gelé', `USER_MALL` bigint(20) DEFAULT NULL COMMENT 'Current MALL', `LAST_LOGIN_DATE` datetime DEFAULT NULL COMMENT 'Dernière heure de connexion', /> `LAST_LOGIN_IP` varchar(100) DEFAULT NULL COMMENT 'Dernière adresse IP de connexion', `SRC_OPEN_USER_ID` bigint(20) DEFAULT NULL COMMENT 'Source de connexion conjointe', `EMAIL` varchar( 200) COMMENTAIRE NULL PAR DÉFAUT 'Boîte aux lettres', `MOBILE` varchar(50) COMMENTAIRE NULL PAR DÉFAUT 'Téléphone mobile', `IS_DEL` char(1) COMMENTAIRE PAR DÉFAUT '0' 'Supprimer', `IS_EMAIL_CONFIRMED` char(1) DEFAULT '0' COMMENT 'S'il faut lier une adresse e-mail', `IS_PHONE_CONFIRMED` char(1) DEFAULT '0' COMMENT 'S'il faut lier un téléphone mobile', `CREATER` bigint (20) DEFAULT NULL COMMENT 'Créateur', `CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Heure d'enregistrement', `UPDATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Date de modification', `PWD_INTENSITY` char(1) COMMENTAIRE NULL PAR DÉFAUT 'Force du mot de passe', `MOBILE_TGC` char(64) COMMENTAIRE NULL PAR DÉFAUT 'ID de connexion du téléphone portable', `MAC` char(64 ) COMMENTAIRE NULL PAR DÉFAUT 'adresse mac', `SOURCE` char(1) COMMENTAIRE PAR DÉFAUT '0' '1:WEB,2:IOS,3:ANDROID,4:WIFI,5:Système de gestion, 0:Inconnu ', `ACTIVATE `char(1) DEFAULT '1' COMMENT 'Activation, 1 : activé, 0 : non activé', `ACTIVATE_TYPE` char(1) DEFAULT '0' COMMENT 'Type d'activation , 0 : automatique, 1 : manuel ', CLÉ PRIMAIRE (`ID`), CLÉ UNIQUE `USER_NAME` (`USER_NAME`), CLÉ `MOBILE` (`MOBILE`) , CLÉ `IDX_MOBILE_TGC ` (`MOBILE_TGC`,`ID`), CLÉ `IDX_EMAIL` (`EMAIL`,`ID`), CLÉ `IDX_CREATE_DATE` (`CREATE_DATE`, `ID`), KEY `IDX_UPDATE_DATE` (`UPDATE_DATE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table utilisateur';
corps>
2. 500WTest de données
2.1 Entrez 500W données, incrémentation automatique ID économisez la moitié l'Espace disque
Déterminer le volume de données des deux tables
# 自增id为主键的表
mysql> select count(1) from UC_USER;
----------
| count(1) |
----------
| 5720112 |
----------
1 row in set (0.00 sec)
mysql>
# uuid为主键的表
mysql> select count(1) from UC_USER_PK_VARCHAR_1;
----------
| count(1) |
----------
| 5720112 |
----------
1 row in set (1.91 sec)
# Table avec identifiant d'incrémentation automatique comme clé primairemysql> select count(1) from UC_USER; ----------
主键类型
数据文件大小
占据容量
自增ID
-rw-rw---- 1 mysql mysql 2.5G Aug 11 18:29 UC_USER.ibd
2.5 G
UUID
-rw-rw---- 1 mysql mysql 5.4G Aug 15 15:11 UC_USER_PK_VARCHAR_1.ibd
5.4 G
| compte(1) |
----------
| 5720112 |
---------- 1 ligne dans l'ensemble (0,00 sec) mysql> # Table avec uuid comme clé primairemysql> sélectionnez count(1 ) depuis UC_USER_PK_VARCHAR_1;
主键类型
SQL语句
执行时间 (秒)
自增ID
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
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` IN( '14782121512','13761460105');
0.049
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` IN('14782121512','13761460105');
0.040
自增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
---------- | count(1) | ----- ----- | 5720112 | ---------- 1 rang dans l'ensemble (1,91 sec)
tbody>
À en juger par la capacité d'espace occupée, l'ID auto-croissant est environ la moitié plus petit que l'UUID.
Type de clé primaire
Taille du fichier de données
Capacité occupée strong>
ID à auto-incrémentation
-rw -rw---- 1 mysql mysql 2.5G 11 août 18:29 UC_USER.ibd
2.5 G
UUID
-rw-rw---- 1 mysql mysql 5.4G 15 août 15 : 11 UC_USER_PK_VARCHAR_1.ibd
5.4 G
2.2 Les données uniques sont indexées et l'identifiant est incrémenté de id et uuidPas très différent
Type de clé primaire
Instruction SQL td>
Durée d'exécution (secondes)
ID d'incrémentation automatique
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
Incrémentation automatique 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');
0,040
ID d'incrémentation automatique
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
2.3 Portéecommerequête, incrémentation automatique ID fonctionne mieux que UUID
主键类型
SQL语句
执行时间 (秒)
（1）模糊范围查询1000条数据，自增ID性能要好于UUID
自增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
（2）日期范围查询20条数据，自增ID稍微弱于UUID
自增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）范围查询200条数据，自增ID性能要好于UUID
自增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
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
范围查询总数量，自增ID要好于UUID
自增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
Type de clé primaire
Instruction SQL
Durée d'exécution (secondes)
(1) Requête de plage floue 1000 pièces des données, les performances de l'ID auto-croissant sont meilleures que celles de l'UUID
ID auto-croissant
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) Requête de plage de dates 20 données, l'ID d'incrémentation automatique est légèrement plus faible que l'UUID
ID d'incrémentation automatique
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) Interrogation de plage 200 éléments de données, les performances d'identification par incrémentation automatique sont meilleures que l'UUID
ID d'incrémentation automatique
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`CREATE_DATE` > -01 10:26:36' ORDER BY t.`UPDATE_DATE ` DESC LIMIT 200;
2.314
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
Quantité totale de la requête de plage, l'ID à incrémentation automatique est meilleur que l'UUID
ID d'incrémentation automatique
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 OÙ t.`CREATE_DATE` > '2016-07 -01 10:26:36' ;
1.092 td>
PS : En présence de cache, il n'y a pas de petite différence d'efficacité d'exécution entre les deux.
2.4 Test d'écriture, incrémentation automatique ID est 4 fois
Type de clé primaire
Instruction SQL
Temps d'exécution (secondes)
ID à incrémentation automatique
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' ;
<🎜><🎜>自增ID<🎜><🎜><🎜><🎜>heure mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_500> UC_USER_500.sql28.59秒
UUIDheure mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_PK_VARCHAR_500> UC_USER_PK_VARCHAR_500.sql31.08秒
Application MySQL
自增IDtime mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_500.sql<🎜><🎜><🎜><🎜>7m36.601s<🎜><🎜><🎜>
<🎜><🎜>UUID<🎜><🎜><🎜><🎜>heure mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_500.sql<🎜><🎜><🎜><🎜>9m42.472s<🎜><🎜><🎜>
<🎜><🎜><🎜><🎜>< 🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜>
3, 500WRésumé
Sous le test d'une table d'enregistrement de 500 W :
(1) Pour la récupération ordinaire d'un seul enregistrement ou d'environ 20 enregistrements, l'uuid est la clé primaire. L'efficacité est presque la même ;
(2) Mais dans les requêtes par plage, en particulier pour des centaines ou des milliers d'enregistrements, l'efficacité de l'auto-incrémentation de l'identifiant est supérieure à celle de l'uuid ; Dans la requête de plage Lorsque les statistiques sont résumées, l'efficacité de l'ID auto-croissant est supérieure à celle de l'uuid ;
(5 ) En termes de sauvegarde et de récupération, la clé primaire ID à incrémentation automatique est légèrement meilleure que l'UUID.
4、
1000WTest de données 4.1
Entrée
1000WEnregistrement des données, voir espace de stockage
# auto-incrémentation
# 自增id为主键的表
mysql> use test;
Database changed
mysql> select count(1) from UC_USER_1;
----------
| count(1) |
----------
| 10698102 |
----------
1 row in set (27.42 sec)
# uuid为主键的表
mysql> select count(1) from UC_USER_PK_VARCHAR_1;
----------
| count(1) |
----------
| 10698102 |
----------
1 row in set (0.00 sec)
mysql>
id
Table comme clé primaire
mysql> utiliser le test ;
Base de données modifiée
主键类型
数据文件大小
占据容量
自增ID
-rw-rw---- 1 mysql mysql 4.2G Aug 20 23:08 UC_USER_1.ibd
4.2 G
UUID
-rw-rw---- 1 mysql mysql 8.8G Aug 20 18:20 UC_USER_PK_VARCHAR_1.ibd
8.8 G
mysql> - -------- | compte(1) | ---------- | 10698102 | ---------- 1 ligne dans la série (27,42 sec) # uuid est la table de clé primairemysql> select count(1) from UC_USER_PK_VARCHAR_1; ---------- | | ---------- | 10698102 | ---------- 1 ligne dans l'ensemble (0,00 sec) mysql> À en juger par l'espace occupé, l'ID d'auto-incrémentation est environ moitié plus petit que l'UUID :
Type de clé primaire
Taille du fichier de données
Capacité occupée
ID d'incrémentation automatique
-rw-rw---- 1 mysql mysql 4.2G 20 août 23:08 UC_USER_1.ibd
4.2G
UUID
-rw-rw---- 1 mysql mysql 8.8G 20 août 18:20 UC_USER_PK_VARCHAR_1.ibd
8.8G
4.2 Les données uniques sont indexées et incrémentées id et uuid est : (2~3):1
Type de clé primaire
主键类型
SQL语句
执行时间 (秒)
单条记录查询
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`MOBILE` ='14782121512';
0.069
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` ='14782121512';
0.274
小范围查询
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`MOBILE` IN( '14782121512','13761460105');
0.050
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` IN('14782121512','13761460105');
0.151
根据日期查询
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`CREATE_DATE`='2013-11-24 10:26:36' ;
0.269
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.810
Instruction SQL
Durée d'exécution (secondes)
Requête d'enregistrement unique
ID à incrémentation automatique
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`MOBILE` ='14782121512';
0,069
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` ='14782121512';
0,274
Requête à petite plage
Auto -increment ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`MOBILE` IN( '14782121512','13761460105');
0,050
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` IN('14782121512','13761460105' );
0,151
Requête basée sur la date
ID d'incrémentation automatique
SELECT SQL_NO_CACHE t.* FROM test .`UC_USER_1` t OÙ t.`CREATE_DATE`='2013-11-24 10:26:36' ;
0,269
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.810
4.3 Portéecommerequête, incrémentation automatique ID fonctionne mieux que UUID, ratio(1,5~2) :1
主键类型
SQL语句
执行时间 (秒)
（1）模糊范围查询1000条数据，自增ID性能要好于UUID
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1000;
2.398
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1000;
5.872
（2）日期范围查询20条数据，自增ID稍微弱于UUID
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`CREATE_DATE` > '2016-08-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 20;
0.765
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;
1.090
（3）范围查询200条数据，自增ID性能要好于UUID
自增ID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`CREATE_DATE` > '2016-07-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 200;
1.569
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;
2.597
范围查询总数量，自增ID要好于UUID
自增ID
SELECT SQL_NO_CACHE COUNT(1) FROM test.`UC_USER_1` t WHERE t.`CREATE_DATE` > '2016-07-01 10:26:36' ;
1.129
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' ;
2.302
Type de clé primaire
Instruction SQL
Durée d'exécution (secondes)
(1) Requête de plage floue 1 000 éléments de données, les performances d'identification par incrémentation automatique sont meilleures que l'UUID td>
ID d'incrémentation automatique
SELECT SQL_NO_CACHE t.* FROM test. `UC_USER` t OÙ t .`MOBILE` LIKE '147%' LIMITE 1000;
2.398
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` LIKE '147%' LIMITE 1000 ;
5.872
(2) Interrogez 20 éléments de données dans la plage de dates et l'ID d'incrémentation automatique est légèrement plus faible que l'UUID
tr>
ID d'incrémentation automatique
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t OÙ t.`CREATE_DATE` > '2016-08-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 20;
0,765
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t OÙ t.`CREATE_DATE` > '2016-08-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 20;
1.090
(3) Interrogation de plage de 200 éléments de données, les performances de l'ID à incrémentation automatique sont meilleures que celles de l'UUID
ID à incrémentation automatique td>
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`CREATE_DATE` > '2016-07-01 10:26:36' ORDER BY t.` UPDATE_DATE` DESC LIMIT 200 ;
1,569
UUID
SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`CREATE_DATE` > ' ORDER BY t.`UPDATE_DATE` DESC LIMIT 200;
2.597
Quantité totale de la requête de plage, l'ID à incrémentation automatique est meilleur que l'UUID
ID d'incrémentation automatique
SELECT SQL_NO_CACHE COUNT(1) FROM test.`UC_USER_1` t WHERE t.`CREATE_DATE ` > '2016-07-01 10:26:36' ;
1.129
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' ;
2.302
4.4 Test d'écriture, incrémentation automatique ID Il est plus efficace que UUID, et le rapport est (3~10) : 1
Type de clé primaire
Instruction SQL
Durée d'exécution (secondes)
Modifier les enregistrements d'une journée
Augmenter l'ID
UPDATE test.`UC_USER_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' ;
2.685
UUID
UPDATE test.`UC_USER_PK_VARCHAR_1` t SET t.`MOBILE_TGC`='T2' OÙ t.`CREATE_DATE` > '2016-05-03 10:26:36' ET t.`CREATE_DATE` <'2016- 05-04 00:00:00' ;
<🎜><🎜>自增ID<🎜><🎜><🎜><🎜>heure mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_1> UC_USER_1.sql0m50.548s
UUIDheure mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_PK_VARCHAR_1> UC_USER_PK_VARCHAR_1.sql0m58.590s
Application MySQL
自增IDtime mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_1.sql<🎜><🎜><🎜><🎜>17m30.822s<🎜><🎜><🎜>
<🎜><🎜>UUID<🎜><🎜><🎜><🎜>heure mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_1.sql<🎜><🎜><🎜><🎜>23m6.360s<🎜><🎜><🎜>
<🎜><🎜><🎜><🎜>< 🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜><🎜>
5, 1000WRésumé
Sous le test d'une table d'enregistrement de 1 000 W :
(1) Pour une récupération ordinaire d'un seul enregistrement ou d'environ 20 enregistrements, l'efficacité de la clé primaire à incrémentation automatique est de 2 à 3 fois celle de la clé primaire uuid
(2) Cependant, pour les requêtes de plage, notamment pour des centaines ou des milliers d'enregistrements, l'efficacité des identifiants auto-incrémentés est supérieure à celle de uuid ; Lors d'un résumé statistique pour les requêtes de plage, l'efficacité de la clé primaire id à incrémentation automatique est 1,5 à 2 fois supérieure à celle de la clé primaire uuid
(4) En termes de stockage, l'espace de stockage occupé par le ; L'identifiant auto-incrémenté est la moitié de l'uuid ;
( 5) En termes d'écriture, l'efficacité de la clé primaire ID auto-incrémentée est 3 à 10 fois celle de la clé primaire UUID. est évident, surtout lors de la mise à jour des données dans une petite plage.
(6) En termes de sauvegarde et de récupération, la clé primaire ID à incrémentation automatique est légèrement meilleure que l'UUID.
6
, MySQLArchitecture distribuée compromisL'architecture distribuée signifie que le caractère unique de la clé primaire d'une table doit être conservé dans plusieurs instances. À l'heure actuelle, la clé primaire d'ID auto-croissante ordinaire à table unique ne convient pas, car plusieurs instances MySQL rencontreront le problème de l'unicité globale de la clé primaire.
6.1
, incrémentation automatique ID Clé primaire taille de pas, adaptée aux scénarios distribués à moyenne échelle
Sur le maître de chaque groupe de nœuds de cluster, définissez (auto_increment_increment), échelonnez le point de départ de chaque cluster de 1 et choisissez une taille de pas supérieure au nombre de clusters divisés qui est fondamentalement impossible à atteindre à l'avenir, afin d'obtenir l'effet de segmentation relative de l'ID pour répondre aux effet global unique.
Les avantages sont : une mise en œuvre simple, une post-maintenance simple et transparente pour l'application.
L'inconvénient est le suivant : la première configuration est relativement compliquée, car des tailles d'étapes suffisantes doivent être calculées pour le développement futur de l'entreprise
;
Planification :
Par exemple, si un total de N groupes de nœuds sont planifiés, alors le i- ème groupe de nœuds La configuration de my.cnf est :
auto_increment_offset i
auto_increment_increment N
Si 48 groupes de nœuds sont prévus, N vaut 48, configurez maintenant le 8ème groupe de nœuds, ce i est 8, la configuration dans my.cnf du 8ème groupe de nœuds est :
auto_increment_offset 8
auto_increment_increment 48
6.2
, UUID, adapté à un environnement distribué à petite échelle Pour un moteur de type clé primaire clusterisée comme InnoDB, les données seront triées en fonction de la clé primaire. En raison du désordre de l'UUID, InnoDB générera une énorme pression d'E/S et parce que l'index et les données sont stockés ensemble. , les chaînes La clé primaire doublera l'espace de stockage.
Pendant le stockage et la récupération, innodb triera physiquement les clés primaires, ce qui est une bonne nouvelle pour auto_increment_int, car la position de la clé primaire insérée plus tard est toujours à la fin. Mais pour uuid, c'est une mauvaise nouvelle, car uuid est compliqué et la position de la clé primaire insérée à chaque fois est incertaine. Cela peut être au début ou au milieu. Lorsque la clé primaire est physiquement triée, cela entraînera inévitablement. un grand nombre d'opérations d'E/S affectent l'efficacité. Lorsque la quantité de données continue d'augmenter, en particulier lorsque la quantité de données dépasse des dizaines de millions d'enregistrements, les performances de lecture et d'écriture chutent considérablement.
Avantages : La construction est relativement simple et ne nécessite pas l'unicité de la clé primaire.
Inconvénients : prend deux fois plus d'espace de stockage (cela coûtera
2 fois plus cher pour un seul élément de stockage cloud), plus tard Lecture et les performances d'écriture diminuent considérablement.
6.3
, l'algorithme Snowflake crée une auto- made Ajout de ID, adapté aux scénarios distribués dans les environnements Big Data L'algorithme d'identification distribué open source snowflake (version Java) annoncé par Twitter
IdWorker.java：
package com.demo.elk;
importer org.slf4j.Logger;
importer org.slf4j.LoggerFactory ;
protected static final Logger LOG = LoggerFactory.getLogger(IdWorker.class);
private long workerId;
private long datacenterId;
séquence longue privée = 0L;
privée longue twepoch = 1288834974657L;
privée longue workerIdBits = 5L;
privée longue datacenterIdBits = 5L;
privé long maxWorkerId = -1L ^ (-1L << workerIdBits);
privé long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);
privé long séquenceBits = 12L;
privé long travailleurIdShift = séquenceBits;
privé long datacenterIdShift = séquenceBits travailleurIdBits;
priv mangé long timestampLeftShift = séquenceBits workerIdBits datacenterIdBits;
private long séquenceMask = -1L ^ (-1L << séquenceBits);
private long lastTimestamp = -1L;
public IdWorker(long workerId, long datacenterId) {
// vérification de l'intégrité de workerId
if (workerId > maxWorkerId || ID travailleur < 0) {
throw new IllegalArgumentException(String.format("l'ID du travailleur ne peut pas être supérieur à %d ou inférieur à 0", maxWorkerId));
}
<🎜 < } this.workerId = workerId; this.datacenterId = datacenterId; LOG.info(String.format("worker Starting. timestamp décalage gauche %d, bits d'identifiant de centre de données %d, bits d'identifiant de travailleur %d, bits de séquence %d, identifiant de travailleur %d", timestampLeftShift, datacenterIdBits, workerIdBits, séquenceBits, workerId)); } public synchronisé long nextId() { long timestamp = timeGen(); if (timestamp < lastTimestamp) { LOG.error(String.format("l'horloge recule. Rejet des requêtes jusqu'à %d.", lastTimestamp)); throw new RuntimeException(String.format("L'horloge a reculé. Refus de générer un identifiant pendant %d millisecondes", lastTimestamp - timestamp)); } if (lastTimestamp == timestamp) { séquence = (séquence 1) & sequenceMask; if (séquence == 0) { timestamp = tilNextMillis(lastTimestamp); } } autre { séquence = 0L; } lastTimestamp = timestamp; tour ((horodatage - twepoch) << timestampLeftShift) | (datacenterId << datacenterIdShift) | (workerId << workerIdShift) | séquence ; } protégé long tilNextMillis(long lastTimestamp) { long timestamp = timeGen(); while (timestamp <= lastTimestamp) {
timestamp = timeGen();
}
return timestamp;
}
protected long timeGen() {
return System.currentTimeMillis();
}
}
测试生成ID的测试类，IdWorkerTest.java：
package com.demo.elk;
import java.util.HashSet;
import java.util.Set;
public class IdWorkerTest {
static class IdWorkThread implements Runnable {
private Set set;
private IdWorker idWorker;
public IdWorkThread(Set set, IdWorker idWorker) {
this.set = set;
this.idWorker = idWorker;
}
public void run() {
while (true) {
long id = idWorker.nextId();
System.out.println(" real id:" id);
if (!set.add(id)) {
System.out.println("duplicate:" id);
}
}
}
}
public static void main(String[] args) {
Set set = new HashSet();
final IdWorker idWorker1 = new IdWorker(0, 0);
final IdWorker idWorker2 = new IdWorker(1, 0);
Thread t1 = new Thread(new IdWorkThread(set, idWorker1));
Thread t2 = new Thread(new IdWorkThread(set, idWorker2));
t1.setDaemon(true);
t2.setDaemon(true);
t1.start();
t2.start();
try {
Thread.sleep(30000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
package com.demo.elk; importer java.util.HashSet;importer java.util. Set; classe publique IdWorkerTest { classe statique IdWorkThread implémente Runnable { private Set set; private IdWorker idWorker; public IdWorkThread(Set set, IdWorker idWorker) { this.set = set; this.idWorker = idWorker; } public void run() { while (true) > long id = idWorker.nextId(); System.out.println(" identifiant réel :" if (!set.add(id)) { System.out.println("duplicate:" id); } } } } public static void main(String[] args) { Set set = new HashSet(); final IdWorker idWorker1 = new IdWorker(0, 0); final IdWorker idWorker2 = new IdWorker(1, 0); Thread t1 = nouveau Thread(new IdWorkThread(set, idWorker1)); Thread t2 = nouveau Thread(new IdWorkThread(set, idWorker2)); t1.setDaemon(true) ; t2.setDaemon(true); t1.start(); t2.start(); essayez { Thread.sleep(30000); } catch (InterruptedException e) { e.printStackTrace(); } }}
7, Résumé
(1) Instance unique ou groupe de nœuds uniques :
Après des tests de table autonomes de 500 W et 1 000 W, l'ID à incrémentation automatique est meilleur que l'UUID Les performances de la clé primaire d'ID incrémentielle sont supérieures à celles de l'UUID et le coût du stockage sur disque est la moitié de celui de l'UUID. Par conséquent, sur une seule instance ou un seul groupe de nœuds, utilise l'ID auto-incrémenté comme clé primaire préférée.
(2) Scénario d'architecture distribuée :
Les scénarios distribués à petite échelle sous le groupe de 20 nœuds Afin de réaliser rapidement un déploiement, la clé primaire UUID peut être rapidement déployée avec des coûts de stockage multi-fleurs et en sacrifiant une partie des performances ; >
Pour les scénarios distribués à moyenne échelle avec 20 à 200 groupes de nœuds, une solution plus rapide d'étapes d'identification auto-croissantes peut être utilisée. Les scénarios distribués sous Big Data avec plus de 200 groupes de nœuds peuvent apprendre de l'ID global à incrémentation automatique construit par l'algorithme Twitter Snowflake comme clé primaire. Ce qui précède est le processus détaillé de comparaison des avantages et des inconvénients de l'utilisation de la clé primaire ID à incrémentation automatique et de l'UUID comme clé primaire dans MySQL (tests de millions à des dizaines de millions d'enregistrements de table). Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
