?
測試緣由
?
一個開發(fā)同事做了一個框架，里面主鍵是uuid，我跟他建議說mysql不要用uuid用自增主鍵，自增主鍵效率高，他說不一定高，我說innodb的索引特性導(dǎo)致了自增id做主鍵是效率最好的，為了拿實際的案例來說服他，所以準(zhǔn)備做一個詳細(xì)的測試。
?
作為互聯(lián)網(wǎng)公司，一定有用戶表，而且用戶表UC_USER基本會有百萬記錄，所以在這個表基礎(chǔ)上準(zhǔn)測試數(shù)據(jù)來進(jìn)行測試。
?
???????? 測試過程是目前我想到的多方位的常用的幾種類型的sql進(jìn)行測試，當(dāng)然可能不太完善，歡迎大家留言提出更加完善的測試方案或者測試sql語句。
?
?
?
1、準(zhǔn)備表以及數(shù)據(jù)
UC_USER，自增ID為主鍵，表結(jié)構(gòu)類似如下：
|
CREATE?TABLE?`UC_USER`?( |
?
?
?
UC_USER_PK_VARCHAR表，字符串ID為主鍵，采用uuid
|
CREATE?TABLE?`UC_USER_PK_VARCHAR_1`?( |
?
?
?
?
2、500W數(shù)據(jù)測試
2.1 錄入500W數(shù)據(jù)，自增ID節(jié)省一半磁盤空間
確定兩個表數(shù)據(jù)量
|
# 自增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) |
?
占據(jù)的空間容量來看，自增ID比UUID小一半左右。
|
主鍵類型 |
數(shù)據(jù)文件大小 |
占據(jù)容量 |
|
自增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 |
?
?
?
2.2 單個數(shù)據(jù)走索引查詢，自增id和uuid相差不大
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
自增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 |
?
?
?
2.3 范圍like查詢，自增ID性能優(yōu)于UUID
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? （1）模糊范圍查詢1000條數(shù)據(jù)，自增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條數(shù)據(jù)，自增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條數(shù)據(jù)，自增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 |
|
? 范圍查詢總數(shù)量，自增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 |
?
?
?
PS：在有緩存的情況下，兩者執(zhí)行效率沒有相差很小。
?
?
?
2.4 寫入測試，自增ID是UUID的4倍
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? ? |
? ? |
? |
|
自增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` ? |
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` |
5.639 |
|
? |
? |
? |
|
自增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,??? 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.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、備份和恢復(fù)，自增ID性能優(yōu)于UUID
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? 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恢復(fù) |
||
|
自增ID |
time mysql? -utim -ptimgood -h192.168.121.63? test |
7m36.601s |
|
UUID |
time mysql? -utim -ptimgood -h192.168.121.63? test |
9m42.472s |
|
? |
? |
? |
?
?
?
?
3、500W總結(jié)
在500W記錄表的測試下：
（1）??????普通單條或者20條左右的記錄檢索，uuid為主鍵的相差不大幾乎效率相同；
（2）??????但是范圍查詢特別是上百成千條的記錄查詢，自增id的效率要大于uuid；
（3）??????在范圍查詢做統(tǒng)計匯總的時候，自增id的效率要大于uuid；
（4）??????在存儲上面，自增id所占的存儲空間是uuid的1/2；
（5）??????在備份恢復(fù)上，自增ID主鍵稍微優(yōu)于UUID。
?
?
?
4、1000W數(shù)據(jù)測試
4.1 錄入1000W數(shù)據(jù)記錄，看存儲空間
|
# 自增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> |
?
?
占據(jù)的空間容量來看，自增ID比UUID小一半左右：
|
主鍵類型 |
數(shù)據(jù)文件大小 |
占據(jù)容量? |
|
自增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 單個數(shù)據(jù)走索引查詢，自增id和 uuid效率比是：(2~3):1
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? 單條記錄查詢 |
||
|
自增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 |
|
? 根據(jù)日期查詢 |
||
|
自增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 范圍like查詢，自增ID性能優(yōu)于UUID，比值(1.5~2)：1
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? （1）模糊范圍查詢1000條數(shù)據(jù)，自增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條數(shù)據(jù)，自增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條數(shù)據(jù)，自增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 |
|
? 范圍查詢總數(shù)量，自增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 |
?
?
?
4.4 寫入測試，自增ID比UUID效率高，比值(3~10)：1
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? ? 修改一天的記錄 |
||
|
自增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` |
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` |
26.521 |
|
? 錄入數(shù)據(jù) |
||
|
自增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`,?? ?`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,??? 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.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、備份和恢復(fù)，自增ID性能優(yōu)于UUID
|
主鍵類型 |
SQL語句 |
執(zhí)行時間 (秒) |
|
? 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恢復(fù) |
||
|
自增ID |
time mysql -utim -ptimgood ?-h192.168.121.63 test |
17m30.822s |
|
UUID |
time mysql -utim -ptimgood ?-h192.168.121.63 test |
23m6.360s |
|
? |
? |
? |
?
?
?
5、1000W總結(jié)
在1000W記錄表的測試下：
（1）普通單條或者20條左右的記錄檢索，自增主鍵效率是uuid主鍵的2到3倍；
（2）但是范圍查詢特別是上百成千條的記錄查詢，自增id的效率要大于uuid；
（3）在范圍查詢做統(tǒng)計匯總的時候，自增id主鍵的效率是uuid主鍵1.5到2倍；
（4）在存儲上面，自增id所占的存儲空間是uuid的1/2；
（5）在寫入上面，自增ID主鍵的效率是UUID主鍵的3到10倍，相差比較明顯，特別是update小范圍之內(nèi)的數(shù)據(jù)上面。
（6）在備份恢復(fù)上，自增ID主鍵稍微優(yōu)于UUID。
?
?
?
6、MySQL分布式架構(gòu)的取舍
分布式架構(gòu)，意味著需要多個實例中保持一個表的主鍵的唯一性。這個時候普通的單表自增ID主鍵就不太合適，因為多個mysql實例上會遇到主鍵全局唯一性問題。
?
?
?
6.1、自增ID主鍵+步長，適合中等規(guī)模的分布式場景
?
???????? 在每個集群節(jié)點組的master上面，設(shè)置（auto_increment_increment），讓目前每個集群的起始點錯開 1，步長選擇大于將來基本不可能達(dá)到的切分集群數(shù)，達(dá)到將 ID 相對分段的效果來滿足全局唯一的效果。
?
優(yōu)點是：實現(xiàn)簡單，后期維護(hù)簡單，對應(yīng)用透明。
?
缺點是：第一次設(shè)置相對較為復(fù)雜，因為要針對未來業(yè)務(wù)的發(fā)展而計算好足夠的步長;
?
規(guī)劃：
比如計劃總共N個節(jié)點組，那么第i個節(jié)點組的my.cnf的配置為：
auto_increment_offset? i
auto_increment_increment ?N
?
假如規(guī)劃48個節(jié)點組，N為48，現(xiàn)在配置第8個節(jié)點組，這個i為8，第8個節(jié)點組的my.cnf里面的配置為：
auto_increment_offset? 8
auto_increment_increment? 48
?
?
?
6.2、UUID，適合小規(guī)模的分布式環(huán)境
???????? 對于InnoDB這種聚集主鍵類型的引擎來說，數(shù)據(jù)會按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會產(chǎn)生巨大的IO壓力，而且由于索引和數(shù)據(jù)存儲在一起，字符串做主鍵會造成存儲空間增大一倍。
?
在存儲和檢索的時候，innodb會對主鍵進(jìn)行物理排序，這對auto_increment_int是個好消息，因為后一次插入的主鍵位置總是在最后。但是對uuid來說，這卻是個壞消息，因為uuid是雜亂無章的，每次插入的主鍵位置是不確定的，可能在開頭，也可能在中間，在進(jìn)行主鍵物理排序的時候，勢必會造成大量的 IO操作影響效率，在數(shù)據(jù)量不停增長的時候，特別是數(shù)據(jù)量上了千萬記錄的時候，讀寫性能下降的非常厲害。
?
優(yōu)點：搭建比較簡單，不需要為主鍵唯一性的處理。
缺點：占用兩倍的存儲空間（在云上光存儲一塊就要多花2倍的錢），后期讀寫性能下降厲害。
?
?
?
?
6.3、雪花算法自造全局自增ID，適合大數(shù)據(jù)環(huán)境的分布式場景
由twitter公布的開源的分布式id算法snowflake（Java版本）
?
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 ??? ?private long maxDatacenterId = -1L ^ (-1L ??? ?private long sequenceBits = 12L; ? ??? ?private long workerIdShift = sequenceBits; ??? ?private long datacenterIdShift = sequenceBits + workerIdBits; ??? ?private long timestampLeftShift = sequenceBits + workerIdBits + ?datacenterIdBits; ??? ?private long sequenceMask = -1L ^ (-1L ? ??? ?private long lastTimestamp = -1L; ? ??? ?public IdWorker(long workerId, long datacenterId) { ??????? ?// sanity check for workerId ??????? ?if (workerId > maxWorkerId || workerId ??????????? throw new ?IllegalArgumentException(String.format(“worker Id can’t be greater than ?%d or less than 0”, maxWorkerId)); ??????? ?} ??????? ?if (datacenterId > maxDatacenterId || datacenterId ?????????? ??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 ??????????? ?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) ? ??} ? ??? ?protected long tilNextMillis(long lastTimestamp) { ??????? ?long timestamp = timeGen(); ??????? ?while (timestamp ??????????? 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 ??????? ?private IdWorker idWorker; ? ??????? ?public IdWorkThread(Set ??????????? 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 ??????? ?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，總結(jié)
（1）單實例或者單節(jié)點組：
經(jīng)過500W、1000W的單機(jī)表測試，自增ID相對UUID來說，自增ID主鍵性能高于UUID，磁盤存儲費用比UUID節(jié)省一半的錢。所以在單實例上或者單節(jié)點組上，使用自增ID作為首選主鍵。
?
（2）分布式架構(gòu)場景：
???????? 20個節(jié)點組下的小型規(guī)模的分布式場景，為了快速實現(xiàn)部署，可以采用多花存儲費用、犧牲部分性能而使用UUID主鍵快速部署；
?
???????? 20到200個節(jié)點組的中等規(guī)模的分布式場景，可以采用自增ID+步長的較快速方案。
?
???????? 200以上節(jié)點組的大數(shù)據(jù)下的分布式場景，可以借鑒類似twitter雪花算法構(gòu)造的全局自增ID作為主鍵。
?
?
?以上就是MySQL 使用自增ID主鍵和UUID 作為主鍵的優(yōu)劣比較詳細(xì)過程（從百萬到千萬表記錄測試）的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！
?
.png)
