'쇠똥굴리기(BOOK)/Mysql 성능최적화'에 해당되는 글 6건

1. Mysql 실행계획(explain) 사용

- SQL문 앞에 explain을 붙여주면 된다

2. type 컬럼

1) ALL -- 테이블 풀스캔

--> 풀스캔이 무조건 나쁜 것은 아니다 where 조건절을 걸어도 옵티마이저가 검색row수가 적을 때는 풀스캔으로 처리할 경우도 많다.

2) index  -- 인덱스 스캔, extra 컬럼 using index 출력시 비용감소 확인

3) range -- 래인지 스캔은 제한된 형태의 인덱스 스캔이다. 레인지 스캔의 경우 인덱스의 특정부분에서 시작해서 특정 범위에 있는 값을 가지는 행을 반환한다. 전체 인덱스를 살펴봐야 할 필요는 없으므로 전체 인덱스 스캔보다 더 나은 성능을 보인다. where 절에 Between 이나 >가 있을 경우 범위 검색을 사용하게 되며, in() 이나 OR 리스트와 같이 어떤 값들의 리스트를 찾는 경우에도 인덱스를 사용할 수 있다. 이경우에도 레인지 스캔이라고 표시되지만 접근방법에는 큰자이가 있고, 성능상에도 꽤 차이가 난다.

4) ref -- 이방식은 어떤 값 하나에 대해 매치되는 행들을 반환해주는 인덱스 접근방식이다. 이때 같은 값을 가지는 여러 개의 행을 찾게 될 수도 있으므로 탐색과 스캔이 함께 사용되며, 고유하지 않은 값에 대한 인덱스나 고유하지 않은 프리픽스에 대한 인덱스에만 이런식으로 접근할 수 있다. 인덱스를 어떤 참조(reference) 값과 비교하기 때문에 ref라고 불리며, 참조 값은 상수여야하며, 다중-테이블 쿼리의 경우엔 이전 테이블에서 넘어온 값도 참조 값으로 사용될 수 있다.

접근 방식 ref_or_null은 ref방식의 변형으로 Mysql이 초기 탐색을 마친후 NULL 엔트리들을 찾기 위해 두 번째 탐색을 해야만 한다는 것을 의미한다.

ex) select * from ref_table where key_column ='상수' or key_column is null

5) eq_ref -- Mysql이 기껏해야 값 하나만을 반환해준다는 것을 알고 있을 때 이런 인덱스 탐색법이 사용되며, Mysql이 쿼리를 실행하기 위해 참조 값을 기본키 혹은 유니크 인덱스에 비교할 떄 이런 접근방법을 사용하는 것을 확인할 수 있다. Mysql에서 값을 하나라도 찾으면 더 이상 매치되는 행들ㄹ의 범위를 평가하거나 매치되는 행을 찾을 필요가 없으므로 이런 접근 방법은 아주 잘 최적화되었다고 볼 수 있다.

6) const, system -- 쿼리의 일부를 상수로 대체해서, 쿼리를 최적화할 수 있을 경우 Mysql에서는 이런 접근방법들을 사용하게 된다. where 절에 기본키를 이용해 검색하는 조건을 사용하는 경우 Mysql에서는 이 쿼리를 상수로 바꿔버린 뒤 조인에서 테이블을 사실상 제거해버린다.

7) NULL -- 이방법은 Mysql이 최적화 단계에서 쿼리를 처리할 수 있으므로 실행 단계에서 인덱스나 테이블에 접근조차하지 않았다는 것을 의미한다. 예를들어 인덱스가 되어 있는 컬럼에서 최솟값을 선택하려는 경우라면 인덱스만 찾아보면 되므로 실행단계에서 테이블에 접근할 필요가 없다.

3. rows 컬럼

이 컬럼은 원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할지에 대한 예측값을 의미한다. 이 숫자는 중첩루프(Nested-loop) 조인계획에서 루프 한 번당 몇 개의 레코드를 읽어야 할지에 대한 예측값이다. 다시말하면 이 값은 그저 Mysql이 테이블에서 읽어야할 거라고 생각하는 행 수를 의미하는 것만이 아니고, Mysql이 쿼리를 실행 관점에서 어떤 기준 이상으로 효율적으로 실행시킬 떄 읽어야하는 행 수의 평균을 의미한다.(기준은 조인 순서에서 앞쪽에서 나온 테이블에서 넘어온 칼럼뿐 아니라 SQL에서 주어진 상수들에 대한 것도 포함한다)

--> rows 의  전체 행을 곱해주면 얼마나 많은 행을 읽을지 조사할 수 있지만

     실제 행수를 읽는 값과 정확히 일치하지 않는다는 말

ex) 테이블 4개 lefe outer join 되어있는 쿼리 실행계획

전체 테이블 row 수 -- 11899, 193, 83, 37606

 = 7721 * 1 * 1 * 195 = 1505595 (예상 rows수)

4. filtered 컬럼

이 컬럼은 어떤 테이블에 대한 조인 조건이나 where 절에 있는 조건 등을 만족시키는 행이 어느 정도 될지에 대한 비관적 예측을 나타낸다. 이 퍼센트 값을 rows 컬럼에 곱해주면 쿼리 계획에서 이전 테이블과 조인할 때 얼마나 많은 행과 조인하게 될지에 대해 Mysql이 예측한 값을 보여준다.

동일한 호스트에 있는 응용 프로그램에 Mysql을 사용하면 Mysql에 네트워크를 통한 접근을 전혀 허용하지 않아도 될 확률이 높다. 외부연결을 허용할 필요를 없애면 공격자가 여러분의 Mysql 서버에 접근하는 방법의 수가 줄어든다.

네트워크 접근 비활성화는 관리 변경을 원격에서 하는 기능을 제한하므로, Mysql 서버에 SSH로 로그인하거나, 여러분이 직접 변경하도록 해주는 웹 기반 응용프로그램을 설치해야 한다. 일부 윈도우 시스템에서는 원격 로그인이 어려울 수도 있는데, 그런 경우 원격 접근의 대안이 있다. 해결책 한 가지는 phpMyAdmin을 설치하는 것이다. 하지만 거기에도 보안상의 문제가 있으므로 주의해야한다.

skip_networking 옵션은 Mysql이 어떠한 TCP 소켓에 대해서도 접속 대기를 하지 않게 하지만, 유닉스 소켓의 연결은 계속 허용할 것이다. 네트워크 지원없이 Mysql을 시작하기는 간단하다. 다음 옵션을 my.cnf 파일의 [mysqld]절에 넣는다.

[mysqld]

skip_networking

skip_networking 옵션은 불편한 부작용이 있다. 이옵션은 안전한 원격 연결과 복제에 stunnel 등의 도구를 사용하는 것을 막으며 자바 응용프로그램의 연결을 막는다.( Mysql JDBC 드라이버인 Connector/J는 TCP/IP를 통해서만 연결한다.)

다른 방법으로는 아래와 같이 Mysql을 구성하는 것이 있다.

[mysqld]

bind_address=127.0.0.1

이렇게 하면 TCP 연결이 가능해지지만 로컬 장치에서만 가능하므로 안전하며 편리하다. 잘 알려진 GNIU/리눅스 배포판 일부는 이 구성을 기본으로 포함하도록 바뀌었다.

* skip_networking 으로 구성된 Mysql 슬레이브 서버 구성은 흥미롭다. 이것은 마스터에 연결을 시작하므로 슬레이브의 모든 데이터가 갱신되지만 어떠한 TCP  연결도 허용되지 않으므로 원격으로 피해를 입을 수 없는 더욱 안전한 백업 복제를 할 수 있다. 하지만 장애 조치 구성에서는 그러한 슬레이브를 사용할 수 없다.

다른 클라이언트가 거기에 연결할 수 없기 때문이다.

-> 마스터 & 슬레이브 구성할 떄는 들어가면 위험한 옵션이었군

1. 지침

- 여기서 설명하는 일반적인 지침은 운영체제 수준 보안에 관한 포괄적인 지침은 아니다. 보안에 관련해 더 심도있게 공부하려면 simson Carfinkel의 저서인 Practical Unix and Internet Security(O'Reilly 출판사)을 참고하기 바란다. 여기서는 데이터베이스 서버에 적걸한 보안을 유지하는 법에 대해 알아보자.

1) 권한이 주어진 계정에 Mysql을 실행하지 말라

- Root가 뚫리면 다 뚫린다. root 계정으로 mysql을 실행하지 말고 mysql과 같은 사용자 계정을 만들어 mysql을 실행해야한다.

2) 운영체제를 최신 버전으로 유지하라

- OS의 보안업데이트 반드시 하자

3) 데이터베이스 호스트에 로그인 제한하기

- Mysql 기반 응용프로그램 개발자들이 서버계정이 필요한가?

  SE와 DBA만 계정이 필요하다. 개발자들은 TCP/IP를 통해 데이터베이스에 원격으로 쿼리를 실행시킬 수만 있으면 된다.

4) 운영 환경 데이터베이스를 다른 것과 분리하라

- 운영기와 테스트기를 분리하라. 물리적으로 아에 별도로 구축하는 것이 좋다.

  이렇게 하면 실수를 방지하고, 보수가 용이해진다.

5) 서버를 감시하라.

- 보안 컨설턴트 고용

6) 강력한 도구를 사용하라

- 백업 복사본을 다른 서버에 저장하는 것도 중요한 보안 방법이다. DB 서버에 침입해서 공격받았다면 OS부터 다시 설치해고 데이터 복구작업을 진행해야한다.

# 같은듯 다른 느낌(localhost vs 127.0.0.1)

localhost라는 호스트 이름은 일반적으로 IP주소 127.0.0.1의 다른 명칭이지만 Mysql에서는 다소 다른 기본동작을 보인다.

연결할 때 호스트 이름 매개변수로 localhost를 지정하면, 예상대로 기본적인 TCP/IP 대신 유닉스 소캣을 통해 연결을 시도한다.

그러므로 다음 명령은 유닉스 소켓을 통해 연결할 것이다.

mysql --host=localhost

이것은 사람들의 예측대로 작동하지 않으므로 좋지 않은 설계이지만 이를 변경하면 오래된 응용프로그램과 클라이언트 라이브러리와의 호환성이 깨질 수 있으므로, 변경하기엔 무리가 있다.

실행되는 장치에 TCP/IP로 연결하는 방법이 두가지 있다.

호스트 이름 대신 IP 주소를 지정하거나 프로토콜을 명시적으로 지정하는 것이다.

mysql --host=127.0.0.1

mysql --host=localhost --protocol=tcp

SSH 터널링을 구성할 때 localhost에서 정방향 TCP 포트에 연결을 시도하면 작동하지 않는다.

포트로 연결하려면 TCP를 사용해야하므로, 대신 IP 주소 127.0.0.1을 사용해야 한다.

이 호스트 이름에는 특별한 점이 또 있다. Mysql은 localhost를 % 와일드 카드에 포함시키지 않는다.

다시말해, user@'%'와 user@localhost에 권한을 지정하는 것은 중복이 아니다.

--> 똑같은 권한을 왜 두번이나 넣었지? 라고 생각했는데.. 다르구나..

1-1 Mysql의 논리적 아키텍처

Mysql의 스토리지 엔진

-- 테이블 생성시 xx.frm 파일이 만들어지고 파일안에는 테이블이름과 스키마 정의를 저장
-- show table status like '테이블이름' \G;

1) MyISAM 엔진
Full-text 인덱싱, 압축, 공간함수 지원, 트랜잭션 x, 행수준의 잠금지원x
스토리지 저장파일 : xxxx.MYD, xxxx.MYI
기능
/*
잠금과 동시성
- 테이블 전체를 잠금
자동복구
수동복구 - CHECK TABLE '테이블이름', REPAIR TABLE '테이블이름'
인덱스기능
지연된키 쓰기 - DELAY_KEY_WRITE 옵션이 ON으로 설정된 MyISAM 테이블은 쿼리 실행 마지막에 변경된 인덱스 데이터를 디스크에 기록하지 않고,
대신 메모리 상의 버퍼에 변경 내용을 버퍼링한다. MyISAM 테이블은 버퍼를 정리하거나 테이블을 닫을 때 인덱스 블록을 디스크로 flush 한다.
이러한 작업은 이용 빈도수가 높고 데이터 변경이 잦은 테이블의 성능을 높여준다. 그러나 서버나 시스템에 충돌이 나면 인덱스가 손상되므로 이를 복구해야한다.
서버를 다시 시작하기 전 myisamchk을 실행하는 스크립트나 자동복구 옵션을 이용해 이 상황을 처리한다.(DELAY_KEY_WRITE 옵션을 사용하지 않더라도 이렇게 하는 것은
매우 좋은 생각이다.) DELAY_KEY_WRITE는 전역적으로 또는 개별 테이블 기준으로 설정할 수 있다.
*/
2) InnoDB 엔진
트랜잭션 O, MVCC이용 4가지 격리수준을 모두 구현한다. REPEATABLE READ 가 DEfault
인덱스 압축을 지원x

3) Memory 엔진
매우빠름, HEAP 테이블, 재시작시 테이블 구조는 있지만, 데이터는 모두 사라짐
HASH 인덱스 사용, 테이블 수준의 잠금, TEXT BLOB 지원x
고정된 레코드만 지원하기 때문에 VARCHAR -> CHAR로 변경해야함(메모리낭비)
사용예)
--조회용 매핑용 테이블(우편번호를 주소에 매핑하는 테이블)
--주기적으로 집계되는 테이블의 결과 캐시용
--데이터 분석시 중간 결과 저장용

4) Archive 엔진
고속삽입과 압축 저장을 위해 최적화된 스토리진 엔진(replication작업 등)
INSERT와 SELECT 쿼리만 지원, zlib 압축, 인덱스 사용x

5) CSV 엔진
쉼표로 구분된 값으로 구성된 파일을 테이블로 처리할 수 있으나 인덱스는 지원x
데이터 교환하거나 일부 로깅작업에 매우 유용

6) Federated 엔진
원격 서버에 테이블 참조
INSERT 쿼리에 가장 유용, 조인이나 집계쿼리는 성능이슈있음

7) Blackhole 엔진
저장 메커니즘이 아에 없음, insert 쿼리는 버림
복제구성과 감사용 로깅에 유용

8) NDB 클러스터 엔진
shared nothing 아무것도 공유하지 않는 형태의 클러스터링 개념
NDB가 스토리지 엔진 수준이 아닌 Mysql 서버 수준에서 조인을 실행함, NDB의 모든 데이터는 네트워클르 거쳐 나오므로 복잡한 조인은 매우 느리다.
반면 단일 테이블 조회는 여러 데이터 노드가 결과를 구성하기 때문에 신속하다.

9) Falcon 엔진
MVCC

10) SolidDB 엔진
MVCC, InnoDB와 유사함

11) PBXT(Primebase XT) 엔진
트랜젝션 커밋의 오버헤드를 최대한 줄이기 위해 고안, 좀더 확인필요

12) Maria 엔진
MVCC

mysql 성능관련해서 심화과정을 공부할 수 있는 책으로
mysql high performance mysql의 번역본인데 현재 절판되어서 시중에서 구할 수는 없다. 원서는 3쇄 4쇄가 나오는데.. 번역본은 아직 소식이 없다..

아직 공부중이며 Mysql sale out를 고려할 때 쯤엔 한번은 꼭 봐야할 책이라 생각한다.