A detailed introduction to how mysql optimizes the speed of inserting records

When inserting records, the main factors that affect the insertion speed are index, uniqueness verification, the number of records inserted at one time, etc. According to these situations, optimization can be performed separately. This section will introduce several methods to optimize the speed of inserting records. Let's take a look with the editor below

When inserting records, the main factors that affect the insertion speed are indexing, uniqueness verification, and the number of records inserted at one time. According to these situations, optimization can be performed separately. This section will introduce several methods to optimize the speed of inserting records.

1. Common optimization methods for MyISAM engine tables are as follows:

1. Disable indexes. When inserting records into a non-empty table, MySQL will index the inserted records based on the index of the table. If you insert a large amount of data, indexing will slow down the speed of inserting records. In order to solve this situation, you can disable the index before inserting the record, and enable the index after the data is inserted. The statement to disable the index is: ALTER TABLE tb_name DISABLE KEYS; The statement to re-enable the index is: ALTER TABLE table_name ENABLE KEYS; For batch import of data from empty tables, this operation is not required because the MyISAM engine table is imported after the data is imported. indexed.​

2. Disable uniqueness check: When data is inserted, MySQL will perform a uniqueness check on the inserted record. This uniqueness check also slows down the speed of inserting records. In order to reduce the impact of this situation on query speed, you can disable uniqueness checking before inserting records, and then enable it after the records are inserted. The statement to disable the uniqueness check is: SET UNIQUE_CHECKS=0; The statement to enable the uniqueness check is: SET UNIQUE_CHECKS=1;

3. Use batch insertion. Use one INSERT statement to insert multiple records. Such as INSERT INTO table_name VALUES(....),(....),(....)

4. Use LOAD DATA INFILE to batch import when you need to import data in batches When using the LOAD DATA INFILE statement to import data faster than the INSERT statement.

2. For InnoDB engine tables, common optimization methods are as follows:

1. Disable uniqueness check . Same as the MyISAM engine, set this value to 1 after importing data through SET UNIQUE_CHECKS=0;.​

2. Disable foreign key checking. Insert dataDisable the query of foreign keys before executing, and resume the check of foreign keys after the data insertion is completed. The statement to disable foreign key checking is: SET FOREIGN_KEY_CHECKS=0; The statement to restore foreign key checking is: SET FOREIGN_KEY_CHECKS=1;

3. Disable automatic submission. Disable the automatic submission of transactions before inserting data. After the data import is completed, perform the restore automatic submission operation. The statement to disable automatic submission is: SET AUTOCOMMIT=0; To restore automatic submission, just set the value to 1.

The above is the detailed content of A detailed introduction to how mysql optimizes the speed of inserting records. For more information, please follow other related articles on the PHP Chinese website!