Explore the impact of index and commit frequency in MySQL on the write speed of InnoDB tables, mysqlinnodb

Explore the impact of index and commit frequency in MySQL on the write speed of InnoDB tables, mysqlinnodb

This time, let's take a look at the impact of the index and commit frequency on the write speed of InnoDB tables and learn what needs to be paid attention.

Let's make a few conclusions:

1. Impact of indexes on write speed:
A. If an auto-increment column is used as the primary key, the write speed increases by about 3.11% if there is no index at all;
B. If an auto-increment column is used as the primary key and the secondary index is relatively non-indexed, the write speed is reduced by about 27.37%;

Therefore, it is recommended that the InnoDB table always have an auto-incrementing column as the primary key.

2. The impact of the submission frequency on the write speed (in the case that only the auto-incrementing column is used as the primary key in the table, 0.3 million rows of data are written at a time as an example ):

A. After all the data is written, it is the most efficient to execute the commit statement;
B. Each 0.1 million rows is submitted once, which is about 1.17% slower than one-time submission;
C. Each 10 thousand rows is submitted once, which is about 3.01% slower than one-time submission;
D. submit each 23.38% rows, which is about slower than one-time commit;
E. Each 100 rows is submitted once, which is about 24.44% slower than one-time submission;
F. Each 10 rows are submitted once, which is about 92.78% slower than one-time submission;
G. Each row is submitted once, which is about 546.78% slower than one-time submission, that is, 5 times slower;

Therefore, it is best to wait for all transactions to end before batch commit, instead of submitting each SQL statement.
I once compared and tested mysqldump to enable extended-insert and SQL scripts without export. The latter is more than five times slower than the former.
Important: This suggestion is not absolutely true. It depends on specific scenarios. If it is a highly concurrent online business, you need to commit the transaction as soon as possible to avoid the lock range being extended. However, in non-high-concurrency business scenarios, especially for batch data import, we recommend that you submit data in batches.

The following is a detailed test case process. If you are interested, please take a look:

Drop table if exists 'mytab'; create table 'mytab' ('id' int (10) unsigned not null AUTO_INCREMENT, 'c1' int (11) not null default '0 ', 'c2 'int (11) not null default '0', 'c3' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 'c4 'varchar (200) not null default ", primary key ('id') ENGINE = InnoDB; DELIMITER $ drop procedure if exists 'insert _ mytab'; create procedure 'insert _ mytab' (in rownum int, in commitrate int) BEGINDECLARE I int default 0; set autocommit = 0; WHILE I <rownum DO INSERT INTO mytab (c1, c2, c3, c4) VALUES (FLOOR (RAND () * rownum), FLOOR (RAND () * rownum), NOW (), REPEAT (CHAR (ROUND (RAND () * 255), 200 )); SET I = I + 1;/* submit once every COMMITRATE frequency */IF (commitrate> 0) AND (I % commitrate = 0) THENCOMMIT; SELECT CONCAT ('commitrate: ', commitrate, 'in', I); END IF; END WHILE;

/* Submit again to ensure the submission is successful */

COMMIT; SELECT 'all COMMIT; '; END; $ # call insert_mytab (300000, 1) for a test call;-call insert_mytab (300000, 10) for each call ); -call insert_mytab (300000,100) is submitted every 10 times.-call insert_mytab (100 0) is submitted every 300000,100 times.-call insert_mytab is submitted every 300000,100 times (00 ); -call insert_mytab (10 thousand 000) is submitted every 300000,100 times;-call insert_mytab (0.1 million, 0) is submitted every 300000 times;-one-time submission

Comparison of test time consumption results: