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

3. 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)

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)

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

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)

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

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 兼容性

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

另请参阅