Mysql tip: Improve the speed of inserting data (adding records) _mysql

Problem Description:

Ordinary desktop, collect data, the table already has >1000 million data quantity.

The data collected is slow to insert into the table, 100 milliseconds per treaty.

Workaround:

1, enlarge the MySQL configuration in the bulk_insert_buffer_size, this parameter defaults to 8M

bulk_insert_buffer_size=100m

2, rewrite all INSERT statements for insert delayed

This insert delayed differs in that it immediately returns the result and the background handles the insertion.

Another technique is to insert multiple data into a single hop insert, similar to insert INTO tablename values (' xxx ', ' xxx '), (' yyy ', ' yyy '), (' zzz ', ' zzz ') ...; But in my acquisition program to change the SQL is more troublesome, so did not use

Cloud Habitat Community Small series remind:

The truth is generally used for data collection and warehousing operations, mature CMS does not recommend changes, Dedecms is due to my system design problems, the proposal to use the commercial version or the establishment of their own index, now large data preferred Imperial cms or PHPCMS.