The show command in MySQL is detailed

There are a lot of basic commands in MySQL, and the show command is one of them, and it's easy to confuse the use of SHOW commands among many users, and this article brings together the many uses of the show command.

A. Show tables or show tables from database_name; --Displays the names of all tables in the current database
B. show databases; --Displays the names of all databases in MySQL
C. Show columns from table_name to database_name; or show columns from Database_name.table_name; --Show the column names in the table
D. Show grants for USER_NAME; --Displays a user's permissions, displaying results similar to the grant command
E. Show index from TABLE_NAME; --Display the index of the table
F. Show status; --Displays information about some system-specific resources, such as the number of threads running
G. Show variables; --Displays the name and value of the system variable
H. Show Processlist; --Displays all the processes that are running in the system, that is, the query that is currently executing. Most users can view their own processes, but if they have process permissions, they can see all processes, including passwords.
I. Show table status; --Displays information about each table in the database that is currently being used or specified. Information includes the table type and the last updated time of the table
J. Show Privileges; --Displays the different permissions supported by the server
K. Show CREATE DATABASE database_name; --Show whether the CREATE DATABASE statement can create the specified databases
L. show CREATE TABLE table_name; --Show whether the CREATE DATABASE statement can create the specified databases
M. show Engies; --Displays the storage engine and the default engine that are available after installation.
N. show InnoDB status; --Displays the status of the InnoDB storage engine
O. Show logs; --Displays logs of the BDB storage engine
P. Show warnings; --Displays errors, warnings, and notifications resulting from the last statement executed
Q. Show errors; --Displays only the errors generated by the last execution statement
R. Show [storage] engines; --Displays the available storage engine and the default engine after installation
S. Show procedure status-displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, etc.
T. Show CREATE PROCEDURE Sp_name--show details of a stored procedure

Example:

1 displaying the character set used by the database connection

SHOW VARIABLES like '%char% ';

Variable_name Value
------------------------ ---------------------------------------------------------
Character_set_client UTF8
Character_set_connection UTF8
Character_set_database UTF8
Character_set_filesystem binary
Character_set_results UTF8
Character_set_server UTF8
Character_set_system UTF8
Character_sets_dir C:\Program files\mysql\mysql Server 5.7\share\charsets\

2 Viewing the various status values running on the MySQL server

Show global status;

3 Number of connections

SHOW VARIABLES like ' max_connections ';
Variable_name VALUE
--------------- --------
Max_connections 151

SHOW GLOBAL STATUS like ' max_used_connections ';
Variable_name VALUE
-------------------- --------
Max_used_connections 3

If you set the maximum number of connections to 500, and the number of connections to the response is 498
Max_used_connections/max_connections * 100% = 99.6% (ideal value ≈85%)

4 key_buffer_size
Key_buffer_size is one of the most significant effects on MyISAM table performance, but most of the data in the database is InnoDB

SHOW VARIABLES like ' key_buffer_size ';
SHOW GLOBAL STATUS like ' key_read% ';
SHOW GLOBAL STATUS like ' key_blocks_u% ';
Key_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks ever used
Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈18% (ideal value ≈80%)
Max_used_connections/max_connections * 100% = 99.6% (ideal value ≈85%)

5 Case of Open Table

SHOW GLOBAL STATUS like ' open%tables% ';

Variable_name Value
------------- --------
Open_tables 236
Opened_tables 1900

Open_tables indicates the number of open tables, opened_tables indicates the number of open tables, and if the opened_tables quantity is too large, the Table_cache in the configuration (5.1.3 This value is called Table_open_cache) The value may be too small, let's check the server Table_cache value

Show variables like ' Table_cache ';

Open_tables/opened_tables * 100% =69% Ideal value (>= 85%)
Open_tables/table_cache * 100% = 100% Ideal Value (<= 95%)

6 use of the process

SHOW GLOBAL STATUS like ' thread% ';

Variable_name Value
----------------- --------
Threads_cached 1
Threads_connected 2
Threads_created 3
Threads_running 1

If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects, the server processes the client's thread to cache in response to the next customer instead of destroying it (provided the cache count is not up to the limit). Threads_created indicates the number of threads created, if the threads_created value is found to be too large, it indicates that the MySQL server has been creating threads, which is also a relatively resource-intensive, can appropriately increase the thread_cache_size value in the configuration file, Query Server Thread_cache_size configuration:

SHOW VARIABLES like ' query_cache% ';

Variable_name Value
---------------------------- ---------
Query_cache_limit 1048576
Query_cache_min_res_unit 4096
Query_cache_size 0
Query_cache_type OFF
Query_cache_wlock_invalidate OFF

Explanation of the fields:  

Query_cache_limit: Queries that exceed this size will not cache  
Query_cache_min_res_unit: The minimum size of the cache block  
Query_cache_size: Query cache size  
Query_cache_type: Cache type, determines what query is cached, and the example indicates that select Sql_no_cache query   is not cached;
Query_ Cache_wlock_invalidate: When there are other clients that are writing to the MyISAM table, if the query is to be returned with the cache result or wait for the write operation to complete, the table gets the result.  

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, setting the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste. &NBSP

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%&NBSP;

If the query cache fragmentation rate exceeds 20%, you can use the flush query Cache defragment, or try to reduce query_cache_min_res_unit if your queries are small data volumes.  

Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%&NBSP;

Query Cache utilization at 25% The following words indicate that the query_cache_size setting is too large, can be appropriately reduced; the query cache utilization is above 80% and qcache_lowmem_prunes > 50 says query_cache_size may be a little small, or too many fragments.  

Query Cache Hit Ratio = (qcache_hits–qcache_inserts)/qcache_hits * 100%&NBSP;

Sample server query cache Fragmentation rate = 20.46%, query Cache utilization = 62.26%, the query cache hit rate = 1.94%, the hit rate is poor, may write more frequent bar, and may be some fragments.

7 file opens (Open_files)

SHOW GLOBAL STATUS like ' open_files ';
SHOW VARIABLES like ' open_files_limit ';

More appropriate setting: Open_files/open_files_limit * 100% <= 75%

8 table Lock Condition

Show global status like ' table_locks% ';

Table_locks_immediate indicates that the number of table locks is released immediately, table_locks_waited indicates the number of table locks that need to wait, if table_locks_immediate/table_locks_waited > 5000, it is best to use the InnoDB engine, because InnoDB is a row lock and MyISAM is a table lock, for high concurrent write application InnoDB effect will be better.

9. Table Scan Condition

SHOW GLOBAL STATUS like ' handler_read% ';

Variable_name Value
--------------------- --------
Handler_read_first 40
Handler_read_key 108
Handler_read_last 0
Handler_read_next 161
Handler_read_prev 0
Handler_read_rnd 15
Handler_read_rnd_next 25915

Calculate table Scan Rate:

Table Scan rate = Handler_read_rnd_next/com_select

If the table scan rate exceeds 4000, indicating that there are too many table scans, it is likely that the index is not built, and that increasing the read_buffer_size value will have some benefits, but it is best not to exceed 8MB.

Normal

The show command in MySQL is detailed