The INSERT and UPDATE statements used to register data in DB can be written by registering multiple data in one SQL, but if the amount of data is large, how can I create SQL faster?
I thought it would be faster to register multiple pieces of data in one SQL, but my friends seem to like to create SQL one by one, so I was wondering which one was better, so I asked you a question.
mysql sql
The word "mass" (especially in the context of the database) varies widely from person to person, and there is no point in not specifying a specific amount, such as 1MB, 1GB, or 1TB.
If you're really dealing with a lot of data, you'll need to use a dedicated read instruction from the database engine, and the caller will have to process the data beforehand.For example, MySQL seems to have the LOAD DATA INFILE
syntax.
I thought it would be faster to register multiple data in one SQL, but my friends like to create SQL one by one
If it means to speed up easily without going that far, of course it would be more efficient to put them together at once.This reduces the number of transactions and reduces the number of resources available, such as memory and disks.
The official documentation includes:
8.2.5.1 Optimizing INSERT Statements
The time required to insert a line depends on the following factors:The number here represents an approximate percentage.
Connect: (3)
Sending queries to the server: (2)
parsing queries:(2)
Insert line: (1 x line size)
Insert index: (1 x number of indexes)
Closed: (1)
If you want to insert many lines from the same client at the same time, use the INSERT statement in multiple VALUES lists and insert multiple lines at the same time. This is significantly (and possibly several times) faster than using separate single-line INSERT statements. If you want to add data to a non-empty table, you can adjust the bulk_insert_buffer_size variable for faster data insertion. See section 5.1.8 "Server System Variables".
Use LOAD DATA to load tables from a text file. This is typically 20 times faster than using INSERT statements. See section 13.2.7 "LOAD DATA STATEMENT"
There are many other things to worry about when it comes to production, but the following article may be helpful.
Database design for social games
© 2024 OneMinuteCode. All rights reserved.