MySQL Information_schema Library Learning

Mysql> Show CREATE TABLE Test.test\g

1 row in *************************** 1. Row ***************************

Table:test

Create table:create Table ' test ' (

' id ' int (one) unsigned not NULL auto_increment,

' A ' int (one) unsigned DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' A ' (' a ')

) Engine=innodb auto_increment=6 DEFAULT Charset=utf8set (0.00 sec)

Parameters

Information_schema

I. System tables related to character set and collation

Character_sets--Character Set

Collations--Character Set check set

Collation_character_set_applicability--the relationship between the character set and the character check set these columns are equivalent to the first two display fields of show COLLATION.

The character set (character sets) stores strings, which are the smallest of the semantic symbols in human languages. such as ' A ', ' B ' and so on;

Collation (collations) Rules compare strings, collations refers to the comparison rule between characters within the same character set

Each word Fu She uniquely corresponds to a character set, but a character set can correspond to multiple characters Fu She, one of which is the default word Fu She

The word Fu She name in MySQL follows the naming convention: begins with the character set name of the word Fu She, _ci (which is case insensitive), _cs (which is case sensitive), or _bin (which means comparison by encoded value). For example: Under the word Fu She "Utf8_general_ci", the characters "a" and "a" are equivalent

Take a look at the MySQL variables related to character set and proofreading:

Character_set_client: The character set used by the client source data

Character_set_connection: Connection Layer Character Set

Character_set_database: The default character set for the currently selected database

Character_set_system: System metadata (field name, etc.) character set

Character_set_server: Default internal operation character Set

Character_set_results: Query result Character Set

And look at the character set conversion process in MySQL:

(1) When MySQL server receives the request, it converts the request data from character_set_client to character_set_connection;

(2) To convert the request data from character_set_connection to the internal operation character set before the internal operation, the method is determined as follows:

Use the character set set value for each data field;

If the above value does not exist, the default CHARACTER set value of the corresponding data table is used (MySQL extension, non-SQL standard);

If the above value does not exist, the default CHARACTER set value of the corresponding database is used;

If the above value does not exist, use Character_set_server to set the value.

(3) Convert the operation result from the internal operation character set to Character_set_results.

Ii. Permissions-related tables

Schema_privileges-Provides access to the database, information from mysql.db

Table_privileges-Provides information about table permissions, information from the Mysql.tables_priv table loaded

Column_privileges--Gives information about column permissions. This information is derived from the Mysql.columns_priv authorization form

User_privileges-Provides information about table permissions, information from the Mysql.user table

Third, some tables of the entity objects that store the database system

COLUMNS--select * from COLUMNS where table_name= ' test ' \g is equivalent to Desc test.test or show co Lumns from Test.test;

Innodb_sys_columns--The INNODB metadata is stored, and he is dependent on the statistical table of Sys_columns.

ENGINES--supported engine and default engine

Events-equivalent to show events or mysql.event, Backup is to add--event, main Library settings event_scheduler=on, from library event_sched Uler=off

Files--Table space files, the data stored in the table space of the MySQL file, the location of the file storage,

PARAMETERS--The parameter table stores the parameters of stored procedures and methods, as well as the return value information of the stored procedure. Storage and methods are stored inside the routines.

PLUGINS--basically MySQL plug-in information, whether it is active status and other information. SHOW Plugins information from this

ROUTINES--Some information about stored procedures and method function, and help documentation, but this information does not include user-defined, but only some information about the system. Mysql.proc Name

Schemata--This table provides the number of databases under the instance, and also the default character set of the database

TRIGGERS This table records the information of the trigger, including all relevant information. The system's and its own user-created triggers.

Views-the information of the view, is also the basic view information of the system and the user.

Iv. some tables related to constraint foreign keys, etc.

Referential_constraints-Provides foreign key-related information and provides only foreign key-related information

Table_constraints-Provides the relevant constraint information, more comprehensive

Innodb_sys_foreign_cols-Stored INNODB metadata information about foreign keys is consistent with sys_foreign_cols stored information

Innodb_sys_foreign-Stored INNODB metadata information about foreign keys is consistent with sys_foreign_cols stored information, except for INNODB alone

Key_column_usage--Describes the key columns with constraints.

V. Some of the tables on management:

Global_status-not checked, see the documentation for ' show_compatibility_56 ' show global STATUS

Global_variables-not checked, see the documentation for ' show_compatibility_56 ' show global VARIABLES

Session_status-not checked, see the documentation for ' show_compatibility_56 ' show STATUS

Session_variables-not checked, see the documentation for ' show_compatibility_56 ' show VARIABLES

Partitions--MySQL partition table related information

Processlist--show processlist data from this table

Innodb_cmp_per_index-stores information about compressed INNODB tables

Innodb_cmp_per_index_reset-stores information about compressed INNODB tables

Innodb_cmpmem--Store the buffer pool information about the compressed page of MySQL INNODB, and show variables like ' innodb_cmp_per_index_en Abled '; about, turning on impact performance

Innodb_cmpmem_reset--Store the buffer pool information about the compressed page of MySQL INNODB, and show variables like ' innodb_cmp_per_index_en Abled '; about, turning on impact performance

Innodb_buffer_pool_stats-Information about the INNODB BUFFER POOL and the information provided by show engine INNODB status are the same. It is also the source of information for show engine InnoDB status.

INNODB_BUFFER_PAGE_LRU--maintains the information about the INNODB LRU list

Innodb_buffer_page-The cached page data is stored in buffer, and querying the table can have a serious impact on performance.

Innodb_sys_datafiles-This table is a correspondence between the location of the file stored in the recorded table and the table space

Innodb_temp_table_info-This table records all the information used by all users of the INNODB, but can only be recorded in memory and no persisted information.

Innodb_metrics-Provides a variety of performance indices for INNODB, which is a supplement to Information_schema, which collects MySQL system statistics. These statistics can be manually configured to be turned on or off. The following parameters can be controlled: innodb_monitor_enable, innodb_monitor_disable, Innodb_monitor_reset, Innodb_monitor_reset_all.

Innodb_sys_virtual--stores information about the virtual columns of the INNODB table,

INNODB_CMP-stores information about the time the INNODB information table is compressed

Innodb_cmp_reset-stores information about the time the INNODB information table is compressed

Vi. Some tables on table information and index information

TABLES-Records information about tables in the database, including system databases and user-created databases. The source of the show table status like ' Test1 ' \g is this table

Tablespaces-This table does not provide the tablespace information about InnoDB, which is not very useful for us because our production library is mandatory innodb;

Innodb_sys_tables--This table provides information about the formatting and storage characteristics of tables, including row formats, compressing page size bit levels

Innodb_sys_tablespaces-Provides a table-space information about INNODB, which is in fact consistent with the INNODB information in Sys_tablespaces.

STATISTICS-Provides information about the table's index information, all indexes.

Innodb_sys_indexes--Provides information about the index of the relevant INNODB table, and the information stored in the Sys_indexes table is basically the same,

Innodb_sys_tablestats-important, the log is MySQL INNODB table information and the MySQL optimizer will estimate SQL to choose the appropriate index information, in fact, is the MySQL database statistics

Innodb_sys_fields--The table index field information that holds the INNODB, and the rank of the field

Innodb_ft_config--The information that is stored in the full-text index

Innodb_ft_default_stopword--The information stored is STOPWORD, which is used to match the full-text index.

Innodb_ft_index_table--Storage is about the INNODB table has a full-text index of the index usage information, the same table is to be set innodb_ft_aux_table to be able to use later, generally empty

Innodb_ft_index_cache-Storage is the pre-insertion record information, also to avoid DML when expensive index reorganization

Vii. some tables related to MySQL optimization

Optimizer_trace--View the selection of execution plan, set optimizer_trace= "Enabled=on"; SET optimizer_trace_max_mem_size=1000000; Desc SELECT * ...; Select Optimizer_trace\g

PROFILING--show PROFILES, you cannot view and parse other connected statements, as well as the consumption caused by profiling. Profiling_history_size set to 0 is equivalent to turning off the analysis function

Innodb_ft_being_deleted--innodb_ft_being_deleted This watch is a snapshot of innodb_ft_deleted.

innodb_ft_deleted--OPTIMIZE TABLE is only used when

Viii. some tables related to MySQL things and locks

The Innodb_locks innodb_locks table mainly contains the specifics of the INNODB transaction lock, including the lock that the transaction is requesting and the lock the transaction is added to.

The Innodb_trx Innodb_trx table primarily contains information about all the transactions that are being performed in the INNODB engine, including waiting for a lock and running transactions

Innodb_lock_waits innodb_lock_waits table contains the state of the lock waits for a transaction of blocked

Reference: https://www.cnblogs.com/shengdimaya/p/6920677.html

MySQL Information_schema Library Learning