How does mysql optimize insert record speed and mysql optimize insert

How does mysql optimize insert record speed and mysql optimize insert

When a record is inserted, index, Uniqueness check, and the number of records inserted at a time are mainly affected. Based on these conditions, you can optimize the insert record speed. This section describes several methods to optimize the insert record speed.
1. Common Optimization Methods for MyISAM engine tables are as follows:
1. Disable the index. When a non-empty table is inserted, MySQL indexes the inserted records based on the table index. If a large amount of data is inserted, creating an index will reduce the insert record speed. To solve this problem, you can disable indexes before inserting records, and enable indexes after data insertion. The statement for disabling an index is alter table tb_name disable keys. The statement for re-enabling an index is alter table table_name enable keys. This operation is not required for batch data import from empty tables, because MyISAM engine tables are indexed only after data is imported.
2. Disable uniqueness check: when data is inserted, MySQL checks the uniqueness of the inserted records. This uniqueness check also reduces the insert record speed. To reduce the impact of this situation on query speed, you can disable the uniqueness check before inserting a record and enable it after the record is inserted. The statement for disabling the uniqueness check is: SET UNIQUE_CHECKS = 0; the statement for enabling the uniqueness check is: SET UNIQUE_CHECKS = 1;
3. Use batch insert. INSERT multiple records using an INSERT statement. For example, insert into table_name VALUES (....),(....),(....)
4. Use load data infile to import DATA in batches. When you need to import DATA in batches, use the load data infile statement to import DATA faster than the INSERT statement.
2. Common Optimization Methods for InnoDB Engine tables are as follows:
1. Disable the uniqueness check. Same as the MyISAM engine, SET UNIQUE_CHECKS = 0. After importing data, SET this value to 1.
2. Disable the foreign key check. Before inserting data, execute the query to prohibit external keys. After the data is inserted, restore the external key check. SET FOREIGN_KEY_CHECKS = 0; SET FOREIGN_KEY_CHECKS = 1;
3. Automatic submission is prohibited. Automatic commit of transactions is prohibited before data is inserted. After data is imported, the automatic commit operation is resumed. To disable automatic submission, set autocommit = 0. To restore automatic submission, SET this value to 1.