MySQL主键自增与自建序列的性能比较
场景：在oracle数据库，custcontactinfo的ID为主键，但是数据类型为字符型，由sequence生成，
现需要将数据库迁移到MySQL平台，但是MySQL没有sequence，且主键的自增仅支持int类型。
服务器配置：虚拟机，4G内存，2vcpu
测试场景：新建表s_custcontactinfo,作为新建序列，表custcontactinfo 的主键从s_custcontactinfo.nextval获取，
CUSTCONTACTINFO_TMP主键为自增。
测试目的：验证这两种主键获取方式的性能
验证结果MySQL自增性能较优
mysql> desc s_custcontactinfo;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| nextval | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
1 row in set (0.05 sec)
mysql> desc CUSTCONTACTINFO_TMP;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| CUSTOMERGUID | varchar(38) | YES | | NULL | |
| NAME | varchar(45) | YES | | NULL | |
| IDCARD | varchar(18) | YES | | NULL | |
| MOBILE | varchar(11) | YES | | NULL | |
| GENDER | char(1) | YES | | NULL | |
| RELATION | varchar(38) | YES | | NULL | |
| ISLOCKED | char(1) | YES | | NULL | |
| LIVEADDRESS | varchar(150) | YES | | NULL | |
| CORPNAME | varchar(100) | YES | | NULL | |
| CORPTEL | varchar(30) | YES | | NULL | |
| DEPT | varchar(50) | YES | | NULL | |
| POSITION | varchar(50) | YES | | NULL | |
| CORPADDRESS | text | YES | | NULL | |
| REMARK | text | YES | | NULL | |
| HOUSEPHONE | varchar(40) | YES | | NULL | |
| CUSTCONTACTTYPE | char(1) | YES | | NULL | |
| RELATIONOTHER | varchar(20) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
18 rows in set (0.00 sec)
mysql> desc custcontactinfo;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id | varchar(38) | NO | PRI | NULL | |
| CUSTOMERGUID | varchar(38) | YES | | NULL | |
| NAME | varchar(45) | YES | | NULL | |
| IDCARD | varchar(18) | YES | | NULL | |
| MOBILE | varchar(11) | YES | | NULL | |
| GENDER | char(1) | YES | | NULL | |
| RELATION | varchar(38) | YES | | NULL | |
| ISLOCKED | char(1) | YES | | NULL | |
| LIVEADDRESS | varchar(150) | YES | | NULL | |
| CORPNAME | varchar(100) | YES | | NULL | |
| CORPTEL | varchar(30) | YES | | NULL | |
| DEPT | varchar(50) | YES | | NULL | |
| POSITION | varchar(50) | YES | | NULL | |
| CORPADDRESS | text | YES | | NULL | |
| REMARK | text | YES | | NULL | |
| HOUSEPHONE | varchar(40) | YES | | NULL | |
| CUSTCONTACTTYPE | char(1) | YES | | NULL | |
| RELATIONOTHER | varchar(20) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
初始数据量为：
mysql> select count(*) from custcontactinfo;---主键为字符型
+----------+
| count(*) |
+----------+
| 196195 |
+----------+
1 row in set (0.32 sec)
mysql> select count(*) from CUSTCONTACTINFO_TMP;---主键为int类型
+----------+
| count(*) |
+----------+
| 196195 |
+----------+
1 row in set (1.63 sec)
mysql>
测试的脚本：
mysql> create procedure P_CUSTCONTACTINFO_TMP()
begin
declare j int default 0;
declare m int default 0;
declare begin_insert TIMESTAMP default now();
declare end_insert TIMESTAMP default now();
declare timeconsum int default 0;
declare i int default 0;
while j<5 do
set i=0;
set j=j+1;
set begin_insert=now();
while i<100 do
INSERT INTO CUSTCONTACTINFO_TMP (CUSTOMERGUID, NAME, IDCARD, MOBILE, GENDER, RELATION, ISLOCKED, LIVEADDRESS, CORPNAME, CORPTEL, DEPT, POSITION, CORPADDRESS, REMARK, HOUSEPHONE, CUSTCONTACTTYPE, RELATIONOTHER)
VALUES ('{5F6E1ED3-36AA-452C-92D4-34E8091B1675}', '测试1', '900005100008010060', '18988888236', '2', '{16EC7EED-A836-439F-9318-2171FBDD235D}', '0', '火星深圳市区镇八联村啊八戒11栋8单元1D', '星球开发（火星）有限公司', '0755-10000-1234', '总经办', '总经理', '银河星系太阳系火星球', NULL, '0755-10000', '5', NULL);
set i=i+1;
end while;
set end_insert=now();
commit;
set m=j*i;
set timeconsum=timediff(end_insert,begin_insert);
select (j-1)*i "开始插入数据",j*i "结束插入数据",timeconsum "数据插入耗时";
end while;
end
mysql> call P_CUSTCONTACTINFO_TMP;
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 0 | 100 | 19 |
+--------------------+--------------------+--------------------+
1 row in set (18.30 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 100 | 200 | 20 |
+--------------------+--------------------+--------------------+
1 row in set (38.75 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 200 | 300 | 18 |
+--------------------+--------------------+--------------------+
1 row in set (57.00 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 300 | 400 | 18 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 14.27 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 400 | 500 | 17 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 31.95 sec)
Query OK, 0 rows affected (1 min 31.95 sec)
create procedure p_custcontactinfo()
begin
declare j int default 0;
declare m int default 0;
declare begin_insert TIMESTAMP default now();
declare end_insert TIMESTAMP default now();
declare timeconsum int default 0;
declare nextval0 int;
declare i int default 0;
while j<5 do
set i=0;
set j=j+1;
set begin_insert=now();
while i<100 do
update s_custcontactinfo set nextval=nextval+1;
select nextval into @nextval0 from s_custcontactinfo;
INSERT INTO custcontactinfo (ID,CUSTOMERGUID, NAME, IDCARD, MOBILE, GENDER, RELATION, ISLOCKED, LIVEADDRESS, CORPNAME, CORPTEL, DEPT, POSITION, CORPADDRESS, REMARK, HOUSEPHONE, CUSTCONTACTTYPE, RELATIONOTHER)
VALUES (@nextval0,'{5F6E1ED3-36AA-452C-92D4-34E8091B1675}', '测试1', '900005100008010060', '18988888236', '2', '{16EC7EED-A836-439F-9318-2171FBDD235D}', '0', '火星深圳市区镇八联村啊八戒11栋8单元1D', '星球开发（火星）有限公司', '0755-10000-1234', '总经办', '总经理', '银河星系太阳系火星球', NULL, '0755-10000', '5', NULL);
set i=i+1;
end while;
set end_insert=now();
commit;
set m=j*i;
set timeconsum=timediff(end_insert,begin_insert);
select (j-1)*i "开始插入数据",j*i "结束插入数据",timeconsum "数据插入耗时";
end while;
end
mysql> call p_custcontactinfo;
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 0 | 100 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (36.19 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 100 | 200 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 12.28 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 200 | 300 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 47.79 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 300 | 400 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (2 min 24.08 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 400 | 500 | 35 |
+--------------------+--------------------+--------------------+
1 row in set (2 min 58.53 sec)
Query OK, 0 rows affected (2 min 58.53 sec)
mysql>
场景：在oracle数据库，custcontactinfo的ID为主键，但是数据类型为字符型，由sequence生成，
现需要将数据库迁移到MySQL平台，但是MySQL没有sequence，且主键的自增仅支持int类型。
服务器配置：虚拟机，4G内存，2vcpu
测试场景：新建表s_custcontactinfo,作为新建序列，表custcontactinfo 的主键从s_custcontactinfo.nextval获取，
CUSTCONTACTINFO_TMP主键为自增。
测试目的：验证这两种主键获取方式的性能
验证结果MySQL自增性能较优
mysql> desc s_custcontactinfo;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| nextval | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
1 row in set (0.05 sec)
mysql> desc CUSTCONTACTINFO_TMP;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| CUSTOMERGUID | varchar(38) | YES | | NULL | |
| NAME | varchar(45) | YES | | NULL | |
| IDCARD | varchar(18) | YES | | NULL | |
| MOBILE | varchar(11) | YES | | NULL | |
| GENDER | char(1) | YES | | NULL | |
| RELATION | varchar(38) | YES | | NULL | |
| ISLOCKED | char(1) | YES | | NULL | |
| LIVEADDRESS | varchar(150) | YES | | NULL | |
| CORPNAME | varchar(100) | YES | | NULL | |
| CORPTEL | varchar(30) | YES | | NULL | |
| DEPT | varchar(50) | YES | | NULL | |
| POSITION | varchar(50) | YES | | NULL | |
| CORPADDRESS | text | YES | | NULL | |
| REMARK | text | YES | | NULL | |
| HOUSEPHONE | varchar(40) | YES | | NULL | |
| CUSTCONTACTTYPE | char(1) | YES | | NULL | |
| RELATIONOTHER | varchar(20) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
18 rows in set (0.00 sec)
mysql> desc custcontactinfo;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id | varchar(38) | NO | PRI | NULL | |
| CUSTOMERGUID | varchar(38) | YES | | NULL | |
| NAME | varchar(45) | YES | | NULL | |
| IDCARD | varchar(18) | YES | | NULL | |
| MOBILE | varchar(11) | YES | | NULL | |
| GENDER | char(1) | YES | | NULL | |
| RELATION | varchar(38) | YES | | NULL | |
| ISLOCKED | char(1) | YES | | NULL | |
| LIVEADDRESS | varchar(150) | YES | | NULL | |
| CORPNAME | varchar(100) | YES | | NULL | |
| CORPTEL | varchar(30) | YES | | NULL | |
| DEPT | varchar(50) | YES | | NULL | |
| POSITION | varchar(50) | YES | | NULL | |
| CORPADDRESS | text | YES | | NULL | |
| REMARK | text | YES | | NULL | |
| HOUSEPHONE | varchar(40) | YES | | NULL | |
| CUSTCONTACTTYPE | char(1) | YES | | NULL | |
| RELATIONOTHER | varchar(20) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
初始数据量为：
mysql> select count(*) from custcontactinfo;---主键为字符型
+----------+
| count(*) |
+----------+
| 196195 |
+----------+
1 row in set (0.32 sec)
mysql> select count(*) from CUSTCONTACTINFO_TMP;---主键为int类型
+----------+
| count(*) |
+----------+
| 196195 |
+----------+
1 row in set (1.63 sec)
mysql>
测试的脚本：
mysql> create procedure P_CUSTCONTACTINFO_TMP()
begin
declare j int default 0;
declare m int default 0;
declare begin_insert TIMESTAMP default now();
declare end_insert TIMESTAMP default now();
declare timeconsum int default 0;
declare i int default 0;
while j<5 do
set i=0;
set j=j+1;
set begin_insert=now();
while i<100 do
INSERT INTO CUSTCONTACTINFO_TMP (CUSTOMERGUID, NAME, IDCARD, MOBILE, GENDER, RELATION, ISLOCKED, LIVEADDRESS, CORPNAME, CORPTEL, DEPT, POSITION, CORPADDRESS, REMARK, HOUSEPHONE, CUSTCONTACTTYPE, RELATIONOTHER)
VALUES ('{5F6E1ED3-36AA-452C-92D4-34E8091B1675}', '测试1', '900005100008010060', '18988888236', '2', '{16EC7EED-A836-439F-9318-2171FBDD235D}', '0', '火星深圳市区镇八联村啊八戒11栋8单元1D', '星球开发（火星）有限公司', '0755-10000-1234', '总经办', '总经理', '银河星系太阳系火星球', NULL, '0755-10000', '5', NULL);
set i=i+1;
end while;
set end_insert=now();
commit;
set m=j*i;
set timeconsum=timediff(end_insert,begin_insert);
select (j-1)*i "开始插入数据",j*i "结束插入数据",timeconsum "数据插入耗时";
end while;
end
mysql> call P_CUSTCONTACTINFO_TMP;
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 0 | 100 | 19 |
+--------------------+--------------------+--------------------+
1 row in set (18.30 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 100 | 200 | 20 |
+--------------------+--------------------+--------------------+
1 row in set (38.75 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 200 | 300 | 18 |
+--------------------+--------------------+--------------------+
1 row in set (57.00 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 300 | 400 | 18 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 14.27 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 400 | 500 | 17 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 31.95 sec)
Query OK, 0 rows affected (1 min 31.95 sec)
create procedure p_custcontactinfo()
begin
declare j int default 0;
declare m int default 0;
declare begin_insert TIMESTAMP default now();
declare end_insert TIMESTAMP default now();
declare timeconsum int default 0;
declare nextval0 int;
declare i int default 0;
while j<5 do
set i=0;
set j=j+1;
set begin_insert=now();
while i<100 do
update s_custcontactinfo set nextval=nextval+1;
select nextval into @nextval0 from s_custcontactinfo;
INSERT INTO custcontactinfo (ID,CUSTOMERGUID, NAME, IDCARD, MOBILE, GENDER, RELATION, ISLOCKED, LIVEADDRESS, CORPNAME, CORPTEL, DEPT, POSITION, CORPADDRESS, REMARK, HOUSEPHONE, CUSTCONTACTTYPE, RELATIONOTHER)
VALUES (@nextval0,'{5F6E1ED3-36AA-452C-92D4-34E8091B1675}', '测试1', '900005100008010060', '18988888236', '2', '{16EC7EED-A836-439F-9318-2171FBDD235D}', '0', '火星深圳市区镇八联村啊八戒11栋8单元1D', '星球开发（火星）有限公司', '0755-10000-1234', '总经办', '总经理', '银河星系太阳系火星球', NULL, '0755-10000', '5', NULL);
set i=i+1;
end while;
set end_insert=now();
commit;
set m=j*i;
set timeconsum=timediff(end_insert,begin_insert);
select (j-1)*i "开始插入数据",j*i "结束插入数据",timeconsum "数据插入耗时";
end while;
end
mysql> call p_custcontactinfo;
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 0 | 100 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (36.19 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 100 | 200 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 12.28 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 200 | 300 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 47.79 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 300 | 400 | 36 |
+--------------------+--------------------+--------------------+
1 row in set (2 min 24.08 sec)
+--------------------+--------------------+--------------------+
| 开始插入数据 | 结束插入数据 | 数据插入耗时 |
+--------------------+--------------------+--------------------+
| 400 | 500 | 35 |
+--------------------+--------------------+--------------------+
1 row in set (2 min 58.53 sec)
Query OK, 0 rows affected (2 min 58.53 sec)
mysql>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10569970/viewspace-1732755/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10569970/viewspace-1732755/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
