Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people don't know what to do about how to optimize the speed of inserting records in mysql. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

one。 Common optimization methods for MyISAM engine tables are as follows:

1. Disable indexing. When inserting a record for a non-empty table, MySQL indexes the inserted record based on the index of the table. If you insert a large amount of data, establishing an index slows down the speed of inserting records. To solve this situation, you can disable the index before inserting the record, and open the index after the data has been inserted. The statement to disable the index is: ALTER TABLE tb_name DISABLE KEYS; reopens the index as follows: ALTER TABLE table_name ENABLE KEYS; does not need to do this if it imports data in bulk for empty tables, because the tables of the MyISAM engine are indexed after importing the data.

two。 Disable uniqueness checking: when data is inserted, MySQL verifies the uniqueness of the inserted record. This uniqueness check also slows down the insertion of records. To reduce the impact of this on query speed, you can disable uniqueness checking before inserting the record, and then turn it on after the record has been inserted. The statement to disable the uniqueness check is: SET UNIQUE_CHECKS=0; the statement to turn on the uniqueness check is: SET UNIQUE_CHECKS=1

3. Use bulk insert. Insert multiple records using a single INSERT statement. Such as INSERT INTO table_name VALUES (....), (....), (....)

4. Using LOAD DATA INFILE bulk Import when bulk importing data is required, importing data using LOAD DATA INFILE statements is faster than INSERT statements.

two。 For InnoDB engine tables, common optimization methods are as follows:

1. Disable uniqueness checking. As with the MyISAM engine, the value is set to 1 after the data is imported through SET UNIQUE_CHECKS=0;.

two。 Disable foreign key checking. Execute the query that forbids the foreign key before inserting the data, and then resume the foreign key check after the data insertion is completed. Disable foreign key check statement: SET FOREIGN_KEY_CHECKS=0; restore foreign key check statement is: SET FOREIGN_KEY_CHECKS=1

3. Automatic submission is prohibited. Disable the automatic commit of the transaction before inserting the data, and perform the resume auto-commit operation after the data import is completed. The disable autocommit statement is: SET AUTOCOMMIT=0; resumes autocommit simply by setting the value to 1.

After reading the above, have you mastered how to optimize the speed of inserting records in mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.