Class
- Tip&Tech
| [DBMS] MySQL 쓰면서 하지 말아야 할 것 17가지 | |||||
| 글쓴이 |   higher |
날 짜 | 11-10-08 23:40 | 조 회 | 12194 |
|---|---|---|---|---|---|
| 간편URL |
https://phpschool.com/link/tipntech/74384
|
||||
| Link1 | http://xeraph.com/4286421 (594) | ||||
|
출처->http://xeraph.com/4286421
mysql로 작업을 하면서도 퍼포먼스에 대한 이해부족으로 어떤식으로 디비를 구성하고 테이블을 작성해야 좋은 효율을 내게 될것인가에 대해 나름 고민 해 왔었습니다. 이글은 그런 고민에대해 어느정도는 방향을 제시해 주는 글이었던것 같습니다. 물론 이 내용에 대해 다 이해하고 또 다른 관점으로 보시는 고수님들이 스쿨에는 많이 계시겠지만 이제 시작하는 분들에겐 어느정도 방향을 제시 해줄 수 있을것 같아 옮깁니다. ================================================================================================ *MySQL 쓰면서 하지 말아야 할 것 17가지* 작게 생각하기 - 조만간 규모가 커질거라면 MySQL ecosystem을 봐야된다. - 그리고 캐싱 빡시게 안 하는 메이저 웹사이트는 없다. - develooper.com의 Hansen PT랑 Ilia 튜토리얼 볼 것 - 처음부터 확장 가능하게 아키텍처 잘 쪼개놔야된다. - 복제랑 파티셔닝 어떻게 할지 미리 계획 세워놔라. - 파일 기반 세션 좀 쓰지마 -_- - 그렇다고 너무 쓸데없이 크게 생각하지도 말 것 - 특히 성능하고 확장성 구분 못 하면 난감함 EXPLAIN 안 써보기 - SELECT 앞에 EXPLAIN 이라고 붙이기만 하면 되는 것을 (..) - 실행 계획 확인 - 타입 컬럼에 index 써있는거랑 Extra 컬럼에 index 써있는거랑 "매우 큰" 차이 있음 * 타입에 있으면 Full 인덱스 스캔 (안 좋다.) * Extra 컬럼에 있으면 Covering 인덱스 찾았다는 의미임 (좋다!) - 5.0 이후부터는 index_merge 최적화도 한다. 잘못된 데이터 타입 선택 - 한 메모리 블럭 단위에 인덱스 레코드가 많이 들어갈수록 쿼리가 빨리 실행될 것이다. (중요) - 아.. 정규화 좀 해 -_-... (이거 정말 충격과 공포인 듯) - 가장 작은 데이터 타입을 써.. (진짜 BIGINT가 필요하냐고..) - 인덱스 걸리는 필드는 정말 최소한으로 데이터 크기를 써야된다고. - IP는 INT UNSIGNED로 저장해!! (아주 공감) * 이럴 때 쓰라고 INET_ATON 함수가 아예 내장되어 있음. PHP에서 pconnect 쓰는 짓 - 아파치에서 좀비 프로세스라도 생기면 그 커넥션은 그냥 증발하는거야.. - 어차피 MySQL 접속 속도는 Oracle이나 PostgreSQL 보다 10~100배 빠르다고. 너무 과도한 DB 추상화 계층을 두는 것 - 어디 포팅 열심히 할 거 아니면 추상화 계층 쓰지마 (ADODB, MDB2, PearDB 등) - scale out 가능한걸 쓰라고. 스토리지 엔진 이해 못 하는 것 - 단일 엔진만으로 전체 아키텍처를 결정했다면 대부분 최적이 아님 - 엔진 별 장단점을 공부할 것 - ARCHIVE : zlib으로 압축해주고 UPDATE 안 되고 로그 Bulk Insert에 유용함. - MEMORY : 서버 재시작하면 증발. 인덱스가 HASH나 BTREE로 가능함. 임시, 요약 데이터에 사용. * 주간 top X 테이블 같은 것. * 하여튼 메모리에 박아넣고 싶은 데이터 있으면.. 인덱스 레이아웃 이해 못 하는 것 - 제대로 인덱스랑 스토리지 엔진 선택하려면 공부 좀 해 - 엔진은 데이터와 인덱스 레코드를 메모리나 디스크에 레이아웃하는 걸 구현한 것 - clustered 구성은 데이터를 PK 순서에 따라 저장함. - non-clustered 구성은 인덱스만 순서대로 저장하고 데이터는 순서 가정하지 않음. - clustered에서는 인덱스만 타면 추가적인 조회 없이 바로 데이터 가져오는 것임. - 그래서 clustered PK는 작은 놈으로 할 필요가 있다는거 * 다른 인덱스는 각 레코드마다 PK를 앞에 더 붙이게 되니까. * PK 지정 안 하면 아무렇게나 해버림 쿼리 캐시 이해 못 하는 것 - 어플리케이션 read/write 비율은 알고 있어야지 - 쿼리 캐시 설계는 CPU 사용과 읽기 성능 간의 타협 - 쿼리 캐시 크기를 늘린다고 읽기 성능이 좋아지는게 아님. heavy read라도 마찬가지. - 과도한 CPU 사용을 막기 위해 무효화 할 때는 캐시 항목들을 뭉텅이로 날려버림 - 한마디로 SELECT가 참조하는 테이블 데이터 하나라도 변경되면 그 테이블 캐시는 다 날라간다는 얘기임 - 수직 테이블 파티셔닝으로 처방 * Product와 ProductCount를 쪼갠다든지.. * 자주 변하는 것과 변하지 않는 것을 쪼개는게 중요하다 이 말임. Stored Procedure를 쓰는 것 - 무조건 쓰면 안 된다는게 아니고.. - 컴파일 할 때 무슨 일이 일어나는지 이해 못 하고 쓰면 재앙이 된다 이 말. - 다른 RDBMS랑 다르게 connection thread에서 실행 계획이 세워짐. - 이게 뭔 얘기냐 하면 데이터 한 번 가져오고 연결 끊으면 그냥 CPU 낭비 (7~8% 정도)하는 꼴이라는 것. - 웬만하면 Prepared 구문과 Dynamic SQL을 써라.. 아래 경우를 제외하고 * ETL 타입 프로시저 * 아주아주 복잡하지만 자주 실행되지는 않는 것 * 한 번 요청할 때마다 여러번 실행되는 간단한 것 (연결한 상태로 여러번 써야 된다니까) 인덱스 컬럼에 함수 쓰는 것 - 함수에 인덱스 컬럼 넣어 호출하면 당연히 인덱스 못 탄다 - 함수를 먼저 계산해서 상수로 만든 다음에 = 로 연결해야 인덱스 탈 수 있다. * 여기 실행 계획 보면 LIKE도 range type 인덱스 타는 것 보임 인덱스 빼먹거나 쓸모없는 인덱스 만들어 놓는 것 - 인덱스 분포도(selectivity)가 허접하면 안 쓴다. - S = d/n * d = 서로 다른 값의 수 (# of distinct values) * n = 테이블의 전체 레코드 수 - 쓸모없는 인덱스는 INSERT/UPDATE/DELETE를 느리게 할 뿐.. - FK는 무조건 인덱스 걸어라. (물론 FK 제약 걸면 인덱스 자동으로 생긴다.) - WHERE나 GROUP BY 표현식에서 쓰이는 컬럼은 인덱스 추가를 고려할 것 - covering index 사용을 고려할 것 - 인덱스 컬럼 순서에 유의할 것! join 안 쓰는 짓 - 서브쿼리는 join으로 재작성해라 - 커서 제거해라 - 좋은 Mysql 성능을 내려면 기본 - 집합 기반으로 생각해야지 루프 돌리는거 생각하면 안 된다. Deep Scan 고려하지 않는 것 - 검색엔진 크러울러가 쓸고 지나갈 수 있다. - 이 경우 계속해서 전체 집합을 정렬한 다음 LIMIT로 가져와야 하니 무진장 느려진다. - 어떻게든 집합을 작게 줄인 다음 거기서 LIMIT 걸어 가져올 것 InnoDB 테이블에서 WHERE 조건절 없이 SELECT COUNT(*) 하는 짓 - InnoDB 테이블에서는 조건절 없이 COUNT(*) 하는게 느리다. - 각 레코드의 transaction isolation을 유지하는 MVCC 구현이 복잡해서 그렇다는.. - 트리거 걸어서 메모리 스토리지 엔진 쓰는 테이블에 통계를 별도로 유지하면 된다. 프로파일링이나 벤치마킹 안 하는 것 - 프로파일링 : 병목 찾아내기 - 벤치마킹 : 시간에 따른 성능 변화 추이 평가, 부하 견딜 수 있는지 테스트 - 프로파일링 할 때는 실제 데이터를 옮겨와서 할 것 - 어디가 병목이냐~ Memory? Disk I/O? CPU? Network I/O? OS? - 느린 쿼리 로그로 남기기 * log_slow_queries=/path/to/log * log_queries_not_using_indexes - 벤치마킹 시에는 다 고정시키고 변수 하나만 바꿔가면서 해야 함. (쿼리 캐시는 끌 것.) - 도구를 써라~~ * EXPLAIN * SHOW PROFILE * MyTop/innotop * mysqlslap * MyBench * ApacheBench (ab) * super-smack * SysBench * JMeter/Ant * Slow Query Log AUTO_INCREMENT 안 쓰는 것 - PK를 AUTO_INCREMENT로 쓰는건 무진장 최적화 되어 있음 * 고속 병행 INSERT 가능 * 잠금 안 걸리고 읽으면서 계속 할 수 있다는! - 새 레코드를 근처에 놓음으로써 디스크와 페이지 단편화를 줄임 - 메모리와 디스크에 핫 스팟을 생성하고 스와핑을 줄임 ON DUPLICATE KEY UPDATE를 안 쓰는 것 - 레코드가 있으면 업데이트하고 없으면 인서트하고 이런 코드 필요없다!! 다 날려버려라!! - 서버에 불필요하게 왔다갔다 할 필요가 없어짐 - 5-6% 정도 빠름 - 데이터 입력이 많다면 더 커질 수 있음 하지 말아야 할 것 총정리 Thinking too small Not using EXPLAIN Choosing the wrong data types Using persistent connections in PHP Using a heavy DB abstraction layer Not understanding storage engines Not understanding index layouts Not understanding how the query cache works Using stored procedures improperly Operating on an indexed column with a function Having missing or useless indexes Not being a join-fu master Not accounting for deep scans Doing SELECT COUNT(*) without WHERE on an InnoDB table Not profiling or benchmarking Not using AUTO_INCREMENT Not using ON DUPLICATE KEY UPDATEK |
|||||
전체댓글수 15
-
그네 11-10-09 01:22
소스보기
-
mysql 에 극한된 이야기도 있지만 대부분의 DBMS 에 다 해당되는 내용들입니다.
쭈욱 읽으면서 조금씩 추가를 해보면
1 인덱스
인덱스를 태우면 빠르다는것은 누구나 아는 사실이지만 인덱스도 똑똑한놈이 있고 좀 멍청한 놈이 있습니다.
데이타 비율이 그러한데요 이게 인덱스 좋다 그러니깐 무조건 걸어두면요 옵티마이져가 미쳐버립니다. 그래서 아무거나 쓸려고 듭니다. 멍청한 인덱스 사용하게 되면 쥐약입니다. (옵티마이져가 똑똑하면 그것마져도 선별해서 사용하겠지만...)
그러니 인덱스 좋다고 무조건 만들지 마세요,
2. 테이타 타입, 정규화
데이타 타입은 정말 해당 데이타에 가장 접합한 것을 쓰시고
왠만하면 하나의 row 를 작게 만들어야 합니다.
똑같은 데이타로 옆으로 긴거보다 아래로 긴게 훨씬 효율적입니다.
전에 가끔 한 테이블에 컬럼 몇개까지 만들수 있느냐 그러는걸 보면서 느끼는 것은 참 한심하다는 것
절대 옆으로 길게 만들지 마세요
3. mysql 속도 (지나가는 말, 근데 원문다시 보니 실행속도가 아니라 접속속도군요...)
아까 like %검색어% 이거 테스트 한다고 mysql 에서도 테스트 해 보았습니다.
똑같은 자료로 똑같은 쿼리문을 똑같은 시스템에서 똑같이 인덱스 없이 시행해 보았습니다.
pgsql 9.1.1, mysql 은 5.5.x (거의 최신 버젼입니다.)
아무런 인덱스 잆이 시행해 본 결과 myisam, innodb, 그리고 pgsql
pgsql 이 더 빨랐습니다. (쿼리캐시 미적용, 테스트방법이 잘못되었는지는 모르겠지만서도..) 테스트 자료 16만건 실 게시물 본문검색, 지금은 mysql 이 제일 빠르다는 편견 버리세요
이 이야기는 mysql 이 나쁘다 라는 말을 할려고 하는것이 아니라
mysql 이니 빠르니깐 그거만 믿지 말고 테이블구조, 인덱스 등 신경을 쓰시라는 의미로 받아주세요
4 디비 추상화
이 문제는 디비보다는 프로그램단(웹서버단) 의 문제로 보여집니다.
5. 함수사용
폼 좀 내 볼려고 함수들 마구마구 만들어서 많이 사용하는 사람들 있습니다.
함수는 아무곳에나 쓰는게 아닙니다. 함수는 함수가 필요한 곳이 있으며, 그 쓰임도 상당히 제한적입니다.
쿼리문에 한번 함수 쓰는거.. select 된 전 로우에 다 영향을 줘서 시스템 콜이 많이 발생합니다.
디비시스템의 속도는 I/O 와 시스템콜을 줄일수록 빨라집니다.
6. FK 에 대하여
mysql 에서는 잘 모르겠는데 보통 참조키를 걸게되면 parent 는 당연 unique 해야 하니 인덱스가 걸려 있어야 합니다만 참조하는 chiild 의 경우에는 제약 조건만 있고 인덱스가 자동 걸리지 않습니다.
꼭 확인하시고 직접 걸어주어야 합니다.
7. 잘못 알고 있는 것 하나 언급합니다.
쿼리문이 길면 속도가 느려질거라는 생각을 가지고 있는 사람들이 있는거 같습니다.
쿼리문이 길다고 속도가 느려지는건 아닙니다. 쿼리문이 얼마나 효율적으로 짜여졌나가 문제지
쿼리문 길이와는 별로 연관성이 없음을 인지하시고
아울려 조인하면 느려진다는 사람들도 간혹 있습니다. 어디에서 그런 이야기가 나왔는지는 모르겠지만
천만에 만만의 말씀입니다.
조인안하고 역정규화해서 필요한 컬럼을 본 테이블에 붙이면요
조인없이 그냥 select 만 하게 되니 빠를거 같지만요 절대 아닙니다.
그렇게 되면 row 크기가 증가해서 디스크 I/O 가 더 많이 발생합니다.
데이타 크기 줄일수 있다면 최대한 줄이시길 바래요. 조인하면 느려진다는 망상하지 마시고요
-
시우施雨 11-10-09 02:55
소스보기
-
Mysql 쓰는데 인덱스를 엉뚱한 걸 사용하기에 어이 없어서...
그 이후로 USE INDEX() 를 사용해 강제로 적용시키고 있습니다.
-
그네 11-10-09 18:32
소스보기
-
조인에 대한 한가지 추가해 봅니다.
select 하지 않는 테이블과의 조인이 필요할 경우에는
from 절에서 나열하지 말것! 입니다.
여기서 조인은 여러가지 의미가 있습니다.
단순히 inner, outer, cross 등의 조인 구문을 사용하는것을 포함하여
데이타를 서로 연결한다는 의미로 보셔야 합니다.
union, 프로시져호출, in, subquery, exists 등등이 모두 조인에 해당됩니다.
아무튼 select 에 필요하지 않는 테이블은 from 절에서 빼 주시는게
쓸데없는 드라이빙을 막아 불필요한 I/O 를 줄여줍니다.
예를 들면
select b.no, b.name
from a, b
where a.no = b.no
(and ...... 등등 조건들)
이렇게 작성 하지 말라는... (이런 스타일 정말 많이 봅니다....)
-
스타빈 11-10-17 11:39
소스보기
-
위의 조인과 정규화 부분에서는 실제 사례에서는 저렇게 쓰는게 정답이 아닐 경우도 참 많아요.
예를 들면, 100만건 조인하면 쿼리 죽어 납니다.
테이블을 나누지 않고 1개의 테이블로 가는것은 조인의 한계 때문에 키와 값으로 구분하는 해결 방법으로 쓰고 있는 No-SQL에 대한 개념과 가깝습니다. 세세하게 들어가면 다른 부분도 많지만..
위의 글에서 join 안 쓰는 짓. 정규화 안 하는 것은 가이드는 맞겠지만, 진리가 아니라는 ...
좋은 글 잘 보고 갑니다.
-
노마 11-10-27 10:49
소스보기
- 정말 좋은 글이네요... 다른 분들도 이걸 참고하셔서 쓩쓩~ 날라다니는 DB 구성하시길... 저도 좀 찔리는 부분이 몇 군데 있던데요..ㅋ풉~
-
ArchEviL™ 17-07-29 11:37
소스보기
-
- IP는 INT UNSIGNED로 저장해!! (아주 공감)
* 이럴 때 쓰라고 INET_ATON 함수가 아예 내장되어 있음.
이 부분은 좀 생각해야 할 게, 이미 IPv4가 고갈되어서 IPv6으로 이행 단계에 와 있고(IPv6은 무려 128비트입니다. INT는 물론이고 BIGINT로도 못 담습니다), 원문 작성자는 IPv6을 끄라고 권장하던데 이러면 네이티브로 IPv6 주소를 갖고 있는 기기는 사용이 불가하게 됩니다.
VARBINARY 컬럼에 HEX(INET6_ATON(addr)) 같은식으로 담는 게 더 좋을 듯합니다(도로 가져올 때는 INET6_NTOA(UNHEX(addr))로).
-
게시물 13,194건
1 2 3 4 5 6 7 8 9 10 
