MySQLは、自増IDプライマリ・キーとUUIDをプライマリ・キーとして使用するメリットとデメリット
目次 一、準備表&データ 二、500 w級データテスト 2.1 500 Wのデータを入力、IDを増加して半分のディスクスペースを節約する . 2.2単一データインデックスクエリ、自己増加idとuuidの差は大きくない 2.3範囲likeクエリ、自己増加ID性能はUUID より優れている. 2.4書き込みテスト、自増IDはUUIDの4倍 である. 2.5、バックアップとリカバリ、自己増加ID性能はUUID より優れている. 500 Wまとめ 1000 Wまとめ 自増IDキー+ステップ長で、中規模の分散シーン に適している. UUID、小規模な分散環境 に適合
一、テーブル&データの準備
UC_USERは、自増IDが主キーであり、表構造は以下のように類似している.
UC_USER_PK_VARCHARテーブル、文字列IDはプライマリキーで、uuidを採用する:
二、500 w級データテスト
2.1 500 Wのデータを入力して、IDを増加して半分のディスクスペースを節約する
占有する空間容量は,自増IDがUUIDより半分程度小さい.
プライマリ・キー・タイプ
データファイルサイズ
占有容量
オートインクリメントID
-rw-rw---- 1 mysql mysql 2.5G Aug 11 18:29 UC_USER.ibd
2.5G
UUID
-rw-rw---- 1 mysql mysql 5.4G Aug 15 15:11 UC_USER_PK_VARCHAR_1.ibd
5.4G
2.2単一データインデックスクエリ、自己増加idとuuidの差は大きくない
プライマリ・キー・タイプ
SQL
実行時間(s)
オートインクリメントID
SELECT SQL_NO_CACHE t.* FROM test.
0.118
UUID
SELECT SQL_NO_CACHE t.* FROM test.
0.117
オートインクリメントID
SELECT SQL_NO_CACHE t.* FROM test.
0.049
UUID
SELECT SQL_NO_CACHE t.* FROM test.
0.040
オートインクリメントID
SELECT SQL_NO_CACHE t.* FROM test.
0.139
UUID
SELECT SQL_NO_CACHE t.* FROM test.
0.126
2.3範囲likeクエリ、自己増加ID性能はUUIDより優れている
プライマリ・キー・タイプ
SQL
実行時間(s)
(1)ファジイ範囲は1000個のデータを問合せ、自己増加IDの性能はUUIDより優れている
オートインクリメントID
SELECT SQL_NO_CACHE t.* FROM test.
1.784
UUID
SELECT SQL_NO_CACHE t.* FROM test.
3.196
(2)日付範囲20件のデータを照会し、自増IDがUUIDよりやや弱い
オートインクリメントID
SELECT SQL_NO_CACHE t.* FROM test.
0.601
UUID
SELECT SQL_NO_CACHE t.* FROM test.
0.543
(3)範囲200件のデータを検索し、自増IDの性能はUUIDより優れている
オートインクリメントID
SELECT SQL_NO_CACHE t.* FROM test.
2.314
UUID
SELECT SQL_NO_CACHE t.* FROM test.
3.229
(4)範囲クエリの合計数、自増IDはUUIDより良い
オートインクリメントID
SELECT SQL_NO_CACHE COUNT(1) FROM test.
0.514
UUID
SELECT SQL_NO_CACHE COUNT(1) FROM test.
1.092
2.4書き込みテスト、自増IDはUUIDの4倍
プライマリ・キー・タイプ
SQL
実行時間(s)
オートインクリメントID
UPDATE test.
1.419
UUID
UPDATE test.
5.639
2.5、バックアップと回復、自増ID性能はUUIDより優れている
プライマリ・キー・タイプ
SQL
実行時間(s)
Mysqldumpバックアップ
オートインクリメントID
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_500> UC_USER_500.sql
28.59
UUID
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_PK_VARCHAR_500> UC_USER_PK_VARCHAR_500.sql
31.08
MYSQLリカバリ
オートインクリメントID
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_500.sql
7m36
UUID
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_500.sql
9m42
500 Wまとめ
500 W記録表のテストで:
(1)通常の単一または20個程度の記録検索では,uuidはプライマリキーの差がほとんど効率的ではない.
(2)ただし、範囲クエリ、特に数百数千件以上のレコードクエリは、自己増加idの効率がuuidより大きい.
(3)範囲クエリが統計的に要約される場合、自己増加idの効率はuuidより大きい.
(4)記憶上、自増idが占める記憶空間はuuidの1/2である.
(5)バックアップ・リカバリにおいて,自己増加IDプライマリ・キーはUUIDよりやや優れている.
1000 Wまとめ
1000 W記録表のテストで:
(1)通常の単一または20個程度の記録検索では,自己増加プライマリキー効率はuuidプライマリキーの2〜3倍である.
(2)ただし、範囲クエリ、特に数百数千件以上のレコードクエリは、自己増加idの効率がuuidより大きい.
(3)範囲クエリを統計的にまとめる場合、自己増加idプライマリ・キーの効率はuuidプライマリ・キーの1.5～2倍である.
(4)記憶上、自増idが占める記憶空間はuuidの1/2である.
(5)書き込み上では,自増IDプライマリ・キーの効率はUUIDプライマリ・キーの3~10倍であり,特にupdateの小範囲内のデータ上では有意な差が認められた.
(6)バックアップ・リカバリにおいて,自己増加IDプライマリ・キーはUUIDよりやや優れている.
自増IDキー+ステップ長で、中規模の分散シーンに適しています
各クラスタノードグループのマスター上に、現在の各クラスタの開始点を1ずらすように設定し、ステップ長は将来実質的に達成不可能なダイバーシティクラスタ数より大きく選択し、IDをセグメント化する効果でグローバル一意の効果を満たす.
利点は:実現が簡単で、後期のメンテナンスが簡単で、応用に対して透明である.
欠点は、最初の設定が比較的複雑で、将来の業務の発展に対して十分なステップを計算しなければならないからです.
UUIDは、小規模な分散環境に適しています
InnoDBのような集約プライマリ・キー・タイプのエンジンでは、データはプライマリ・キーに従ってソートされます.UUIDの無秩序性のため、InnoDBは大きなIO圧力を発生し、インデックスとデータが格納されているため、文字列がプライマリ・キーになると記憶空間が2倍になります.
ストレージと取得時にinnodbはプライマリ・キーを物理的にソートし、auto_increment_intは、後で挿入されるプライマリ・キーの位置が常に最後であるため、良いメッセージです.しかしuuidにとって、これは悪いニュースです.uuidは雑然としているので、挿入するたびに主キーの位置が不確定になり、先頭にあるかもしれません.中間にあるかもしれません.主キーの物理的なソートを行うと、大量のIO操作が効率に影響を与えるに違いありません.データ量が増加し続けているとき、特にデータ量が千万記録されているとき、読み書きの性能が非常に低下している.
利点:構築が簡単で、プライマリ・キーの一意性の処理は必要ありません.
欠点:2倍のストレージスペース(クラウド上で1枚だけ保存すると2倍のお金がかかります)を占有し、後期の読み書き性能が低下します.
一、テーブル&データの準備
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=' ';
二、500 w級データテスト
# id
mysql> select count(1) from UC_USER;
+----------+
| count(1) |
+----------+
| 5720112 |
+----------+
1 row in set (0.00 sec)
# uuid
mysql> select count(1) from UC_USER_PK_VARCHAR_1;
+----------+
| count(1) |
+----------+
| 5720112 |
+----------+
1 row in set (1.91 sec)
2.1 500 Wのデータを入力して、IDを増加して半分のディスクスペースを節約する
占有する空間容量は,自増IDがUUIDより半分程度小さい.
プライマリ・キー・タイプ
データファイルサイズ
占有容量
オートインクリメントID
-rw-rw---- 1 mysql mysql 2.5G Aug 11 18:29 UC_USER.ibd
2.5G
UUID
-rw-rw---- 1 mysql mysql 5.4G Aug 15 15:11 UC_USER_PK_VARCHAR_1.ibd
5.4G
2.2単一データインデックスクエリ、自己増加idとuuidの差は大きくない
プライマリ・キー・タイプ
SQL
実行時間(s)
オートインクリメント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
2.3範囲likeクエリ、自己増加ID性能はUUIDより優れている
プライマリ・キー・タイプ
SQL
実行時間(s)
(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
(4)範囲クエリの合計数、自増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
2.4書き込みテスト、自増IDはUUIDの4倍
プライマリ・キー・タイプ
SQL
実行時間(s)
オートインクリメント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 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 5.639
2.5、バックアップと回復、自増ID性能はUUIDより優れている
プライマリ・キー・タイプ
SQL
実行時間(s)
Mysqldumpバックアップ
オートインクリメントID
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_500> UC_USER_500.sql
28.59
UUID
time mysqldump -utim -ptimgood -h192.168.121.63 test UC_USER_PK_VARCHAR_500> UC_USER_PK_VARCHAR_500.sql
31.08
MYSQLリカバリ
オートインクリメントID
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_500.sql
7m36
UUID
time mysql -utim -ptimgood -h192.168.121.63 test < UC_USER_PK_VARCHAR_500.sql
9m42
500 Wまとめ
500 W記録表のテストで:
(1)通常の単一または20個程度の記録検索では,uuidはプライマリキーの差がほとんど効率的ではない.
(2)ただし、範囲クエリ、特に数百数千件以上のレコードクエリは、自己増加idの効率がuuidより大きい.
(3)範囲クエリが統計的に要約される場合、自己増加idの効率はuuidより大きい.
(4)記憶上、自増idが占める記憶空間はuuidの1/2である.
(5)バックアップ・リカバリにおいて,自己増加IDプライマリ・キーはUUIDよりやや優れている.
1000 Wまとめ
1000 W記録表のテストで:
(1)通常の単一または20個程度の記録検索では,自己増加プライマリキー効率はuuidプライマリキーの2〜3倍である.
(2)ただし、範囲クエリ、特に数百数千件以上のレコードクエリは、自己増加idの効率がuuidより大きい.
(3)範囲クエリを統計的にまとめる場合、自己増加idプライマリ・キーの効率はuuidプライマリ・キーの1.5～2倍である.
(4)記憶上、自増idが占める記憶空間はuuidの1/2である.
(5)書き込み上では,自増IDプライマリ・キーの効率はUUIDプライマリ・キーの3~10倍であり,特にupdateの小範囲内のデータ上では有意な差が認められた.
(6)バックアップ・リカバリにおいて,自己増加IDプライマリ・キーはUUIDよりやや優れている.
自増IDキー+ステップ長で、中規模の分散シーンに適しています
各クラスタノードグループのマスター上に、現在の各クラスタの開始点を1ずらすように設定し、ステップ長は将来実質的に達成不可能なダイバーシティクラスタ数より大きく選択し、IDをセグメント化する効果でグローバル一意の効果を満たす.
利点は:実現が簡単で、後期のメンテナンスが簡単で、応用に対して透明である.
欠点は、最初の設定が比較的複雑で、将来の業務の発展に対して十分なステップを計算しなければならないからです.
UUIDは、小規模な分散環境に適しています
InnoDBのような集約プライマリ・キー・タイプのエンジンでは、データはプライマリ・キーに従ってソートされます.UUIDの無秩序性のため、InnoDBは大きなIO圧力を発生し、インデックスとデータが格納されているため、文字列がプライマリ・キーになると記憶空間が2倍になります.
ストレージと取得時にinnodbはプライマリ・キーを物理的にソートし、auto_increment_intは、後で挿入されるプライマリ・キーの位置が常に最後であるため、良いメッセージです.しかしuuidにとって、これは悪いニュースです.uuidは雑然としているので、挿入するたびに主キーの位置が不確定になり、先頭にあるかもしれません.中間にあるかもしれません.主キーの物理的なソートを行うと、大量のIO操作が効率に影響を与えるに違いありません.データ量が増加し続けているとき、特にデータ量が千万記録されているとき、読み書きの性能が非常に低下している.
利点:構築が簡単で、プライマリ・キーの一意性の処理は必要ありません.
欠点:2倍のストレージスペース(クラウド上で1枚だけ保存すると2倍のお金がかかります)を占有し、後期の読み書き性能が低下します.