mysql中可以顯式添加哈希索引的場景僅限于memory存儲(chǔ)引擎，1.創(chuàng)建memory表時(shí)通過using hash語法指定主鍵或輔助索引；2.對已有memory表使用alter table添加哈希索引。對于innodb等磁盤引擎，無法手動(dòng)創(chuàng)建哈希索引，但其內(nèi)部會(huì)自動(dòng)管理自適應(yīng)哈希索引（ahi）以優(yōu)化頻繁訪問的數(shù)據(jù)。哈希索引適用于精確等值查詢、無范圍和排序需求、數(shù)據(jù)量小且穩(wěn)定的內(nèi)存表，具有o(1)的查找效率，但不支持范圍查詢、排序和前綴匹配，且存在哈希沖突和內(nèi)存消耗等局限性。

MySQL中添加哈希索引，通常指的是針對MEMORY存儲(chǔ)引擎的表，可以通過USING HASH語法來創(chuàng)建。對于InnoDB或MyISAM等磁盤存儲(chǔ)引擎，MySQL并不直接提供顯式的CREATE HASH INDEX語法讓用戶手動(dòng)創(chuàng)建哈希索引。相反，InnoDB引擎內(nèi)部會(huì)根據(jù)訪問模式自動(dòng)創(chuàng)建和管理一種稱為“自適應(yīng)哈希索引”（Adaptive Hash Index, AHI）的機(jī)制，來加速頻繁訪問的數(shù)據(jù)。哈希索引的使用場景主要集中在需要快速進(jìn)行精確等值查找的場景，尤其是在數(shù)據(jù)量相對穩(wěn)定且查詢模式單一的內(nèi)存表中。

解決方案

如果你是想在MEMORY表上創(chuàng)建哈希索引，語法非常直接。例如，你有一個(gè)名為lookup_table的MEMORY表，想要在id列上創(chuàng)建哈希索引：

CREATE TABLE lookup_table (
    id INT NOT NULL,
    value VARCHAR(255),
    PRIMARY KEY (id) USING HASH
) ENGINE=MEMORY;

-- 或者在已有的MEMORY表上添加哈希索引
ALTER TABLE lookup_table ADD INDEX idx_value (value) USING HASH;

這里USING HASH明確告訴MySQL為這個(gè)索引使用哈希算法。

但對于InnoDB或MyISAM表，你無法這樣顯式地添加哈希索引。InnoDB的自適應(yīng)哈希索引是其存儲(chǔ)引擎內(nèi)部的一個(gè)優(yōu)化，它會(huì)監(jiān)控對索引頁的訪問模式，如果發(fā)現(xiàn)某些索引值被頻繁訪問，它就會(huì)在內(nèi)存中為這些值創(chuàng)建哈希索引，以加速查找。這個(gè)過程是完全自動(dòng)的，我們作為用戶無法直接控制它的創(chuàng)建、刪除或配置，也無法通過SHOW INDEXES命令看到它。它更像是一種內(nèi)部的緩存機(jī)制，而不是一個(gè)我們能直接操作的數(shù)據(jù)庫對象。

MySQL中哈希索引與B-Tree索引的主要區(qū)別是什么？

我個(gè)人覺得，理解這兩者的根本差異，是選擇索引策略的基石。哈希索引和B-Tree索引在結(jié)構(gòu)、查詢方式和適用場景上有著顯著的不同。

首先是結(jié)構(gòu)和查找機(jī)制。B-Tree索引是一種平衡樹結(jié)構(gòu)，數(shù)據(jù)是按順序存儲(chǔ)的。查找時(shí)，MySQL會(huì)從根節(jié)點(diǎn)開始，沿著樹的分支向下遍歷，直到找到目標(biāo)數(shù)據(jù)。這個(gè)過程類似于在字典里查找單詞，每次都能排除掉一半的可能，所以查找效率是O(logN)。而哈希索引則像是一個(gè)散列表，它通過對索引列的值計(jì)算哈希碼，直接映射到數(shù)據(jù)行在存儲(chǔ)中的物理位置。理論上，只要沒有哈希沖突，哈希索引的查找速度是O(1)，也就是常數(shù)時(shí)間，非?？?。

其次是查詢類型支持。B-Tree索引由于其有序性，不僅支持精確等值查找（=），還非常擅長范圍查詢（>、 100這樣的查詢，哈希索引是無能為力的。

最后是存儲(chǔ)和內(nèi)存使用。B-Tree索引通常會(huì)占用更多的磁盤空間，因?yàn)樗鼈冃枰鎯?chǔ)樹的結(jié)構(gòu)和指向數(shù)據(jù)行的指針。在內(nèi)存中，它們也需要維護(hù)整個(gè)樹的結(jié)構(gòu)。哈希索引在內(nèi)存中通常表現(xiàn)為哈希表，對于MEMORY表來說，如果哈希沖突較少，它的內(nèi)存效率可能很高。但如果沖突很多，每個(gè)哈希桶下掛的鏈表會(huì)變長，查找效率就會(huì)下降，并且可能需要更多的內(nèi)存來存儲(chǔ)這些鏈表。對于InnoDB的自適應(yīng)哈希索引，它只在內(nèi)存中存在，不持久化到磁盤，因此重啟數(shù)據(jù)庫后會(huì)重新構(gòu)建。

什么時(shí)候應(yīng)該考慮使用MySQL的哈希索引（或依賴其內(nèi)部機(jī)制）？

說實(shí)話，對于InnoDB，我們能做的就是優(yōu)化好B-Tree索引，然后把自適應(yīng)哈希索引（AHI）的優(yōu)化交給MySQL自己。但了解它的工作原理，能讓我們更安心，知道某些查詢?yōu)槭裁磿?huì)突然變快。

具體到我們能控制的哈希索引，也就是MEMORY表的情況，主要考慮以下場景：

• 精確等值查找非常頻繁：這是哈希索引的“主場”。如果你有一個(gè)表，絕大多數(shù)查詢都是形如SELECT * FROM table WHERE id = 123這種精確匹配，并且這個(gè)表的數(shù)據(jù)量不大，且經(jīng)常被訪問，那么哈希索引能提供極高的性能。
• 作為查找表（Lookup Table）：MEMORY表通常被用作存儲(chǔ)一些不經(jīng)常變動(dòng)、需要快速查找的配置信息、映射關(guān)系或枚舉值。比如，一個(gè)存儲(chǔ)國家代碼和國家名稱的表，或者一個(gè)存儲(chǔ)錯(cuò)誤碼和錯(cuò)誤描述的表。這些表的數(shù)據(jù)量通常不大，而且查詢模式單一，就是根據(jù)ID或代碼查找對應(yīng)的描述。
• 無需范圍查詢和排序：如果你的業(yè)務(wù)邏輯對這個(gè)表完全沒有范圍查詢、排序或者模糊匹配的需求，那么哈希索引的劣勢就不會(huì)暴露出來。

對于InnoDB的AHI，我們雖然不能控制，但可以知道，當(dāng)你的B-Tree索引上的某些熱點(diǎn)數(shù)據(jù)行被反復(fù)訪問時(shí)，AHI會(huì)嘗試介入，在內(nèi)存中為這些熱點(diǎn)數(shù)據(jù)構(gòu)建一個(gè)哈希索引，從而繞過B-Tree的遍歷過程，直接定位到數(shù)據(jù)。這在OLTP（在線事務(wù)處理）系統(tǒng)中，對于那些高并發(fā)、重復(fù)查詢的場景，能帶來顯著的性能提升。你不需要為此做任何額外配置，它是InnoDB引擎的智能優(yōu)化。

MySQL哈希索引有哪些局限性或潛在的性能陷阱？

我見過不少開發(fā)者，一聽說哈希索引快，就想當(dāng)然地用，結(jié)果發(fā)現(xiàn)根本不是那么回事。了解它的短板，比只知道它的優(yōu)點(diǎn)更重要。

哈希索引的局限性非常明顯，而且這些局限性往往是導(dǎo)致性能問題的“陷阱”：

• 不支持范圍查詢：這是最致命的局限。WHERE id > 100、WHERE name LIKE 'A%'、WHERE created_at BETWEEN '...' AND '...'這類查詢，哈希索引完全無法使用。它只能處理=或IN操作。如果你在MEMORY表上創(chuàng)建了哈希索引，但執(zhí)行了范圍查詢，MySQL會(huì)退化為全表掃描，性能會(huì)非常差。
• 不支持排序：哈希索引不存儲(chǔ)有序信息，因此不能用于ORDER BY子句。如果查詢需要排序，即使有哈希索引，也需要額外的排序操作。
• 不支持前綴匹配：LIKE 'abc%'這樣的查詢也無法利用哈希索引，因?yàn)樗枰闅v前綴匹配的范圍，而哈希索引無法提供這種能力。
• 哈希沖突的影響：當(dāng)不同的鍵值經(jīng)過哈希函數(shù)計(jì)算后得到相同的哈希碼時(shí)，就會(huì)發(fā)生哈希沖突。哈希索引通常通過鏈表來解決沖突，這意味著在發(fā)生沖突的情況下，查找效率會(huì)從理想的O(1)退化到O(N)，其中N是沖突鏈表的長度。如果數(shù)據(jù)分布不均勻，或者哈希函數(shù)選擇不當(dāng)，可能導(dǎo)致某些哈希桶下的鏈表過長，從而嚴(yán)重影響性能。
• 內(nèi)存消耗：對于MEMORY表，哈希索引需要將整個(gè)索引結(jié)構(gòu)加載到內(nèi)存中。如果索引的列值非常多且唯一，或者哈希沖突嚴(yán)重導(dǎo)致鏈表過長，可能會(huì)消耗大量內(nèi)存。一旦內(nèi)存不足，可能會(huì)導(dǎo)致性能下降甚至系統(tǒng)不穩(wěn)定。
• 非持久化（針對MEMORY表）：MEMORY表的數(shù)據(jù)和索引都存儲(chǔ)在內(nèi)存中，這意味著MySQL服務(wù)重啟后，數(shù)據(jù)和索引都會(huì)丟失。這使得MEMORY表不適合存儲(chǔ)需要持久化的數(shù)據(jù)。
• InnoDB自適應(yīng)哈希索引的不可控性：雖然AHI是自動(dòng)的性能優(yōu)化，但它的工作原理和觸發(fā)條件對用戶是透明的。在某些高并發(fā)、高更新的場景下，AHI的維護(hù)成本可能會(huì)抵消其帶來的收益，甚至可能導(dǎo)致性能抖動(dòng)。我們無法手動(dòng)關(guān)閉或調(diào)整它（只能通過innodb_adaptive_hash_index參數(shù)全局開啟或關(guān)閉，但通常不建議關(guān)閉）。

以上就是mysql怎么添加哈希索引 mysql創(chuàng)建哈希索引的使用場景的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！