Write a stored procedure while the do bulk insert is a simple insert when inserted into the hundreds of thousands of three or four fields or after insertion speed from one million per thousand to several hundred per second has nothing to do with the transaction, I tested don't know what is the relationship between the database parameter and
What cache what IO have great spirit has studied

CodePudding user response:

Batch derivative according to turn off the index, after the data import to open the index

CodePudding user response:

A small batch submit this is not easy to conflict and other statements

CodePudding user response:

reference 1st floor csdn_castiel response:

batch derivative according to, after the first closing index, data import and open index

Only one index from one second to insert several hundred data so slow? Disk I/o is very high, whether to cache Settings???????

CodePudding user response:

reference LQW_home reply: 3/f

Quote: refer to 1st floor csdn_castiel response:

Batch derivative according to, after the first closing index, data import and open index

Only one index from one second to insert several hundred data so slow? Disk I/o is very high, whether to cache Settings???????

Database table there are a lot of data, so the index tree affirmation, insert data indexed consumption will be more and more big

There are more than the SQL sentence into an insert into... Values (), () () ()...
Don't insert strips, or will slow

There is this parameter innodb_write_io_threads, you study the

CodePudding user response:

Show variables like '% sync_binlog %';
Turn off the binlog open words

CodePudding user response:

To build a stored procedure, table statements posted see,

CodePudding user response:

refer to 6th floor liangtu response:

the stored procedure, build table statements posted a look at

Delimiter//
Create procedure pro_insert_user ()
The begin
Declare int I;
The set I=0;
While i<100000000 do
Insert into sys_user (addr, name, password, age) values (concat (' name_, I), password (I), I % 110, concat (' haila_ ', I));
The set I=I + 1;
End the while;
End//

CodePudding user response:

You can try to close the log I really want to remember a single process log is to cache etc. After the completion of the first one-time saved to log in