| 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: In the presence of cache, there is no small difference in execution efficiency between the two.
2.4 Write test, auto-increment ID is 4 times
##Primary key type
SQL statement |
Execution Time (seconds) |
|
|
|
|
#Auto-increment ID |
##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 | | | | | Increase 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`, `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 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`)), `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.424 | 2.5、备份和恢复，自增ID性能优于UUID主键类型 | 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 | | | |
3、500WSummaryUnder the test of 500W record table: (1) For ordinary single or about 20 record retrieval, the uuid is the primary key. The efficiency is almost the same; (2) But for range queries, especially for hundreds or thousands of records, the efficiency of auto-incrementing ids is greater than uuid; (3) In range queries When statistics are summarized, the efficiency of auto-incrementing id is greater than that of uuid; (4) In terms of storage, the storage space occupied by auto-incrementing id is 1/2 of uuid; (5 ) In terms of backup and recovery, the auto-incrementing ID primary key is slightly better than UUID. 4、1000WData test 4.1 Enter1000WData record, check the storage space Auto-incrementidTable with primary key mysql> use test;
Database changed
mysql> select count(1) from UC_USER_1;
+----------+
| count(1) |
+----------+
| 10698102 |
+--------- -+
1 row in set (27.42 sec)
# uuidTable with primary key
mysql> select count(1) from UC_USER_PK_VARCHAR_1;
+----------+
| count(1) |
+- ---------+
| 10698102 |
+----------+
1 row in set (0.00 sec )
mysql>
|
##occupied In terms of space capacity, auto-increment ID is about half smaller than UUID:
| ##Primary key type
|
Data file size
|
Occupied capacity
|
Auto-increment 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 |
4.2 Single data is indexed and incrementedid and uuidThe efficiency ratio is: (2~3):1
Primary key type |
##SQL statement |
Execution time ( Seconds) |
|
##Single record query
|
Auto-increment 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
|
| #Small range query
|
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 |
|
Query based on date |
|
Auto-increment 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 |
|
4.3 RangelikeQuery, self-incrementID performance is better than UUID, ratio(1.5~2):1
##Primary key type |
SQL Statement |
Execution time (seconds) |
| (1) Fuzzy range query 1000 pieces of data, auto-increment ID performance is better than UUID
|
| Auto-increment 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) Date range query 20 pieces of data, the auto-increment ID is slightly weaker than UUID
|
| Auto-increment 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) Range query 200 pieces of data, auto-increment ID performance is better than UUID
|
Auto-increment 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 |
|
| Range query total quantity, auto-increment ID is better than UUID
| Auto-increment 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 |
4.4 Write test, auto-increment ID More efficient than UUID, ratio(3~10):1
Primary key type |
##SQL statement |
Execution time (seconds) |
|
##Modify one day’s record
|
Increase 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' WHERE t.`CREATE_DATE` > '2016-05-03 10:26:36' AND t.`CREATE_DATE` <'2016- 05-04 00:00:00' ; | ##26.521 | | ##Enter data| Increase ID | | INSERT INTO test.`UC_USER_1`( 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`, `MO BILE`, `IS_DEL `, `IS_EMAIL_CONFIRMED`, `IS_PHONE_CONFIRMED`, `CREATER`, `CREATE_DATE`, `UPDATE_DATE`, `PWD_INTENSITY`, `MOBILE_TGC`, `MAC`, `SOURCE`, `ACTIVATE`, `ACTIVATE _TYPE` ) SELECT NULL, CONCAT ; `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.534 | UUID | INSERT INTO test.`UC_USER_PK_VARCHAR_1`( 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 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`)), `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; | 1.716 | 4.5、备份和恢复，自增ID性能优于UUID主键类型 | SQL语句 | 执行时间 (秒) | Mysqldump备份 | 自增ID | time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_1> UC_USER_1.sql | 0m50.548s |
UUID |
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_PK_VARCHAR_1> UC_USER_PK_VARCHAR_1.sql |
0m58.590s |
|
MySQL恢复
|
自增ID |
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_1.sql | 17m30.822s | UUID | time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_1.sql | 23m6.360s | | | |
5、1000WSummaryUnder the test of 1000W record table: (1) For ordinary single or about 20 record retrieval, the efficiency of auto-increasing primary key is 2 to 3 times that of uuid primary key ; (2) However, for range queries, especially for hundreds or thousands of records, the efficiency of auto-incrementing ids is greater than that of uuid; (3) When doing statistical summary for range queries , the efficiency of the auto-incrementing id primary key is 1.5 to 2 times that of the uuid primary key; (4) In terms of storage, the storage space occupied by the auto-incrementing id is 1/2 of uuid; ( 5) In terms of writing, the efficiency of the auto-incrementing ID primary key is 3 to 10 times that of the UUID primary key. The difference is obvious, especially when updating data within a small range. (6) In terms of backup and recovery, the auto-incrementing ID primary key is slightly better than UUID. 6、MySQLDistributed Architecture trade-offsDistributed architecture means that the uniqueness of the primary key of a table needs to be maintained in multiple instances. At this time, the ordinary single-table self-increasing ID primary key is not suitable, because multiple mysql instances will encounter the problem of global uniqueness of the primary key. ##6.1、Auto-incrementID Primary key+ step size, suitable for medium-scale distributed scenarios On the master of each cluster node group, set (auto_increment_increment), stagger the starting point of each cluster by 1, and choose a step size larger than the number of split clusters that is basically impossible to achieve in the future, so as to achieve the effect of relatively segmenting the ID to meet the global unique effect. The advantages are: simple implementation, simple post-maintenance, and transparent to the application. The disadvantage is: the first setup is relatively complicated, because sufficient step length needs to be calculated for future business development; Planning: For example, if a total of N node groups are planned, then the i-th node group The configuration of my.cnf is: auto_increment_offset iauto_increment_increment N If you plan 48 node groups, N is 48, and now configure the 8th Node group, this i is 8, the configuration in my.cnf of the 8th node group is: auto_increment_offset 8auto_increment_increment 48 6.2、UUID, suitable for small-scale distributed environment For Innodb engine of the main key type, the data will be sorted according to the main key. Due to the disorder of UUID, InnoDB will produce huge IO pressure, and because the indexes and data are stored together, the string is made by the string, and the string is made by the string. The primary key will double the storage space. During storage and retrieval, innodb will physically sort the primary keys, which is good news for auto_increment_int, because the position of the primary key inserted later is always at the end. But for uuid, this is bad news, because uuid is messy, and the position of the primary key inserted each time is uncertain. It may be at the beginning or in the middle. When performing physical sorting of primary keys, it will inevitably cause a large number of IO operations affect efficiency. When the amount of data continues to grow, especially when the amount of data exceeds tens of millions of records, the read and write performance drops dramatically. Advantages: The construction is relatively simple and does not require the uniqueness of the primary key. Disadvantages: takes up twice the storage space (it will cost 2 times more to store a piece of light on the cloud), and later Reading and writing performance drops drastically. 6.3, Snowflake algorithm creates global self-made Added ID, suitable for distributed scenarios in big data environmentsThe open source distributed ID algorithm snowflake (Java version) announced by twitter IdWorker.java： ##package com.demo.elk; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class IdWorker { protected static final Logger LOG = LoggerFactory.getLogger(IdWorker.class); private long workerId; private long datacenterId; private long sequence = 0L; private long twepoch = 1288834974657L; private long workerIdBits = 5L; private long datacenterIdBits = 5L; private long maxWorkerId = -1L ^ (-1L << workerIdBits); private long maxDatacenterId = -1L ^ (-1L << datacenterIdBits); private long sequenceBits = 12L; private long workerIdShift = sequenceBits; private long datacenterIdShift = sequenceBits + workerIdBits; private long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits; private long sequenceMask = -1L ^ (-1L << sequenceBits); private long lastTimestamp = -1L; public IdWorker(long workerId, long datacenterId) { // sanity check for workerId if (workerId > maxWorkerId || workerId < 0) { throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId)); } if (datacenterId > maxDatacenterId || datacenterId < 0) { throw new IllegalArgumentException(String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId)); } this.workerId = workerId; this.datacenterId = datacenterId; LOG.info(String.format("worker starting. timestamp left shift %d, datacenter id bits %d, worker id bits %d, sequence bits %d, workerid %d", timestampLeftShift, datacenterIdBits, workerIdBits, sequenceBits, workerId)); } public synchronized long nextId() { long timestamp = timeGen(); if (timestamp < lastTimestamp) { LOG.error(String.format("clock is moving backwards. Rejecting requests until %d.", lastTimestamp)); throw new RuntimeException(String.format("Clock moved backwards. Refusing to generate id for %d milliseconds", lastTimestamp - timestamp)); } if (lastTimestamp == timestamp) { sequence = (sequence + 1) & sequenceMask; if (sequence == 0) { timestamp = tilNextMillis(lastTimestamp); } } else { sequence = 0L; } lastTimestamp = timestamp; return ((timestamp - twepoch) << timestampLeftShift) | (datacenterId << datacenterIdShift) | (workerId << workerIdShift) | sequence; } protected 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();
}
}
}
|
7，Summary
（1) Single instance or single node group:
After 500W and 1000W stand-alone table testing, compared with UUID, auto-increment ID is The performance of incremental ID primary key is higher than that of UUID, and the disk storage cost is half of that of UUID. Therefore, on a single instance or a single node group, use the auto-incrementing ID as the preferred primary key.
（2）Distributed architecture scenario: 20 distributed scenarios under the node group, in order to quickly achieve deployment, you can use multi -flower storage costs and sacrifice some performance to use the UUID main key to quickly deploy; For medium-scale distributed scenarios with 20 to 200 node groups, a faster solution of self-increasing ID + step size can be used.
# The distributed scenario under the big data of the node group above the node group, you can learn from the global self -increase ID of the Twitter Snowflake algorithm structure as the primary key.
The above is the detailed process of comparing the advantages and disadvantages of using auto-incrementing ID primary key and UUID as the primary key in MySQL (testing from millions to tens of millions of table records). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!
|
|
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
