mysql索引创建hash_【Mysql】mysql使用触发器创建hash索引

概述

若设计的数据表中，包含较长的字段，比如URL(通常都会比较长)，查询时需要根据该字段进行过滤：

select * from table_xxx where url = 'xxxxxxx';

为了提高查询性能，通常需要对字段做索引，在Innodb中，如果使用URL做索引，将会导致：

索引存储占用空间大；

索引查询比较性能差；

为了解决上面的问题，有如下解决方案：

对URL做hash，hash作为记录的一个字段，查询时，使用该hash值做过滤；

使用触发器对更新和插入的数据做hash，不需要手动计算hash；

hash碰撞：如果表中记录条数很多，则应该选择合适的hash，避免大量的hash碰撞；

hash碰撞： 查询时，除了使用hash进行过滤，还需要使用url进行过滤；

创建数据表 + 触发器进行hash运算

假设需要使用如下查询：

select id from table where url='http://xxx.xxx.com/xxxxxxxxxxxxxxxxxxxx';

分析：

URL通常比较长，如果在URL上创建索引，无论是存储和检索，效率都会非常低；

可以对URL做Hash，在该HASH值进行索引，这样的查询性能会非常高；

** 使用触发器创建表和hash索引**

CREATE TABLE `url_hash` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`url` varchar(255) COLLATE utf8_bin NOT NULL,

`url_crc` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `url_crc_idx` (`url_crc`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DELIMITER //

CREATE TRIGGER `urlhash_crc_insert` BEFORE INSERT ON `url_hash` FOR EACH ROW

set NEW.url_crc=crc32(NEW.url);

END;

//

CREATE TRIGGER `urlhash_crc_update` BEFORE UPDATE ON `url_hash` FOR EACH ROW

set NEW.url_crc=crc32(NEW.url);

END;

//

DELIMITER ;

说明：

DELIMITER ：先修改一下语句分隔符，这样就可以在触发器中使用分号；

在insert和update前设置触发器，对每一条记录的URL做crc32 Hash运算；

验证

插入数据

insert into url_hash(url) values('http://www.baidu.com');

insert into url_hash(url) values('http://www.cnblogs.com/ssslinppp');

*结果

select * from url_hash;

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

| id | url | url_crc |

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

| 1 | http://www.baidu.com | 3500265894 |

| 2 | http://www.cnblogs.com/ssslinppp | 3363624100 |

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

如何查询

select * from url_hash where url='http://www.baidu.com' and url_crc=crc32('http://www.baidu.com');

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

| id | url | url_crc |

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

| 1 | http://www.baidu.com | 3500265894 |

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

注意点：

查询时，必须将url作为过滤条件，因为可以产生hash碰撞；

选择合适的Hash计算方式

不要使用sha1()和md5作为hash函数

因为上面两个计算的hash值通常都会特别长，浪费空间，比较时也会更慢；

数据表非常大，不建议使用crc32()进行hash计算

crc32()返回的是32位的整数，因为所谓的生日悖论，当数据表非常大时，将会产生大量的hash碰撞；

当条数>9.3w时，碰撞的概率：1%；

自定义hash函数

返回一个64位的整数(不是字符串)就可以；

比如：fnv64()函数作为hash函数(改函数默认不包含，可以移植进来)；