Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand and master information_schema in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

| | what is information_schema? |

Information_schema provides access to database metadata, statistics, and information about MySQL Server (for example, database or table names, data types and access permissions for fields, and so on). The information stored in this library can also be referred to as the data dictionary or system directory of MySQL.

There is a separate information_schema in each MySQL instance to store the basic information of all other databases in the MySQL instance. The information_schema database contains multiple read-only tables (non-persistent tables), so there are no corresponding associated files under the data directory on disk, and triggers cannot be set on these tables. Although you can use the user statement to set the default database to information_schema when querying, all tables under the library are read-only and cannot perform data change operations such as INSERT, UPDATE, DELETE, and so on.

Query operations for tables under information_schema can replace some show query statements (such as SHOW DATABASES,SHOW TABLES, etc.). Compared with using show statements, querying tables under information_schema has the following advantages:

It complies with the Codd Law, and all access is based on table access.

You can use the SQL syntax of the SELECT statement, just learn the meaning of some table and column names you want to query

The query based on SQL statement can filter, sort and join the query results from information_schema, and the format of the query result set is more friendly to the application.

This technical implementation is more interoperable with similar implementations in other database systems. For example, users of Oracle database are familiar with querying tables in Oracle data dictionary, so tables that query data dictionary in MySQL can also use the same method to execute queries to get the desired data.

Permissions required to access information_schema

All users have access permissions under information_schema (but they can only see the data rows corresponding to the objects in these tables that users have access rights to), but they can only access part of the data dictionary table in Server layer, part of the data dictionary table in Server layer and the data dictionary table in InnoDB layer. If the user permission is insufficient, no data will be returned when querying the Server layer data dictionary table. Or a column returns a null value when it does not have permission to access it. Access is denied directly when querying InnoDB data dictionary tables (process permission is required to access these tables, note that it is not select permission)

The permissions required to query related data from information_schema also apply to SHOW statements. No matter which query method is used, you must have the permission of an object to see the relevant data.

PS:

There are a total of 59 tables in MySQL version 5.6, including 10 MyISAM engine temporary tables (data dictionary tables) and 49 Memory engine temporary tables (holding statistics and some temporary information). In MySQL version 5.7, there are a total of 61 tables under the schema, including 10 InnoDB storage engine temporary tables (data dictionary tables) and 51 Memory engine temporary tables. In MySQL 8.0, the data dictionary tables under the schema (including some of the original memory engine temporary tables) are migrated to mysql schema, and these data dictionary tables are hidden under mysql schema and cannot be accessed directly, so they need to be accessed through the tables of the same name under information_schema (the statistics table is retained under information_schema and is still the Memory engine).

Although there are many advantages to getting data directly by querying the tables in information_schema, because the SHOW syntax is familiar and widely used, the SHOW statement is still an alternative, and with the implementation of information_schema, the functionality in the SHOW statement has been enhanced (you can use the like or where clause to filter), such as:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

# Syntax

Syntax:

SHOW [GLOBAL | SESSION] VARIABLES

[LIKE 'pattern' | WHERE expr]

# example 1

Root@localhost: information_schema 12:20:31 > show variables like'% log_bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / home/mysql/data/mysqldata1/binlog/mysql-bin |

| | log_bin_index | / home/mysql/data/mysqldata1/binlog/mysql-bin.index |

| | log_bin_trust_function_creators | ON |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

# instance 2

Root@localhost: information_schema 12:21:41 > show variables where Variable_name like 'log_bin%' and Value='ON'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_bin | ON |

| | log_bin_trust_function_creators | ON |

+-+ +

2 rows in set (0.00 sec)

# Note that like and where clauses can be used separately, but to use both where and like clauses, the like clause must come after where

| | information_schema is composed of objects |

All the tables under information_schema are Memory and InnoDB storage engines, and they are temporary tables, not persistent tables. These data will be lost after the database restart. Among the four system libraries of MySQL, it is also the only system library that does not correspond to the directories and files of library tables on the file system.

According to the similarity of the respective uses of these tables, we classify the tables under information_schema as follows. In this issue, we first get a general idea of what tables are in the information_schema system library and what these tables are generally used for.

1. Server layer statistics dictionary table

COLUMNS:

Provide column (field) information in the query table

This table is a temporary table for the InnoDB storage engine

KEY_COLUMN_USAGE:

Provides a query for which index columns have constraints

The information in this table contains information about constraints such as primary key, unique index, foreign key, etc., such as the name of the library table column, the referenced database table column name, and so on. The information in the table is similar to that recorded in the TABLE_ connections table, but the library table column information referenced by the constraint is not recorded in the TABLE_CONSTRAINTS table. But it records the constraint type information that is not in the TABLE_ CONSTRAINTS table

This table is the temporary table of Memory engine

REFERENTIAL_CONSTRAINTS:

Provides some information about querying foreign key constraints

This table is the temporary table of Memory engine

STATISTICS:

Provides some statistical information about the query about the index, with an index corresponding to a row of records

This table is the temporary table of Memory engine

TABLE_CONSTRAINTS:

Provide constraint information related to the query table

This table is the temporary table of Memory engine

FILES:

Provide information about querying MySQL data tablespace files, including data file information related to InnoDB storage engine and NDB storage engine. Because NDB storage engine is rarely used in China, we use InnoDB storage engine in most scenarios (more than 95% of scenarios InnoDB storage engine can be used)

This table is the Memory storage engine table.

ENGINES:

Provides information about querying engines supported by MySQL Server

This table is the temporary table of Memory engine

TABLESPACES:

Provides query information about active tablespaces (mainly records NDB storage engine tablespace information)

Note: this table does not provide information about the table spaces of the InnoDB storage engine. For InnoDB tablespace metadata information, query the INNODB_SYS_TABLESPACES and INNODB_SYS_ tables. In addition, since MySQL 5.7.8, the INFORMATION_ schema. Files table also provides metadata information for querying InnoDB tablespaces.

This table is the Memory engine temporary table.

SCHEMATA:

Provides database list information in query MySQL Server. A schema represents a database.

This table is the temporary table of Memory engine

2. Server layer table level object dictionary table

VIEWS:

Provide information related to querying views in the database. Show view permission is required to query the account of the table.

This table is the temporary table of InnoDB engine

TRIGGERS:

Provides information about querying triggers under a database. To query triggers for a table, the queried account must have trigger permission.

This table is the temporary table of InnoDB engine

TABLES:

Provide basic information about querying tables in the database

This table is the temporary table of Memory engine

ROUTINES:

Provides query information about stored procedures and stored functions (excluding the user-defined function UDF). The information in this table corresponds to the information recorded in "mysql.proc" (if there is a value in the table).

This table is the temporary table of InnoDB engine

PARTITIONS:

Provides information about querying about partition tables

This table is the temporary table of InnoDB engine

EVENTS:

Provide information related to query plan task events

This table is the InnoDB engine temporary table

PARAMETERS:

Provides parameter information about stored procedures and functions, as well as information about the return values of stored functions. These parameter information are similar to those recorded in the param_list column in the mysql.proc table.

This table is the temporary table of InnoDB engine

3. Server layer hybrid information dictionary table

GLOBAL_STATUS 、 GLOBAL_VARIABLES 、 SESSION_STATUS 、 SESSION_VARIABLES:

Provides information about querying global, session-level state variables and system variables. These tables are Memory engine temporary tables

OPTIMIZER_TRACE:

Provides information generated by the optimizer tracking feature.

Tracing is turned off by default and is enabled using the optimizer_trace system variable. If this feature is enabled, each session can only track the statements executed by itself, can not see the statements executed by other sessions, and each session can only record the last tracked SQL statement

This table is the temporary table of InnoDB engine

PLUGINS:

Provides query information about which plug-ins are supported in MySQL Server

This table is the temporary table of InnoDB engine

PROCESSLIST:

Provides some information about the status of running threads.

This table is the temporary table of InnoDB engine

PROFILING:

Provides query information about statement performance analysis. The contents of the records correspond to the information generated by SHOW PROFILES and SHOW PROFILE statements. The table does not record statement performance analysis information until the session variable profiling=1 is used, otherwise it does not record.

Note: starting with MySQL 5.7.2, this table is no longer recommended and will be deleted in future MySQL releases. Replace it with Performance Schema;

This table is the temporary table of Memory engine

CHARACTER_SETS:

Provides querying which character sets are available for MySQL Server support

This table is the temporary table of Memory engine

COLLATIONS:

What are the available proofreading rules for querying MySQL Server support

This table is the temporary table of Memory engine

COLLATION_CHARACTER_SET_APPLICABILITY:

Provides a query about which character set in MySQL Server applies to which proofing rules. The query result set is equivalent to the first two field values in the result set obtained from SHOW COLLATION. At present, the table has not been found to be very useful for Memory engine temporary tables.

COLUMN_PRIVILEGES:

Provides permission information for querying columns (fields). The contents of the table are from the mysql.column_priv column permissions table (only after separate authorization is required for the columns of a table)

This table is the temporary table of Memory engine

SCHEMA_PRIVILEGES:

Provides query information about library-level permissions. Each type of library-level permissions records a row of information. The information in this table comes from the mysql.db table.

This table is the temporary table of Memory engine

TABLE_PRIVILEGES:

Provides query information about table-level permissions, which are from mysql.tables_priv

This table is the temporary table of Memory engine

USER_PRIVILEGES:

Provides information for querying global permissions, and the information in this table comes from the mysql.user table

This table is the temporary table of Memory engine

4. InnoDB layer system dictionary table

INNODB_SYS_DATAFILES:

Provides path information for querying InnoDB file-per-table and regular tablespace data files, which is equivalent to the information in the SYS_ tables in the InnoDB data dictionary

The information in this table contains metadata for all InnoDB tablespace types, including independent tablespaces, regular tablespaces, system tablespaces, temporary tablespaces, and undo tablespaces (if independent tablespaces are enabled)

The table is a temporary table of the memory engine, and the user who queries the table needs to have process permission.

INNODB_SYS_VIRTUAL:

Provides metadata information for querying InnoDB virtual generated columns and the columns associated with them, which is equivalent to the information in the SYS_ VIRTUAL table within the InnoDB data dictionary. The row information displayed in the INNODB_SYS_ VIRTUAL table is the information for each column that is virtually generated as an associated column.

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_INDEXES:

Provides metadata information about querying the InnoDB index, which is equivalent to the information in the SYS_ index table within the InnoDB data dictionary

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_TABLES:

Provides information for querying metadata about the InnoDB table, which is equivalent to the SYS_TABLES table within the InnoDB data dictionary.

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_FIELDS:

Provides metadata information about querying InnoDB index key columns (fields), which is equivalent to the information of SYS_FIELDS tables within the InnoDB data dictionary

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_TABLESPACES:

Provides metadata information for querying InnoDB independent tablespaces and regular tablespaces (including full-text indexed tablespaces), which is equivalent to the information in SYS_ tables in the InnoDB data dictionary

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_FOREIGN_COLS:

Provides information about querying the status of InnoDB foreign key columns, which is equivalent to the information of SYS_FOREIGN_COLS tables within the InnoDB data dictionary

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_COLUMNS:

Provides metadata information for querying InnoDB table columns, which is equivalent to SYS_COLUMNS table information within the InnoDB data dictionary

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_FOREIGN:

Provides metadata information about querying InnoDB foreign keys, which is equivalent to the information of SYS_FOREIGN tables inside the InnoDB data dictionary

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_SYS_TABLESTATS:

Provides a view of querying lower-level status information about InnoDB tables. The MySQL optimizer uses these statistics to calculate and determine which index to use when querying the InnoDB table. This information is stored in a data structure in memory and does not correspond to the data stored on disk. There is no corresponding system table within InnoDB.

This table is a temporary table of memory engine. Users who query this table need to have process permission.

5. InnoDB layer lock, transaction, statistical information dictionary table

INNODB_LOCKS:

Provide lock information that is being requested and not obtained in the innodb engine transaction and blocks other transactions at the same time (that is, lock information that no lock waiting between different transactions occurs is not visible here, for example, lock information added by the transaction cannot be seen when there is only one transaction). The contents of this table can be used to diagnose lock contention information under high concurrency.

This table is a temporary table of memory engine. Access to this table requires process permission.

INNODB_TRX:

Provides information about querying each transaction (excluding read-only transactions) currently executed in the InnoDB engine, including whether the transaction is waiting for a lock, when the transaction starts, and the text information of the SQL statement that the transaction is executing, if there is a SQL.

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_BUFFER_PAGE_LRU:

Provides information about pages in the query buffer pool. Unlike the INNODB_BUFFER_PAGE table, the INNODB_BUFFER_PAGE_LRU table stores information about how pages in innodb buffer pool enter the LRU linked list and determines which pages need to be ejected from the buffer pool when buffer pool is insufficient.

This table is the temporary table of Memory engine

INNODB_LOCK_WAITS:

Provides a lock wait record for querying each blocked InnoDB transaction, including locks such as locks that occur with transaction requests and locks that prevent the lock request from being granted

This table is a memory engine table, and the user who accesses it requires process permission.

INNODB_TEMP_TABLE_INFO:

Provides information about querying InnoDB temporary tables created by users currently active in the InnoDB instance (users who have established connections and temporary tables corresponding to disconnected users are automatically deleted). It does not provide information queries for internal InnoDB temporary tables used by the query optimizer. The INNODB_TEMP_TABLE_INFO table is created on the first query.

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_BUFFER_PAGE:

Provides information about querying about pages in buffer pool

Querying the table requires the user to have PROCESS permissions, which is the temporary table of the Memory engine

INNODB_METRICS:

Provides more detailed and detailed performance information for querying InnoDB, which is a supplement to InnoDB's PERFORMANCE_SCHEMA. Through the query of the table, it can be used to check the overall health status of innodb. It can also be used to diagnose performance bottlenecks, resource shortages, and application problems.

This table is a temporary table of memory engine. Users who query this table need to have process permission.

INNODB_BUFFER_POOL_STATS:

Provide to query some status information in Innodb buffer pool. The information recorded in this table is similar to the information output by SHOW ENGINE INNODB STATUS. In addition, some state variables of innodb buffer pool also provide some of the same values.

Process permission is required to view this table, which is a temporary table of Memory engine.

6. InnoDB layer full-text index dictionary table

INNODB_FT_CONFIG:

Provides information about querying FULLTEXT indexes and associated metadata about InnoDB tables. Before querying this table, you need to set innodb_ft_aux_table='db_name/tb_name',db_name/tb_name to the name of the table and library that contains the full-text index.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

INNODB_FT_BEING_DELETED:

This table is used only as snapshot data for the INNODB_FT_ delete table during maintenance operations performed by the OPTIMIZE TABLE statement. When you run the OPTIMIZE TABLE statement, it first clears the data in the INNODB_FT_BEING_ delete table, saves the snapshot data in the INNODB_FT_ delete table to the INNODB_FT_BEING_ delete table, and removes the DOC_ID from the INNODB_FT_ delete table. Because the content in the INNODB_FT_BEING_ delete table usually has a short life cycle, the data in the table is not very useful for monitoring or debugging.

The data is not recorded by default in the table, so you need to set the system configuration parameter innodb_ft_aux_table=string (string represents db_name.tb_name string), create a full-text index, set stop words, and so on.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

INNODB_FT_DELETED:

Provides information about rows that are deleted by the query from the FULLTEXT index of the InnoDB table. It exists to avoid expensive index reorganization operations during the DML operation of the InnoDB FULLTEXT index, where the information of the words in the newly deleted full-text index is stored separately in the table, where the search results are filtered out when the text search is performed, and the information in the table is emptied only when the OPTIMIZE TABLE statement is executed.

The information in this table is not recorded by default, and you need to use the innodb_ft_aux_table option (which defaults to an empty string) to specify which innodb engine table information needs to be recorded, for example: test/test.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

INNODB_FT_DEFAULT_STOPWORD:

This table is the default full-text index deactivated thesaurus and provides query deactivated word list values. To enable the deactivate thesaurus, you need to enable the parameter innodb_ft_enable_stopword=ON, which defaults to ON. After enabling the deactivate words function, if the innodb_ft_user_stopword_table option (effective for the full-text index in the specified innodb engine table) customizes the deactivate thesaurus name value, the deactivate word function uses the deactivate thesaurus specified by the innodb_ft_user_stopword_table option, if the innodb_ft_user_stopword_table option is not specified While the innodb_ft_server_stopword_table option (effective for full-text indexes in all innodb engine tables) customizes the deactivated thesaurus name value, the deactivated thesaurus specified by the innodb_ft_server_stopword_table option is used by the same deactivate words function, and if the innodb_ft_server_stopword_table option is not specified, the default deactivated thesaurus, the INNODB_FT_DEFAULT_ STOPWORD table, is used.

Querying this table requires the account to have PROCESS permission. This table is a temporary table of Memory engine.

INNODB_FT_INDEX_TABLE:

Provides word segmentation information about the inverted index used for reverse text lookup in the full-text index of the innodb table.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

INNODB_FT_INDEX_CACHE:

Provides full-text index tag information for querying newly inserted rows in the innodb storage engine table that contains FULLTEXT indexes. It exists to avoid expensive index reorganization during DML operations, where information about newly inserted full-text indexed words is stored separately in the table until the OPTIMIZE TABLE statement is executed on the table, or when the server is shut down, or when the size of the information stored in the cache exceeds the size specified by the innodb_ft_cache_size or innodb_ft_total_cache_size system configuration parameters. Data is not recorded by default, and you need to use the innodb_ft_aux_table system configuration parameter to specify which table you want to record the full-text index data for the newly inserted rows.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

7. InnoDB layer compression related dictionary table

INNODB_CMP and INNODB_CMP_RESET:

The data in these two tables contains status information about the operations related to the compressed InnoDB table page. The data recorded in the table provide a reference for the effectiveness of InnoDb table compression in the survey database.

The user who queries the table must have PROCESS privileges, which is the Memory engine temporary table

INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET:

The operation status information related to InnoDB compressed table data and index is recorded in these two tables, and different statistical information is used for each combination of database, table and index, so as to provide reference data for evaluating the compression performance and practicability of specific tables.

For InnoDB compressed tables, the data in the table and all secondary indexes are compressed. At this point, the data in the table is treated as another index (a clustered index containing all data columns).

Note: because collecting separate measures for each index results in significant performance degradation, INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET table statistics are not collected by default. If necessary, enable the system configuration parameter innodb_cmp_per_index_enabled (which is a dynamic variable and defaults to OFF).

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

INNODB_CMPMEM and INNODB_CMPMEM_RESET:

These two tables record the status information on the compressed pages in the InnoDB buffer pool, which provides a reference for the effectiveness of InnoDB table compression in the measurement database.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

This is the end of the content of "how to understand and master information_schema in mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.