This time, let's take a look at the impact of indexing and submission frequency on the writing speed of InnoDB tables, and learn what to pay attention to.

Let's just say a few conclusions first:

1.About the impact of index on writing speed:
a.If there is an auto-incrementing column as the primary key, the writing speed will increase by about 3.11% compared to the case where there is no index at all;
b.If there is an auto-incrementing column as the primary key and the secondary index is relatively unindexed, the writing speed will be reduced by about 27.37%;

Therefore, InnoDB tables should always have an auto-incrementing column as the primary key.

2.Regarding the impact of the submission frequency on the writing speed (take the scenario where only the auto-increment column is used as the primary key in the table, and write 300,000 rows of data at one time as an example):

a.After waiting for all data to be written, it is most efficient to execute commit at the end;
b.Submit every 100,000 lines, which is about 1.17% slower than one-time submission;
c.Submit every 10,000 lines, which is about 3.01% slower than one-time submission;
d.Submit every 1,000 lines, which is about 23.38% slower than one-time submission;
e.Submit every 100 lines, which is about 24.44% slower than one-time submission;
f.Submit every 10 lines, which is about 92.78% slower than one-time submission;
g.Each line is submitted once, which is about 546.78% slower than one-time submission, which is 5 times slower;

Therefore, it is better to wait for all transactions to complete before committing in batches, rather than committing once after each SQL execution.
There was a comparative test mysqldump with extended-insert and without exporting SQL scripts, the latter was slower than the former more than 5 times.
Important: This recommendation is not absolute and depends on the specific scenario.If it is a highly concurrent online business, it is necessary to submit the transaction as soon as possible to avoid the expansion of the lock scope.However, if it is a non-high concurrency business scenario, especially in a scenario where data is imported in batches, it is recommended to use batch submission.

The following is a detailed test case process, interested students can take a look:

/* Submit again to ensure success */

Comparison of test time-consuming results: