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

This article mainly explains the "large amount of data in the case of MySQL insertion method performance comparison", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "large amount of data in the case of MySQL insertion method performance comparison" bar!

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 the query to the server: (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:

Write multiple lines in each insert statement and insert in bulk

Write all query statements to the transaction

Import data with Load 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.