mysql 模拟哈希索引

/ 技术相关 / 0 条评论 / 110浏览

mysql

在 mysql 中，只有 Memory 引擎显示支持哈希索引。Memory 引擎同时也支持 B- Tree 索引。

哈希索引优缺点

1、哈希索引只包含哈希值和行指针，不存储字段值。 2、哈希索引不是按照索引值顺序存储的，故无法排序。 3、哈希索引也不支持匹配查找。 4、哈希索引只支持等值比较查询。 5、哈希索引访问数据非常快。 6、哈希冲突很多的话，维护成本比较高。

在有些特殊场景，比如超高数据量下我们需要做 「按邮箱/URL 精准匹配查询」，既想使用到索引，又不想索引占用太多空间，这时候模拟 hash 他就可以解决我们的问题。

模拟哈希索引优缺点


优点：key_len 占用少
缺点：数据库需要额外增加字段，需要自己维护 hash；

模拟哈希索引在数据库中如何操作

首先创建 test_person 表，创建 email，email_crc 两个字段，email 为我们的待优化字段，email_crc 为我们的 hash 索引字段，hash 函数使用 crc32。

模拟哈希索引 key_len 消耗情况

数据前期准备，数据量 1000 万。

创建表

DROP TABLE IF EXISTS test_person;

CREATE TABLE `test_person` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `surname` VARCHAR(20) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `age` TINYINT(3) UNSIGNED NOT NULL,
  `sex` TINYINT(1) UNSIGNED NOT NULL,
  `email` varchar(255) NULL,
  `email_crc` int UNSIGNED NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

创建插入和更新触发器

# 创建插入触发器
CREATE TRIGGER person_ins
    BEFORE INSERT
    ON test_person
    FOR EACH ROW
BEGIN
    SET NEW.email_crc = crc32(NEW.email);
END;

# 创建更新触发器
CREATE TRIGGER person_upd
    BEFORE UPDATE
    ON test_person
    FOR EACH ROW
BEGIN
    SET NEW.email_crc = crc32(NEW.email);
END;

批量插入数据

DROP PROCEDURE IF EXISTS generate_email_crc;
CREATE PROCEDURE generate_email_crc(IN num int)
BEGIN
    DECLARE surname_chars VARCHAR(512) DEFAULT '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄曲家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴鬱胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍卻璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逯盖益桓公';
    DECLARE compound_surname_chars VARCHAR(255) DEFAULT '万俟司马上官欧阳夏侯诸葛闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐钟离宇文长孙慕容鲜于闾丘司徒司空丌官司寇仉督子车颛孙端木巫马公西漆雕乐正壤驷公良拓跋夹谷宰父谷梁晋楚闫法汝鄢涂钦段干百里东郭南门呼延归海羊舌微生岳帅缑亢况郈有琴梁丘左丘东门西门商牟佘佴伯赏南宫墨哈谯笪年爱阳佟';
    DECLARE name_chars VARCHAR(10) DEFAULT '甲乙丙丁';
    DECLARE email_chars VARCHAR(65) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

    DECLARE id INT UNSIGNED;
    DECLARE surname VARCHAR(10) DEFAULT '';
    DECLARE name VARCHAR(25) DEFAULT '';
    DECLARE email VARCHAR(35) DEFAULT '';

    DECLARE len INT UNSIGNED;

    DELETE FROM test_person;

    SET id=1;
    WHILE id <= num DO
        # 随机取单姓和复姓
        SET len = FLOOR(RAND()*2);
        SET surname = '';
        IF len = 0 THEN
            SET surname = SUBSTR(surname_chars,FLOOR(1 + RAND()*408),1);
        ELSE
            SET len = FLOOR(RAND()*151);
            IF len % 2 = 0 && len != 1 THEN
                SET len = len + 1;
            END IF;
            SET surname = SUBSTR(compound_surname_chars,len,2);
        END IF;

        # 随机取名字
        SET len = FLOOR(1 + RAND()*2);
        SET name = '';
        WHILE len >= 1 DO
            SET name = CONCAT(name,SUBSTR(name_chars,FLOOR(1 + RAND()*4),1));
            SET len = len - 1;
        END WHILE;

        SET len = FLOOR(1 + RAND()*25);
        SET email = '';
        WHILE len >= 1 DO
            SET email = CONCAT(email,substring(email_chars,FLOOR(1 + RAND()*62),1));
            SET len = len - 1;
        END WHILE;
        SET email = CONCAT(email,'@qq.com');

        INSERT INTO test_person VALUES (id, surname, name, FLOOR(RAND()*100), FLOOR(RAND()*2), email, crc32(email));
        SET id = id + 1;
    END WHILE;
END

使用 email 字段做索引

explain 之后如下：

EXPLAIN SELECT * from test_person
WHERE email = 'Hvh@qq.com';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	test_person		ref	idx_email	idx_email	1022	const	77	100.00

使用 email_crc 字段做索引

explain 情况如下：

EXPLAIN SELECT * from test_person
WHERE email_crc = CRC32('Hvh@qq.com');

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE test_person ref idx_email_crc idx_email_crc 4 const 77 100.00

可以看到索引 key_len 有明显区别。由于 hash 存在冲突情况，查询时需 email，email_crc 一起使用。

SELECT * from test_person
WHERE email = 'Hvh@qq.com' AND email_crc = CRC32('Hvh@qq.com')

本站文章除注明转载/出处外，均为作者原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2023/03/09 12:33