prepared_statements_instances

performance_schema提供了针对prepare语句的instruments，prepare语句支持两个协议，如下：

• 1、二进制协议。提供MySQL C API方式访问，访问时相关函数的访问被映射到底层server命令，映射表如下所示：

C API函数	对应的server命令
mysql_stmt_prepare()	COM_STMT_PREPARE
mysql_stmt_execute()	COM_STMT_EXECUTE
mysql_stmt_close()	COM_STMT_CLOSE

• 2、文本协议。提供以SQL语句方式访问，并把相关的SQL语句映射到底层server命令，映射表如下所示：

SQL语句	相应的server命令
PREPARE	SQLCOM_PREPARE
EXECUTE	SQLCOM_EXECUTE
DEALLOCATE PREPARE，DROP PREPARE	SQLCOM_DEALLOCATE PREPARE

• 当有prepare语句执行时，prepare语句相关的信息可以在prepared_statements_instances表中查询。一个prepare对应一行记录，且每个prepare语句被调用一次就会进行一次聚合统计，所以，可以使用此表来统计server中正在使用的所有prepare语句，并提供有关它们的统计信息。要控制此表的大小，可以在server启动之前设置系统变量performance_schema_max_prepared_statements_instances的值。注意：整个实例中最大prepare语句数量由系统参数max_prepared_stmt_count控制，默认为16382，如果实例中的prepare数量超过了这个系统变量的值，则后续的prepare语句无法创建。
• performance_schema使用如下一些instruments来对prepare语句的statements事件信息进行收集。这些instruments默认开启。可以使用setup_instruments表来进行配置修改

instruments	server命令
statement/com/Prepare	COM_STMT_PREPARE
statement/com/Execute	COM_STMT_EXECUTE
statement/sql/prepare_sql	SQLCOM_PREPARE
statement/sql/execute_sql	SQLCOM_EXECUTE

• performance_schema如何管理prepare_statements_instances表中的内容：

  • prepare语句预编译：COM_STMT_PREPARE或SQLCOM_PREPARE命令在server中创建一个prepare语句。如果语句检测成功，则会在prepared_statements_instances表中新添加一行。如果prepare语句无法检测，则会增加Performance_schema_prepared_statements_lost状态变量的值
  • prepare语句执行：为已检测的prepare语句实例执行COM_STMT_EXECUTE或SQLCOM_PREPARE命令，同时会更新prepare_statements_instances表中对应的行信息
  • prepare语句解除资源分配：对已检测的prepare语句实例执行COM_STMT_CLOSE或SQLCOM_DEALLOCATE_PREPARE命令，同时将删除prepare_statements_instances表中对应的行信息。为了避免资源泄漏，请务必在prepare语句不需要使用的时候执行此步骤释放资源

• prepared_statements_instances表字段含义如下：

  • OBJECT_INSTANCE_BEGIN：prepare语句事件的instruments 实例内存地址
  • STATEMENT_ID：由server分配的语句内部ID。文本和二进制协议都使用该语句ID
  • STATEMENT_NAME：对于二进制协议的语句事件，此列值为NULL。对于文本协议的语句事件，此列值是用户分配的外部语句名称。例如：PREPARE stmt FROM'SELECT 1';，语句名称为stmt
  • SQL_TEXT：prepare的语句文本，带？的表示是占位符标记，后续execute语句可以对该标记进行传参
  • OWNER_THREAD_ID，OWNER_EVENT_ID：这些列表示创建prepare语句的线程ID和事件ID
  • OWNER_OBJECT_TYPE，OWNER_OBJECT_SCHEMA，OWNER_OBJECT_NAME：对于由客户端会话使用SQL语句直接创建的prepare语句，这些列值为NULL。对于由存储程序创建的prepare语句，这些列值显示相关存储程序的信息。如果用户在存储程序中忘记释放prepare语句，那么这些列可用于查找这些未释放的prepare对应的存储程序，使用语句查询：SELECT OWNER_OBJECT_TYPE,OWNER_OBJECT_SCHEMA,OWNER_OBJECT_NAME,STATEMENT_NAME,SQL_TEXT FROM performance_schema.prepared_statemments_instances WHERE OWNER_OBJECT_TYPE IS NOT NULL;
  • TIMER_PREPARE：执行prepare语句本身消耗的时间
  • COUNT_REPREPARE：该行信息对应的prepare语句在内部被重新编译的次数（详见链接：https://dev.mysql.com/doc/refman/5.7/en/statement-caching.html），重新编译prepare语句之后，之前的相关统计信息就不可用了，因为这些统计信息是作为语句执行的一部分被聚合到表中的，而不是单独维护的
  • COUNT_EXECUTE，SUM_TIMER_EXECUTE，MIN_TIMER_EXECUTE，AVG_TIMER_EXECUTE，MAX_TIMER_EXECUTE：执行prepare语句时的相关汇总统计数据
  • SUM_xxx：其余的SUM_xxx开头的列与语句摘要表中的信息相同，语句摘要表后续章节会详细介绍

• 允许执行TRUNCATE TABLE语句，但是TRUNCATE TABLE只是重置prepared_statements_instances表的统计信息列，但是不会删除该表中的记录，该表中的记录会在prepare对象被销毁释放的时候自动删除

• PS：什么是prepare语句？prepare语句实际上就是一个预编译语句，先把SQL语句进行编译，且可以设定参数占位符（例如：?符号），然后调用时通过用户变量传入具体的参数值（叫做变量绑定），如果一个语句需要多次执行而仅仅只是where条件不同，那么使用prepare语句可以大大减少硬解析的开销，prepare语句有三个步骤，预编译prepare语句，执行prepare语句，释放销毁prepare语句，prepare语句支持两种协议，前面已经提到过了，binary协议一般是提供给应用程序的mysql c api接口方式访问，而文本协议提供给通过客户端连接到mysql server的方式访问，下面以文本协议的方式访问进行演示说明：

  • prepare步骤：语法PREPARE stmt_name FROM preparable_stmt，示例：PREPARE stmt FROM'SELECT 1'; 执行了该语句之后，在prepared_statements_instances表中就可以查询到一个prepare示例对象了
  • execute步骤：语法EXECUTE stmt_name[USING @var_name [, @var_name] ...]，示例：execute stmt; 返回执行结果为1，此时在prepared_statements_instances表中的统计信息会进行更新
  • DEALLOCATE PREPARE步骤：语法 {DEALLOCATE | DROP} PREPARE stmt_name，示例：drop prepare stmt; ，此时在prepared_statements_instances表中对应的prepare示例记录自动删除
  • 详见参考链接：https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

• 表记录内容示例

admin@localhost : performance_schema 10:50:38> select * from prepared_statements_instances\G;
*************************** 1. row ***************************
      OBJECT_INSTANCE_BEGIN: 139968890586816
              STATEMENT_ID: 1
            STATEMENT_NAME: stmt
                  SQL_TEXT: SELECT 1
            OWNER_THREAD_ID: 48
            OWNER_EVENT_ID: 54
          OWNER_OBJECT_TYPE: NULL
        OWNER_OBJECT_SCHEMA: NULL
          OWNER_OBJECT_NAME: NULL
              TIMER_PREPARE: 896167000
            COUNT_REPREPARE: 0
              COUNT_EXECUTE: 0
          SUM_TIMER_EXECUTE: 0
          MIN_TIMER_EXECUTE: 0
          AVG_TIMER_EXECUTE: 0
          MAX_TIMER_EXECUTE: 0
              SUM_LOCK_TIME: 0
                SUM_ERRORS: 0
              SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
    SUM_CREATED_TMP_TABLES: 0
      SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
          SUM_SELECT_RANGE: 0
    SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
            SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
    SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

• 表定义语句

CREATE TABLE `prepared_statements_instances` (
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `STATEMENT_ID` bigint(20) unsigned NOT NULL,
  `STATEMENT_NAME` varchar(64) DEFAULT NULL,
  `SQL_TEXT` longtext NOT NULL,
  `OWNER_THREAD_ID` bigint(20) unsigned NOT NULL,
  `OWNER_EVENT_ID` bigint(20) unsigned NOT NULL,
  `OWNER_OBJECT_TYPE` enum('EVENT','FUNCTION','PROCEDURE','TABLE','TRIGGER') DEFAULT NULL,
  `OWNER_OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OWNER_OBJECT_NAME` varchar(64) DEFAULT NULL,
  `TIMER_PREPARE` bigint(20) unsigned NOT NULL,
  `COUNT_REPREPARE` bigint(20) unsigned NOT NULL,
  `COUNT_EXECUTE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL,
  `SUM_ERRORS` bigint(20) unsigned NOT NULL,
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL,
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL,
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL,
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

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

---

上一篇： events_statements_history_long表 | 下一篇： transacton tables