Beckham _ mysql SQL statement optimization process, Beckham _ mysqlsql statement

Beckham _ mysql SQL statement optimization process, Beckham _ mysqlsql statement
SQL statement OptimizationI. general steps for SQL Optimization

(1) Use the show status command to understand the execution frequency of various SQL statements.

(2) locate SQL statements with low execution efficiency-(select)

(3) analyze the execution of inefficient SQL statements through explain

(4) determine the problem and take corresponding optimization measures

(1) Use the showstatus command to learn the frequency of running various SQL statements.

After the MySQL client is successfully connected, you can use the show [session | global] status command to provide server status information. The session indicates the statistical results of the current connection, and the global indicates the statistical results since the last time the database was started. The default value is session level.

Example: show status like 'com _ % ';

Com_XXX indicates the number of times the XXX statement is executed.

Important:

With these parameters Com_select, Com_insert, Com_update, and Com_delete, you can easily see whether the current database application is dominated by insert update or query operations, and the approximate execution ratio of various types of SQL statements.

Parameter description:

Com_xxx indicates the number of executions of each xxx statement. For example:

Com_select indicates the number of select operations, and only 1 is added for each query.

Com_update indicates the number of update operations.

Com_insert indicates the number of insert operations performed. Batch insert is counted only once.

Com_delete indicates the number of delete operations

Only for innodb Storage engine parameters

Innodb_rows_deleted: number of rows affected by the delete operation

Innodb_rows_inserted: number of rows affected by the insert operation

Innodb_rows_read: number of rows affected by the select Operation

Innodb_rows_updated: number of rows affected by the update operation

There are also several common parameters to help you understand the basic information of the database.

Connections: number of attempts to connect to the MySQL server (both succeed and fail)

Uptime: the server's working time (unit: seconds)

Slow_queries: Number of slow queries (10 by default)

(2) locate SQL statements with low execution efficiency

Enable slow Query

(3) analyze the execution of inefficient SQL statements through explain

(4) determine the problem and take corresponding optimization measures

The quieter you become, the more you are able to hear!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.