Mysql-optimize insert

Locking a table can accelerate the insert operation with multiple statements:

• Lock tables a write;
• Insert into a values );
• Insert into a values (8, 26), (6, 29 );
• Unlock tables;

This improves the performance because the index cache is refreshed to the disk only once after all insert statements are completed. Generally, the number of insert statements is the index cache refresh. If you can use one statement to insert all rows, you do not need to lock them.

For transaction tables, begin and commit should be used instead of lock tables to speed up insertion.

 

1. For tables of the MyISAM type, you can import a large amount of data quickly using the following methods.
Alter table tblname disable keys;
Loading the data
Alter table tblname enable keys;
These two commands enable or disable the update of non-unique indexes in the MyISAM table. Import a large amount of data
When creating non-empty MyISAM tables, you can set these two commands to improve the import efficiency. For a large number of imports
Data to an empty MyISAM table. By default, the index is created only after data is imported first.
.
2. For InnoDB tables, this method cannot improve the efficiency of data import. For the InnoDB type
To improve the import efficiency:
A. Because InnoDB tables are saved in the order of primary keys, import data according to the primary key
Sequential sorting can effectively improve the efficiency of data import. If the InnoDB table does not have a primary key, the system defaults
Create an internal column as the primary key, so if you can create a primary key for the table, you can use this advantage to improve
Data Import efficiency.
B. Run set unique_checks = 0 before importing data, disable uniqueness check, and run set
UNIQUE_CHECKS = 1. Restoring the uniqueness check improves the import efficiency.
C. If the application uses the automatic submission method, we recommend that you execute set autocommit = 0 before importing and disable the automatic
Submit. After the import is complete, execute set autocommit = 1 to enable automatic submission, which can also improve the import efficiency.
11.4.2 optimize the insert statement:
3. If you INSERT many rows from the same customer at the same time, use the INSERT Statement of multiple value tables. This is faster than using separate INSERT statements (several times in some cases ).
Insert into test values (1, 2), (1, 3), (1, 4 )...
4. If you INSERT many rows from different customers, you can use the insert delayed statement to get a higher speed.
The meaning of Delayed is to let the insert statement be executed immediately. In fact, the data is put in the memory queue and
Not actually written to the disk; this is much faster than inserting each statement separately; LOW_PRIORITY is the opposite,
All other users can insert a table after reading it;
5. Store index files and data files on different disks (using the options in Table creation );
6. If batch inserts are performed, you can increase the value of the bulk_insert_buffer_size variable to improve the speed.
Degrees, but this can only be used for myisam tables;
7. When loading a table from a text file, use load data infile. This is usually more
The INSERT statement is 20 times faster;
8. replace the insert statement as needed;
9. ignore duplicate records by using the ignore keyword based on application conditions.

 

When multiple tables are inserted after union, is it faster to lock the table and insert each table? Is it related to the memory and bulk_insert_size? You need to test it.