跳到主要内容
跳到主要内容

SHOW-DATA-SKEW

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