InnoDB与MyISAM插入查询时间 [英] InnoDB vs. MyISAM insert query time

问题描述

我有一个大的MySQL表（〜10万行，6.5G）我用于​​读&写。它是MyISAM，我得到很多锁，由于MyISAM的所有表锁的写入。

I have a large MySQL table (~10 Million Rows, 6.5G) Which i use for read & write. It is MyISAM, and i get a lot of locks due to MyISAM's all table lock on writes.

我决定尝试移动到InnoDB推荐读/写表，并且只锁定写入时的特定行。

I decided to try and move to InnoDB which is recommended for read/write tables, and it locks only specific rows on writes.

转换后，我测试了insert语句，结果是它在InnoDB表中比在MyISAM表中需要大约15倍（从0.1秒到1.5秒） 。为什么？

After converting, I tested insert statements, and it turns out it takes ~15 times more (from 0.1 sec to 1.5 sec) in the InnoDB table than in the MyISAM table. Why is that?

我还没有为InnoDB配置任何东西，并计划添加分区，但这个数字对我来说仍然是意想不到的。当然，这些表格是相同的，相同的索引等。

I haven't configured anything for InnoDB yet, and plan to add partitions also, but this figure is still unexpected for me. Of course the tables are the same, same indexes etc.

根据要求提供的其他信息：

2个索引。 primary是类型为Big INT的data_id，以及类型为varchar（255）的非唯一的user_id。

2 indexes. primary is data_id of type Big INT, and non unique user_id of type varchar(255).

插入具有相同user_id的〜150行。

Inserts are of ~150 rows together that have the same user_id.

索引大小：MyISAM中为200 MB，InnoDB中为400 MB

Size of indexes: 200 MB in MyISAM, 400MB in InnoDB

推荐答案

相关答案建议将 innodb_flush_log_at_trx_commit 变量为2可能会提高写入对读取的比率相对较高时的性能。有关详情，请参见文档。

A related answer suggests that setting the innodb_flush_log_at_trx_commit variable to 2 is likely to improve performance when the ratio of writes to reads is relatively high. See the documentation for more.

这篇关于InnoDB与MyISAM插入查询时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！