MySQL011–如何处理MySQL的慢查询(MySQL 011 — how to handle slow queries of MySQL)

1、开启慢查询日志,准确定位到哪个sql语句出现了问题。

2、分析sql语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对于据进行分析并改写。

3、分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,试得语句可以尽可能的命中索引。

4、如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表。

————————

1. Start the slow query log to locate the SQL statement that has a problem.

2. Analyze the SQL statement to see if additional data is loaded. It may be that redundant rows are queried and discarded. It may be that many unnecessary columns in the results are loaded. Analyze and rewrite the data.

3. Analyze the execution plan of the statement, and then obtain its use of the index. Then modify the statement or modify the index, and try to get that the statement can hit the index as much as possible.

4. If it is impossible to optimize the statement, you can consider whether the amount of data in the table is too large. If so, you can divide the table horizontally or vertically.