mysql优化 - MySQL-SQl语句优化步骤

Question

RT，在遇到一个运行很慢SQL的情况下，如何进行分析、优化、测试？
简单说明一下步骤思路；

Answer 1

If you don’t know which sql is slow, enable the slow query log of mysql. Use mysqldumpslow in the bin directory of the mysql installation directory to view the recorded log files. The specific command is mysqldumpslow -s c -t 10 /path/to/slow.log. The top 10 slow SQL statement patterns can be extracted. In this way, you can find which statements are slow.

The easiest way to analyze after finding it is to explain and view the execution plan. For example
explain select * from t where xxx order by xxx.

View the explain results, you can see the index usage, sorting method, etc. For specific results, it is best to check the explanation of the explain command in the official mysql documentation.

The most important means of optimization is to ensure that the index is established correctly. If there is no room for optimization in the index, then it is probably because the table structure design is unreasonable or the query method combination is unreasonable. This requires optimizing the data storage structure or program architecture based on the specific business.

Answer 2

The explain command in MySQL can analyze query efficiency based on its returned content. After the mysql slow query log is turned on, some SQL statements with relatively slow query speed will also be recorded in the log.