benchmark mysql 普通索引,唯一索引 性能比较

gistfile1.txt

# 表结构
users(login)

# 生成 一千万 条数据
#!/usr/bin/env ruby 
require 'uuid'

uuid = UUID.new

File.open("./data.txt","a+") do |f|
 	10_000_000.times {
           login = uuid.generate
           f.write(%Q("#{login}"\n))
 	}
end

# mysql 导入数据 唯一索引
mysql> load data infile '/usr/local/system/projects/ruby_demo/data.txt' into table users fields terminated by "," optionally enclosed by '"'  lines terminated by "\n" (login);
Query OK, 10000000 rows affected, 65535 warnings (5 min 20.74 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 10000000

# mysql 导入数据  普通索引
mysql> load data infile '/usr/local/system/projects/ruby_demo/data.txt' into table users fields terminated by "," optionally enclosed by '"'  lines terminated by "\n" (login);
Query OK, 10000000 rows affected, 65535 warnings (4 min 47.10 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 10000000

# 唯一索引 查询
mysql> select id,login from users where login='de56e1c0-7c1c-012f-f5a4-3c0754';
+----------+--------------------------------+
| id       | login                          |
+----------+--------------------------------+
| 11048363 | de56e1c0-7c1c-012f-f5a4-3c0754 |
+----------+--------------------------------+
1 row in set (0.04 sec)

# 普通索引查询
mysql> select id,login from users where login="de56e130-7c1c-012f-f5a4-3c0754";
+----------+--------------------------------+
| id       | login                          |
+----------+--------------------------------+
| 11048361 | de56e130-7c1c-012f-f5a4-3c0754 |
+----------+--------------------------------+
1 row in set (0.04 sec)

===================================================================
结论:

一千万条数据,查询单条数据时间大概都在 0.02s ~ 0.04s 之间
导入一千万条数据,唯一索引比普通索引慢了 30s 左右,插入单条数据都是瞬间插入