인덱스란?
번역을하면 바로 색인이라는 단어로 번역된다.
색인은, 검색하면 책속의 낱말이나 어떤 챕터나 구절들을 빠르게 찾아볼 수 있게 쪽수 정보를 나타내주는 것을 뜻한다.
이 개념을 DB에 적용시킨 것이다.

인덱스는 어떻게 구성하는가?

인덱스는 CREATE INDEX 키워드로 구성이 가능하다.

Team 이라는 테이블이 이렇게 있다.

이름만으로 인덱스를 구성하고 싶다면 아래와 같이 수행해주면 된다.
CREATE INDEX <인덱스명> 테이블(칼럼);

CREATE INDEX team_index team(name);

이렇게 해주게 되면 name의 오름차순 순서로 정렬되게 된다.

왜 정렬이 되는가?
기본적으로 Mysql에서는 BTree 자료구조로 인덱스를 구성하게 된다.
b트리는 이진트리와 같게 기본적으로 정렬을 통해 구성해주게 된다.
-> 데이터 탐색에 용이하도록 구성하는 것이다.

인덱스를 생성하면 아래와같이 주소값을 참조하고 있는 구성이 완료된다.
| name | 주소값 |
|------|-----------------------|
| 팀1 | name이 팀1인 어떤 데이터의 주소값 |
| 팀2 | name이 팀2인 어떤 데이터의 주소값 |

이렇게 되었을 때 다시 SELECT 쿼리를 수행하게되면,

SELECT * FROM team WHERE name = '팀1';

name만을 인덱스 구성한 인덱스 주소 구성을 바라보게 될 것이다.
여기서 이제 트리구조의 탐색 알고리즘이 뒷받침하여 탐색하게 되는데,
가장 가운데 row부터 맞는지 검색을 들어가서 팀1 조건에 부합하는 데이터를 찾게된다.

찾게되면 name이 팀1 인 데이터가 예시에선 지금 2개로 구성했으니, 2개만을 조회해서 결과 반환을 해주게 될 것이다.

이런식으로 탐색을 빠르게 해줄 수 있다.

그럼 한개만의 칼럼만 인덱스를 구성할 수 있나요?

놉. 그렇지는 않다.
예를 들어서 팀에 속한 멤버가, 항상 등번호를 갖고있어야하고 적어도 그 팀에있는 멤버인 홍길동, 1번은 같이 묶여다닌다.
그렇게 된다면 인덱스에 그 둘을 같이 묶어 구성해주는 것이다.
말로 표현한걸 도식화하면 아래와 같다.
| member_id | number | 주소값 |
|-----------|--------|--------------------------|
| 1 | 1 | id가 1이며 등번호도 1인 데이터의 주소값 |
이게 근데 겹칠 수 있는 데이터라면 일반적인 인덱스로 구성시킬 수 있겠지만,
등번호가 유니크한 고유값으로 묶인다면 Unique Index를 구성해줄 수 있다.

CREATE INDEX member_index member(name, number);

인덱스로 탐색한 이후는?

자, 이제 우리는 인덱스로 탐색하는 방법을 조금은 안 것 같다.
여기서 인덱스가 유니크가 아닌경우를 좀 더 보려고한다.
일단 유니크 인덱스가 아니라면, 같은 조건으로 묶여있는 데이터가 여러건 있다는 것인데,
이미 인덱스에서 한번 체로 거른 수준처럼 데이터가 걸러졌는데 이후는 full scan을 수행하여 그 데이터들 중 완전한 조건에 부합하는 데이터만 추려 조회하여 결과를 내준다.

이렇게 되니까 인덱스 참 좋은것 같은데 그럼 칼럼마다 다 생성해주면 좋은거 아닌가?

놉. 그렇지 않다.
왜냐면 데이터는 테이블 자체에 저장이 될텐데, 인덱스는 처음에 b-tree 구조이고 정렬을 한다고 했었다.
인덱스를 많이 구성하게 되면, 그 인덱스들이 원하는 조건대로 재정렬을 해주어야 하기 때문에 성능 저하가 발생할 수 있다.
그리고 이 인덱스가 구성되는게 논리적인게 아니고 주소값을 참조하는 값들이 계속 생성하여 디스크에 저장되기 때문에 저장 용량의 한 부분을 차지하게 된다.

Where 조건에서 복합 칼럼 인덱스를 안타게 구성할 수도 있는지?

당연히 구성해볼 수 있다.
이것은 테이블을 만들고 실제 select 후 실행계획을 분석해보도록 하자.

자 테이블 구조는 위와같이 DDL을 정의해놓은 상태이다.

이제 EXPLAIN 키워드를 사용해서 인덱스를 타는지 안타는지 보게 될 것이다.
일단 기본적으로 member_id와 grade 팀순위를 묶었다.
-> 대충 구성하려고 하다보니 이상한 데이터 구조가 되어버렸다. ㅋㅋㅋㅋㅋㅋㅋ
일단 이 부분은… 넘어가도록…ㅎㅎ
각설하고!

EXPLAIN SELECT *
FROM test.demo2
WHERE member_id = 1;

EXPLAIN SELECT *
FROM test.demo2
WHERE grade = 1;

위의 두개 쿼리를 각각 실행한 결과이다.

위의 DDL에서 member_id를 먼저 구성하고, 그 뒤에 grade를 넣은 인덱스를 구성했다.

member_id를 먼저 구성해주었기 때문에 member_id로 선정렬된 인덱스를 탐색하게 될 것이니

member_id를 조건에 넣어주면 인덱스를 통해 데이터를 추려주는게 가능하다.

member_id를 통한 조회

grade를 통한 조회

하지만, 복합으로 구성된 상태에서는 member_id로 선정렬이 되어있기에 grade만을 where조건에 넣어주면 인덱스 탐색이 불가능하여 보는것처럼 인덱스를 타지 못하게 쿼리가 구성된다.

왜 이렇게 됐을까?
인덱스 구성한 ddl을 보면 member_id 로 시작하게 된다.

그래서 member_id가 Index에 먼저 구성되어 있기 때문에 우선적으로 추려볼 수는 있는 과정을 거치는 것이다.
그래서 grade만을 조회할 때는 grade로만 정렬이되거나, index의 가장 앞단에 grade로 잡혀있는 둘다 없기 때문에 가장 좋지 않은 Full scan 데이터를 조회하게 되는 것이다.

자 그럼 커버링 인덱스는 뭐야?

우선 앞의 내용을 천천히 다시 되짚어보자. (누구나 충분히 이해할 수 있을거라고 생각한다.)

우리는 여태 인덱스를 구성할 때 인덱스로 정할 n개의 칼럼들 + 주소 참조값을 가진 별도의 인덱스를 구성한다고 했다.

근데 해당 조건에 부합하는 row의 모든 데이터가 아니라 인덱스에 포함된 데이터만 조회한다면 사실 테이블 스캔이 필요 없는거 아냐?

-> 이게 바로 커버링 인덱스이다.

조건에 부합하는걸 갖고 디스크가서 데이터를 조회할 필요를 줄이기 때문에 성능상으로 굉장히 이득을 볼 수 있다!

그러면 이제 인덱스를 구성하기 좋게 만드려는 조건들을 나열해볼 수 있지 않나?

그렇다. 인덱스를 잘 설계하기 위해서는 어떻게 만들어줘야 하는지 이쯤 되면 조금은 이해가 될 수 있어보인다.

일단 단일 칼럼 인덱스라면 중복도가 낮은 데이터를 잡아주는게 무조건 유리할 것이다.

-> 이래야 조건에 부합하는 데이터의 Full scan을 하더라도 빠르게 찾아낼 수 있을 것이다.

복합 컬럼 인덱스라면...

일단 자주 엮이는 칼럼들을 우선적으로 묶어주는데, 그 조합의 유니크함이 필요할 것이다.

칼럼의 갯수가 너무 많아지면 반대로 또 인덱스 용량이 무거워지기 때문에 좋지 않을 것이다.

정리

일단 정리 차원에서 인덱스를 정리해봤다.

이전에 공부했던 것보다 지금 공부하면서 정리하는게 좀 더 많이 이해할 수 있게 된 것 같다.

계속 조금씩 점진적으로 깊게 공부하는 방법을 천천히 체득시켜야겠다.

쿼리 속도 개선
1.6초 -> 0.4초
메타테이블과의 조인을 해서 정보를 얻어오는 과정
서로 논리적인 데이터베이스는 다른데 mysql에서 뷰 테이블을 사용하고 있음
그런데 유저 개개인의 데이터에서 매칭시키는 메타테이블의 idx 칼럼이 인덱스가 정해져있지 않았다.
그래서 유저 개개인의 데이터를 전부 조회해서 매칭되는 데이터를 추리고 있었다.

개선안

100개 이상의 row를 탐색하던 것이 25개만 탐색하는것으로 바뀐 실행이 나오게 된다.
그러면서 Extra가 제외되고 참조 정보가 인덱스로 가게 된다.
지금은 100개인 데이터였지만, 만약에 조회할 데이터가 10만 건, 100만 건 이렇게 늘어날 수록
시간은 증가했을 것이다.
진행중인 mysql 독서 스터디가 이런 생각을 하는데 도움을 많이 줬다.

Real Mysql

real mysql 책을 읽으면서 스터디 한 내용을 정리하고자 한다.
일단 11.1 부터 11.3까지의 내용만을 정리했다.
내가 사용하는 애플리케이션에서 특정 데이터를 테이터베이스에 저장하거나 조회를 할 때
SQL이라는 문장을 사용해야 한다.

데이터베이스의 테이블이나 구조를 변경할때는 DDL(데이터 정의 언어)

테이블의 데이터를 조작을 위한 언어는 DML(데이터 조작 언어) 이다.

SQL작성 규칙은 mysql의 서버 시스템 정책에 따라 바뀔 수 있다.
이 정책은 데이터베이스에 어떤 테이블의 데이터들이 들어가지 않았을 때 설정하는 것이 중요하다.

영문 대소문자 구분

Mysql에서는 설치된 운영체제에 따라서 대소문자를 구분하는데,
DB의 테이블이 디스크의 디렉토리나 파일로 매핑이 되기 때문이다.
윈도우의 명령 프롬프트에서는 대충 디렉토리를 대소문자 구분하지 않고
Tap키를 눌러 자동완성을 시키면 그냥 그 알파벳에 맞는 디렉토리를 자동완성 시킨다.
반면에 유닉스 계열에서는 대소문자를 구분해서 대문자로 시작하는 디렉토리를 소문자부터 눌러서 Tap키를 누르면 찾지를 못한다.
그래서 운영체제를 옮기면서 db를 이관할 경우 문제가 생길 수 있기 때문에

Mysql 설정 파일에 lower_case_table_name 시스템 변수를 설정해주면 된다.

Mysql 예약어

데이터베이스 테이블에 예약어와 겹치는 키워드로 생성하는 경우
역따옴표나 큰따옴표로 감싸주어야 한다.
근데 이 감싸주는 행동 때문에 애를 먹을 수 있다.
단순 조회에서도 에러가 나올텐데 이 에러가 상세 정보를 나타내주는 것이 아니라 문법 오류라고만 띄워준다고 한다.
테이블을 생성해주어야 할 때에도 역따옴표를 넣지않고 생성을 해보다가 에러를 맞는 방법이 좋을것 같다.
그리고 무엇보다 최선의 방법은 예약어 키워드와 같은 테이블을 만들지 않는것이
가장 좋은 방법이다.

문자열

나는 이 문자열이 좀 신기했는데,
자동으로 다른 칼럼으로 형변환해서 비교한다는게 조금 신기했었다.

SELECT * FROM Member
WHERE number = '123';

위와 같이 정수형인 컬럼에 문자열로 데이터를 조회하면
조건에 해당하는 저 문자열만 숫자로 자동 형변환이 들어가게 되니까 성능상 문제는 존재하지 않는다.
하지만 역으로 문자열 컬럼이지만 숫자데이터만 저장되어있는 경우에

SELECT * FROM Member
WHERE zipcode = 10001;

우편번호를 형식만 숫자인 문자열로 저장했다고 했을 때 숫자형으로 조건을 검색하면

zipcode에 해당하는 값을 전부 형변환하면서 하나씩 탐색하기 때문에

형변환에 대한 리소스를 많이 잡아먹는다. 이렇게 비교하는건 좋지 않다.

그리고 zipcode가 보편적으로 숫자가 99% 이겠지만, 만약 문자가 들어간게 하나라도 있었다면 위의 조건은 에러를 뱉게 될 것이다.

DATE

이부분은 따로 뗴어져서 있었지만, 마찬가지로 위에서 봤던것 처럼
이 날짜부분도 자동으로 형변환이 된다.
그래서 문자를 Date형식으로 치환하는 어떤 함수를 쓰지 않아도 된다.
그리고 문자열로 조회한다고 해서 인덱스를 못타는 것도 아니다.

Boolean

나는 이부분을 보자마자 바로 tinyint(1) 을 떠올렸다.
true는 1, false는 0으로 나타내주지만,
이것을 정수형 변수에 넣어도 동작한다.
대신 false는 딱 0만 표현이 되는데,
true라고 해서 1 이상의 값들을 표현해주지는 못한다.
그래서 사용할거라면 tinyint(1)로 제한해서 쓰는게 좋을것 같다고 봤다.

더 많은 상태가 필요하다면 Enum을 사용하는게 바람직하다고 생각한다.

Like 연산자

이 연산자를 통해서 정규표현식을 사용하는 연산자보다는 좀 넓은 범위로 검색할 수 있는데 대신 인덱스를 사용할 수가 있다.

• Like에서 사용하는 와일드카드
  • % : 0 또는 1개 이상의 모든 문자에 일치하는지
  • _ : 정확히 1개의 문자 일치

이 와일드카드들을 직접 문자열에 넣어서 탐색하고 싶다면

ESCAPE를 추가해서 검색하면 된다.

~로 시작하는 칼럼을 찾는 데에는 인덱스 레인지 스캔을 적용해서 탐색하는게 빠르지만,
~로 끝나는 칼럼을 찾는곳에서는 인덱스의 left-most 특성으로 인덱스 풀스캔을 진행하게 된다.
mysql의 B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분(Left-most)만 일치하는 경우에 사용할 수 있다.

Between 연산자

위의 이미지는 real mysql에서 가져온 이미지이다.
왼쪽이 Between 연산, 오른쪽이 In 연산이다.
특정 조건이 명확하게 보이는경우엔 In 연산자를 적용해주는 것이 훨씬 빠를것이다.
둘다 같은 데이터를 조회할 수는 있지만 범위를 지정하기 때문에 인덱스를 타지 않고 해당 조건을 쭉 조회하게 될 것이다.

다시 설명하면, 값이 불분명한 범위내에서 검색을 해야하면 Between을 사용해야 하지만, 명확한 경우라면 In절을 사용하는것이 훨씬좋다는것

Mysql 내장함수

여기서 다른 JSON에 대한 특정 문법들에 대한 내용도 나오지만,
쓸일이 많이 없을것 같아서 읽기만 했고 제대로 봤던건

NOW, SYSDATE의 차이

이 두개는 나는 자바를 엮어서 생각했다.
아마 그 부분이 맞을거라고 생각한다.

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();

NOW는 한 명령에 대해 동일한 시간을 가지고 2초를 지나서 데이터를 출력해주니까 값이 같다.
반면, SYSDATE는 한 명령이 아니라 그 자체의 함수가 있을때마다 즉각적으로 실행을 하기 때문에 두 값에 차이가 있다.
이게 이해가 잘 안된다면 아래의 자바 코드로 생각해보면 될 것 같다.

public class Demo {
    public static void main(String[] args) {
        LocalDateTime now = LocalDateTime.now();
        System.out.println(now);
        Thread.sleep(2000);
        System.out.println(now);

        System.out.println(LocalDateTime.now());
        Thread.sleep(2000);
        System.out.println(LocalDateTime.now());
    }
}

이 두개 방식의 차이이다.

그래서 조건식에 현재 시간을 여러번 넣어야하는 경우라면 NOW()를 쓰고

자바에서는 위쪽에 한번 선언한것으로 전부 넣어서 조회를 해주어야 조건이 제대로 동작할 것이다.

정리

11.3장이 MYSQL의 내장함수 설명부분이라 특정 함수들이 많아서
읽는데에 조금 분량이 많았던 것 같다.
다 같이 같은 공간에서 한번에 읽고 토론하는 시간을 가지니까
몰랐던부분도 이해하게 되고 집단지성으로 이게 이런의미구나! 라는걸 가져갈 수 있는 장점이 있다고 생각한다.
개발자에게 있어서 이 책은 2장이 더 괜찮을거라는 추천들 때문에 2장부터 보지만, 더 나아가서는 1장도 봐야 이해가 더 쉬울거라고 본다.