MySQL 8.0 Reference Manual（读书笔记86节– InnoDB INFORMATION_SCHEMA Tables(2)）

1. InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

The following tables provide metadata for FULLTEXT indexes:

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%';
+-------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_FT%) |
+-------------------------------------------+
| INNODB_FT_CONFIG |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_DELETED |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
+-------------------------------------------+

概述

• INNODB_FT_CONFIG: Provides metadata about the FULLTEXT index and associated processing for an InnoDB table.

• INNODB_FT_BEING_DELETED: Provides a snapshot of the INNODB_FT_DELETED table; it is used only during an OPTIMIZE TABLE maintenance operation. When OPTIMIZE TABLE is run, the INNODB_FT_BEING_DELETED table is emptied, and DOC_ID values are removed from the INNODB_FT_DELETED table. Because the contents of INNODB_FT_BEING_DELETED typically have a short lifetime, this table has limited utility【juːˈtɪləti 多功能的;多用途的;多效用的;】 for monitoring or debugging.

• INNODB_FT_DELETED: Stores rows that are deleted from the FULLTEXT index for an InnoDB table. To avoid expensive index reorganization during DML operations for an InnoDB FULLTEXT index, the information about newly deleted words is stored separately, filtered out of search results when you do a text search, and removed from the main search index only when you issue an OPTIMIZE TABLE statement for the InnoDB table.

• INNODB_FT_DEFAULT_STOPWORD: Holds a list of stopwords that are used by default when creating a FULLTEXT index on InnoDB tables.

• INNODB_FT_INDEX_TABLE: Provides information about the inverted【ɪnˈvɜːrtɪd 反向的，倒转的，颠倒的，（尤指）倒置的;】 index used to process text searches against the FULLTEXT index of an InnoDB table.

• INNODB_FT_INDEX_CACHE: Provides token information about newly inserted rows in a FULLTEXT index. To avoid expensive index reorganization during DML operations, the information about newly indexed words is stored separately, and combined with the main search index only when OPTIMIZE TABLE is run, when the server is shut down, or when the cache size exceeds a limit defined by the innodb_ft_cache_size or innodb_ft_total_cache_size system variable.

说明

With the exception of the INNODB_FT_DEFAULT_STOPWORD table, these tables are empty initially. Before querying any of them, set the value of the innodb_ft_aux_table system variable to the name (including the database name) of the table that contains the FULLTEXT index (for example, test/ articles).

2.InnoDB INFORMATION_SCHEMA Buffer Pool Tables

The InnoDB INFORMATION_SCHEMA buffer pool tables provide buffer pool status information and metadata about the pages within the InnoDB buffer pool.

The InnoDB INFORMATION_SCHEMA buffer pool tables include those listed below:

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_POOL_STATS |
+-----------------------------------------------+

概述

 • INNODB_BUFFER_PAGE: Holds information about each page in the InnoDB buffer pool.

 • INNODB_BUFFER_PAGE_LRU: Holds information about the pages in the InnoDB buffer pool, in particular how they are ordered in the LRU list that determines which pages to evict【ɪˈvɪkt 驱逐;逐出;(尤指依法从房屋或土地上)赶出;】 from the buffer pool when it becomes full. The INNODB_BUFFER_PAGE_LRU table has the same columns as the INNODB_BUFFER_PAGE table, except that the INNODB_BUFFER_PAGE_LRU table has an LRU_POSITION column instead of a BLOCK_ID column.

• INNODB_BUFFER_POOL_STATS: Provides buffer pool status information. Much of the same information is provided by SHOW ENGINE INNODB STATUS output, or may be obtained using InnoDB buffer pool server status variables.

Warning

Querying the INNODB_BUFFER_PAGE or INNODB_BUFFER_PAGE_LRU table can affect performance. Do not query these tables on a production system unless you are aware of the performance impact and have determined it to be acceptable. To avoid impacting performance on a production system, reproduce the issue you want to investigate and query buffer pool statistics on a test instance.

查询示例

Querying User Data in the INNODB_BUFFER_PAGE Table

This query provides an approximate count of pages containing user data by counting pages where the TABLE_NAME value is NOT NULL and NOT LIKE ‘%INNODB_TABLES%’.

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
 WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%';
+----------+
| COUNT(*) |
+----------+
| 7897 |
+----------+

This query returns the approximate number of pages that contain user data, the total number of buffer pool pages, and an approximate percentage of pages that contain user data.

mysql> SELECT
 (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
 WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
 ) AS user_pages,
 (
 SELECT COUNT(*)
 FROM information_schema.INNODB_BUFFER_PAGE
 ) AS total_pages,
 (
 SELECT ROUND((user_pages/total_pages) * 100)
 ) AS user_page_percentage;
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
| 7897 | 8192 | 96 |
+------------+-------------+----------------------+

This query identifies user-defined tables with pages in the buffer pool:

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
 WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
 AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';
+-------------------------+
| TABLE_NAME |
+-------------------------+
| `employees`.`salaries` |
| `employees`.`employees` |
+-------------------------+

Querying Index Data in the INNODB_BUFFER_PAGE Table

For information about index pages, query the INDEX_NAME column using the name of the index. For example, the following query returns the number of pages and total data size of pages for the emp_no index that is defined on the employees.salaries table:

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
AS 'Total Data (MB)'
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`';
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| emp_no | 1609 | 25 |
+------------+-------+-----------------+

This query returns the number of pages and total data size of pages for all indexes defined on the employees.salaries table:

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
 ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
 AS 'Total Data (MB)'
 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
 WHERE TABLE_NAME = '`employees`.`salaries`'
 GROUP BY INDEX_NAME;
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| emp_no | 1608 | 25 |
| PRIMARY | 6086 | 95 |
+------------+-------+-----------------+

3.InnoDB INFORMATION_SCHEMA Metrics Table

The INNODB_METRICS table provides information about InnoDB performance and resource-related counters.

INNODB_METRICS table columns are shown below.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
 NAME: dml_inserts
 SUBSYSTEM: dml
 COUNT: 46273
 MAX_COUNT: 46273
 MIN_COUNT: NULL
 AVG_COUNT: 492.2659574468085
 COUNT_RESET: 46273
MAX_COUNT_RESET: 46273
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
 TIME_ENABLED: 2014-11-28 16:07:53
 TIME_DISABLED: NULL
 TIME_ELAPSED: 94
 TIME_RESET: NULL
 STATUS: enabled
 TYPE: status_counter
 COMMENT: Number of rows inserted

Counters

The list of available counters is subject to change. Query the Information Schema INNODB_METRICS table for counters available in your MySQL server version.

The counters enabled by default correspond to those shown in SHOW ENGINE INNODB STATUS output. Counters shown in SHOW ENGINE INNODB STATUS output are always enabled at a system level but can be disable for the INNODB_METRICS table. Counter status is not persistent. Unless configured otherwise, counters revert to their default enabled or disabled status when the server is restarted.

If you run programs that would be affected by the addition or removal of counters, it is recommended that you review the releases notes and query the INNODB_METRICS table to identify those changes as part of your upgrade process.

查询各个counters

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME;

Counter Modules

Each counter is associated with a particular module. Module names can be used to enable, disable, or reset all counters for a particular subsystem. For example, use module_dml to enable all counters associated with the dml subsystem.

mysql> SET GLOBAL innodb_monitor_enable = module_dml;
mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS
 WHERE subsystem ='dml';
+-------------+-----------+---------+
| name | subsystem | status |
+-------------+-----------+---------+
| dml_reads | dml | enabled |
| dml_inserts | dml | enabled |
| dml_deletes | dml | enabled |
| dml_updates | dml | enabled |
+-------------+-----------+---------+

Module names can be used with innodb_monitor_enable and related variables.

Module names and corresponding SUBSYSTEM names are listed below.

• module_adaptive_hash (subsystem = adaptive_hash_index)

• module_buffer (subsystem = buffer)

• module_buffer_page (subsystem = buffer_page_io)

• module_compress (subsystem = compression)

• module_ddl (subsystem = ddl)

• module_dml (subsystem = dml)

• module_file (subsystem = file_system)

• module_ibuf_system (subsystem = change_buffer)

• module_icp (subsystem = icp)

• module_index (subsystem = index)

• module_innodb (subsystem = innodb)

• module_lock (subsystem = lock)

• module_log (subsystem = log)

• module_metadata (subsystem = metadata)

• module_os (subsystem = os)

• module_purge (subsystem = purge)

• module_trx (subsystem = transaction)

• module_undo (subsystem = undo)

4. InnoDB INFORMATION_SCHEMA Temporary Table Info Table

INNODB_TEMP_TABLE_INFO provides information about user-created InnoDB temporary tables that are active in the InnoDB instance. It does not provide information about internal InnoDB temporary tables used by the optimizer.

5.Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES

The Information Schema FILES table provides metadata about all InnoDB tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, temporary table tablespaces, and undo tablespaces (if present).

The INNODB_TABLESPACES and INNODB_DATAFILES tables also provide metadata about InnoDB tablespaces, but data is limited to file-per-table, general, and undo tablespaces.

This query retrieves metadata about the InnoDB system tablespace from fields of the Information Schema FILES table that are pertinent to InnoDB tablespaces. FILES columns that are not relevant to InnoDB always return NULL, and are excluded from the query.

mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
 TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE
 FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system' \G
*************************** 1. row ***************************
 FILE_ID: 0
 FILE_NAME: ./ibdata1
 FILE_TYPE: TABLESPACE
TABLESPACE_NAME: innodb_system
 FREE_EXTENTS: 0
 TOTAL_EXTENTS: 12
 EXTENT_SIZE: 1048576
 INITIAL_SIZE: 12582912
 MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 67108864
 DATA_FREE: 4194304
 ENGINE: NORMAL

This query retrieves the FILE_ID and FILE_NAME for the InnoDB global temporary tablespace. Global temporary tablespace file names are prefixed by ibtmp.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES
 WHERE FILE_NAME LIKE '%ibtmp%';
+---------+-----------+
| FILE_ID | FILE_NAME |
+---------+-----------+
| 22 | ./ibtmp1 |
+---------+-----------+

Similarly, InnoDB undo tablespace file names are prefixed by undo. The following query returns the FILE_ID and FILE_NAME for InnoDB undo tablespaces.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES
 WHERE FILE_NAME LIKE '%undo%';

 

6.InnoDB Integration with MySQL Performance Schema

 This section provides a brief introduction to InnoDB integration with Performance Schema.

 You can profile【ˈproʊfaɪl 扼要介绍;概述;写简介;】 certain internal InnoDB operations using the MySQL Performance Schema feature. This type of tuning is primarily for expert users who evaluate optimization strategies to overcome performance bottlenecks. DBAs can also use this feature for capacity planning, to see whether their typical workload encounters any performance bottlenecks with a particular combination of CPU, RAM, and disk storage; and if so, to judge whether performance can be improved by increasing the capacity of some part of the system.

To use this feature to examine InnoDB performance:

• You must be generally familiar with how to use the Performance Schema feature. For example, you should know how enable instruments【ˈɪnstrəmənts 仪器;(车辆、机器的)仪器，仪表;器械;手段;器具;促成某事的人(或事物);】 and consumers, and how to query performance_schema tables to retrieve data.

• You should be familiar with Performance Schema instruments that are available for InnoDB. To view InnoDB-related instruments, you can query the setup_instruments table for instrument names that contain ‘innodb’.

mysql> SELECT *
 FROM performance_schema.setup_instruments
 WHERE NAME LIKE '%innodb%';

For additional information about the instrumented InnoDB objects, you can query Performance Schema instances tables, which provide additional information about instrumented objects.

Instance tables relevant to InnoDB include:

• The mutex_instances table

• The rwlock_instances table

• The cond_instances table

• The file_instances table

Mutexes and RW-locks related to the InnoDB buffer pool are not included in this coverage; the same applies to the output of the SHOW ENGINE INNODB MUTEX command.

7. 其它知识点

7.1 InnoDB Limits

This section describes limits for InnoDB tables, indexes, tablespaces, and other aspects of the InnoDB storage engine.

• A table can contain a maximum of 1017 columns. Virtual generated columns are included in this limit.

• A table can contain a maximum of 64 secondary indexes.

• The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

Attempting to use an index key prefix length that exceeds the limit returns an error.

If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

The limits that apply to index key prefixes also apply to full-column index keys.

• A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.

ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

• The maximum row size, excluding any variable-length columns that are stored off-page, is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row size for the default innodb_page_size of 16KB is about 8000 bytes. However, for an InnoDB page size of 64KB, the maximum row size is approximately 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row size, including BLOB and TEXT columns, must be less than 4GB.

If a row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page.

• Although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a rowsize limit of 65,535 for the combined size of all columns. 

• On some older operating systems, files must be less than 2GB. This is not an InnoDB limitation. If you require a large system tablespace, configure it using several smaller data files rather than one large data file, or distribute table data across file-per-table and general tablespace data files.

• The combined maximum size for InnoDB log files is 512GB.

• The minimum tablespace size is slightly larger than 10MB. The maximum tablespace size depends on the InnoDB page size.

• An InnoDB instance supports up to 2^32 (4294967296) tablespaces, with a small number of those tablespaces reserved for undo and temporary tables.

• Shared tablespaces support up to 2^32 (4294967296) tables.

• The path of a tablespace file, including the file name, cannot exceed the MAX_PATH limit on Windows. Prior to Windows 10, the MAX_PATH limit is 260 characters. As of Windows 10, version 1607, MAX_PATH limitations are removed from common Win32 file and directory functions, but you must enable the new behavior.

• For limits associated with concurrent read-write transactions.

7.2 InnoDB Restrictions and Limitations

This section describes restrictions and limitations of the InnoDB storage engine.

• You cannot create a table with a column name that matches the name of an internal InnoDB column (including DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR. This restriction applies to use of the names in any lettercase.

mysql> CREATE TABLE t1 (c1 INT, db_row_id INT) ENGINE=INNODB;
ERROR 1166 (42000): Incorrect column name 'db_row_id'

• SHOW TABLE STATUS does not provide accurate【ˈækjərət 精确的;准确的(掷、射、击等);正确无误的;】 statistics for InnoDB tables except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

• InnoDB does not keep an internal

【ɪnˈtɜːrnl 里面的;本身的;内政的;体内的;内心的;(机构)内部的;】count of rows in a table because concurrent【kənˈkɜːrənt 同时发生的;同意的，一致的;<律>有相等权力的，同时(实施)的;合作的;协调的;并存的;<数>共点的，会合的;共同(或同时)起作用的;】 transactions might “see” different numbers of rows at the same time. Consequently【ˈkɑːnsɪkwentli 因此;所以;】, SELECT COUNT(*) statements only count rows visible to the current transaction.

• ROW_FORMAT=COMPRESSED is unsupported for page sizes greater than 16KB.

• A MySQL instance using a particular InnoDB page size (innodb_page_size) cannot use data files or log files from an instance that uses a different page size.