SQL statement optimization when MySQL inserts data in bulk

For MyISAM types of tables, you can quickly import large amounts of data in the following ways:

ALTER TABLE tblname DISABLE KEYS;
Loading the data
ALTER TABLE tblname ENABLE KEYS;
These two commands are used to open or close an update for a non-unique index of the MyISAM table. When importing large amounts of data into a non-empty MyISAM table, you can increase the efficiency of the import by setting these two commands. For importing large amounts of data into an empty MyISAM table, the default is to import the data first and then create the index, so you don't have to set it up.

DISABLE keys and enable keys are used to turn on or off updates to non-unique indexes on MyISAM tables, which can increase speed, note that the InnoDB table is not valid.

Do not use open or close MyISAM table non-unique indexes:
mysql> load Data infile '/home/mysql/film_test.txt ' into table Film_test2 fieldsterminated by ",";
Query ok,529056 rows Affected (1 min 55.12 sec)
records:529056 deleted:0 skipped:0 warnings:0

Use to turn MyISAM tables on or off non-unique indexes:
mysql> ALTER TABLE FILM_TEST2 Disablekeys;
Query ok,0 rows Affected (0.0SEC)
mysql> load Data infile '/home/mysql/film_test.txt ' into table film_test2;
Query ok,529056 rows Affected (6.34 sec)
records:529056 deleted:0 skipped:0 warnings:0
mysql> ALTER TABLE FILM_TEST2 Enablekeys;
Query ok,0 rows Affected (12.25SEC)
The above data import of the MyISAM table, but for the InnoDB table does not improve the efficiency of importing data

For InnoDB types of tables, there are several ways we can improve the efficiency of the import:

• Because tables of the InnoDB type are saved in the order of the primary key, the imported data is arranged in the order of the primary key, which effectively improves the efficiency of the imported data. If the InnoDB table does not have a primary key, an internal column is created by default as the primary key, so if you can create a primary key for the table, you can use this advantage to improve the efficiency of importing data.
• Perform set unique_checks=0 before importing data, turn off uniqueness Check, execute set unique_checks=1 after import, restore uniqueness check, can improve efficiency of import.
• If your app uses autocommit, it is recommended that you execute set autocommit=0 before importing, turn off autocommit, set autocommit=1 after import, turn on autocommit, or increase the efficiency of the import.

Because the InnoDB table is saved in the primary key order, the order in which the imported data primary keys are sorted can effectively improve the efficiency of the imported data.

Using test3.txt text is saved in the Order of table Film_test4 primary key storage
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query OK, 1587168 rows Affected (22.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0
Use test3.txt without any order of text (1.12 times times slower)
mysql> load Data infile '/home/mysql/film_test4.txt ' into table film_test4;
Query OK, 1587168 rows Affected (31.16 sec)
records:1587168 deleted:0 skipped:0 warnings:0

turn off uniqueness validation to improve import efficiency

Perform set unique_checks=0 before importing data, turn off uniqueness validation, perform set Unique_checks=1 after import, restore uniqueness, and improve import efficiency.

When Unique_checks=1
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (22.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0
When unique_checks=0
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (19.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0

Turn off auto-commit to improve import efficiency

The import efficiency can be improved by executing set autocommit=0 before importing data, turning off autocommit transactions, performing set autocommit=1 after import, and resuming autocommit.

When Autocommit=1
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (22.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0
When autocommit=0
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (20.87 sec)
records:1587168 deleted:0 skipped:0 warnings:0

SQL statement optimization when MySQL inserts data in bulk