MySQL optimization show status View MySQL server status information

In the lamp structure of the Web site development process, sometimes we need to understand the MySQL server status information, such as the current MySQL startup running time, the current MySQL client session connection number, the current MySQL server execution of the slow query number, The current MySQL implementation of how many SELECT statements, how many update/delete/insert statements, such as statistics, so that we can adapt to the current MySQL server running state of the corresponding adjustment or optimization work.

In MySQL, we can use the Show Status command statement to view the status information of the MySQL server. Below, we connect MySQL in the form of a DOS command window and execute show status, and we will see the following display information:
Perform some of the results shown in the show status directive

When we execute the show status statement, MySQL will list up to 300 records of status information, including a variety of information for us to look at. However, if you get more than 300 records directly using the show status command, we will be dazzled, so we want to be able to "view" part of the status information. At this point, we can add the corresponding like clause after the show status statement. For example, if we want to see the elapsed time after the current MySQL startup, we can execute the following statement:

--Query the current MySQL after the start of the running statistics time

The code is as follows	Copy Code
Show status like ' uptime ';

At this point, we can see the following results:

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Uptime | 5667 |
+---------------+-------+
1 row in Set (0.00 sec)

Similarly, if we want the number of SELECT statements executed after this MySQL startup, we can execute the following statement:

The code is as follows	Copy Code
Show status like ' Com_select ';

The corresponding output results are as follows:

+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Com_select | 1 |
+---------------+-------+
1 row in Set (0.00 sec)

In addition, similar to the LIKE keyword in the WHERE clause, show status can also use ' _ '
or '% ' wildcard characters for fuzzy matching. For example, we can execute the following statement to view the thread information for the MySQL server:

The code is as follows	Copy Code
Show status like ' thread_% ';

The corresponding output results are as follows:

+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| threads_cached | 0 |
| threads_connected | 1 |
| threads_created | 1 |
| threads_running | 1 |
+-------------------+-------+
4 rows in Set (0.00 sec)

It is noteworthy that, in the example of the execution of the show status like ' com_select ' instruction, the SELECT statement statistics displayed only represent the number of SELECT statements executed by the current session connection. Because the complete syntax for the show status directive is as follows:

Show [statistical scope] status [like ' Status Item name ']
--The statistical range keyword is divided into global and session (or local) two.

In the complete syntax for show status, the section in [] is optional, and if our show status statement does not contain a statistical range keyword, the default statistic range is session, which is to count only the status information for the current connection. If we need to query the total number of SELECT statements executed by all connections since the current MySQL startup, we can execute the following statement:

The code is as follows	Copy Code
Show global status like ' Com_select ';

The above is the detailed usage of show status. Since show status has more state statistics, we are no longer one by one to explain the specific meaning of each statistic, where we list only some of the commonly used status information viewing statements:

The code is as follows

Copy Code

--See how long MySQL runs after this startup (in seconds) Show status like ' uptime '; --View the number of executions of a SELECT statement Show [global] status like ' Com_select '; --View the number of executions of the INSERT statement Show [global] status like ' Com_insert '; --View the execution number of the UPDATE statement Show [global] status like ' com_update '; --View the number of executions of a DELETE statement Show [global] status like ' Com_delete '; --View the number of connections attempting to connect to MySQL (regardless of whether the connection was successful) Show status like ' connections '; --View the number of threads within the thread cache. Show status like ' threads_cached '; --View the number of connections currently open. Show status like ' threads_connected '; --View the number of connections currently open. Show status like ' threads_connected '; --View the number of threads created to handle the connection. If the threads_created is larger, you may want to increase the thread_cache_size value. Show status like ' threads_created '; --View the number of active (non-sleep state) threads. Show status like ' threads_running '; --the number of times to view the locks of the immediately obtained table. Show status like ' Table_locks_immediate '; --the number of times to view the lock of a table that cannot be obtained immediately. If the value is higher and there is a performance problem, you should first optimize the query and then split the table or use replication. Show status like ' table_locks_waited '; --View the number of threads created longer than slow_launch_time seconds. Show status like ' Slow_launch_threads '; --View the number of queries that are longer than long_query_time seconds. Show status like ' Slow_queries ';

The above is only the optimization of the server of course, we can also optimize the SQL query statements, related articles: MySQL enable slow query logging method