Performance comparison of MySQL insertion methods in the case of large amount of data
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
