mysql中导入数据load data在myisam与innodb区别

myisam与innodb存储引擎类型在数据上是有很大的区别的,今天我来给大家做一个测试用来测试load data导入数据在myisam与innodb类型存储引擎上的一些区别,希望例子能帮助各位带来帮助中.

innodb数据表结构如下,代码如下:

1. CreateTable:CREATETABLE`tinnodb`(
2. `id`int(11)DEFAULTNULL,
3. `content`mediumtext
4. )ENGINE=InnoDBDEFAULTCHARSET=utf8

表中的数据为4194304行,通过select into outfile导出,代码如下:

1. root@localhost:tiger>select*fromtinnodbintooutfile‘/tmp/tiger_tinnodb.txt’fieldsterminatedby‘,’optionallyenclosedby‘”‘;
2. QueryOK,4194304rowsaffected(3.67sec)

Myisam数据结构如下:

1. CreateTable:CREATETABLE`tmyisam`(
2. `id`int(11)DEFAULTNULL,
3. `content`mediumtext
4. )ENGINE=myisamDEFAULTCHARSET=utf8
5. root@localhost:tiger>showvariableslike'innodb_flush_log_at_trx_commit';
6. +--------------------------------+-------+
7. |Variable_name|Value|
8. +--------------------------------+-------+
9. |innodb_flush_log_at_trx_commit|1|
10. +--------------------------------+-------+
11. 1rowinset(0.00sec)

以下开始导入测试,导入到innodb数据表的结果如下:

1. root@localhost:tiger>loaddatainfile‘/tmp/tiger_tinnodb.txt’intotabletinnodbfieldsterminatedby‘,’optionallyenclosedby‘”‘;
2. QueryOK,4194304rowsaffected(58.37sec)
3. Records:4194304Deleted:0Skipped:0Warnings:0

导入到myisam数据表的结果如下:

1. root@localhost:tiger>loaddatainfile‘/tmp/tiger_tinnodb.txt’intotabletinnodbfieldsterminatedby‘,’optionallyenclosedby‘”‘;
2. QueryOK,4194304rowsaffected(2.97sec)
3. Records:4194304Deleted:0Skipped:0Warnings:0

通过初步的耗时来看,在批量导入这个方面myisam占不小的优势,现在调整innodb_flush_log_at_trx_commit重新做测试,代码如下:

1. root@localhost:tiger>setglobalinnodb_flush_log_at_trx_commit=2;
2. QueryOK,0rowsaffected(0.00sec)
3. root@localhost:tiger>showvariableslike'innodb_flush_log_at_trx_commit';
4. +--------------------------------+-------+
5. |Variable_name|Value|
6. +--------------------------------+-------+
7. |innodb_flush_log_at_trx_commit|2|
8. +--------------------------------+-------+
9. 1rowinset(0.00sec)
10. root@localhost:tiger>loaddatainfile‘/tmp/tiger_tinnodb.txt’intotabletinnodb2fieldsterminatedby‘,’optionallyenclosedby‘”‘;
11. QueryOK,4194304rowsaffected(56.46sec)
12. Records:4194304Deleted:0Skipped:0Warnings:0
13. //开源代码phpfensi.com

这里没什么大的变化,若是与mysqldump所导出的.sql文件做比对导入的话,这个时间不是差一点点的,具体的可以自行测试下.

值得思考下,什么场景下可以通过select into outfile和load data infile来做局部的数据备份？毕竟load data infile还算挺高效的.