Shulou(Shulou.com)06/01 Report--

This article mainly introduces the comparison of several methods of MySQL bulk data insertion or modification, the contents of the article are carefully selected and edited by the author, with a certain pertinence, and is of great significance to everyone's reference, the following is a comparison of several methods of MySQL bulk data insertion or modification with the author.

Insert analysis

The time it takes to insert a record in MySQL consists of the following factors, where the numbers represent approximate proportions:

Connection: (3)

Send query to CVM: (2)

Analysis query: (2)

Insert record: (1x record size)

Insert index: (1x index)

Close: (1)

If we execute a SQL statement for each insert, then we need to perform all the steps except connect and close N times, which is very time-consuming and can be optimized in the following ways:

(1) write multiple lines in each insert statement and insert them in batches

(2) write all query statements into the transaction

(3) use Load Data to import data

The performance of each method is as follows.

Innodb engine

InnoDB provides MySQL with transaction security (transaction-safe (ACID compliant)) tables with commit, rollback, and crash repair capabilities (crash recovery capabilities). InnoDB provides row locks (locking on row level) and foreign key constraints (FOREIGN KEY constraints).

InnoDB is designed to deal with large-capacity database systems, and its CPU utilization is unmatched by other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL. InnoDB establishes its dedicated buffer pool in main memory for caching data and indexes.

Test environment

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

Total 100W pieces of data

After insertion, the database size 38.6MB (no index), 46.8 (with index)

Total time to insert single bar without index: 229s peak memory: 246KB

Total time to insert single bar with index: 242s peak memory: 246KB

Total time spent on indexed bulk insert: 10s peak memory: 8643KB

Total time spent on bulk insert with index: 16s peak memory: 8643KB

Total insert time for indexed transactions: 78s peak memory: 246KB

Total insert time for indexed transactions: 82s peak memory: 246KB

Total time spent inserting indexed Load Data: 12s peak memory: 246KB

Total time taken to insert indexed Load Data: 11s peak memory: 246KB

MyIASM engine

MyISAM is the default storage engine for MySQL. The design is simple and supports full-text search.

Test environment

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

Total 100W pieces of data

After insertion, the database size 19.1MB (no index), 38.6 (with index)

Total time to insert single bar without index: 82s peak memory: 246KB

Total time to insert single bar with index: 86s peak memory: 246KB

Total time spent on indexed bulk insert: 3s peak memory: 8643KB

Total time spent on bulk insert with index: 7s peak memory: 8643KB

Total time spent inserting indexed Load Data: 6s peak memory: 246KB

Total time taken to insert indexed Load Data: 8s peak memory: 246KB

Summary

The amount of data I tested is not very large, but I can get a general idea of the impact of these insertion methods on speed, and the fastest one must be Load Data. This approach is relatively troublesome because it involves writing files, but it can strike a balance between memory and speed.

Test code

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.