ホームページ > 記事 > データベース > MySQL で自動インクリメント ID の主キーと UUID を主キーとして使用する利点と欠点を比較する詳細なプロセス (100 万から 1,000 万のテーブルレコードのテスト)

MySQL で自動インクリメント ID の主キーと UUID を主キーとして使用する利点と欠点を比較する詳細なプロセス (100 万から 1,000 万のテーブルレコードのテスト)

黄舟オリジナル: 2017-02-16 11:35:541812ブラウズ

テストの理由

開発同僚が、主キーが uuid であるフレームワークを作成しました。私は、mysql は uuid を使用せず、自動インクリメント主キーを使用するべきだと彼に提案しました。自動インクリメントの主キーはより効率的であると彼は言いましたが、それは必ずしも高いわけではなく、実際に彼を説得するために、innodb のインデックス作成の特性により、自動インクリメント ID が最も効率的に使用されることになります。場合によっては、詳細なテストを行う準備をしました。

インターネット企業としてはユーザーテーブルが必ず存在し、ユーザーテーブル UC_USER には基本的に数百万のレコードがあるため、このテーブルに基づいて準テストデータを使用してテストします。

テストプロセスは、私が現在考えている一般的に使用されているさまざまな種類の SQL に基づいています。もちろん、完璧ではないかもしれません。より完全なテスト計画を提案するためにメッセージを残してください。または SQL ステートメントをテストします。

1. 自動インクリメント ID を主キーとしてテーブルとデータ

UC_USER を準備します。テーブル構造は次のようになります。

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)フォルトのヌルコメント'モバイルログインロゴ ',
`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 'Activate, 1: Activated, 0: Inactive',
`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テーブル、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を主キーとするテーブル

mysql> select count(1) from UC_USER;

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

| カウント(1 ) |

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

| 5720112 |

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

1 行(0.00 秒)

mysql> ;

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

mysql> select count(1) from UC_USER_PK_VARCHAR_1;

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

| count(1) |

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

| 5720112 |

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

1 行 (1.91 秒)

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

主キーの種類	データファイルサイズ	占有容量
ID	-rw-rw---- 1 mysql mysql 2.5G 8 月 11 日 18:29 UC_USER.ibd	2.5 G
UUID	-rw-rw---- 1 mysql mysql 5.4G 8 月 15 15:1 1 UC_USER_PK_VARCHAR_1 .ibd	5.4 G

2.2 単一データインデックスクエリ、自動インクリメントidおよびuuid あまり違いはありません

UUIDSELECT 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.139UUIDSELECT 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文	実行時間(秒)
自動インクリメント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

よりも優れています 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`、 `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`、 `ACT IVATE_TYPE` FROM `test`.` UC_USER_1` LIMIT 100;	0.424

2.5

、备份和恢复、自增IDのパフォーマンスはUUID

より主键型 SQL语句実行時間(秒)自增IDUUID自增IDUUID

3、500W概要

500W録画台テスト中:

(1)通常のシングルまたは約20の場合レコードの取得、uuid は主キーには大きな違いはなく、効率はほぼ同じです

(2) ただし、範囲クエリ、特に数百または数千のレコードのクエリでは、自動インクリメント ID の効率が uuid よりも優れています

( 3) 範囲クエリの統計的要約を行う場合、自己増加 ID の効率は uuid の効率よりも優れています

(4) ストレージに関しては、自己増加 ID が占めるストレージ容量は uuid の 1/2 です。

(5) バックアップとリカバリの点では、UUID では自己増加 ID の主キーの方が若干優れています。

4、1000Wデータテスト

4.1 入力1000Wデータ記録、ストレージスペースを参照


Mysqldump备份
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_500> UC_USER_500.sql	28.59秒
time mysqldump -utim -ptimgood -h192.168.121.63 テスト UC_USER_PK_VARCHAR_ 500> UC_USER_PK_VARCHAR_500.sql	31.08秒
MySQL恢复
time mysql -utim -ptimgood -h192.168.121.63 test < ; UC_USER_500.sql	7m36.601s
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_500.sql	9m42.472s

# idテーブルを主キーとして自動インクリメント

mysql> use test;

データベースが変更されました

mysql> select count(1) from UC_USER_1;

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

| カウント(1) |

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

| 10698102 |

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

1 行 (27.42 秒) )

# uuid主キーとしてのテーブル

mysql> select count(1) from UC_USER_PK_VARCHAR_1;

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

| 10698102 |

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

セット内の 1 行 (0.00 秒)

mysql>

占有容量の点で、自動インクリメントIDはUUIDの約半分です:

4.2 単一データにインデックスが付けられ、効率比は次のとおりです: (2~3):1 主キーの種類

主キータイプ	ファイルサイズ	占有容量
自動インクリメント ID	-rw-rw---- 1 mysql mysql 4.2G 8 月 20 日 23:08 UC_USER_1.ibd	4.2g
UUID	-rw-rw---- 1 mysql mysql 8.8G 8月 20 18:20 UC_USER_PK_VARCHAR_1.ibd	8.8 G

SELECT CHE t .* FROM test.`UC_USER_1` t WHERE t .`MOBILE` ='14782121512';UUIDSELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t .`モバイル` ='14782121512';S ELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1` t WHERE t.`モバイル` IN( ' 14782121512','13761460105');

SQLステートメント

実行時間(秒)

単一レコードクエリ

自己インクリメントID

0.069

0.274

狭い範囲のクエリ

自己インクリメントID

0.050

UUID

SELECT SQL_NO_CACHE t.* FROM test .`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE ` IN('14782121512','13761460105 ');

0.151

日付に基づくクエリ

自己インクリメントID

SELECT SQL_NO_CACHE t.* FROM test .` UC_USER_1` t WHERE t.` CREATE_DATE`='2013-11-24 10:26:36' ;

0.269

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.810

4.3 rangelikeクエリ、自動インクリメントIDのパフォーマンスはUUIDの比率( 1.5 ~2)： 1

(3) 範囲クエリ 200 項目のデータ、自動インクリメント ID のパフォーマンスは UUID自動インクリメント IDUUID 範囲クエリの合計数量、自動インクリメント ID は UUID よりも優れています自己インクリメント ID

主キーの種類

SQL文

実行時間(秒)

(1) 1000個のデータをファジーでクエリする範囲、自動インクリメント ID UUID よりも優れたパフォーマンス

自動インクリメント ID

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

2.3 98

UUID

SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_PK_VARCHAR_1` t WHERE t.`MOBILE` LIKE '147%' LIMIT 1 000;

5.872

(2 ) 日付範囲内の 20 個のデータをクエリします。自動インクリメント ID は、UUID よりわずかに弱いです

自動インクリメント ID

SELECT SQL_NO_CACHE t.* FROM テスト。 .`UC_USER_1` t WHERE t.`CREATE_DATE` > '2016 -08-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 20;

0.765

UUIだ

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;

1.090

SELECT SQL_NO_CACHE t.* FROM test.`UC_USER_1 よりも優れています` t WHERE t.`CREATE_DATE` > '2016-07-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 200;

1.569

SELECT SQL_NO_CACHE t.* FROM test.`UC_USERPK_VARCH AR_1` t WHERE t.`CREATE_DATE` > ; '2016-07-01 10:26:36' ORDER BY t.`UPDATE_DATE` DESC LIMIT 200;

2.597

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

1 .129

UUID

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

;

4.4 書き込みテスト、自動インクリメントID比率UUID高効率、比率(3~10): 1

主キーの種類	SQL文	実行時間(秒)
1 日の記録を修正する
自動インクリメントID	UPDATE test.`UC_USER_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' ;	2.685
UUID	UPDATE test.`UC_USER_PK_VARCHAR_1` t. `MO BILE_TGC`='T2 ' WHERE t.`CREATE_DATE` > '2016-05-03 10:26:36' AND t.`CREATE_DATE` <'2016-05-04 00:00:00' ;	26.521
入力データ
自動インクリメントID	INSERT INTO test.`UC_USER_1`( ID, `USER_NAME`, `USER_PWD`, `BIRTHDAY`, `NAME`, _アイコン` 、`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`、`S OURCE`、`ACTIVATE `, `ACTIVATE_TYPE` FROM `test`.`UC_USER_1` LIMIT 100;	0.534
UUID	INSERT INTO test.`UC_USER_PK_VARCHAR_1`( ID, `USER_NAME`, `USER_PWD`, `BIRTHDAY`, `NAME`, `USER_ICON`, `SEX`, `NICKNAME`, `STAT`, `USER_モール`, ` 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`、 `ACT IVATE_TYPE` FROM `test`.` UC_USER_1` LIMIT 100;	1.716

4.5、备份和恢复、自增IDのパフォーマンスはUUID

より主键型	SQL语句	実行時間(秒)
Mysqldump备份
自增ID	time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_1> UC_USER_1.sql	0m50.548s
UUID	time mysqldump -utim -ptimgood -h192.168.121.63 テスト UC_USER_PK_VARCHAR _1> UC_USER_PK_VARCHAR_1.sql	0m58.590s
MySQL恢复
自增ID	time mysql -utim -ptimgood -h192.168.121.63 test	17m30.822s
UUID	time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_1.sql	23m6.360s

5、1000W概要

1000W記録表のテスト中:

(1) 通常の単一記録または約20記録取得、自動インクリメント主キー効率は uuid 主キーの 2 ～ 3 倍です

(2) ただし、範囲クエリ、特に数百または数千のレコードの場合、自動インクリメント ID の効率は uuid よりも優れています

(3)。範囲クエリの統計サマリを実行する場合、自動インクリメント ID プライマリキーの効率は、uuid プライマリキーの 1.5 ～ 2 倍です

(4) ストレージ上では、自動インクリメント ID が占有するストレージスペースは 1/ です。 2 の uuid;

(5) 書き込み時、自動インクリメント ID 主キーを追加する効率は、特に狭い範囲内のデータを更新する場合に、UUID 主キーの 3 ～ 10 倍になります。

(6) バックアップとリカバリの点では、自己増加 ID の主キーは UUID よりわずかに優れています。

6、MySQL 分散アーキテクチャの選択

分散アーキテクチャとは、テーブルの主キーの一意性を複数のインスタンスで維持する必要があることを意味します。現時点では、複数の mysql インスタンスが主キーのグローバルな一意性の問題に遭遇するため、通常の単一テーブルの自己増加 ID の主キーは適していません。

6.1、自己インクリメントID主キー+ステップサイズ、中規模の分散シナリオに適しています

各クラスターノードのマスター上group では、各クラスターの開始点が 1 ずつずらされるように (auto_increment_increment) を設定し、ステップサイズは、基本的に将来達成することが不可能な分割クラスターの数よりも大きくなるように選択されます。グローバルな固有の効果を満たすために ID を相対的にセグメント化する効果。

利点は、実装が簡単、事後メンテナンスが簡単、アプリケーションに対して透過的であることです。

欠点は、将来のビジネス開発に十分なステップサイズを計算する必要があるため、最初のセットアップが比較的複雑であることです、次に i 番目のノードグループの my.cnf の構成を計算する必要があります。ノードグループは:

auto_increment_offset i

auto_increment_increment N

48 のノードグループが計画されている場合、N は 48、次に 8 番目のノードグループを構成します。この i は 8 であり、8 番目のノードの my.cnf の構成です。グループは:

auto_increment_offset 8

auto_increment_increment 48

6.2

、

U UID

、小規模分散環境に適しています

InnoDB のエンジンのメインキーUUID の乱れにより、InnoDB は大きな IO 負荷を発生し、インデックスとデータが一緒に保存されるため、文字列を主キーとして使用すると、データが 2 倍になります。収納スペース。保存と取得中に、innodb は主キーを物理的にソートします。これは auto_increment_int にとって朗報です。後で挿入される主キーの位置は常に最後になるからです。しかし、uuid にとってこれは悪いニュースです。uuid は乱雑であり、主キーが物理的にソートされると、毎回挿入される主キーの位置が不確かになる可能性があります。大量の IO 操作は効率に影響します。データ量が増加し続けると、特にデータ量が数千万レコードを超えると、読み取りおよび書き込みのパフォーマンスが大幅に低下します。

利点: 設定が比較的簡単で、主キーの一意性は必要ありません。

短所: 2 倍のストレージ容量が必要になり (クラウド内の 1 つのストレージに

倍のコストがかかります)、後半になると読み取りと書き込みのパフォーマンスが大幅に低下します。

6.3

、Snowflakeアルゴリズムが自己作成したグローバル自己インクリメント

、ビッグデータ環境における分散シナリオに適しています

によって発表されたオープンソースの分散IDアルゴリズムtwitter スノーフレーク (Java バージョン) IdWorker.java:

パッケージ com.demo.elk;

import org. slf4j.Log gerFactory;

パブリッククラス IdWorker {

保護された静的最終ロガー LOG = LoggerFactory.getLogger(IdWorker.class);

プライベートロングワーカーID;

プライベートロングデータセンターId;

プライベートロングシーケンス = 0L;

プライベートロングトゥエポック = 1288834974657L;

private long workIdBits = 5L;

private long datacenterIdBits = 5L;

private long maxWorkerId = -1L ^ (-1L << workIdBits);

private long maxDatacenterId = -1L < < ; datacenterIdBits);

プライベートロング sequenceBits = 12L;

プライベートロングworkerIdShift = sequenceBits;

プライベートロングdatacenterIdShift = sequenceBits + workerIdBits;

プライベートロングタイムスタンプLeftShift = sequenceBits + workerIdBits + datacenterIdBits;

プライベートの長いシーケンスマスク = -1L ^ (-1L << sequenceBits);

private long lastTimestamp = -1L;

public IdWorker(long workerId, long datacenterId) {

// workerId の健全性チェック

場合 (ワーカー ID > maxWorkerId || ワーカー ID < 0) {

throw new IllegalArgumentException("ワーカー ID は %d 未満にすることはできません", maxWorkerId);

}

場合 (データセンターID > maxDatacenterId ||データセンターID <; 0) {

throw new IllegalArgumentException(String.format("datacenter Id は %d より大きいか 0 未満にすることはできません", maxDatacenterId));

}

this.workerId = workerId;

this.datacenterId = datacenterId;

LOG.info(String.format("ワーカー開始。タイムスタンプ左シフト %d, データセンター ID ビット %d、ワーカー ID ビット %d、シーケンスビット %d、ワーカー ID %d", timestampLeftShift, datacenterIdBits、workerIdBits 、 sequenceBits, workerId));

}

public synchronized long nextId() {

long timestamp = timeGen();

if (タイムスタンプ & lt; lastTimestamp) {

LOG.error(String. format("時計が逆方向に移動しています。%d までリクエストを拒否しています。", lastTimestamp));

throw new RuntimeException(String.format("時計が逆方向に移動しています。%d ミリ秒間 ID の生成を拒否しています", lastTimestamp - timestamp)) ;

}

if (lastTimestamp == タイムスタンプ) {

sequence = (sequence + 1) & sequenceMask;

if (sequence == 0) {

タイムスタンプ = tilNextMillis(lastTimestamp);

}

} else {

sequence = 0L;

}

lastTimestamp = タイムスタンプ;

return ((タイムスタンプ - twepoch) <<タイムスタンプ左シフト) | (データセンター ID << データセンター IDShift) | (workerId <

}

protected long tilNextMillis(long lastTimestamp) {

long timestamp = timeGen();

while (timestamp <= lastTimestamp) {

timestamp = timeGen();

}

return timestamp;

}

protected long timeGen() {

return System.currentTimeMillis();

}

测试生成IDの测试类，IdWorkerTest.java：

package com.demo.elk;

import java.util.HashSet;

java.util.Setをインポートする;

public class IdWorkerTest {

static class IdWorkThreadimplements Runnable {

private Set set;

private IdWorker idWorker;

public IdWorkThread(Set set, IdWorker idWorker) {

this.set = set;

this.idWorker = idWorker;

}

public void run() {

while (true) {

long id = idWorker.nextId();

System.out.println(" real id:" + id) ;

if (!set.add(id) ) {

System.out.println("duplicate:" + id);

}

public static void main(String[] args) {

Set();

final IdWorker idWorker1 = new IdWorker(0, 0);

final IdWorker idWorker2 = new IdWorker(1, 0);

Thread t1 = new Thread(new I dWorkThread(セット、idWorker1));

スレッド t2 = new Thread(new IdWorkThread(set, idWorker2));

t1.setDaemon(true);

t2.setDaemon(true);

t1.start();

t2.start();

try {

Thread.sleep(30000);

} catch (InterruptedException e) {

e.printStackTrace();

}

7、概要

(1) 単一インスタンスまたは単一ノードグループ:

500W、1000W 単一マシンテーブルテスト、自動インクリメントID相対to UUID 一般に、自動インクリメント ID 主キーのパフォーマンスは UUID よりも高く、ディスクストレージのコストは UUID の半分です。したがって、単一のインスタンスまたは単一のノードグループでは、優先主キーとして自動インクリメントされる ID を使用します。

(2) 分散アーキテクチャシナリオ:

20 ノードグループによる小規模分散シナリオでは、迅速な導入を実現するために、より多くのストレージコストを費やし、一部のパフォーマンスを犠牲にすることができます。

中規模の分散シナリオの 20 ～ 200 ノードグループは、自己増加 ID+steppen のより高速なスキームを採用できます。ポイント 200以上のノード群のビッグデータ配下の分散シナリオは、Twitter Snowflakeのアルゴリズム構造と同様のグローバル自己増加IDを主キーとして学習できます。

上記は、自動インクリメント ID の主キーと UUID を主キーとして使用する MySQL の長所と短所を比較する詳細なプロセスです (数百万から数千万のテーブルレコードをテストします)。 PHP 中国語 Web サイト (www.php.cn) にご注意ください。

声明：

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

前の記事：mySql の学習メモ: SQL サーバーを書くよりも簡単次の記事：mySql の学習メモ: SQL サーバーを書くよりも簡単

続きを見る

MySQL で自動インクリメント ID の主キーと UUID を主キーとして使用する利点と欠点を比較する詳細なプロセス (100 万から 1,000 万のテーブル レコードのテスト)

1. 自動インクリメント ID を主キーとしてテーブルとデータ

2、500Wデータテスト

2.1 入力500Wデータ、 IDディスク容量を半分に節約

2.2 単一データインデックスクエリ、自動インクリメントidおよびuuid あまり違いはありません

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

2.4 書き込みテスト、自動インクリメント ID は UUID 4 回

、备份和恢复、自增IDのパフォーマンスはUUID

3、500W概要

4、1000Wデータテスト

4.1 入力1000Wデータ記録、ストレージスペースを参照

4.2 単一データにインデックスが付けられ、効率比は次のとおりです: (2~3):1 主キーの種類

4.3 rangelikeクエリ、自動インクリメントIDのパフォーマンスはUUIDの比率( 1.5 ~2)： 1

4.4 書き込みテスト、自動インクリメントID比率UUID高効率、比率(3~10): 1

4.5、备份和恢复、自增IDのパフォーマンスはUUID

5、1000W概要

6、MySQL 分散アーキテクチャの選択

6.1、自己インクリメントID主キー+ステップサイズ、中規模の分散シナリオに適しています

によって発表されたオープンソースの分散IDアルゴリズムtwitter スノーフレーク (Java バージョン) IdWorker.java:

7、概要

関連記事

MySQL で自動インクリメント ID の主キーと UUID を主キーとして使用する利点と欠点を比較する詳細なプロセス (100 万から 1,000 万のテーブルレコードのテスト)