mysql中可以顯式添加哈希索引的場景僅限于memory存儲引擎，1.創建memory表時通過using hash語法指定主鍵或輔助索引；2.對已有memory表使用alter table添加哈希索引。對于innodb等磁盤引擎，無法手動創建哈希索引，但其內部會自動管理自適應哈希索引（ahi）以優化頻繁訪問的數據。哈希索引適用于精確等值查詢、無范圍和排序需求、數據量小且穩定的內存表，具有o(1)的查找效率，但不支持范圍查詢、排序和前綴匹配，且存在哈希沖突和內存消耗等局限性。
mysql中添加哈希索引，通常指的是針對MEMORY存儲引擎的表，可以通過USING HASH語法來創建。對于InnoDB或MyISAM等磁盤存儲引擎，MySQL并不直接提供顯式的CREATE HASH INDEX語法讓用戶手動創建哈希索引。相反，InnoDB引擎內部會根據訪問模式自動創建和管理一種稱為“自適應哈希索引”（Adaptive Hash Index, AHI）的機制，來加速頻繁訪問的數據。哈希索引的使用場景主要集中在需要快速進行精確等值查找的場景，尤其是在數據量相對穩定且查詢模式單一的內存表中。
解決方案
如果你是想在MEMORY表上創建哈希索引，語法非常直接。例如，你有一個名為lookup_table的MEMORY表，想要在id列上創建哈希索引：
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為這個索引使用哈希算法。
但對于InnoDB或MyISAM表，你無法這樣顯式地添加哈希索引。InnoDB的自適應哈希索引是其存儲引擎內部的一個優化，它會監控對索引頁的訪問模式，如果發現某些索引值被頻繁訪問，它就會在內存中為這些值創建哈希索引，以加速查找。這個過程是完全自動的，我們作為用戶無法直接控制它的創建、刪除或配置，也無法通過SHOW INDEXES命令看到它。它更像是一種內部的緩存機制，而不是一個我們能直接操作的數據庫對象。
MySQL中哈希索引與B-Tree索引的主要區別是什么？
我個人覺得，理解這兩者的根本差異，是選擇索引策略的基石。哈希索引和B-Tree索引在結構、查詢方式和適用場景上有著顯著的不同。
首先是結構和查找機制。B-Tree索引是一種平衡樹結構，數據是按順序存儲的。查找時，MySQL會從根節點開始，沿著樹的分支向下遍歷，直到找到目標數據。這個過程類似于在字典里查找單詞，每次都能排除掉一半的可能，所以查找效率是O(logN)。而哈希索引則像是一個散列表，它通過對索引列的值計算哈希碼，直接映射到數據行在存儲中的物理位置。理論上，只要沒有哈希沖突，哈希索引的查找速度是O(1)，也就是常數時間，非常快。
其次是查詢類型支持。B-Tree索引由于其有序性，不僅支持精確等值查找（=），還非常擅長范圍查詢（>、 100這樣的查詢，哈希索引是無能為力的。
最后是存儲和內存使用。B-Tree索引通常會占用更多的磁盤空間，因為它們需要存儲樹的結構和指向數據行的指針。在內存中，它們也需要維護整個樹的結構。哈希索引在內存中通常表現為哈希表，對于MEMORY表來說，如果哈希沖突較少，它的內存效率可能很高。但如果沖突很多，每個哈希桶下掛的鏈表會變長，查找效率就會下降，并且可能需要更多的內存來存儲這些鏈表。對于InnoDB的自適應哈希索引，它只在內存中存在，不持久化到磁盤，因此重啟數據庫后會重新構建。
什么時候應該考慮使用MySQL的哈希索引（或依賴其內部機制）？
說實話，對于InnoDB，我們能做的就是優化好B-Tree索引，然后把自適應哈希索引（AHI）的優化交給MySQL自己。但了解它的工作原理，能讓我們更安心，知道某些查詢為什么會突然變快。
具體到我們能控制的哈希索引，也就是MEMORY表的情況，主要考慮以下場景：
- 精確等值查找非常頻繁：這是哈希索引的“主場”。如果你有一個表，絕大多數查詢都是形如select * FROM table WHERE id = 123這種精確匹配，并且這個表的數據量不大，且經常被訪問，那么哈希索引能提供極高的性能。
- 作為查找表（Lookup Table）：MEMORY表通常被用作存儲一些不經常變動、需要快速查找的配置信息、映射關系或枚舉值。比如，一個存儲國家代碼和國家名稱的表，或者一個存儲錯誤碼和錯誤描述的表。這些表的數據量通常不大，而且查詢模式單一，就是根據ID或代碼查找對應的描述。
- 無需范圍查詢和排序：如果你的業務邏輯對這個表完全沒有范圍查詢、排序或者模糊匹配的需求，那么哈希索引的劣勢就不會暴露出來。
對于InnoDB的AHI，我們雖然不能控制，但可以知道，當你的B-Tree索引上的某些熱點數據行被反復訪問時，AHI會嘗試介入，在內存中為這些熱點數據構建一個哈希索引，從而繞過B-Tree的遍歷過程，直接定位到數據。這在OLTP（在線事務處理）系統中，對于那些高并發、重復查詢的場景，能帶來顯著的性能提升。你不需要為此做任何額外配置，它是InnoDB引擎的智能優化。
MySQL哈希索引有哪些局限性或潛在的性能陷阱？
我見過不少開發者，一聽說哈希索引快，就想當然地用，結果發現根本不是那么回事。了解它的短板，比只知道它的優點更重要。
哈希索引的局限性非常明顯，而且這些局限性往往是導致性能問題的“陷阱”：
- 不支持范圍查詢：這是最致命的局限。WHERE id > 100、WHERE name LIKE ‘A%’、WHERE created_at BETWEEN ‘…’ AND ‘…’這類查詢，哈希索引完全無法使用。它只能處理=或IN操作。如果你在MEMORY表上創建了哈希索引，但執行了范圍查詢，MySQL會退化為全表掃描，性能會非常差。
- 不支持排序：哈希索引不存儲有序信息，因此不能用于ORDER BY子句。如果查詢需要排序，即使有哈希索引，也需要額外的排序操作。
- 不支持前綴匹配：LIKE ‘abc%’這樣的查詢也無法利用哈希索引，因為它需要遍歷前綴匹配的范圍，而哈希索引無法提供這種能力。
- 哈希沖突的影響：當不同的鍵值經過哈希函數計算后得到相同的哈希碼時，就會發生哈希沖突。哈希索引通常通過鏈表來解決沖突，這意味著在發生沖突的情況下，查找效率會從理想的O(1)退化到O(N)，其中N是沖突鏈表的長度。如果數據分布不均勻，或者哈希函數選擇不當，可能導致某些哈希桶下的鏈表過長，從而嚴重影響性能。
- 內存消耗：對于MEMORY表，哈希索引需要將整個索引結構加載到內存中。如果索引的列值非常多且唯一，或者哈希沖突嚴重導致鏈表過長，可能會消耗大量內存。一旦內存不足，可能會導致性能下降甚至系統不穩定。
- 非持久化（針對MEMORY表）：MEMORY表的數據和索引都存儲在內存中，這意味著MySQL服務重啟后，數據和索引都會丟失。這使得MEMORY表不適合存儲需要持久化的數據。
- InnoDB自適應哈希索引的不可控性：雖然AHI是自動的性能優化，但它的工作原理和觸發條件對用戶是透明的。在某些高并發、高更新的場景下，AHI的維護成本可能會抵消其帶來的收益，甚至可能導致性能抖動。我們無法手動關閉或調整它（只能通過innodb_adaptive_hash_index參數全局開啟或關閉，但通常不建議關閉）。
