인덱스

 
 
목차, 색인, 책갈피와 같은 기능을 하는 인덱스는, 데이터베이스 분야에서는 어떤 데이터를 검색할 때 속도를 높여주는 자료 구조로
메모리 영역에 생성되는 일종의 책갈피이다.
 
 
 
 

인덱스 구조

보통 Hash, B-Tree, B+Tree가 있다.

Hash

우리가 알고있는 Key, Value형태의 자료 구조다.
해시 함수로 키 값을 해시값으로 변환하고, 이 해시 값을 기반으로 데이터를 빠르게 조회할 수 있다. - O(1)
 

 
 
하지만 이런 해시구조의 특성상 범위 검색에 효율이 떨어진다. 키 값이 조금이라도 변하게 되면 완전히 다른 해시 값을 반환하기 때문이다.
범위 검색에서의 부등호 연산을 포함한 범위 조건(BETWEEN, LIKE 등)에는 적합하지 않다.
 
 
 

B-Tree

B-Tree는 이진 트리를 확장한 트리 구조이다. 자식 노드를 2개보다 더 많이 가질 수 있다.
 

 
 
Root, Branch, Leaf의 구조로 되어있으며 Branch가 곧 리프가 되는 경우도 있다.
B-Tree의 주요 특징 중 하나는 모든 리프 노드가 동일한 깊이에 있다는 점이다.
이는 트리의 균형을 유지하여 연산 시간의 복잡도 logN으로 보장하여 효율적인 검색을 가능하게 한다.
 
B-Tree의 구조가 효율적인 이유는 위에서 언급한 균형 잡힌 구조와 더불어 대수 확장성에 있다.
대수 확장성이란 트리 깊이가 리프 노드 수에 비해 매우 느리게 성장하는 것을 의미한다.
4차 B-Tree구조가 있다고 가정해보자. 각 노드는 최대 4개의 자식노드를 가질 수 있다. 즉 트리의 깊이가 1씩 증가할 때마다 최대 4배의 인덱스가 추가될 수 있다. 따라서 깊이가 d인 4차 B-Tree는 최대 4^d의 리프 노드를 가질 수 있다. 깊이가 10만 되어도 리프 노드수는 100만 개가 넘는다.

트리의 깊이가 낮기 때문에 I/O작업이 적고 연산이 빠르게 일어날 수 있다.

B+Tree

B-Tree를 개선시킨 자료 구조로 각 노드에는 인덱스 키만 저장되고, 실제 데이터는 리프 노드에 저장된다.
 

 
 
리프 노드들은 서로 연결 리스트 형태로 연결되어 있어 범위 검색에 효율적이다. 하지만 데이터를 조회하기 위해선 반드시 리프 노드까지 탐색(log2N)이 되어야 한다.
 
 
 
 

주의사항

DML의 성능 저하

DML(insert, update, delete) 명령에 따라 레코드에 해당하는 인덱스를 생성, 삭제, 변경해야 하기 때문에 성능 저하가 발생한다.
또한 인덱스의 개수가 너무 많으면 레코드 당 인덱스 변경의 횟수가 많아지기 때문에 성능 저하를 발생시킬 수 있다.
인덱스의 개수가 많아지면 옵티마이저가 잘못된 인덱스를 선택하여 조회 성능에 영향을 미치지 못하는 경우도 있다.
 
 

페이지 크기와 레코드 저장

MySQL에서는 디스크와 메모리에 데이터를 읽고 쓰는 최소 작업의 단위를 페이지라 한다.

인덱스를 포함해 PK와 테이블 모두 페이지 단위로 관리된다.

 
우리는 하나의 레코드를 읽기 위해서 여러 레코드가 포함된 페이지를 읽어서 그 중 일치하는 레코드를 읽는 것이다.
(당연히 페이지에는 여러 레코드가 기록되어 있을 수 있다.)
만약 레코드를 찾기 위해 1개의 페이지로 해결이 되지 않는다면 다른 페이지들을 읽어야 하는 추가적인 디스크 I/O가 발생하게 된다. 
 
(참고)
InnoDB의 경우 페이지 크기는 16KB로 고정되어 있었지만, 공식 문서에 따르면 MySQL 5.7.6 이후부터는 페이지 크기를 32KB, 64KB로 설정하는 것이 가능하다고 한다. 하지만 한 레코드의 크기는 16KB로 여전히 제한된다고 한다.
 

 
 
인덱스 역시 페이지 단위로 관리가 된다. 인덱스로 사용되는 PK, 컬럼의 크기가 크면 클수록 인덱스 페이지가 줄어들게 된다. 그럼 인덱스를 활용한 조회 시에도 여러 인덱스 페이지를 읽어야 하는 문제가 발생한다. 그렇기에 인덱스 키는 길면 길수록 성능 저하를 발생시킨다.
 

InnoDB의 경우 하나의 레코드 크기가 페이지 크기의 절반 이상일 경우 레코드에서 외부에 저장할 가변 길이 컬럼을 선택해서 외부 페이지(off-page)에 데이터를 저장하고 기존의 페이지에는 off-page를 가리키는 포인터를 저장한다.

인덱스 컬럼 기준

단일 인덱스

단일 인덱스를 생성할 때는 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다.

카디널리티란 중복된 수치를 얘기한다. Unique 한 값일수록 카디널리티가 높다.
예를 들어 성별은 카디널리티가 낮고, 주민등록번호는 카디널리티가 높다.

 
카디널리티가 높은 컬럼을 인덱스로 생성해야 하는 이유는 인덱스로 많은 부분을 걸러낼 수 있어야 하기 때문이다. 인덱스를 통해 많이 걸러내면 걸러낼수록 조회 성능이 올라간다.
 
 

복합 인덱스

복합 인덱스를 최적화하기 위해서는 카디널리티를 포함하여 다음 상황들을 고려해야 한다.

• 공통으로 사용되는 필수 조건
• =, IN 연산
• 정렬
• 범위 비교 연산 (BETWEEN, LIKE, 비교연산자)

여러 자료를 통해 학습을 했을 때, 인덱스는 범위 비교 시에는 다음 인덱스 컬럼은 사용되지 않는다고 했다.
인덱스를 지정할 때 위의 상황들을 오름차순으로 생성하는 게 효율적이라는 자료도 있었다.
 
 
 

복합 인덱스를 통한 현업에서의 튜닝 시도

종류

인덱스는 분류에 따라 여러 종류의 인덱스가 있다.

• Unique - Non-Unique Index
• Single - Composite Index
• Clustered - Non-Clustered Index
• Function-Based, Bitmap, Reverse key, Hash Index

등등 여러 인덱스들이 있지만 여기서는 클러스터의 분류에 따른 인덱스를 살펴보자
 
 

Clustered Index

클러스터드 인덱스는 데이터가 테이블에 물리적으로 저장되는 순서를 정의한다. 이 뜻은 인덱스가 되는 컬럼을 기준으로 데이터들을 정렬시킴을 의미한다.

primary key의 제약 조건은 클러스터드 인덱스를 자동 생성하기 때문에 일반적인 상황에서는 PK가 곧 클러스터드 인덱스가 된다.
 
테이블당 하나의 클러스터드 인덱스만 존재할 수 있고 인덱스 페이지의 리프 노드에 실제 데이터의 페이지가 들어있다.
 

 
 
 
실제 데이터가 인덱스 순서에 따라 정렬되어 검색 시 매우 빠른 성능을 보일 수 있다. 하지만 위에서 말한 것처럼 정렬된 상태여야 한다. no, id값의 일반적인 auto_increment값을 가진 PK라면 문제가 되지 않겠으나, UUID 같은 정렬되지 않은 키를 사용한다면 정렬을 위해 추가적인 리소스를 발생시킬 수 있다. 성능이 저하될 수 있다는 의미이다.

PK의 보안 때문에 UUID를 고려한다면 UUIDv7을 사용해 보는 것도 괜찮을 것 같다.
UUIDv7은 UNIX_TIMESTAMP를 ms 단위로 인코딩하여 효율적인 색인화가 가능하다고 한다.

Non-Clustered Index

논-클러스터드 인덱스는 테이블에 저장된 물리적 순서에 따라 데이터를 정렬하지는 않는다.
 
클러스터드 인덱스와 달리 리프 노드에는 데이터 페이지에 대한 포인터가 있어 포인터를 통해 데이터 페이지를 조회할 수 있는 형태이고 데이터 페이지는 정렬되어있지 않다.
 

 
 
클러스터드 인덱스와 달리 데이터를 찾는 데 여러 개의 인덱스를 생성할 수 있다.
 
데이터를 찾을 때 추가적인 스텝 (리프 레벨에서 데이터 페이지에 접근)이 필요하기 때문에, 클러스터드 인덱스보다는 속도가 느리고 데이터 입력 시 별도의 공간에 인덱스를 생성해야 하기 때문에 추가 작업이 요구된다. 별도의 공간도 따로 할당되어야 한다. (약 10%)
 
 
 
 

마무리

블로그를 돌아보니 DB 포스팅이 SQLD를 취득하기 위한 학습 이후에 멈춰있었는데, 사실상 현재 해나가고 있는 모든 것들을 포스팅하는 내 성격상 현재 가장 많이 다루고 있는 것이 데이터베이스인데, 네트워크와 PS에 빠져서 소홀했다는 생각이 들었다. 
 
리팩토링 과정에서 단순히 실행 계획을 바탕으로 튜닝을 이것저것 많이 처리하고, 일의 양이 많아 바로 다음 작업으로 넘어가다 보니 정확히 쿼리 튜닝을 위해 관련된 지식들을 학습할 필요를 느껴 다시 하나하나 시작해보려 한다. DB의 여러 부분들의 공부를 깊게 해 보면서 실제로 적용시켜 보며 정리를 위한 포스팅을 계속 이어나갈 수 있도록 해야겠다.
 
 
 
 

참조

해당 프로젝트는 2023/01/25 ~ 2023/03/12 내에 진행되는
아카데미 내 수강생들끼리 팀을 나누어 진행한 모의 프로젝트입니다.
팀원은 5명이었으며, 프로젝트 리더를 맡았습니다.

이전 글 목록

1) 주어진 RFP를 바탕으로 주제 선정 - Spring Project(OTT 서비스)
2) ERD 설계 - Spring Project(OTT 서비스)
3) 회원 가입 기능 구현 - Spring Project (OTT 서비스)
4) 로그인, 로그아웃 기능 구현 - Spring Project (OTT 서비스)
5) 상세 페이지 및 회원 정보 수정 - Spring Project (OTT 서비스)
6) CRUD를 한번에 → 게시판 만들기(QNA게시판) - Spring Project(Mybatis) (OTT 서비스)
7) 게시판 페이징 처리 - Spring Project (OTT 서비스)
8) 카카오 지도 API 사용하기 - Spring Project (OTT 서비스)
9) (네아로) 네이버 로그인 API 활용 사이트 로그인 및 회원가입 - SPRING Project(OTT 서비스)
10) 카카오 로그인 API 사용하기(내 사이트 로그인 및 회원가입) - Spring Project(OTT Service)
11) 아임포트(포트원) API를 이용한 결제처리 - Spring Project(OTT Service)
12) 관리자 페이지 만들기(데이터 통계 및 chart.js, 유저 알고리즘) - Spring Project(OTT Service)

관리자 페이지에서 아래의 이미지는 VIDEO 데이터 입력 시의 화면이다.

개발자 입장이 아닌 보통의 관리자 입장으로 생각 해 보았을 때

영상이 새로 업로드 되면, 위의 이미지처럼 영상 정보 하나에 배우, 카테고리 및 장르 정보까지 다 넣을 것이라고 생각했다.

그렇게 되면 내가 설계했던 ACTOR, CATEGORY, GENRE 및 기타 연결 테이블들에 정보가 입력되지 않게 될 것이다.

때문에 영상 정보가 업로드될 때, 내가 설계한 대로 관련된 모든 테이블에 조회를 한 후, 해당 데이터가 없을 경우 추가시켜줘야 한다고 생각했다.

우선 등록하기를 클릭할 경우, form.submit 형태로 데이터가 전송되게 구성하였다.

Controller

//VIDEO CREATE - 02.19 장재호
@RequestMapping(value="/admin/videoCreate", method=RequestMethod.POST)
public ModelAndView videoCreate(ModelAndView mv, AdminETCDto dto) {
    if(adminService.check(dto) != null) {
        mv.addObject("error", "중복 된 제목이 존재합니다");
        mv.setViewName("redirect:/admin/databases/video/create");
        return mv;
    }
    
    //1. 카테고리
    String category_name = dto.getCategory_name();
    adminService.addCategory(category_name);
    
    //2. 장르
    String genre_name = dto.getGenre_name();
    adminService.addGenre(genre_name);
    
    //3. 배우
    String[] actor = dto.getActor_name().split(",");
    adminService.addActor(actor);
    
    //4. 비디오
    adminService.addVideo(dto);
    
    //5. 비디오카테고리
    adminService.addVideoCategory(dto);
    
    //6. 비디오장르 
    adminService.addVideoGenre(dto);
    
    //7. 비디오액터
    List<String> list = new ArrayList<>();
    for(String a : actor) {
        list.add(a);
    }
    List<Integer> list2 = new ArrayList<>();
    for(int i=0; i<list.size(); i++) {
        list2.add(dto.getVideo_id());
    }
    dto.setActor(list);
    dto.setVideoidx(list2);
    adminService.addVideoActor(dto);	    

    mv.setViewName("admin/admin_video");
    return mv;
}

배우 테이블 접근 시, 이전에 View에서 구분자를 "," 로 설정하였기 때문에, Dao에서 처리할까 하다가 컨트롤러에서 미리 actor배열로 바꿔서 파라미터로 보냈다.

VIDEO_ACTOR의 경우, list에는 배우들을, list2에는 지금 입력하는 영화의 PK값을 담고 dto에 넣어주었다.

VIDEO의 PK를 굳이 컬렉션에 담은 이유는, Mybatis에서 반복문을 편하게 돌리기 위해서였다.

Dao

public void addCategory(String category_name) {
    ss.insert("admin.addCategory", category_name);	

}

public void addGenre(String genre_name) {
    ss.insert("admin.addGenre", genre_name);
}

//배우는 여러명이기 때문에 걸러냇음
public void addActor(String[] actor) {
    List<String> delArr = new ArrayList<>();
    List<String> actorArr = new ArrayList<>();

    delArr = ss.selectList("admin.actorCheck", actor);
    for(String a : actor) actorArr.add(a);

    for(int i=0; i<delArr.size(); i++) {
        for(int j=0; j<actorArr.size(); j++) {
            if(delArr.get(i).equals(actorArr.get(j))) {
                actorArr.remove(j);
            }
        }
    }
    if(actorArr.size() > 0) {
        ss.insert("admin.addActor", actorArr);
    }
}

public void addVideo(AdminETCDto dto) {
    ss.insert("admin.addVideo", dto);
}

public void addVideoCategory(AdminETCDto dto) {
    ss.insert("admin.addVideoCategory", dto);
}

public void addVideoGenre(AdminETCDto dto) {
    ss.insert("admin.addVideoGenre", dto);
}

public void addVideoActor(AdminETCDto dto) {
    ss.insert("admin.addVideoActor", dto);
}

addActor메서드에서, delArr은 들고 들어온 actor 배열과 비교하여 ACTOR 테이블에 중복되어 있는 배우명을 의미한다.

actorArr에서 delArr을 빼 준 값이 기존 ACTOR 테이블에 존재하지 않는 새로운 배우명이 될 것이다.

SQL.xml

<insert id="addCategory">
    insert into CATEGORY(CATEGORY_NAME)
    select #{category_name} from dual where not exists(select CATEGORY_NAME from CATEGORY where CATEGORY_NAME=#{category_name})
</insert>

<insert id="addGenre">
    insert into GENRE(`GENRE_NAME(KOR)`)
    select #{genre_name} from dual where not exists(select `GENRE_NAME(KOR)` from GENRE where `GENRE_NAME(KOR)`=#{genre_name})
</insert>

<select id="actorCheck" resultType="String">		
    select `ACTOR_NAME(KOR)`
      from ACTOR
     where `ACTOR_NAME(KOR)` IN
     <foreach collection="array" item="actor" index="index" open="(" close=")" separator=",">#{actor}</foreach>					 
</select>

<insert id="addActor">
    insert into ACTOR(`ACTOR_NAME(KOR)`)			  
    values
    <foreach collection="list" item="actor" index="index" separator=",">(#{actor})</foreach>
</insert>

<delete id="delActor">
    delete from ACTOR
     where `ACTOR_NAME(KOR)`=#{actor_name}
</delete>

<insert id="addVideo">
    insert into VIDEO(TITLE, SUMMARY, VIDEO_URL, IMAGE_URL, CREATE_COUNTRY, CREATE_YEAR, GRADE)
    values (#{title}, #{summary}, #{video_url}, #{image_url}, #{create_country}, #{create_year}, #{grade})
</insert>

<insert id="addVideoCategory">
    insert into VIDEO_CATEGORY(VIDEO_ID, CATEGORY_ID)
    values ((select VIDEO_ID from VIDEO where TITLE=#{title}),
            (select CATEGORY_ID from CATEGORY where CATEGORY_NAME=#{category_name}))
</insert>

<insert id="addVideoGenre">
    insert into VIDEO_GENRE(VIDEO_ID, GENRE_ID)
    values ((select VIDEO_ID from VIDEO where TITLE=#{title}),
            (select GENRE_ID from GENRE where `GENRE_NAME(KOR)`=#{genre_name}))		
</insert>

<insert id="addVideoActor">
    insert into VIDEO_ACTOR(VIDEO_ID, ACTOR_ID)
    values
    <foreach collection="actor" item="actor" index="index" separator=",">
        ((select VIDEO_ID from VIDEO where TITLE=#{title}), (select ACTOR_ID from ACTOR where `ACTOR_NAME(KOR)`=#{actor}))
    </foreach>
</insert>

쿼리문을 짜는데 크게 어려운 것이 없었기 때문에 딱히 부연설명은 하지 않겠다.

insert시 select from dual이라는 임의의 테이블을 생성하여 갖고 들어온 파라미터 값이 존재하지 않을 경우에만 insert했다.

원하는대로 관리자가 VIDEO 데이터를 입력 시, ACTOR, CATEGORY, GENRE에 중복되지 않는 값들만 추가되었으며

VIDEO테이블에 정보가 insert된 후, VIDEO의 정보에 맞는 VIDEO_ACTOR, VIDEO_CATEGORY, VIDEO_GENRE가 잘 들어왔다.

에러 원인

Mybatis 사용 시 Where절에 있는 ${id}가 문자가 아닌 컬럼으로 인식되어 에러가 발생했다.

${id}를 "${id}"로 바꿔주었더니 해결되었다.

별 것 아닌 실수를 줄이는 연습을 많이 해야할 것 같다.

에러

구름IDE를 이용하여 MySQL과 연동하는 과정에서 MySQL 서버 시작 시 홈 디렉토리가  "/"  로 설정되어있어서 발생

해결

아래 세 줄을 차례로 입력

sudo service mysql stop

sudo usermod -d /var/lib/mysql/ mysql

sudo service mysql start

[  서브쿼리 (SUBQUERY) ]

다른 쿼리 내부에 포함되어 있는 SELECT문을 말한다.

서브쿼리는 괄호() 로 묶어서 표현한다.

서브쿼리 실행 후 메인쿼리를 실행한다.

서브쿼리는 단일 행 연산자(=, >=)와 다중 행 연산자(IN, NOT IN, ANY, EXISTS 등)들을 사용하여 표현한다.

메인쿼리와 서브쿼리
메인쿼리 = 부모쿼리 = 외부쿼리(outer query)
서브쿼리 = 자식쿼리 = 내부쿼리(inner query)

SELECT
  FROM
 WHERE 조건식 연산자 (SELECT FROM WHERE);

장점

1. 쿼리의 구조화를 통한 가독성 상승

2. 복잡한 JOIN, UNION과 같은 동작을 수행할 수 있는 또 다른 방법을 제공

서브쿼리를 사용 가능 한 곳

​MySQL에서 서브쿼리를 포함할 수 있는 외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있다.

서브쿼리는 다른 서브쿼리 안에 포함될 수 있다.

-- 서브쿼리 안에 서브쿼리를 사용 가능하다.
SELECT A.ACTOR_ID, A.FIRST_NAME, A.LAST_NAME
  FROM ACTOR A
 WHERE A.ACTOR_ID IN (SELECT FA.ACTOR_ID
                        FROM FILM_ACTOR FA
                       WHERE FA.FILM_ID IN (SELECT F.FILM_ID
                                              FROM FILM F
                                             WHERE F.RATING IN ('G', 'PG', 'NC-17')));

위치에 따른 분류

SELECT column, (SELECT ...) -- 스칼라 서브쿼리(Scalar SubQuery): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...)         -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE column = (SELECT ...)  -- 중첩 서브쿼리(Nested SubQuery: 하나의 변수(상수)처럼 사용 (조건절)

중첩 서브쿼리

1. 단일 행 서브쿼리(Single Row SubQuery)
  단 하나의 컬럼만 가지는 서브쿼리

-- SAKILA DB에서 카테고리가 COMEDY인 영화
SELECT FC.FILM_ID, F.TITLE
  FROM FILM_CATEGORY FC, FILM F
 WHERE FC.CATEGORY_ID = (SELECT C.CATEGORY_ID
                          FROM CATEGORY C
                         WHERE C.NAME='COMEDY')
   AND FC.FILM_ID = F.FILM_ID;

               
2. 다중 행 서브쿼리(Multiple Row SubQuery)
  다중 행 연산자(IN, NOT IN, ANY, EXISTS 등)들을 사용하여 표현하며, 여러 필드값을 반환한다.

-- MySQL의 world DB에서 영어, 스페인어, 한국어를 95%이상 사용하는 나라를 오름차순 조회
SELECT A.NAME
  FROM COUNTRY A, COUNTRYLANGUAGE B
 WHERE A.CODE = B.COUNTRYCODE
   AND B.LANGUAGE IN ('KOREAN', 'SPANISH', 'ENGLISH')
   AND B.PERCENTAGE>=95
 ORDER BY A.NAME ASC;

3. 다중 컬럼 서브쿼리(Multiple Column SubQuery)

  서브쿼리의 결과가 여러 컬럼의 데이터를 출력 해 주는 서브쿼리

-- Oracle 사원 DB에서 영업부서의 연봉, 부서번호, 사원번호를 출력
SELECT EMPNO, SAL, DEPTNO
  FROM EMP
 WHERE (SAL, DEPTNO) IN (SELECT SAL, DEPTNO
                           FROM EMP
                          WHERE DEPTNO=30
                            AND COMM IS NOT NULL);

인라인 뷰(Inline View)

FROM절에서 원하는 데이터를 조회하여 가상의 집합을 만들어 조인을 수행하거나 다시 조회할 때 사용한다.

무조건 별칭(Alias)을 생성하여야 한다.

-- Oracle 사원 DB에서
-- JOB이 매니저이면서, 20번 부서의 평균 급여보다 큰 다른 부서 사람들의
-- 부서번호, 사원명, 직급, 월급, 사원번호 조회
SELECT B.DEPTNO, B.ENAME, B.JOB, B.SAL, B.EMPNO
  FROM (SELECT DEPTNO, ENAME, JOB, SAL, EMPNO
          FROM EMP
          WHERE SAL > (SELECT AVG(SAL)
                         FROM EMP
                        WHERE DEPTNO=20)) A, EMP B
 WHERE A.EMPNO = B.EMPNO
   AND B.MGR IS NOT NULL
   AND B.DEPTNO != 20;

스칼라 서브쿼리(Scalar Subquery)

SELECT 문에 나타나는 서브쿼리로 다른 테이블에서 어떠한 값을 가져올 때 주로 쓰인다.

단 하나의 레코드만 조회가 가능하다.

일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.

-- Oracle 사원 DB에서 부서별 최대 급여정보 조회
SELECT DEPTNO, DNAME,
       (SELECT MAX(SAL)
          FROM EMP
         WHERE DEPTNO = D.DEPTNO) SAL
  FROM DEPT D;

참조

http://www.tcpschool.com/mysql/mysql_multipleTable_subquery

[  JOIN  ]

데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현해준다.

관계형 데이터베이스(Relation Database)에서 가장 많이 쓰인다.

특징

• 조인하는 테이블에는 같은 값을 가진 컬럼이 필요하다.
• 세 개 이상의 테이블도 조인이 가능하다.
• 조인할 때 테이블에 대한 별칭이 필요하다.
• 조인 컬럼을 비교하는 조건이 필요하다.

INNER JOIN

조인하는 테이블의 ON 절의 조건이 만족하는 데이터만 가져온다.

MySQL에서는 JOIN, INNER JOIN, CROSS JOIN이 같은 의미로 사용된다.

조인 관계에 부합되는 레코드를 모두 가지며, 조인에 부합되지 않는 레코드는 모두 삭제된다.

SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E INNER JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
 WHERE D.DNAME = 'RESEARCH'; -- JOIN이 끝난 후 WHERE절의 조건을 확인한다.

-- 해당 쿼리도 위와 같다.
SELECT E.EMPNO, E.ENAME, D.DNAME
  FROM EMP E INNER, DEPT D
 WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'RESEARCH';

OUTER JOIN(LEFT, RIGHT, FULL)

두 테이블이 합쳐질 때 기준이 어디냐에 따라 기준 테이블의 것은 모두 출력된다.

LEFT JOIN

첫 번째 테이블을 기준으로 두 번째 테이블을 조합한다.
ON절의 조건을 만족하지 못하는 경우 첫 번째 테이블의 필드값은 모두 가져오지만 해당 튜플의 두 번째 테이블 필드값은 모두 NULL이다.

SELECT DISTINCT E.DEPTNO, D.DEPTNO
  FROM EMP E
  LEFT JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

# DEPT에는 40번이 DEPTNO가 존재하지만 EMP에는 존재하지 않아 NULL이 발생한다.
SELECT DISTINCT E.DEPTNO, D.DEPTNO
  FROM DEPT D
  LEFT JOIN EMP E
    ON E.DEPTNO = D.DEPTNO;

RIGHT JOIN

LEFT JOIN과 반대라고 생각하면 된다.

LEFT JOIN이나 RIGHT JOIN을 여러번 할 때
가장 첫 번째 테이블로 가장 많은 열을 가져와야 하며, JOIN을 변경 해 가면서 사용하지 않아야 한다.

FULL OUTER JOIN

MySQL에서는 FULL OUTER JOIN을 지원하지 않는다.

LEFT JOIN과 RIGHT JOIN으로 FULL OUTER JOIN을 구현할 수 있다.

-- UNION은 자동으로 DISTINCT(중복제거)처리가 된다.
(SELECT * FROM EMP E LEFT JOIN DEPT D on E.DEPTNO = D.DEPTNO) 
UNION 
(SELECT * FROM EMP E RIGHT JOIN DEPT D on E.DEPTNO = D.DEPTNO);

[  UNION(합집합)  ]

여러개의 SELECT문의 결과를 하나의 테이블이나 결과 집합으로 표현할 수 있다.

SELECT문으로 선택된 필드의 개수와 타입, 순서는 모두 같아야 한다.

자동으로 DISTINCT(중복제거)처리가 된다.

DISTINCT처리를 하기 싫다면 UNION ALL을 사용하면 된다.

(SELECT * FROM EMP E LEFT JOIN DEPT D on E.DEPTNO = D.DEPTNO) 
UNION 
(SELECT * FROM EMP E RIGHT JOIN DEPT D on E.DEPTNO = D.DEPTNO);

[  DISTINCT(중복 제거)  ]

SELECT절에서 SELECT DISTINCT를 사용하면 간단하게 처리가 가능하지만, 쿼리가 길어지면 성능이 저하된다.

DISTINCT를 사용할 경우, 성능 개선을 위해 JOIN 전에 중복을 제거하는 것이 좋다.( 서브쿼리 )

-- 간단한 사용방법
SELECT DISTINCT E.DEPTNO, D.DEPTNO
  FROM EMP E
  LEFT JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

-- JOIN 전에 먼저 서브쿼리를 사용하여 DISTINCT처리
SELECT C.NAME, C.COUNTRYCODE
  FROM CITY C
  LEFT JOIN (SELECT DISTINCT NAME, CODE
			   FROM COUNTRY) AS CO
    ON C.COUNTRYCODE = CO.CODE;

참조

• https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-JOIN-%EC%A1%B0%EC%9D%B8-%EA%B7%B8%EB%A6%BC%EC%9C%BC%EB%A1%9C-%EC%95%8C%EA%B8%B0%EC%89%BD%EA%B2%8C-%EC%A0%95%EB%A6%AC
• http://www.tcpschool.com/mysql/mysql_multipleTable_join

집계함수

NULL값은 집계하지 않는다.

Oracle의 EMP 테이블로 사용해보자. 혹시 없다면 해당 링크에서 데이터를 추가하기 바란다.

SELECT MAX(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP;
SELECT SUM(SAL) FROM EMP;
SELECT TRUNCATE(AVG(SAL), 2) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
>> 5000 / 800 / 29025 / 2073.21 / 4

그룹화(GROUP BY)

유형별 개수를 알고 싶을 때 데이터의 그룹화를 위해 사용하는 절이다.

WHERE절과 ORDER BY절 사이에 위치해야 한다.

사용하기

-- 기본
SELECT 컬럼
  FROM 테이블
 GROUP BY 그룹화할 컬럼;
 
-- 조건 처리 후 그룹화
 SELECT 컬럼
  FROM 테이블
 WHERE 조건
 GROUP BY 그룹화할 컬럼;

-- 그룹화 후 조건처리
 SELECT 컬럼
  FROM 테이블
 GROUP BY 그룹화할 컬럼;
HAVING 조건

-- 조건 처리 후 그룹화 후 그룹화 한 데이터에 조건처리
SELECT 컬럼
  FROM 테이블
 WHERE 조건
 GROUP BY 그룹화할 컬럼;
HAVING 조건

아래의 예시는 EMP테이블에서 부서번호를 기준으로 그룹화 하여 부서번호 별 사원수와 최대급여 최소급여 급여합계 평균급여의 값을 조회했으며 조회할 때의 컬럼명을 변경해 주었다.

SELECT DEPTNO 부서번호, COUNT(*) 사원수, MAX(SAL) 최대급여,
	   MIN(SAL) 최소급여, SUM(SAL) 급여합계, TRUNCATE(AVG(SAL), 2) 평균급여
  FROM EMP
 GROUP BY DEPTNO;

EMP

더보기

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-12-1980', 'DD-MM-YYYY'),  800, NULL, 20);
        
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600,  300, 30);

INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1250,  500, 30);

INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('02-04-1981', 'DD-MM-YYYY'),  2975, NULL, 20);

INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('01-05-1981', 'DD-MM-YYYY'),  2850, NULL, 30);

INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('09-06-1981', 'DD-MM-YYYY'),  2450, NULL, 10);

INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-12-1982', 'DD-MM-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, 10);

INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('08-09-1981', 'DD-MM-YYYY'),  1500, NULL, 30);

INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-01-1983', 'DD-MM-YYYY'), 1100, NULL, 20);

INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('03-12-1981', 'DD-MM-YYYY'),   950, NULL, 30);

INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('03-12-1981', 'DD-MM-YYYY'),  3000, NULL, 20);

INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-01-1982', 'DD-MM-YYYY'), 1300, NULL, 10);

DEPT

더보기

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

SALGRADE

더보기

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

   

해당 테이블들의 컬럼명이 의미하는 것은 다음과 같다.

EMPNO - 사원번호
ENAME - 사원이름
JOB - 직업
MGR - 관리자의 사원번호
HIREDATE - 입사일
SAL - 봉급
COMM - 커미션
DEPTNO - 부서번호

제어 흐름 함수

프로그램의 흐름을 제어할 때 사용한다.

IF

IF(expr1, expr2, expr3) : expr1이 참이면 expr2, 거짓이면 expr3을 반환한다.

SELECT IF(100>100, '정답', '오답');
>> 오답

IFNULL

IFNULL(expr1, expr2) : expr1이 NULL이면 expr2, NULL이 아니면 expr1을 반환한다.

SELECT IFNULL(NULL, 2313);
SELECT IFNULL(2313, 2);
>> 2313 / 2

NULLIF

NULLIF(expr1, expr2) : expr1=expr2이면 NULL, 아니면 expr1을 반환한다.

SELECT NULLIF(1, 2);
SELECT NULLIF(1, NULLIF(1,2));
>> 1 / NULL

CASE

값을 서로 비교하거나, 표현식의 논리값에 따라 다른 값을 반환한다.

-- 여러 CASE들을 이어서 표현할 수 있다.
SELECT CASE 10 WHEN 0 THEN 'A'
		WHEN 1 THEN 'B'
		ELSE "WHAT?"               
		END CASE1,
	CASE 9  WHEN 0 THEN 'A'
		WHEN 1 THEN 'B'
		ELSE "NO"
		END CASE2;
                
>> CASE1 : WHAT? / CASE2 : NO