AUTOID 与 UUID 性能测试

更改 Mysql 配置

在 my.cnf 文件末尾添加如下配置

max_allowed_packet=100M
innodb_buffer_pool_size=2G

自增ID测试表的建表SQL

CREATE TABLE `test1s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hashed_id` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_test1_on_hashed_id` (`hashed_id`),
  KEY `index_test1s_on_name` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

UUID 测试表的建表SQL

CREATE TABLE `test2s` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_test2s_on_name` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

每次插入100W条数据，插入10次，共1000W条数据，每次插入时间对比

                          user     system      total        real
autoid insert 1th(M):   0.100000   0.060000   0.160000 ( 15.519051)
autoid insert 2th(M):   0.130000   0.040000   0.170000 ( 18.436685)
autoid insert 3th(M):   0.090000   0.050000   0.140000 ( 18.134797)
autoid insert 4th(M):   0.070000   0.090000   0.160000 ( 18.576962)
autoid insert 5th(M):   0.070000   0.040000   0.110000 ( 19.521584)
autoid insert 6th(M):   0.050000   0.030000   0.080000 ( 20.520616)
autoid insert 7th(M):   0.090000   0.030000   0.120000 ( 20.975899)
autoid insert 8th(M):   0.050000   0.040000   0.090000 ( 22.163973)
autoid insert 9th(M):   0.070000   0.030000   0.100000 ( 21.911627)
autoid insert 10th(M):  0.050000   0.030000   0.080000 ( 23.018506)
uuid insert 1th(M):     0.110000   0.050000   0.160000 ( 15.847269)
uuid insert 2th(M):     0.060000   0.070000   0.130000 ( 15.641427)
uuid insert 3th(M):     0.080000   0.040000   0.120000 ( 15.027765)
uuid insert 4th(M):     0.050000   0.080000   0.130000 ( 15.359175)
uuid insert 5th(M):     0.090000   0.080000   0.170000 ( 15.245869)
uuid insert 6th(M):     0.060000   0.090000   0.150000 ( 15.235896)
uuid insert 7th(M):     0.090000   0.080000   0.170000 ( 15.360716)
uuid insert 8th(M):     0.060000   0.090000   0.150000 ( 15.374005)
uuid insert 9th(M):     0.090000   0.080000   0.170000 ( 15.311570)
uuid insert 10th(M):    0.060000   0.090000   0.150000 ( 15.395394)

测试结果

• 数据插入性能方面 UUID 略胜一筹

1000W条数据，逐条查询总时间对比

name 有非主键索引，hashed_id 有唯一性索引

                          user     system      total        real
autoid/hashed_id:       978.910000 147.270000 1126.180000 (1736.307611)
uuid/id:                945.670000 143.720000 1089.390000 (1541.445782)
autoid/name:            950.880000 142.000000 1092.880000 (1961.690465)
uuid/name:              921.100000 136.620000 1057.720000 (1888.261413)

测试结果

• UUID 做主键查询会比 hashed_id 要快那么一点点
• name 等非主键索引字段的查询，UUID 优于 AUTOID

整个测试的代码

在 Rails Console 里面执行。

ActiveRecord::Base.logger = ActiveSupport::Logger.new('/dev/null')
ActiveRecord::Base.connection.execute "drop table test1s"
ActiveRecord::Base.connection.execute "drop table test2s"
ActiveRecord::Base.connection.execute "reset query cache"

ActiveRecord::Base.connection.execute <<-SQL
CREATE TABLE `test1s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hashed_id` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_test1_on_hashed_id` (`hashed_id`),
  KEY `index_test1s_on_name` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQL

ActiveRecord::Base.connection.execute <<-SQL
CREATE TABLE `test2s` (
  `id` binary(16) NOT NULL DEFAULT '',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_test2s_on_name` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQL

ActiveRecord::Base.connection.execute <<-SQL
CREATE TRIGGER `set_test2s_id_as_uuid` BEFORE INSERT ON `test2s`
FOR EACH ROW
BEGIN
  SET NEW.id = UNHEX(REPLACE(UUID(),'-',''));
END
SQL

autoid_contents, uuid_contents = [], []
10.times do |a|
  autoid_values, uuid_values = [], []
  1000000.times do |b|
    autoid_values << "('#{SecureRandom.hex(8)}','test#{a}_#{b}')"
    uuid_values   << "('test#{a}_#{b}')"
  end
  autoid_contents << "INSERT INTO test1s (hashed_id, name) VALUES #{autoid_values.join(',')}"
  uuid_contents << "INSERT INTO test2s (name) VALUES #{uuid_values.join(',')}"
end

Benchmark.bm do |x|
  autoid_contents.each_with_index do |sql1, index|
    x.report("autoid insert #{index + 1}th(M):") {
      ActiveRecord::Base.connection.execute(sql1)
    }
  end

  uuid_contents.each_with_index do |sql2, index|
    x.report("uuid insert #{index + 1}th(M):") {
      ActiveRecord::Base.connection.execute(sql2)
    }
  end
end

hashed_ids = ActiveRecord::Base.connection.execute("SELECT hashed_id FROM test1s").to_a.flatten
uuids = ActiveRecord::Base.connection.execute("SELECT id FROM test2s").map do |array|
  array.first.unpack('H*').first
end
names = ActiveRecord::Base.connection.execute("SELECT name FROM test1s").to_a.flatten
ActiveRecord::Base.connection.execute "reset query cache"

# 此处需重启 Mysql，重置 buffer。
Benchmark.bm do |x|
  x.report('autoid/hashed_id:') {
    hashed_ids.each do |hashed_id|
      ActiveRecord::Base.connection.execute("SELECT * FROM test1s WHERE hashed_id = '#{hashed_id}' LIMIT 1")
    end
  }
end

# 此处需重启 Mysql，重置 buffer。
Benchmark.bm do |x|
  x.report('uuid/id:') {
    uuids.each do |uuid|
      ActiveRecord::Base.connection.execute("SELECT * FROM test2s WHERE id = x'#{uuid}' LIMIT 1")
    end
  }
end

# 此处需重启 Mysql，重置 buffer。
Benchmark.bm do |x|
  x.report('autoid/name:') {
    names.each do |name|
      ActiveRecord::Base.connection.execute("SELECT * FROM test1s WHERE name = '#{name}'")
    end
  }
end

# 此处需重启 Mysql，重置 buffer。
Benchmark.bm do |x|
  x.report('uuid/name:') {
    names.each do |name|
      ActiveRecord::Base.connection.execute("SELECT * FROM test2s WHERE name = '#{name}'")
    end
  }
end