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

This article introduces the knowledge of "MySQL common sql optimization statements". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Bulk insertion of data

1. For Myisam-type tables, you can import large amounts of data quickly by:

ALTER TABLE tblname DISABLE KEYS;

loading the data

ALTER TABLE tblname ENABLE KEYS;

These two commands are used to turn on and off updates to non-unique indexes of Myisam tables. When importing large amounts of data into a non-empty Myisam table, you can improve the efficiency of the import by setting these two commands. For importing large amounts of data into an empty Myisam table, the default is to import the data first and then create the index, so there is no need to set it.

2. For Innodb-type tables, this approach does not improve the efficiency of importing data. For Innodb-type tables, there are several ways to improve import efficiency: www.2cto.com

a. Because Innodb tables are saved in the order of primary keys, arranging imported data in the order of primary keys can effectively improve the efficiency of importing data. If the Innodb table does not have a primary key, then the system creates an internal column as the primary key by default, so if you can create a primary key for the table, you can take advantage of this to improve the efficiency of importing data.

b. Execute SET UNIQUE_CHECKS=0 before importing data, turn off uniqueness check, and execute SETUNIQUE_CHECKS=1 after importing to restore uniqueness check, which can improve import efficiency.

c. If the application uses automatic commit mode, it is recommended to execute SET AUTOCOMMIT=0 before import, turn off automatic commit, and execute SET AUTOCOMMIT=1 after import, and turn on automatic commit, which can also improve import efficiency.

Optimize insert statements

If you insert many rows from the same customer at the same time, use INSERT statements for multiple value tables. This is faster (and in some cases several times faster) than using a split INSERT statement. Insert into test values(1,2),(1,3),(1,4)…

If you insert a lot of rows from different clients, you can get faster by using the INSERT DELAYED statement. Delayed means that the insert statement is executed immediately, in fact, the data is placed in a queue in memory, and there is no real write to disk; this is much faster than inserting each statement separately;LOW_PRIORITY is just the opposite, inserting after all other users have read and written to the table.

Store index files and data files on separate disks (using options in table creation);

For bulk inserts, you can increase the value of the bulk_insert_buffer_size variable to speed things up, but this can only be used for myisam tables; www.2cto.com

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using many INSERT statements;

Use replace statement instead of insert according to application;

Ignore duplicate records using the ignore keyword depending on the application.

Optimizing Group By Statements

By default, MySQL sorts all GROUP BY col1, col2,... The query works as if you specified ORDER BY col1, col2,... in the query. If you explicitly include an ORDER BY clause that contains the same columns, MySQL can optimize it without slowing down, although it still sorts. If the query includes GROUP BY but you want to avoid consuming the sort results, you can specify ORDER BY NULL to disable sorting.

Optimize the Order by statement

In some cases, MySQL can use an index to satisfy an ORDER BY clause without additional sorting. The where condition and order by use the same index, and the order of order by is the same as the index order, and the fields of order by are both ascending or descending.

"MySQL common sql optimization statements" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.