MySQL的load data命令性能小测试
MySQL的load data命令性能小测试
以下环境为:
软件:
主机:winxp sp3
虚拟机软件:wmware ws 7.1
虚拟机系统:rhel5.1+mysql5.157
主机硬件:
cpu:酷睿2 T8100
内存:3G
- mysql> desc t;
- +-----------------+---------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------------+---------------------+------+-----+---------+-------+
- | TABLE_CATALOG | varchar(512) | YES | | NULL | |
- | TABLE_SCHEMA | varchar(64) | NO | | NULL | |
- | TABLE_NAME | varchar(64) | NO | | NULL | |
- | TABLE_TYPE | varchar(64) | NO | | NULL | |
- | ENGINE | varchar(64) | YES | | NULL | |
- | VERSION | bigint(21) unsigned | YES | | NULL | |
- | ROW_FORMAT | varchar(10) | YES | | NULL | |
- | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
- | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
- | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
- | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
- | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
- | DATA_FREE | bigint(21) unsigned | YES | | NULL | |
- | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
- | CREATE_TIME | datetime | YES | | NULL | |
- | UPDATE_TIME | datetime | YES | | NULL | |
- | CHECK_TIME | datetime | YES | | NULL | |
- | TABLE_COLLATION | varchar(32) | YES | | NULL | |
- | CHECKSUM | bigint(21) unsigned | YES | | NULL | |
- | CREATE_OPTIONS | varchar(255) | YES | | NULL | |
- | TABLE_COMMENT | varchar(80) | NO | | NULL | |
- +-----------------+---------------------+------+-----+---------+-------+
- 21 rows in set (0.04 sec)
- mysql> select count(*) from t;
- +----------+
- | count(*) |
- +----------+
- | 502564 |
- +----------+
- 1 row in set (0.00 sec)
- mysql> select * from t into outfile '/tmp/t.txt' fields terminated by ',' enclosed by '"';
- Query OK, 502564 rows affected (5.09 sec)
- mysql> create table t1 select * from t where 1<>1;
- Query OK, 0 rows affected (0.02 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> select * from t1;
- Empty set (0.01 sec)
- mysql> load data infile '/tmp/t.txt' into table t1 fields terminated by ',' enclosed by '"';
- Query OK, 502564 rows affected (8.73 sec)
- Records: 502564 Deleted: 0 Skipped: 0 Warnings: 0
- mysql> select count(*) from t;
- +----------+
- | count(*) |
- +----------+
- | 502564 |
- +----------+
- 1 row in set (0.00 sec)
- mysql> select count(*) from t1;
- +----------+
- | count(*) |
- +----------+
- | 502564 |
- +----------+
- 1 row in set (0.00 sec)
- mysql>
图1 用 information_schema.tables来生成测试表t.
- [root@rhel5 ~ 05:04:56]#ll -h /tmp
- total 88M
- -rw-rw-rw- 1 mysql mysql 80 Sep 21 16:16 a.txt
- drwx------ 2 root root 4.0K Feb 18 2015 keyring-mViyDu
- srwxr-xr-x 1 Oracle oinstall 0 Aug 3 2010 mapping-oracle
- srwxr-xr-x 1 root root 0 Feb 18 2015 mapping-root
- -rw-rw---- 1 mysql mysql 2.8K Sep 21 16:56 mysqlslow.log
- srwxrwxrwx 1 mysql mysql 0 Sep 21 15:17 mysql.sock
- srw------- 1 oracle oinstall 0 Aug 3 2010 scim-panel-socket:0-oracle
- srw------- 1 root root 0 Feb 18 2015 scim-panel-socket:0-root
- drwx------ 2 root root 4.0K Sep 21 17:02 ssh-kahSRu6355
- drwx------ 2 root root 4.0K Sep 21 16:16 ssh-XCGOuG6217
- -rw-rw-rw- 1 mysql mysql 88M Sep 21 17:01 t.txt
图2 测试表t导出数据文件为t.txt
测试过程如下:
用 insert into t select * from t; 不断插入数据直到其记录数为50万条。
然后把t的数据导入到文本文件中。
用loaddata命令把文本文件中的数据导入到表t2中。
各操作时间已经显示在图中,速度比我预想得要快得多!
评论暂时关闭