Small shell _mysql SQL statement optimization process

SQL statement Optimization I. General steps for SQL optimization

(1), through the show status command to understand the frequency of execution of various SQL.

(2), positioning execution Less efficient SQL statement-(key Select)

(3) Explain analysis of inefficient SQL statement execution

(4), identify the problem and take corresponding optimization measures

(1) Showstatus command to understand the frequency of various SQL execution

After the MySQL client connection succeeds, the server status information can be provided by using the Show[session|global] status command. The session to represent the statistics of the current connection, and global to represent the statistical results from the date the database was last started. The default is session level.

The following example: Show status like ' com_% ';

Where com_xxx represents the number of times that the XXX statement was executed.

Key notes:

Com_select,com_insert,com_update,com_delete with these parameters, it is easy to see whether the current database application is primarily for insert updates or query operations, and what is the approximate scale of execution for various types of SQL.

Parameter description:

Com_xxx represents the number of executions per XXX statement.

Com_select represents the number of times a select operation is performed, and only one query is cumulative plus 1

Com_update indicates the number of times the update operation was performed

Com_insert indicates the number of times the insert operation was performed, and the bulk insert was counted only once

Com_delete indicates the number of times the delete operation was performed

Parameters for the InnoDB storage engine only

innodb_rows_deleted the number of rows affected by the delete operation

innodb_rows_inserted the number of rows affected by the insert operation

Innodb_rows_read the number of rows affected by the select operation performed

innodb_rows_updated the number of rows affected by the update operation

There are several common parameters that make it easy for users to understand the basics of a database.

Connections: Number of attempts to connect to the MySQL server (success or failure will accumulate)

Uptime: The time the server is working (in seconds)

Slow_queries: Number of slow queries (default is 10)

(2), positioning the execution of less efficient SQL statements

By turning on slow query

(3) Explain analysis of inefficient SQL statement execution

(4), identify the problem and take corresponding optimization measures

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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Small shell _mysql SQL statement optimization process