DBeaver 설치 / 무료 데이터베이스 관리 툴 DBeaver 사용법

2023-02-16

DBeaver 설치

DBeaver 사용법

DB 연결 생성

좌측 Database Navigator 상단 새 데이터베이스 연결 아이콘 > DB 종류 선택 > 다음 >

Server Host	(DB서버명).ck39dp9w2h39.ap-northeast-2.rds.amazonaws.com
Database	DB명 (필수 아님)
Username	계정명
Password	암호

위 예시 참고하여 DB 정보 입력 > Test Connection 시 Connected 나오면 > 확인

DB 버전 확인
DB 연결 우클릭 > Edit Connection > Test Connection > DB 서버 버전 및 드라이버 확인

테이블명 검색 방법

좌측 Database Navigator 상단 인풋에 테이블명 입력 시 하단에 필터링 됩니다.

실행계획 보는 방법

쿼리 앞에 explain을 붙이고 실행하면 아래 Results에 해당 쿼리의 실행계획이 그리드 형태로 나옵니다.
DELETE 문은 실행계획이 정상적으로 나오지 않으므로, DELETE를 SELECT *으로 변경 후 조회하는 것이 좋습니다.

실행계획 테이블 컬럼 설명

컬럼명	설명
id	각 단계의 고유 식별자이며, 실행 순서를 오름차순으로 나타냅니다. (낮을수록 먼저 실행)
select_type	각 단계의 쿼리 유형입니다. SIMPLE : 하위 쿼리나 UNION이 없는 단순 SELECT 문 PRIMARY : 가장 바깥쪽의 주 SELECT 문 (쿼리의 루트) UNION : UNION 연산에서 두 번째 이후의 SELECT 문 DEPENDENT UNION : UNION 연산에서 상위 쿼리에 의존하는 SELECT 문 UNION RESULT : UNION의 결과를 가져오는 SELECT 문 SUBQUERY : SELECT, WHERE, HAVING 절 내의 서브쿼리 DEPENDENT SUBQUERY : 상위 쿼리의 값에 의존하여 실행되는 서브쿼리 (외부 쿼리의 각 행마다 실행) DERIVED : FROM 절에 서브쿼리를 사용하는 파생 테이블 MATERIALIZED : 임시 테이블을 사용하여 결과를 캐싱하는 서브쿼리 DELETE : 테이블 데이터를 삭제하는 DELETE 문 (다른 유형으로 표시될 수 있음)
table ★	각 단계에서 접근하는 테이블명입니다. 하위 쿼리나 파생 테이블이면 고유 식별자를 표시합니다.
partitions	각 단계에서 사용된 파티션입니다. 파티셔닝 된 테이블이면 접근한 파티션 목록을 표시합니다.
type ★	각 단계의 테이블 접근 방식을 나타내며, 쿼리 성능을 평가하는 중요한 지표입니다. system : 한 행만 있는 테이블 스캔 (가장 효율적) const : 기본키나 유니크 인덱스를 사용하여 상수처럼 취급되는 한 행 스캔 eq_ref : 조인에서 기본 키나 유니크 인덱스를 사용하여 한 행 스캔 ref : 비유니크 인덱스를 사용하여 특정 값과 일치하는 여러 행 스캔 ref_or_null : NULL 비교도 수행하는 ref unique_subquery : 서브쿼리에서 고유한 값으로 한 행 스캔 index_subquery : 서브쿼리에서 인덱스를 사용하여 스캔 range : 인덱스를 사용하여 특정 범위 스캔 (BETWEEN, <, >, IN 조건) index : 인덱스를 사용하여 테이블 풀스캔 ALL : 테이블 풀스캔 (가장 비효율적)
possible_keys	각 단계에서 사용 가능한 인덱스 후보 리스트입니다. 옵티마이저가 가장 적합한 인덱스를 선택하여 쿼리를 실행합니다.
key ★	각 단계에서 실제 사용된 인덱스입니다. PRIMARY : PK 인덱스가 사용되었음
key_len	각 단계에서 사용된 인덱스의 길이를 바이트 수로 나타냅니다. (길수록 세밀한 인덱스 사용)
ref	인덱스를 참조하는데 사용되는 컬럼명이나 표현식입니다. const : 상수 값과 비교하여 특정 행 선택 eq_ref : 유니크 인덱스를 사용하여 조인이나 서브쿼리 수행 ref : 비유니크 인덱스를 사용하여 조인이나 서브쿼리 수행 range : 인덱스 범위를 사용하여 특정 범위의 행 선택 index : 인덱스 전체 또는 부분적으로 스캔하여 행 선택 ALL : 전체 테이블을 스캔하여 조건에 맞는 행 선택 func : 인덱스 조회 시 함수가 사용되어 인덱스 효율 저하
rows ★	각 단계에서 조회되는 예상 행 수입니다. 클수록 비효율적, 작을수록 효율적입니다. WHERE 절에 사용되는 컬럼에 인덱스 추가하면 rows 값을 줄일 수 있습니다.
filtered ★	스캔된 행에서 조건에 의해 필터링된 행의 백분율입니다. 100에 가까울수록 필요한 행만 가져온 것이라 성능이 좋습니다.
Extra	쿼리 실행계획에서 제공하는 부가적인 정보입니다. Using filesort : 결과를 정렬하기 위해 파일 정렬이 수행됨 Using temporary : 임시 테이블이 사용됨 Using index : 실제 테이블 접근 없이, 커버링 인덱스를 통해 데이터를 가져옴 Using where : WHERE 절에서 필터링이 수행됨 Using join buffer : 조인 버퍼를 사용하여 조인이 수행됨 Select tables optimized away : 최적화된 쿼리로 인해 테이블이 선택되지 않음 Impossible WHERE noticed after reading const tables : WHERE 조건이 상수 테이블을 읽은 후에 발견됨

DBeaver 단축키

쿼리 실행	Ctrl + Enter
다중 쿼리 일괄 실행	Ctrl + Alt + Shift + X
창 닫기	Ctrl + w
전체 창 닫기	Shift + Ctrl + w
쿼리 포맷팅 (코드 정리)	Ctrl + Shift + F
DB 연결 수정 팝업 열기	DB 연결 클릭 > F4

DBeaver 설정

힙 메모리 상태 표시 설정

상단 윈도우 메뉴 > 설정 > 일반 > 힙 상태 표시 체크 > Apply and Close

DBeaver 힙 메모리 늘리는 방법

작업표시줄에서 DBeaver 아이콘 우클릭 > DBeaver 우클릭 > 속성 > 대상을 아래와 같이 수정합니다.

DBeaver설치경로\dbeaver.exe -nl ko -vmargs -Xms4096m

max_allowed_packet 설정 변경

max_allowed_packet=64M

/etc/my.cnf 파일을 수정하여 위 설정을 추가 후 MySQL 서버를 재시작하면, DB에서 허용하는 최대 패킷 수가 늘어납니다.

max_allowed_packet 설정 확인

SHOW VARIABLES LIKE 'max_allowed_packet';

DBeaver SQL 편집기에서 위 쿼리를 실행하면 최대 허용 패킷 수를 확인할 수 있습니다.

쿼리 대문자 자동완성 설정

쿼리 편집기 우클릭 > Preferences… > 편집기 > SQL 편집기 > SQL 포맷 설정 > 우측 상단 Global settings > Keyword case: Upper 변경 > 적용 > Apply and Close
쿼리 작성 시 대문자로 작성해도 스페이스바 누르면 소문자로 변환되는 문제를 해결할 수 있습니다.

오토커밋 해제 방법

상단 윈도우 메뉴 > 설정 > 연결 > 연결 유형 > Auto-commit by default
운영 DB에서 delete 쿼리를 실수로 실행해서 바로 적용되지 않도록, 오토커밋이 꺼진 상태에 적응하면 좋습니다.
또한, SELECT는 rollback; 후 조회, DML은 한건씩 commit;하는 습관을 들이면 좋습니다.

개발 DB에서 운영 DB 마이그레이션 방법

테이블 내보내기 방법 (DB to DB)

개발 DB에서 데이블 다중 선택 > 우클릭 > 데이터 내보내기 > 데이터베이스 테이블 > 다음 > Target container : Choose … 운영 DB 선택 > Source(개발 DB) 테이블에 Target(운영 DB) 테이블 매핑 > 다음 > 다음 > transfer auto-generated columns 체크 (Auto Increment 컬럼 포함 전송) > 다음 > 진행

위 방법으로는 테이블 제약조건들이 전혀 옮겨지지 않아서 DDL+INSERT문으로 옮기는 것이 좋습니다.

테이블 DDL 생성 방법

개발 DB에서 데이블 다중 선택 > 우클릭 > SQL 생성 > DDL > Copy

테이블 INSERT문 내보내기 방법 (.sql 파일)

개발 DB에서 데이블 다중 선택 > 우클릭 > 데이터 내보내기 > SQL INSERT문으로 내보내기 > 다음 > 다음 > Include generated columns 체크 (Auto Increment 컬럼 포함), INSERT 구문 당 로우 개수 1 입력 > 다음 > .sql 파일 저장할 로컬 Directory 선택, Write to the single file 체크 해제 > 진행

다중 쿼리 실행 방법

운영 DB 우클릭 > SQL 편집기 > SQL 편집기 > notepad++로 .sql 파일 열어서 모든 쿼리 전체 복사 > 편집기 화면에 붙여넣기 > 전체 선택 후 다중 쿼리 실행 (Ctrl + Alt + Shift + X)

폴인키 제약조건으로 인해 중간에 실패가 날 수 있으니, 한 테이블씩 끊어서 INSERT하는 것이 좋습니다.
1만건 이상 실행 시 약 3분 정도 소요됩니다.

힙 초과 에러

Unhandled event loop exception
Java heap space

많은 쿼리 붙여넣기 시 위와 같은 에러 팝업이 나오면 DBeaver 힙 메모리를 늘리고 다시 시도해야 합니다.

최대 허용 패킷 초과 에러

Error occurred during SQL query execution

이유:
 SQL Error [HY000]: (conn=40) Could not send query: query size is >= to max_allowed_packet (16777216)

my.cnf 파일에 max_allowed_packet 설정 값을 높여서 추가하고, DB 재시작 후 다시 시도하면 20만 건 이상도 정상적으로 Insert 됩니다.
다만, 대용량 INSERT 시에는 시간이 오래걸리니 DB가 설치된 리눅스 서버에서 명령어로 sql 파일 실행을 권장합니다.

대용량 .sql 파일 실행 방법

mysql -u root -p DB명 < 파일명.sql

파일질라로 업로드 한 대용량 sql 파일 위치에서 위 명령어를 실행하고 root 계정 패스워드 입력 시 SQL 편집기보다 빠르게 INSERT 됩니다.
DBeaver에서 힙 초과 에러로 INSERT 할 수 없었던 600MB 이상 290만 건 INSERT를 성공한 방법입니다.
PuTTY로 리눅스 서버에 연결하였을 경우, 세션 연결 유지 시간을 180초 정도로 설정해야 중간에 끊기지 않습니다.

View 이관 방법

개발 DB에서 View 우클릭 > SQL 생성 > DDL > Copy > 운영 DB에서 쿼리 실행

프로시저 이관 방법

개발 DB에서 Procedure 우클릭 > SQL 생성 > DDL > Copy > 운영 DB에서 실행

테이블 엑셀 내보내기 방법 (.csv 파일)

개발 DB에서 데이블 다중 선택 > 우클릭 > 데이터 내보내기 > CSV 파일로 내보내기 > 다음 > 다음 > 다음 > 내보내기 할 Directory 선택 > 진행

csv 한글 깨짐 해결
CSV 파일 우클릭 > 연결 프로그램 : 메모장 선택 > 다른 이름으로 저장하기 > 인코딩 : ANSI 선택 > 저장

일부 데이터 INSERT문 생성 방법

조건 쿼리로 테이블 조회 > INSERT문 생성할 ROW 다중 선택 > 우클릭 > SQL 생성 > INSERT > Use fully qualified names 체크 해제 (‘테이블명.’ 제거), Compact SQL 체크 (한 줄로 표시) 후 Copy

엑셀 일괄업로드 방법

엑셀 가져오기 방법 (.csv 파일)

개발 DB에서 데이블 우클릭 > 데이터 가져오기 > CSV에서 가져오기 > 다음 > 다음 > 다음 > 진행

쿼리로 일괄 업로드 방법
데이터 제작 팀에서 받아온 엑셀을 csv 파일로 편집하여 마이그레이션용 테이블에 넣고,
INSERT SELECT 쿼리를 사용하여 마이그레이션용 테이블의 데이터를 가공하고 실제 테이블에 넣습니다.

Java로 일괄 업로드 방법
엑셀 데이터를 Java POI로 읽고, 유효성 검사 후 공통코드 테이블 조회하며 가공한 뒤 Insert 합니다.