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

原文連結https://blog.csdn.net/mchdba/article/details/52279523

一個開發同僚做了一個架構，裡面主鍵是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小一半左右。

3、總結

在500W記錄表的測試下：

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

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

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

（4）      在存儲上面，自增id所占的存儲空間是uuid的1/2；（int占4個位元組，uuid占3個位元組）

--------------------------------------------------------------------------------------------------------------------------

結論

（1）單執行個體或者單節點組：

經過500W、1000W的單機表測試，自增ID相對UUID來說，自增ID主鍵性能高于UUID，磁盤存儲費用比UUID節省一半的錢。是以在單執行個體上或者單節點組上，使用自增ID作為首選主鍵。

（2）分布式架構場景：

20個節點組下的小型規模的分布式場景，為了快速實作部署，可以采用多花存儲費用、犧牲部分性能而使用UUID主鍵快速部署；

20到200個節點組的中等規模的分布式場景，可以采用自增ID+步長的較快速方案。

200以上節點組的大資料下的分布式場景，可以借鑒類似twitter雪花算法構造的全局自增ID作為主鍵。