mysql创建自定义伪hash索引-蒲公英云

mysql创建自定义伪hash索引

电玩女神 2023-06-28 06:21 36阅读 0赞

innodb存储引擎中使用hash索引的原因：（innodb中无法使用hash索引，只由btree索引）

-- 创建资源表
CREATE TABLE `my_resource`  (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `resource_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '资源名称',
  `type` int(2) NOT NULL DEFAULT 0 COMMENT '资源类型 0其他 1视频 2文件',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '资源地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 在url字段上创建索引，用于检索
create index idx_url on my_resource (url asc);
-- 新增数据
insert into my_resource (resource_name,type,url) values('庆余年_1',1,'https://v.qq.com/x/cover/rjae621myqca41h/Cz4BRyhUY3.html');
insert into my_resource (resource_name,type,url) values('庆余年_2',1,'https://v.qq.com/x/cover/rjae621myqca41h/tlTt4CiBc1.html');
insert into my_resource (resource_name,type,url) values('庆余年_3',1,'https://v.qq.com/x/cover/rjae621myqca41h/QdTUsz40Cb.html');
insert into my_resource (resource_name,type,url) values('庆余年_4',1,'https://v.qq.com/x/cover/rjae621myqca41h/dFdlXmOd9n.html');
insert into my_resource (resource_name,type,url) values('庆余年_5',1,'https://v.qq.com/x/cover/rjae621myqca41h/TsvFR6b8wg.html');
insert into my_resource (resource_name,type,url) values('庆余年_6',1,'https://v.qq.com/x/cover/rjae621myqca41h/YgWeaPrYpc.html');
-- 查询执行计划
explain select url from my_resource order by url ;

从执行计划中可以看出，key中用到了索引idx_url，但是索引长度为767，而索引优化中，索引长度是越短越好，

原因：索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大—增删改变慢,并间影响查询速度.
所以, 要在区分度 + 长度两者上,取得一个平衡.

解决方法：截取不同长度,并测试其区分度

-- 截取url字段长度，从1开始截取，计算字符前缀没有重复的字符占全部数据的比例
select count(distinct right(url,15))/count(*) from my_resource;

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz_size_16_color_FFFFFF_t_70

区分百分比达到1是最最理想的

自定义伪hash索引：（核心内容）

前提
在数据类型上考虑，自然想到，是不是可以把记录比较字段从字符串的比较，改成数字的比较？
这是个优化的方向。在计算机里底层数据都是01010这样，只需要把数字换算成0101就可以做等值比较了，但是变成字符，需要先去字符编码表找到字符对应的数字，在把数字换算成0101，这里多出一步查找操作。另一方面字符占用的空间比数字要大很多，一个页内能存下的item条目比数字的要少，这会导致更多的数据页读取。

根据这个方向，尝试使用自定义HASH索引，常见的HASH函数有MD5,crc32,sha1等，只有crc32哈希之后的值的数字型的。

伪hash索引创建操作：
1、在表里加个字段记录hash之后的值，并对这个字段加上索引（innodb的btree索引）。
-- 添加 url的伪hash索引值存储字段  bigint类型
ALTER TABLE my_resource ADD COLUMN `url_crc32` bigint(10) NOT NULL COMMENT 'url的伪hash索引值存储字段' AFTER `url`;
-- 创建该字段索引
create index idx_url_crc32 on my_resource (url_crc32 asc);
--删除 url的索引
drop index idx_url on my_resource;
2、创建触发器
delimiter $$
CREATE TRIGGER my_resource_url_crc32_trigger BEFORE INSERT ON my_resource FOR EACH ROW 
BEGIN 
SET NEW.url_crc32 = crc32(NEW.url);-- url通过crc32计算后将结果赋值给url_crc
END; $$
delimiter ;
3、新增数据，查看结果

4、查看执行计划
explain select url_crc32 from my_resource order by url_crc32;
key_len长度为8，打完收工

4、hash索引的缺点

1）hash不能处理范围比较，只能处理等值比较。

2）hash不能做排序，hash出来的结果是随机分布的。(上例只是为了看效果，切勿做排序)

3）hash不支持部分索引，如index a(10)就不支持。

4）hash无法覆盖索引

5）hash有碰撞，碰撞得比较厉害时，处理碰撞的代价就比较高。