MySQL InnoDB插入性能(Windows) [英] MySQL InnoDB insert performance (Windows)

问题描述

我是MySQL的新手，我对 InnoDB性能有一些疑问.我有一个要存储一些度量的表，并且正在使用存储过程中的循环来测试密集插入:

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

执行的总时间约为 150秒，但是如果我使用MyISAM而不是InnoDB，则该时间约为 0.2秒.

The total time of execution is about 150 seconds, but if I use MyISAM instead of InnoDB, the time is about 0.2 seconds.

两个引擎之间的性能差异是否正常?是否可以使用InnoDB改进插入以达到MyISAM性能，或者这是引擎限制?

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?

注意:我使用的是 MySQL 5.1.56(64位)， Windows 7 x64 ，并为开发人员计算机配置了默认配置(我也尝试过使用服务器配置，但性能相似).

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).

先谢谢了. 约翰

推荐答案

就像peufeu所说的那样，仅使用一个事务就可以大大提高性能.如果您愿意放松ACID，则可以通过设置

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

innodb_flush_log_at_trx_commit=0

在my.ini中.

这篇关于MySQL InnoDB插入性能(Windows)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！