MySQL crc32 & crc64函数 提高字符串查询效率

一、CRC32概念

CRC全称为Cyclic Redundancy Check,又叫循环冗余校验。CRC32是CRC算法的一种,常用于校验网络上传输的文件。

二:MySQL里如何利用CRC32来加快查询???

CRC32的基本特征

#1.CRC32函数返回值的范围是0-4294967296(2的32次方减1)

#2.相比MD5,CRC32函数很容易碰撞

CRC32的使用场景

由上述两个基本特性可知,MySQL CRC32 生成整型结果使用用bigint存储,而MD5需要varchar来存储。但是CRC32很容易碰撞,这适合做索引么?

场景:我们在做一个爬虫,对于一个URL,先去数据库里查询是否存在,如果不存在,插入到数据库中。大家都知道这种类型应用表会增长非常迅速,如果简单的

SELECT * FROM urls WHERE url = 'http://wwww.shopperplus.com';

会每次全表扫描,效率非常低。如果在url列上面加索引会快一些,但由于url是varchar类型,字段本身的存储空间和索引占用的存储空间都比较大。

如果加一个crc32_url列,并且只在这个列上加索引,索引空间就会小很多,并使用整型加速查询速度避免字符串url全表扫描。

SELECT * FROM urls WHERE crc_url = 907060870;

上面这种做法一定不可以,因为前面已经提到crc32函数会产生碰撞,也就是说值为907060870的不止有hello。一个小技巧是只使用crc32列来过滤:

SELECT * FROM urls WHERE crc_url = 907060870 AND url = 'hello';

这样一来,大部分查询还是只需要扫描一行就获得结果。对于少部分碰撞的记录,只需要多扫描几行也可以正确获得结果。 url的场景从varchar到bigint的优化其实效果不是特别明显。另一个例子是文本,假如我们有一个text类型的字段(文章内容、评论、微博之类),每次插入之前要判断一下这个内容是否在数据库里存在了。如果使用crc32的技巧,改善的空间还是蛮大的。

三、crc32 缺点容易发生碰撞,有没有更好的方案?

答案是肯定的 -> crc64

crc64() 这个函数补充了MySQL的crc32()函数,其结果是在大量值上分布不均匀。crc64()算法依赖于MD5作为底层机制。

crc64()安装: 需要安装 common_schema database函数库,安装 common_schema库对db要求: MySQL 5.1, 5.5 and 5.6, Oracle distribution, Percona Server and MariaDB.

common_schema 下载: common_schema-2.2.sql

https://code.google.com/archive/p/common-schema/downloads

支持2种安装方式:

# mysql安装

SOURCE /home/ssd/software/common_schema-2.2.sql

#shell 安装

mysql < /home/ssd/software/common_schema-2.2.sql

common_schema-2.2安装成功效果

common_schema-2.2安装成功效果

# 验证安装成功: 多了一个common_schema 证明安装成功

show databases;

多了一个common_schema 证明安装成功

多了一个common_schema 证明安装成功

# crc64()使用:

mysql> SELECT common_schema.crc64('mysql') AS crc64;

+---------------------+

| crc64 |

+---------------------+

| 9350511318824990686 |

+---------------------+

参考:

https://blog.tanteng.me/2016/02/crc32-mysql-index/

http://shopperplus.github.io/blog/2015/01/10/crc32-hash-usage-in-mysql.html

crc64()文档:

https://shlomi-noach.github.io/common_schema/crc64.html

https://shlomi-noach.github.io/common_schema/documentation.html

common_schema2.2 安装:

https://shlomi-noach.github.io/common_schema/install.html

common_schema2.2 下载:

https://shlomi-noach.github.io/common_schema/download.html

https://code.google.com/archive/p/common-schema/

https://code.google.com/archive/p/common-schema/downloads

posted @   Tom1997  阅读(824)  评论(0编辑  收藏  举报
编辑推荐:
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
阅读排行:
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!
点击右上角即可分享
微信分享提示