Detailed introduction to information_schema in MySQL

information_schema database is the database that comes with the MySQL system, which provides access to database metadata. It feels like information_schema is like an encyclopedia of MySQL instances, recording most of the information we need to resolve in the database, such as character set , permission related, database entity object information, external inspection Constraints, partitions, compressed tables, table information, index information, parameters, optimization, locks and things, etc. Through information_schema, we can get a glimpse of the running status of the entire MySQL instance, learn the basic information of the MySQL instance, and even optimize and maintain the database. It can be said to be a real encyclopedia, haha.

The following is a general classification of these tables based on some small experiences I have learned, so that everyone can summarize it. If there are any deficiencies, please point them out and I will modify them as soon as possible.

1: System tables related to character sets and collation rules

CHARACTER_SETS: Stores database-related character set information ( memory storage engine)

COLLATIONS: The sorting rules corresponding to the character set

COLLATION_CHARACTER_SET_APPLICABILITY: It is a corresponding relationship between a character set and connection proofreading That’s it

Let’s talk about the difference between character sets and collations:

Character sets (character sets) store strings, which refers to the smallest character set in human language Meaningful symbols. For example, 'A', 'B', etc.;

Collations rules compare strings, collations refers to the comparison rules between characters in the same character set

Each character sequence It only corresponds to one character set, but one character set can correspond to multiple character sequences, one of which is the default character sequence (Default Collation)

The character sequence names in MySQL follow the naming convention: corresponding to the character sequence Begins with the character set name; ends with _ci (indicating case insensitivity), _cs (indicating case sensitivity), or _bin (indicating comparison by encoding value). For example: In the character sequence "utf8_general_ci", the characters "a" and "A" are equivalent

Look at the MySQLvariables related to character sets and proofreading:

character_set_server: Default internal operating character set

character_set_client: Character set used by client source data

character_set_connection: Connection layer character set

character_set_results: Query Result character set

character_set_database: The default character set of the currently selected database

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

Look at MySQL again The character set conversion process in:

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

(2). Before performing internal operations, it converts the request data from character_set_connection is converted to an internal operating character set, and the determination method is as follows:

Use the CHARACTER SET setting value of each data field;

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

If the above value does not exist, use the DEFAULT CHARACTER SET setting value of the corresponding database;

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

(3). Convert the operation results from the internal operation character set to character_set_results.

2: Some tables related to permissions:

SCHEMA_PRIVILEGES: Provides relevant permissions for the database. This table is a memory table from mysql. Pulled out from db.

TABLE_PRIVILEGES: Provides information related to table permissions. The information is loaded from the mysql.tables_priv table

COLUMN_PRIVILEGES: This table can be clear You can see the user object authorized by the table, the table, the database and the permissions granted. If you add the with grant option when authorizing, we can see that the value of PRIVILEGE_TYPE must be YES.

USER_PRIVILEGES: Provides information related to table permissions. The information is loaded from the mysql.user table.

Through the table, we can clearly see the level of MySQL authorization. , SCHEMA, TABLE, COLUMN levels, of course these are all granted based on the user. It can be seen that MySQL authorization is also quite detailed and can be specific to columns. This is still very useful in certain application scenarios, such as auditing.

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

COLUMNS: Stores the field information of the table, all storage engines

INNODB_SYS_COLUMNS: Stores the metadata of INNODB, which depends on the SYS_COLUMNS statistics exists on the table.

ENGINES: Engine type, whether it supports this engine, description, whether it supports things, whether it supports distributed transactions, whether it can support the rollback point of things

EVENTS: Record events in MySQL, similar to scheduled jobs

FILES: This This table provides information about the files stored in the MySQL table space and the location of the file storage. The data of this table is pulled from InnoDB in-memory, so this table itself is also a memory table. , pull again every time you restart. That is the INNODB_SYS_DATAFILES table we are going to talk about below. Another thing to note is that this table contains temporary table information, so it is not equivalent to the SYS_DATAFILES table. You still have to look at INNODB_SYS_DATAFILES. If the undo table space is also configured with InnoDB, it will also be recorded.

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

PLUGINS: Basically MySQL plug-in information, whether it is active status and other information. In fact, SHOW PLUGINS itself pulls moral data through this table

ROUTINES: Some information about stored procedures and method functions, but this information does not include user-defined ones, just Some information about the system.

SCHEMATA: This table provides how many databases there are under the instance, and also the default character set of the database.

TRIGGERS: This table records is the information of trigger, including all related information. System and own user-created triggers.

VIEWS :The information of view is also the basic view information of the system and the user.

These tables store entity objects of some databases, which are convenient for us to query and manage. For a DBA, these tables can greatly facilitate our work and make it faster and more convenient to complete and query the database. Related Information.

4: Some tables related to constraint foreign keys:

REFERENTIAL_CONSTRAINTS: This table provides information related to foreign keys, and only provides Foreign key related information

TABLE_CONSTRAINTS: This table provides related constraint information

INNODB_SYS_FOREIGN_COLS: This table also stores INNODB’s information about foreign keys The metadata information is consistent with the information stored in SYS_FOREIGN_COLS

INNODB_SYS_FOREIGN: The metadata information stored in INNODB about foreign keys is consistent with the information stored in SYS_FOREIGN_COLS, but only for For INNODB

KEY_COLUMN_USAGE: All constrained columns in the database will be saved, and the name and category of the constraint will also be recorded

Why should foreign keys and constraints be listed separately? Because it feels like this is an independent thing. Although most of our production environments will not use foreign keys because it will reduce performance, it is still a good idea to use constraints reasonably. options, such as unique constraints.

5: Some tables about management:

GLOBAL_STATUS, GLOBAL_VARIABLES, SESSION_STATUS, SESSION_VARIABLES: This The four tables record the system variables and status (global and session information) respectively. As a DBA, I believe everyone is familiar with them, and these tables are also reloaded when the system is restarted. That is, the memory table.

PARTITIONS: Information related to the MySQL partition table. Through this table, we can query the related information of the partition (the partitioned table in the database, as well as the partitions of the partition table and each partition data information), for details about partitions, see MySQL Partition Management

PROCESSLIST: show processlist actually pulls data from this table, and the data of PROCESSLIST is its basis. Since it is a memory table, we are equivalent to querying in memory, and these operations are very fast.

INNODB_CMP_PER_INDEX, INNODB_CMP_PER_INDEX_RESET: These two tables store relevant information about compressing the INNODB information table, about the entire table and index information. We know that for an INNODB compressed table, both the data and the secondary index will be compressed, because the data itself can also be regarded as a clustered index. There is a brief introduction to compression tables in information_schema series 11.

INNODB_CMPMEM, INNODB_CMPMEM_RESET: These two tables store buffer pool information about the compressed pages of MySQL INNODB, but one thing to note is that these two tables are used to collect all information. When using the table, it will have a serious impact on performance, so it is turned off by default. If you want to turn on this function, we need to set the innodb_cmp_per_index_enabled parameter to ON.

INNODB_BUFFER_POOL_STATS: The table provides information about the buffer pool of INNODB, which is the same as the information provided by show engine innodb status. It is also the source of information for show engine innodb status.

INNODB_BUFFER_PAGE_LRU, INNODB_BUFFER_PAGE: Maintains information related to INNODB LRU LIST. For details, please see the editor's notes innodb buffer pool explanation

INNODB_BUFFER_PAGE: This table is quite cool, it stores the page data buffered in the buffer. Querying this table will have a serious impact on performance. Do not execute this statement on our own production library unless you can accept a short pause in the service. For details, please see the editor's notes on innodb buffer pool explanation

INNODB_SYS_DATAFILES: This table is a correspondence between the file storage location of the recorded table and the table space (INNODB)

INNODB_TEMP_TABLE_INFO: This table records records All information used by all users of INNODB can only be recorded in memory and has no persistence.

INNODB_METRICS: Provides various performance indexes of INNODB, which is a supplement to INFORMATION_SCHEMA. It collects MySQL system statistics. These statistics can be manually configured to be turned on or off. The following parameters are controllable: innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all.

INNODB_SYS_VIRTUAL: The table stores information about the virtual columns of the INNODB table. Of course, this is relatively simple. In MySQL 5.7, two types of Generated Column are supported, namely Virtual Generated Column and Stored Generated Column, the former only saves the Generated Column in the data dictionary (metadata of the table), and does not persist this column of data to the disk; the latter will persist the Generated Column to the disk instead of reading it every time. It is calculated when taking it. Obviously, the latter stores data that can be calculated from existing data, requires more disk space, and has no advantage compared to actually storing a column of data. Therefore, in MySQL 5.7, the type of Generated Column is not specified and the default is It is a Virtual Column.

INNODB_CMP, INNODB_CMP_RESET: Stores relevant information about compressing the INNODB information table. Please see the recommended notes for details.

Why are these tables listed as management-related tables? Because I think they are like connections, partitions, compression tables, innodb buffer pool and other tables. We can clearly see the correlation of our database through these tables. Function status, especially through some variables, it is easier for us to see the running status of MySQL, which facilitates our management. Relevant notes include recommended innodb buffer pool explanation, MySQL partition management, information_schema series 11. These are all my own notes.

6: Some tables about table information and index information

TABLES, TABLESPACES, INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES:

TABLES There is no doubt that this table is the recorded table information in the database, including system databases and user-created databases. The source of show table status like 'test1'\G is this table;

TABLESPACES is the marked active table space. This table does not provide information about the table space of innodb, and it is not of much use to us, because our production library is forced to use INNODB;

INNODB_SYS_TABLES This table relies on being pulled from the SYS_TABLES data dictionary. This table provides the format and storage characteristics of the table, including row format, compressed page size bit-level information (if applicable)

provides table space information about INNODB. In fact, It is consistent with the INNODB information in SYS_TABLESPACES.

STATISTICS: This table provides index information about the table and related information about all indexes.

INNODB_SYS_INDEXES: Provides information related to the index of the INNODB table. The information stored in the SYS_INDEXES table is basically the same, except that the latter provides index information for all storage engines. The latter only provides index information for the INNODB table.

INNODB_SYS_TABLESTATS:

This table is more important. It records the INNODB table information of MySQL and the MySQL optimizer will estimate the appropriate index information for SQL selection. In fact, It is the statistical information of MySQL database

The records of this table are recorded in the memory. It is a memory table. It will be recorded again after each restart, so it can only record the data from the last time. Database statistics after restart. With this table, it is more convenient for us to maintain the index. We can query the number of times the index is used, facilitate cleaning deleteinfrequently used indexes, and improve the efficiency of updateinsertion of the table. , save disk space.

INNODB_SYS_FIELDS: This table records the table index field information of INNODB and the ranking of the fields

INNODB_FT_CONFIG: This table stores the Full-text index information

INNODB_FT_DEFAULT_STOPWORD: This table stores stopword information and is used to match the full-text index. , which is the same as innodb's INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD. This STOPWORD must be created before creating the index, and the field must be specified as varchar. stopword is what we call stop words. During full-text search, the stop word list will be read and retrieved. Under different character sets and sorting methods, the hit will fail or the data cannot be found, depending on the stop word. Different ways of ordering words. We can use this feature to filter out unnecessary fields.

INNODB_FT_INDEX_TABLE: This table stores index usage information about the INNODB table with full-text index. Similarly, this table can only be used after setting innodb_ft_aux_table. Generally, it is empty.

INNODB_FT_INDEX_CACHE: This table stores the record information before insertion, and is also to avoid expensive index reorganization during DML

7: Some tables related to MySQL optimization

OPTIMIZER_TRACE: Provides information generated by the optimization tracking function. Regarding this, I also did a small test, MySQL Tracking Optimizer Trial

PROFILING: SHOW PROFILE can provide an in-depth view of the working status of server execution statements. And it can also help you understand the time it takes to execute statements. Some limitations are the functionality it does not implement, the inability to view and parse statements from other connections, and the cost incurred during parsing.

SHOW PROFILES displays multiple statements recently sent to the server. The number of statements is defined according to the session variable profiling_history_size. The default is 15 and the maximum value is 100. Setting it to 0 is equivalent to turning off the analysis function. For detailed information, please see MySQL profile

INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED This table is a snapshot of INNODB_FT_DELETED, only in OPTIMIZE TABLE will only be used when . For detailed information, please see my OPTIMIZE TABLE explanation

8: Some tables related to MySQL transactions and locks

INNODB_LOCKS: Get now locks, but does not include unacquired locks, and is only for INNODB.

INNODB_LOCK_WAITS: System lock wait related information, including records of one or more blocked rows, as well as lock information on lock requests and blocked change requests.

INNODB_TRX: Contains information related to all executing transactions (INNODB), and includes whether the transaction is blocked or requires a lock.

We can easily query unfinished things and blocked processes through these tables. Is this more convenient? For details, see information_schema series eight (things, locks)

The above is the detailed content of Detailed introduction to information_schema in MySQL. For more information, please follow other related articles on the PHP Chinese website!