自動インクリメント ID 主キーと UUID を主キーとして使用する MySQL の長所と短所を比較する詳細なプロセス (500W の単一テーブル)

テストの理由

開発同僚が主キーがuuidであるフレームワークを作成したので、mysqlはuuidを使用せず、自動インクリメントを使用するべきだと彼に提案しました。主キーは効率的ですが、必ずしも高いわけではないと私は言いました。InnoDB のインデックス機能では、主キーとして自動インクリメントを使用するのが最も効率的です。彼を説得するために、私は詳細を準備しました。テスト。

インターネット企業としてはユーザーテーブルが必ず存在し、ユーザーテーブル 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) DE FAULT NULL コメント 'アバター画像',
`SEX` char(1) デフォルト NULL コメント '性別、1: 男性、2: 女性、3: 機密',
`NICKNAME` varchar(200) デフォルト NULL コメント 'ニックネーム',
`STAT` varchar(10) DEFAULT NULL COMMENT 'ユーザーステータス、01: 通常、02: 凍結',
`USER_MALL` bigint(20) DEFAULT NULL COMMENT '現在のモール',
`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 COMMENTFAULT 'メールボックス',
`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 'Creator',
` 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)フォルトヌル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'コメント 'アクティベーションの種類、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テーブル、uuidを使用して文字列IDが主キーになります

CREATE TABLE `UC_USER_PK_VAR_1` (
`ID`文字セット utf8mb4 はデフォルトでは NULL ではありません ' 0 'comment'primarykey '、
`user_name` varchar（100）デフォルトのnullコメント' username '、
`user_pwd` varchar（200）デフォルトのヌルコメント'パスワード '、
データタイムデフォルトのヌルコメント'誕生日',
`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 '現在のモール',
`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 'モバイルログインID',
`MAC` char(64) DEFAULT NULL COMMENT 'MACアドレス',
`SOURCE ` char (1) DEFAULT '0' COMMENT '1:WEB,2:IOS,3:ANDROID,4:WIFI,5:管理システム, 0:Unknown',
`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ディスク容量の半分を節約

各テーブルのデータ量を2つ確認

占有スペース 自己増加IDはUUIDの半分くらい小さいようです。

# 自動インクリメントされたIDを主キーとするテーブル

mysql> select count(1) from UC_USER;

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

| カウント(1 ) |

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

| 5720112 |

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

1 行(0.00 秒)

mysql> ;

# 主キーとして uuid を持つテーブル

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

|

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

1 row in set (1.91 sec)

主キーの種類-rw-rw---- 1 mysql mysql 2.5G 8 月 11 日 18:29 UC_USER.ibd2.5 G-rw-rw---- 1 mysql mysql 5.4G 8 月 15 15:1 1 UC_USER_PK_VARCHAR_1 .ibd5.4 G

データファイルサイズ	占有容量	ID
		UUID

2.2

単一データインデックスクエリ、自動インクリメント

id

およびuuid あまり違いはありません

主キーの種類SELECT SQL_NO_CACHE t.* FROM test.` UC_USER` t WHERE t .`MOBILE` ='14782121512';0.118SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` ='14782121512';0.117 SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` IN( '14782121512','13761460105');0.049UUIDSELECT SQL_NO_ CACHE t. * FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` IN('14782121512','13761460105');自己インクリメント ID SELECT 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' ;

SQL文

実行時間(秒)

自動インクリメントID

UUID

自己増分ID

0.040

0.139

0.126

2.3 スコープlikeクエリ、自動インクリメントIDパフォーマンスはUUID

よりも優れています Auto-IncrementIdSQL_NO_CACHEカウント（1）からテストから選択します1.092

主キーの種類

SQL文

実行時間(秒)

(1)ファジー範囲クエリ 1000データ、自動インクリメントIDのパフォーマンスがUUIDより優れています

自動-increment ID

SELECT SQL_NO_CACHE t.* FROM test.`UC_USER` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1000;

1.784

UUID

選択SQL _NO_CACHE t.* FROM test. `UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1000;

3.196

(2) 日付範囲クエリ 2 0データ、自動インクリメント 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_VAR_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 よりも優れています

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

PS: キャッシュが存在する場合、この 2 つの実行効率には小さな違いはありません。

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 ` <'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`,誕生日、名前、 `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;	0.105
UUID : 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`、 `UP DATE_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`、 `ACT IVATE_TYPE` FROM `test`.` UC_USER_1` LIMIT 100;	0.424

3

、总结在500W记录表の测试下：

（1） 普通单条または、20 条の左右の记录検索、uuid は主键の相差不大啈率同じ；

（2） しかし范围询特に上百成千条の记录蟥询、自 ID の能率は uuid より大きい；

（ 3.

以上ですMySQL は、主键の劣悪比较详细过程（500W 单表）の内容、より多くの相关内容请关注PHP中文网（www.php.cn）！