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

时间:2022-09-24 15:24:31

概述

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

select * from table_xxx  where url = 'xxxxxxx';

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

  • 索引存储占用空间大;
  • 索引查询比较性能差;

为了解决上面的问题,有如下解决方案:

  1. 对URL做hash,hash作为记录的一个字段,查询时,使用该hash值做过滤;
  2. 使用触发器对更新和插入的数据做hash,不需要手动计算hash;
  3. hash碰撞:如果表中记录条数很多,则应该选择合适的hash,避免大量的hash碰撞;
  4. 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 |
+----+----------------------+------------+

注意点:

  1. 查询时,必须将url作为过滤条件,因为可以产生hash碰撞;

选择合适的Hash计算方式

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

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

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

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

当条数>9.3w时,碰撞的概率:1%;

自定义hash函数

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

比如:fnv64()函数作为hash函数(改函数默认不包含,可以移植进来);

【Mysql】mysql使用触发器创建hash索引的更多相关文章

  1. 【转载】Mysql中的Btree与Hash索引比较

    转载地址:http://www.jb51.net/article/62533.htm 这篇文章主要介绍了Mysql中的Btree与Hash索引比较,本文起讲解了B-Tree 索引特征.Hash 索引特 ...

  2. 警惕 InnoDB 和 MyISAM 创建 Hash 索引陷阱

    MySql 最经常使用存储引擎 InnoDB 和 MyISAM 都不支持 Hash 索引,它们默认的索引都是 B-Tree.可是假设你在创建索引的时候定义其类型为 Hash,MySql 并不会报错,并 ...

  3. mysql触发器与hash索引

    url查询哈希值的维护 触发器 2.1 创建表 pseudohash. 2.2 创建触发器,当对表进行插入和更新时,触发 触发器 delimiter |create trigger pseudohas ...

  4. mysql InnoDB引擎是否支持hash索引

    看一下mysql官方文档:https://dev.mysql.com/doc/refman/5.7/en/create-index.html , 从上面的图中可以得知,mysql 是支持hash索引的 ...

  5. Mysql中的Btree与Hash索引

    B-Tree 索引特征 B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上.而且还可以用于LIKE操作符,只要它的查询条件是一个不以通配符开头的 ...

  6. MySQL的btree索引和hash索引的区别

    Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-T ...

  7. MySQL索引类型 btree索引和hash索引的区别

    来源一 Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 ...

  8. mysql索引hash索引和b-tree索引的区别

    Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-T ...

  9. MySQL的btree索引和hash索引的区别 (转)

    Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-T ...

随机推荐

  1. excel下划线转驼峰公式

    最近为了省事,决定从excel将表结构生成jquery.datatable的json对象结构,其中要把下划线转驼峰,如下: =LEFT(C251,1)&MID(SUBSTITUTE(PROPE ...

  2. fiddler抓取手机数据包

    百度经验:http://jingyan.baidu.com/article/d8072ac4605905ec95cefda0.html

  3. jQuery加载外部文件的方式get、post、ajax、load的区别及异步加载的实现

    一.$.post(url, [data], [callback], [type])  url (String) : 发送请求的URL地址. data (Map) : (可选) 要发送给服务器的数据,以 ...

  4. 【LeetCode OJ】Word Ladder II

    Problem Link: http://oj.leetcode.com/problems/word-ladder-ii/ Basically, this problem is same to Wor ...

  5. vmware虚拟机上网:NAT搭建局域网

    若是你不知道的情况下,可以编辑虚拟机网络配置,然后恢复默认,vmware会自动给你分配好ip,默认使用的是vmware8,下面的是使用默认的配置 看图 注意:子网的ip一定要在如上图所示的范围 适配器 ...

  6. 文件I&sol;O(不带缓冲)之read函数

    调用read函数从打开文件中读数据. #include <unistd.h> ssize_t read( int filedes, void *buf, size_t nbytest ); ...

  7. Android优秀开源项目

    本文转自:http://blog.tisa7.com/android_open_source_projects Android优秀开源项目 Android经典的开源项目其实非常多,但是国内的博客总是拿 ...

  8. Oracle Day07 PL&sol;SQL基础

    1.基本格式 set serveroutput on declare -- 申明部分 name ); begin -- 执行的sql语句 ; dbms_output.put_line(name); e ...

  9. 循环队列和链式队列(C&plus;&plus;实现)

    循环队列: 1.循环队列中判断队空的方法是判断front==rear,队满的方法是判断front=(rear+1)%maxSize.(我曾经想过为什么不用一个length表示队长,当length==m ...

  10. &lbrack; 转 &rsqb; RESTful

    一.什么是RESTful 定义: REST全程是Representational State Transfer,表述性状态转移.它首次出现在2000年Roy Fielding的博士论文中,Roy Fi ...