SHOW STATS_LOCKED

SHOW STATS_LOCKED 语句显示统计信息被锁定的表。

SHOW STATS_LOCKED - 图1

警告

锁定统计信息目前为实验特性,不建议在生产环境中使用。

语法图

ShowStatsLockedStmt

SHOW STATS_LOCKED - 图2

ShowLikeOrWhereOpt

SHOW STATS_LOCKED - 图3

  1. ShowStatsLockedStmt ::= 'SHOW' 'STATS_LOCKED' ShowLikeOrWhereOpt
  2. ShowLikeOrWhereOpt ::= 'LIKE' SimpleExpr | 'WHERE' Expression

示例

创建表 t,插入一些数据,在未锁定表 t 的统计信息的情况下成功执行 ANALYZE 语句。

  1. mysql> create table t(a int, b int);
  2. Query OK, 0 rows affected (0.03 sec)
  3. mysql> insert into t values (1,2), (3,4), (5,6), (7,8);
  4. Query OK, 4 rows affected (0.00 sec)
  5. Records: 4 Duplicates: 0 Warnings: 0
  6. mysql> analyze table t;
  7. Query OK, 0 rows affected, 1 warning (0.02 sec)
  8. mysql> show warnings;
  9. +-------+------+-----------------------------------------------------------------+
  10. | Level | Code | Message |
  11. +-------+------+-----------------------------------------------------------------+
  12. | Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t |
  13. +-------+------+-----------------------------------------------------------------+
  14. 1 row in set (0.00 sec)

锁定表 t 的统计信息,执行 ANALYZE 语句,使用 SHOW STATS_LOCKED 可查看表 t 的统计信息已被锁定。warning 提示跳过对表 tANALYZE

  1. mysql> lock stats t;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> show stats_locked;
  4. +---------+------------+----------------+--------+
  5. | Db_name | Table_name | Partition_name | Status |
  6. +---------+------------+----------------+--------+
  7. | test | t | | locked |
  8. +---------+------------+----------------+--------+
  9. 1 row in set (0.01 sec)
  10. mysql> analyze table t;
  11. Query OK, 0 rows affected, 2 warnings (0.00 sec)
  12. mysql> show warnings;
  13. +---------+------+-----------------------------------------------------------------+
  14. | Level | Code | Message |
  15. +---------+------+-----------------------------------------------------------------+
  16. | Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t |
  17. | Warning | 1105 | skip analyze locked table: t |
  18. +---------+------+-----------------------------------------------------------------+
  19. 2 rows in set (0.00 sec)

解锁表 t 的统计信息,成功执行 ANALYZE 语句。

  1. mysql> unlock stats t;
  2. Query OK, 0 rows affected (0.01 sec)
  3. mysql> analyze table t;
  4. Query OK, 0 rows affected, 1 warning (0.03 sec)
  5. mysql> show warnings;
  6. +-------+------+-----------------------------------------------------------------+
  7. | Level | Code | Message |
  8. +-------+------+-----------------------------------------------------------------+
  9. | Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t |
  10. +-------+------+-----------------------------------------------------------------+
  11. 1 row in set (0.00 sec)

MySQL 兼容性

该语句是 TiDB 对 MySQL 语法的扩展。

另请参阅