A Brief Talk about MySQL Optimizer Slow Query

  • 2021-06-29 12:15:11
  • OfStack

Slow Query

First, no matter what the optimization is, opening a slow query is a precondition.A slow query mechanism that records slow query statements (events) to provide optimization objectives for DB maintainers.

Check if slow queries are open

adopt show variables like 'slow_query_log' This statement finds the status of the slow query (On/Off).

Open Slow Query

MySQL version used in this article: MariaDB - 10.1.19, please note that there are differences between different versions of MySQL.

Under [mysqld], join:


[mysqld]
port= 3306

slow-query-log=1 #  Slow Query: Confirm Open 
slow-query-log-file="D:/xampp/mysql/log/mysql-slow.log" #  Slow query: log files and paths 
long_query_time = 5 #  Slow Query: Specify more than 5s Statements that are still incomplete, for statements that execute too slowly 

Optimizing steps

Look at the log and lock the target statement that needs to be optimized.Note the SQL settings, for example: SQL_NO_CACHE .

Focus on complex sentence writing.Complex statements themselves have high degrees of freedom, combined with the particularity of SQL syntax, which leads to different written complex statements with the same function, possibly with different efficiency.

Clear application scenarios, although we all have principles in every situation, in fact, if we can make clear application scenarios, we can make efficient and localized optimization for the current situation.

Statements that cannot be optimized may still face optimization failures when we use the above two methods and more optimization methods that are not mentioned in this article.Without corrections at the business level, the data level does not have the power to do so.

epilogue

When I hit "Can't Optimize", I couldn't help thinking about my favorite game design industry.

If you know 12, you will find that there are quite a number of excellent designs in game design, but most of them were embarrassed by the local technical level at that time, and could not achieve a variety of game designs.

I also remember that my friend who was UI last year threw a slot with me: I was afraid I could design it, it was cool or human, but the front end couldn't do that at all.


Related articles: