分析表
描述
这个
ANALYZE TABLE
语句收集有关一个特定表或一个指定数据库中所有表的统计信息，这些信息将用于查询优化器，以找到更好的查询执行计划。
语法
ANALYZE TABLE table_identifier [ partition_spec ]
COMPUTE STATISTICS [ NOSCAN | FOR COLUMNS col [ , ... ] | FOR ALL COLUMNS ]
ANALYZE TABLES [ { FROM | IN } database_name ] COMPUTE STATISTICS [ NOSCAN ]
参数
-
table_identifier
指定一个表名，可以选择性地加上数据库名。
语法：
[ database_name. ] table_name -
partition_spec
一个可选参数，指定一个以逗号分隔的键值对列表用于分区。当指定时，将返回分区统计信息。
语法：
PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] ) -
{ FROM
|IN } database_name指定要分析的数据库名称。如果没有数据库名，
ANALYZE将收集当前用户有权限分析的当前数据库中的所有表。 -
NOSCAN
仅收集表的大小（以字节为单位），不需要扫描整个表。
-
FOR COLUMNS col [ , … ]
|FOR ALL COLUMNS为每个指定的列收集列统计信息，或者为每一列收集统计信息，以及表统计信息。
如果未指定分析选项，则会收集行数和字节大小。
示例
CREATE DATABASE school_db;
USE school_db;
CREATE TABLE teachers (name STRING, teacher_id INT);
INSERT INTO teachers VALUES ('Tom', 1), ('Jerry', 2);
CREATE TABLE students (name STRING, student_id INT) PARTITIONED BY (student_id);
INSERT INTO students VALUES (, 111111), (, 222222);
ANALYZE TABLE students COMPUTE STATISTICS NOSCAN;
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 字节| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLE students COMPUTE STATISTICS;
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 字节, 2 行| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLE students PARTITION (student_id = 111111) COMPUTE STATISTICS;
DESC EXTENDED students PARTITION (student_id = 111111);
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
|Partition Statistics| 432 字节, 1 行| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLE students COMPUTE STATISTICS FOR COLUMNS name;
DESC EXTENDED students name;
+--------------+----------+
| info_name|info_value|
+--------------+----------+
| col_name| name|
| data_type| string|
| comment| NULL|
| min| NULL|
| max| NULL|
| num_nulls| 0|
|distinct_count| 2|
| avg_col_len| 4|
| max_col_len| 4|
| histogram| NULL|
+--------------+----------+
ANALYZE TABLES IN school_db COMPUTE STATISTICS NOSCAN;
DESC EXTENDED teachers;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| teacher_id| int| null|
| ...| ...| ...|
| Statistics| 1382 字节| |
| ...| ...| ...|
+--------------------+--------------------+-------+
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 字节, 2 行| |
| ...| ...| ...|
+--------------------+--------------------+-------+