Shulou(Shulou.com)05/31 Report--

This article mainly explains how long it will take to insert 1 million pieces of data into MySQL. Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn how long it will take to insert 1 million pieces of data into MySQL.

Catalogue

1. Multithreaded insertion (single table)

2. Multithreaded insertion (multi-table)

3. Preprocess SQL

4. Insert SQL with multiple values

5. Transaction (N items submitted once)

Multithreaded insert (single table)

Q: why is it faster to insert multithreaded than single-threaded into the same table? Shouldn't writes to a table be exclusive at the same time?

A: when inserting into the data, the overall time allocation is as follows:

1. Time consuming for multiple links (30%)

2. Send more query to the server (20%)

3. Multi-resolution query (20%)

4. Multi-insert operation (10% * number of entries)

5. Insert more index (10% * number of Index)

6. Close links more often (10%)

From this we can see that what is really time-consuming is not the operation, but the process of linking and parsing.

MySQL insert data is exclusive in the write phase, but inserting a piece of data still requires parsing, calculation, and finally writing processing, such as assigning a self-incrementing id to each record, verifying the unique key attribute of the primary key, or some other logical processing, all need to be calculated, so multithreading can improve efficiency.

Multithreaded insert (multi-table)

Use multithreaded insertion after partitioning and dividing the table.

Pre-processing SQL

Normal SQL: even if you use the Statement interface to execute SQL

Preprocessing SQL: even if you use the PreparedStatement interface to execute SQL

Using the PreparedStatement interface allows the database to precompile SQL statements, and only need to pass in parameters later, avoiding the database compiling SQL statements every time, so the performance is better.

Multi-valued insert SQL

Normal insert SQL: INSERT INTO TBL_TEST (id) VALUES (1)

Multi-valued insertion SQL: INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

The total length of the multi-valued insert SQL,SQL statement is reduced, that is, the network IO is reduced, and the number of connections is also reduced. The database can insert multiple pieces of data at a time of SQL parsing.

Transaction (N commit once)

Committing a large number of INSERT statements in a transaction can improve performance.

1. Modify the storage engine of the table to myisam 2, assemble the sql into strings, and commit transactions every 1000 or so.

Execute multiple SQL statements to implement database transactions.

Mysql database

Multiple SQL statements

10w pieces of data take about 10s!

At this point, I believe you have a deeper understanding of "how long it will take to insert 1 million pieces of data into MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.