스토리지 엔진 종류와 차이점(InnoDB , MylSAM)

오늘은 MySQL 에서도 스토리지 엔진에 대해서 글을 작성해보려 한다.

먼저 간단하게 SQL 실행 순서를 살펴볼 것이다.

테이블을 구성할 때 자주 변동이 일어나는 데이터는 분리 해주는 것이 좋다. 이후 JOIN 을 통해서 사용하면 된다.

• users → 필수적인 데이터

• user_profiles → 선택적인 데이터(변경이 자주 일어날 수 있음)

SQL 실행 순서

SELECT
name, age, sex, COUNT(*) AS cnt
FROM users
JOIN user_profiles
ON users.id = user_profiles.user_id
WHERE age >= 20
GROUP BY age, sex
HAVING COUNT(*) > 0
ORDER BY age DESC
LIMIT 5

JOIN 까지는 데이터 범위를 확인하고 → 그 다음부터는 데이터를 필터링하고(WHERE)

→ 압축한다.(SELECT)

실제로 MYSQL 서버에서의 동작 과정

SQL 처리되는 과정을 살펴보면 아래 와 같다.

1. DB에 로그인 하면서 connection 을 1개 들고온다.
2. MySQL 5.7 까지는 쿼리 캐시가 남아 있어서 메모리상에 저장되어 있는게 있다하면 그 값을 바로 리턴 (cache hit) → 서버 부하가 적다.
3. MySQL 8.0 부터 쿼리 캐시 사라졌다.
   • 사라진 이유: DB의 부하를 줄이기 위해 앞단에 두었는데 서비스가 커지다보면 캐쉬를 타면 안되는 경우가 많이 생긴다.(예를 들어 “조회수” → 캐쉬에 저장되어 조회수가 안올라간다.)
   • 캐쉬를 피하기 위해 필요없는 쿼리를 넣다 보니 쿼리 파싱이 더 오래걸린다.
   • 그래서 8.0 부터는 캐쉬가 필요하면 별도의 캐쉬 시스템을 사용하도록 하였다.
4. 쿼리 캐시에 데이터가 없으면 cache miss 쿼리 파싱이 돌아간다. (위에서 본 SQL 순서대로 파싱)
   • 파싱되면 어떤 테이블에 어떤 데이터를 조회할지 정리된다.
5. 이후 전처리에서 위에서 파싱이 정리된 내용을 확인을 해준다.
   • 예를 들어 이 테이블에 진짜 이 컬럼이 있는지 없는지
   • DB 에 접근한 사용자가 이 테이블에 접근할 수 있는지 없는지
   • 권한이나 validation 을 체크
6. 이렇게 까지 권한 이랑 값 체크까지 하면 실행할 준비는 끝난 것이다.
7. 하지만 그냥 바로 실행 되는게 아니라 optimization 에서 자동으로 쿼리를 최적화 해준다.
   • 개발자가 작성했던 쿼리에 대해서 어떤식으로 하는게 효율적인지 자동 최적화
   • 예를들어 SELECT 구문에 있지만 나중에 실행한다던가 이런 판단을 해줌
8. 최적화를 끝내면 최종적으로 실행할 수 있는 execution plan 이 나온다.
9. execution plan 을 실행
   • execution plan 을 어떻게 실행하느냐?
   • Disk 에 있는 Data 에 바로 접근하는게 아니라 Storage Engine 이라는 걸 통해서 API 호출 할 때 어떤 인터페이스를 통해서 실행
   • 어떤 구현체를 통해서 API 호출 할 때 인터페이스인 Disk 에 접근
10. Disk 에서 데이터를 가져온 다음에 결과 반환

중요포인트 : 우리가 쿼리를 파싱해서 실행이 될 때 실제 실행이 되는 물리적인 구현체는 Storage Engine 이다.

• 어떤 Storage Engine을 쓰느냐에 따라 성능이 좌우된다.

Storage Engine의 역할

• 물리적 저장 방식: 테이블 구조, index
• 데이터 읽고/쓰기
• 트랜잭션
• 데이터 무결성
• 성능 최적화
• 충돌 복구 및 백업
• 저장 공간의 효율성(Disk)

여기서 모든 Storage Engine 다 볼필요는 없다.

• MEMORY 는 껐다 키면 데이터 날라감
  • 예를 들어 회원가입 했는데 껐다 키면 데이터 난라가서 회원가입 조차 안되어 있으면 안된다.

스토리지 엔진 종류와 차이점(InnoDB, MyISAM)

• InnoDB 에서는 트랜잭션을 완벽하게 지원하지만 MyISAM 에서는 지원하지 않음

• Lock 매커니즘 차이

  • InnoDB 는 데이터를 넣을 때 1번 유저를 넣었으면 2번 유저를 넣을 때 1번 유저 조회 가능
  • MyISAM 은 1번 유저 들어가 있는 상태에서 2번 유저를 넣을 때 1번 유저 조회 불가능
  • MyISAM 은 그래서 테이블 레벨에 Lock 이 걸려 답답한 경우가 있지만 읽기 작업 최적화되어있다.
  • 그래서 검색엔진 같은 걸 사용하지 않을 때 MyISAM 사용
  • 하지만 요즘은 MyISAM 으로 검색엔진을 사용하는게 아닌 검색엔진을 별도로 둠
    • 그 이유가 InnoDB 를 많이 사용하면서 인데 InnoDB 같은 경우 높은 동시성 처리를 자랑하고 데이터 무결성 등 장점이 많다.
    • InnoDB 는 다 좋지만 하나의 단점이 저장 공간 효율성이 안 좋다.
    • 당연히 이런 복잡한 위의 장점들을 가질려면 데이터를 분산화해서 관리해야 하기 때문에 발생하는 저장 공간 효율성의 문제점
    • 그래서 같은 데이터라도 InnoDB 는 MyISAM 보다 크게 잡힌다.

• 옛날에 MyISAM 을 썼던 이유는 읽기나 검색에 효율이 좋았기 때문

  • 예를들어 DB 게시판 검색을 제공하는데 InnoDB을 사용하면 많은 문제가 있다.
  • 데이터 공간의 효율성이 안좋다라는 것은 데이터 사이즈가 크다는 것이고 그 만큼 데이터를 많이 봐야 한다는 의미
  • 많은 데이터를 봐야 한다는 의미는 조회 할 때 손해가 많다는 결론으로 이어진다.
  • 하지만 MyISAM 같은 경우에는 읽기/검색에 효율성이 매우 좋기 때문에 글 검색 같은 상황에 많이 이용되었다.
  • 요즘은 elasticsearch를 많이 이용

이번에 개발을 해볼 예정인 게시판 서비스에서는 InnoDB 사용

• 이유는 게시판을 쓰면 댓글을 작성해야하는데 댓글을 작성한다고 글을 조회 못하는 MyISAM 을 사용하면 사용자들이 답답함을 느낄 수 있다.
• 댓글 쓰기 경진대회로 이루어질 수 있다 ㅎ
  • 글을 봐야 댓글을 달 수 있기 때문

그래서 요즘 서비스에서는 동시성 처리 때문에 InnoDB 를 많이 사용 한다.

• 많은 유저를 감당해야 하기 때문