InnoDB 引擎是否與 Memory Engine 相提並論？

我正在探索 MySQL 5.5.18 版本中不同數據庫引擎的效率，以查看哪個最適合用於 500 萬行數據集的範圍查詢：

SELECT P.col1, P.col2, P.col3, P.col4, P.col5, P.col6, P.col7, P.col8, P.col9
, P.col10, P.col10 * R.col3 as 'combi' 
FROM PRODUCT P INNER JOIN RATE R ON R.col2 = P.col2 
WHERE P.col3 = 'y' 
AND P.col4 >= 1000 
AND P.col5 >= 5 
AND P.col6 BETWEEN 10 AND 100 
AND P.col7 >= 0 
AND P.col8 >= 7 
AND P.col9 >= NOW() 
AND P.col10 * R.col3 BETWEEN 50 AND 80
ORDER BY P.col8 DESC LIMIT 100;

基於對Stackoverflow 的一些討論，我了解到可以通過將InnoDB參數設置innodb_buffer_pool_size為大於數據集的大小來將數據庫載入到 RAM 中。在調整了這個參數之後，我很失望的是，在大多數情況下（平均 3 秒對 0.3 秒）查詢速度甚至不比MyISAM快，並且比****Memory慢 100 倍。

進一步查看MySQL 手冊後，它陳述了以下兩點：

• InnoDB 維護一個稱為緩衝池的儲存區域，用於在記憶體中記憶體數據和索引。
• 緩衝池越大，InnoDB越像記憶體數據庫，從磁碟讀取一次數據，然後在後續讀取期間從記憶體中訪問數據。

在我看來，innodb_buffer_pool_size更多的是用於記憶體重複查詢的結果，而不是提供從中執行查詢的數據庫的熱副本。我的理解是正確的還是我錯過了一些可以讓 InnoDB 引擎匹配記憶體引擎的東西，對於非重複範圍查詢（如上述）的性能方面？

更新：

Frederick Cheung 正確地指出，InnoDB 緩衝池確實記憶體了數據庫的熱副本。我錯過的是在 Rolando 給出的連結中找到的載入過程：

在每次啟動時掃描表的全部內容（所有數據和索引頁），以將內容預載入到記憶體中，SELECT * FROM <table> ORDER BY <pkey fields>然後是每個表，然後是SELECT <indexed fields> FROM <table> ORDER BY <index fields>每個索引。

經過進一步測試，我能夠將查詢時間（0.3 到 1.0 秒）降低到與MyISAM引擎相當的水平。但這仍然比使用Memory Engine獲得的速度慢七倍左右。

由於兩個引擎都在從 RAM 查詢數據庫，誰能告訴我為什麼InnoDB引擎仍然沒有跟上記憶體引擎的速度？

具有諷刺意味的是，我之前回答了一個關於InnoDB 與 MEMORY 儲存引擎的問題。

您必須考慮有關MEMORY 儲存引擎的一些非常奇怪的問題。

每次有 INSERT、UPDATE 和 DELETE 時，MEMORY 表都會執行全表鎖。

MEMORY 表仍然會觸發少量磁碟 I/O，因為 MEMORY 表的 .frm 文件是一個磁碟文件，只要表的存在和之後的查詢解析，每個查詢都必須引用該文件。

MEMORY 表的預設索引類型是 HASH 索引而不是 BTREE。如果您忘記聲明USING BTREE，所有範圍搜尋都會變成表掃描。HASH 索引不適合用於滿足範圍查詢的索引。您在問題正文中的查詢將很快成為受害者。

即使您使用該USING BTREE子句在 MEMORY 表中創建索引，RAM 中的 BTREE 索引也會以 O(log n) 的速度增長，因此希望磁碟 I/O 再次檢查 .frm 文件中的索引定義加上 O(log n)頁面訪問的執行時間。

使用 MEMORY 儲存引擎時要考慮的另一件瘋狂的事情是：如果您嘗試連接 MEMORY 表和 InnoDB 表，則生成的鎖定行為預設為最差的鎖定行為，在這種情況下是全表鎖定。

警告

其他人在 2011 年 3 月回答了這樣的問題

• David Splllett 對在 MySQL 中使用 MEMORY 儲存引擎的原因是什麼不的回答？
• Morgan Tocker 對在 MySQL 中使用 MEMORY 儲存引擎的原因是什麼不的回答？

這是關於為什麼全記憶體數據庫好壞的一個原因：讓 MySQL 記憶體儲存引擎利用 512 GB 的 RAM 是否可行？

MySQL 儲存引擎包括處理事務安全表的引擎和處理非事務安全表的引擎以及許多其他引擎。MySQL 通過他們的 Pluggable Storage Engine Architecture 做到這一點。兩者都有一些優點和缺點。我們可以將核心功能/性能分為四個領域；

1. 支持的欄位和數據類型
2. 鎖定類型
3. 索引和
4. 交易

一些引擎具有獨特的功能，也可以推動您的決定。

MEMORY儲存引擎將 所有數據儲存在記憶體中；一旦 MySQL 伺服器關閉，儲存在 MEMORY 數據庫中的任何資訊都將失去。但是，會保留各個表的格式，這使您能夠創建可用於儲存資訊以便快速訪問的臨時表，而不必在每次啟動數據庫伺服器時重新創建表。

長期使用 MEMORY 儲存引擎通常不是一個好主意，因為數據很容易失去。但是，如果您有 RAM 來支持您正在處理的數據庫，那麼使用基於 MEMORY 的表是在大型數據集上執行複雜查詢並受益於性能提升的一種有效方式。

使用 MEMORY 表的最佳方法是使用 SELECT 語句從基於磁碟的原始表中選擇更大的數據集，然後針對所需的特定元素對該資訊進行子分析。

InnoDB 引擎支持 MyISAM 引擎的所有數據庫功能（以及更多），還添加了完整的事務功能（具有完整的 ACID（原子性、一致性、隔離性和持久性）合規性）和數據的行級鎖定。

InnoDB 系統的關鍵是數據庫、記憶體和索引結構，其中索引和數據都記憶體在記憶體中以及儲存在磁碟上。這可以實現非常快速的恢復，甚至可以在非常大的數據集上工作。通過支持行級鎖定，您可以將數據添加到 InnoDB 表，而無需引擎在每次插入時鎖定表，這加快了數據庫中資訊的恢復和儲存。

如果您願意（並且能夠）為您的伺服器配置 InnoDB 設置，那麼我建議您花時間優化您的伺服器配置，然後使用 InnoDB 引擎作為預設引擎。

有關更多詳細資訊，請參閱本文：MySQL 儲存引擎 – 它們的限制和比較嘗試

引用自：https://dba.stackexchange.com/questions/11099