高性能的MySQL（5）创建高性能的索引一哈希索引

最新推荐文章于 2022-08-08 21:15:00 发布

Oooover

最新推荐文章于 2022-08-08 21:15:00 发布

阅读量799

点赞数

分类专栏： Mysql Linux 文章标签： MySQL 哈希索引

Linux 同时被 2 个专栏收录

117 篇文章 0 订阅

订阅专栏

Mysql

23 篇文章 0 订阅

订阅专栏

哈希索引（hash index）基于哈希表实现，只有精确匹配索引的所有列的查询才有效，对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，不同键值的行计算出来的哈希码也不一样，哈希码保存在哈希索引中，同时哈希表中保存指向每个数据的指针。

1、Memory引擎支持哈希索引，也支持B-Tree索引，而且支持非唯一的哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目，这个是和特别的。

举例说明：

 
        CREATE  
        TABLE  
        `testhash` ( 
       
        `fname`  
        varchar 
        (50)  
        NOT  
        NULL 
        , 
       
        `lname`  
        varchar 
        (50)  
        NOT  
        NULL 
        , 
       
        KEY  
        `fname` (`fname`) USING HASH 
       
        ) ENGINE=MEMORY  
        DEFAULT  
        CHARSET=utf8 |

假设索引使用f()生成哈希码如下

f('Arjen') = 2323

f('Baron') = 7437

f('Peter') = 8784

f('Vadim') = 2458

则哈希索引数据结构如下

槽	值
2323	指向第1行指针
2458	指向第4行指针
7437	指向第2行指针
8784	指向第3行指针

注意哈希码是有序的，但是数据行不是。

当执行查询的时候

 
   
        select  
        *  
        from  
        testhash  
        where  
        fname= 
        'Peter' 
        ； 
       
 
 

先计算哈希码，然后找到第3行指针，最后比较第3行的值是否为‘Peter’，以确定就是要找的行。

2、哈希索引的限制：

a、哈希索引只包含哈希码和行指针，不存储字段值，所以无法用索引中的值来避免去读取行。

b、哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

c、哈希索引也不支持部分索引列匹配查找，必须利用所有索引列，因为哈希值是通过所有索引列计算的。

d、哈希索引只支持等值比较查询，包括=、in()、<=>(安全比较)比较包含null的时候用。哈希也不支持任何范围查询，比方说where price > 100

e、哈希索引非常快，除非有哈希冲突（不同的索引值会有相同的哈希值），这个时候引擎必须遍历链表中的所有行来匹配。

f、哈希冲突较多的时候，比方列上相同的值比较多的时候，索引维护代价就会比较高。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，由引擎内部实现，也可以关闭。

3、创建自定义哈希索引

如果存储引擎不支持哈希索引，可以在B-Tree基础上创建一个伪哈希索引。这个和真正的哈希索引不是一回事，还是用到B-Tree进行查找，只是利用键值的哈希值而不是键值来进行索引查找，只需要在where中手动指定哈希函数。

举例说明：

如果需要存储大量的URL，并且需要根据URL进行搜索，如果使用B-Tree来索引URL，存储内容会很大。比方说下面的查询

 
   
        select  
        *  
        from  
        url  
        where  
        url= 
        "http://www.baidu.com" 
        ; 
       
 
 

若删除原来的URL列索引，而新增一个被索引的字段url_crc,使用crc32做哈希就可以使用下面的查询了

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

这样性能就会很高。

这样的缺陷是需要维护哈希值。可以使用触发器来实现维护工作。

创建一张表

 
   
        CREATE  
        TABLE  
        `pseudohash` ( 
       
 
           
        `id`  
        int 
        (10) unsigned  
        NOT  
        NULL  
        AUTO_INCREMENT, 
       
 
           
        `url`  
        varchar 
        (255)  
        NOT  
        NULL 
        , 
       
 
           
        `url_crc`  
        int 
        (10) unsigned  
        NOT  
        NULL  
        DEFAULT  
        '0' 
        , 
       
 
           
        PRIMARY  
        KEY  
        (`id`) 
       
 
        ) ENGINE=InnoDB  
        DEFAULT  
        CHARSET=utf8; 
       
 
 

创建触发器

 
   
        //插入 
       
 
        delimiter $$ 
       
 
        create  
        trigger  
        pseudohash_crc_ins before  
        insert  
        on  
        pseudohash  
        for  
        each row  
        begin  
        set 
        NEW.url_crc=crc32(NEW.url); 
        end 
        ;$$ 
       
 
        //更新 
       
 
        create  
        trigger  
        pseudohash_crc_upd before  
        update  
        on  
        pseudohash  
        for  
        each row  
        begin  
        set 
        NEW.url_crc=crc32(NEW.url); 
        end 
        ;$$ 
       
 
        delimiter ; 
       
 
 