LOCK STATS

LOCK STATS 语句用于锁定表或分区的统计信息，使得在锁定期间，TiDB 不会自动更新统计信息。具体行为请参见锁定统计信息的行为说明。

语法图

LockStatsStmt

LOCK STATS - 图1

TableNameList

LOCK STATS - 图2

TableName

LOCK STATS - 图3

PartitionNameList

LOCK STATS - 图4

1. LockStatsStmt ::=
2. 'LOCK' 'STATS' (TableNameList) | (TableName 'PARTITION' PartitionNameList)

4. TableNameList ::=
5. TableName (',' TableName)*

7. TableName ::=
8. Identifier ( '.' Identifier )?

10. PartitionNameList ::=
11. Identifier ( ',' Identifier )*

示例

创建表 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, reason to use this rate is "Row count in stats_meta is much smaller compared with the row count got by PD, use min(1, 15000/4) as the sample-rate=1" |
16. +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17. 1 row in set (0.00 sec)

锁定表 t 的统计信息，执行 ANALYZE 语句，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, reason to use this rate is "use min(1, 110000/8) as the sample-rate=1" |
20. | Warning | 1105 | skip analyze locked table: test.t                                                                                                       |
21. +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
22. 2 rows in set (0.00 sec)

另外，你也可以通过 LOCK STATS 语句锁定分区的统计信息。用例如下：

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

1. mysql> CREATE TABLE t(a INT, b INT) PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30));
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, 6 warning (0.02 sec)

11. mysql> SHOW WARNINGS;
12. +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13. | Level   | Code | Message                                                                                                                                                                                                                              |
14. +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15. | Warning | 1105 | disable dynamic pruning due to t has no global stats                                                                                                                                                                                 |
16. | Note    | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p0, reason to use this rate is "Row count in stats_meta is much smaller compared with the row count got by PD, use min(1, 15000/4) as the sample-rate=1" |
17. | Warning | 1105 | disable dynamic pruning due to t has no global stats                                                                                                                                                                                 |
18. | Note    | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p1, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1"                                                                 |
19. | Warning | 1105 | disable dynamic pruning due to t has no global stats                                                                                                                                                                                 |
20. | Note    | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p2, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1"                                                                 |
21. +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22. 6 rows in set (0.01 sec)

锁定分区 p1 的统计信息，执行 ANALYZE 语句，warning 提示跳过对分区 p1 的 ANALYZE。

1. mysql> LOCK STATS t PARTITION p1;
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          | p1             | locked |
9. +---------+------------+----------------+--------+
10. 1 row in set (0.00 sec)

12. mysql> ANALYZE TABLE t PARTITION p1;
13. Query OK, 0 rows affected, 2 warnings (0.01 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's partition p1, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1" |
20. | Warning | 1105 | skip analyze locked table: test.t partition (p1)                                                                                                                     |
21. +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22. 2 rows in set (0.00 sec)

解锁统计信息请参考 UNLOCK STATS。

MySQL 兼容性

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

另请参阅