InnoDB(MySQL 5.5.8)是数十亿行的正确选择吗? [英] Is InnoDB (MySQL 5.5.8) the right choice for multi-billion rows?

问题描述

因此，我在MySQL中使用InnoDB存储引擎的表之一将包含数十亿行(可能不限制要插入的行数).

So, one of my tables in MySQL which uses the InnoDB storage engine will contain multi-billion rows(with potentially no limit to how many will be inserted).

您能告诉我我可以做些什么样的优化来加快速度吗? 因为已经有几百万行，它将开始变慢.

Can you tell me what sort of optimizations i can do to help speed up things? Cause with a few million rows already, it will start getting slow.

当然，如果您建议使用其他物品.我唯一的选择是PostgreSQL和Sqlite3.但是有人告诉我，sqlite3并不是一个好的选择. 至于PostgreSQL，我绝对不知道它是怎么回事，因为我从没用过.

Of course if you suggest to use something else. The only options i have are PostgreSQL and Sqlite3. But I've been told that sqlite3 is not a good choice for that. As for postgresql, i have absolutely no idea how it is, as i've never used it.

我想不过是该表中至少每秒大约有1000-1500次插入.

I imagine though, at least about 1000-1500 inserts per second in that table.

推荐答案

一个简单的答案是，对于数十亿行数据集，InnoDB将是理想的选择.

A simple answer to your question would be yes InnoDB would be the perfect choice for a multi-billion row data set.

有很多可能的优化方法.

There is a host of optimization that is possbile.

最明显的优化方法是设置一个较大的缓冲池，因为对于InnoDB而言，缓冲池是最重要的事情，因为InnoDB会缓冲缓冲池中的数据以及索引.如果您有一个仅具有InnoDB表的专用MySQL服务器，则应设置InnoDB最多使用80％的可用RAM.

The most obvious optimizations would be setting a large buffer pool, as buffer pool is the single most important thing when it comes to InnoDB because InnoDB buffers the data as well as the index in the buffer pool. If you have a dedicated MySQL server with only InnoDB tables, then you should set upto 80% of the available RAM to be used by InnoDB.

另一个最重要的优化是在表上具有适当的索引(牢记数据访问/更新模式)，包括主索引和辅助索引. (请记住，主索引会自动附加到辅助索引上.)

Another most important optimization is having proper indexes on the table (keeping in mind the data access/update pattern), both primary and secondary. (Remember that primary indexes are automatically appended to secondary indexes).

使用InnoDB，还有一些额外的好处，例如防止数据损坏，自动恢复等.

With InnoDB there are some extra goodies, such as protection from data corruption, auto-recovery etc.

要提高写入性能，您应该将事务日志文件设置为最大4G.

As for increasing write-performance, you should setup your transaction log files to be upto a total of 4G.

您可以做的另一件事是对表进行分区.

One other thing that you can do is partition the table.

通过将bin-log-format设置为"row"，并将auto_inc_lock_mode设置为2(可以确保innodb在插入自动增量列时不持有表级锁)，可以提高性能.

You can eek out more performance, by setting the bin-log-format to "row", and setting the auto_inc_lock_mode to 2 (that will ensure that innodb does not hold table level locks when inserting into auto-increment columns).

如果您需要任何具体建议可以与我联系，我将非常乐意为您提供帮助.

If you need any specific advice you can contact me, I would be more than willing to help.

这篇关于InnoDB(MySQL 5.5.8)是数十亿行的正确选择吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！