Mysql 索引的基础（下）_MySQL

如果需要存储大量的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（）要少很多。