InnoDB關(guān)鍵特征之自適應(yīng)hash索引

2024-09-07 22:12:42

字體：大中小

供稿：網(wǎng)友

InnoDB關(guān)鍵特性之自適應(yīng)hash索引：

一、索引的資源消耗分析

1、索引三大特點(diǎn)

　　1、?。褐辉谝粋€(gè)到多個(gè)列建立索引

　　2、有序：可以快速定位終點(diǎn)

　　3、有棵樹(shù)：可以定位起點(diǎn)，樹(shù)高一般小于等于3

2、索引的資源消耗點(diǎn)

　　1、樹(shù)的高度，順序訪問(wèn)索引的數(shù)據(jù)頁(yè)，索引就是在列上建立的，數(shù)據(jù)量非常小，在內(nèi)存中；

　　2、數(shù)據(jù)之間跳著訪問(wèn)

　　　　1、索引往表上跳，可能需要訪問(wèn)表的數(shù)據(jù)頁(yè)很多；

　　　　2、通過(guò)索引訪問(wèn)表，主鍵列和索引的有序度出現(xiàn)嚴(yán)重的不一致時(shí)，可能就會(huì)產(chǎn)生大量物理讀；

　　資源消耗最厲害：通過(guò)索引訪問(wèn)多行，需要從表中取多行數(shù)據(jù)，如果無(wú)序的話，來(lái)回跳著找，跳著訪問(wèn)，物理讀會(huì)很嚴(yán)重。

二、自適應(yīng)hash索引原理

1、原理過(guò)程

Innodb存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上二級(jí)索引的查找，如果發(fā)現(xiàn)某二級(jí)索引被頻繁訪問(wèn)，二級(jí)索引成為熱數(shù)據(jù)，建立哈希索引可以帶來(lái)速度的提升，則：

　　1)、自適應(yīng)hash索引功能被打開(kāi)

mysql> show variables like '%ap%hash_index';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_adaptive_hash_index | ON    |

+----------------------------+-------+

1 row in set (0.01 sec)

　　2)、經(jīng)常訪問(wèn)的二級(jí)索引數(shù)據(jù)會(huì)自動(dòng)被生成到hash索引里面去(最近連續(xù)被訪問(wèn)三次的數(shù)據(jù))，自適應(yīng)哈希索引通過(guò)緩沖池的B+樹(shù)構(gòu)造而來(lái)，因此建立的速度很快。

2、特點(diǎn)

　　1、無(wú)序，沒(méi)有樹(shù)高

　　2、降低對(duì)二級(jí)索引樹(shù)的頻繁訪問(wèn)資源

　　　　索引樹(shù)高<=4，訪問(wèn)索引：訪問(wèn)樹(shù)、根節(jié)點(diǎn)、葉子節(jié)點(diǎn)

　　3、自適應(yīng)

3、缺陷

　　1、hash自適應(yīng)索引會(huì)占用innodb buffer pool；

　　2、自適應(yīng)hash索引只適合搜索等值的查詢，如select * from table where index_col='xxx'，而對(duì)于其他查找類型，如范圍查找，是不能使用的；

　　3、極端情況下，自適應(yīng)hash索引才有比較大的意義，可以降低邏輯讀。

三、監(jiān)控與關(guān)閉

1、狀態(tài)監(jiān)控

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：字節(jié)為單位，占用內(nèi)存空間總量

　　2、通過(guò)hash searches、non-hash searches計(jì)算自適應(yīng)hash索引帶來(lái)的收益以及付出，確定是否開(kāi)啟自適應(yīng)hash索引

2、限制

　　1、只能用于等值比較，例如=， <=>，in

　　2、無(wú)法用于排序

　　3、有沖突可能

　　4、MySQL自動(dòng)管理，人為無(wú)法干預(yù)。

3、自適應(yīng)哈希索引的控制

　　由于innodb不支持hash索引，但是在某些情況下hash索引的效率很高，于是出現(xiàn)了adaptive hash index功能，但是通過(guò)上面的狀態(tài)監(jiān)控，可以計(jì)算其收益以及付出，控制該功能開(kāi)啟與否。

　　默認(rèn)開(kāi)啟，建議關(guān)掉，意義不大?？梢酝ㄟ^(guò) set global innodb_adaptive_hash_index=off/on 關(guān)閉和打開(kāi)該功能。

（編輯：武林網(wǎng)）

上一篇：建表和sql不規(guī)范導(dǎo)致服務(wù)器幾乎無(wú)反應(yīng)

下一篇：percona server 日志時(shí)間錯(cuò)誤