SHOW STATS_HEALTHY

The SHOW STATS_HEALTHY statement shows an estimation of how accurate statistics are believed to be. Tables with a low percentage health may generate sub-optimal query execution plans.

The health of a table can be improved by running the ANALYZE statement. ANALYZE runs automatically when the health drops below the tidb_auto_analyze_ratio threshold.

Currently, the SHOW STATS_HEALTHY statement returns the following columns:

Column nameDescription
Db_nameThe database name
Table_nameThe table name
Partition_nameThe partition name
HealthyThe healthy percentage between 0 and 100

Synopsis

ShowStatsHealthyStmt

SHOW STATS_HEALTHY - 图1

ShowLikeOrWhere

SHOW STATS_HEALTHY - 图2

  1. ShowStatsHealthyStmt ::=
  2.     "SHOW" "STATS_HEALTHY" ShowLikeOrWhere?
  4. ShowLikeOrWhere ::=
  5.     "LIKE" SimpleExpr
  6. |   "WHERE" Expression

Examples

Load example data and run ANALYZE:

  1. CREATE TABLE t1 (
  2.  id INT NOT NULL PRIMARY KEY auto_increment,
  3.  b INT NOT NULL,
  4.  pad VARBINARY(255),
  5.  INDEX(b)
  6. );
  8. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM dual;
  9. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  10. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  11. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  12. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  13. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  14. INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1000), RANDOM_BYTES(255) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 100000;
  15. SELECT SLEEP(1);
  16. ANALYZE TABLE t1;
  17. SHOW STATS_HEALTHY; # should be 100% healthy
  1. ...
  2. mysql> SHOW STATS_HEALTHY;
  3. +---------+------------+----------------+---------+
  4. | Db_name | Table_name | Partition_name | Healthy |
  5. +---------+------------+----------------+---------+
  6. | test    | t1         |                |     100 |
  7. +---------+------------+----------------+---------+
  8. 1 row in set (0.00 sec)

Perform a bulk update deleting approximately 30% of the records. Check the health of the statistics:

  1. DELETE FROM t1 WHERE id BETWEEN 101010 AND 201010; # delete about 30% of records
  2. SHOW STATS_HEALTHY;
  1. mysql> SHOW STATS_HEALTHY;
  2. +---------+------------+----------------+---------+
  3. | Db_name | Table_name | Partition_name | Healthy |
  4. +---------+------------+----------------+---------+
  5. | test    | t1         |                |      50 |
  6. +---------+------------+----------------+---------+
  7. 1 row in set (0.00 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also