Shulou(Shulou.com)06/01 Report--

Let's learn about the relevant knowledge of MySQL information_schema library. I believe you will benefit a lot after reading it. The text is not much in essence. I hope this short article about MySQL information_schema library knowledge is what you want.

Mysql > show create table test.test\ G

1 row in * * 1.row * *

Table: test

Create Table: CREATE TABLE `test` (

`id`int (11) unsigned NOT NULL AUTO_INCREMENT

`a`int (11) 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 sets and collations

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.

Character set (character sets) stores strings and refers to the smallest semantic symbol in human language. For example,'A','B', etc.

Collation (collations) rules compare strings. Collations is a comparison rule between characters in the same character set.

Each character order uniquely corresponds to a character set, but a character set can correspond to multiple character orders, one of which is the default character order (Default Collation)

Character order names in MySQL follow naming conventions: they start with the character set name corresponding to the character order, and end with _ ci (for case insensitivity), _ cs (for case sensitivity), or _ bin (for comparison by coded value). For example, under the character order "utf8_general_ci", the characters "a" and "A" are equivalent.

Take a look at the MySQL variables related to character sets 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 of 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

Take another look at the character set conversion process in MySQL:

(1) when MySQL Server receives a 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 internal operation character set before internal operation, the determination method is as follows:

Use the CHARACTER SET of each data field to set the value

If the above value does not exist, the DEFAULT CHARACTER SET setting value of the corresponding data table is used (MySQL extension, non-SQL standard)

If the above value does not exist, the DEFAULT CHARACTER SET of the corresponding database is used to set the value

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.

2. Tables related to permissions

SCHEMA_PRIVILEGES-provides permissions for the database. The information comes from mysql.db.

TABLE_PRIVILEGES-provides information about the permissions of the table, which comes from the

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, which comes from the mysql.user table

3. Some tables that store entity objects of the database system

COLUMNS-- select * from COLUMNS where TABLE_NAME='test'\ G is equivalent to desc test.test or show columns from test.test

INNODB_SYS_COLUMNS-holds the metadata of INNODB, which relies on the statistical table SYS_COLUMNS.

ENGINES-supported engine and default engine

EVENTS-equivalent to show events or mysql.event, backup needs to add-- event, master library sets event_scheduler=ON, slave library event_scheduler=OFF

FILES-- tablespace file, information about the file stored by the data in the tablespace of MySQL, the location where the file is stored

PARAMETERS-- the parameter table stores the parameters of some stored procedures and methods, as well as the return value information of the stored procedure. Storage and methods are stored in ROUTINES.

PLUGINS-basically the plug-in information of MySQL, whether it is active or not, etc. SHOW PLUGINS's information comes from this

ROUTINES-some information about stored procedures and methods function and help documentation, but this information does not include user-defined information, just some information about the system. Mysql.proc name

SCHEMATA-this table provides how many databases there are under the instance, as well as the default character set of the database

The TRIGGERS table records the information of the trigger, including all relevant information. Systematic and user-created triggers.

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

Fourth, constrain foreign keys and other related tables

REFERENTIAL_CONSTRAINTS-provides information about foreign keys, and only provides information about foreign keys

TABLE_CONSTRAINTS-provides relevant constraint information and is more comprehensive

INNODB_SYS_FOREIGN_COLS-the metadata information stored by INNODB about foreign keys is consistent with the information stored by SYS_FOREIGN_COLS

INNODB_SYS_FOREIGN-the metadata information stored by INNODB about foreign keys is consistent with the information stored by SYS_FOREIGN_COLS, but only for INNODB alone

KEY_COLUMN_USAGE-describes key columns with constraints.

5. Some tables on management:

GLOBAL_STATUS-not available, see the documentation for 'show_compatibility_56' show global status

GLOBAL_VARIABLES-not available, see the documentation for 'show_compatibility_56' show global variables

SESSION_STATUS-not available, see the documentation for 'show_compatibility_56' show status

SESSION_VARIABLES-not available, see the documentation for 'show_compatibility_56' show variables

PARTITIONS-Information related to the MySQL partition table

PROCESSLIST-- SHOW PROCESSLIST data comes 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-stores buffer pool information about MySQL INNODB's compressed pages, related to show variables like 'innodb_cmp_per_index_enabled';, and opens to affect performance

INNODB_CMPMEM_RESET-stores buffer pool information about MySQL INNODB's compressed pages, related to show variables like 'innodb_cmp_per_index_enabled';, and opens to affect performance

INNODB_BUFFER_POOL_STATS-buffer pool-related information about INNODB, which is the same as that provided by show engine innodb status. It is also a source of information for show engine innodb status.

INNODB_BUFFER_PAGE_LRU-maintains information about INNODB LRU LIST

INNODB_BUFFER_PAGE-the page data buffered in buffer is stored. Querying this table will have a serious impact on performance.

INNODB_SYS_DATAFILES-- this table is a correspondence between the file storage location of the recorded table and the table space.

INNODB_TEMP_TABLE_INFO-this table records information used by all users of all INNODB, but only in memory and without persistence.

INNODB_METRICS-provides various performance indices for INNODB, complements INFORMATION_SCHEMA and collects system statistics for MySQL. These statistics can be manually configured to turn 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 compression of INNODB information tables

INNODB_CMP_RESET-stores information about the compression of INNODB information tables

VI. Some tables about table information and index information

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

TABLESPACES-this table does not provide tablespace information about innodb and is not of much use to us because our production library is mandatory for INNODB

INNODB_SYS_TABLES-this table provides information about the format and storage characteristics of the table, including row formatting, compressed page size bit level

INNODB_SYS_TABLESPACES-provides tablespace information about INNODB, which is actually consistent with the INNODB information in SYS_TABLESPACES.

STATISTICS-provides index information about the table, information about all indexes.

INNODB_SYS_INDEXES-provides information about the index of the relevant INNODB table, which is basically the same as the information stored in the SYS_INDEXES table

INNODB_SYS_TABLESTATS-- important, it records the INNODB table information of MySQL and the MySQL optimizer predicts that SQL will choose the appropriate index information, which is actually the statistical information of the MySQL database.

INNODB_SYS_FIELDS-stores the table index field information of the INNODB and the ranking of the fields

INNODB_FT_CONFIG-stores full-text indexed information

INNODB_FT_DEFAULT_STOPWORD-stores stopword information and is used to match full-text indexing

INNODB_FT_INDEX_TABLE-stores information about the index usage of the INNODB table with a full-text index. Similarly, this table can only be used after innodb_ft_aux_table is set, which is generally empty.

INNODB_FT_INDEX_CACHE-stores pre-insert record information, but also to avoid expensive index reorganization during DML

7. Some tables related to MySQL optimization

OPTIMIZER_TRACE-View the selection of the execution plan, set optimizer_trace= "enabled=on"; SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; desc select *...; select OPTIMIZER_TRACE\ G

PROFILING-- SHOW PROFILES, cannot view and parse the statements of other connections, as well as the consumption caused by profiling. Setting profiling_history_size to 0 is equivalent to turning off the analysis function.

INNODB_FT_BEING_DELETED-INNODB_FT_BEING_DELETED this table is a snapshot of INNODB_FT_DELETED

INNODB_FT_DELETED-only used when OPTIMIZE TABLE

8. Some tables about MySQL things and locks

The INNODB_LOCKS INNODB_LOCKS table mainly contains the details of the InnoDB transaction lock, including the lock being applied for by the transaction and the lock added by the transaction.

The INNODB_TRX INNODB_TRX table mainly contains information about all transactions being executed in the InnoDB engine, including transactions for waiting for a lock and running

The INNODB_LOCK_WAITS INNODB_LOCK_WAITS table contains the lock wait status of the blocked transaction

After reading this article on the knowledge of MySQL information_schema library, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.