MySQL哈希索引以及InnoDB自适应哈希索引-优快云博客

MySQL哈希索引的原理与使用

哈希索引通过哈希表实现，利用哈希函数计算索引键的哈希值，快速定位数据位置。适合等值查询，时间复杂度接近O(1)。

创建哈希索引的示例：

CREATE TABLE user_hash (
    id INT NOT NULL,
    username VARCHAR(50),
    INDEX USING HASH (username)
) ENGINE=MEMORY;

哈希索引的局限性：

仅支持等值查询（=、IN），不支持范围查询（>、<）
无法用于排序操作
存在哈希冲突可能影响性能
MEMORY引擎支持，InnoDB不支持原生哈希索引

InnoDB自适应哈希索引（AHI）

InnoDB的自适应哈希索引是内部自动创建的索引结构，监控表索引的使用模式，动态构建哈希索引加速查询。

查看AHI状态：

SHOW ENGINE INNODB STATUS;

AHI相关参数配置：

# my.cnf配置
innodb_adaptive_hash_index=ON  # 启用AHI
innodb_adaptive_hash_index_parts=8  # AHI分区数

AHI使用场景：

频繁访问的索引值
查询模式相对固定的场景
等值查询占主导的应用

性能对比测试

创建测试表：

CREATE TABLE perf_test (
    id INT PRIMARY KEY,
    data VARCHAR(255),
    INDEX idx_data (data)
) ENGINE=InnoDB;

使用B-tree索引查询：

SELECT * FROM perf_test WHERE data = 'test_value';

模拟AHI生效后的查询（需多次执行使AHI建立）：

-- 执行多次相同查询
SELECT * FROM perf_test WHERE data = 'test_value';

监控与优化

查看AHI命中率：

SELECT 
    (SELECT variable_value FROM sys.metrics WHERE variable_name = 'adaptive_hash_searches') as searches,
    (SELECT variable_value FROM sys.metrics WHERE variable_name = 'adaptive_hash_searches_btree') as btree_searches,
    (SELECT searches - btree_searches) as ahi_hits,
    ROUND((SELECT searches - btree_searches)/searches*100,2) as hit_rate
FROM dual;

AHI调优建议：

对于OLTP系统通常保持开启
高并发写入场景可考虑关闭
调整innodb_adaptive_hash_index_parts减少锁争用
监控hit_rate确保AHI有效工作

实现自定义哈希索引

在InnoDB中模拟哈希索引：

CREATE TABLE custom_hash_index (
    hash_key BIGINT UNSIGNED PRIMARY KEY,
    original_id INT,
    INDEX (original_id)
);

-- 插入数据时计算哈希值
INSERT INTO custom_hash_index 
VALUES (CRC32('user123'), 1);

使用自定义哈希索引查询：

SELECT t.* 
FROM main_table t
JOIN custom_hash_index h ON t.id = h.original_id
WHERE h.hash_key = CRC32('user123');

这种方案牺牲了写入性能换取查询速度，适合读多写少的场景。