Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to optimize the slow query of SQL statement in MySQL

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Share

Shulou(Shulou.com)06/03 Report--

Let's learn how to optimize the slow query of SQL statements in MySQL. I believe you will benefit a lot after reading it. The text is not much in essence. I hope that how to optimize the slow query of SQL statements in MySQL is what you want.

1. There are two ways to catch SQL slow query sentences:

1. Temporary emergency crawl

SHOW FULL PROCESSLIST; # check the thread on which MySQL is running; if you execute it several times and have the same statement, it may be a SQL slow query statement.

The most important thing in this command is the state column. Mysql lists the following states:

Checking table # is checking the data table (this is automatic). Closing tables # is flushing the modified data in the table to disk while closing the used table. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load. Connect Out # replication slave CVM is connecting to the master CVM. Copying to tmp table on disk # is saving memory by changing temporary tables from memory storage to disk storage because the temporary result set is larger than tmp_table_size. Creating tmp table # is creating a temporary table to hold some of the query results. Deleting from main table # CVM is performing the first part of multi-table deletion, and the first table has just been deleted. The deleting from reference tables # CVM is performing the second part of multi-table deletion and is deleting the records of other tables. Flushing tables # is executing FLUSH TABLES, waiting for another thread to close the data table. Killed # sends a kill request to a thread, then the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released. Locked # is locked by other queries. Sending data # is processing records for SELECT queries and is sending the results to the client. Sorting for group # is sorting for GROUP BY. Sorting for order # is sorting for ORDER BY. Opening tables # this process should be very fast, unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an ALTER TABLE or LOCK TABLE statement is complete. Trying to open a table. Removing duplicates # is executing a query in SELECT DISTINCT mode, but MySQL cannot optimize those duplicate records in the previous stage. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client. Reopen table # acquires a lock on a table, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet. The Repair by sorting # repair directive is sorting to create an index. The Repair with keycache # repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting. Searching rows for update # is talking about finding qualified records for update. It must be done before UPDATE modifies the relevant records. Sleeping # is waiting for the client to send a new request. System lock # is waiting to acquire an external system lock. If you are not currently running multiple mysqld CVMs to request the same table at the same time, you can disable external system locks by adding the-- skip-external-locking parameter. Upgrading lock INSERT DELAYED # is trying to get a lock table to insert a new record. Updating # is searching for matching records and modifying them. INSERT DELAYED # has processed all pending insert operations and is waiting for a new request. Summary: in general, simple queries should be completed within 2 seconds. If there is a timeout, there may be an exception. In addition, most of the above states are provided for troubleshooting after a problem occurs, similar to error codes.

2, periodic analysis-the way to record slow query logs

In my work, I usually set three parameters in the configuration file.

Log-slow-queries = / data/3306/slow.log # slow query log path, log file is defined in my.cnf long_query_time = 2 # record SQL query statement log-queries-not-using-indexes = 1 # record sql without using index

Through the above three parameters, the slow query log is collected, the log is cut by script and mysqladmin, analyzed by msyqlsla tool, and then executed by scheduled tasks at 8 o'clock every day, and the results are regularly emailed to the company's DBA, or themselves, core developers, and CC CTO.

Msyqlsla tool address: http://blog.itpub.net/7607759/viewspace-692828

Second, we have caught the slow query, how to optimize it?

Create an index

Explian command # check the index usage of the sql statement SQL-no-cache\ G, # check whether the SQL statement is indexed or not

Note that there is a key, which really shows whether to go to the index, if not, it is necessary to set it, then how to set it?

For example, we query all users of the current system, and this query enables the PRIMARY primary key index (see key)

Mysql > explain select user Host from mysql.user\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: user type: indexpossible_keys: NULL key: PRIMARY key_len: 228 ref: NULL rows : 6 Extra: Using index1 row in set (0.00 sec)

View table structure (RPI primary key index)

Mysql > desc mysql.user +-- +-- +-+ | Field | Type | | Null | Key | Default | Extra | + -+-+ | Host | char (60) | NO | PRI | User | char (16) | NO | PRI |

Third, how to create an index?

We can index this kind of column according to the condition after where, and choose the column on the large table with more unique values as the index as far as possible (for example, the male and female columns have few unique values, which is not suitable for indexing). If the conditional columns have several columns and the unique values are very few, we can build a joint index to achieve the purpose of optimization. The federated index has prefix characteristics, and the columns that query frequently should be put in front of them. After confirming how to build the index, we begin to create the index

1. There are two ways to create an index:

Alter table student change id id int primary key auto_increment; # add self-incrementing primary key index alter table student add index index_name (name) # add normal index create index index_dept on student (dept (8)); # create a specified number of characters index

When the amount of data is very large, we try to choose that the database is at a low ebb or at night, so as not to affect site access, except in case of emergency.

IV. More advanced optimization

You can also use the select profile function to optimize every detail of the SQL statement. I don't use much here, and the company's professional DBA is generally used to deal with it.

Of course, we will also encounter particularly long sql statements, and there is not much room for optimization. I will send sql statements to the core development for processing. For example, if one statement is very slow, I can change it into two and walk the index separately. It may be very high, and we can also use ways to improve the product and architecture. For example, there is no room for optimization in this statement, so we can query it on the internal slave database.

After reading this article on how to optimize SQL sentences in MySQL, many readers will certainly want to know more about it. If you need more industry information, you can follow our industry information section.

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.

Views: 245

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Servers

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report