MySQL common SQL optimization statement _ MySQL

MySQL common SQL optimization statement bitsCN.com
MySQL common SQL optimization statements insert data in large batches. 1. for tables of the Myisam type, you can import a large amount of data quickly using the following methods. Alter table tblname disable keys; loading the dataALTER TABLE tblname enable keys; these two commands are used to ENABLE or DISABLE updating non-unique indexes of the Myisam TABLE. When importing a large amount of data to a non-empty Myisam table, you can improve the import efficiency by setting these two commands. To import a large amount of data to an empty Myisam table, the index is created only after the data is imported first by default, so you do not need to set it. 2. for Innodb tables, this method cannot improve the efficiency of data import. For Innodb tables, we have the following methods to improve the import efficiency:. because Innodb tables are saved in the order of primary keys, the imported data is arranged in the order of primary keys, which can effectively improve the efficiency of data import. If the Innodb table does not have a primary key, an internal column is created by default as the primary key. Therefore, if you can create a primary key for the table, you can use this advantage to improve the efficiency of data import.
B. run SET UNIQUE_CHECKS = 0 before the data is imported, disable the uniqueness check, and run SETUNIQUE_CHECKS = 1 after the import to restore the uniqueness check, which improves the import efficiency. C. if the application uses the automatic submission method, we recommend that you execute set autocommit = 0 before import, disable automatic submission, and then execute set autocommit = 1 after import. enable automatic submission, it can also improve the import efficiency. Optimize the insert statement. if you INSERT multiple rows from the same customer at the same time, use the insert statement for multiple value tables. This is faster than using separate INSERT statements (several times in some cases ). Insert into test values (1, 2), (1, 3), (1, 4 )... If you INSERT many rows from different customers, you can use the insert delayed statement to get a higher speed. The meaning of Delayed is to let the insert statement be executed immediately. In fact, the data is put in the memory queue and is not actually written to the disk; this is much faster than inserting each statement separately; LOW_PRIORITY, on the contrary, is inserted only after all other users read and write the table. Store Index files and data files on different disks (using the options in table creation). if batch insertion is performed, you can increase the speed by adding the bulk_insert_buffer_size variable value. However, this can only be used for myisam tables. when loading a table from a text file, load data infile is used. This is usually 20 times faster than using many INSERT statements; using replace statements instead of insert based on the application; using ignore keywords based on the application to ignore duplicate records. Optimize the Group By statement. BY default, MySQL sorts all GROUP By col1, col2 ,..... The query method is as follows: specify order by col1, col2,... in the query ,.... If the statement explicitly contains an order by clause containing the same columns, MySQL can optimize it without slowing down, even though it still performs sorting. If the query includes group by but you want to avoid consumption of sorting results, you can specify order by null to prohibit sorting. Optimize the Order by statement in some cases, MySQL can use an index to satisfy the order by clause, without additional sorting. The where condition and order by condition use the same index, and the order by order is the same as the index order, and the order by field is both ascending or descending. Author bengdabitsCN.com