MySQL 使用自增ID主鍵和UUID 作為主鍵的優劣比較詳細過程（500W單表）

4214

?

?

測試緣由

?

一個開發同事做了一個框架，里面主鍵是uuid，我跟他建議說mysql不要用uuid用自增主鍵，自增主鍵效率高，他說不一定高，我說innodb的索引特性導致了自增id做主鍵是效率最好的，為了說服他，所以準備做一個詳細的測試。

?

作為互聯網公司，一定有用戶表，而且用戶表UC_USER基本會有百萬記錄，所以在這個表基礎上準測試數據來進行測試。

?

? 大概環境是：Centos6.5、MySQL5.6.12

?

?

1、準備表以及數據

UC_USER，自增ID為主鍵：

CREATE?TABLE?`UC_USER`?(
???`ID`?bigint(20)?NOT?NULL?AUTO_INCREMENT?COMMENT?‘主鍵‘,
???`USER_NAME`?varchar(100)?DEFAULT?NULL?COMMENT?‘用戶名’,
???`USER_PWD`?varchar(200)?DEFAULT?NULL?COMMENT?‘密碼’,
???`BIRTHDAY`?datetime?DEFAULT?NULL?COMMENT?‘生日’,
???`NAME`?varchar(200)?DEFAULT?NULL?COMMENT?‘姓名’,
???`USER_ICON`?varchar(500)?DEFAULT?NULL?COMMENT?‘頭像圖片’,
???`SEX`?char(1)?DEFAULT?NULL?COMMENT?‘性別,?1:男，2:女，3：保密’,
???`NICKNAME`?varchar(200)?DEFAULT?NULL?COMMENT?‘昵稱’,
???`STAT`?varchar(10)?DEFAULT?NULL?COMMENT?‘用戶狀態，01:正常，02:凍結’,
???`USER_MALL`?bigint(20)?DEFAULT?NULL?COMMENT?‘當前所屬MALL’,
???`LAST_LOGIN_DATE`?datetime?DEFAULT?NULL?COMMENT?‘最后登錄時間’,
???`LAST_LOGIN_IP`?varchar(100)?DEFAULT?NULL?COMMENT?‘最后登錄IP’,
???`SRC_OPEN_USER_ID`?bigint(20)?DEFAULT?NULL?COMMENT?‘來源的聯合登錄’,
???`EMAIL`?varchar(200)?DEFAULT?NULL?COMMENT?‘郵箱’,
???`MOBILE`?varchar(50)?DEFAULT?NULL?COMMENT?‘手機’,
???`IS_DEL`?char(1)?DEFAULT?‘0’?COMMENT?‘是否刪除’,
???`IS_EMAIL_CONFIRMED`?char(1)?DEFAULT?‘0’?COMMENT?‘是否綁定郵箱’,
???`IS_PHONE_CONFIRMED`?char(1)?DEFAULT?‘0’?COMMENT?‘是否綁定手機’,
???`CREATER`?bigint(20)?DEFAULT?NULL?COMMENT?‘創建人’,
???`CREATE_DATE`?datetime?DEFAULT?CURRENT_timestamp?COMMENT?‘注冊時間’,
???`UPDATE_DATE`?datetime?DEFAULT?CURRENT_TIMESTAMP?COMMENT?‘修改日期’,
???`PWD_INTENSITY`?char(1)?DEFAULT?NULL?COMMENT?‘密碼強度’,
???`MOBILE_TGC`?char(64)?DEFAULT?NULL?COMMENT?‘手機登錄標識’,
???`MAC`?char(64)?DEFAULT?NULL?COMMENT?‘mac地址’,
???`SOURCE`?char(1)?DEFAULT?‘0’?COMMENT?‘1:WEB,2:IOS,3:ANDROID,4:WIFI,5:管理系統,?0:未知’,
???`ACTIVATE`?char(1)?DEFAULT?‘1’?COMMENT?‘激活，1：激活，0：未激活’,
???`ACTIVATE_TYPE`?char(1)?DEFAULT?‘0’?COMMENT?‘激活類型，0：自動，1：手動’,
???PRIMARY?KEY?(`ID`),
???UNIQUE?KEY?`USER_NAME`?(`USER_NAME`),
???KEY?`MOBILE`?(`MOBILE`),
???KEY?`IDX_MOBILE_TGC`?(`MOBILE_TGC`,`ID`),
???KEY?`IDX_EMAIL`?(`EMAIL`,`ID`),
???KEY?`IDX_CREATE_DATE`?(`CREATE_DATE`,`ID`),
???KEY?`IDX_UPDATE_DATE`?(`UPDATE_DATE`)
?)?ENGINE=InnoDB?AUTO_INCREMENT=7122681?DEFAULT?CHARSET=utf8?COMMENT=’用戶表’

?

?

?

UC_USER_PK_VARCHAR表，字符串ID為主鍵，采用uuid

CREATE?TABLE?`UC_USER_PK_VARCHAR_1`?(
??`ID`?varchar(36)?CHARACTER?SET?utf8mb4?NOT?NULL?DEFAULT?‘0’?COMMENT?‘主鍵‘,
???`USER_NAME`?varchar(100)?DEFAULT?NULL?COMMENT?‘用戶名’,
???`USER_PWD`?varchar(200)?DEFAULT?NULL?COMMENT?‘密碼’,
???`BIRTHDAY`?datetime?DEFAULT?NULL?COMMENT?‘生日’,
???`NAME`?varchar(200)?DEFAULT?NULL?COMMENT?‘姓名’,
???`USER_ICON`?varchar(500)?DEFAULT?NULL?COMMENT?‘頭像圖片’,
???`SEX`?char(1)?DEFAULT?NULL?COMMENT?‘性別,?1:男，2:女，3：保密’,
???`NICKNAME`?varchar(200)?DEFAULT?NULL?COMMENT?‘昵稱’,
???`STAT`?varchar(10)?DEFAULT?NULL?COMMENT?‘用戶狀態，01:正常，02:凍結’,
???`USER_MALL`?bigint(20)?DEFAULT?NULL?COMMENT?‘當前所屬MALL’,
???`LAST_LOGIN_DATE`?datetime?DEFAULT?NULL?COMMENT?‘最后登錄時間’,
???`LAST_LOGIN_IP`?varchar(100)?DEFAULT?NULL?COMMENT?‘最后登錄IP’,
???`SRC_OPEN_USER_ID`?bigint(20)?DEFAULT?NULL?COMMENT?‘來源的聯合登錄’,
???`EMAIL`?varchar(200)?DEFAULT?NULL?COMMENT?‘郵箱’,
???`MOBILE`?varchar(50)?DEFAULT?NULL?COMMENT?‘手機’,
???`IS_DEL`?char(1)?DEFAULT?‘0’?COMMENT?‘是否刪除’,
???`IS_EMAIL_CONFIRMED`?char(1)?DEFAULT?‘0’?COMMENT?‘是否綁定郵箱’,
???`IS_PHONE_CONFIRMED`?char(1)?DEFAULT?‘0’?COMMENT?‘是否綁定手機’,
???`CREATER`?bigint(20)?DEFAULT?NULL?COMMENT?‘創建人’,
???`CREATE_DATE`?datetime?DEFAULT?CURRENT_TIMESTAMP?COMMENT?‘注冊時間’,
???`UPDATE_DATE`?datetime?DEFAULT?CURRENT_TIMESTAMP?COMMENT?‘修改日期’,
???`PWD_INTENSITY`?char(1)?DEFAULT?NULL?COMMENT?‘密碼強度’,
???`MOBILE_TGC`?char(64)?DEFAULT?NULL?COMMENT?‘手機登錄標識’,
???`MAC`?char(64)?DEFAULT?NULL?COMMENT?‘mac地址’,
???`SOURCE`?char(1)?DEFAULT?‘0’?COMMENT?‘1:WEB,2:IOS,3:ANDROID,4:WIFI,5:管理系統,?0:未知’,
???`ACTIVATE`?char(1)?DEFAULT?‘1’?COMMENT?‘激活，1：激活，0：未激活’,
???`ACTIVATE_TYPE`?char(1)?DEFAULT?‘0’?COMMENT?‘激活類型，0：自動，1：手動’,
???PRIMARY?KEY?(`ID`),
???UNIQUE?KEY?`USER_NAME`?(`USER_NAME`),
???KEY?`MOBILE`?(`MOBILE`),
???KEY?`IDX_MOBILE_TGC`?(`MOBILE_TGC`,`ID`),
???KEY?`IDX_EMAIL`?(`EMAIL`,`ID`),
???KEY?`IDX_CREATE_DATE`?(`CREATE_DATE`,`ID`),
???KEY?`IDX_UPDATE_DATE`?(`UPDATE_DATE`)
?)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COMMENT=’用戶表’;

?

?

?

?

2、500W數據測試

2.1 錄入500W數據，自增ID節省一半磁盤空間

確定兩個表數據量

# 自增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)

?

占據的空間容量來看，自增ID比UUID小一半左右。

主鍵類型

數據文件大小

占據容量

自增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 單個數據走索引查詢，自增id和uuid相差不大

主鍵類型

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

?

?

?

2.3 范圍like查詢，自增ID性能優于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

?

?

?

PS：在有緩存的情況下，兩者執行效率沒有相差很小。

?

?

?

2.4 寫入測試，自增ID是UUID的4倍

主鍵類型

SQL語句

執行時間 (秒)

?

?

?

?

?

自增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

?

?

?

?

3、總結

在500W記錄表的測試下：

（1）??????普通單條或者20條左右的記錄檢索，uuid為主鍵的相差不大幾乎效率相同；

（2）??????但是范圍查詢特別是上百成千條的記錄查詢，自增id的效率要大于uuid；

（3）??????在范圍查詢做統計匯總的時候，自增id的效率要大于uuid；

（4）??????在存儲上面，自增id所占的存儲空間是uuid的1/2；

?

?以上就是MySQL 使用自增ID主鍵和UUID 作為主鍵的優劣比較詳細過程（500W單表）的內容，更多相關內容請關注PHP中文網（www.php.cn）！

? 版權聲明
THE END
數據庫
# timestamp
喜歡就支持一下吧
點贊14 分享