Member-only story
How to Insert 100 million Data Quickly
Hello, everyone, MySQL as a widely used open source relational database management system, there should be no Java development has not used it.
Regarding MySQL, we mostly talk about how to improve query efficiency. Today, let’s talk about how to improve MySQL insertion efficiency.
Ways to improve insertion efficiency
Generally, databases run on dedicated servers. The most obvious way to improve insertion efficiency is to improve server configuration.
For example, using high-performance CPUs and SSD disks, and using a distributed system architecture to distribute write pressure to multiple nodes. This method also has the highest cost, so bosses will certainly not use this method.
We can also start from other aspects:
- Adjust database configuration: optimize buffer pool size, increase batch insertion buffer, etc., by adjusting MySQL database parameters.
- Choose the
MyISAMstorage engine because of its simple table locking mechanism and no transaction overhead, which performs better in insertion speed. - Use batch insertion.
Considering the actual application scenario, the most likely thing we can do is to use the third implementation method to improve efficiency by batch insertion.
