Shulou(Shulou.com)05/31 Report--

How to insert data quickly in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

How to insert data quickly by MySQL

1. Minimize the size of the imported file

First of all, I would like to give a suggestion, export and import data as far as possible to use MySQL's own command-line tools, do not use Navicat, workbench and other graphical tools. Especially when there is a large amount of data, exporting and importing with the command line tools included in MySQL is several times faster than using graphical tools such as Navicat, and it is easy to get stuck when using graphical tools such as Navicat to operate with large amounts of data. The following is a brief description of how to use MySQL's own command-line tools to do import and export.

# Export the entire instance

Mysqldump-uroot-pxxxxxx--all-databases > all_database.sql

# Export the specified library

Mysqldump-uroot-pxxxxxx--databasestestdb > testdb.sql

# Export the specified table

Mysqldump-uroot-pxxxxxxtestdbtest_tb > test_tb.sql

# Import specified SQL file (specified import into testdb library)

Mysql-uroot-pxxxxxxtestdb

Most of the imported SQL scripts are to build a database and build a table, and then insert data, of which the longest time-consuming should be insert inserting data. In order to reduce the file size, it is recommended to use the extended insertion method, that is, multi-line batch insert, like this: insertintotable_namevalues (),..., ();. The file size is much smaller and the insertion speed is several times faster with extended inserts than strip inserts. Files exported using mysqldump use bulk insert by default, and you can use the-- skip-extended-insert parameter to insert one by one instead.

It can be seen that the use of expansion to insert the SQL script import takes about 10 minutes, while a bar insert SQL script import time is too long, about 1 hour is still not finished, a more than 2 G text import more than an hour has not finished, the author can not wait to manually cancel but it can still be seen that a number of insert than a bar to insert data to save several times the time.

two。 Try to modify the parameters to speed up the import

In MySQL, there is a pair of famous "double one" parameters, namely innodb_flush_log_at_trx_commit and sync_binlog. For security, the default value of these two parameters is 1. In order to quickly import the script, we can temporarily modify these two parameters. Here is a brief description of these two parameters:

Innodb_flush_log_at_trx_commit defaults to 1, which can be set to 0, 1, 2

If innodb_flush_log_at_trx_commit is set to 0 log buffer, it will be written to logfile once a second, and the flush (brush to disk) operation of logfile will occur at the same time. In this mode, writing to disk is not actively triggered when the transaction is committed. If innodb_flush_log_at_trx_commit is set to 1, MySQL will write logbuffer data to logfile and flush (flush to disk) each time the transaction commits. If innodb_flush_log_at_trx_commit is set to 2, MySQL writes logbuffer data to logfile. But the flush (flush to disk) operation does not happen at the same time. In this mode, MySQL performs a flush (flush to disk) operation once a second.

The default value of sync_ binlog is 1, which can be set to [0menn).

When sync_binlog=0, like the operating system brushing other files, MySQL does not synchronize to disk but relies on the operating system to refresh binarylog. When sync_binlog=N (N > 0), MySQL uses the fdatasync () function to synchronize its write binary log binarylog to disk every time it writes N binary log binarylog.

How to insert data quickly by MySQL

These two parameters can be modified online. If you want to import them quickly, you can follow these steps:

# 1. Go to the MySQL command line to temporarily modify these two parameters

Setglobalinnodb_flush_log_at_trx_commit=2

Setglobalsync_binlog=2000

# 2. Execute SQL script import

Mysql-uroot-pxxxxxxtestdb

# 3. Change the parameters back after the import is completed

Setglobalinnodb_flush_log_at_trx_commit=1

Setglobalsync_binlog=1

There is another scenario where your requirement is to create a new slave library, or you do not need to generate binlog. When you import a SQL script, you can set not to record binlog for the time being, and you can add setsql_log_bin=0; at the beginning of the script and then perform the import, which will be further accelerated. If your MySQL instance does not have binlog enabled, you do not need to execute this statement.

This is the answer to the question about how to insert data quickly in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.