MySQL InnoDB 插入性能 (Windows)
我是 MySQL 新手，对 InnoDB 性能有一些疑问。我有一个表，我想在其中存储一些度量，并且我正在使用存储过程中的循环来测试密集插入：
CREATE TABLE `measures` (
`Id` int(10) unsigned NOT NULL,
`DT` datetime NOT NULL,
`TF1` float DEFAULT '0',
`IF1` float DEFAULT '0',
`PAF1` float DEFAULT '0',
`PRF1` float DEFAULT '0',
`CF1` float DEFAULT '0',
`TF2` float DEFAULT '0',
`IF2` float DEFAULT '0',
`PAF2` float DEFAULT '0',
`PRF2` float DEFAULT '0',
`CF2` float DEFAULT '0',
`TF3` float DEFAULT '0',
`IF3` float DEFAULT '0',
`PAF3` float DEFAULT '0',
`PRF3` float DEFAULT '0',
`CF3` float DEFAULT '0',
`CTotal` float DEFAULT '0',
PRIMARY KEY (`Id`,`DT`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE PROCEDURE `sp_INSERT_TEST`()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=6000) DO
INSERT INTO measures
(Id, DT, TF1, IF1, PAF1, PRF1, CF1, TF2, IF2, PAF2, PRF2, CF2,
TF3, IF3, PAF3, PRF3, CF3, CTotal)
VALUES
(1, TIMESTAMPADD(MINUTE,i, NOW()),
0.1, 1, 10, 100, 1000,
0.2, 2, 20, 200, 2000,
0.3, 3, 30, 300, 3000,
i * 10);
SET i=i+1;
END WHILE;
END
执行的总时间约为150秒，但如果我使用MyISAM而不是InnoDB，时间大约是0.2秒。
两种发动机之间的这种性能差异正常吗？是否可以使用 InnoDB 改进插入以接近 MyISAM 性能，或者这是引擎限制？
注意：我使用的是MySQL 5.1.56（64位），Windows 7 x64，开发人员计算机的默认配置（我也尝试过服务器配置，但性能相似）。
提前致谢。 约翰
I'm new to MySQL and I have some doubts about InnoDB performance. I have a table where I want to store some measures and I'm testing intensive insert using a loop in a stored procedure:
CREATE TABLE `measures` (
`Id` int(10) unsigned NOT NULL,
`DT` datetime NOT NULL,
`TF1` float DEFAULT '0',
`IF1` float DEFAULT '0',
`PAF1` float DEFAULT '0',
`PRF1` float DEFAULT '0',
`CF1` float DEFAULT '0',
`TF2` float DEFAULT '0',
`IF2` float DEFAULT '0',
`PAF2` float DEFAULT '0',
`PRF2` float DEFAULT '0',
`CF2` float DEFAULT '0',
`TF3` float DEFAULT '0',
`IF3` float DEFAULT '0',
`PAF3` float DEFAULT '0',
`PRF3` float DEFAULT '0',
`CF3` float DEFAULT '0',
`CTotal` float DEFAULT '0',
PRIMARY KEY (`Id`,`DT`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE PROCEDURE `sp_INSERT_TEST`()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=6000) DO
INSERT INTO measures
(Id, DT, TF1, IF1, PAF1, PRF1, CF1, TF2, IF2, PAF2, PRF2, CF2,
TF3, IF3, PAF3, PRF3, CF3, CTotal)
VALUES
(1, TIMESTAMPADD(MINUTE,i, NOW()),
0.1, 1, 10, 100, 1000,
0.2, 2, 20, 200, 2000,
0.3, 3, 30, 300, 3000,
i * 10);
SET i=i+1;
END WHILE;
END
The total time of execution is about 150 seconds, but if I use MyISAM instead of InnoDB, the time is about 0.2 seconds.
Is this difference of performance between the two engines normal? Is it possible to improve the inserts using InnoDB to get close to MyISAM performance or it's a engine limitation?
Note: I'm using MySQL 5.1.56 (64 bit), Windows 7 x64, with a default configuration for developer machine (I've also tried server configuration but the performance is similar).
Thanks in advance.
John
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
就像 peufeu 所说，仅使用一笔交易就可以大幅提高性能。如果您愿意放松 ACID，您可以通过
在 my.ini 中进行设置来获得更好的性能。
Just like peufeu says you can improve performance a lot by only using one transaction. If you are willing to relax ACID you can get even better performance by setting
in my.ini.
是的，这很正常。与所有事务型数据库一样，InnoDB 必须确保 ACID 一致性，这需要大量工作，而且影响性能的是，在每个事务结束时，它必须等待硬盘说“确定数据已写入”，至于ACID中的“D=耐久性”。
解决方案很简单，就是在单个事务中发出批量 INSERT，即 BEGIN、批量 INSERT、COMMIT。
或者使用更适合的东西，比如 LOAD DATA INFILE，或者至少使用多行 INSERT。
Yes that is normal. Like all transactional DBs, InnoDB has to ensure ACID consistency which takes a lot of work, and also what kills your performance is that at the end of each transaction, it has to wait for the harddisk to say "OK the data is written", as for the "D=Durability" in ACID.
Solution is simply to issue your bulk INSERT in a single transaction, ie BEGIN, mass INSERT, COMMIT.
Or use something more adapted, like a LOAD DATA INFILE, or at least use multiline INSERTs.
InnoDB 通常比 MyISAM 慢，它是一个具有 ACID 属性的事务数据库引擎。但是，更改配置或操作方式可能会对速度产生很大影响。
首先，一些不起作用的事情：添加缓存是没有用的，减少表上的索引数量会有所帮助，因为您在插入时必须创建更少的索引，但在您的情况下，您只有主键。
我的建议是尝试以下操作之一：
在单个事务中运行所有 INSERT 查询，然后在最后提交。
示例：设置自动提交=0；
INSERT 语句；
COMMIT;
使用扩展插入语法，允许将多行插入在一起。我以为 InnoDB 不支持这一点，但我只是尝试使用 MySQL 5.0.75 并且它有效。我不认为你可以一次插入所有 6000 行，因为你受到 max_allowed_packet 的限制（如果你愿意，你可以增加它）。例如，您可以一次插入 500 行。
示例：INSERT INTO 表（字段 1，字段 2）
价值观
(1、测试'),
(2, 'hello');
InnoDB is often slower than MyISAM, being a transactional DB engine with ACID properties. However, changing the configuration or the way you do things can have a big impact on speed.
First something that won't work: adding caches is useless, reducing the number of indexes on the table helps, because you've to create less indexes when inserting, but in your case you've only the primary key.
My suggestion is to try one of the following:
Run all INSERT queries in a single transaction and then commit at the end.
Example: SET autocommit=0;
INSERT statements;
COMMIT;
Use the extended insert syntax, that allows to INSERT many rows together. I thought InnoDB didn't support that, but I just tried with MySQL 5.0.75 and it worked. I don't think that you can INSERT all 6000 rows at once, because you're limited by max_allowed_packet (you can increase it if you want). You can INSERT 500 rows at a time, for example.
Example: INSERT INTO table (field1, field2)
VALUES
(1, test'),
(2, 'hello');
其他发帖者是对的 - 在一笔交易中完成所有操作。
6000 行是一个很小的表，插入性能对于如此小的数据集来说并不真正相关。
无论如何，没有任何现代数据库针对此类“玩具”系统进行了优化。
尝试十亿条记录，并使用合理规模的交易来完成，然后看看谁会获胜。
拔掉插头，看看谁恢复得更快（提示：不会是 myisam）
使用 LOAD DATA INFILE 或批量插入可能会提高存储过程的性能，即使它在一个事务中，因为 mysql SP 语言不是这样总是特别高效。
The other posters are right - do it all in one transaction.
6000 rows is such a tiny table, insert performance is not really relevant for such a tiny data set.
No modern database is optimised for such "toy" systems anyway.
Try a billion records, and do it with sensible size transactions, and see who wins then.
An pull the plug, and see who recovers more quickly (hint: it will not be myisam)
Using LOAD DATA INFILE or a batch insert might give better performance of a stored procedure, even if it's in one transaction, as mysql SP language is not always particularly efficient.
除了 stivlo 所说的所有内容之外，您可能会发现以下文章和随附视频很有用。
http://www.mysqlperformanceblog。 com/2011/03/18/video-the-innodb-storage-engine-for-mysql/
正如 Morgan Tocker 在他的演讲中所说的“朋友不要让朋友使用 myisam”
希望你觉得它很有启发:)
In addition to everything stivlo said - you might find the following article and accompanying video useful.
http://www.mysqlperformanceblog.com/2011/03/18/video-the-innodb-storage-engine-for-mysql/
as Morgan Tocker says in his presentation "friends don't let friends use myisam"
hope you find it enlightening :)
在插入方面，MyISAM 比 InnoDB 快，但相差不到 750 倍，这表明您需要 InnoDB 调优。
这是一个以前讨论过的主题，例如，您可以在以下博客中阅读一些好东西。
MyISAM is faster than InnoDB when it comes to insert, but not by a margin of 750 times, which indicates you require InnoDB tuning.
It's a topic that's been covered before, for example you can read some good things at the following blog.