mariadb,mysql partition table 조회

- 파티션 테이블 리스트 조회

SELECT distinct TABLE_NAME

FROM INFORMATION_SCHEMA.PARTITIONS

where partition_name is not null ;

 

- 파티션 테이블별 용량 조회

SELECT TABLE_SCHEMA, TABLE_NAME,
            PARTITION_NAME,PARTITION_DESCRIPTION, TABLE_ROWS,
            ROUND(DATA_LENGTH/(1024*1024),2) AS 'DATA_SIZE(MB)',
            ROUND(INDEX_LENGTH/(1024*1024),2) AS 'INDEX_SIZE(MB)'           
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE PARTITION_NAME IS NOT NULL
AND TABLE_SCHEMA = 'DB명'
#AND TABLE_NAME = '파티션 테이블명';

 

- 파티션 테이블 조회

SELECT * FROM test PARTITION (p20210401);