How to locate, eliminate and avoid MySQL performance faults

The first is how to check SQL efficiency. 1. make good use of explain: After designing the SQL, you should use the explain command to check whether the index is used and whether filesort exists. Check whether the number of retrieved rows (rows) is too large. Generally, the. rows1000 is in an acceptable range. Rows in 1000 ~ Between, performance problems may occur during intensive access

The first is how to check SQL efficiency. 1. make good use of explain: After designing the SQL, you should use the explain command to check whether the index is used and whether filesort exists. Check whether the number of retrieved rows (rows) is too large. Generally, the. rows1000 is in an acceptable range. Rows in 1000 ~ Between, performance problems may occur during intensive access

The first is how to check SQL efficiency.

1. Make good use of explain:

After designing the SQL statement, run the explain command to check whether the index is used and whether filesort exists. Check whether the number of retrieved rows (rows) is too large.

In general.

Rows <1000 is acceptable.

Rows in 1000 ~ Performance problems may occur during intensive access within, but it is acceptable if the access is not too frequent (once every minute) and it is difficult to optimize it. However, you should pay attention to it.

When rows is greater than 10 thousand, you should carefully consider the SQL design, optimize SQL, and optimize the database. Generally, frequent operation is not allowed (the frequency is less than 1 hour ).

When the number of rows reaches, it cannot be used as a real-time running SQL statement. Except for data import, the time and frequency of data import must be well controlled.

The explain SQL statement should be a habitual action in daily development. Sometimes the explain result may be out of the unexpected deviation from the design.

** It is strongly recommended that you explain SQL statements in the test environment or even the actual environment in advance when designing SQL statements, especially SQL statements that are slightly complex **

2. MySQL slow query log

In general, you should open the MySQL slow query log (in my. in cnf, The log_slow_queries and long_query_time parameters are added to record all SQL statements whose query duration exceeds long_query_time. After these statements are logged, they are analyzed one by one (explain) for optimization.

3. Monitor the current process

Log on to MySQL and use show processlist to view the running SQL statements. If there are too many running statements and the running time is too long, the MySQL efficiency is faulty. If necessary, kill the corresponding process.

4. system commands

Use top/vmstat and other system commands to check the cpu, memory, and disk I/O usage of MySQL processes.

There are a lot of articles on MySQL optimization. Here we only mention some common methods in my daily work.

◆ When creating a table, explicitly specify the innodb database engine instead of myisam. The myisam engine locks are table locks, read locks and write locks are mutually exclusive, and read/write operations are sequential, lock conflicts will seriously affect concurrency. innodb provides row-level locks to provide good concurrency performance. In our business scenarios, it will not cause deadlocks.

◆ Use indexes to properly create indexes for fields used in the where condition of SQL statements. Although creating an index on a table affects the write efficiency to some extent, the index brings more benefits when the table data size is not large and the write pressure is not very high.

◆ When SQL statements are dynamically generated by Code, for example, different where parameters are added according to user operations during running, the typical and boundary conditions of SQL generation should be tested in the test phase, check whether it may cause performance problems. Some logs should be generated appropriately for extraction of the final generated SQL for efficiency analysis.

◆ Data should be reasonably divided into database and table shards, and the application layer should dynamically select databases and tables. Although the innodb table of MySQL can contain massive amounts of data in theory, it is reasonable to control data below million in our business scenarios. In pursuit of performance, it is best to control the table below million, reasonable index.

◆ Use left join/right join instead of Multi-table join when joint queries are required.

◆ Try not to use select to set the select compound query. If you can split it, try to split it as much as possible. The combination of multiple lean SQL statements may be a huge SQL statement, which should be avoided.

◆ Make good use of the cache to load infrequently modified, limited data volume, and intensive query information into the cache, effectively reducing the pressure on the database. Open-source memcache is recommended for general business scenarios, which is simple and efficient.

◆ If some logics can be completed at the application layer, you can consider putting them at the application layer. However, if the SQL logic is split into the application layer, which may lead to more frequent data access, you need to consider modifying the application logic, data structure, or returning to a reasonable joint query.

For example, the sorting of some data can be loaded into the php array, and then sort. for example, if you want to query tables A and B, the data in Table A is A comparative description of A field in Table B (for example, A: t_service table, B. t_task table). Table A has A limited amount of data and can be used for joint queries. You can also load Table A to A process or memory, cache Table A with A hash structure, and then query table B, then, query the hash in the cache to obtain the comparison description.

◆ About data import and statistical query. data Import puts a lot of pressure on the database in terms of computing and disk I/O. The database pressure should be reasonably apportioned in terms of time and space. If you need to export specific batch data for analysis, A database server dedicated to data analysis should be established, or a temporary database table should be created to export data first and then perform analysis operations on it.

Operations that may cause Batch Data Reading, such as data import, should be scheduled tasks, in the time when the database is not busy (1 ~ 7) running a general statistical operation requires little real-time performance (5 ~ For more than 10 minutes, even one day, one week, etc.), such data should not be exported from the database directly count, group, but from the scheduled task at each access, create a result table or intermediate result table for end users.

◆ The operation permissions on the production database should be strictly controlled, and developers should be cautious when running SQL statements directly on the production database.

To achieve the above, we can basically get started with MySQL and related system optimization, so we can ensure that our database will not be exhausted all day long.

Finally, even if you have done your homework, you still need to observe and monitor the running status of the database as soon as possible to discover its performance bottleneck and solve it before it causes no harm.