MYSQL Query optimization-effectively loading data-MySQL

MYSQL Query Optimization: effectively loading data
Sometimes we need to load a large amount of data into the data table. the batch loading method is more efficient than loading one record, because MySQL does not need to refresh the index every time a record is loaded. The following describes how to accelerate data loading:

The load data statement is faster than the INSERT statement.

Load data is more efficient than load data local statements. The former can be used by the server to directly read and load data from the local disk, and the latter must be read by the client program and transmitted to the server over the network.

If you must use the INSERT statement, try to INSERT multiple data rows in one statement.

If you must use multiple INSERT statements, try to put them together in one transaction for processing, instead of executing them in automatic commit mode: for example:

BEGIN;
Insert into table_name values (...);
Insert into table_name values (...);
Insert into table_name values (...);
...
COMMIT;
For tables that do not support transactions, write locks should be performed on the table, and INSERT operations should be performed on the table during the table lock, such:

Lock tables table_name WRITE;
Insert into table_name ...;
Insert into table_name ...;
Insert into table_name ...;
...
Unlock tables;
Reduce the amount of data transmitted over the network by using the compression function in the client/server communication protocol. However, this compression will consume a lot of system resources, so be careful when using it.

Insert the default value to MySQL whenever possible. Do not write too many values in the INSERT statement to reduce the amount of network transmission and the syntax analysis time on the server.

For MyISAM and ISAM data tables, if you need to load a large amount of data, you should first create a non-indexed table, load the data and then create an index. This method is not applicable to InnoDB or BDB data tables.

There are two ways to disable and reactivate an index:

Run the disable keys and enable keys commands of the alter table statement, for example:

Alter table table_name disable keys;
Alter table table_name enable keys;
Use the myisamchk or isamchk tool. For example:

$ Myisamchk -- keys-used = 0 table_name # forbidden
$ Myisamchk -- recover -- quick -- key-used = n table_name # Activate
N is the bitmask used to indicate the index to be activated. 0th bits correspond to the first index. if there are three indexes, the value of n is 7 (binary 111 ). You can run the following command to determine the index number:
$ Myisamchk -- description table_name