MySQL 8.0 Reference Manual（讀書筆記85節-- InnoDB INFORMATION_SCHEMA Tables(1)）

1.概述

This section provides information and usage【ˈjuːsɪdʒ 使用;(詞語的)用法，慣用法;利用;利用率;】 examples for InnoDB INFORMATION_SCHEMA tables.

InnoDB INFORMATION_SCHEMA tables provide metadata, status information, and statistics about various aspects of the InnoDB storage engine. You can view a list of InnoDB INFORMATION_SCHEMA tables by issuing a SHOW TABLES statement on the INFORMATION_SCHEMA database:

 SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB%';

2.InnoDB INFORMATION_SCHEMA Tables about Compression

There are two pairs of InnoDB INFORMATION_SCHEMA tables about compression that can provide insight into【insight into 洞察力;洞察;對…深刻理解】 how well compression is working overall:

• INNODB_CMP and INNODB_CMP_RESET provide information about the number of compression operations and the amount of time spent performing compression.

• INNODB_CMPMEM and INNODB_CMPMEM_RESET provide information about the way memory is allocated for compression.

2.1 INNODB_CMP and INNODB_CMP_RESET

The INNODB_CMP and INNODB_CMP_RESET tables provide status information about operations related to compressed tables. The PAGE_SIZE column reports the compressed page size.

These two tables have identical【aɪˈdentɪkl 完全相同的;相同的;同一的;完全同樣的;】 contents, but reading from INNODB_CMP_RESET resets the statistics on compression and uncompression operations. For example, if you archive the output of INNODB_CMP_RESET every 60 minutes, you see the statistics for each hourly period. If you monitor the output of INNODB_CMP (making sure never to read INNODB_CMP_RESET), you see the cumulative statistics since InnoDB was started.

2.2 INNODB_CMPMEM and INNODB_CMPMEM_RESET

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables provide status information about compressed pages that reside in the buffer pool.

2.3 Internal Details

InnoDB uses a buddy allocator system to manage memory allocated to pages of various sizes, from 1KB to 16KB. Each row of the two tables described here corresponds to a single page size.

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables have identical contents, but reading from INNODB_CMPMEM_RESET resets the statistics on relocation operations. For example, if every 60 minutes you archived the output of INNODB_CMPMEM_RESET, it would show the hourly statistics. If you never read INNODB_CMPMEM_RESET and monitored the output of INNODB_CMPMEM instead, it would show the cumulative statistics since InnoDB was started.

3.InnoDB INFORMATION_SCHEMA Transaction and Locking Information

3.1 概述

This section describes locking information as exposed by the Performance Schema data_locks and data_lock_waits tables, which supersede【ˌsuːpərˈsiːd 取代;替代(已非最佳選擇或已過時的事物);】 the INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables in MySQL 8.0.

One INFORMATION_SCHEMA table and two Performance Schema tables enable you to monitor InnoDB transactions and diagnose potential locking problems:

• INNODB_TRX: This INFORMATION_SCHEMA table provides information about every transaction currently executing inside InnoDB, including the transaction state (for example, whether it is running or waiting for a lock), when the transaction started, and the particular SQL statement the transaction is executing.

• data_locks: This Performance Schema table contains a row for each hold lock and each lock request that is blocked waiting for a held lock to be released:

•  There is one row for each held lock, whatever the state of the transaction that holds the lock (INNODB_TRX.TRX_STATE is RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING).
•  Each transaction in InnoDB that is waiting for another transaction to release a lock (INNODB_TRX.TRX_STATE is LOCK WAIT) is blocked by exactly one blocking lock request. That blocking lock request is for a row or table lock held by another transaction in an incompatible mode.

A lock request always has a mode that is incompatible【ˌɪnkəmˈpætəbl 不相容的，不相容的，互斥的;（與某事物）不一致，不相配;不協調的;】 with the mode of the held lock that blocks the request (read vs. write, shared vs. exclusive). The blocked transaction cannot proceed until the other transaction commits or rolls back, thereby【從而;由此;因此;】releasing the requested lock. For every blocked transaction, data_locks contains one row that describes each lock the transaction has requested, and for which it is waiting.

• data_lock_waits: This Performance Schema table indicates which transactions are waiting for a given lock, or for which lock a given transaction is waiting. This table contains one or more rows for each blocked transaction, indicating the lock it has requested and any locks that are blocking that request. The REQUESTING_ENGINE_LOCK_ID value refers to the lock requested by a transaction, and the BLOCKING_ENGINE_LOCK_ID value refers to the lock (held by another transaction) that prevents the first transaction from proceeding. For any given blocked transaction, all rows in data_lock_waits have the same value for REQUESTING_ENGINE_LOCK_ID and different values for BLOCKING_ENGINE_LOCK_ID.

3.2 Using InnoDB Transaction and Locking Information

Identifying Blocking Transactions

It is sometimes helpful to identify which transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested.

Suppose that three sessions are running concurrently【kənˈkɜrəntli 同時;兼;】. Each session corresponds【ˌkɔːrəˈspɑːndz 相當於;通信;符合;相一致;類似於;】 to a MySQL thread, and executes one transaction after another. Consider the state of the system when these sessions have issued the following statements, but none has yet committed its transaction:

• Session A:

BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);

• Session B:

SELECT b FROM t FOR UPDATE;

• Session C:

SELECT c FROM t FOR UPDATE;

In this scenario【səˈnærioʊ 方案;設想;預測;腳本;(電影或戲劇的)劇情梗概;】, use the following query to see which transactions are waiting and which transactions are blocking them:

SELECT
 r.trx_id waiting_trx_id,
 r.trx_mysql_thread_id waiting_thread,
 r.trx_query waiting_query,
 b.trx_id blocking_trx_id,
 b.trx_mysql_thread_id blocking_thread,
 b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
 ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
 ON r.trx_id = w.requesting_engine_transaction_id;

Or, more simply, use the sys schema innodb_lock_waits view:

SELECT
 waiting_trx_id,
 waiting_pid,
 waiting_query,
 blocking_trx_id,
 blocking_pid,
 blocking_query
FROM sys.innodb_lock_waits;

In the preceding table, you can identify sessions by the “waiting query” or “blocking query” columns. As you can see:

• Session B (trx id A4, thread 6) and Session C (trx id A5, thread 7) are both waiting for Session A (trx id A3, thread 5).

• Session C is waiting for Session B as well as Session A.

You can see the underlying data in the INFORMATION_SCHEMA INNODB_TRX table and Performance Schema data_locks and data_lock_waits tables.

The following table shows some sample contents of the INNODB_TRX table.

The following table shows some sample contents of the data_locks table.

The following table shows some sample contents of the data_lock_waits table.

Identifying a Blocking Query After the Issuing Session Becomes Idle

When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle. In this case, use the following steps to determine the blocking query:

1. Identify the processlist ID of the blocking transaction. In the sys.innodb_lock_waits table, the processlist ID of the blocking transaction is the blocking_pid value.

2. Using the blocking_pid, query the MySQL Performance Schema threads table to determine the THREAD_ID of the blocking transaction. For example, if the blocking_pid is 6, issue this query:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

3. Using the THREAD_ID, query the Performance Schema events_statements_current table to determine the last query executed by the thread. For example, if the THREAD_ID is 28, issue this query:

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28\G

4. If the last query executed by the thread is not enough information to determine why a lock is held, you can query the Performance Schema events_statements_history table to view the last 10 statements executed by the thread.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

Correlating InnoDB Transactions with MySQL Sessions

Sometimes it is useful to correlate internal InnoDB locking information with the session-level information maintained by MySQL. For example, you might like to know, for a given InnoDB transaction ID, the corresponding MySQL session ID and name of the session that may be holding a lock, and thus blocking other transactions.

3.3 InnoDB Lock and Lock-Wait Information

When a transaction updates a row in a table, or locks it with SELECT FOR UPDATE, InnoDB establishes【ɪˈstæblɪʃɪz 建立(尤指正式關係);設立;確立;創立;使立足;使穩固;】 a list or queue of locks on that row. Similarly, InnoDB maintains a list of locks on a table for table-level locks. If a second transaction wants to update a row or lock a table already locked by a prior【ˈpraɪər 先前的，較早的，在前的;優先的，占先的;】 transaction in an incompatible【ˌɪnkəmˈpætəbl 不相容的，不相容的，互斥的;】 mode, InnoDB adds a lock request for the row to the corresponding queue. For a lock to be acquired by a transaction, all incompatible lock requests previously entered into the lock queue for that row or table must be removed (which occurs when the transactions holding or requesting those locks either commit or roll back).

A transaction may have any number of lock requests for different rows or tables. At any given time, a transaction may request a lock that is held by another transaction, in which case it is blocked by that other transaction. The requesting transaction must wait for the transaction that holds the blocking lock to commit or roll back. If a transaction is not waiting for a lock, it is in a RUNNING state. If a transaction is waiting for a lock, it is in a LOCK WAIT state. (The INFORMATION_SCHEMA INNODB_TRX table indicates transaction state values.)

The Performance Schema data_locks table holds one or more rows for each LOCK WAIT transaction, indicating any lock requests that prevent its progress. This table also contains one row describing each lock in a queue of locks pending for a given row or table. The Performance Schema data_lock_waits table shows which locks already held by a transaction are blocking locks requested by other transactions.

3.4 Persistence and Consistency of InnoDB Transaction and Locking Information

The data exposed【ɪkˈspoʊzd 暴露;露出;揭露;】 by the transaction and locking tables (INFORMATION_SCHEMA INNODB_TRX table, Performance Schema data_locks and data_lock_waits tables) represents a glimpse【ɡlɪmps 一瞥;一看;短暫的感受(或體驗、領會);】 into fast-changing data. This is not like user tables, where the data changes only when application-initiated updates occur. The underlying data is internal system-managed data, and can change very quickly: 

• Data might not be consistent between the INNODB_TRX, data_locks, and data_lock_waits tables.

The data_locks and data_lock_waits tables expose live data from the InnoDB storage engine, to provide lock information about the transactions in the INNODB_TRX table. Data retrieved from the lock tables exists when the SELECT is executed, but might be gone or changed by the time the query result is consumed by the client.

Joining data_locks with data_lock_waits can show rows in data_lock_waits that identify a parent row in data_locks that no longer exists or does not exist yet.

• Data in the transaction and locking tables might not be consistent with data in the INFORMATION_SCHEMA PROCESSLIST table or Performance Schema threads table.

For example, you should be careful when comparing data in the InnoDB transaction and locking tables with data in the PROCESSLIST table. Even if you issue a single SELECT (joining INNODB_TRX and PROCESSLIST, for example), the content of those tables is generally not consistent. It is possible for INNODB_TRX to reference rows that are not present in PROCESSLIST or for the currently executing SQL query of a transaction shown in INNODB_TRX.TRX_QUERY to differ from the one in PROCESSLIST.INFO.

4.InnoDB INFORMATION_SCHEMA Schema Object Tables

You can extract metadata about schema objects managed by InnoDB using InnoDB INFORMATION_SCHEMA tables. This information comes from the data dictionary. The InnoDB INFORMATION_SCHEMA table interface allows you to query this data using SQL.

InnoDB INFORMATION_SCHEMA schema object tables include the tables listed below.

INNODB_DATAFILES
INNODB_TABLESTATS
INNODB_FOREIGN
INNODB_COLUMNS
INNODB_INDEXES
INNODB_FIELDS
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_FOREIGN_COLS
INNODB_TABLES

The table names are indicative【ɪnˈdɪkətɪv 指示的;表明;顯示;標示;指示性;暗示;陳述的;】 of the type of data provided:

• INNODB_TABLES provides metadata about InnoDB tables.

• INNODB_COLUMNS provides metadata about InnoDB table columns.

• INNODB_INDEXES provides metadata about InnoDB indexes.

• INNODB_FIELDS provides metadata about the key columns (fields) of InnoDB indexes.

• INNODB_TABLESTATS provides a view of low-level status information about InnoDB tables that is derived from in-memory data structures.

• INNODB_DATAFILES provides data file path information for InnoDB file-per-table and general tablespaces.

• INNODB_TABLESPACES provides metadata about InnoDB file-per-table, general, and undo tablespaces.

• INNODB_TABLESPACES_BRIEF provides a subset of metadata about InnoDB tablespaces.

• INNODB_FOREIGN provides metadata about foreign keys defined on InnoDB tables.

• INNODB_FOREIGN_COLS provides metadata about the columns of foreign keys that are defined on InnoDB tables.

InnoDB INFORMATION_SCHEMA schema object tables can be joined together through fields such as TABLE_ID, INDEX_ID, and SPACE, allowing you to easily retrieve all available data for an object you want to study or monitor.

舉個例子

創建場景

mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (
 col1 INT,
 col2 CHAR(10),
 col3 VARCHAR(10))
 ENGINE = InnoDB;
mysql> CREATE INDEX i1 ON t1(col1);

查詢 INNODB_TABLES

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
 TABLE_ID: 71
 NAME: test/t1
 FLAG: 1
 N_COLS: 6
 SPACE: 57
 ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
 INSTANT_COLS: 0

Table t1 has a TABLE_ID of 71. The FLAG field provides bit level information about table format and storage characteristics. There are six columns, three of which are hidden columns created by InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).[包含三個隱藏列] The ID of the table's SPACE is 57 (a value of 0 would indicate that the table resides in the system tablespace). The ROW_FORMAT is Compact. ZIP_PAGE_SIZE only applies to tables with a Compressed row format. INSTANT_COLS shows number of columns in the table prior to adding the first instant column using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT.

查詢 INNODB_INDEXES

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 71 \G
*************************** 1. row ***************************
 INDEX_ID: 111
 NAME: GEN_CLUST_INDEX
 TABLE_ID: 71
 TYPE: 1
 N_FIELDS: 0
 PAGE_NO: 3
 SPACE: 57
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
 INDEX_ID: 112
 NAME: i1
 TABLE_ID: 71
 TYPE: 0
 N_FIELDS: 1
 PAGE_NO: 4
 SPACE: 57
MERGE_THRESHOLD: 50

INNODB_INDEXES returns data for two indexes. The first index is GEN_CLUST_INDEX, which is a clustered index created by InnoDB if the table does not have a user-defined clustered index. The second index (i1) is the user-defined secondary index.

The INDEX_ID is an identifier for the index that is unique across all databases in an instance. The TABLE_ID identifies the table that the index is associated with. The index TYPE value indicates the type of index (1 = Clustered Index, 0 = Secondary index). The N_FILEDS value is the number of fields that comprise the index. PAGE_NO is the root page number of the index B-tree, and SPACE is the ID of the tablespace where the index resides. A nonzero value indicates that the index does not reside in the system tablespace. MERGE_THRESHOLD defines a percentage threshold value for the amount of data in an index page. If the amount of data in an index page falls below the this value (the default is 50%) when a row is deleted or when a row is shortened by an update operation, InnoDB attempts to merge the index page with a neighboring index page.

可以利用上面查詢的 INDEX_ID 去查看 INNODB_FIELDS

Using the INDEX_ID information from INNODB_INDEXES, query INNODB_FIELDS for information about the fields of index i1.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
 NAME: col1
 POS: 0

INNODB_FIELDS provides the NAME of the indexed field and its ordinal position within the index. If the index (i1) had been defined on multiple fields, INNODB_FIELDS would provide metadata for each of the indexed fields.

外鍵

The INNODB_FOREIGN and INNODB_FOREIGN_COLS tables provide data about foreign key relationships. This example uses a parent table and child table with a foreign key relationship to demonstrate the data found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables.

示例

mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE parent (id INT NOT NULL,
 PRIMARY KEY (id)) ENGINE=INNODB;
mysql> CREATE TABLE child (id INT, parent_id INT,
 INDEX par_ind (parent_id),
 CONSTRAINT fk1
 FOREIGN KEY (parent_id) REFERENCES parent(id)
 ON DELETE CASCADE) ENGINE=INNODB;

關係查詢

After the parent and child tables are created, query INNODB_FOREIGN and locate the foreign key data for the test/child and test/parent foreign key relationship:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
 ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
 N_COLS: 1
 TYPE: 1

Metadata includes the foreign key ID (fk1), which is named for the CONSTRAINT that was defined on the child table. The FOR_NAME is the name of the child table where the foreign key is defined. REF_NAME is the name of the parent table (the “referenced” table). N_COLS is the number of columns in the foreign key index. TYPE is a numerical【nuːˈmerɪkl 數字的;用數字表示的;】 value representing bit flags that provide additional information about the foreign key column. In this case, the TYPE value is 1, which indicates that the ON DELETE CASCADE option was specified for the foreign key. 

查看 INNODB_FOREIGN_COLS

Using the foreign key ID, query INNODB_FOREIGN_COLS to view data about the columns of the foreign key.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1' \G
*************************** 1. row ***************************
 ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
 POS: 0

FOR_COL_NAME is the name of the foreign key column in the child table, and REF_COL_NAME is the name of the referenced column in the parent table. The POS value is the ordinal position of the key field within the foreign key index, starting at zero.

聯合查詢舉例

This example demonstrates joining three InnoDB INFORMATION_SCHEMA schema object tables (INNODB_TABLES, INNODB_TABLESPACES, and INNODB_TABLESTATS) to gather file format, row format, page size, and index size information about tables in the employees sample database.

mysql> SELECT a.NAME, a.ROW_FORMAT,
 @page_size :=
 IF(a.ROW_FORMAT='Compressed',
 b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
 AS page_size,
 ROUND((@page_size * c.CLUST_INDEX_SIZE)
 /(1024*1024)) AS pk_mb,
 ROUND((@page_size * c.OTHER_INDEX_SIZE)
 /(1024*1024)) AS secidx_mb
 FROM INFORMATION_SCHEMA.INNODB_TABLES a
 INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES b on a.NAME = b.NAME
 INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESTATS c on b.NAME = c.NAME
 WHERE a.NAME LIKE 'employees/%' #### 可以替代你指定的資料庫
 ORDER BY a.NAME DESC;