Mysql 효과적으로 사용하기 [2]

어떤 데이타베이스를 사용하던 가장 중요한 것은 쿼리를 얼마나 효율적으로 날리냐는 것이다.
쿼리에 따라서 속도의 차이기 많이 날 수도 있으니까.

 

mysql의 경우 상용 데이타베이스에 비해서 락이 잘 걸리는 편이다.
대부분 이것 때문에 많은 속도저하가 나오는 것을 확인했다.

 

 

3> 인덱스의 활용

 

대량의 DB를 돌려보시면 알겠지만 index는 검색과 삭제, 수정에 있어서 상당히 중요하다

 

서버에서 100만개의 데이타의 정렬을 테스트 해본 결과 index로 정력했을 경우 144.33초가 나왔고 그렇지 않은 경우는 211.54초가 나왔다..
물론 정렬한 데이타나 필스는 같았고 말이다.

 

이는 큰 차이는 아니지만 데이타의 량이 많을 수록 더 차이가 날 것이다.

 

또 index는 전체 데이타의 50%를 넘지 말라고 규정하고 있는데 이보다는 30%미만으로 정하는 것이 좋다는 것이 개인적인 생각이다.
또 varchar, text는 인덱스로 지정해봐야 큰 속도차를 느끼지 못하므로 인덱스로는 부적합하다는 생각이다.

 

인덱스는 int, char로 정하는 것이 좋다. varchar과 char이 똑같은 문자가 크기만 다를 뿐인데 먼 차이냐고 물을 수도 있지만 두가지는 작지만 큰차이를 가지고 있다.
두개가 같은 크기라도 다른 점은 똑같은 땅에 방을 만들어 놓았냐 아니냐의 차이 일 것이다.

 

varchar, char 둘다 크기가 5라고 하여도 varchar은 데이타가 들어오면 그때서야 들어온 데이타에 크기에 따라서 새로 방을 만들지만 char은 방은 이미 만들어져 있고 그안에 데이타만 들어가는 형식이니 말이다.
따라서 이미 방이 만들어져 데이타만 들어가는 쪽이 속도 처리에 있어 빠르기 때문에 인덱스로서의 활용이 높음을 알아야 한다.

 

 

4> 테이블의 설계

 

데이타베이스를 약간이라도 공부해 보신 분이라면 1, 2, 3 정규형이라고 들어 보았을 것이다.
물론 이런 것들은 실무에서 그다지 중요하지는 않지만 알아두어서 나쁠 것은 없다.

 

이부분은 다른 많은 서적에서 워낙 많이 설명이 되어 있다.

 

테이블을 어떻게 쪼개냐에 따른 구분이지만 속도에 따른 중요한 부분이기도 하다.

 

특히 대용량의 DB의 경우 속도를 높이기 위해서 조개는 경우가 상당히 많다.

 

특정키를 공통으로 하여 데이타를 쪼개고 볼때는 이러한 것들을 다시 붙이는 것인데..
검색할때 많이 유용하게 처리가 가능하다.

 

예를 들어 쇼핑몰의 경우 회원이 구매한 물건, 회원데이타, 주문정보, 상품 등으로 나누어져 있다..

 

만약 A란 회원이 2004년 1월 1일에 어떠한 물건을 구매했으며 회원의 정보도 알고 싶을 경우 어떻게 해야 할까?

 

처음 회원데이타를 통해 회원 여부와 아이디를 알아내고 아이디로 주문정보를 조회한 후 구매한 물건에서 어더한 물건을 구매했으며 그 구매한 물건은 어던 것인지 상품 테이블에서 확인을 해야 할 것이다.

 

만약 이러한 데이타가 하나의 테이블로 묶여있다면 데이타 량이 너무 많아 검색하는데 어려움이 있을 것이고 회원을 삭제하면서 주문정보나 상품등도 함께 삭제될 수 있는 문제가 생긴다.

 

물론 이런 테이블을 하나로 묶는 경우는 없겠지만 단적인 예를 들면 그렇다는 것이다.

 

이렇게 여러개의 테이블로 쪼개어 검색의 속도를 높이는 것이 아주 중요하다.

 

그렇다고 무식하게 너무 많이 나누는 것도 좋지 않으니 잘 판단하여 설계해야 하겠다.

 

 

5> 락을 줄이자.

 

인덱스나 테이블의 설계는 많은 분들이 신경을 쓰는 부분이다.

 

하지만 락에 대해서는 많은 분들이 중요성을 인식하지 못하는 경우가 많다.

 

mysql의 경우 오라클 처럼 필드단위의 락이 아닌 테이블 락이 걸리므로 속도에 아주 치명적이다.

 

특히 insert와 select가 빈번하게 일어나면서 량이 크다면 큰 문제이다.

 

본인의 경우에도 락의 중요성을 인식하지 못하다가 1000만개의 데이타 쯤에서 동시에 50명 이상이 데이타를 select하고 insert하는 과정에서 속도의 저하가 워낙 심해서 원인을 찾던 중에 알게 되었다.

 

insert의 경우에는 동시에 많이 해도 큰 문제가 없었으나 한 번 select 하면 데이타가 크다보니 시간이 걸리면서 락이 심해졌는데 이는 insert가 너무 많아 select를 시도하는 경우에도 반대의 경우보다는 아니지만 그런 현상이 발생했다.

 

이를 위해서 테이블을 많이 쪼개서 해결을 하였고 insert나 select 구문도 상당 수 개선 작업을 하였다.

 

insert의 경우 insert into Table(a,b,c) values(1,2,3)(2,3,4)(4,5,6) 의 형태로 바꾸었는데 이는 insert를 세번 하는 것이 아니라 한번에 처리하므로 속도의 새선에 많은 영향을 준것으로 생각된다.

 

물론 이경우도 동시에 100명 이상이 들어올 경우 문제가 가끔 발생하였지만 이러한 작업들로 조금 느리다는 생각만 들뿐 크게 문제가 없이 개선이 되었다.

 

우선 테이블을 쪼갤때 많이 신경을 썼던 것이 어떤 부분은 select를 많이 하냐는 것이었다.
또한 이미 처리가 완료되어 잘 사용하지 않는 데이타 들도 따로 보관하고 이러한 작업들을 많이 하였다.

 

이러한 부분들은 게시판에서 아주 효과적으로 사용할 수 있을 것으로 생각이 되는데..
오래된 게시물을 테이블을 분리하여 관리하고 자주 select되는 부분과 그렇지 않은 부분을 테이블을 분리하여 관리하는 것이다.

 

사실 일반 사용자들은 이런 한 것을 잘 모르므로 우선 보여주는 화면이 아닌 효율적인 관리가 되어야 한다는 것이다.

 

게시판의 경우 리스트시에 나오는 제목, 작성자, 조회수, 작성일은 자주 select 가 될 수 있다. 하지만 여기서 조회수는 update가 많이 되므로 따로 분리할 필요가 있다.
또한 내용도 다로 관리하여 테이블의 양을 적게 만든다.

 

이렇게 구성한다면 천만개의 게시물도 효과적으로 처리 할 수 있을 것이다.

 

그러나 게시판의 경우 특별한 경우가 아니라면 이러한 작업은 오히려 번거러울 수 있다는 것을 명심해야 한다.
대형사이트의 게시판이나 이렇게 관리할 필요성이 있겠지만 그렇지 않은 경우는 꼭 이런 작업을 할 필요성이 없음을 미리 밝혀둔다.

 

게시판을 예로 들었지만 게시판 보다는 다른 데에 더 큰 활용이 있을 것으로 생각된다.

 

본인은 리서치 데이타에 이러한 부분을 처리하였고 현재 속더에 거의 영향이 없이 사용하고 있다.
무슨 설문에 이런 처리를 하느냐 싶겠지만 문항이 30개 이상이고 복잡한 처리를 하는 경우라면 상당히 많은 데이타를 insert, select 하므로 꼭 필요한 작업으로 생각된다.

 

지금가지 설명한 내용은 스스로가 어떻게 응용하냐에 따라서 효과적이 될수도 아닐 수도 있음을 다시 한번 밝히며 이쯤에서 마무리 하겠다.

 

PS. 사실 일주일 단위로 강좌  혹은 팀을 올리려하는데 그다지 쉽지가 않다. 너무 게으른듯...

 

 

[출처] 아낌없이 주는 나무 | 마게님 (http://comager.blog.me/60040577018)