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