InnoDB关键特性之自适应hash索引

动画资讯 拿铁 1年前 (2017-07-28) 8514次浏览 0个评论

一、索引的资源消耗分析

1、索引三大特点

  1、小:只在一个到多个列建立索引

  2、有序:可以快速定位终点

  3、有棵树:可以定位起点,树高一般小于等于 3

2、索引的资源消耗点

  1、树的高度,顺序访问索引的数据页,索引就是在列上建立的,数据量非常小,在内存中;

  2、数据之间跳着访问

    1、索引往表上跳,可能需要访问表的数据页很多;

    2、通过索引访问表,主键列和索引的有序度出现严重的不一致时,可能就会产生大量物理读;

  资源消耗最厉害:通过索引访问多行,需要从表中取多行数据,如果无序的话,来回跳着找,跳着访问,物理读会很严重。

二、自适应 hash 索引原理

1、原理过程

 

  Innodb 存储引擎会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,二级索引成为热数据,建立哈希索引可以带来速度的提升,则:

  1、自适应 hash 索引功能被打开

复制代码
mysql> show variables like '%ap%hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)
复制代码

  2、经常访问的二级索引数据会自动被生成到 hash 索引里面去(最近连续被访问三次的数据),自适应哈希索引通过缓冲池的 B+树构造而来,因此建立的速度很快。

2、特点

  1、无序,没有树高

  2、降低对二级索引树的频繁访问资源

    索引树高<=4,访问索引:访问树、根节点、叶子节点

  3、自适应

3、缺陷

  1、hash 自适应索引会占用 innodb buffer pool;

  2、自适应 hash 索引只适合搜索等值的查询,如 select * from table where index_col=’xxx’,而对于其他查找类型,如范围查找,是不能使用的;

  3、极端情况下,自适应 hash 索引才有比较大的意义,可以降低逻辑读。

三、监控与关闭

1、状态监控

mysql> show engine innodb status\G
……
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

  1、34673:字节为单位,占用内存空间总量

  2、通过 hash searches、non-hash searches 计算自适应 hash 索引带来的收益以及付出,确定是否开启自适应 hash 索引

2、限制

  1、只能用于等值比较,例如=, <=>,in

  2、无法用于排序

  3、有冲突可能

  4、MySQL 自动管理,人为无法干预。

3、自适应哈希索引的控制

  由于 innodb 不支持 hash 索引,但是在某些情况下 hash 索引的效率很高,于是出现了 adaptive hash index 功能,但是通过上面的状态监控,可以计算其收益以及付出,控制该功能开启与否。

  默认开启,建议关掉,意义不大。可以通过 set global innodb_adaptive_hash_index=off/on 关闭和打开该功能。


昱音资讯 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:InnoDB 关键特性之自适应 hash 索引
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址