db는 왜 필요할까?

• excel,텍스트 문서로 저장하면 대량의 데이터를 저장할수록 탐색에 시간이 더 걸린다(전체검색).
  db는 인덱싱을 통해 대량의 데이터이더라도 일정한 탐색 시간을 보장할 수 있다.
• 무결성 보장, 동시접속에 대한 안전성 - 트랜잭션 제공
• 데이터를 영속화해야 하는 서비스라면, 결국 db에 대한 이해는 필요함.

1. 인덱스

인덱스란?

db의 데이터를 효율적으로 탐색하기 위한 메타 데이터.

ex) 사전 맨끝의 색인들

• 어떤 식으로 이런 색인을 구성할 수 있을까?
  -데이터를 고정된 크기 단위로 저장?
  ex)100KB단위로 저장해놓으면, 3803 데이터는 39번째 구간에서 찾을 수 있음.
  -> but 길이 단위로 검증해야함. group by같은 내용검색이 필요할 경우 의미가 없음. 단위마다 저정해야 하기에 낭비되는 공간 존재.

-해시 인덱스
키값-데이터의 위치를 저장해둔다. 단건의 조회는 빠르지만 역시 내용을 통한 검색,정렬 등에는 도움이 되지 않는다.

• 이외에도 b- tree, b+ tree, fractal-tree 등 다양하게 구성될 수 있다.

그러나 인덱스는 만능이 아니다. 인덱스도 결국 자료구조이다. sotedList라는 자료구조처럼 update마다 인덱스들은 정렬이 되어야 하기에 비용이 존재한다. 즉 쓰기,업데이트의 성능을 희생해 조회의 성능을 향상시킨 것이다.

인덱스들의 종류에 따른 특성과 차이점을 알고 어떤 쿼리에 어떤 인덱스가 적절할 지 판단해야 쿼리를 튜닝할 수 있다.

인덱스의 종류와 활용

종류

• 프라이머리 키/보조 키
• 유니크 인덱스 / 유니크 하지 않은 인덱스
• 전문 검색용 인덱스/공간 검색용 인덱스
  이런 인덱스의 종류를 고려해 옵티마이저가 쿼리 실행계획을 수립한다.

b- tree 인덱스

루트 노트, 브랜치 노트, 리프 노드들로 구성된다.
각 노드들은 스토리지 엔진의 버퍼에 페이지 단위로 불러와진다.
루트 노드에서부터 참조를 타고가면서 탐색하는 데이터가 어디에 존재하는지 좁혀나간다.

innodb에서는 레코드가 프라이머리 키 순으로 정렬되어 저장된다.
클러스터링 방식으로 비슷한 값끼리는 모아서 저장되도록 한다.

b tree 인덱스 사용에 영향을 미치는 요소

인덱스를 구성하는 컬럼의 크기,레코드의 개수
인덱스가 유니크 한지의 여부...

• 인덱스인 값의 크기
  인덱스를 구성하는 컬럼의 크기는 페이지에 들어갈 수 있는 데이터 개수에 영향을 미치고,
  이에 따라 각 페이지는 몇개의 자식 노드를 가질 수 있는지가 결정된다.
  (한 페이지에 4개의 데이터만 들어갈 수 있다면, 최대 4개의 다른 페이지 참조를 가져 자식노드도 최대 4개일 것이다.)
  -> 인덱스 컬럼의 크기가 크면, 참조를 타고 가야하는 페이지의 개수도 많아지고, 이에 따라 성능이 악화될 수 있다!

• 선택도(기수성) - 얼마나 유니크한 값인지
  유니크하지 않은 값일수록, 인덱스를 통해 탐색을 한 후 불필요하게 읽어야 하는 값이 많다.
  -> 가능하면 유니크한 값이 인덱스가 되는 게 좋다!

• b tree에서 추가,삭제가 발생
  b tree는 일정한 기준으로 정렬되어야 탐색에 써먹을 수 있다.
  때문에 데이터 수정/추가 시 인덱스가 저장된 방식도 수정되어야 한다.
  특히 데이터 추가로 인해 인덱스 노드가 분리되어야 할 경우, 새로운 페이지를 만들어서 데이터들을 많이 옮겨 저장하므로 비용이 크다.
  -> 추가,수정, 업데이트가 적은 컬럼이 인덱스가 되는 것이 좋다!

• 읽어야 하는 레코드의 개수
  예를 들어 1000개의 데이터 중 500개에 해당하는 데이터를 읽어야 한다면, 굳이 인덱스를 거치지 않고 전체 탐색을 하는 것이 나을 수도 있다. 인덱스에 접근하는 것도 비용이 있다. 일반적으로 인덱스를 통해서 1개 레코드를 조회하는 건, 테이블에서 직접 1개 레코드를 조회하는 것보다 4-5배의 비용이 있다고 판단한다. 그 비용에도 불구하고 인덱스를 통해 찾아야할만큼 많은 데이터들이 있는지도 영향을 미친다.
  -> 얼마나 많은 데이터들이 있는지, 읽어야 하는 레코드의 개수가 많은지에 따라 인덱스를 통하지 않고 탐색할 수도 있다!

b tree인덱스를 통해 데이터를 읽는 방법-과정들

인덱스 레인지 스캔

select * from employee where first_name BETWEEN 'aa' AND 'xx'
같은 범위 탐색 쿼리를 검색할 경우이다. 검색해야 할 인덱스의 범위가 결정되었을 때 사용한다.

커버링 인덱스?
디스크의 레코드까지 읽지 않아도 되는 것. 예를 들어 count만을 쿼리한다면 인덱스 정보만을 통해서도 알 수 있다.

리프 노드간에는 링크가 있다? 왜? 브랜치 노드 간에는 없나?

인덱스 풀 스캔

인덱스 테이블 전부를 탐색한다. 레코드 전부를 불러와야 하는 것보다는 낫지만,
인덱스가 효율적으로 사용된 상황은 일반적으로 아니다.

루스 인덱스 스캔

듬성듬성하게 인덱스를 읽는 것.
옵티마이저의 판단하에 인덱스를 읽지 않아도 되는 부분은 안 읽는다.
ex) grouby가 걸려있고 이 중 min값을 쿼리한다면, 이미 정렬되어 있는 테이블에서는 맨 앞의 값만 읽어도 min값이기에 다른 것까지 읽지 않는다.

인덱스 스킵 스캔

클러스터링 인덱스

innodb스토리지 엔진에 한해서 구현되있다. pk값이 비슷한 레코드 끼리 묶어서 저장하는 것을 말한다. -> pk값이 바뀌면 저장되는 위치도 수정된다.
사실 이는 인덱스 정보가 따로 생성되는게 아니라 레코드의 저장 위치가 바뀌는 것이므로
클러스터링 인덱스 말고도 클러스터링 테이블이라고 부르기도 한다.

• 장/단점

세컨더리 인덱스의 영향

인덱스의 스캔 방향

b tree인덱스가 효율적인 상황

비교조건

전문검색 인덱스

함수 기반 인덱스

멀티밸류 인덱스

클러스터링 인덱스

2. mysql 아키텍처

2.1 mysql 서버 구성

• mysql 엔진
  • 쿼리 파서
  • 전처리기
  • 옵티마이저
  • 실행 엔진
• 스토리지 엔진
  • 핸들러

으로 구성되어 있다.

쿼리 실행 구조

mysql엔진은 sql구문을 분석하고, 에러를 검사하고, 실행계획을 세워서
스토리지 엔진에 요청하는 머리와 같은 역할을 한다.
스토리지 엔진은 mysql엔진의 요청을 받아 실제로 수행하는, 손과 발 같은 역할을 한다.
핸들러를 통해 요청을 받아 수행한다.

스레드

• 과정적으로
  db connection 스레드 존재 - 프로그램에서 쿼리 요청 - mysql엔진의 핸들러에서 요청 받음 -> sql파싱해 트리 생성 -> 트리를 프로세서가 검증 -> 옵티마이저가 실행계획 수립 ->
  스토리지 엔진에 실행계획의 수행 요청 -> 스토리지 엔진의 핸들러가 요청 받음 ->
  해당 요청을 수행

• 해당 요청 수행방법: 요청을 받으면 인덱스 부분을 뒤져봄 ->

• 스토리지 엔진은 요청을 구체적으로 어떻게 수행하지?

• 각각의 작업들은 별개의 스레드인가? 이것도 하나의 스레드, 트랜잭션인가?

mysql서버는 프로세스가 아닌 스레드를 기반으로 동작한다.
쿼리 요청을 받으면, 스레드가 할당된다. 이렇게 클라이언트 요청에 의해 할당되는 스레드는
포그라운드 스레드(사용자 스레드)이다.
이 스레드가 버퍼,캐시나 디스크로부터 데이터를 읽어와서 요청받은 작업을 수행한다.
이후 이 작업결과를 디스크에 기록할 때, innoDb는 이 디스크에 쓰기작업은 백그라운드 스레드로 하지만 myisam스토리지 엔진은 사용자 스레드에서 직접 수행한다.

• 이 때문에 myisam은 쓰기지연이 없이 요청에 대해서 바로 쓰기가 될 때까지 기다린다.

• 수행을 어떻게 하지?(스토리지 엔진의 작업과 mysql의 엔진은 별도의 스레드인가?
  스토리지 엔진은 디스크와는 어떻게 이어지는 거지)

2.2 스토리지 엔진 아키텍처

스토리지 엔진은 실행계획을 실제로 수행하며, 이를 위해 락을 통한 동시성 처리, 인덱스 정보들의 캐싱등이 이뤄지는 부분이다.
sql실행기로부터 수행할 쿼리가 오면, 버퍼 풀이라는 메모리 공간의 인덱스 정보, 로그 정보를 통해 쿼리를 수행하고 후처리를 한다.

특징

• 특징1: pk를 통한 클러스터링
• 특징2: 외래 키 지원
• 특징3: mvcc(multi version concurrency control)를 통한 잠금없는 일관된 읽기
  undo로그를 통해 repeatable read 격리 수준에서도 phantom read가 일어나지 않도록 한다. 아직 commit되지 않은 데이터를 조회한다면 이 undo로그에 있는 데이터를 반환한다.
• 특징4: 자동 데드락 감지
• 특징5: 장애복구 자동화

6. 버퍼풀을 통한 캐싱, 디스크 접근 줄임.

7. 버퍼풀의 구조

버퍼풀의 역할: 디스크의 데이터, 수행학 작업 로그,인덱스 정보들을 메모리에 올려 캐시해 두는 것.

버퍼 풀은 거대한 메모리 공간이다.
이는 페이지 크기로 나뉘어져 있다.
페이지 크기 조각을 관리하기 위해

• LRU 리스트
• 플러시 리스트
• 프리 리스트
  라는 3개의 자료구조로 페이지 조각들을 관리한다.
  (페이지 조각이 뭐지? 메모리에 저장된 정보에 대한 메타 데이터 같은 건가)

  b tree의 노드 정보들도 페이지 단위로 구성된다. 이로보아 인덱스 데이터들은 데이터 페이지 버퍼에 페이지 단위로 저장이 된느 듯하다.

리두 로그? 더티 페이지?

버퍼 풀 플러시, 플러시 리스트 플러시, double write buffer,

언두 로그

체인지 버퍼

인덱스 정보들은 어디에 저장되고 어느 시점에 불러와 참고되는데?
우선 디스크에 저장된다. 하지만 매번 디스크로부터 가져오면 성능에 불리하니 스토리지 엔진 내에 캐시되는 곳이 있다.

myisam 스토리지 아키텍처

mysql 로그파일

3. 옵티마이저와 힌트

목표: 실행계획, 쿼리 수행의 방법을 확인하고,판단하에 소트버퍼 설정,힌트를 사용해서 더 나은 실행계획이 되도록 유도를 할수 있게된다.

sql엔진에서 sql파싱을 통해 sql파스 트리를 생성하고, 후에 이 파스트리를 이용해
sql의 최적화와 실행계획을 수립한다.

이런 옵티마이징을 하는 방식의 종료는 2가지가 있다.

• 비용 기반 최적화(cbo)
• 규칙 기반 최적화(rbo)
  현재는 테이블,인덱스에 대한 통계 정보와 cpu연산이 개선되었기에 대부분의 rdbms는 비용 기반 최적화를 한다.

기본 데이터 처리 방식

스트리밍,버퍼링 방식

쿼리가 처리되는 방식은 1)스트리밍 2)버퍼링 2가지가 있다.

• 스트리밍 방식
  쿼리조건에 해당하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송하는 방식.

• 버퍼링 방식
  order by, group by같은 처리는 스트리밍 방식이 불가능하다.
  where절에 일치하는 레코드들을 모두 가져온 후에야 정렬이나 그룹핑을 해서 보내야하기 때문이다. 이런 경우는 limit문으로 조회 레코드 개수를 제한해보았자 어차피 모두 가져온 후 정렬/그룹핑을 해야하므로 큰 성능 개선이 없다.

풀 테이블 스캔, 풀 인덱스 스캔을 하는 상황

인덱스를 굳이 사용하지 않고 풀 테이블 스캔을 하는 상황은 언제일까?

• 레코드 수가 적어(약 페이지 1개) 인덱스에 접근하는 비용이 더 큰 상황
• where, on절에 인덱스를 사용할만한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있더라도 옵티마이저가 판단하기에 조건에 일치하는 레코드의 건수가 너무 많을 경우

이때 innodb스토리지 엔진에서 풀 테이블 스캔을 할때 하나씩 일일히 디스크에서 페이지를 가져오지는 않는다. 백그라운드 스레드가 read ahead를 해 최대 64개의 페이지를 버퍼풀에 저장해두고 그걸 조회한다.

select count(*) from empployee;
같은 구문처럼 레코드의 건수만 필요하다면 인덱스 스캔을 할 가능성이 높다.

order by처리

인덱스 정렬, filesort, 싱글 패스,투 패스, 인덱스 레인지 스캔, 루스 인덱스 스캔, 풀 테이블 스캔...

정렬을 처리하는 방법은 1) 인덱스를 이용 2) filesort 처리를 이용 2가지 방법이 있다.
이미 인덱스에 따라 정렬된 데이터는 순서대로 읽기만 하면 된다. 하지만 인덱스로만 모든 정렬을 처리할 수는 없다. 아래와 같은 상황이 존재하기 때문이다.

정렬 기준이 너무 많아 이에 대해 모두 인덱스를 생성할 수 없음 / group by, distict같은 처리의 결과를 정렬해야 하는 경우(??? 이건 왜지)/ union의 결과처럼 임시 테이블의 결과를 또 정렬하는 경우...
때문에 filesort를 통한 방식을 알아야 한다.
1)인덱스 사용 방식만이 스트리밍 방식으로 처리된다.

filesort

인덱스를 통하지 않고 정렬할 때 메모리 공간을 할당받아서 사용하는데, 이를 소트버퍼라 한다.
but! 정렬해야 하는 레코드들의 크기가 소트버퍼의 크기를 초과하면 레코드들을 여러 조각으로 나눠서 처리한다. 이때 이 레코드의 임시저장을 위해 디스크를 사용하는데 이런 작업은 성능에 악영향을 미친다. 이 디스크에 있는 임시데이터를 합쳐서 정렬하는 것이 멀티 머지이다. 그렇다고 소트버퍼의 크기를 아주 크게 해버려도 리눅스 os에서는 큰 메모리 공간 할당은 성능을 낮추므로 비슷한 결과를 보인다.
적절한 소트버퍼의 크기는 56kb-1mb정도이다.

싱글,투 패스 방식

소트버퍼를 사용하는 정렬의 모드는 1)싱글 패스 2)투 패스
2가지가 있다.
싱글패스는 레코드 전체를 소트 버퍼에 담는 방식이고,
투 패스는 레코드에서 정렬의 기준이 되는 일부컬럼 소트 버퍼에 담는 방식이다.
이후 이 일부컬럼을 통해 테이블을 다시 조회해서 결과를 반환한다.

소트버퍼에 데이터가 다 담길 수 있다면 싱글패스가 더 효율적이지만, 그렇지 않을 경우 투 패스가 더 나을 수 있다.

정렬 처리 방법

• 1)인덱스를 사용한 정렬
• 2)조인에서 드라이빙 테이블만 정렬
• 3)조인에서 조인결과를 임시 테이블로 저장 후 정렬

2),3)이 filesort를 이용하는 방법이다.

1)의 경우
select * from employee e, salaries s
where s.emp_no = e.emp_no
AND e.emp_no between 10002 AND 10020
order by e.emp_no;
같은 쿼리에서 emp_no는 인덱스가 걸려있기에 그 순으로 인덱스에서 그 순으로 읽어오기만 하면 된다.

2)의 경우
select * from employee e, salaries s
where s.emp_no = e.emp_no
AND e.emp_no between 10002 AND 10020
order by e.last_name
위와 같은 쿼리에서 드라이빙 테이블만 정렬해서 처리한다.
order by의 기준 컬럼이 인덱스라 걸리지 않은 부분이라 인덱스를 사용할 수는 없다. 하지만
드라이빙 테이블인 e만 가져와 정렬 후 조인하면 조인한 후 정렬하는 것보다 효율적이다.
이 방법으로 처리되려면 order by의 기준이 드라이빙 테이블의 컬럼이어야 한다.

3)의 경우
select * from employee e, salaries s
where s.emp_no = e.emp_no
AND e.emp_no between 10002 AND 10020
order by s.salary;
order by의 기준이 드라이빙 테이블이 아닌 위의 쿼리의 경우,
테이블을 조인한 후 그 그 결과를 임시 테이블을 통해 정렬한다.

이 방식이 많은 데이터를 메모리에 올리기에 소트버퍼를 초과할 가능성이 높고, 따라서 투 패스를 사용할 가능성도 상대적으로는 높을 듯하다.

가능하면 인덱스를 통한 정렬이 되도록 하고, 그게 안된다면 드라이빙 테이블 중심적으로 정렬이 되도록 쿼리를 작성하는 게 좋겠다.

group by처리

앞서 설명했듯 버퍼링 방식으로 처리되는 구문이다.
group by를 사용하면서도 가능한 인덱스를 사용하도록 하는 법이 뭘까.

• 1) 드라이빙 테이블에 속한 인덱스 된 컬럼만으로 그룹핑한다.
• 2) 루스 인덱스 스캔을 유도
  select emp_no from salaries
  where from_date = '1998-02-02'
  group by emp_no;
  위와 같은 쿼리를 처리할때 salaries에 (emp_no, from-date)인덱스가 걸려있다면
  emp_no레코들을 조회하면서 from_date값이 19980202인것을 조회한다.

3) 임시 테이블을 사용
이때는 인덱스를 사용하지 못한다.
select e.last_name, AVG(s.salary)
from salaries s, employee e
where s.emp_no = e.emp_no
group by e.last_name
같이 min,max이외의 집계함수 사용, 인덱스가 걸려있지 않은 컬럼으로 그룹핑
된다면 임시 테이블을 사용한다.

distinct처리

distictc처리는 집합 함수와 같이 사용될 때, 아닐 때로 구분된다.
distinct키워드가 영향을 미치는 범위가 그 함수에 따라 달라지기 때문이다.

내부 임시 테이블 활용

고급 최적화

쿼리 힌트

rdbms는 서비스에 대한 이해가 없다. 때문에 힌트를 통해 실행계획을 어떻게 수립해야 좋을 지 판단을 개선할 수 있다.
힌트는

• 인덱스 힌트
• 옵티마이저 힌트
  2개가 있다.

인덱스 힌트는 select,update명령에서만 사용할 수 있다.

실행계획

트랜잭션

트랜잭션을 어떤 방식으로 구현했나

mysql의 백업,롤백 구현

성능 최적화의 방법들