InnoDB引擎是否与内存引擎同步？

Question

我正在探索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;

基于堆栈溢出的一些讨论，我了解到通过设置大于数据集大小的InnoDB参数innodb_buffer_pool_size，可以将数据库加载到内存中。在调整了这个参数之后，我感到失望的是，在大多数情况下，查询的速度甚至没有比MyISAM快(平均3s对0.3s)，比内存慢100倍。

在进一步审查MySQL手册时，它指出了以下两点：

• InnoDB维护一个名为缓冲池的存储区域，用于缓存内存中的数据和索引。
• 缓冲池越大，InnoDB就越像内存中的数据库，只从磁盘读取数据一次，然后在随后的读取过程中从内存中访问数据。

在我看来，innodb_buffer_pool_size更多地用于缓存重复查询的结果，而不是提供运行查询的数据库的热副本。我的理解是正确的，还是遗漏了一些允许InnoDB引擎与内存引擎相匹配的东西，对于不重复的范围查询(如上面的查询)性能而言？

更新：

弗雷德里克·张正确地指出，InnoDB缓冲池确实缓存数据库的热副本。我遗漏的是Rolando提供的链接中的一个加载过程：

在每次启动时，扫描表的全部内容(所有数据和索引页)，为每个表预加载内容，然后用SELECT * FROM <table> ORDER BY <pkey fields>将内容加载到内存中，然后对每个索引进行SELECT <indexed fields> FROM <table> ORDER BY <index fields>。

经过进一步的测试，我能够将查询时间(0.3到1.0)降低到与MyISAM引擎相当的水平。但这仍然比内存引擎的速度慢了7倍。

既然这两个引擎都在从RAM中查询数据库，那么有人能告诉我为什么InnoDB引擎仍然不能适应内存引擎的速度吗？

Answer 1

讽刺的是，我早些时候回答了一个关于InnoDB与内存存储引擎的问题。

关于内存存储引擎，您必须考虑一些非常奇怪的东西。

每次插入、更新和删除时，内存表都会执行完整的表锁。

内存表仍然会触发一个小磁盘I/O，因为内存表的..frm文件是一个磁盘文件，必须与每个查询一起引用，因为该表的存在以及随后的查询解析。

内存表的默认索引类型是散列索引，而不是BTREE。如果忘记声明USING BTREE，则所有范围搜索都会变成表扫描。哈希索引对于完成范围查询的索引来说不是很好的候选。问题体中的查询很快就会成为问题的受害者。

即使使用USING BTREE子句在内存表中创建索引，内存中的BTREE索引也以O(log )的速度增长，因此再次期望磁盘I/O检查.frm文件中的索引定义加上在页面访问上运行的O(log )时间。

在使用内存存储引擎时需要考虑的另一件疯狂的事情是:如果您试图加入一个内存表和一个InnoDB表，那么由此产生的锁行为默认为最坏的锁行为，在本例中是全表锁定。

警告

其他人早在2011年3月就回答了这样的问题

• David对原因是什么*不*如何在MySQL中使用内存存储引擎？的回答
• Morgan对原因是什么*不*如何在MySQL中使用内存存储引擎？的回答

这里有一个关于为什么所有内存数据库是好的还是坏的：让MySQL内存存储引擎利用512 GB内存是否可行？

Answer 2

MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎以及许多其他的。MySQL通过他们的可插拔存储引擎架构来实现这一点。两人都有正反两面。我们可以将核心功能/性能划分为四个方面；

1. 受支持的字段和数据类型。
2. 锁定类型
3. 索引和
4. 交易记录

有些引擎具有独特的功能，也可以驱动您的决策。

内存存储引擎将所有数据存储在内存中；一旦关闭了MySQL服务器，存储在内存数据库中的任何信息都将丢失。但是，保留了各个表的格式，这使您能够创建临时表，这些表可用于存储信息以进行快速访问，而无需每次启动数据库服务器时重新创建这些表。

长期使用内存存储引擎通常不是一个好主意，因为数据很容易丢失。但是，如果您有RAM来支持您正在处理的数据库，那么使用基于内存的表是在大型数据集上运行复杂查询并从性能收益中获益的一种有效方法。

使用内存表的最佳方法是使用SELECT语句从原始的、基于磁盘的表中选择更大的数据集，然后对所需的特定元素进行子分析。

InnoDB引擎支持MyISAM引擎的所有数据库功能(以及更多)，还添加了完全符合ACID (原子性、一致性、隔离性和持久性)和行级数据锁定的事务功能。

InnoDB系统的关键是数据库、缓存和索引结构，其中索引和数据都被缓存在内存中并存储在磁盘上。这使得恢复非常快，甚至可以在非常大的数据集上工作。通过支持行级锁定，您可以将数据添加到InnoDB表中，而无需引擎通过每次插入锁定表，这将加快数据库中信息的恢复和存储。

如果您愿意(并且能够)为您的服务器配置InnoDB设置，那么我建议您花时间优化服务器配置，然后使用InnoDB引擎作为默认设置。

有关更多详细信息，请参阅本文：MySQL存储引擎--它们的局限性和比较尝试