测试缘由
一个开发同事做了一个框架,里面主键是uuid,我跟他建议说mysql不要用uuid用自增主键,自增主键效率高,他说不一定高,我说innodb的索引特性导致了自增id做主键是效率最好的,为了说服他,所以准备做一个详细的测试。
作为互联网公司,一定有用户表,而且用户表UC_USER基本会有百万记录,所以在这个表基础上准测试数据来进行测试。
大概环境是:Centos6.5、MySQL5.6.12
UC_USER,自增ID为主键:
CREATE TABLE `UC_USER` ( |
UC_USER_PK_VARCHAR表,字符串ID为主键,采用uuid
CREATE TABLE `UC_USER_PK_VARCHAR_1` ( |
确定两个表数据量
# 自增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 |
主键类型 |
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 |
主键类型 |
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:在有缓存的情况下,两者执行效率没有相差很小。
主键类型 |
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` <'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 |
|
|
|
自增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 |
在500W记录表的测试下:
(1) 普通单条或者20条左右的记录检索,uuid为主键的相差不大几乎效率相同;
(2) 但是范围查询特别是上百成千条的记录查询,自增id的效率要大于uuid;
(3) 在范围查询做统计汇总的时候,自增id的效率要大于uuid;
(4) 在存储上面,自增id所占的存储空间是uuid的1/2;
以上就是MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程(500W单表)的内容,更多相关内容请关注PHP中文网(www.php.cn)!