Optimalkan Rencana Kueri dengan Mengumpulkan Statistik Tabel - MaxCompute

Mengumpulkan statistik metadata tabel untuk membantu mengoptimalkan kinerja query.

Sintaksis

Tabel non-partisi

analyze table <table_name> compute statistics for columns [(<col_name> [,<col_name> ...])];

Tabel partisi

analyze table <table_name> partition(<pt_spec>) compute statistics for columns (<column_name>[, <column_name>]);

Anda juga dapat menjalankan pernyataan berikut untuk menghapus statistik data di kolom tertentu atau semua kolom.

Hapus statistik data di semua kolom.

ANALYZE TABLE <table_name> DELETE STATISTICS FOR COLUMNS;

Hapus statistik data di kolom tertentu.

ANALYZE TABLE <table_name> DELETE STATISTICS FOR COLUMNS (column_name>[, <column_name>]);

Parameter

table_name: Nama tabel.
column_name: Nama kolom tempat data dikumpulkan untuk analisis statistik.
pt_spec: Partisi tempat data dikumpulkan untuk analisis statistik.

Contoh

Kumpulkan data dari kolom tertentu atau semua kolom tabel non-partisi dan lakukan analisis statistik.

-- Buat tabel non-partisi bernama analyze2_test.
create table if not exists analyze2_test
 (tinyint1 tinyint,
  smallint1 smallint,
  int1 int,
  bigint1 bigint,
  double1 double,
  decimal1 decimal,
  decimal2 decimal(20,10),
  string1 string,
  varchar1 varchar(10),
  boolean1 boolean,
  timestamp1 timestamp,
  datetime1 datetime )
 lifecycle 30;
-- Masukkan data ke dalam tabel.
insert overwrite table analyze2_test select * from values 
(1Y, 20S, 4, 8L, 123452.3, 12.4, 52.5, 'str1', 'str21', false, timestamp '2018-09-17 00:00:00', datetime '2018-09-17 00:59:59'),
(10Y, 2S, 7, 11111118L, 67892.3, 22.4, 42.5, 'str12', 'str200', true, timestamp '2018-09-17 00:00:00', datetime '2018-09-16 00:59:59'),
(20Y, 7S, 4, 2222228L, 12.3, 2.4, 2.57, 'str123', 'str2', false, timestamp '2018-09-18 00:00:00', datetime '2018-09-17 00:59:59'),
(null, null, null, null, null, null, null, null, null, null, null , null) 
as t(tinyint1, smallint1, int1, bigint1, double1, decimal1, decimal2, string1, varchar1, boolean1, timestamp1, datetime1);

-- Kumpulkan data di kolom tinyint1 tabel dan lakukan analisis statistik pada data tersebut.
analyze table analyze2_test compute statistics for columns (tinyint1); 

-- Kumpulkan data di kolom smallint1, string1, boolean1, dan timestamp1 tabel dan lakukan analisis statistik pada data tersebut.
analyze table analyze2_test compute statistics for columns (smallint1, string1, boolean1, timestamp1);

-- Kumpulkan data di semua kolom tabel dan lakukan analisis statistik pada data tersebut.
analyze table analyze2_test compute statistics for columns;

Jalankan pernyataan SHOW STATISTIC untuk memeriksa hasil statistik. Contoh pernyataan:

-- Uji hasil statistik kolom tinyint1. 
show statistic analyze2_test columns (tinyint1);

-- Uji hasil statistik kolom smallint1, string1, boolean1, dan timestamp1. 
show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);

-- Uji hasil statistik semua kolom. 
show statistic analyze2_test columns;

Hasil berikut akan ditampilkan:

-- Hasil statistik kolom tinyint1. 
ID = 20201126085225150gnqo****
tinyint1:MaxValue:      20                   -- Nilai maksimum. 
tinyint1:DistinctNum:   4.0                  -- Nilai ndv. 
tinyint1:MinValue:      1                    -- Nilai minimum. 
tinyint1:NullNum:       1.0                  -- Nilai nNulls. 
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      -- Nilai topK. 10=1.0 menunjukkan frekuensi kemunculan nilai kolom 10 adalah 1. Hingga 20 nilai dengan frekuensi kemunculan tertinggi dapat dikembalikan. 

-- Hasil statistik kolom smallint1, string1, boolean1, dan timestamp1. 
ID = 20201126091636149gxgf****
smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

string1:MaxLength       6.0                  -- Nilai maxColLen. 
string1:AvgLength:      3.0                  -- Nilai avgColLen. 
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

-- Hasil statistik semua kolom. 
ID = 20201126092022636gzm1****
tinyint1:MaxValue:      20
tinyint1:DistinctNum:   4.0
tinyint1:MinValue:      1
tinyint1:NullNum:       1.0
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}

smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

int1:MaxValue:  7
int1:DistinctNum:       3.0
int1:MinValue:  4
int1:NullNum:   1.0
int1:TopK:      {4=2.0, 7=1.0}

bigint1:MaxValue:       11111118
bigint1:DistinctNum:    4.0
bigint1:MinValue:       8
bigint1:NullNum:        1.0
bigint1:TopK:   {8=1.0, 2222228=1.0, 11111118=1.0}

double1:MaxValue:       123452.3
double1:DistinctNum:    4.0
double1:MinValue:       12.3
double1:NullNum:        1.0
double1:TopK:   {12.3=1.0, 67892.3=1.0, 123452.3=1.0}

decimal1:MaxValue:      22.4
decimal1:DistinctNum:   4.0
decimal1:MinValue:      2.4
decimal1:NullNum:       1.0
decimal1:TopK:  {2.4=1.0, 12.4=1.0, 22.4=1.0}

decimal2:MaxValue:      52.5
decimal2:DistinctNum:   4.0
decimal2:MinValue:      2.57
decimal2:NullNum:       1.0
decimal2:TopK:  {2.57=1.0, 42.5=1.0, 52.5=1.0}

string1:MaxLength       6.0
string1:AvgLength:      3.0
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

varchar1:MaxLength      6.0
varchar1:AvgLength:     3.0
varchar1:DistinctNum:   4.0
varchar1:NullNum:       1.0
varchar1:TopK:  {str2=1.0, str200=1.0, str21=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

datetime1:DistinctNum:  3.0
datetime1:NullNum:      1.0
datetime1:TopK:         {1537117199000=2.0, 1537030799000=1.0}

Kumpulkan data dari partisi tertentu tabel partisi dan lakukan analisis statistik.

-- Buat tabel partisi bernama srcpart_test.
create table if not exists srcpart_test 
  (key string,
    value string) 
partitioned by (ds string, hr string) 
lifecycle 30;

-- Masukkan data ke dalam tabel.
insert into table srcpart_test partition(ds='20201220', hr='11') values 
('123', 'val_123'), ('76', 'val_76'), ('447', 'val_447'), ('1234', 'val_1234');
insert into table srcpart_test partition(ds='20201220', hr='12') values
 ('3', 'val_3'), ('12331', 'val_12331'), ('42', 'val_42'), ('12', 'val_12');
insert into table srcpart_test partition(ds='20201221', hr='11') values
 ('543', 'val_543'), ('2', 'val_2'), ('4', 'val_4'), ('9', 'val_9');
insert into table srcpart_test partition(ds='20201221', hr='12') values
 ('23', 'val_23'), ('56', 'val_56'), ('4111', 'val_4111'), ('12333', 'val_12333');

-- Kumpulkan data dari partisi tertentu tabel dan lakukan analisis statistik.
analyze table srcpart_test partition(ds='20201221') compute statistics for columns (key , value);

Jalankan pernyataan SHOW STATISTIC untuk memeriksa hasil statistik. Contoh pernyataan:

show statistic srcpart_test partition (ds='20201221') columns (key , value);

Hasil berikut akan ditampilkan:

ID = 20210105121800689g28p****
(ds=20201221,hr=11) key:MaxLength       3.0
(ds=20201221,hr=11) key:AvgLength:      1.0
(ds=20201221,hr=11) key:DistinctNum:    4.0
(ds=20201221,hr=11) key:NullNum:        0.0
(ds=20201221,hr=11) key:TopK:   {2=1.0, 4=1.0, 543=1.0, 9=1.0}

(ds=20201221,hr=11) value:MaxLength     7.0
(ds=20201221,hr=11) value:AvgLength:    5.0
(ds=20201221,hr=11) value:DistinctNum:  4.0
(ds=20201221,hr=11) value:NullNum:      0.0
(ds=20201221,hr=11) value:TopK:         {val_2=1.0, val_4=1.0, val_543=1.0, val_9=1.0}

(ds=20201221,hr=12) key:MaxLength       5.0
(ds=20201221,hr=12) key:AvgLength:      3.0
(ds=20201221,hr=12) key:DistinctNum:    4.0
(ds=20201221,hr=12) key:NullNum:        0.0
(ds=20201221,hr=12) key:TopK:   {12333=1.0, 23=1.0, 4111=1.0, 56=1.0}

(ds=20201221,hr=12) value:MaxLength     9.0
(ds=20201221,hr=12) value:AvgLength:    7.0
(ds=20201221,hr=12) value:DistinctNum:  4.0
(ds=20201221,hr=12) value:NullNum:      0.0
(ds=20201221,hr=12) value:TopK:         {val_12333=1.0, val_23=1.0, val_4111=1.0, val_56=1.0}