날짜별 행 크기 찾기

날짜별 행 크기 찾기

저는 지난 며칠 동안 테이블 크기의 변화를 파악하기 위해 조사를 좀 했습니다.날짜별로 테이블의 행 크기를 찾는 것이 가능한지 궁금합니다.예를 들어, 사이에2020-01-01그리고.2020-01-31.

|---------------------|------------------|
|      column_name    |     data_type    |
|---------------------|------------------|
|          id         | int(10) unsigned |
|---------------------|------------------|
|       created_at    |       date       |
|---------------------|------------------|
|      some column    |    varchar(255)  |
|---------------------|------------------|

아래 쿼리에 날짜 조건을 추가하는 것은 불가능할 것 같습니다.

SELECT
  table_name "Table Name",
  table_rows "Rows Count",
  round(((data_length + index_length) / 1024 / 1024), 2) "Table Size (MB)"
FROM
  information_schema.TABLES
WHERE
  table_schema = "DB NAME"
  AND table_name = "TABLE NAME";

MariaDB는 테이블과 인덱스 크기에 대한 기록을 가지고 있지 않습니다.파일 크기를 기록하는 운영 체제도 없는 것으로 알고 있습니다.이 작업은 일반적으로 모니터링 도구의 작업입니다.

이진 로그가 활성화된 경우(log-bin=1) 특정 테이블에 대한 타임스탬프와 함께 DML 문을 가져올 수 있어야 합니다.이진 로그는 다음과 같이 표시할 수 있습니다.mysqldump도구 또는 MariaDB Connector/C의 RPL API를 사용합니다.다음은 mysqdump의 출력 예입니다.

#200227 17:34:24 server id 1  end_log_pos 759 CRC32 0x7934578c  Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1582821264/*!*/;
insert into t1 (a,b) values (1, "foo")
/*!*/;

열 이름도 지정하는 보다 읽기 쉬운 SQL 구문을 사용하는 경우 추가 열을 추가할 수 있습니다.c타임스탬프를 기록하고 로그를 재생합니다.그러면 c에는 실제 날짜가 아닌 이진 로그(=12002821264)의 타임스탬프가 포함됩니다.

Georg의 말처럼 MySQL과 MariaDB는 테이블 통계 기록을 보유하지 않지만 사용자가 직접 시스템을 구축할 수 있습니다.

모든 테이블의 통계 기록이 저장된 테이블 만들기

각 날짜에 대해 모든 테이블에 대해 information_schema에서 사용할 수 있는 모든 크기/행 정보가 유지됩니다.

CREATE TABLE TB_STATS_HISTORY ( 
    DT DATE ,
    TB_SCHEMA VARCHAR(64),
    TB_NAME VARCHAR(64),
    TB_ROWS INT,
    TB_DATA_LENGTH INT,
    TB_INDEX_LENGTH INT,
    TB_AI INT,
    TB_SIZEKB INT
);

ALTER TABLE `TB_STATS_HISTORY` ADD PRIMARY KEY( `DT`, `TB_SCHEMA`, `TB_NAME`);

요청 시 통계를 수집하는 프로시저 생성

테이블이 있는 스키마(DB 이름만 해당)와 날짜를 전달하는 절차를 호출합니다.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Gather_Table_Stats` $$
CREATE PROCEDURE `Gather_Table_Stats`(arg_schema VARCHAR(64), arg_date DATE) 

BEGIN

DECLARE val_schema  VARCHAR(64);
DECLARE val_table   VARCHAR(64);
DECLARE val_rows    INT DEFAULT NULL;
DECLARE val_data    INT DEFAULT NULL;
DECLARE val_idx     INT DEFAULT NULL;
DECLARE val_ai      INT DEFAULT NULL;
DECLARE val_size    INT DEFAULT NULL;

DECLARE done        TINYINT DEFAULT FALSE;

DECLARE cursor1 
 CURSOR FOR
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    AUTO_INCREMENT,
    round(((data_length + index_length) / 1024 )) `SizeKB` 
FROM information_schema.TABLES 
WHERE table_schema = arg_schema AND TABLE_TYPE='BASE TABLE';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor1;

my_loop:
LOOP

  FETCH NEXT FROM cursor1 
    INTO val_schema, val_table, val_rows, val_data, val_idx, val_ai, val_size ;
  
  IF done THEN 
    LEAVE my_loop; 
  ELSE 
    INSERT INTO MTG.TB_STATS_HISTORY (DT, TB_SCHEMA, TB_NAME, TB_ROWS, TB_DATA_LENGTH , TB_INDEX_LENGTH, TB_AI, TB_SIZEKB)
    VALUES (arg_date, val_schema, val_table, val_rows, val_data, val_idx, val_ai, val_size);

  END IF;
END LOOP;

CLOSE cursor1;

END $$

DELIMITER ;

스케줄러 활성화

SET GLOBAL event_scheduler = ON;

프로세스를 확인하여 스케줄러가 실행 중인지 확인할 수 있습니다.

SHOW PROCESSLIST

사용자: event_scheduler를 사용하여 프로세스 하나를 출력해야 합니다.

하루에 한 번 절차를 호출하는 이벤트 추가

여기서 나는 오늘 정오에 처음 시작하고 매일 정오에 무한정 반복하는 이벤트를 만듭니다.

CREATE EVENT event_daily_stats
ON SCHEDULE EVERY 1 DAY
STARTS '2020-09-08 12:00:00'
DO
    CALL Gather_Table_Stats('your_schema',CURRENT_DATE);

완료됨

매일 정오에 모든 테이블의 통계 정보가 기록 테이블에 저장됩니다.

DT        |TB_SCHEMA  |TB_NAME  |TB_ROWS|TB_DATA_LENGTH |TB_INDEX_LENGTH|TB_AI  |TB_SIZEKB  
2020-09-08|your_schema|Table_1  |3287   |131072         |0              |NULL   |128
2020-09-08|your_schema|Table_2  |166    |29124          |4096           |251    |32
2020-09-08|your_schema|Table_3  |0      |16384          |98304          |1      |112

언급URL : https://stackoverflow.com/questions/60427522/find-the-size-of-the-rows-by-date