Solution I
Analyze slow SQL logs and CPU usage monitoring indicators to locate slow SQL queries, and view the SQL execution plans to optimize related slow SQL statements.
• Check the slow SQL logs to see if there is any slow SQL query and the corresponding execution plan to locate the cause. To query the RDS for MySQL slow logs,see Viewing Slow Logs.
• Check the CPU usage indicators for RDS for MySQL instances to help locate the cause.
• Create and add read-only instances for some query services, thus reducing the load from the primary instance and easing the query pressure of the main database.
• Add indexes for associated fields in multi-table association queries.
• Do not use the SELECT * statements to scan all tables. You can specify fields or add WHERE conditions.
• Starting with a percent sign (%) in a LIKE condition invalidates the index. The index can be used when the condition is not started with (or ended with) a percent sign (%).
• Do not operate on indexed columns.
• Do not use unnecessary implicit type conversions in SQL.
• For statements involving order by/group by, use the WHERE condition and the order/group fields as a compound index, and the sorting methods must be the same.
• The compound index should follow the leftmost principle by placing the columns that use WHERE most frequently on the leftmost.
Solution II
Analyze the running threads of the current database to locate slow SQL statements and optimize these statements to reduce the CPU usage of the instance, thus improving system performance. The procedure is as follows:
1.Connect to a MySQL Instance. For details about the instance connection, see Introduction to Instance Connection Mode..
2.Execute the show full processlist command to view the executening threads of the current MySQL, including the status of the threads, whether to lock the table, etc. By doing so, you can view the current SQL execution in real time and locate inefficient queries.
3.Run the SQL statement: select * from information_schema.processlist where user='$service user' and Command<>'sleep' order by time desc limit 50.
4.After obtaining the query to be optimized, you can identify SQL statements that take a long time to execute, consume a large amount of resources, or are executed frequently. Then, you can take corresponding measures to optimize and maintain the stability of database performance.