SHOW-DATA-SKEW

Name

SHOW DATA SKEW

Description

该语句用于查看表或某个分区的数据倾斜情况。
语法：
    SHOW DATA SKEW FROM [db_name.]tbl_name [PARTITION (partition_name, ...)];
说明：
    1. 结果将展示指定分区下，各个分桶的数据行数，数据量，以及每个分桶数据量在总数据量中的占比。
    2. 对于非分区表，查询结果中分区名称同表名。

Example

1. 分区表场景

• 建表语句

  CREATE TABLE test_show_data_skew
  (
    id int, 
    name string, 
    pdate date
  ) 
  PARTITION BY RANGE(pdate) 
  (
    FROM ("2023-04-16") TO ("2023-04-20") INTERVAL 1 DAY
  ) 
  DISTRIBUTED BY HASH(id) BUCKETS 5
  PROPERTIES (
    "replication_num" = "1"
  );

• 查询整表的数据倾斜情况

   mysql> SHOW DATA SKEW FROM test_show_data_skew;
   +---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
   | PartitionName | BucketIdx | AvgRowCount | AvgDataSize | Graph                                                                                                | Percent |
   +---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
   | p_20230416    | 0         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                                                    | 49.77 % |
   | p_20230416    | 1         | 2           | 654         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                                                   | 50.23 % |
   | p_20230416    | 2         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230416    | 3         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230416    | 4         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230417    | 0         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230417    | 1         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230417    | 2         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230417    | 3         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230417    | 4         | 2           | 656         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 100.00% |
   | p_20230418    | 0         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230418    | 1         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 100.00% |
   | p_20230418    | 2         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230418    | 3         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230418    | 4         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230419    | 0         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230419    | 1         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                                                    | 49.96 % |
   | p_20230419    | 2         | 0           | 0           |                                                                                                      | 00.00 % |
   | p_20230419    | 3         | 1           | 649         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                                                   | 50.04 % |
   | p_20230419    | 4         | 0           | 0           |                                                                                                      | 00.00 % |
   +---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+

• 查询指定分区的数据倾斜情况

  mysql> SHOW DATA SKEW FROM test_show_data_skew PARTITION(p_20230416, p_20230418);
  +---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
  | PartitionName | BucketIdx | AvgRowCount | AvgDataSize | Graph                                                                                                | Percent |
  +---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
  | p_20230416    | 0         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                                                    | 49.77 % |
  | p_20230416    | 1         | 2           | 654         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>                                                   | 50.23 % |
  | p_20230416    | 2         | 0           | 0           |                                                                                                      | 00.00 % |
  | p_20230416    | 3         | 0           | 0           |                                                                                                      | 00.00 % |
  | p_20230416    | 4         | 0           | 0           |                                                                                                      | 00.00 % |
  | p_20230418    | 0         | 0           | 0           |                                                                                                      | 00.00 % |
  | p_20230418    | 1         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 100.00% |
  | p_20230418    | 2         | 0           | 0           |                                                                                                      | 00.00 % |
  | p_20230418    | 3         | 0           | 0           |                                                                                                      | 00.00 % |
  | p_20230418    | 4         | 0           | 0           |                                                                                                      | 00.00 % |
  +---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+

1. 非分区表场景

• 建表语句

  CREATE TABLE test_show_data_skew2
  (
      id int, 
      name string, 
      pdate date
  ) 
  DISTRIBUTED BY HASH(id) BUCKETS 5
  PROPERTIES (
      "replication_num" = "1"
  );

• 查询整表的数据倾斜情况

  mysql> SHOW DATA SKEW FROM test_show_data_skew2;
  +----------------------+-----------+-------------+-------------+---------------------------+---------+
  | PartitionName        | BucketIdx | AvgRowCount | AvgDataSize | Graph                     | Percent |
  +----------------------+-----------+-------------+-------------+---------------------------+---------+
  | test_show_data_skew2 | 0         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>  | 24.73 % |
  | test_show_data_skew2 | 1         | 4           | 667         | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.46 % |
  | test_show_data_skew2 | 2         | 0           | 0           |                           | 00.00 % |
  | test_show_data_skew2 | 3         | 1           | 649         | >>>>>>>>>>>>>>>>>>>>>>>>  | 24.77 % |
  | test_show_data_skew2 | 4         | 2           | 656         | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.04 % |
  +----------------------+-----------+-------------+-------------+---------------------------+---------+

mysql> SHOW DATA SKEW FROM test_show_data_skew2 PARTITION(test_show_data_skew2);
+----------------------+-----------+-------------+-------------+---------------------------+---------+
| PartitionName        | BucketIdx | AvgRowCount | AvgDataSize | Graph                     | Percent |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
| test_show_data_skew2 | 0         | 1           | 648         | >>>>>>>>>>>>>>>>>>>>>>>>  | 24.73 % |
| test_show_data_skew2 | 1         | 4           | 667         | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.46 % |
| test_show_data_skew2 | 2         | 0           | 0           |                           | 00.00 % |
| test_show_data_skew2 | 3         | 1           | 649         | >>>>>>>>>>>>>>>>>>>>>>>>  | 24.77 % |
| test_show_data_skew2 | 4         | 2           | 656         | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.04 % |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
```

Keywords

SHOW,DATA,SKEW

Best Practice