摘要：數(shù)據(jù)庫主鍵性能對比： 名稱 存儲長度 生成方式 1. uuid 32+4 uuid()函數(shù) 2. uuid20 20 UUID_SHORT
數(shù)據(jù)庫主鍵性能對比：
名稱 存儲長度 生成方式 1. uuid 32+4 uuid()函數(shù) 2. uuid20 20 UUID_SHORT()函數(shù) 3. bigint自增 20 auto_increment
測試表：id_int（）、
-- uuid測試表 CREATE TABLE `id_uuid` ( `id` varchar(50) NOT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- uuid20測試表 【注：id是bigint unsigned 】 CREATE TABLE `id_uuid20` ( `id` bigint(20) unsigned NOT NULL, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --int自增測試表（我本地使用的是int 可以改成bigint） CREATE TABLE `id_int` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
初始化100w條數(shù)據(jù)的存儲過程：
-- 初始化int自增
CREATE PROCEDURE `int_init`(in s int)
BEGIN
DECLARE ct INT;
SET ct=1;
while ct<s DO
insert into id_int( name) VALUES ( concat('aaa',ct));
set ct=ct+1;
END WHILE;
END
-- 初始化uuid20
CREATE PROCEDURE `uuid20_init`( in s int)
BEGIN
DECLARE ct INT;
SET ct=1;
while ct<s DO
insert into id_uuid20(id ,name) VALUES (uuid_short(), concat('aaa',ct));
set ct=ct+1;
END WHILE;
END
-- 初始化uuid
CREATE PROCEDURE `uuid_init`(in s int)
BEGIN
DECLARE ct INT;
SET ct=1;
while ct<s DO
insert into id_uuid (id, name) VALUES (uuid(), concat('aaa',ct));
set ct=ct+1;
END WHILE;
END
-- 分別調(diào)用
call int_init(1000000);
call uuid20_init(1000000);
call uuid_init(1000000);
數(shù)據(jù)插入過程能發(fā)現(xiàn)int自增的插入速度明顯高出另外兩個，uuid()函數(shù)調(diào)用肯定沒有自增快。不過相較于插入，我更關注查詢的性能對比
count: 長整形的效率明顯高于字符型的
mysql> select count(*) from id_int; +----------+ | count(*) | +----------+ | 2412382 | +----------+ 1 row in set (0.65 sec) mysql> select count(*) from id_uuid; +----------+ | count(*) | +----------+ | 1005356 | +----------+ 1 row in set (6.11 sec) mysql> select count(*) from id_uuid20; +----------+ | count(*) | +----------+ | 1000003 | +----------+ 1 row in set (0.82 sec)
基于主鍵查詢：差別不大
mysql> select * from id_int where id = 555555; +--------+-----------+ | id | name | +--------+-----------+ | 555555 | aaa555538 | +--------+-----------+ 1 row in set (0.08 sec) mysql> select * from id_uuid where id ='e27267ba-a897-11e6-b4a0-742f68b93fab'; +--------------------------------------+-----------+ | id | name | +--------------------------------------+-----------+ | e27267ba-a897-11e6-b4a0-742f68b93fab | aaa550010 | +--------------------------------------+-----------+ 1 row in set (0.02 sec) mysql> select * from id_uuid where id ='e27267ba-a897-11e6-b4a0-742f68b93fab'; +--------------------------------------+-----------+ | id | name | +--------------------------------------+-----------+ | e27267ba-a897-11e6-b4a0-742f68b93fab | aaa550010 | +--------------------------------------+-----------+ 1 row in set (0.00 sec) mysql> select * from id_uuid20 where id = 24811291965678717; +-------------------+-----------+ | id | name | +-------------------+-----------+ | 24811291965678717 | aaa550010 | +-------------------+-----------+ 1 row in set (0.04 sec)
基于name查詢（無索引）：
mysql> select * from id_int where name = 'aaa550010'; +--------+-----------+ | id | name | +--------+-----------+ | 550027 | aaa550010 | +--------+-----------+ 1 row in set (0.93 sec) mysql> select * from id_uuid where name = 'aaa550010'; +--------------------------------------+-----------+ | id | name | +--------------------------------------+-----------+ | e27267ba-a897-11e6-b4a0-742f68b93fab | aaa550010 | +--------------------------------------+-----------+ 1 row in set (6.36 sec) mysql> select * from id_uuid20 where name = 'aaa550010'; +-------------------+-----------+ | id | name | +-------------------+-----------+ | 24811291965678717 | aaa550010 | +-------------------+-----------+ 1 row in set (0.82 sec)
加入索引后
mysql> select * from id_int where name = 'aaa550010'; +--------+-----------+ | id | name | +--------+-----------+ | 550027 | aaa550010 | +--------+-----------+ 1 row in set (0.06 sec) mysql> select * from id_uuid where name = 'aaa550010'; +--------------------------------------+-----------+ | id | name | +--------------------------------------+-----------+ | e27267ba-a897-11e6-b4a0-742f68b93fab | aaa550010 | +--------------------------------------+-----------+ 1 row in set (0.00 sec) mysql> select * from id_int where name = 'aaa550010'; +--------+-----------+ | id | name | +--------+-----------+ | 550027 | aaa550010 | +--------+-----------+ 1 row in set (0.00 sec)
插入操作：
mysql> insert into id_int (name) values('ccccd');
Query OK, 1 row affected (0.02 sec)
mysql> insert into id_uuid20 (id,name) values(uuid_short(),'ccccc');
Query OK, 1 row affected (0.13 sec)
mysql> insert into id_uuid20 (id,name) values(uuid_short(),'cccc1');
Query OK, 1 row affected (0.06 sec)
mysql> insert into id_uuid20 (id,name) values(uuid_short(),'cccc3');
Query OK, 1 row affected (0.07 sec)
mysql> insert into id_int (name) values('cccc1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into id_int (name) values('cccc2');
Query OK, 1 row affected (0.07 sec)
mysql> insert into id_int (name) values('cccc3');
Query OK, 1 row affected (0.07 sec)
mysql> insert into id_uuid20 (id,name) values(5231231231111111,'cccc4');
Query OK, 1 row affected (0.04 sec)
mysql> insert into id_uuid20 (id,name) values(5231231231111112,'cccc5');
Query OK, 1 row affected (0.02 sec)
mysql> insert into id_uuid20 (id,name) values(5531231231111112,'cccc5');
Query OK, 1 row affected (0.07 sec)
mysql> insert into id_uuid (id,name) values(uuid(),'cccc1');
Query OK, 1 row affected (0.07 sec)
mysql> insert into id_uuid (id,name) values(uuid(),'cccc2');
Query OK, 1 row affected (0.05 sec)
mysql> insert into id_uuid (id,name) values(uuid(),'cccc3');
Query OK, 1 row affected (0.03 sec)
mysql> insert into id_uuid (id,name) values('11cccasdqwdeqweqw','cccc4');
Query OK, 1 row affected (0.07 sec)
mysql> insert into id_uuid (id,name) values('12cccasdqwdeqweqw','cccc5');
Query OK, 1 row affected (0.07 sec)