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

This article introduces the knowledge of "how to optimize slow SQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Slow SQL diagnosis

System level: check the operation of the system server, disk Icano, etc.

Database level:

For MySQL, slow SQL is located through slow log

For slow SQL, view its execution plan and analyze the reasons for the time-consuming

If the index is missing, create an appropriate index according to the business requirements; if there is a problem with the writing of the SQL statement, you need to optimize the statement writing according to the business logic.

Practical SQL Diagnostic tool: SQLTXPLAIN

A tool developed by Oracle experts for diagnosing SQL-related problems, easy to use but powerful.

Many Optimzer problems are supported for slow SQL diagnostics, such as abnormal execution plan, slow SQL, Wrong Result, and so on.

Slow SQL optimization

Recommendations for optimization of slow SQL caused by SQL writing:

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.

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

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

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

Use Oder by rand () with caution.

Open source SQL optimization platform

For the optimization of SQL statements, there are some open source SQL optimization platforms, which incorporate the experience of a large number of DBA engineers, and we can optimize SQL with these platforms.

1. SOAR

Is an intelligent SQL optimization and rewriting tool, produced by Xiaomi operation and maintenance DBA team, SOAR is mainly composed of syntax parser, integrated environment, optimization recommendations, rewriting logic, toolset five modules, compared with other excellent products in the industry has its own advantages, can not only help you rewrite SQL but also give some optimization suggestions.

2. SQLAdvisor

It is a tool developed and maintained by DBA team (Beijing) of Technical Engineering Department of Meituan Dianping Company to analyze SQL and give suggestions for index optimization. It is based on the original lexical analysis of MySQL, combined with the analysis of the conditions in SQL, aggregation conditions, multi-table join relations to give suggestions for index optimization.

This is the end of "how to optimize slow SQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.