MySQL information_schema and informationschema

MySQL information_schema and informationschema
The information_schema database is a MySQL database that provides access to database metadata. It seems that information_schema is like an encyclopedia of a MySQL instance, which records most of the information we need to settle in the database, such as character sets, permission-related, database object information, external check constraints, partitions, compress tables, table information, index information, parameters, optimization, locks, and transactions. Through information_schema, we can get a glimpse of the running status of the entire MySQL instance, get the basic information of the MySQL instance, and even optimize, optimize, and maintain the database. It can be said that it is a real encyclopedia. Haha. The following is a general classification of these tables based on some of my learning experience. If there are any deficiencies, please note that I will modify them as soon as possible.1: system tables related to character sets and sorting rulesCHARACTER_SETS: stores database-related character set information (memory storage engine) COLLATIONS: Sorting rule COLLATION_CHARACTER_SET_APPLICABILITY: it is a correspondence between character sets and line-based verification. Let's talk about the differences between character sets and collations: character sets store strings, which are the smallest semantic symbols in human languages. For example, 'A', 'B', and so on. Sort rules compare strings. collations refer to the comparison rules between characters in the same character set. Each character order uniquely corresponds to one character set, however, a character set can correspond to multiple Collation characters, one of which is the Default Collation MySQL Character Sequence name. Follow the naming convention: It starts with the character set name corresponding to the Collation; end with _ ci (Case Insensitive), _ cs (case sensitive), or _ bin (compare by encoding value. For example, in the collation "utf8_general_ci", the characters "a" and "A" are equivalent. Let's take a look at the MySQL variable about character set and verification: character_set_server: default internal character set character_set_client: character set used by the client source data character_set_connection: Connection layer Character Set character: Query Result Character Set character_set_database: the default Character Set character_set_system of the selected Database (Field name, etc) character Set: (1 ). when receiving the request, MySQL Server converts the request data from character_set_client to character_set_connection; (2 ). before performing internal operations, convert the request data from character_set_connection to internal operations. Character set. The method for determining the character set is as follows: Use the character set value of each data field. If the value does not exist, use the default character set Value of the corresponding data table (MySQL extension, non-SQL standard). If the preceding value does not exist, use the DEFAULT CHARACTER SET Value of the corresponding database. If the preceding value does not exist, use character_set_server to SET the value. (3). Convert the operation result from the internal character set to character_set_results.2: permission-related tables:SCHEMA_PRIVILEGES: Provides database-related permissions. The memory table is pulled from mysql. db. TABLE_PRIVILEGES: Provides information about table permissions from mysql. COLUMN_PRIVILEGES loaded in the tables_priv table: This table clearly shows the authorized user objects, the database of the table, and the permissions granted, if with grant option is added during authorization, we can see that the value of PRIVILEGE_TYPE must be YES. USER_PRIVILEGES: Provides information about table permissions from mysql. the user TABLE is loaded through the TABLE. We can clearly see the MySQL authorization level, SCHEMA, TABLE, and COLUMN level. Of course, these are granted based on the user. It can be seen that MySQL authorization is quite fine, and can be specific to columns, which is useful in some application scenarios, such as auditing.3: some tables that store the object of the database system:COLUMNS: Stores Table field information. All storage engines INNODB_SYS_COLUMNS store INNODB metadata, which depends on the SYS_COLUMNS statistical table. ENGINES: engine type, whether to support this engine, description, whether to support transactions, whether to support distributed transactions, whether to support transaction rollback point EVENTS: records EVENTS in MySQL, similar to scheduled job FILES: This table provides information about the FILES stored in the MySQL tablespace, where the FILES are stored, the data in this table is pulled from InnoDB in-memory. Therefore, this table itself is also a memory table and is pulled again after each restart. That is, the INNODB_SYS_DATAFILES table. Note that this table package contains information about the temporary table. Therefore, it cannot be equivalent to the SYS_DATAFILES table. It should be viewed from INNODB_SYS_DATAFILES. If the undo tablespace is configured as InnoDB, it will also be recorded. PARAMETERS: the parameter table stores the PARAMETERS of some stored procedures and methods, as well as the returned values of stored procedures. The storage and methods are stored in ROUTINES. PLUGINS: It is basically the MySQL plug-in information, whether it is activity status or other information. In fact, show plugins itself uses this table to pull moral data ROUTINES: some information about stored procedures and method functions, but this information does not include user-defined information, only some information about the system. SCHEMATA: This table provides the number of databases under the instance, and the default character set TRIGGERS of the database: This table records the trigger information, including all relevant information. System and user-created triggers. VIEWS: View information is the basic view information of the system and the user. These tables store entity objects in some databases, so that we can query and manage them conveniently. For a DBA, these tables can greatly facilitate our work, it is faster and more convenient to settle and query database information.4. Tables related to foreign keys:REFERENTIAL_CONSTRAINTS: This table provides information about foreign keys, and only provides information about foreign keys TABLE_CONSTRAINTS: This table provides the related constraint information INNODB_SYS_FOREIGN_COLS: this table also stores INNODB metadata about foreign keys. The information stored in SYS_FOREIGN_COLS is consistent with INNODB_SYS_FOREIGN: the stored INNODB metadata about foreign keys is consistent with the information stored in SYS_FOREIGN_COLS, KEY_COLUMN_USAGE for INNODB alone: all the columns with constraints in the database are saved, and the names and categories of constraints are recorded. Why should we list Foreign keys and constraints, it seems that this is an independent thing. Although most of our production environments do not use foreign keys, this will reduce performance, it is still a good choice to make rational use of constraints, such as the unique constraint.5: Some managed tables:GLOBAL_STATUS, GLOBAL_VARIABLES, SESSION_STATUS, and SESSION_VARIABLES: these four tables respectively record system variables and States (global and session information). As a DBA, I believe everyone is familiar with them, these tables are also reloaded when the system restarts. That is, the memory table. PARTITIONS: MySQL Partition Table-related information. Through this table, we can query partition-related information (partitioned tables in the database, PARTITIONS in the Partition Table, and data information of each partition ), for details about partitions, see MySQL partition management PROCESSLIST: show processlist is actually pulling data from this table, and PROCESSLIST data is its basis. Because it is a memory table, we perform the same operations as in-memory queries. These operations are fast. INNODB_CMP_PER_INDEX, INNODB_CMP_PER_INDEX_RESET: the two tables store information about compressing the INNODB information table. The information about the entire table and index is available. we know that for an INNODB compressed table, both data and secondary indexes will be compressed, because the data itself can also be seen as a clustered index. This section briefly introduces the compressed table in the ion_schema series 11. INNODB_CMPMEM, INNODB_CMPMEM_RESET: These two tables store the buffer pool information about the compression page of MySQL INNODB. Note that when using these two tables to collect all information, it will have a serious impact on performance, so it is disabled by default. To enable this function, set innodb_cmp_per_index_enabled 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 show engine innodb status information. INNODB_BUFFER_PAGE_LRU, INNODB_BUFFER_PAGE: maintains innodb lru list information. For more information, see innodb buffer pool. INNODB_BUFFER_PAGE: This table is awkward. It stores the buffer pages. Querying this table will have a serious impact on performance. Do not execute this statement on our production database unless you can accept the service for a short pause, for details, see the notes in the innodb buffer pool. INNODB_SYS_DATAFILES: This table records the file storage location of the table and a ing relationship between the tablespace (INNODB) INNODB_TEMP_TABLE_INFO: this table records the information used by all INNODB users, but can only be recorded in memory and without persistent information. INNODB_METRICS: provides various performance indexes of INNODB, supplements INFORMATION_SCHEMA, and collects MySQL system statistics. You can manually enable or disable these statistics. The following parameters are controllable: innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, and innodb_monitor_reset_all. INNODB_SYS_VIRTUAL: The table stores information about the virtual columns in the INNODB table. Of course, this is relatively simple. In MySQL 5.7, two types of Generated columns are supported, virtual Generated Column and Stored Generated Column. The former only saves the Generated Column in the data dictionary (Table metadata) and does not persistently store the Column data on the disk; the latter will persist the Generated Column to the disk, instead of calculating the result each time it is read. Obviously, the latter stores data that can be computed using existing data and requires more disk space, which has no advantage over storing a column of data. Therefore, mySQL 5.7 does not specify the Generated Column type. The default value is Virtual Column. INNODB_CMP, INNODB_CMP_RESET: stores information about compressing the INNODB information table. For more information, see the recommendation notes. Why are these tables listed as management-related tables? I feel like joining, partitioning, compressing tables, innodb buffer pool, and other tables, through these tables, we can clearly see the status of related functions of our database, especially through some variables, it is easier for us to understand the running status of MySQL and facilitate our management. We recommend innodb buffer pool for notes, MySQL partition management, and ion_schema series 11. They all make up their own notes.6: tables with table information and index informationTABLES, TABLESPACES, INNODB_SYS_TABLES, and INNODB_SYS_TABLESPACES: The TABLES Table is undoubtedly the information of the table in the recorded database, including the system database and the database created by the user. The source of show table status like 'test1' \ G is this table; TABLESPACES is the labeled active tablespace. This table does not provide table space information about innodb, which is not very useful for us because our production database forces INNODB; the INNODB_SYS_TABLES table depends on the SYS_TABLES data dictionary. This table provides table format and storage features, including row format and page size bit-level compression information (if applicable) about the INNODB tablespace, in fact, it is consistent with the INNODB information in SYS_TABLESPACES. STATISTICS: This table provides index information about the table and information about all indexes. INNODB_SYS_INDEXES: Provides information about indexes of INNODB tables. The information stored in the table SYS_INDEXES is basically the same, but the latter provides the index information of all storage engines, the latter only provides index information for INNODB tables. INNODB_SYS_TABLESTATS: This table is important. It records the INNODB table information of MySQL and the number of times used for index search, in fact, it is the statistical information of the MySQL database. This table is recorded in the memory and is a memory table. It will be rerecorded every time it is restarted, therefore, you can only record the database statistics after the last restart. With this table, we have made it easier to maintain indexes. We can query the number of times indexes are used to conveniently Delete infrequently used indexes and improve the efficiency of updating and inserting tables, saves disk space. INNODB_SYS_FIELDS: This table records INNODB table index field information and field ranking INNODB_FT_CONFIG: This table stores full-text index information INNODB_FT_DEFAULT_STOPWORD: This table stores stopword information, it is used in concert with full-text indexes. It is the same as the INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD of innodb. This STOPWORD must be created before the index is created and the field must be specified as varchar. Stopword is what we call the Stop Word. During full-text retrieval, the Stop Word List will be read and retrieved. In different character sets and sorting methods, this will cause a hit failure or the data cannot be found, depending on the different sorting methods of the Stop words. We can use this function to filter unnecessary fields. INNODB_FT_INDEX_TABLE: This table stores full-text index usage information about the INNODB table. Similarly, this table can be used only after innodb_ft_aux_table is set. Generally, it is an empty INNODB_FT_INDEX_CACHE: this table stores the pre-inserted record information to avoid expensive index restructuring during DML.7. Tables related to MySQL OptimizationOPTIMIZER_TRACE: Provides information generated by the optimization tracking function. I have also made a small test on this. The MySQL tracking optimizer has tested PROFILING: SHOW PROFILE to view the statement execution status on the server. It also helps you understand the time consumed by executing statements. Some restrictions are that it does not implement the function, and it cannot view and analyze other connected statements, as well as the consumption caused by analysis. 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 value is 15 and the maximum value is 100. Setting 0 is equivalent to disabling the analysis function. For more information, see MySQL profileINNODB_FT_BEING_DELETED, INNODB_FT_DELETED: INNODB_FT_BEING_DELETED. This TABLE is a snapshot of INNODB_FT_DELETED, which is used only when optimize table is used. For more information, see my optimize table solution.8: tables related to MySQL transactions and locksINNODB_LOCKS: The current lock, but it does not include any unobtained lock, and it is only for INNODB. INNODB_LOCK_WAITS: system lock wait information, including the blocked records of one or more rows, as well as the lock information of the lock request and the blocked modification request. INNODB_TRX: contains all information about the executed transactions (INNODB), and contains whether the transactions are blocked or request locks. Through these tables, we can easily query uncompleted transactions and blocked processes. This is more convenient. For details, see information_schema series 8 (transactions, locks) finally, if you are interested, please read my ion_schema series and give more comments. If you are encouraged, click the recommendation button in the lower right corner.