SHOW-QUERY-STATS

Name

SinceVersion devSHOW QUERY STATS

Description

该语句用于展示数据库中历史查询命中的库表列的情况

1. SHOW QUERY STATS [[FOR db_name]|[FROM table_name]] [ALL] [VERBOSE]];

说明：

  1. 支持查询数据库和表的历史查询命中情况，重启fe后数据会重置，每个fe单独统计
  2. 通过 FOR DATABASE 和FROM TABLE 可以指定查询数据库或者表的命中情况，后面分别接数据库名或者表名
  3. ALL 可以指定是否展示所有index的查询命中情况，VERBOSE 可以展示更详细的命中情况， 这两个参数可以单独使用， 也可以一起使用，但是必须放在最后 而且只能用在表的查询上
  4. 如果没有 use 任何数据库那么直接执行SHOW QUERY STATS 将展示所有数据库的命中情况
  5. 命中结果中可能有两列： QueryCount：该列被查询次数 FilterCount: 该列作为where 条件被查询的次数

Example

  1. 展示表baseall 的查询命中情况

``` 1. MySQL [test_query_db]> show query stats from baseall; 2. +-------+------------+-------------+ 3. | Field | QueryCount | FilterCount | 4. +-------+------------+-------------+ 5. | k0 | 0 | 0 | 6. | k1 | 0 | 0 | 7. | k2 | 0 | 0 | 8. | k3 | 0 | 0 | 9. | k4 | 0 | 0 | 10. | k5 | 0 | 0 | 11. | k6 | 0 | 0 | 12. | k10 | 0 | 0 | 13. | k11 | 0 | 0 | 14. | k7 | 0 | 0 | 15. | k8 | 0 | 0 | 16. | k9 | 0 | 0 | 17. | k12 | 0 | 0 | 18. | k13 | 0 | 0 | 19. +-------+------------+-------------+ 20. 14 rows in set (0.002 sec)

  1. MySQL [test_query_db]> select k0, k1,k2, sum(k3) from baseall where k9 > 1 group by k0,k1,k2;
  2. +------+------+--------+-------------+
  3. | k0 | k1 | k2 | sum(k3) |
  4. +------+------+--------+-------------+
  5. | 0 | 6 | 32767 | 3021 |
  6. | 1 | 12 | 32767 | -2147483647 |
  7. | 0 | 3 | 1989 | 1002 |
  8. | 0 | 7 | -32767 | 1002 |
  9. | 1 | 8 | 255 | 2147483647 |
  10. | 1 | 9 | 1991 | -2147483647 |
  11. | 1 | 11 | 1989 | 25699 |
  12. | 1 | 13 | -32767 | 2147483647 |
  13. | 1 | 14 | 255 | 103 |
  14. | 0 | 1 | 1989 | 1001 |
  15. | 0 | 2 | 1986 | 1001 |
  16. | 1 | 15 | 1992 | 3021 |
  17. +------+------+--------+-------------+

  18. 12 rows in set (0.050 sec)

  19. MySQL [test_query_db]> show query stats from baseall;

  20. +-------+------------+-------------+
  21. | Field | QueryCount | FilterCount |
  22. +-------+------------+-------------+
  23. | k0 | 1 | 0 |
  24. | k1 | 1 | 0 |
  25. | k2 | 1 | 0 |
  26. | k3 | 1 | 0 |
  27. | k4 | 0 | 0 |
  28. | k5 | 0 | 0 |
  29. | k6 | 0 | 0 |
  30. | k10 | 0 | 0 |
  31. | k11 | 0 | 0 |
  32. | k7 | 0 | 0 |
  33. | k8 | 0 | 0 |
  34. | k9 | 1 | 1 |
  35. | k12 | 0 | 0 |
  36. | k13 | 0 | 0 |
  37. +-------+------------+-------------+
  38. 14 rows in set (0.001 sec) ```
  39. 展示表的所物化视图的的命中的汇总情况

1. MySQL [test_query_db]> show query stats from baseall all; 2. +-----------+------------+ 3. | IndexName | QueryCount | 4. +-----------+------------+ 5. | baseall | 1 | 6. +-----------+------------+ 7. 1 row in set (0.005 sec) 3. 展示表的所物化视图的的命中的详细情况

1. MySQL [test_query_db]> show query stats from baseall all verbose; 2. +-----------+-------+------------+-------------+ 3. | IndexName | Field | QueryCount | FilterCount | 4. +-----------+-------+------------+-------------+ 5. | baseall | k0 | 1 | 0 | 6. | | k1 | 1 | 0 | 7. | | k2 | 1 | 0 | 8. | | k3 | 1 | 0 | 9. | | k4 | 0 | 0 | 10. | | k5 | 0 | 0 | 11. | | k6 | 0 | 0 | 12. | | k10 | 0 | 0 | 13. | | k11 | 0 | 0 | 14. | | k7 | 0 | 0 | 15. | | k8 | 0 | 0 | 16. | | k9 | 1 | 1 | 17. | | k12 | 0 | 0 | 18. | | k13 | 0 | 0 | 19. +-----------+-------+------------+-------------+ 20. 14 rows in set (0.017 sec) 4. 展示数据库的命中情况

1. MySQL [test_query_db]> show query stats for test_query_db; 2. +----------------------------+------------+ 3. | TableName | QueryCount | 4. +----------------------------+------------+ 5. | compaction_tbl | 0 | 6. | bigtable | 0 | 7. | empty | 0 | 8. | tempbaseall | 0 | 9. | test | 0 | 10. | test_data_type | 0 | 11. | test_string_function_field | 0 | 12. | baseall | 1 | 13. | nullable | 0 | 14. +----------------------------+------------+ 15. 9 rows in set (0.005 sec) 5. 展示所有数据库的命中情况，这时不能use 任何数据库

1. MySQL [(none)]> show query stats; 2. +-----------------+------------+ 3. | Database | QueryCount | 4. +-----------------+------------+ 5. | test_query_db | 1 | 6. +-----------------+------------+ 7. 1 rows in set (0.005 sec)

SHOW QUERY STATS;

```

```

Keywords

1. SHOW， QUERY, STATS;

Best Practice