Mysql存储引擎用于日志表 [英] Mysql storage engine for log table

问题描述

什么是最好的存储引擎用于mysql上的日志表？

如果id字段（int），表将有一对夫妇日期字段，

它将记录所有类型的事情，用户操作，系统事件等。它预计会增长巨大快速。

其中的记录永远不会被修改，很少访问。当访问时，应该按日期级别和id排序。

一个有趣的选项是归档引擎。它满足所有标准，但有一个很大的缺点，不仅不能删除记录，表甚至不能清除。清除表格的唯一方法是删除它并重新创建。

有任何建议吗？

解决方案

根据你的问题，InnoDB应该做这项工作，因为：

1. 它的可扩展性


2. 它是行锁定的，因此如果你将写入比选择更多，它更适合。


3. 最后，他们很少访问，没有必要为MyISAM，因为它是更好的选择查询。

查看详情

EDIT

好吧，在您对其他引擎的评论中。以下是完整列表的引擎。其中，正如你说的归档有一个缺点，其他人不适合你的要求。以下是MySQL网站的报价：

  InnoDB设计用于处理大数据量时的最大性能。 
它的CPU效率可能与任何其他基于磁盘的关系数据库
引擎不匹配。 
  

基本上：

1. 如果你要使用MEMORY，不要像你说的那样不会访问数据，你的表会增长太多。


2. 如果您要使用MyISAM，它不是为设计选择查询的表而设计的比插入和更新更频繁。


3. 至于存档，这是您的选择。 这里是MyISAM和归档之间的比较日志表。我会坚持到InnoDB。


4. 我不会提到Merge，Blackhole，Example和其他引擎。 （我对CSV引擎没有太多的了解，但就我所读的来说，这不是这种表格的合适引擎。

说实话，我在编码方面花了很多时间，我研究了几个小时，也许是关于一个问题的天，看看最适合的方式。我会告诉你什么，研究是好的，但如果它阻止你的工作，然后你应该停止，喝一杯咖啡，并立即做出选择，一个点，因此，只是尝试最适合的一个给你，因为你会体验你找到一个更好的方式，通过尝试自己。我的意思是，我不相信Facebook的设计是为这样的卷，但随着它的增长，他们继续改变结构相应地，这是我相信，可能不是

下面您将看到内置MySQL存储引擎的简要说明。

MyISAM

这些表包括其他优化，如高级缓存和索引机制，可提供对数据的快速访问。使用表级锁定，MyISAM存储引擎提供并发操作。当读性能受到关注时，一般来说，MyISAM是选择。

内存

也称为堆表，内存表是快速检索很少更改的常用数据（例如国家/地区代码，邮政编码或其他查找表）。顾名思义，数据存储在内存中，因此访问比存储在磁盘中的数据快得多。使用内存的一个重要限制是数据在MySQL会话期间有效。当它崩溃或关闭数据丢失。

InnoDB

当您需要使用外键或事务时，您必须使用此存储引擎的情况。 InnoDB比MyISAM更多的并发，因为它提供行级锁定。存储引擎高度可靠。另一种情况下，当你想要使用这个存储是你有更多的写而不是读。当你经常写数据到表中尝试使用这个存储是它比MyISAM更多并发。

存档

它用于以压缩格式存储大量数据。此存储引擎的一个用例是存储归档或历史数据或安全日志。该表不使用索引，因此对于日常数据检索和存储它不是一个好的选择。它是行级锁定，数据在需要时即时解压缩。

使用合并功能以合并驻留在同一台机器上的分区表。当您将大型表拆分为几个较小的表，并使用合并表同时访问它们时，最大的好处是它的速度。搜索和排序将执行得更快，因为表中的数据较少。

What is the best storage engine to use for a log table on 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.

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.

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?

解决方案

Well, according to your question, InnoDB should do the work because:

1. It's scalability is a lot better than MyISAM
2. It's row-locking, therefore if you are going to have more writes than selects, it fits better.
3. Finally, since you said that they will be rarely accessed, there is no need for MyISAM since it is better on select queries.

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:

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:

1. 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.
2. 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.
3. 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.
4. 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.

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.

EDIT 2013

Below you'll find brief descriptions for built-in MySQL storage engines.

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.

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.

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.

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.

这篇关于Mysql存储引擎用于日志表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！