Mysql使用Java UUID作为唯一值时使用前缀索引测试

Mysql可以使用字符串前缀 作为索引 以节约空间。

下面我们以 Java的UUID 生成的 32位(移除UUID中的 中划线)字符串 来做一下 测试。

表结构：

CREATE TABLE `test_uuid` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uuid` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8

UUID生成的方式(不考虑replaceAll的替换效率)：

UUID.randomUUID().toString().replaceAll("-", "")　　

查询 不同长度的前缀索引效率（索引的选择性，即不重复的索引值）的SQL:

select 
count(DISTINCT uuid) / count(*) as total,
count(DISTINCT LEFT(uuid,5)) / count(*) as five,
count(DISTINCT LEFT(uuid,6)) / count(*) as six,
count(DISTINCT LEFT(uuid,7)) / count(*) as seven,
count(DISTINCT LEFT(uuid,8)) / count(*) as eight,
count(DISTINCT LEFT(uuid,9)) / count(*) as nine,
count(DISTINCT LEFT(uuid,10)) / count(*) as ten
from test_uuid;　

下面看一下测试数据及结果：

20W数据

40W

60W

80W

100W

200W

300W

500W

1000W

 

2000W

随着数据量的增多，同样长度的前缀索引选择性 逐渐降低。

前7位 在2000W数据的时候损失了 0.04， 也就是说 每100 条数据， 会有4条与其他96条数据 有重复。

前9位 在2000W数据的时候损失了 0.0001 ，也就是说 每10000 条数据，会有1 条与 其他 9999条有重复。

前10位 在2000W数据的时候 选择行 依然为1 (前10位没有任何重复的)。

大家可以算一下 26的10次方，大概是 141 万 亿。 

当然，以上数据 我只做过一次， 其他测试数据应该与 本次测试数据 稍有不同，但可以肯定的是：不会相差很多。

所以，当业务需要使用 uuid 作为 业务唯一的key时， 可以评估业务数据量，选择合适长度的前缀索引。

前缀索引的选择性 越接近 总长度索引的选择性 时，说明已经可以了。

但是，前缀索引有一些缺点：

1. Mysql 前缀索引 不支持 order by 和 group by 查询。

2 Mysql 前缀索引 不能作为 覆盖索引使用。 

 各位大佬有问题，欢迎交流。