MySQL Optimization-DML statements

Author: skate
Time: 2012/07/02

 

MySQL optimization ----- DML statements

 

I. insert statements

1. Insert statement operation process:
1). Connecting
2). Send the query to the server
3). Analyze Query
4). Insert row
5). Insert Indexes
6) Close the connection

2. The optimization idea of insert statements is to submit insert statements in batches (the principle of zero should also be balanced)
For example,
A. Use Insert with multiple values;
B. Disable the autocommit function to avoid each insert flush log to the disk. Multiple insert statements are batch run and then commint.

Note:
Adjust the bulk_insert_buffer_size parameter to increase the number of Batch Processing

 

3. "load data infile" loads data 20 times faster than insert

Mysql> load data infile '/tmp/t2.txt' into Table T2 fields terminated by '"'encodingby ',';

4. In the insert statement, columns with default values must use the default value. Do not manually give the value, which means less time for SQL parsing.

5. For InnoDB tables, if the tables have unique and foreign constraints, it is best to disable them when you import data.

Set unique_checks = 0; // disable unique constraint
Set foreign_key_checks = 0; // disable foreign Constraint
SQL insert statement;
Set unique_checks = 1; // enable unique constraint
Set foreign_key_checks = 1; // enableforeign Constraint
 

Ii. Update/delete
The update/delete speed depends on the updated data volume. You can also use batch update. After frequent updates, you must execute optimize table to eliminate fragments.

 

----- End -----