How to locate, exclude and avoid MySQL performance failure

The first is how to check the efficiency of SQL.

1. Use of explain:

After you design SQL, you should use the explain command to check the SQL to see if you are using the index and whether there is a filesort, focusing on whether the number of rows retrieved (rows) is too large.

Generally speaking.

ROWS<1000 is within an acceptable range.

Rows between 1000~1w, which can cause performance problems during intensive access, are acceptable if they are not too frequent (frequency less than 1 minutes) and are difficult to optimize, but need to be observed

When rows are greater than 10,000, you should carefully consider SQL design, optimize SQL, optimize DB, and generally do not allow frequent runs (frequency less than 1 hours).

When rows reach the 10w level, you cannot be a SQL that runs in real time. However, except for the data-guide, the data must be controlled for a good time and frequency.

Explain SQL statements should be custom actions in day-to-day development, and sometimes the results of explain may be unexpected in terms of deviations from design, so

* * It is strongly recommended that in designing SQL, especially for slightly complex SQL, be sure to explain** in the test environment and even the actual environment

2.MySQL Slow Query log

In general, you should open the slow query log of MySQL (add log_slow_queries and long_query_time two parameters in my.cnf), and record all queries that are longer than the Long_query_time SQL statement. After log these statements, then analyze (explain) optimization.

3. Monitoring the current process

Login to MySQL, use show processlist to view the running SQL statements, if you are running too many statements, running too long, indicating that there is a problem with MySQL efficiency. When necessary, the corresponding process can be killed.

4. System command

Use system commands such as Top/vmstat to check the CPU, memory, and disk IO consumed by the MySQL process.