Performance comparison of MySQL insertion method under large data volume

Whether it is in daily business data processing or import and export of databases, you may encounter insertions that require processing large amounts of data. Insertion speed is affected by both the insert and the database engine, and this article aims to analyze and compare various methods theoretically and practically to facilitate the selection of insertion methods in future applications.

Insert Analysis

The time required to insert a record in MySQL is made up of the following factors, where the number represents the approximate scale:

• Connection: (3)
• Send query to server: (2)
• Analysis query: (2)
• Insert Record: (1x record Size)
• Insert Index: (1x index)
• Close: (1)

If we execute an SQL statement every time we insert one, then we need to do all the steps except connect and close n times, which is very time-consuming and there are several ways to optimize it:

1. Write multiple lines in each INSERT statement, BULK INSERT
2. Writes all query statements to the transaction
3. Import data using Load data

The performance performed in each of these ways is as follows.

InnoDB engine

InnoDB provides MySQL with transaction security with transactional (commit), rollback (rollback), and crash-repair capabilities (crash recovery capabilities) (Transaction-safe (ACID compliant )) Type table. The InnoDB provides a row lock (locking on row level) and a FOREIGN KEY constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes.

Test environment

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

Total 100W Data

Database size 38.6MB (no index) after insert, 46.8 (indexed)

• No index single insert total time: 229s Peak Memory: 246KB
• Index single Insert total time: 242s Peak Memory: 246KB
• No index BULK INSERT total time: 10s Peak memory: 8643KB
• Index BULK INSERT Total time: 16s Peak Memory: 8643KB
• Total time to insert without index transactions: 78s Peak Memory: 246KB
• Total elapsed time for indexed transaction insertion: 82s Peak Memory: 246KB
• No index load data insert total time: 12s Peak Memory: 246KB
• Indexed load Data Insert Total time: 11s Peak Memory: 246KB

Myiasm engine

MyISAM is the MySQL default storage engine. Simple design, support full-text search.

Test environment

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

Total 100W Data

Database size 19.1MB (no index) after insert, 38.6 (indexed)

• No index single insert total time: 82s Peak Memory: 246KB
• Index single Insert total time: 86s Peak Memory: 246KB
• No index BULK INSERT total time: 3s peak memory: 8643KB
• Index BULK INSERT Total time: 7s Peak Memory: 8643KB
• No index load data insert total time: 6s Peak memory: 246KB
• Indexed load Data Insert total time: 8s Peak Memory: 246KB

Summarize

The amount of data I am testing is not very large, but it is possible to understand how these kinds of insertions affect speed, the fastest must be the load data mode. This is a relatively cumbersome approach because it involves writing files, but it can take into account both memory and speed.

<?php $dsn = ' mysql:host=localhost;dbname=test '; $db = new PDO ($dsn, ' root ', ' ', array (pdo::attr_persistent = true)); Delete the last inserted data $db->query (' Delete from ' test '); Start timing $start _time = time (); $sum = 1000000;  Test option $num = 1; if ($num = = 1) {//single insert for ($i = 0; $i < $sum; $i + +) {$db->query (' INSERT INTO ' test ' (' id ', ' name ') VALUES ($i, ' tsets SDF ') ");  }} elseif ($num = = 2) {//BULK INSERT, in order not to exceed Max_allowed_packet, select every 100,000 inserts for ($i = 0; $i < $sum; $i + +) {if ($i = = $sum-1) {//Last if ($i%100000 = = 0) {$values = "($i, ' testtest ')"; $db->query ("INSERT INTO ' test ' (' id ', ' name ') values $valu Es "); } else {$values. = ", ($i, ' testtest ')"; $db->query ("INSERT INTO ' test ' (' id ', ' name ') values $values"); if ($i%100000 = = 0) {//normally only in this case insert if ($i = = 0) {$values = "($i, ' testtest ')";} else {$db->query ("insert INTO ' t EST ' (' id ', ' name ') values $values "); $values = "($i, ' testtest ')"; }} else {$values. = ", ($i, ' testtest ')";}} } elseif ($num = = 3) {//Transaction insert $db->begintransaction (); for ($i = 0; $i < $sum; $i + +) {$db->query ("INSERT INTO ' test ' (' id ', ' name ') ' Values ($i, ' tsetssdf ') ')} $db->commi T (); } elseif ($num = = 4) {//file load Data $filename = DirName (__file__). ' /test.sql '; $fp = fopen ($filename, ' w '); for ($i = 0; $i < $sum; $i + +) {fputs ($fp, "$i, ' testtest ' \ r \ n");} $db->exec ("Load Data infile ' $filename ' into table Test fields terminated by ', ' "); } $end _time = time (); echo "Total time-consuming", ($end _time-$start _time), "Seconds \ n";  echo "Peak Memory", round (Memory_get_peak_usage ()/1000), "kb\n";  ?>

Performance comparison of MySQL insertion method under large data volume