Ways to understand how frequently SQL is executed

Once the MySQL client connection succeeds, the show [session|global]status command can provide server status information, or you can use the Mysqladmin extended-status command on the operating system to obtain these messages.

Show [Sessionglobal] status can be combined with the parameter "session" or "global" as needed to display the statistical results of the session level (current connection) and the global level (since the database was last started). If you do not write, the default parameter is "session".

Com_xxx represents the number of times each XXX statement executes, and we are usually more concerned with the following statistical parameters:

Com_select: The number of times a select operation is performed, and only 1 is added to a query.

Com_insert: The number of times the insert operation was performed, and the insert operation for bulk inserts is incremented only once.

Com_update: The number of times the update operation was performed.

Com_delete: The number of times the delete operation was performed.

Knowing this information will make it easier for us to determine what kind of storage engine we need, and in general the INNODB engine will be a little more efficient when we update more.

The above parameters are cumulative for all the table operations of the storage engine. The following parameters are only for the InnoDB storage engine, and the cumulative algorithm is slightly different:

The number of rows returned by the Innodb_rows_read:select query.

Innodb_rows_inserted: The number of rows inserted by the insert operation.

Innodb_rows_updated: The number of rows updated by the update operation.

Innodb_rows_deleted: The number of rows deleted by the delete operation.

With the above parameters, it is easy to see whether the current database application is primarily based on insert updates or query operations, and how much of the SQL is roughly executed for each type. For the count of update operations, the count of the number of executions, whether committed or rolled back, is cumulative.

For transaction-type applications, the case of transaction commit and rollback can be understood through com_commit and Com_rollback, and a database with very frequent rollback operations may mean that there is a problem with application writing.

In addition, the following parameters allow us to understand the basics of the database:

Connections: Number of attempts to connect to the MySQL server.

Uptime: Server working time.

Slow_queries: The number of slow queries.