β

load data 在myisam与innodb上的差别

刘佛福 203 阅读

innodb数据表结构如下:

Create Table: CREATE TABLE `tinnodb` (
`id` int(11) DEFAULT NULL,
`content` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中的数据为4194304行,通过select into outfile导出

root@localhost:tiger>select * from tinnodb into outfile ‘/tmp/tiger_tinnodb.txt’ fields terminated by ‘,’ optionally enclosed by ‘”‘;
Query OK, 4194304 rows affected (3.67 sec)

Myisam数据结构如下:

Create Table: CREATE TABLE `tmyisam` (
`id` int(11) DEFAULT NULL,
`content` mediumtext
) ENGINE=myisam DEFAULT CHARSET=utf8

root@localhost:tiger>show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
 row in set (0.00 sec)

以下开始导入测试:

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

root@localhost:tiger>load data infile ‘/tmp/tiger_tinnodb.txt’ into table tinnodb fields terminated by ‘,’ optionally enclosed by ‘”‘;
Query OK, 4194304 rows affected (58.37 sec)
Records: 4194304 Deleted: 0 Skipped: 0 Warnings: 0

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

root@localhost:tiger>load data infile ‘/tmp/tiger_tinnodb.txt’ into table tinnodb fields terminated by ‘,’ optionally enclosed by ‘”‘;
Query OK, 4194304 rows affected (2.97 sec)
Records: 4194304 Deleted: 0 Skipped: 0 Warnings: 0

通过初步的耗时来看,在批量导入这个方面myisam占不小的优势。

现在调整innodb_flush_log_at_trx_commit重新做测试

root@localhost:tiger>set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

root@localhost:tiger>show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
 row in set (0.00 sec)

root@localhost:tiger>load data infile ‘/tmp/tiger_tinnodb.txt’ into table tinnodb2 fields terminated by ‘,’ optionally enclosed by ‘”‘;
Query OK, 4194304 rows affected (56.46 sec)
Records: 4194304 Deleted: 0 Skipped: 0 Warnings: 0

这里没什么大的变化。

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

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

作者:刘佛福
关注linux及数据库相关技术mysql|nosql
原文地址:load data 在myisam与innodb上的差别, 感谢原作者分享。

发表评论