mysql 퍼포먼스 최적화를 읽고..

mysql 퍼포먼스 최적화를 읽고.. 이 책의 기준은 mysql5.5 기준이다.
스토리지엔진으로 제공되는게 (버전 5.5) 8가지나된다ㅎㅎ
innodb , myisam, mrg_myisam blackhole csv memory federated archive(단순 로그 수집에 적함)
(innodb엔진만 썼는데ㅎ)
innodb스토리지엔진
유일하게 트랜잭션을 지원하고
mvcc 를 제공하고 ( 여러개의 복제본을 이용해 하나의 데이터를 처리함으로써 서로의 read/write를 방해하지 않도록 하는 방식)
행단위 잠금으로 데이터 변경 작업을 수행해 연관없는 데이터를 다른 사용자가 변경할수있고 ( myIsam은 table 락 )
인덱스와 데이터를 모두 메모리에 올린다.( myisam은 인덱스만 메모리에 올림) 그래서 메모리버퍼크기가 디비성능에 큰영향을 미친다.
primary key 나 unique key 기를 설정을 하지 않아도 내부적으로 6바이트크기의 pk를 갖는다. (볼수는 없단다.)
특히, PK순으로 테이블에 데이터가 저장되기 때문에, 무작위의 PK생성의 insert를 수행하게 되면, 성능이 급격히 저하된다. PK가 무작위 순이라고 하면, 파티셔닝을 이용해 디스크 IO를 최대한 줄일 수 있다.
mysql은 데이터를 어떻게 처리할까?
모든 sql을 단일코어에서 처리한다. 즉 병렬처리하지않는다. 그래서 cpu코어 개수를 늘리는 스케일아웃보다는 단위처리량이 좋은 cpu로 스케일업하는것이 좋다.
nested loop join알고리즘으로만 처리한다. 선행테이블의 조건검색결과값 하나하나를 테이블과 비교해 조인하는 방식이다.
http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html
디비 내부에서는 block nested loop join방식(=조인버퍼) 으로 처리한다. 테이블 조인시 테이블에 접근하기보다는 조인이필요한 데이터를 메모리에 일시적으로 저장해 한번에 테이블에 접근해 조인을 효율적으로 개선한것을 말한다.
조건 이해 where
묵시적형변환. 조건절의 데이터타입이 다를때 우선순위가 높은 타입으로 형이 변환되는건이다. 정수타입이 문자열타입보다 우선순위가 높다. 그래서 문자열과 정수값을 비교하는 쿼리가 들어오면 두개칼럼중 문자열은 정수타입으로 형변환된다.
형변환대상이 인덱스라면? 조건절처리를위해 모든데이터가 형변환대상이될것이고 테이블풀스캔할수밖에 없다.
예를들어. txt라는 문자열칼럼이 있고. 인덱스도 걸려있다. 그래서 빠른 수행속도를 기대하고 sql을 작성했다. 그런데 조건을 정수조건으로 검색한다면? 데이터가 천만건 이상이라면 성능에 좋지않다.
like검색. like검색시 % 위치에 따라 칼럼에 해당 인덱스가 있더라도 의미가 없을 수 있다. 즉 중간이나 뒷부분부터 검색하면 인덱스의 의미가 없다
like lhr% 은 인덱스가 있다면 기대한것처럼 인덱스를 탄다.
like l%은 어떨까? 옵티마이저는 데이터 분포도를 따져 sql을 최적으로 처리하는데, 예를들어 l로 시작하는 데이터 비율이 적지 않다면(20%이상) 처음부터 전체데이터를 읽는게 빠르다고 판단되면 인덱스를 타지 않을 수 있다.
http://dev.mysql.com/doc/refman/5.5/en/how-to-avoid-table-scan.html

(기존에 쓰던 블로그를 정리하면서 글을 옮기고 있는데 다시 읽어보니 ㅎㅎㅎㅎ 더 공부해야 겠다는 생각이 ㅎㅎ )