LOCK STATS

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

语法图

LockStatsStmt
LOCKSTATSTableNameListTableNamePARTITIONPartitionNameList
TableNameList
TableName,
TableName
Identifier.Identifier
PartitionNameList
Identifier,

示例

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

mysql> CREATE TABLE t(a INT, b INT); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t VALUES (1,2), (3,4), (5,6), (7,8); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> ANALYZE TABLE t; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> SHOW WARNINGS; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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" | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

锁定表 t 的统计信息,执行 ANALYZE 语句,warning 提示跳过对表 tANALYZE

mysql> LOCK STATS t; Query OK, 0 rows affected (0.00 sec) mysql> SHOW STATS_LOCKED; +---------+------------+----------------+--------+ | Db_name | Table_name | Partition_name | Status | +---------+------------+----------------+--------+ | test | t | | locked | +---------+------------+----------------+--------+ 1 row in set (0.01 sec) mysql> ANALYZE TABLE t; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ | 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" | | Warning | 1105 | skip analyze locked table: test.t | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

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

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

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)); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t VALUES (1,2), (3,4), (5,6), (7,8); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> ANALYZE TABLE t; Query OK, 0 rows affected, 6 warning (0.02 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1105 | disable dynamic pruning due to t has no global stats | | 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" | | Warning | 1105 | disable dynamic pruning due to t has no global stats | | 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" | | Warning | 1105 | disable dynamic pruning due to t has no global stats | | 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" | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.01 sec)

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

mysql> LOCK STATS t PARTITION p1; Query OK, 0 rows affected (0.00 sec) mysql> SHOW STATS_LOCKED; +---------+------------+----------------+--------+ | Db_name | Table_name | Partition_name | Status | +---------+------------+----------------+--------+ | test | t | p1 | locked | +---------+------------+----------------+--------+ 1 row in set (0.00 sec) mysql> ANALYZE TABLE t PARTITION p1; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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" | | Warning | 1105 | skip analyze locked table: test.t partition (p1) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

解锁统计信息请参考 UNLOCK STATS

MySQL 兼容性

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

另请参阅