My sql 성능 최적화
* Mysql 기본 아키텍처를 이해하고 성능 최적화를 위해서 기본 개념 정리한다.
* 고성능 시스템 구축을 위한 전략과 최적화 기법
Mysql 아키텍처
mysql 논리적 아키텍처
최상위 계층인 클라이언트에는 (네트워크기반 클라이언트/서버 도구, 인증, 보안 등 포함)
두번째 계층 -쿼리 파싱, 분석, 최적화 및 기본 제공 함수 (mysql 지능적인 부분이 여기에 속함)
세번째 계층 - 스토리지 엔진 포함하며 모든 데이터 저장하고 검색하는 역할 담당
연결 관리 및 보안
클라이언트가 mysql 서버에 연결되면 서버에서 인증해야한다.
인증은 사용자 이름, 호스트 및 암호를 기반으로 한다.
클라이언트가 연결되면 각 쿼리에 권한이 있는지 확인한다.
최적화 및 실행
mysql은 쿼리를 구문 분석해 내부 구조를 생성 한 후 최적화(쿼리재작성, 테이블 읽는 순서, 사용한 인덱스 선택)를 적용한다. mysql 8.0 릴리즈 에서부터는 쿼리 캐시가 사라졌다.
쿼리 캐시 대신 자주 제공되는 결과 셋은 memcached 나 redis 로 데이터 캐시한다.
동시성 제어
읽기/쓰기 잠금
공유잠금= 읽기 잠금(shared Lock)과 전용잠금 = 쓰기 잠금(Exclusive Lock)
잠금 세분화
1. 테이블 잠금
- 전체 테이블 잠금, 클라이언트가 테이블에 쓰기를 원할 때 쓰기 잠금을 획득한다. (다른 읽기, 쓰기 작업은 차단된다.)
- 예를 들어 read locak 테이블 잠금은 일부 동시 쓰기 작업을 허용한다.
공식 레퍼런스 : https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
2. 행 잠금
- 가장 큰 동시성을 제공하며 오버헤드가 큰 형태
- 서버가 아닌 스토리지 엔진에서 구현된다.
격리 수준(Isolation level)
- 격리 수준이 낮을 수록 동시성이 높아지고 오버헤드는 줄어든다.
- 동시에 여러 트랜잭션이 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것
아래 표는 트랜잭션 격리 수준에 따라 발생하는 문제점입니다.
| 격리 수준 | DIRTY READ | NON-REPEATABLE READ | PHANTOM READ |
| READ UNCOMMITTED | O | O | O |
| READ COMMITTED | O | O | |
| REPEATABLE READ | O(InnoDB는 발생 X) | ||
| SERIALIZABLE |
READ UNCOMMITTED(커밋되지 않은 읽기)
-실제로는 거의 사용되지 않으며, 커밋되지 않은 트랜잭션의 결과를 볼 수 있다. = dirty read
READ COMMITTED(커밋된 읽기)
- Mysql 을 제외한 대부분의 기본 격리 수준이며 커밋 될때까지 변경 사항은 다른사람에게 표시되지 않는다.
- non-repeatable read를 허용한다.
REPEATABLE READ(반복 가능한 읽기)
- Mysql 의 기본 트랜잭션 격리 수준이다.
- read committed 의 문제를 해결한다. 팬텀 리드는 허용한다.
SERIALIZABLE(직렬화 가능)
- 가장 높은 격리 수준이다.
- 읽는 모든 행에 잠금을 설정한다.
- 거의 사용되지 않는다.
InnoDB VS MyISAM
MySQL의 스토리지 엔진으로 가장 많이 사용하는 엔진으로는 InnoDB와 MyISAM이 있다.
InnoDB 엔진은 트랜잭션 처리가 필요하고 대용량의 데이터를 다루는 부분에서 효율적이고,
MyISAM 엔진은 트랜잭션 처리가 필요 없고, Read only 기능이 많은 서비스일수록 효율적이다.
한줄로 정리하면, InnoDB는 데이터의 변화가 많은 서비스에, MyISAM은 SELECT가 많은 서비스에 적합하다 할 수 있다.
용도에 따라 InnoDB나 MyISAM 스토리지 엔진을 결정하는데, InnoDB와 MyISAM 테이블을 같이 사용할 경우, 조인시 주의해야한다
* 애플리케이션에서 스토리지 엔진을 혼용하지 않는 것이 좋다.
InnoDB
InnoDB 및 BDB 스토리지 엔진은 트랜젝션에 안전한 테이블을 제공하는 트랜잭션-세이프 스토리지 엔진이다.
InnoDB는 모든 5.0 바이너리 배포판에 디폴트로 포함되어 있다.
MyISAM과 비슷하지만 ORACLE처럼 많은 기능을 지원한다.
commit, rollback, 장애복구, row-level locking, 외래키 등
다수의 사용자 동시접속과 퍼포먼스가 증가하여 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었습니다.
CPU 효율은 어느 디스크 기반의 데이터 베이스와 비교해도 우수하고, 자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)을 관리한다.
MyISAM 과 다른 점으로 테이블과 인덱스를 테이블 스페이스에 저장을 하고 테이블 스페이스는 몇개의 서버파일이나 디스크 파티션으로 구성되어있습니다.
MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리
InnoDB 테이블은 OS의 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있다.
InnoDB는 높은 퍼포먼스가 필요한 대용량 사이트에 적합합니다.
InnoDB의 장점으로는 MyISAM의 단점으로 있었던 row level locking이 지원된다.
그렇기 때문에 트랜잭션 처리가 필요한 대용량의 데이터에 유리한 점이 있어서, 사용자의 CRUD가 많은 서비스에 유리하다.
단점으로는, MyISAM의 장점인 풀텍스트 인덱스를 지원하지 못한다.
MyISAM
MyISAM은 비-트랜젝션-세이프(non-transactional-safe) 테이블을 관리한다.
전체 문장 검색 능력 뿐만 아니라, 고-성능 스토리지 밀 복구 기능을 제공한다.
모든 MySQL 구성에서 지원되며, 다른 타입의 엔진으로 디폴트 구성하지 않는 한 이 엔진이 디폴트 스토리지 엔진으로 구성된다.
MyISAM은 블로그라던지, 게시판 처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에 최적의 성능을 발휘한다.
MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리한다.
InnoDB와 다른점
MyISAM의 장점으로는 항상 테이블에 ROW COUNT를 가지고 있기 때문에 SELECT count(*) 명령시 빠르고, SELECT 명령시에도 빠른 속도를 지원한다.
MyISAM은 풀텍스트 인덱스를 지원하는데 그렇기 때문에 Read Only기능이 많은 서비스일 수록 MyISAM이 효율적이라 할 수 있다.
풀텍스트 인덱스 : 검색 엔진과 유사한 방법으로 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장
단점으로는, row level locking을 지원하지 못해, SELECT INSERT UPDATE DELETE시 해당 Table 전체에 Locking이 걸린다.
그래서 row의 수가 커지면 커질수록 CRUD 속도는 엄청나게 느려진다.