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

In this article, the editor introduces in detail the "SQL query slow problem", the content is detailed, the steps are clear, and the details are handled properly. I hope this "SQL query slow problem" article can help you solve your doubts.

1. Causes of slow SQL

In the case of slow SQL, the cause cannot be simply attributed to SQL writing problems (although this is the most common factor). In fact, there are many factors that lead to slow SQL, including hardware and mysql's own bug. According to the probability of occurrence, from large to small, it is listed as follows:

Problems in writing SQL

Lock

Business instances compete for IO/CPU resources with each other

Server hardware

MYSQL BUG

2. Slow SQL optimization caused by SQL writing

For slow SQL caused by SQL writing, it is relatively convenient to optimize. As mentioned in the previous section, the correct use of indexes can speed up queries, so we need to pay attention to the rules related to indexes when writing SQL:

Field type conversion results in no index, such as string type without quotation marks, numeric type with quotation marks, etc., which may lead to full table scan without using index.

Mysql does not support function conversion, so you cannot add a function in front of the field, otherwise the index will not be used.

Do not add or subtract before the field

If the string is long, you can consider the part of the index to reduce the size of the index file and improve the writing efficiency.

Like% does not need an index in the front

No index is needed for individual queries based on the second and subsequent fields of the federated index

Do not use select *

Please use ascending order as much as possible.

Try to use union instead of Innodb for or query.

The highly selective fields of the composite index are at the top.

The order by / group by field is included in the index to reduce sorting, which is more efficient.

In addition to the above rules for using indexes, there are a few points that SQL needs to pay special attention to when writing:

Try to avoid the SQL of large transactions. The SQL of large transactions will affect the concurrent performance of the database and master-slave synchronization.

The problem of paging statement limit

To delete all records in the table, please use truncate, not delete

Don't let mysql do superfluous things, such as calculating

Input and write SQL with fields to prevent problems caused by later table changes, and the performance is also excellent (when it comes to data dictionary parsing, please query the information yourself)

Use select count (*) on Innodb because Innodb stores statistics

Use Oder by rand () with caution.

3. Analysis and diagnosis tools

In the daily development work, we can do some work to prevent slow SQL problems, such as using diagnostic tools to analyze SQL before launch. Common tools are:

Mysqldumpslow

Mysql profile

Mysql explain

The specific use and analysis methods will not be described here, there are rich resources on the Internet for reference.

4. What to do in case of misoperation and program bug, the solution

Obviously, this question is mainly aimed at young colleagues who are just starting to work. In fact, misoperation and program bug lead to data deletion or confusion is not uncommon, but new developers will be more nervous. A mature enterprise will often have perfect data management standards and rich data recovery programs (except start-ups), and will carry out data backup and data disaster recovery.

When you find that misoperation or program bug has caused online data to be deleted or changed by mistake, you must not panic. You should contact DBA in time to recover the data as soon as possible (stop the service directly in serious cases) and reduce the impact and loss as much as possible. For the operation of important data (such as funds), be sure to test repeatedly during development to ensure that there are no problems before going online.

After reading this, the article "what are the slow SQL queries?" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself to understand it. If you want to know more about related articles, welcome to follow the industry information channel.

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.