今天测试了一下uuid与自增主键性能.
测试数据库:mysql 5.7
测试工具:MySQLWorkbench 6.3
1.创建四个表
表结构均为:id,name;
分别为自增主键InnoDB,自增主键MyISAM,UUID做主键InnoDB,UUID做主键MyISAM.
(这里我的UUID是去除了'-'的).
2.创建四个存储过程
向四张表插入一百万条测试数据.
3.性能测试
测试语句
4.测试结果
5.附件
最后附上测试存储过程以及查询sql
BEGIN
set @i = 0;
set @name = '做个测试';
while @i < 1000000 do
insert into test_int_innodb(name) values(@name);
set @i = @i+1;
end while ;
RETURN null;
END
BEGIN
set @i = 0;
set @name = '做个测试';
while @i < 1000000 do
SET @id = REPLACE(uuid(),'-','');
insert into test_uuid_innodb(id,name) values(@id, @name);
set @i = @i+1;
end while ;
RETURN null;
END
SELECT * FROM test.test_uuid_innodb;
SELECT * FROM test.test_int_innodb where ID='123456';
SELECT * FROM test.test_int_innodb where NAME='做个测试6x5';
select count(1)from test.test_int_innodb;
6.总结
从测试数据上看,就百万级数据来看,UUID并无很明显的简单查询性能问题.Engine类型才是对查询性能影响较大的因素.