The INFORMATION_SCHEMA database is the MySQL system's own database, which provides access to the database metadata. Feeling information_schema is like an encyclopedia of MySQL instances, which records most of the information we need to close in the database, such as character sets, permission correlation, database entity object information, external check constraints, partitioning, compression tables, table information, index information, parameters, optimization, Locks and things and so on. Through the information_schema we can peek through the entire MySQL instance of the operation, can end the basic information of MySQL instance, even optimize tuning, maintenance database, etc., can be said to be a real encyclopedia ah haha. Here are some of the small experience of their own learning to carry out a general classification of these tables, convenient for everyone to close, if there is insufficient place please point out, I will revise as soon as possible.
1: System tables related to character set and collationcharacter_sets: Storing database-related character set information (memory storage engine)collations: Collation for character setcollation_character_set_applicability: It's a correspondence between a character set and a line proofreading.Let's talk about the difference between character sets and collations: the character set (character sets) stores the string, which refers to the smallest of the semantic symbols in the human language. such as ' A ', ' B ', and so on; collation (collations) Rules compare strings, collations refers to the comparison rules 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 ( Default Collation) The word Fu She name in MySQL follows the naming convention: start with the character Fu She corresponding to the charset name, _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 to look at the MySQL variables related to character set and proofreading: Character_set_server: Default internal operation character Set character_set_ Client: The character set character_set_connection: Connection layer Character Set character_set_results: Query result character Set character_set_ Database: The default character set for the currently selected Character_set_system: System metadata (field name, etc.) character set 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). The request data is converted from character_set_connection to the internal operation character set before the internal operation, and is determined as follows: Use the character set setpoint for each data field, or use the default of the corresponding data table if the above value does not exist CHARACTER set setpoint (MySQL extension, non-SQL standard), if the above value does not exist, the default CHARACTER set value of the corresponding database is used, and if the above value does not exist, the Character_set_server setting value is used. (3). Converts the operation result from the internal operation character set to Character_set_results.
2: Some tables related to permissions:schema_privileges: Provides permissions for the database, which is the memory table pulled out of the mysql.db.table_privileges: Provides information about table permissions, which is loaded from the Mysql.tables_priv table.column_privileges: This table can clearly see the object of the user authorized by the table, the Library of that table and what permissions are granted, and if the authorization is given with GRANT option, we can see that the value of Privilege_type must be yes.user_privileges: Provides table permissions related information, information is loaded from the Mysql.user table through the table we can clearly see the level of MySQL authorization, schema,table,column level, of course, these are based on the user to grant. It can be seen that the authorization of MySQL is also quite fine, can be specific to the column, which is useful in some application scenarios, such as auditing.
3: Some tables that store the entity objects of the database system:COLUMNS: Stores the table's field information, all the storage enginesInnodb_sys_columns: The metadata of InnoDB is stored, and he relies on the statistical table of Sys_columns to exist.ENGINES: Engine type, whether it supports this engine, describes whether it supports things, whether it supports distributed transactions, whether it can support the rollback point of thingsEVENTS: Logs events in MySQL, similar to timed jobsFILES: This table provides information about the files stored in the table space in MySQL, the location of the file storage, and the data for this table is pulled from the InnoDB in-memory, so the table itself is also a memory table, and each reboot restarts the pull. That's what we're going to say innodb_sys_datafiles this table. It is also important to note that this table contains the information of the temporary table, so that the table and Sys_datafiles is not able to peer, or to see from the Innodb_sys_datafiles. If the Undo table space is also configured as InnoDB, it will be recorded as well.PARAMETERS: parameter tables store parameters for stored procedures and methods, and return value information for stored procedures. Storage and methods are stored inside the routines.PLUGINS: basically MySQL plug-in information, whether it is active status and other information. Actually, show plugins itself is using this watch to pull ethical data.ROUTINES: Some information about stored procedures and method function, but this information does not include user-defined, just some information about the system.schemata: This table provides the number of databases under the instance, and also the default character set for the databaseTRIGGERS: This table records the information of the trigger, including all relevant information. The system's and its own user-created triggers. views: View information, which is also the basic view information of the system and the user. These tables are stored in some database entity objects, so that we can query and manage, for a DBA, these tables will greatly facilitate our work, faster and more convenient to close and query the database related information.
4: Some tables related to constraint foreign keys, etc.:referential_constraints: This table provides foreign key-related information, and only provides information about foreign keystable_constraints: This table provides the relevant constraint informationInnodb_sys_foreign_cols: This table is also stored InnoDB metadata information about foreign keys is consistent with sys_foreign_cols stored informationinnodb_sys_foreign: Stored InnoDB metadata information about foreign keys is consistent with sys_foreign_cols stored information, except for InnoDB aloneKey_column_usage: All constrained columns in the database will be saved, and the names and categories of the constraints will be recorded. Why do you want to list foreign keys and constraints separately, because it feels like a separate thing, although most of our production environments don't use foreign keys because they degrade performance, but reasonable use of constraints is a good choice, such as the unique constraint.
5: Some of the tables on management:Global_status, Global_variables,session_status,session_variables: These four tables record the system variables, status (Global and session information), as the DBA believes everyone is familiar, and these tables are also reloaded when the system restarts. This is the memory table.Partitions: MySQL partition table related information, through this table we can query the information about the partition (partitioned tables in the database, and partition table partition and data information of each partition), partition related details see MySQL partition managementprocesslist: Show Processlist is actually pulling data from this table, Processlist's data is his foundation. Because it is a memory table, we are equivalent to querying in memory, and these operations are fast.Innodb_cmp_per_index，Innodb_cmp_per_index_reset: These two tables store information about the time when the InnoDB information table is compressed, with information about the entire table and index. We know that for a innodb compression table, both the data and the level two index are compressed, because the data itself can also be thought of as a clustered index. About the compression table in the INFORMATION_SCHEMA series 11 has a little simple introduction.Innodb_cmpmem, Innodb_cmpmem_reset:These two tables are the buffer pool information that holds the compressed pages about MySQL InnoDB, but one thing to note is that when you use these tables to collect all of the information, it can have a serious impact on performance, so the default is the off state. If you want to turn on this feature, we want to set the innodb_cmp_per_index_enabled parameter to on state.Innodb_buffer_pool_stats: Table provides information about the InnoDB buffer pool, and the information provided by show engine InnoDB status is the same. It is also the source of information for show engine InnoDB status.Innodb_buffer_page_lru,innodb_buffer_page: Maintenance of the InnoDB LRU list information, please see the small notes InnoDB buffer pool peeInnodb_buffer_page: This table is a dick, and the cached page data is stored in buffer. Querying this table will have a serious impact on performance, and do not execute this statement on our own production library, unless you can accept a short pause in the service, please see the small innodb buffer pool peeInnodb_sys_datafiles: This table is the file storage location of the recorded table and a correspondence between the table spaces (INNODB)Innodb_temp_table_info: This table Megumi records all the information that all users of the InnoDB use, but only records 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: The table stores information about the virtual columns of the InnoDB table, which of course is relatively simple, in MySQL 5.7, supports two Generated column, virtual Generated column and stored Generated column, The former only saves the generated column in the data dictionary (the table's metadata) and does not persist this column of data to disk, which persists the generated column to disk instead of being computed each time it is read. Obviously, the latter stores data that can be computed from existing data, requires more disk space, and does not have an advantage over the actual storage of a column of data, so MySQL 5.7 does not specify the type of generated column, which by default is virtual column.Innodb_cmp,innodb_cmp_reset: Stores information about compressing the INNODB information table, see recommended notes for details. Why are these tables listed as management related tables, because I feel like connections, partitions, compressed tables, InnoDB buffer pool tables, we can clearly see the status of their database related functions, especially we through some variables more easily peep through the MySQL running state, It's easy for us to manage. Related notes are recommended InnoDB buffer pool pee, MySQL partition management, INFORMATION_SCHEMA series 11. It's all a small series of notes.
6: Some tables about table information and index informationtables,tablespaces,innodb_sys_tables, Innodb_sys_tablespaces:Tables This table is undoubtedly the information of the tables in the database, including the system database and the user-created database. The source of the show table status like ' Test1 ' \g is the table, and tablespaces is the active tablespace of the callout. This table does not provide table space information about INNODB, which is not very useful for us because our production library is mandatory INNODB; innodb_sys_tables This table relies on the sys_tables data dictionary to pull out. This table provides information about the format and storage characteristics of the table, including the row format, and compressing the page size bit level (if applicable) provides the tablespace information about the InnoDB, which is in fact consistent with the InnoDB information in the sys_tablespaces.STATISTICS: This table provides information about the index information for the table, all indexes.innodb_sys_indexes: Provides information about the index of the related InnoDB table, and the information stored in the Sys_indexes table is basically the same, except that the latter provides index information for all storage engines, which provides only the index information of the InnoDB table.Innodb_sys_tablestats: This table is more important, the record is the MySQL InnoDB table information and the number of index lookups used, in fact, the statistics of the MySQL database is recorded in memory, is a memory table, each reboot will be re-recorded, Therefore, you can only log statistics from the database since the last reboot. With this table, we are more convenient for the maintenance of the index, we can query the number of index usage, easy to clean and delete the infrequently used indexes, improve the efficiency of the table update Insert, save disk space.Innodb_sys_fields: This table records the table index field information for InnoDB, and the rank of the fieldInnodb_ft_config: This table is full-text indexed informationInnodb_ft_default_stopword: This table holds Stopword information, which is used to match the full-text index, and InnoDB'sInformation_schema.Innodb_ft_default_stopwordIs the same, 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, when the full-text search, the stop word list will be read and retrieved, in different character sets and sorting mode, will cause a hit failure or cannot find this data, depending on the stop word of different sort. We can use this feature to filter out unnecessary fields.innodb_ft_index_table: This table stores information about the index usage of the InnoDB table with full-text indexing, and the same table is to be used after the innodb_ft_aux_table is set, usually empty.Innodb_ft_index_cache: This table holds the pre-insertion record information and is also designed to avoid expensive index reassembly when DML
7: Some tables related to MySQL optimizationOptimizer_trace: Provides information that is generated by the optimization tracking feature. I'm grateful for that. A small test was made, and the MySQL tracker optimizer triedPROFILING:Show profile can drill down to see how the server executes the statement. And it can also help you understand how time is spent executing statements. Some limitations are the functionality that it does not implement, the inability to view and parse other connected statements, and the consumption caused by profiling. Show Profiles displays several statements that were recently sent to the server, the number of which is defined according to the session variable Profiling_history_size, the default is 15, and the maximum value is 100. Set to 0 is equivalent to turning off 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 and will only be used when optimize table. For more information, see my optimize TABLE pee
8: Some tables related to MySQL things and locksInnodb_locks: The lock is now acquired, but does not contain a lock that is not acquired and is only for InnoDB.innodb_lock_waits: The system lock waits for relevant information, contains a block of one or more rows of records, and there are lock requests and blocked changes to the request lock information.Innodb_trx: Contains information about all the things that are executing (INNODB), and it contains whether things are blocked or request locks. Through these tables we can easily query out the unfinished things and blocked processes, which is not more convenient, detailed visible Information_schema series eight (things, locks) Finally, if you are interested, please see my essay Information_ Schema series, a lot of suggestions. If the feeling is worth encouraging, please click on the lower right corner to recommend
10 minutes to finish MySQL Information_schema
