SHOW DATA SKEW
描述
该语句用于查看表或某个分区的数据倾斜情况。
语法：
SHOW DATA SKEW FROM [db_name.]tbl_name [PARTITION (partition_name, ...)];
说明：
- 结果将展示指定分区下，各个分桶的数据行数，数据量，以及每个分桶数据量在总数据量中的占比。
- 对于非分区表，查询结果中分区名称同表名。
示例
- 分区表场景
- 建表语句
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 % |
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
- 非分区表场景
- 建表语句
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 % |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
关键词
SHOW,DATA,SKEW
