SHOW STATS_LOCKED

SHOW STATS_LOCKED shows the tables whose statistics are locked.

SHOW STATS_LOCKED - 图1

Warning

Locking statistics is an experimental feature for the current version. It is not recommended to use it in the production environment.

Synopsis

ShowStatsLockedStmt

SHOW STATS_LOCKED - 图2

ShowLikeOrWhereOpt

SHOW STATS_LOCKED - 图3

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

Examples

Create table t, and insert data into it. When the statistics of table t are not locked, the ANALYZE statement can be successfully executed.

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

Lock the statistics of table t and execute ANALYZE. From the output of SHOW STATS_LOCKED, you can see that the statistics of table t have been locked. The warning message shows that the ANALYZE statement has skipped table t.

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

Unlock the statistics of table t and ANALYZE can be successfully executed again.

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

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also