What is the best storage engine to use for a log table on mysql?
在mysql上使用日志表的最佳存储引擎是什么?
The table will have a couple if id fields (ints) a date field, a varchar for the message level and a text field with the message.
该表将有两个if id字段(ints)、一个日期字段、一个消息级别的varchar和一个带有消息的文本字段。
It will log all sort of things, user actions, system events etc.. It is expected to grow huge quickly.
它会记录所有的事情，用户行为，系统事件等等。预计它将迅速发展壮大。
Records in it will never be modified and rarely accessed. When accessed the should be sortable by date level and id.
它中的记录永远不会被修改，也很少被访问。当访问时，应该按日期级别和id排序。
An interesting option is the archive engine. It meets all criteria but has one big drawback, not only can't records be deleted, the table can't even be purged. The only way to clear the table is to delete it and recreate it.
一个有趣的选项是存档引擎。它满足所有的标准，但有一个很大的缺点，不仅不能删除记录，甚至不能清除表。清除该表的惟一方法是删除它并重新创建它。
Any suggestions?
有什么建议吗?
1 个解决方案
#1
24
Well, according to your question, InnoDB should do the work because:
根据你的问题，InnoDB应该做这个工作，因为:
- It's scalability is a lot better than MyISAM
- 它的可扩展性比MyISAM好得多
- It's row-locking, therefore if you are going to have more writes than selects, it fits better.
- 它是行锁定，因此如果写操作多于选择，它会更适合。
- Finally, since you said that they will be rarely accessed, there is no need for MyISAM since it is better on select queries.
- 最后，由于您说它们将很少被访问，所以不需要MyISAM，因为它更适合select查询。
Check this for more information
请查看此以获取更多信息。
EDIT
编辑
Well, in the comment you ask about other engines. Here is a full list of engines. Among them, as you said archive has a disadvantage, the others do not fit to your request. Here is a quotation from MySQL website:
在评论中你问了其他引擎。这是引擎的完整列表。其中，如你所说的存档有一个缺点，其他的不适合你的要求。以下是MySQL网站的报价:
InnoDB has been designed for maximum performance when processing large data volumes.
Its CPU efficiency is probably not matched by any other disk-based relational database
engine.
So basically:
所以:
- If you're going to use MEMORY don't as you said you won't access data a lot and your table will grow too much. You will need a lot of RAM for that and when you reboot all data will be lost.
- 如果你要使用内存，不要像你说的那样你不会经常访问数据你的表会增长太多。您将需要大量的RAM，当您重新启动时，所有数据将丢失。
- If you're going to use MyISAM don't as it is designed for tables which select queries are more frequent than insert and update.
- 如果要使用MyISAM，就不要使用它，因为它是为那些选择查询比插入和更新更频繁的表设计的。
- As for archive, that's your choice. Here is a comparison between MyISAM and archive for a log table. I would stick to InnoDB though.
- 至于档案，那是你的选择。这里是日志表的MyISAM和archive之间的比较。但我还是坚持使用InnoDB。
- I won't even mention Merge, Blackhole, Example and other engines. (I don't have much knowledge on CSV engine but as far as I've read, that is a not an approriate engine for this kind of table.
- 我甚至不会提到合并，黑洞，例子和其他引擎。(我不太了解CSV引擎，但据我所知，这不是适用于这类表的一个approriate引擎。
To be honest, I used to spend a lot of time before making an important move in coding. I researched for hours, maybe for days about an issue to see which way is the most appropriate. I'll tell you what, researching is good, but after a point if it prevents you from working then you should stop, drink a coffee, and make your choice right away. Therefore, just try the most appropriate one to you and as you will experience you will find even a better way, by trying yourself. I mean, I don't believe that Facebook was designed for such a volume, but as it grew, they continued to change the structure accordingly. That's what I believe though, may not be the reality :) Anyways, hope that info helps you.
老实说，我过去常常花很多时间在编码上做一个重要的动作。我花了好几个小时，也许花了几天时间研究一个问题，看看哪种方式最合适。我告诉你，研究是很好的，但是过了一段时间，如果它阻止你工作，你应该停下来喝杯咖啡，然后马上做出选择。因此，只要试一试最适合你的方法，你就会发现更好的方法，通过自己的尝试。我的意思是，我不相信Facebook是为这么大的容量而设计的，但是随着它的发展，他们继续相应地改变结构。这就是我所相信的，也许不是事实:)无论如何，希望这个信息能帮助你。
EDIT 2013
编辑2013
Below you'll find brief descriptions for built-in MySQL storage engines.
在下面，您将找到内置MySQL存储引擎的简要描述。
MyISAM
MyISAM
These tables include additional optimizations, such as advanced cache and indexing mechanisms, which provide fast access to data. Using table-level locking, the MyISAM storage engine provides for concurrent operations. When read performance is a concern, generally, MyISAM is the choice.
这些表包括额外的优化，如高级缓存和索引机制，它们提供快速的数据访问。使用表级锁，MyISAM存储引擎提供并发操作。当读取性能是一个问题时，通常选择MyISAM。
Memory
内存
Also called heap tables, memory tables are ideal for fast retrievel of frequently used data that is rarely altered (such as country codes, zip codes or other lookup tables). As the name suggests, data is stored in memory and hence access is much faster than data stored in disks. One significant restriction for using memory is that data is valid during MySQL session. When it crashes, or shuts down data is lost.
也称为堆表，内存表是快速检索频繁使用的很少被修改的数据(例如国家代码、邮政编码或其他查找表)的理想方法。顾名思义，数据存储在内存中，因此访问速度比存储在磁盘中的数据要快得多。使用内存的一个重要限制是，数据在MySQL会话期间是有效的。当它崩溃或关闭数据时就会丢失。
InnoDB
InnoDB
One case where you will have to use this storage engine is when you need to use foreign keys or transactions. InnoDB is more concurrent than MyISAM since it provides row-level locking. The storage engine is highly reliable. Another case when you will want to use this storage is when you have more writes than reads. When you frequently write data into the table try using this storage is it is more concurrent than MyISAM.
需要使用此存储引擎的一种情况是需要使用外键或事务。InnoDB比MyISAM更具有并发性，因为它提供行级锁。存储引擎非常可靠。另一种情况是当写操作多于读操作时，需要使用此存储。当您经常向表中写入数据时，请尝试使用此存储，它比MyISAM更具有并发性。
Archive
存档
It is designed for storing large amounts of data in a compressed format. One of the use cases of this storage engine is to store archival or historical data or security logs. The table uses no indexes so for daily data retrieval and storage it is not a good choice. It's row-level locking and data is uncompressed on the fly when demanded. Moreover, altering the table is not possible.
它被设计用于以压缩格式存储大量数据。此存储引擎的一个用例是存储存档或历史数据或安全日志。该表不使用索引，因此用于日常数据检索和存储不是一个好的选择。它是行级锁，当需要时，数据会被动态压缩。此外，改变桌子是不可能的。
Merge
合并
Merge is used to 'merge' partitioned tables which reside on the same machine. When you split a large table into several smaller tables, and access them simultaneously using a merge-table, the biggest benefit is its speed. Searches and sorts will execure quicker since there is less data in tables.
Merge用于“合并”驻留在同一台机器上的分区表。当您将一个大表分割成几个小表，并使用合并表同时访问它们时，最大的好处是它的速度。由于表中数据较少，搜索和排序将执行得更快。
#1
24
Well, according to your question, InnoDB should do the work because:
根据你的问题，InnoDB应该做这个工作，因为:
- It's scalability is a lot better than MyISAM
- 它的可扩展性比MyISAM好得多
- It's row-locking, therefore if you are going to have more writes than selects, it fits better.
- 它是行锁定，因此如果写操作多于选择，它会更适合。
- Finally, since you said that they will be rarely accessed, there is no need for MyISAM since it is better on select queries.
- 最后，由于您说它们将很少被访问，所以不需要MyISAM，因为它更适合select查询。
Check this for more information
请查看此以获取更多信息。
EDIT
编辑
Well, in the comment you ask about other engines. Here is a full list of engines. Among them, as you said archive has a disadvantage, the others do not fit to your request. Here is a quotation from MySQL website:
在评论中你问了其他引擎。这是引擎的完整列表。其中，如你所说的存档有一个缺点，其他的不适合你的要求。以下是MySQL网站的报价:
InnoDB has been designed for maximum performance when processing large data volumes.
Its CPU efficiency is probably not matched by any other disk-based relational database
engine.
So basically:
所以:
- If you're going to use MEMORY don't as you said you won't access data a lot and your table will grow too much. You will need a lot of RAM for that and when you reboot all data will be lost.
- 如果你要使用内存，不要像你说的那样你不会经常访问数据你的表会增长太多。您将需要大量的RAM，当您重新启动时，所有数据将丢失。
- If you're going to use MyISAM don't as it is designed for tables which select queries are more frequent than insert and update.
- 如果要使用MyISAM，就不要使用它，因为它是为那些选择查询比插入和更新更频繁的表设计的。
- As for archive, that's your choice. Here is a comparison between MyISAM and archive for a log table. I would stick to InnoDB though.
- 至于档案，那是你的选择。这里是日志表的MyISAM和archive之间的比较。但我还是坚持使用InnoDB。
- I won't even mention Merge, Blackhole, Example and other engines. (I don't have much knowledge on CSV engine but as far as I've read, that is a not an approriate engine for this kind of table.
- 我甚至不会提到合并，黑洞，例子和其他引擎。(我不太了解CSV引擎，但据我所知，这不是适用于这类表的一个approriate引擎。
To be honest, I used to spend a lot of time before making an important move in coding. I researched for hours, maybe for days about an issue to see which way is the most appropriate. I'll tell you what, researching is good, but after a point if it prevents you from working then you should stop, drink a coffee, and make your choice right away. Therefore, just try the most appropriate one to you and as you will experience you will find even a better way, by trying yourself. I mean, I don't believe that Facebook was designed for such a volume, but as it grew, they continued to change the structure accordingly. That's what I believe though, may not be the reality :) Anyways, hope that info helps you.
老实说，我过去常常花很多时间在编码上做一个重要的动作。我花了好几个小时，也许花了几天时间研究一个问题，看看哪种方式最合适。我告诉你，研究是很好的，但是过了一段时间，如果它阻止你工作，你应该停下来喝杯咖啡，然后马上做出选择。因此，只要试一试最适合你的方法，你就会发现更好的方法，通过自己的尝试。我的意思是，我不相信Facebook是为这么大的容量而设计的，但是随着它的发展，他们继续相应地改变结构。这就是我所相信的，也许不是事实:)无论如何，希望这个信息能帮助你。
EDIT 2013
编辑2013
Below you'll find brief descriptions for built-in MySQL storage engines.
在下面，您将找到内置MySQL存储引擎的简要描述。
MyISAM
MyISAM
These tables include additional optimizations, such as advanced cache and indexing mechanisms, which provide fast access to data. Using table-level locking, the MyISAM storage engine provides for concurrent operations. When read performance is a concern, generally, MyISAM is the choice.
这些表包括额外的优化，如高级缓存和索引机制，它们提供快速的数据访问。使用表级锁，MyISAM存储引擎提供并发操作。当读取性能是一个问题时，通常选择MyISAM。
Memory
内存
Also called heap tables, memory tables are ideal for fast retrievel of frequently used data that is rarely altered (such as country codes, zip codes or other lookup tables). As the name suggests, data is stored in memory and hence access is much faster than data stored in disks. One significant restriction for using memory is that data is valid during MySQL session. When it crashes, or shuts down data is lost.
也称为堆表，内存表是快速检索频繁使用的很少被修改的数据(例如国家代码、邮政编码或其他查找表)的理想方法。顾名思义，数据存储在内存中，因此访问速度比存储在磁盘中的数据要快得多。使用内存的一个重要限制是，数据在MySQL会话期间是有效的。当它崩溃或关闭数据时就会丢失。
InnoDB
InnoDB
One case where you will have to use this storage engine is when you need to use foreign keys or transactions. InnoDB is more concurrent than MyISAM since it provides row-level locking. The storage engine is highly reliable. Another case when you will want to use this storage is when you have more writes than reads. When you frequently write data into the table try using this storage is it is more concurrent than MyISAM.
需要使用此存储引擎的一种情况是需要使用外键或事务。InnoDB比MyISAM更具有并发性，因为它提供行级锁。存储引擎非常可靠。另一种情况是当写操作多于读操作时，需要使用此存储。当您经常向表中写入数据时，请尝试使用此存储，它比MyISAM更具有并发性。
Archive
存档
It is designed for storing large amounts of data in a compressed format. One of the use cases of this storage engine is to store archival or historical data or security logs. The table uses no indexes so for daily data retrieval and storage it is not a good choice. It's row-level locking and data is uncompressed on the fly when demanded. Moreover, altering the table is not possible.
它被设计用于以压缩格式存储大量数据。此存储引擎的一个用例是存储存档或历史数据或安全日志。该表不使用索引，因此用于日常数据检索和存储不是一个好的选择。它是行级锁，当需要时，数据会被动态压缩。此外，改变桌子是不可能的。
Merge
合并
Merge is used to 'merge' partitioned tables which reside on the same machine. When you split a large table into several smaller tables, and access them simultaneously using a merge-table, the biggest benefit is its speed. Searches and sorts will execure quicker since there is less data in tables.
Merge用于“合并”驻留在同一台机器上的分区表。当您将一个大表分割成几个小表，并使用合并表同时访问它们时，最大的好处是它的速度。由于表中数据较少，搜索和排序将执行得更快。