问题描述
我有一个大的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插入查询时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!