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.