Performance analysis and comparison of various MySQL Data insertion Methods

Whether in daily business data processing or Database Import/Export, a large amount of data may be inserted. Both the insertion method and the database engine will affect the insertion speed. This article aims to analyze and compare various methods theoretically and practically to facilitate the selection of insertion methods in future applications. Insert and analyze a record in MySQL

Whether in daily business data processing or Database Import/Export, a large amount of data may be inserted. Both the insertion method and the database engine will affect the insertion speed. This article aims to analyze and compare various methods theoretically and practically to facilitate the selection of insertion methods in future applications. Insert and analyze a record in MySQL

Whether in daily business data processing or Database Import/Export, a large amount of data may be inserted. Both the insertion method and the database engine will affect the insertion speed. 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 composed of the following factors, and the number indicates the approximate proportion:

• 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 an entry, we need to execute all the steps except connect and close N times. This is very time-consuming and there are several optimization methods:

1. Write multiple rows in each insert statement and insert multiple rows 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 a transaction-safe (ACID compliant) table with transaction (commit), rollback, and crash recovery capabilities. InnoDB provides locking on row level and foreign key constraints ).

InnoDB is designed to handle large-capacity database systems. Its CPU utilization is incomparable to other disk-based relational database engines. Technically, InnoDB is a complete database system on the MySQL background. InnoDB establishes a dedicated buffer pool in the primary memory for high-speed data buffering and indexing.

Test Environment

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

Data entries in total

After the data is inserted, the database size is 38.6 MB (no index) and 46.8 (with an index)

• Total insertion time of a single index: 229 s peak memory: 246KB
• Total insertion time with an index: 242 s peak memory: 246KB
• Total time consumed for batch insertion without indexes: 10 s peak memory: 8643KB
• Total index insertion time: 16 s peak memory: 8643KB
• Total insertion time of non-index transactions: 78 s peak memory: 246KB
• Total insertion time of index transactions: 82 s peak memory: 246KB
• No index Load Data insertion time: 12 s peak memory: 246KB
• Total insertion time of index Load Data: 11 s peak memory: 246KB

MyIASM Engine

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

Test Environment

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

Data entries in total

After the data is inserted, the database size is 19.1 MB (no index) and 38.6 (with an index)

• Total insertion time of a single index: 82 s peak memory: 246KB
• Total insertion time with an index: 86 s peak memory: 246KB
• Total time consumed for batch insertion without indexes: 3 s peak memory: 8643KB
• Total index insertion time: 7 s peak memory: 8643KB
• No index Load Data insertion time: 6 s peak memory: 246KB
• Total insertion time of index Load Data: 8 s peak memory: 246KB

Summary

I tested a small amount of Data, but I can probably understand the impact of these insertion methods on the speed. The fastest way is to Load Data. This method is relatively troublesome because it involves writing files, but it can take into account both the memory and speed.

Test code

  True); // delete the last inserted data $ db-> query ('delete from 'test'); // start time $ start_time = time (); $ sum = 1000000; // test option $ num = 1; if ($ num = 1) {// insert a single entry for ($ I = 0; $ I <$ sum; $ I ++) {$ db-> query ("insert into 'test' ('id', 'name') values ($ I, 'tsetssdf ') ") ;}} elseif ($ num = 2) {// batch insert. To avoid exceeding max_allowed_packet, Select Insert for ($ I = 0 every 0.1 million seconds; $ I <$ sum; $ I ++) {if ($ I = $ sum-1) {// last if ($ I % 100000 = 0) {$ values = "($ I,'t Esttest') "; $ db-> query (" insert into 'test' ('id', 'name') values $ values ");} else {$ values. = ", ($ I, 'testtest')"; $ db-> query ("insert into 'test' ('id', 'name ') values $ values ");} break;} if ($ I % 100000 = 0) {// insert if ($ I = 0) only in this case) {$ values = "($ I, 'testtest')";} else {$ db-> query ("insert into 'test' ('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-> commit ();} 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 consumed ", ($ end_time-$ start_time ), "seconds \ n"; echo "peak memory", round (memory_get_peak_usage ()/1000), "KB \ n";?>

Reference

1. MySQL: InnoDB or MyISAM?
2. Mysql storage engine: Differences and advantages of InnoDB and MyISAM
3. MySQL fast insertion of large data volumes and statement Optimization

Original article address: Performance Analysis and Comparison of a variety of MySQL Data insertion methods. Thank you for sharing with me.