How to compare the efficiency of MySQL batch insert
This article introduces you how to compare the efficiency of MySQL batch insert, the content is very detailed, interested friends can refer to, hope to be helpful to you.
Examples of MySQL batch insert efficiency comparison are as follows: [root@BDMYSQL-200-104dumpdir] # more phone_area_new.sql INSERT INTO `phone_area_ new`VALUES ('1302357',' Jiangsu', 'Jiangsu-Nantong', 'Nantong'); INSERT INTO `phone_area_ new`VALUES ('1302358',' Jiangsu', 'Jiangsu-Nantong', 'Nantong') INSERT INTO `phone_area_ New`VALUES ('1302359,' Jiangsu', 'Jiangsu-Nantong', 'Nantong'); INSERT INTO `phone_area_ new`VALUES ('1302360,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302361,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou') INSERT INTO `phone_area_ New`VALUES ('1302362areas,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302363333,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302364provinces,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou') INSERT INTO `phone_area_ New`VALUES ('1302365,' Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('1302366', 'Zhejiang', 'Zhejiang Hangzhou', 'Hangzhou'); INSERT INTO `phone_area_ new`VALUES ('13017433,Guizhou-Guiyang', 'Guiyang') INSERT INTO `phone_area_ New`VALUES ('1301744th,' Guizhou Province', 'Guizhou-Anshun', 'Anshun'); INSERT INTO `phone_area_ new`VALUES ('1301745,Guizhou', 'Guizhou-Guiyang', 'Guiyang'); INSERT INTO `phone_area_ new`VALUES ('1301746th,' Guizhou Province', 'Guizhou-Guiyang', 'Guiyang') INSERT INTO `phone_area_ new`VALUES ('13017479',' Guizhou-Guiyang', 'Guizhou-Guiyang'); INSERT INTO `phone_area_ new`VALUES ('1301748',' Guizhou', 'Guizhou-Guiyang', 'Guiyang'); INSERT INTO `phone_area_ new`VALUES ('1301749th,' Guizhou Province', 'Guizhou-Zunyi', 'Zunyi') INSERT INTO `phone_area_ New`VALUES ('1301750mm,' Henan', 'Henan-Jiaozuo', 'Jiaozuo');-- the number of More-- (0%) is about 271255 lines: [root@BDMYSQL-200-104dumpdir] # wc-l phone_area_new.sql271255 phone_area_new.sql if you don't merge insert, it will take about three hours to import, which is tragic. Now let's compare the number of mergers.
1. Merging 1000 pieces of data first carries on the text processing.
[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 0, 000, 000ba' phone_area_new.sql > mm.sql
Clear the data in the table
System@localhost 21:01: [netdata] > truncate table phone_area_new;Query OK, 0 rows affected (0.20 sec)
Test imported data
[root@BDMYSQL-200-104dumpdir] # time mysql netdata truncate table phone_area_new;Query OK, 0 rows affected (0.20sec)
Merge data
[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 5000RMB ba'phone_area_new.sql > mm.sql)
Test imported data
[root@BDMYSQL-200-104 dumpdir] # time mysql netdata truncate table phone_area_new;Query OK, 0 rows affected (0.24 sec)
Merge data
[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 0700 thousand trees ba'phone_area_new.sql > mm.sql)
Note it took more than ten seconds to import data to merge here.
[root@BDMYSQL-200-104dumpdir] # time mysql netdata truncate table phone_area_new;Query OK, 0 rows affected (0.20sec)
Merge data
[root@BDMYSQL-200-104dumpdir] # sed': a Tantra Nathantra;\ r\?\ n [^ (] * /, /; 08000RMB ba'phone_area_new.sql > mm.sql
Import data
[root@BDMYSQL-200-104 dumpdir] # time mysql netdata
