插入InnoDB / MyISAM记录所需的时间差异

本文介绍了插入InnoDB / MyISAM记录所需的时间差异的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我将记录插入到MySQL表中，并尝试了解MyISAM表和InnoDB表之间的时间差异。

这是代码创建表：

  CREATE TABLE SpectrumData（
 ID INT（11）NULL DEFAULT NULL，
 `set` INT（11）NULL DEFAULT NULL，
波长DOUBLE NULL DEFAULT NULL，
强度DOUBLE NULL DEFAULT NULL，
错误INT（11）NULL DEFAULT NULL，
`状态`INT（11）NULL DEFAULT NULL 
）
 COLLATE ='utf8_general_ci'
 ENGINE = xxx 
 ROW_FORMAT = DEFAULT

我插入10000条记录，测量以秒为单位的时间，并重复此操作100次。我将结果放在两个Excel图表中：

MyISAM增加和InnoDB或多或少恒定。

任何人都可以解释差异？有什么关系的表中的记录数？

Windows XP操作系统SP3

Intel Core2 Duo

3.00 Ghz

2 GB RAM

MySQL 5.5 CE

更新：我应该提到我在Access前端应用程序中插入带有VBA脚本的记录。我通过ODBC系统DSN连接到MySQL数据库。

VBA代码：

 code> Dim RsSpectrumData As DAO.Recordset 
 Dim Db As Database 
 Dim i As Integer 
 Dim j As Integer 
 Dim TimerStart 
 
 Set Db = CurrentDb 
设置RsSpectrumData = Db.OpenRecordset（SpectrumData）
 
对于i = 1到100 
 TimerStart =定时器
对于j = 1到10000 
与RsSpectrumData 
 .AddNew 
！Set = 1 
！波长= 100 
！强度= 25000 
！错误= 0 
！状态= 0 
。更新
结束于
下一页
打印＃1，计时器 -  TimerStart 
下一页
 
 RsSpectrumData.Close

重新更新：

我添加了DAO事务功能，现在平均InnoDB插入时间为10,000记录从215秒减少到平均1.3秒！（感谢@MarkR）：

  Dim RsSpectrumData As DAO.Recordset 
 Dim Db As Database 
 Dim Ws As DAO.Workspace 
 Dim i As Integer 
 Dim j As Integer 
 Dim TimerStart 
 
打开C：\TEMP\logtest.txt用于追加作为＃1 
 
设置Db = CurrentDb 
设置Ws = DBEngine.Workspaces（0）
设置RsSpectrumData = Db.OpenRecordset（SpectrumData）
 $ b b For i = 1 To 20 
 TimerStart = Timer 
 Ws.BeginTrans 
对于j = 1到10000 
使用RsSpectrumData 
 .AddNew 
！Set = 1 
！波长= 100 
！强度= 25000 
！错误= 0 
！状态= 0 
。更新
结束于
 
 Ws.CommitTrans 
打印＃1，计时器 -  TimerStart 
下一页
 RsSpectrumData.Close 
 
关闭＃1

解决方案

要真正看到性能差异，你需要运行一些真正的程序DB。只是插入小批量的记录不是很有说服力。插入速度取决于诸如列数，指标数量，事务模式，数据库约束和其他活动等事情。

I'm inserting records into a MySQL table and try to understand the differences in time it takes between a MyISAM table and a InnoDB table.

This is the code to create the table:

CREATE TABLE SpectrumData (
    ID INT(11) NULL DEFAULT NULL,
    `Set` INT(11) NULL DEFAULT NULL,
    Wavelength DOUBLE NULL DEFAULT NULL,
    Intensity DOUBLE NULL DEFAULT NULL,
    Error INT(11) NULL DEFAULT NULL,
    `Status` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=xxx
ROW_FORMAT=DEFAULT

I insert 10000 records, measure the time it takes in seconds and repeat this 100 times. I put the results in two Excel charts:

So MyISAM increasing and InnoDB more or less constant.

Can anyone explain the differences? Something to do with the number of records in the table? And why these outliers with InnoDB?

Configuration used computer:

Windows XP SP3
Intel Core2 Duo
3.00 Ghz
2 GB RAM
MySQL 5.5 CE

UPDATE: I should have mentioned I insert the records with a VBA script in a Access front-end application. I connect to the MySQL database by a ODBC System DSN.

The VBA code:

Dim RsSpectrumData As DAO.Recordset
Dim Db As Database
Dim i As Integer
Dim j As Integer
Dim TimerStart

Set Db = CurrentDb
Set RsSpectrumData = Db.OpenRecordset("SpectrumData")

For i = 1 To 100
    TimerStart = Timer
    For j = 1 To 10000
        With RsSpectrumData
            .AddNew
            !Set = 1
            !Wavelength = 100
            !Intensity = 25000
            !Error = 0
            !Status = 0
            .Update
        End With
    Next
    Print #1, Timer - TimerStart
Next

RsSpectrumData.Close

UPDATE AGAIN:

I added DAO transaction functionality and now the average InnoDB insert time for 10,000 records decreased from 215 seconds to an average of 1.3 seconds! (Thanks to @MarkR):

Dim RsSpectrumData As DAO.Recordset
Dim Db As Database
Dim Ws As DAO.Workspace
Dim i As Integer
Dim j As Integer
Dim TimerStart

Open "C:\TEMP\logtest.txt" For Append As #1

Set Db = CurrentDb
Set Ws = DBEngine.Workspaces(0)
Set RsSpectrumData = Db.OpenRecordset("SpectrumData")

For i = 1 To 20
    TimerStart = Timer
    Ws.BeginTrans
    For j = 1 To 10000
        With RsSpectrumData
            .AddNew
            !Set = 1
            !Wavelength = 100
            !Intensity = 25000
            !Error = 0
            !Status = 0
            .Update
        End With
    Next
    Ws.CommitTrans
    Print #1, Timer - TimerStart
Next
RsSpectrumData.Close

Close #1

解决方案

To really see the performance differences, you'd need to run some real program on top of the DB. Just inserting tiny batch of records is not telling much. Insert speed depends a lot on things like column count, the amount of indices, transaction pattern, DB constraints and other activities taking place.

这篇关于插入InnoDB / MyISAM记录所需的时间差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！