Insertion limit analysis of MYSQL

  • 2020-05-15 02:17:01
  • OfStack

1. If you are inserting many rows from the same client at the same time, insert several rows at the same time using VALUE statements containing multiple VALUE. This is faster (several times faster in some cases) than using a single-line INSERT statement. If you are adding data to a non-empty table, you can adjust the bulk_insert_buffer_size variable to make data insertion faster. See section 5.3.3, "server system variables."

2. If you insert many lines from different clients, you can use the INSERT DELAYED statement to speed things up. See section 13.2.4, "INSERT syntax".

3. With MyISAM, if there are no deleted rows in the table, rows can be inserted while the SELECT statement is running.

4. When loading a table from a text file, LOAD DATA INFILE is used. This is usually 20 times faster than using many INSERT statements. See section 13.2.5, "LOAD DATA INFILE grammar".

5. When a table has many indexes, it is possible to do more to make LOAD DATA INFILE faster. Use the following process:

1). Selectively create tables with CREATE TABLE.
2). Execute FLUSH TABLES statement or command mysqladmin flush-tables.
3) use myisamchk - keys - used = 0 - rq/path/to/db/tbl_name. This will remove all indexes from the table.
4). Insert data into the table using LOAD DATA INFILE, which is fast because no index is updated.
5). If you only want to read the table later, use myisampack to compress it. See section 15.1.3.3, "compressed table features."
6). Use myisamchk - r - q/path/to/db/tbl_name recreate indexes. This creates an index tree in memory before writing to disk, and it is faster because it avoids a lot of disk searching. The resulting index tree is also perfectly balanced.
7). Execute the FLUSH TABLES statement or the mysqladmin flush-tables command.

6. The lock table can speed up INSERT operations performed with multiple statements:

* LOCK TABLES a WRITE;
* INSERT INTO a VALUES (1,23),(2,34),(4,33);
* INSERT INTO a VALUES (8,26),(6,29);
* UNLOCK TABLES;

This improves performance because the index cache is flushed to disk only once after all INSERT statements are completed. The number of INSERT statements is the number of index cache refreshes. If you can insert all rows in one statement, no locking is required.

For transaction tables, BEGIN and COMMIT should be used instead of LOCK TABLES to speed insertion.

Related articles: