[230817] mysql tips

mysql은 information_schema 를 이용한다.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'schema_name' -- 데이터베이스 이름
AND TABLE_NAME = 'table_name' -- 테이블 이름
AND REFERENCED_COLUMN_NAME IS NOT NULL; -- 외래 키 칼럼만 선택

• TABLE_NAME: 테이블 이름
• TABLE_ROWS: 테이블의 대략적인 행 수
• DATA_LENGTH: 테이블 데이터의 크기 (바이트)
• INDEX_LENGTH: 테이블 인덱스의 크기 (바이트)
• SIZE_MB: 테이블의 총 크기 (MB)
• AVG_ROWSIZEBYTES: 행당 평균 크기 (바이트)

SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / TABLE_ROWS), 2) AS AVG_ROW_SIZE_BYTES
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'table_name';

OCTET_LENGTH() 함수는 문자열의 실제 바이트 수를 반환하므로 다국어 문자열의 크기까지 반영.

SELECT AVG(OCTET_LENGTH(order_sheet_id)) AS 평균_바이트_수
FROM settlement.order_sheet;        

• TINYINT: 1 바이트
• SMALLINT: 2 바이트
• MEDIUMINT: 3 바이트
• INT (또는 INTEGER): 4 바이트
• BIGINT: 8 바이트
• FLOAT: 4 바이트
• DOUBLE: 8 바이트
• DECIMAL(M, D):자리수에 따라 다름. 일반적으로 M이 저장될 최대 숫자 자리수이고, 각각의 9자리마다 4바이트씩 사용 그리고 leftover 로 남은 자리수마다 바이트가 달라짐 0은 0바이트고 나머지는 두 자리당 1바이트가 추가됨. ex) 3~4 2바이트

문자열 타입

• CHAR(N): N 바이트 (고정 길이, UTF-8의 경우 문자당 최대 4바이트)
• VARCHAR(N): 최대 N 바이트 + 1 또는 2 바이트(문자열 길이 저장, UTF-8은 문자당 최대 4바이트)
• TEXT 타입:
• TINYTEXT: 최대 255 바이트
• TEXT: 최대 65,535 바이트 (64KB)
• MEDIUMTEXT: 최대 16,777,215 바이트 (16MB)
• LONGTEXT: 최대 4,294,967,295 바이트 (4GB)

BLOB 타입 (이진 데이터)

• TINYBLOB: 최대 255 바이트
• BLOB: 최대 65,535 바이트 (64KB)
• MEDIUMBLOB: 최대 16,777,215 바이트 (16MB)
• LONGBLOB: 최대 4,294,967,295 바이트 (4GB)

날짜 및 시간 타입

• DATE: 3 바이트
• TIME: 3 바이트
• DATETIME: 5 바이트
• TIMESTAMP: 4 바이트
• YEAR: 1 바이트