table_handles

• performance_schema通过table_handles表记录表锁信息，以对当前每个打开的表所持有的表锁进行追踪记录。table_handles输出表锁instruments采集的内容。这些信息显示server中已打开了哪些表，锁定方式是什么以及被哪个会话持有

• table_handles表是只读的，不能更新。默认自动调整表数据行大小，如果要显式指定个，可以在server启动之前设置系统变量performance_schema_max_table_handles的值

• 对应的instruments为wait/io/table/sql/handler和wait/lock/table/sql/handler，默认开启

• table_handles表字段含义如下：

  • OBJECT_TYPE：显示handles锁的类型，表示该表是被哪个table handles打开的
  • OBJECT_SCHEMA：该锁来自于哪个库级别的对象
  • OBJECT_NAME：instruments对象的名称，表级别对象
  • OBJECT_INSTANCE_BEGIN：instruments对象的内存地址
  • OWNER_THREAD_ID：持有该handles锁的线程ID
  • OWNER_EVENT_ID：触发table handles被打开的事件ID，即持有该handles锁的事件ID
  • INTERNAL_LOCK：在SQL级别使用的表锁。有效值为：READ、READ WITH SHARED LOCKS、READ HIGH PRIORITY、READ NO INSERT、WRITE ALLOW WRITE、WRITE CONCURRENT INSERT、WRITE LOW PRIORITY、WRITE。有关这些锁类型的详细信息，请参阅include/thr_lock.h源文件
  • EXTERNAL_LOCK：在存储引擎级别使用的表锁。有效值为：READ EXTERNAL、WRITE EXTERNAL

• table_handles表不允许使用TRUNCATE TABLE语句

• 表记录内容示例

admin@localhost : performance_schema 05:47:55> select * from table_handles;
+-------------+---------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+-------------+---------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
| TABLE      | xiaoboluo    | test        |      140568038528544 |              0 |              0 | NULL          | NULL          |
+-------------+---------------+-------------+-----------------------+-----------------+----------------+---------------+---------------+
1 row in set (0.00 sec)

• 表结构定义

CREATE TABLE `table_handles` (
  `OBJECT_TYPE` varchar(64) NOT NULL,
  `OBJECT_SCHEMA` varchar(64) NOT NULL,
  `OBJECT_NAME` varchar(64) NOT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `OWNER_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `OWNER_EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  `INTERNAL_LOCK` varchar(64) DEFAULT NULL,
  `EXTERNAL_LOCK` varchar(64) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/table-handles-table.html

---

上一篇： metadata_locks表 | 下一篇： global_variables与session_variables表