mysql创建自定义伪hash索引

电玩女神 2023-06-28 06:21 15阅读 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 ;
> 
> ![20200107194557449.jpg][]
> 
> **从执行计划中可以看出，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哈希之后的值的数字型的。
> 
> ![20200107195525695.png][]
> 
> ### 伪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、新增数据，查看结果**
> 
> ![20200107203129226.png][]
> 
> **4、查看执行计划**
> 
>     explain select url_crc32 from my_resource order by url_crc32;
> 
> key\_len长度为8，打完收工
> 
> ![20200107203741502.png][]
> 
> **4、hash索引的缺点**
> 
> 1）hash不能处理范围比较，只能处理等值比较。
> 
> 2）hash不能做排序，hash出来的结果是随机分布的。(上例只是为了看效果，切勿做排序)
> 
> 3）hash不支持部分索引，如index a(10)就不支持。
> 
> 4）hash无法覆盖索引
> 
> 5）hash有碰撞，碰撞得比较厉害时，处理碰撞的代价就比较高。

[20200107194557449.jpg]: https://img-blog.csdnimg.cn/20200107194557449.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200107194656981.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDQ5Nzcz,size_16,color_FFFFFF,t_70
[20200107195525695.png]: https://img-blog.csdnimg.cn/20200107195525695.png
[20200107203129226.png]: https://img-blog.csdnimg.cn/20200107203129226.png
[20200107203741502.png]: https://img-blog.csdnimg.cn/20200107203741502.png