MySQL 批量导入数据优化

╰半夏微凉° 2024-04-18 01:10 66阅读 0赞

**--MyISAM表**  
mysql> show create table test\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: test  
Create Table: CREATE TABLE \`test\` (  
  \`id\` int(11) NOT NULL,  
  \`last\_name\` char(30) NOT NULL,  
  \`first\_name\` char(30) NOT NULL,  
  PRIMARY KEY (\`id\`),  
  KEY \`name\` (\`last\_name\`,\`first\_name\`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1  
1 row in set (0.00 sec)  
  
mysql> show keys from test;  
\+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| test  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |  
| test  |          1 | name     |            1 | last\_name   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |  
| test  |          1 | name     |            2 | first\_name  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |  
\+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
3 rows in set (0.00 sec)  
  
mysql> alter table test disable keys;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql>  load data infile '/var/lib/mysql-files/test\_out.txt' into table test charset gbk fields terminated by ',' enclosed by '"';  
Query OK, 5 rows affected (0.02 sec)  
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0  
  
mysql> alter table test enable keys;  
Query OK, 0 rows affected (0.00 sec)  
  
**--InnoDB表**  
**导入的数据按照主键的顺序排列；**  
  
**将unique\_checks参数置为0；**  
mysql> show variables like '%unique%';  
\+---------------+-------+  
| Variable\_name | Value |  
\+---------------+-------+  
| unique\_checks | ON    |  
\+---------------+-------+  
1 row in set (0.01 sec)  
  
mysql> set unique\_checks = 0;  
Query OK, 0 rows affected (0.10 sec)  
  
mysql> show variables like '%unique%';  
\+---------------+-------+  
| Variable\_name | Value |  
\+---------------+-------+  
| unique\_checks | OFF   |  
\+---------------+-------+  
1 row in set (0.00 sec)  
  
mysql>  load data infile '/var/lib/mysql-files/test\_out.txt' into table test charset gbk fields terminated by ',' enclosed by '"';  
Query OK, 5 rows affected (0.00 sec)  
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0  
  
**将autocommit参数设为0**  
  
mysql> show variables like 'autocommit';  
\+---------------+-------+  
| Variable\_name | Value |  
\+---------------+-------+  
| autocommit    | ON    |  
\+---------------+-------+  
1 row in set (0.00 sec)  
  
mysql> set autocommit = 0;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> show variables like 'autocommit';  
\+---------------+-------+  
| Variable\_name | Value |  
\+---------------+-------+  
| autocommit    | OFF   |  
\+---------------+-------+  
1 row in set (0.00 sec)  
  
mysql> load data infile '/var/lib/mysql-files/test\_out.txt' into table test charset gbk fields terminated by ',' enclosed by '"';  
Query OK, 5 rows affected (0.00 sec)  
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121909/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121909/