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: The collation of the character set collation_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 ( The word Fu She name in the Default Collation) mysql follows the naming convention: start with the character Fu She corresponding to the charset name, _ci (for case insensitive), _cs (for case sensitivity), 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). To move the result of the operation from the internal operation character setChange 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 table permission-related information, which is loaded from the Mysql.tables_priv table column_privileges: The table can clearly see the object of the user authorized by the table, the table that library and what permissions are granted , we can see that the value of Privilege_type must be yes if you add with GRANT option when authorizing. User_privileges: Provides table permission-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: Store the table field information, all the storage engine Innodb_sys_columns: InnoDB metadata is stored, he is dependent on the Sys_columns this statistical table exists. 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 things events: Log events in MySQL, similar to timed jobs files : 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: The parameter table stores the parameters for stored procedures and methods, and the return value information for the stored procedure. Storage and methods are stored inside the routines. PLUGINS: Basically is the MySQL plugin information, whether is the activity status and so on information. In fact, show plugins itself is through this table to pull moral data routines: Some information about the stored procedure and method function, but this information does not include user-defined, just some information of the system. Schemata: This table provides the number of databases under the instance, and also the default character set of the database triggers: This table records information about 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. 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 provides only foreign key-related information table_constraints: This table provides related constraint information innodb_sys_foreign_cols : This table is also stored InnoDB metadata information about foreign keys and sys_foreign_cols stored information is consistent innodb_sys_foreign: stored InnoDB metadata information about foreign keys and sys_foreign_cols The information stored is consistent, but only for InnoDB key_column_usage: All the constrained columns in the database will be saved, and the names and categories of the constraints will be recorded why the foreign keys and constraints are listed separately, because it feels like a separate thing, While most of our production environments do not use foreign keys because they degrade performance, it is a good choice to make reasonable use of constraints, such as unique constraints.
5: Some of the tables on management:Global_status, Global_variables,session_status,session_variables: These four tables record system variables, states (Global and session information), and as DBAs believe that 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 partitions and data information for each partition), Partition-related details see MySQL partition management processlist: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 that the INNODB information table is compressed, with respect to the entire table and index information. We know that for a INNODB compression table, , both data and level two indexes are compressed, because the data itself can also be considered 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 buffer pool information that holds compressed pages about MySQL INNODB, but one thing to note is that when you use these two tables to gather all the information in the table, 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&NBSP: 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&NBSP: Maintenance of INNODB LRU list information, please see the Small series of notes INNODB BUFFER pool Pee Innodb_buffer_ PAGE&NBSP: This table is a dick, and the cached page data in buffer is stored. Querying this table can have a serious effect on performance.Influence, do not again our own production library above the execution of this statement, unless you can accept a short pause in service, please see the Small series of notes InnoDB buffer pool Pee innodb_sys_datafiles : This table is the file storage location of the recorded table and a correspondence relationship (INNODB) of the table space innodb_temp_table_info : This table benefits records all the information that all users of the INNODB use, However, only information that is in memory and not persisted can be recorded. INNODB_METRICS&NBSP: Providing a variety of INNODB performance indices is a supplement to Information_schema, which collects MySQL's 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&NBSP: The table stores information about the virtual columns of the INNODB table, which is relatively simple, and in MySQL 5.7, supports two Generated column, virtual Generated column and stored Generated column, which only stores Generated column in the data dictionary (metadata for the table), does not persist this column of data to disk, which persists the Generated column to disk. Instead of being computed every 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 the compressed 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 information Tables,tablespaces,innodb_sys_tables, Innodb_sys_tablespaces:tables This watch is no doubt, it is the information of the tables in the database that is recorded, 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 table's index information, 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 it 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 MySQL optimizer will estimate the SQL to choose the appropriate index information, in fact, the MySQL database statistics This table record is recorded in memory, is a memory table that is re-recorded after each reboot, so only the database statistics from the last restart are logged. 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&NBSP: This table records the INNODB table index field information, and the field's rank innodb_ft_config : This table has information about the full-text index Innodb_ft_default_ STOPWORD: This table holds stopword information, which is used to match the full-text index, and InnoDB's information_schema. innodb_ft_default_stopword is 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&NBSP: This table holds the pre-insertion record information and is also intended to avoid the expensive indexing reorganization of 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 done, and the MySQL tracker optimizer Profiling: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, It is only 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.
(turn) 10 minutes to finish MySQL Information_schema
