Detailed process of comparing the advantages and disadvantages of MySQL using auto-incrementing ID primary key and UUID as primary key (500W single table)-Mysql Tutorial-php.cn

Reason for testing

A development colleague made a framework in which the primary key is uuid. I suggested to him that mysql should not use uuid and use auto-incrementing primary keys. Auto-incrementing primary keys are more efficient. He said it is not necessarily high. I said innodb’s index The feature has led to the most efficient use of auto-incrementing ID as the primary key. In order to convince him, I prepared to do a detailed test.

As an Internet company, there must be a user table, and the user table UC_USER basically has millions of records. Therefore, the test is carried out based on the quasi-test data based on this table.

The approximate environment is: Centos6.5, MySQL5.6.12

1, Prepare tables and data

UC_USER, auto-increment ID as primary key:

UC_USER_PK_VARCHAR table, string ID is the primary key, using uuid

##CREATE TABLE `UC_USER` (

`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key', `USER_NAME` varchar(100) DEFAULT NULL COMMENT 'Username',
`USER_PWD` varchar(200) DEFAULT NULL COMMENT 'Password',
`BIRTHDAY` datetime DEFAULT NULL COMMENT 'Birthday',
`NAME` varchar(200) DEFAULT NULL COMMENT 'Name',
`USER_ICON` varchar(500) DEFAULT NULL COMMENT 'Avatar picture',
`SEX` char(1 ) DEFAULT NULL COMMENT 'Gender, 1: Male, 2: Female, 3: Confidential',
`NICKNAME` varchar(200) DEFAULT NULL COMMENT 'Nickname',
`STAT` varchar(10) DEFAULT NULL COMMENT 'User status, 01: normal, 02: frozen',
`USER_MALL` bigint(20) DEFAULT NULL COMMENT 'Current MALL',
`LAST_LOGIN_DATE` datetime DEFAULT NULL COMMENT 'Last login time',
`LAST_LOGIN_IP` varchar(100) DEFAULT NULL COMMENT 'Last login IP',
`SRC_OPEN_USER_ID` bigint(20) DEFAULT NULL COMMENT 'Source of joint login',
`EMAIL` varchar(200) DEFAULT NULL COMMENT 'Mailbox',
`MOBILE` varchar(50) DEFAULT NULL COMMENT 'Mobile phone',
`IS_DEL` char(1) DEFAULT '0' COMMENT 'Whether to delete',
`IS_EMAIL_CONFIRMED` char(1 ) DEFAULT '0' COMMENT 'Whether to bind an email',
`IS_PHONE_CONFIRMED` char(1) DEFAULT '0' COMMENT 'Whether to bind a mobile phone',
`CREATER` bigint(20) DEFAULT NULL COMMENT 'Create People',
`CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Registration time',
`UPDATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Modification date',
`PWD_INTENSITY` char(1) DEFAULT NULL COMMENT 'Password strength' ,
`MOBILE_TGC` char(64) DEFAULT NULL COMMENT 'Mobile phone login ID',
`MAC` char(64) DEFAULT NULL COMMENT 'mac address',
`SOURCE` char(1) DEFAULT ' 0' COMMENT '1:WEB,2:IOS,3:ANDROID,4:WIFI,5:Management system, 0:Unknown',
`ACTIVATE` char(1) DEFAULT '1' COMMENT 'Activate, 1: Activation, 0: Not activated',
`ACTIVATE_TYPE` char(1) DEFAULT '0' COMMENT 'Activation type, 0: automatic, 1: manual',
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='User Table'

CREATE TABLE `UC_USER_PK_VARCHAR_1` (
`ID` varchar(36) CHARACTER SET utf8mb4 NOT NULL DEFAULT '0' COMMENT 'Primary Key' ,
`USER_NAME` varchar(100) DEFAULT NULL COMMENT 'Username',
`USER_PWD` varchar(200) DEFAULT NULL COMMENT 'Password',
`BIRTHDAY` datetime DEFAULT NULL COMMENT ' Birthday',
`NAME` varchar(200) DEFAULT NULL COMMENT 'Name',
`USER_ICON` varchar(500) DEFAULT NULL COMMENT 'Avatar picture',
`SEX` char(1) DEFAULT NULL COMMENT 'Gender, 1: Male, 2: Female, 3: Confidential',
`NICKNAME` varchar(200) DEFAULT NULL COMMENT 'Nickname',
`STAT` varchar(10) DEFAULT NULL COMMENT 'User status , 01: Normal, 02: Freeze',
`USER_MALL` bigint(20) DEFAULT NULL COMMENT 'Current MALL',
`LAST_LOGIN_DATE` datetime DEFAULT NULL COMMENT 'Last login time',
`LAST_LOGIN_IP `varchar(100) DEFAULT NULL COMMENT 'Last login IP',
`SRC_OPEN_USER_ID` bigint(20) DEFAULT NULL COMMENT 'Source of joint login',
`EMAIL` varchar(200) DEFAULT NULL COMMENT 'Mailbox' ,
`MOBILE` varchar(50) DEFAULT NULL COMMENT 'Mobile phone',
`IS_DEL` char(1) DEFAULT '0' COMMENT 'Whether to delete',
`IS_EMAIL_CONFIRMED` char(1) DEFAULT ' 0' COMMENT 'Whether to bind an email',
`IS_PHONE_CONFIRMED` char(1) DEFAULT '0' COMMENT 'Whether to bind a mobile phone',
`CREATER` bigint(20) DEFAULT NULL COMMENT 'Creator',
`CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Registration time',
`UPDATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Modification date',
`PWD_INTENSITY` char(1) DEFAULT NULL COMMENT 'Password strength',
`MOBILE_TGC` char(64) DEFAULT NULL COMMENT 'Mobile phone login ID',
`MAC` char(64) DEFAULT NULL COMMENT 'mac address',
`SOURCE` char(1) DEFAULT '0' COMMENT '1:WEB,2:IOS,3:ANDROID,4:WIFI,5:Management system, 0:Unknown',
`ACTIVATE` char(1) DEFAULT '1' COMMENT 'Activate, 1: Activate, 0 : Not activated',
`ACTIVATE_TYPE` char(1) DEFAULT '0' COMMENT 'Activation type, 0: automatic, 1: manual',
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='User table ';

2、500WData test

2.1 Enter 500W data, auto-increment ID Save half the disk Space

Determine the amount of data in the two tables

Table with auto-increment id as the primary key

mysql> select count(1) from UC_USER;

+----------+

| count(1) |

+----- -----+

| 5720112 |

+----------+

1 row in set (0.00 sec)

mysql>

# Table with uuid as primary key

mysql> select count(1) from UC_USER_PK_VARCHAR_1; #+----------+

| count(1) |

+----------+

| 5720112 |

+----------+

1 row in set (1.91 sec)

Judging from the space capacity occupied, the auto-increment ID is about half smaller than the UUID.

Auto-increment ID -rw-rw---- 1 mysql mysql 2.5G Aug 11 18:29 UC_USER.ibd2.5 GUUID-rw-rw---- 1 mysql mysql 5.4G Aug 15 15:11 UC_USER_PK_VARCHAR_1.ibd5.4 G

Primary key type	Data file size	Occupied capacity

2.2

Single data index query , the self-increment id and uuid are not much different

##SQL statementExecution time ( Seconds)Auto-increment IDSELECT SQL_NO_CACHE t .* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` ='14782121512';0.117SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` IN( '14782121512','13761460105');SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` IN ('14782121512','13761460105');##自increment IDSELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`CREATE_DATE`='2013-11-24 10:26:36' ;UUIDSELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`CREATE_DATE`='2013-11-24 10 :26:43' ;

Primary key type

##SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` ='14782121512';

##0.118

##UUID

##Auto-increment ID

0.049

UUID

0.040

0.139

0.126

2.3 RangelikeQuery, self-incrementIDPerformance is better than UUID

##Auto-increment IDUUID (3) Range query 200 pieces of data, auto-increment ID performance is better than UUIDAuto-increment IDUUID Range query total quantity, auto-increment ID is better than UUIDAuto-increment IDUUID

##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;

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) Query 20 pieces of data in the date range. The auto-increment ID is slightly weaker than UUID

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

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

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

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

SELECT SQL_NO_CACHE COUNT(1) FROM test.`UC_USER` t WHERE t.`CREATE_DATE` > ' 2016-07-01 10:26:36' ;

0.514

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##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' ;UUID0.105

SQL statement	Execution Time (seconds)
		#Auto-increment ID
##1.419		##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;
	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