欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Mysql 索引的基础(下)_MySQL

程序员文章站 2022-05-10 18:30:13
...
如果需要存储大量的URL并需要根据URL进行搜索查找。如果使用B-Tree 来存储URL,存储的内容就会很大,因为URL本身都很长。正常情况下会有如下查询:

SELECT id FROM url WHERE url="http://www.baidu.com";

若删除原来URL上的索引,而新增一个被索引的url_crc列,使用CRC32做hash ,就可以用下面的方式查询:

SELECT id FROM url WHERE url='http://www.baidu.com' AND rul_crc=CRC32('http://www.baidu.com');

这样做性能非常高,因为MySQL 优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。即使有多个相同的索引值,查找任然很快,只需要根据hash值做快速的整数比较就能找到索引条目,然后一一返回对应的行。另外一种方式就是对完整的URL字符串做索引,那样会非常慢。

这样实现的缺陷是需要维护hash值。可以手动维护,可以触发器实现。如果采用这种方式,记住,不要使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的hash值时非常长的字符串,会浪费更大的空间,比较时也会更慢。SHA1()和MD5()是强加密函数,设计目标是最大限度的消除冲突,蛋这里并不需要这样搞的要求。简单hash函数的冲突在一个可以接受的范围,同事有能提供更好的性能。

如果数据表非常大,CRC32()会出现大量的hash冲突,则可以考虑自己实现一个简单的64位hash函数。这个自定义的函数要返回整数,而不是字符串。一个简单的办法可以使用MD5()函数返回值的一部分来作为自定义hash函数。这肯能比自己写一个hash算法的性能要差,不过这样实现最简单。

SELECT CONV(RIGHT(MD5('http://www.baidu.com'),16),16,10) AS HASH64.

处理hash冲突。当使用hash索引进行查询的时候,必须在WHERE子句中包含常量值:

SELECT id from url WHERE url=crc32('http://www.baidu.com') AND url='http://www.baidu.com';

一旦出现hash冲突,另一个字符串的hash值也恰好是相同的,则下面的语句是无法正确工作的:

SELECT id from url WHERE url=crc32('http://www.baidu.com');

因为所谓的‘生日悖论’ 出现hash冲突的概率的增长率可能比想象的要快的多,CRC32()返回的是32位整数,当索引有9.3W条记录时,出现冲突的概率是1%。例如,我们将'/usr/share/dic/words' 中的词倒数数据表,并进行crc32()计算,最后会有98569行。这就已经出现一次hash冲突了。要避免hash冲突问题,必须在WHERE 条件中带入hahs值和对应的列值。如果不是想查询具体的值,例如只是统计记录数(不精确的),则可以不带入列值,直接使用crc32()的hash值查询即可。还可以使用FNV64()函数作为hash函数,hash值为64位,速度非常快,且冲突比crc32()要少很多。

相关标签: 索引 基础