怎么给字符串字段加索引?

先上结论:

方法有以下:

一、 使用前缀索引 

可以根据以下方法确定合适的前缀长度(前缀索引很可能会损失区分度,所以需要预先设定一个可以接受的损失比例,比如 5%)
select count(distinct left(email,4))as L4 from SUser;

优点:如果定义好长度,可以做到既节省空间,又不用额外增加太多的查询成本

缺点:1.使用不了覆盖索引(所以前缀索引如果要做复合索引最好放最右)

   2.可能会因为区分度扫描更多行

 

二、倒序存储(特定场景下可以提高区分度)

 

三、使用hash字段(在表上再创建一个整数字段,来保存校验码)

 

二、三两者做比较:

1. 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。

但是倒序存储方式使用 4 个字节的前缀长度往往是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消。

【占用空间差不多】

 

2.在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。

【CPU消耗 二比三稍小】

 

3.从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

【查询效率上,三比二好】

 

传送门:【付费课程】

https://time.geekbang.org/column/article/71492

 

posted @ 2020-03-07 15:46  钺览IT  阅读(201)  评论(0编辑  收藏