DB) Postgresql 특징 및 SQLD 연습;

2021. 9. 5. 15:30ㆍDB

 빅데이터건 인공지능이건 기반은 데이터이다. 분석을 하려면 기반이 되는 데이터가 있어야하는데 원하는 데이터 셋이 이미 있을 확률은 극히 적기 때문에 데이터 셋을 구성해야한다. 어차피 할 거 자격증 하나 따면서 하는거지 뭐 대단한 건 아님

 

Postgresql

가장 큰 특징으로는 오픈 소스..!무료이며 커뮤니티 활성화가 잘 되어있고, 상당히 높은 신뢰성을 보유하고 있다.

그 외에도 높은 동시성 성능, 상업적 사용, 다양한 프레임워크와의 연동 등 다양한 장점으로 오라클을 제끼고 빠르게 치고 올라오고 있다고 한다.

몽고DB와 포스트그레스가 '20 하반기에 크게 반등한 후 눌림목 구간에 위치, 1,2위는 하락하며 약간의 기술적 반등 중

요새 나오는 서비스 들이 NoSQL(Not Only SQL이다. SQL 없는거 X)많이 쓴다해도 RDBMS가 우위이다.

 

데이터베이스 관리 시스템은 MySQL, PostgreSQL같은 관계형 DB와, Redis처럼 딕셔너리 형태나 MongoDB처럼 json같은 파일 형식으로 데이터를 관리하는 시스템을 의미한다.

 

암튼 DB 깔고 데이터셋 만들고 그거로 연습해보자

 

Brew

홈브루는 있을거라고 생각하고, 없다면 이전 Mysql 가서 깔아오셈

brew install posgtresql && brew services start postgresql

깔고 실행

 

postgres를 깔면서 자동으로 psql이 깔렸는데, CLI 안쓰고 GUI 쓰면 볼 일이 없다.

Postgreql은 SHOW DATABASES; 가 없다.

\l(소문자 L)을 입력하면 데이터베이스들이 보임

 

데이터셋을 담을 DB를 하나 만들어준다. 

CREATE DATABASE {DB이름};

\c DB이름으로 해당 DB에 접속해준 후 엑셀 데이터를 조금 전처리 해준다.

원래 데이터도 거의 1기가 가까워서 Column 최대한 뻈다.

저거 고유번호 대충보고 집마다 하나씩 있는 줄 알았는데 아니였음,,

심지어 데이터가 몇 백만개라 엑셀에서 지원하는 104만개 정도의 데이터 훌쩍 뛰어넘어서 다시 저장을 못함.

엑셀 다른 이름으로 저장하면 로드된 100만개 정도만 저장되길래 파이썬에서 바이너리로 열어서 인코딩만 다시 해줬다.

 

import sys, codecs

def read_csv(path, seperator=',', enc = 'utf-8'):
    try:
        csv = codecs.open(path, 'rb', encoding = 'euc-kr')
        output = codecs.open('/Users/youngmoni/postgres/seoul.csv', 'wb', encoding = enc)
    except:
        print(Exception('failed'))
        exit()
    with csv, output:
        while True:
            a = csv.read(4096)
            if not a:
                break
            output.write(a)

read_csv(path = sys.argv[1])

대충 만든 함수인데, euc-kr을 열어서 파일 전부 utf-8로 다른 파일에 다시 써준다.

그리고 보고싶은 컬럼만 따로 빼서 테이블을 만들고 넣어줬다.

 

이런 모양의 테이블

이제 보니까 왜 공동주택 구분명은 VARCHAR로 안되어있음?;

어쩔 수 x

서울, 세종 두 군데 이미 해놓음;

금일의 실습자료

 

일단 관리하기 쉽게 외부 IP 접속도 허용해줌

SHOW config_file 입력해주면 설정 경로 나옴
listen_addresses를 *로 설정해주면 어디서든 접속 가능, 원하는 IP 있으면 그거로 설정

다시 이론

테이블은 행과 열(row, column)로 이루어지며, 행은 튜플이라고도 하고, 컬럼은 어떠한 데이터를 저장하기 위한 필드로 속성이라고도 함

외래키는 다른 테이블의 기본키를 참조(조인)하는 칼럼임.

 

SQL은 RDBMS에서 데이터의 구조를 정의, 조작, 제어 등을 할 수 있는 절차형 언어

DBMS는 SQL(질의, 쿼리문)을 통해 데이터베이스와 클라이언트를 연결하는 미들웨어 역할을 하며, ANSI/ISO 표준을 준수하기 때문에 DBMS가 변경되어도 그대로 사용할 수 있다.

 

SQL의 종류

  • DDL
    • Data Definition Language, 데이터 정의 언어로 관계형 데이터베이스의 구조를 정의하는 언어
    • 데이터베이스 테이블을 생성, 변경, 삭제하며 데이터를 저장할 '구조'를 정의
    • CREATE, ALTER, DROP, RENAME문이 있음
  • DML
    • Data Manipulation Language, 데이터 조작, CRUD 기능
    • DDL을 통해 구조가 정의되면, 해당 구조에 맞추어 데이터를 입력, 수정, 조회, 삭제가 가능
    • INSERT, SELECT, UPDATE, DELETE가 있음
  • DCL
    • Data Control Language, 데이터 제어 언어, DDL로 정의된 데이터 구조에 대한 권한을 유저에게 부여, 회수함
    • GRANT, REVOKE가 있음
  • TCL
    • Transaction Control Language, 트랜잭션(DB의 작업단위)을 제어하는 명령어로 COMMIT, ROLLBACK 등이 있음

트랜잭션의 특성

ACID : Atomic(원자성) - Consistent(일관성) - Isolation(고립성) - Durable(연속성, 내구성)

  • 원자성 : 트랜잭션은 모든 연산이 완료되거나 혹은 모두 실패하거나 둘 중 하나의 상태, 시도하려는 변경 내역이 중도에 실패하면 전체 연산은 중단되고 아무런 변경사항이 없는 것처럼 보인다.
  • 일관성 : 트랜잭션 실행 결과로 DB의 상태가 모순되지 않아야 함. 트랜잭션의 실행되어 DB가 한 번 변경되면 그 상태가 유지가 됨
  • 고립성 : 여러 트랜잭션이 간섭없이 동시에 실행될 수 있다. 어떠한 트랜잭션 중 이루어진 모든 중간 상태의 변경이 다른 트랜잭션에 보이지 않는다. (두 개의 트랜잭션이 동시에 연관된 작업을 하면 한 트랜잭션에서 변경된 사항이 다른 트랜잭션에 영향을 끼치지 않음)
  • 연속(내구)성 : 트랜잭션이 해당 실행을 일단 성공적으로 완료하면 시스템 오류 등에도 데이터가 손실되지 않아야 한다.

SQL

사용자가 작성한 SQL(DDL, DCL, DML 등)은 3단계를 거쳐 실행된다. SQL문의 문법을 검사하고 구문 분석을 한다.

구문 분석 이후, SQL을 실행하는데 SQL이 실행되면 데이터를 인출하게 된다.

  1. 파싱 - SQL문의 문법을 확인하고 구문을 분석, 분석이 완료된 SQL문은 Library Cache에 저장됨
  2. 실행 - 옵티마이저가 실행한 계획에 따라 SQL을 실행한다.
  3. 인출 - 데이터를 읽어서 전송한다.

SQL 데이터 타입 중 VARCHAR과 CHAR를 궁금해하는 사람이 많은데, CHAR는 고정 길이, VARCHAR은 가변길이.

즉 CHAR(255)와 VARCHAR(255)를 동시에 만들었을 때, 10자의 문자가 들어간다면 VARCHAR의 크기는 10이지만, CHAR의 크기는 255가 됨

DDL

PK 지정은 테이블 생성 혹은 수정하며 추가할 수 있음.

CREATE TABLE test(
-- pk
id AUTO_INCREMENT,
-- pk
email VARCHAR(255),
name VARCHAR(255),
-- pk 두개 등록하려면 괄호열고
constraint test_pk PRIMARY KEY(id, email)
);

 

sysdate는 오늘 날짜 조회함.

CREATE TABLE date(
update_date date default sysdate
);

날짜를 입력 값으로 받고 없다면 오늘의 날짜를 저장한다는 뜻

 

foreign key는 외래키를 지정함

CREATE TABLE users(
name VARCHAR(255) PRIMARY KEY,
password VARCHAR(255)
);

CREATE TABLE posts(
author VARCHAR(255),
content TEXT,
CONSTRAINT author_fk FOREIGN KEY(author) REFERENCE users(name)
);

이런 식으로 데이터에 다른 테이블의 데이터를 종속시킬 수 있다.

여기서 위의 users 테이블을 마스터 테이블로 한다.(근데 이거 요새는 메인으로 불러야한다함. 저번에 스택 오버플로우에 master branch로 지칭해서 질문글 쓴 거 보고 누가 지적했음 흠,, ㅋ;)

 

암튼 users와 posts에 CASCADE 옵션으로 참조 무결성을 준수할 수 있다. users 테이블에는 A라는 사람이 없는데, posts 테이블에는 A라는 사람이 있으면 안된다는 것, Null 값으로 채워두던가 아니면 뭐 author가 A인 사람을 다 날려버리던가 해야 참조무결성을 위배하지 않는 것

 

ON DELETE CASCADE, ON UPDATE CASCADE => 해당 Action시 종속된 테이블도 함께 어떤 Action을 취하게 해줌

DML

WHERE - 조건절, 해당 절을 만족하는 조건만 출력. LIKE '% : 여러 자, _ : 한 글자' 와일드 카드로 유사한 문자열 찾을 수 있으나, 속도에 영향을 끼침, 만약 진짜 %나 _가 들어가는 문자를 찾고 싶다면 LIKE '%like\%%' ESCAPE '\';를 통해 이스케이프 문자 뒤의 문자를 문자 그대로 사용 가능, 대용량의 데이터에서 LIKE에 따른 속도 저하가 심하기 때문에, GROB이라는 정규표현식을 사용함.

ORDER BY - 정렬, ASC는 오름차순 정렬, DESC는 내림차순 정렬

LIMIT x를 통해 x개 만큼의 데이터 추출, OFFSET x를 통해 x + 1번째 데이터부터 찾아봄

 

서브쿼리 - 쿼리 내부에 쿼리를 넣어 쿼리문 실행 결과를 재 가공하는 쿼리문

 

중복되는 값이 있을 시 GROUP BY를 통해 중복되는 값들을 묶기 가능하다.

 

대량의 데이터에서 망원동만 간략히 뽑기위한 테이블 하나 만들어줌
위에 써놓은 것 처럼 INSERT 쿼리 내 서브쿼리로 동 이름, 지번, 주택명, 면적, 가격 등을 뽑아옴

LIKE 문을 통해 법정동명에 망원동이 들어가는 모든 행에서 동 이름, 지번, 주택명, 면적, 가격을 뽑아왔다.

 

테스트 삼아서 10개만 뽑아와봄, 잘 들어갔다.

번지수가 꽤나 중복된다. 번지수에 딸려있는 가격이 여러 개니까 번지 별로 평균 값을 내보자.

아 ;;;
소수점 뒤 0, 1, 2번째 자리에서 반올림해서 소수점 맞춰줌

쿼리문 파싱을 해보자면 mangwondong 테이블을 mwd로 alias하고 '번지수'를 '번지'로, 공시 가격을 평균낸 값을 소수점 둘째 자리까지 반올림하고 '번지평균'으로 Alias, 여기서 AVG등의 함수를 쓰기위해 기준이 필요한데, 이 기준은 뒤의 GROUP BY에 들어가는 값이다.

이때 GROUP BY에 기준을 주고 싶으면 WHERE이 아니라, HAVING 절을 이용해 조건을 주게 된다.

 

AVG처럼 집계할 수 있는 함수는 행 수를 조회하는 COUNT(), 합계를 계산하는 SUM(), 평균 AVG(), 최대, 최솟값을 내주는 MAX(), MIN(), 표준편차와 분산을 계산해주는 STDDEV(), VARIAN()이 있다.

* COUNT(*)를 하면 NULL값을 포함한 모든 행의 수 출력, COUNT(칼럼)을 하면 해당 칼럼의 NULL값을 제외한 행을 출력함

평균 공시가격이 8천만원 이하인 번지

이걸 ORDER BY를 이용해 정렬해서 평균 공시가격 8천만원 이하 중 가장 비싼 곳 5군데

이렇게 보니까 실거래가랑 차이 엄청 나네 ㄹㅇ

위에서 쿼리문 작성하는 것에서 알 수 있듯, 쿼리문에도 작성 순서가 있다. 위 SELECT 문의 실행 순서는 다음과 같다.

  1. FROM : mangwondong 테이블에서
  2. WHERE : 조건에 맞는 행을 추린 후,
  3. GROUP BY : 값을 기준으로 그루핑한 다음,
  4. HAVING : 조건에 맞는 그룹을
  5. SELECT : 가져온 후,
  6. ORDER BY ~ DESC : 내림차순으로 정렬한 다음
  7. LIMIT 5 : 5개만 빼서 출력해줌

SELECT에도 조건문을 걸 수 있는데, 3항 연산자는 DECODE(a,b,c,d)와 비슷하고, if 논리연산자나 switch는 case ~ when ~ else로 구현 가능하다.

 

그런데
POSTGRES에는 DECODE가 없단다 ㅎ

WITH : 서브 쿼리를 이용해 임시 테이블 혹은 뷰처럼 쓸 수 있는 기능이다. 파이썬에서 with 블럭은 임시로 쓰는 것처럼 잠깐 쓰기용

다시 조회해보면 안됨

DCL

GRANT : 데이터 베이스 사용자에게 입력, 조회, 삭제, 접근, 권한 부여의 권한을 부여함.

A에게 WITH GRANT, WITH ADMIN으로 권한을 부여한 후 A가 B에게 권한을 부여, REVOKE로 A의 권한을 뺐으면 GRANT로 권한 줬을 때에는 B의 권한까지 회수되고, ADMIN으로 권한을 줬다면 B의 권한은 남아있게 됨

 

youngmon에게 seoul 테이블에 대한 모든 권한 부여

기본적으로 postgres에서는 모든 권한을 가진 public 롤, public 스키마에 데이터가 저장되고, 모두가 접근할 수 있다.

퍼블릭 스키마에서 public롤에 부여된 모든 권한을 회수해서 아무나 접근할 수 없게 함
특정 유저에게 DB -> 스키마 -> 테이블에 대한 권한을 부여해 youngmon이라는 유저만 쓸 수 있게 해줌

 

TCL

COMMIT : Git의 영원한 친구 커밋, INSERT, UPDATE, DELETE문으로 데이터가 변경되면 COMMIT으로 데이터베이스에 반영해주어야 한다. git push 해놓고 커밋 안하면 안 올라간다는 얘기임

 

한 쉘에서 데이터를 업데이트 해놓고 다른 쉘에서 같은 계정(postgres)로 들어가도 안보인다
COMMIT하면 그때는 보임
데이터를 수정해놓고 DB에 반영하기 싫으면 ROLLBACK으로 변경 사항을 적용시키지 않을 수 있다.

중간중간에 저장해야할 필요성이 있다면 SAVEPOINT 명령어로 ROLLBACK될 위치를 미리 지정할 수 있다.

중간 지점인 a1212로 롤백함

SQL 활용

JOIN - 여러 개의 릴레이션을 결합해 새로운 릴레이션을 만드는 기능으로 테이블을 여러 번 읽을 필요가 없고, 트랜잭션 보호가 가능하며 외래키와 관련한 테이블 집합을 쉽게 처리하도록 도움

 

1. EQUI(등가 조인)

 

가장 기본적인 조인 방법으로 두 개의 테이블 간에 일치하는 것을 조인한다.

seoul 테이블에 법정동명을 빼고 코드를 넣어놨어야하는데 ㅈㅅ

위의 테이블 중 법정동명이 겹치기 때문에 저걸 기준으로 두 테이블을 이어준다.

법정동명이 같은 두 행을 연결함

 

특이점은 FROM에서 두 테이블을 참조한다는 것, where 절에 조인 조건을 걸어준다는 것.

where 절에서 AND 이후에 추가로 조건을 더 걸 수도 있다. INNER JOIN이 Default임

 

2. INNER JOIN

위의 등가 조인에서 where절에 조건을 사용한 것과 다르게 ON, USING 절을 사용하여 조인 조건문과 일반 조건문을 분리한다.

그냥 위에서 암시적으로 조인한 것과 다르게 명시적으로 해주는 것

먼저 테이블을 조회해서 망원동인 행을 뽑은 후, seoul의 동명을 행정구역코드의 동명과 INNER JOIN

순서가 먼저 JOIN하고 찾는게 아니라 WHERE => ON의 순서로 실행계획을 세움

 

3. INTERSECT 연산

두 개의 테이블에서 공통된 값을 조회한다.

조회하는 행의 개수는 같아야함.

이런 테이블에서 address의 교집합 뽑기
where 절에서도 다 할 수 있다.

4. OUTER JOIN

OUTER JOIN은 두 개의 테이블 간 교집합을 조회하는 것은 같으나 INNER JOIN과 다른 점은 한 쪽 테이블에만 있는 데이터도 출력한다는 점이다.

왼쪽 테이블에만 있는 데이터를 포함할 때에는 LEFT OUTER JOIN, 오른쪽은 RIGHT OUTER JOIN, 둘 다는 FULL OUTER JOIN

INNER JOIN
LEFT OUTER JOIN

5. CROSS JOIN

조인 조건 구 없이 2개의 테이블을 하나로 조인하며, 조건구가 없기 때문에 M개의 행과 N개의 행을 크로스 조인 했을 때 카테시안 곱이 발생해 M * N개의 행이 출력된다.

FROM 절에서 질의한다.

가능한 모든 조합을 출력한다.

6. UNION

위의 INTERSECT가 교집합을 구현했다면 해당 JOIN은 합집합을 구현한다.

두 개의 테이블을 하나로 만들면서 중복된 값을 제거하기 때문에 UNION 연산이 끝나면 값이 정렬(ORACLE)되게 된다. postgresql이나 mysql은 UNION이라도 정렬 보장 X

이 때, 두 테이블은 칼럼의 개수와, 칼럼의 자료형이 같아야한다.

 

이런 테이블을 아까 주사위랑 UNION 예정
기준이되는 테이블의 칼럼으로 합쳐짐

+ UNION ALL은 중복 제거 없이 그냥 합쳐버림

갯수랑 자료형만 맞으면 다 합침

7. MINUS

두 개의 테이블에서 차집합을 조회한다. 기준이 되는 테이블에는 있고 뒤의 테이블에는 없는 집합을 조회한다.

postgres에서는 EXCEPT 연산으로, MYSQL은 해당 기능이 아예 없어서 LEFT JOIN으로 구현하곤한다.

아까 위에서 만들어놓은 UNION 결과에서 seoul 테이블을 빼서 다시 세종만 남음

SUBQUERY

서브쿼리는 쉽게 말해 쿼리문 내에 또 다시 쿼리문을 쓰는 것으로,

FROM구에 SELECT문을 쓰는 것을 인라인 뷰 혹은 동적 서브쿼리, SELECT 문에 서브쿼리를 쓰는 것을 스칼라 서브쿼리라고 하며, where 구에 SELECT문을 사용하는 것을 서브쿼리, 서브쿼리 내에서 메인쿼리의 칼럼을 사용하는 것을 연관 서브쿼리라고 한다.

 

테이블을 직접 명시하지 않고 SELECT 문을 사용해서 가상의 테이블을 만들 수 있는데 이것이 인라인 뷰

이런 식으로 다중 서브쿼리도 가능하다.

WHERE에 들어가는 서브쿼리는 값이 해당 가상 테이블에 있거나, 같거나, 없거나 등을 비교하기 위함이기 때문에 단일행과 다중행일 때의 종류가 다르다.

단일 행 서브쿼리에서는 '=', '<=' 등의 비교 연산자가 사용되고, 다중 행 서브쿼리에서는 IN(OR), ALL(메인과 서브쿼리가 모두 동일), ANY(메인 쿼리의 비교 조건이 하나 이상 동일하면 True), EXISTS(메인 쿼리와 서브쿼리의 결과가 하나라도 존재하면 True)

이 중 ANY와 EXISTS는 불리언을 반환한다.

 

스칼라 서브쿼리 예, 한 개의 행을 반환하며 여러개의 행을 반환하면 오류가 남

ROLLUP()

GROUP BY를 할 때 서브토탈(새로운 행)을 만들어줌

구 단위로 평균 공시가격을 매길 때 평균 가격을 만들어 주는 예제를 써봤다.

구 별로 평균을 내고, 시 별로 평균을 내고 전체 합계를 뽑아주는데 서울특별시 하나밖에 없어서 전체 합은 의미가 없음

CUBE 함수는 결합 가능한 모든 집계를 분석한다. (카테시안 곱)

예를 들어 인사팀에서 AVG(급여), GROUP BY CUBE(지점, 부서, 직급)의 쿼리문을 넣으면 조합 가능한 모든 경우의 수로

전체 평균 급여, 모든 지점의 평균 급여, 각 지점의 모든 직급의 평균 급여, 각 지점의 각 부서의 평균 급여 등 조합할 수 있는 모든 경우를 출력해준다. seoul 테이블은 데이터셋이 350만개라 해보지 못했다;;

 

윈도우 함수

행과 행 간의 관계를 정의하기 위해 제공되는 함수로, 순위, 합계, 평균등을 컨트롤 할 수 있다.

윈도우 함수는 크게 ARGUMENTS(0~N개), PARTITION BY COLUMN (집합 기준으로 소그룹화), ORDER BY ~ (해당 항목 기준 정렬), 윈도잉(행 기준의 범위로 ROWS(절대), RANGE(상대), BETWEEN A AND B, UNBOUNDED PRECEDING(윈도우 시작 위치 = 첫 행), UNBOUNDED FOLLOWING(윈도우 시작 위치 = 마지막 행), CURRENT ROW(시작위치 = 현재 행)

 

SYNTAX는

WINDOWS_FUNCTION(ARGV) OVER([PARTITION BY] [ORDER BY] [WINDOWING]) FROM TABLE ~ GROUP BY ~

 

지금까지 쭉 쓰던 RANK나 AVG, SUM 이런거 전부 윈도우 펑션

 

크게 다음과 같이 나뉜다.

  • 순위함수
    • RANK() : 특정 항목 및 파티션에 대한 순위 계산, 동일한 순위는 동일한 값이 부여된다. A, B, C = 1, 1, 5 => rank = 1, 1, 3
    • DENSE_RANK() : 랭크와 같으나 동일한 값은 같은 순위로 계산하며 순위를 세지 않음. A, B, C = 1, 1, 5 => rank = 1, 1, 2
    • ROW_NUMBER() : 값이 같더라도 UNIQUE 값을 부여하여 다른 값을 가지게 한다. 그냥 정렬 느낌
  • 집계함수
    • SUM() : 파티션 별 합계를 계산한다.
    • AVG() : 파티션 별 평균을 계산한다.
    • COUNT() : 파티션 별 행 개수를 계산함.
    • MAX(), MIN() : 최댓값, 최솟값
  • 행 순서 관련 함수
    • FIRST_VALUE() : 파티션에서 가장 처음에 나오는 값을 구함. MIN()과 같은 역할
    • LAST_VALUE() : 위애거랑 반대. MAX()랑 같음
    • LAG() : 이전 행을 가져옴
    • LEAD() : 윈도우에서 특정 행의 값을 가져오며 기본 값은 1이다.
  • 비율 관련 함수
    • CUME_DIST() : 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회(아래로 갈수록 1에 수렴), 0~1 사이의 값을 지님
    • PERCENT_RANK() : 첫 값 = 0, 끝 값은 1로 하여 순서별 백분율을 조회
    • NTILE() : 파티션 별로 전체 건수를 인자 값으로 나눔. 예를 들어 인자가 4가 들어왔다면 전체 4분위 중 몇 분위에 속하는지 보여줌
    • RATIO_TO_REPORT() : 파티션 내 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점 아래로 보여줌

테이블 파티션

대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장하게 함. 데이터가 물리적으로 분리된 데이터 파일에 저장되면 성능이 향상됨.

파티션을 각각의 파티션 별로 독립적인 관리가 가능하고 파티션 전용 인덱스 생성이 가능해진다.

 

파티션은 여러 기준을 가지고 하는데 기준은 값의 범위를 기준으로 하는 Range Partition, 특정 값을 기준으로 분할하는 List Partition, 내부적으로 해시함수를 사용해서 데이터를 분할하는 Hash Partition(사람이 아니라 컴퓨터가 편하게 하기 위함, 파티션의 데이터를 고르게 분산하기 위해 2의 승수를 인자로 줘야함, NULL값은 첫 번째의 파티션에 위치함)

 

파티션 인덱스는 Global(전역, 여러 파티션이 하나의 인덱스 사용) - Local(반대로 각각의 파티션이 다른 인덱스 사용)과 Prefixed(파티션 키와 인덱스 키가 동일)와 Non-Prefixed(다름) 으로 구분함.

 

옵티마이저와 실행계획

SQL을 작성하여 실행할 때, SQL을 어떻게 실행할 것인지 계획하고 실행하게 되는데, 옵티마이저는 해당 SQL에 대해 최적의 실행 방법을 결정하는 역할을 한다. 해당 실행 계획의 적절성에 대해 가장 큰 영향을 미치는 것은 작성한 쿼리문에 대한 수행 속도가 가장 크며 비용 기반과 규칙 기반의 옵티마이저가 있다.

 

규칙 기반 옵티마이저는 사용 가능한 인덱스 유무와 종류, 쿼리문 내 연산자의 종류, 참조하는 객체(자료구조) 등을 참조함.

ROWID를 통해 하나의 행을 액세스 -> UNIQUE 인덱스를 통해 하나의 행을 액세스 -> 복합 인덱스에 '=' 연산자로 액세스 -> 단일 칼럼 인덱스에 '=' 조건으로 검색 -> 인덱스가 생성되어 있을 때 양쪽 범위 한정(BETWEEN, LIKE) -> 한 쪽 범위 한정(<= 등) -> 전체 테이블 조회 순으로 속도가 빠르며, 해당 기준에 따라 옵티마이저가 실행 계획을 수립함.

 

비용기반 옵티마이저는 SQL문을 처리하기 위해 예상되는 시간, 비용 등을 계산해 가장 자원 소모가 적은 계획을 실행한다. 비용기반은 테이블, 인덱스, 칼럼 등의 객체 통계 정보와 시스템 통계 정보 등을 이용해 계획하는데 쿼리 ->통계 수집-> 계획 생성 -> 비용 예측 -> 계획 생성 등의 순서로 실행한다. SQL을 처리 용이하게 수정하는 점에서 컴파일러의 역할을 한다고 볼 수 있을듯

 

옵티마이저 엔진은 크게 Query Transformer(SQL을 효율적으로 실행하기 위해 변환) -> Estimator(SQL 실행 비용 계산) -> Plan Generator(실행 계획 수립)로 나뉜다.

 

이렇게 해도 결과가 맘에 안들 수 있으니 개발자가 옵티마이저에게 어떤 옵티마이저를 쓸 지 힌트를 줄 수 있는데

/*+ {힌트} */

-- 예)
-- SELECT /*+ RULE */ * FROM ~

위와 같이 /*+ */ 를 사용해 힌트를 준다.

 

옵티마이저가 해당 쿼리를 어떻게 실행할건지 보려면 쿼리문 앞에 EXPLAIN을 붙여주면 된다.

EXPLAIN SELECT ~

EXPLAIN ANALYSE 뒤에 쿼리문을 붙이면 수행시간까지 알려줌

 

옵티마이저 조인

  • Nested Loop Join
    • 랜덤 액세스를 통해 하나의 테이블(Outer Table)에서 데이터를 찾고 그 다음 테이블(Inner Table)을 조인
    • /*+ ordered use_nl(table) */을 통해 명시적으로 NL 조인을 할 수 있음
  • Sort Merge Join
    • 두 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩, 정렬 후 병합(Merge Sort)
    • 정렬을 수행하기 때문에 데이터의 양이 많으면 시간 복잡도가 높아지고, 정렬이 힙 메모리에서 수행되기 때문에 성능이 떨어짐
    • /*+ ordered use_merge(table) */을 통해 명시적으로 수행 가능
  • Hash Join
    • 두 테이블 중 작은 테이블을 HASH 메모리에 로드하고 두 테이블의 조인 키를 사용해 해시 테이블을 생성함
    • 해시함수로 주소를 계산하고 사용하기 때문에 CPU 연산을 많이 함
    • /*+ ordered use_hash(table)로 수행 가능

대충 요 정도하고 이론 마무리

반응형

'DB' 카테고리의 다른 글

DB) 초보자를 위한 ORM 설명회, Object-Relational Mapper  (0) 2023.02.03
DB) 데이터 모델링 이론  (0) 2021.08.28
DB) SQL, MySQL 설치하고 외부에서 불러오기  (0) 2021.08.26

관련글

티스토리툴바