MySQL Show related commands

Processlist's show way is not using filter lookup, may originate from the internal security mechanism of MySQL, show is used to view the internal MySQL running data, in fact, Processlist is

Information_schema a table in the database, the way to look up a table is certainly possible:

SELECT user, host, time, command, time

From [mysql|information_schema].processlist

WHERE the user = ' me ' and state are not NULL;

A few details of the MySQL show command are also attached, for review:

Show tables or show tables from database_name or show database_name.tables;

Explanation: Displays the names of all tables in the current database

show databases;

Explanation: Displays the names of all databases in MySQL

Show Processlist;

Explanation: 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 the process for everyone, including passwords.

Show table status;

Explanation: 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

Show columns from table_name to database_name; or show columns from database_name.table_name; or show fields;

Explanation: Displays the column names in the table (and the effect of the DESC table_name command is the same)

Show grants for [email protected];

Explanation: Displays a user's permissions, displaying results similar to the grant command

Show index from TABLE_NAME; or show keys;

Explanation: Displaying the index of a table

Show status;

Explanation: Displays information about some system-specific resources, such as the number of threads that are running

Show variables;

Explanation: Display the name and value of a system variable

Show privileges;

Explanation: Display different permissions supported by the server

Show CREATE DATABASE database_name;

Explanation: Displays the SQL statement that created the specified database

Show CREATE TABLE table_name;

Explanation: Displays the SQL statement that created the specified data table

Show Engies;

Explanation: Displays the storage engine and the default engine that are available after installation.

Show InnoDB status;

Explanation: Displaying the status of the InnoDB storage engine

Show logs;

Explanation: Displaying logs for the BDB storage engine

Show warnings;

Explanation: Displays errors, warnings, and notifications generated by the last statement executed

Show errors;

Explanation: Display only the error resulting from the last execution statement

Most of the above commands can be used like, such as show Table '%abce% '.

Report:

Show status Result description

Column meaning

Name table name

type of table (Isam,myisam or heap)

Row_format Row storage format (fixed, dynamic, or compressed)

Rows Row Quantity

Avg_row_length Average length of the President

Data_length length of the data file

Max_data_length Maximum length of the data file

Index_length length of index file

Data_free The number of bytes allocated but not used

Auto_increment next AutoIncrement (auto plus 1) value

Create_time The time the table was created

Update_time When the data file was last updated

Check_time last time a check is run on the table

Create_options additional options for use with CREATE TABLE

Comment When creating a table, use a comment (or why MySQL cannot access some information about the table information).

Show Index result Description:

Column meaning

Table name

Non_unique 0 If the index cannot contain duplicates.

Key_name Index name

Seq_in_index The column order number in the index, starting at 1.

column_name column name.

How the Collation column is sorted in the index. In MySQL, this can have a value of a (ascending) or null (not sorted).

the number of unique values in the cardinality index. This can be changed by running Isamchk-a.

Sub_part If the column is only partially indexed, the number of index characters. NULL if the entire key is indexed.

Show variables result Description:

Aborted_clients The number of connections that have been discarded because the client did not properly close the connection already dead.

Aborted_connects the number of times the connection to the MySQL server has failed.

Connections The number of attempts to connect to the MySQL server.

Created_tmp_tables the number of suppressed temporary tables that have been created when the statement is executed.

Delayed_insert_threads The number of deferred plug-in processor threads being used.

Delayed_writes The number of rows written with the insert Delayed.

Delayed_errors the number of rows written with insert Delayed for some errors (possibly repeating key values).

Flush_commands the number of times the flush command was executed.

Handler_delete the number of times a row was requested to be deleted from a table.

the number of times the Handler_read_first request reads the first row in the table.

The Handler_read_key requests a number based on the key read line.

the number of times a handler_read_next request reads into a row based on one key.

the number of times a HANDLER_READ_RND request reads into a row based on a fixed position.

Handler_update the number of times a row in the table was requested to be updated.

the number of times the Handler_write request inserts a row into the table.

Key_blocks_used the number of blocks used for the keyword cache.

Key_read_requests the number of times a key value was requested to be read from the cache.

Key_reads the number of times a key value has been physically read from disk.

Key_write_requests requests a keyword block to be written to the cache count.

Key_writes the number of times a key-value block is physically written to disk.

Max_used_connections The maximum number of connections that are used at the same time.

Not_flushed_key_blocks a key block that has been changed in the key cache but has not been emptied to disk.

Not_flushed_delayed_rows the number of rows waiting to be written in the Insert delay queue.

Open_tables The number of open tables.

Open_files The number of open files.

Open_streams Number of open streams (primarily for log records)

Opened_tables the number of tables that have been opened.

Questions the number of queries destined for the server.

Slow_queries The number of queries that will take more than long_query_time time.

Threads_connected the number of connections currently open.

Threads_running The number of threads that are not sleeping.

Uptime How many seconds the server has worked.

Some comments on the above:

If the opened_tables is too large, then your table_cache variable may be too small.

If the key_reads is too big, then your key_cache may be too small. Cache hit ratios can be calculated using Key_reads/key_read_requests.

If the handler_read_rnd is too large, then you are likely to have a large number of queries that require MySQL to scan the entire table or you have a junction (join) that does not correctly use the key value.

MySQL Show related commands