如何使 MySQL 数据库完全在内存中运行？

How do I make a MySQL database run completely in memory?

提问by John Hoffman

I noticed that my database server supports the Memory database engine. I want to make a database I have already made running InnoDB run completely in memory for performance.

我注意到我的数据库服务器支持 Memory 数据库引擎。我想让一个我已经运行 InnoDB 的数据库完全在内存中运行以提高性能。

How do I do that? I explored PHPMyAdmin, and I can't find a "change engine" functionality.

我怎么做？我探索了 PHPMyAdmin，但找不到“更改引擎”功能。

采纳答案by PinnyM

Assuming you understand the consequences of using the MEMORY engine as mentioned in comments, and here, as well as some others you'll find by searching about (no transaction safety, locking issues, etc) - you can proceed as follows:

假设您了解使用 MEMORY 引擎的后果，如评论、此处以及您通过搜索（无事务安全、锁定问题等）会发现的其他一些内容 - 您可以按以下步骤操作：

MEMORY tables are stored differently than InnoDB, so you'll need to use an export/import strategy. First dump each table separately to a file using SELECT * FROM tablename INTO OUTFILE 'table_filename'. Create the MEMORY database and recreate the tables you'll be using with this syntax: CREATE TABLE tablename (...) ENGINE = MEMORY;. You can then import your data using LOAD DATA INFILE 'table_filename' INTO TABLE tablenamefor each table.

MEMORY 表的存储方式与 InnoDB 不同，因此您需要使用导出/导入策略。首先使用SELECT * FROM tablename INTO OUTFILE 'table_filename'. 创建内存数据库，并重新创建你将使用此语法使用的表：CREATE TABLE tablename (...) ENGINE = MEMORY;。然后，您可以使用LOAD DATA INFILE 'table_filename' INTO TABLE tablenamefor 每个表导入数据。

回答by Jotschi

It is also possible to place the MySQL data directory in a tmpfsin thus speeding up the database write and read calls. It might not be the most efficient way to do this but sometimes you can't just change the storage engine.

也可以将 MySQL 数据目录放在tmpfs中，从而加快数据库写入和读取调用。这可能不是执行此操作的最有效方法，但有时您不能只更改存储引擎。

Here is my fstab entry for my MySQL data directory

这是我的 MySQL 数据目录的 fstab 条目

none            /opt/mysql/server-5.6/data  tmpfs   defaults,size=1000M,uid=999,gid=1000,mode=0700          0       0

I also wrote a post that explains the setup in more detail. I use this setup for database tests.

我还写了一篇文章，更详细地解释了设置。我将此设置用于数据库测试。

http://jotschi.de/2014/02/03/high-performance-mysql-testdatabase/

http://jotschi.de/2014/02/03/high-performance-mysql-testdatabase/

You may also want to take a look at the innodb_flush_log_at_trx_commit=2setting. Maybe this will speedup your MySQL sufficently.

您可能还想查看innodb_flush_log_at_trx_commit=2设置。也许这会充分加速你的 MySQL。

innodb_flush_log_at_trx_commit changes the mysql disk flush behaviour. When set to 2 it will only flush the buffer every second. By default each insert will cause a flush and thus cause more IO load.

innodb_flush_log_at_trx_commit 更改 mysql 磁盘刷新行为。当设置为 2 时，它只会每秒刷新缓冲区。默认情况下，每次插入都会导致刷新，从而导致更多的 IO 负载。

回答by Joseph Lust

Memory Engine is not the solution you're looking for. You lose everything that you went to a database for in the first place (i.e. ACID).

内存引擎不是您正在寻找的解决方案。您首先丢失了进入数据库的所有内容（即 ACID）。

Here are some better alternatives:

这里有一些更好的选择：

1. Don't use joins - very few large apps do this (i.e Google, Flickr, NetFlix), because it sucks for large sets of joins.

1. 不要使用连接 - 很少有大型应用程序会这样做（例如 Google、Flickr、NetFlix），因为它对于大量的连接来说很糟糕。

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

-The MySQL Manual

LEFT [OUTER] JOIN 可以比等效的子查询更快，因为服务器可能能够更好地优化它——这一事实不仅仅针对 MySQL 服务器。

- MySQL 手册

1. Make sure the columns you're querying against have indexes. Use EXPLAIN to confirm they are being used.
2. Use and increase your Query_Cache and memory space for your indexes to get them in memory and store frequent lookups.
3. Denormalize your schema, especially for simple joins (i.e. get fooId from barMap).

2. 确保您查询的列有索引。使用 EXPLAIN 确认它们正在被使用。
3. 使用并增加索引的 Query_Cache 和内存空间，以将它们放入内存并存储频繁的查找。
4. 非规范化您的架构，尤其是对于简单连接（即从 barMap 获取 fooId）。

The last point is key. I used to love joins, but then had to run joins on a few tables with 100M+ rows. No good. Better off insert the data you're joining against into that target table (if it's not too much) and query against indexed columns and you'll get your query in a few ms.

最后一点是关键。我曾经喜欢连接，但后来不得不在具有 1 亿多行的几个表上运行连接。不好。最好将您要加入的数据插入该目标表（如果不是太多）并查询索引列，您将在几毫秒内得到您的查询。

I hope those help.

我希望那些帮助。

回答by Kevin Bedell

If your database is small enough (or if you add enough memory) your database will effectively run in memory since it your data will be cached after the first request.

如果您的数据库足够小（或者如果您添加足够的内存），您的数据库将有效地在内存中运行，因为您的数据将在第一次请求后被缓存。

Changing the database table definitions to use the memory engine is probably more complicated than you need.

更改数据库表定义以使用内存引擎可能比您需要的更复杂。

If you have enough memory to load the tables into memory with the MEMORYengine, you have enough to tune the innodb settings to cache everything anyway.

如果您有足够的内存使用MEMORY引擎将表加载到内存中，则您有足够的空间来调整 innodb 设置以缓存所有内容。

回答by Seaux

"How do I do that? I explored PHPMyAdmin, and I can't find a "change engine" functionality."

“我该怎么做？我浏览了 PHPMyAdmin，但找不到“更改引擎”功能。”

In direct response to this part of your question, you can issue an ALTER TABLE tbl engine=InnoDB;and it'll recreate the table in the proper engine.

为了直接回答您问题的这一部分，您可以发出 an ALTER TABLE tbl engine=InnoDB;，它会在适当的引擎中重新创建表。

回答by Jice

In place of the Memory storage engine, one can consider MySQL Cluster. It is said to give similar performance but to support disk-backed operation for durability. I've not tried it, but it looks promising (and been in development for a number of years).

代替内存存储引擎，可以考虑 MySQL Cluster。据说它提供了类似的性能，但支持磁盘支持的操作以实现持久性。我还没有尝试过，但它看起来很有希望（并且已经开发了很多年）。

You can find the official MySQL Cluster documentation here.

您可以在此处找到官方 MySQL 集群文档。

回答by Mike Q

Additional thoughts :

额外的想法：

Ramdisk - setting the temp drive MySQL uses as a RAM disk, very easy to set up.

Ramdisk - 设置 MySQL 用作 RAM 磁盘的临时驱动器，非常容易设置。

memcache - memcache server is easy to set up, use it to store the results of your queries for X amount of time.

memcache - memcache 服务器很容易设置，使用它来存储 X 时间的查询结果。