MySQL 8.4 版本说明
以下部分描述了表 I/O 和锁等待摘要表
table_io_waits_summary_by_index_usage: 按索引使用情况统计的表 I/O 等待table_io_waits_summary_by_table: 按表统计的表 I/O 等待table_lock_waits_summary_by_table: 按表统计的表锁等待
table_io_waits_summary_by_table 表聚合了所有表 I/O 等待事件，这些事件是由 wait/io/table/sql/handler 仪器生成的。分组方式为按表。
table_io_waits_summary_by_table 表具有以下分组列，用于指示表如何聚合事件：OBJECT_TYPE、OBJECT_SCHEMA 和 OBJECT_NAME。这些列的含义与 events_waits_current 表中的相同。它们标识了该行所应用的表。
table_io_waits_summary_by_table 表具有以下摘要列，包含聚合值。如列描述中所示，某些列更通用，其值与更细粒度列的总和相同。例如，聚合所有写入操作的列包含对应于聚合插入、更新和删除操作的列的总和。这样，更高级别的聚合可以直接使用，无需用户定义视图来对更低级别的列进行求和。
COUNT_STAR、SUM_TIMER_WAIT、MIN_TIMER_WAIT、AVG_TIMER_WAIT、MAX_TIMER_WAIT这些列聚合所有 I/O 操作。它们与对应于
xxx_READxxx_WRITECOUNT_READ、SUM_TIMER_READ、MIN_TIMER_READ、AVG_TIMER_READ、MAX_TIMER_READ这些列聚合所有读取操作。它们与对应于
xxx_FETCHCOUNT_WRITE、SUM_TIMER_WRITE、MIN_TIMER_WRITE、AVG_TIMER_WRITE、MAX_TIMER_WRITE这些列聚合所有写入操作。它们与对应于
xxx_INSERTxxx_UPDATExxx_DELETECOUNT_FETCH、SUM_TIMER_FETCH、MIN_TIMER_FETCH、AVG_TIMER_FETCH、MAX_TIMER_FETCH这些列聚合所有获取操作。
COUNT_INSERT、SUM_TIMER_INSERT、MIN_TIMER_INSERT、AVG_TIMER_INSERT、MAX_TIMER_INSERT这些列聚合所有插入操作。
COUNT_UPDATE、SUM_TIMER_UPDATE、MIN_TIMER_UPDATE、AVG_TIMER_UPDATE、MAX_TIMER_UPDATE这些列聚合所有更新操作。
COUNT_DELETE、SUM_TIMER_DELETE、MIN_TIMER_DELETE、AVG_TIMER_DELETE、MAX_TIMER_DELETE这些列聚合所有删除操作。
The table_io_waits_summary_by_table table has these indexes
Unique index on (
OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME)
TRUNCATE TABLE is permitted for table I/O summary tables. It resets the summary columns to zero rather than removing rows. Truncating this table also truncates the table_io_waits_summary_by_index_usage table.
The table_io_waits_summary_by_index_usage table aggregates all table index I/O wait events, as generated by the wait/io/table/sql/handler instrument. The grouping is by table index.
The columns of table_io_waits_summary_by_index_usage are nearly identical to table_io_waits_summary_by_table. The only difference is the additional group column, INDEX_NAME, which corresponds to the name of the index that was used when the table I/O wait event was recorded
A value of
PRIMARYindicates that table I/O used the primary index.A value of
NULLmeans that table I/O used no index.Inserts are counted against
INDEX_NAME = NULL.
The table_io_waits_summary_by_index_usage table has these indexes
Unique index on (
OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME)
TRUNCATE TABLE is permitted for table I/O summary tables. It resets the summary columns to zero rather than removing rows. This table is also truncated by truncation of the table_io_waits_summary_by_table table. A DDL operation that changes the index structure of a table may cause the per-index statistics to be reset.
The table_lock_waits_summary_by_table table aggregates all table lock wait events, as generated by the wait/lock/table/sql/handler instrument. The grouping is by table.
This table contains information about internal and external locks
An internal lock corresponds to a lock in the SQL layer. This is currently implemented by a call to
thr_lock(). In event rows, these locks are distinguished by theOPERATIONcolumn, which has one of these valuesread normal read with shared locks read high priority read no insert write allow write write concurrent insert write delayed write low priority write normalAn external lock corresponds to a lock in the storage engine layer. This is currently implemented by a call to
handler::external_lock(). In event rows, these locks are distinguished by theOPERATIONcolumn, which has one of these valuesread external write external
The table_lock_waits_summary_by_table table has these grouping columns to indicate how the table aggregates events: OBJECT_TYPE, OBJECT_SCHEMA, and OBJECT_NAME. These columns have the same meaning as in the events_waits_current table. They identify the table to which the row applies.
table_lock_waits_summary_by_table has the following summary columns containing aggregated values. As indicated in the column descriptions, some columns are more general and have values that are the same as the sum of the values of more fine-grained columns. For example, columns that aggregate all locks hold the sum of the corresponding columns that aggregate read and write locks. In this way, aggregations at higher levels are available directly without the need for user-defined views that sum lower-level columns.
COUNT_STAR、SUM_TIMER_WAIT、MIN_TIMER_WAIT、AVG_TIMER_WAIT、MAX_TIMER_WAITThese columns aggregate all lock operations. They are the same as the sum of the corresponding
xxx_READxxx_WRITECOUNT_READ、SUM_TIMER_READ、MIN_TIMER_READ、AVG_TIMER_READ、MAX_TIMER_READThese columns aggregate all read-lock operations. They are the same as the sum of the corresponding
xxx_READ_NORMALxxx_READ_WITH_SHARED_LOCKSxxx_READ_HIGH_PRIORITYxxx_READ_NO_INSERTCOUNT_WRITE、SUM_TIMER_WRITE、MIN_TIMER_WRITE、AVG_TIMER_WRITE、MAX_TIMER_WRITEThese columns aggregate all write-lock operations. They are the same as the sum of the corresponding
xxx_WRITE_ALLOW_WRITExxx_WRITE_CONCURRENT_INSERTxxx_WRITE_LOW_PRIORITYxxx_WRITE_NORMALCOUNT_READ_NORMAL,SUM_TIMER_READ_NORMAL,MIN_TIMER_READ_NORMAL,AVG_TIMER_READ_NORMAL,MAX_TIMER_READ_NORMALThese columns aggregate internal read locks.
COUNT_READ_WITH_SHARED_LOCKS,SUM_TIMER_READ_WITH_SHARED_LOCKS,MIN_TIMER_READ_WITH_SHARED_LOCKS,AVG_TIMER_READ_WITH_SHARED_LOCKS,MAX_TIMER_READ_WITH_SHARED_LOCKSThese columns aggregate internal read locks.
COUNT_READ_HIGH_PRIORITY,SUM_TIMER_READ_HIGH_PRIORITY,MIN_TIMER_READ_HIGH_PRIORITY,AVG_TIMER_READ_HIGH_PRIORITY,MAX_TIMER_READ_HIGH_PRIORITYThese columns aggregate internal read locks.
COUNT_READ_NO_INSERT,SUM_TIMER_READ_NO_INSERT,MIN_TIMER_READ_NO_INSERT,AVG_TIMER_READ_NO_INSERT,MAX_TIMER_READ_NO_INSERTThese columns aggregate internal read locks.
COUNT_READ_EXTERNAL,SUM_TIMER_READ_EXTERNAL,MIN_TIMER_READ_EXTERNAL,AVG_TIMER_READ_EXTERNAL,MAX_TIMER_READ_EXTERNALThese columns aggregate external read locks.
COUNT_WRITE_ALLOW_WRITE,SUM_TIMER_WRITE_ALLOW_WRITE,MIN_TIMER_WRITE_ALLOW_WRITE,AVG_TIMER_WRITE_ALLOW_WRITE,MAX_TIMER_WRITE_ALLOW_WRITEThese columns aggregate internal write locks.
COUNT_WRITE_CONCURRENT_INSERT,SUM_TIMER_WRITE_CONCURRENT_INSERT,MIN_TIMER_WRITE_CONCURRENT_INSERT,AVG_TIMER_WRITE_CONCURRENT_INSERT,MAX_TIMER_WRITE_CONCURRENT_INSERTThese columns aggregate internal write locks.
COUNT_WRITE_LOW_PRIORITY,SUM_TIMER_WRITE_LOW_PRIORITY,MIN_TIMER_WRITE_LOW_PRIORITY,AVG_TIMER_WRITE_LOW_PRIORITY,MAX_TIMER_WRITE_LOW_PRIORITYThese columns aggregate internal write locks.
COUNT_WRITE_NORMAL,SUM_TIMER_WRITE_NORMAL,MIN_TIMER_WRITE_NORMAL,AVG_TIMER_WRITE_NORMAL,MAX_TIMER_WRITE_NORMALThese columns aggregate internal write locks.
COUNT_WRITE_EXTERNAL,SUM_TIMER_WRITE_EXTERNAL,MIN_TIMER_WRITE_EXTERNAL,AVG_TIMER_WRITE_EXTERNAL,MAX_TIMER_WRITE_EXTERNALThese columns aggregate external write locks.
The table_lock_waits_summary_by_table table has these indexes
Unique index on (
OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME)
TRUNCATE TABLE is permitted for table lock summary tables. It resets the summary columns to zero rather than removing rows.