MySQL 쓰면서 하지 말아야 할 것 17가지

HYEONG HWAN, MUN/ 10월 22, 2014/ 미분류/ 47 comments

https://blog.lael.be/post/370

*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% 정도 빠름
- 데이터 입력이 많다면 더 커질 수 있음

on duplicate key update는 mysql5.7.21이전 버전에서는 데드락을 발생시킬수도 있음.

https://stackoverflow.com/questions/46379969/mysql-crash-with-update-on-duplicate-key-query

하지 말아야 할 것 총정리
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

47 Comments

  1. Pingback: [펌]MySQL 쓰면서 하지 말아야 할 것 17가지 | poteto's thinks…

  2. 잘 읽었습니다. 아직 반 정도만 이해했지만,
    나머지 부분들도 이해 되도록 가져가서 두고두고 체크해 보겠습니다.

    문제 발생시 알려주시면 자삭 하겠습니다.

    1. 아뇨 지식은 공유되어야 합니다. 막막 퍼가세요~
      자세한 사항은 블로그 하단에 나와있습니다.

      1. “지식은 공유 되어야합니다.”
        하나 더 다시 깨우치고 갑니다 ㅎㅎㅎㅎ

  3. ON DUPLICATE KEY UPDATE를 써야된다고 말씀하시지는 거죠?

    1. 네 ON DUPLICATE KEY UPDATE 는 써야합니다.
      연산 횟수가 많이 줄어들어서 소프트웨어의 속도가 빨라집니다. 단순히 50% insert, 50% update 가 일어난다고 가정해보고 계산해보세요.

  4. 좋은 글 감사합니다.
    저도 제대로 이해하지는 못했지만, 공부를 열심히 해야겠다라는 것을 깨닫습니다.

  5. 죄송합니다.
    이제 데이터베이스를 시작한 학생입니다. 질문이 있어서 이렇게 글을 남깁니다.

    IP를 INET_ATON 함수를 써서 정수로 저장하는 특별한 이유가 있나요?

    1. 더 적은 공간을 사용하게되고 따라서 Query 시 더 빠릅니다.
      예를들어 192.168.100.151 아이피를 정수로 변환하면 3232261271 가 됩니다. (http://www.silisoftware.com/tools/ipconverter.php?convert_from=192.168.100.151)
      192.168.100.151 는 char자료형 15byte 이고, 3232261271 는 int자료형 4byte 입니다. 따라서 INSERT, SELECT, WHERE 조건절 에서 모두 빠릅니다.

      1. IPv6 호환을 위해서 binary(16)을 써도 되지 않을까 싶네요.. 먼 미래의 이야기인가?

        1. ipv6 활성화가 되면 바뀔 수도 있을건데 아직은 고려하지 않아도 될 것 같습니다.
          제가 작성한 다른 글인 “서버세팅” 글을 보시면 세팅 중 ipv6 기능을 끄도록 가이드하고 있습니다.
          제 개인적인 생각으로 현 시점에서 서버에서 ipv6 를 지원한다는건 잠재적 보안에 문제가 생길거라고 봅니다.

  6. 퍼가겠습니다. ㅎ.ㅎ

  7. 좋은정보 감사합니다. 앞으로 더 더욱 공부해야 될 것 같아서 정보좀 가져가겠습니다. 공유 감사드려요!

  8. 궁금한 점이 있는데, 한가지 질문을 드려도 될까요?
    대용량 테이블 같은 경우,
    pk 를 integer 로 했을 경우, 그 범위를 초과할 수 있을텐데요.
    pk의 데이터 타입을 어떻게 해야 하는지 궁금합니다.
    네이버 블로그는 아래의 링크의 글번호의 경우, 어떠한 자료형으로 사용하는지 궁금합니다.
    http://blog.naver.com/tangkwon/220957796929

    1. pk의 기본값인 (signed) int 의 최대치는 21억입니다. unsigned 옵션을 줄 경우 42억까지 저장할 수 있습니다.

      만약 42억건이 넘는 데이터를 저장하고 싶으시면 bigint 타입으로 설정하시면 됩니다.
      (signed) bigint는 최대 922경 까지 저장할 수 있습니다.
      (unsigned) bigint는 최대 1844경 까지 저장할 수 있습니다.

      일반적으로, PK int 가 최대 값인 21억에 도달할 일이 거의 없습니다. 도달하기 전에 속도 문제로 테이블 분할 같은 작업을 하게 됩니다.
      통상적으로, 100만 레코드를 초과했을때 빅데이터라고 부릅니다.

  9. 정리해서 퍼갑니다~ 유용한 정보 감사합니다~ 오늘 처음 방문했는데 대단하시네요. 자주 들리고 조각조각 참고 많이 하겠습니다!

    1. 반가와요! 조각조각 공유할 정보 있으면 틈틈이 적도록 하겠습니다!

  10. 좋은글 재미있게 잘보고 갑니다.

    1. 와우 책도쓰시고 훌륭하신 분이시네요.
      저는 IT에 관심있는 사람의 러닝커브를 완만하게하는 글 위주로 작성하고 있습니다.

  11. 좋은정보 감사합니다.

  12. 안녕하세요. 좋은 글 잘 읽었습니다.

    “auto_increment 안 쓰는 것” 에 대해서 궁금한 사항이 있습니다.

    예를 들어 어떤 사용자의 고유한 아이디 (usrID) 를 auto_increment 값으로 설정 후
    auto_increment 기능을 사용하지 않고 사용자 아이디와 특정 컬럼 (orderID)으로
    복합인덱스 PK를 생성할 경우 PRIMARY KEY (`usrID`,`orderID`) 를 생성할 경우에도
    auto_increment 컬럼이 필요하다고 생각하시는 궁금합니다.

    필요하다면 어떤 이유에서 일까요?
    위와 같이 생성한다면 PK 값이 순차적으로 증가하지는 않습니다.

    1. composite key 인 `usrID`,`orderID` 를 PK 로 지정하고, `orderID` 를 auto_increment 로 지정하면, 사용자별로 `orderID` 가 계산되어 매겨지겠죠.
      이것은 MyISAM 엔진일 경우와 auto_increment 필드가 PK 의 두번째 컬럼일 경우에만 동작합니다.
      하지만 이것은 복제 구성시 문제를 일으킬 수 있습니다. https://dev.mysql.com/doc/refman/5.7/en/replication-features-auto-increment.html
      모든 상황에서 auto_increment 를 쓸수는 없겠죠.

  13. 안녕하세요. 좋은 글 잘 읽었습니다.

    “auto_increment 안 쓰는 것” 에 대해서 궁금한 사항이 있습니다.

    최적화 부분에서 쓰는것이 좋다고 나오는데요.
    구체적으로 쓰게 된 경우와 안 쓴 경우의 퍼포먼스 차이 비교 같은걸 알 수 있을까요?

    감사합니다.

    1. 추가로…
      그럼 모든 테이블에 auto_increment를 쓰는 PK를 만드는 것이 , 그렇지 않는 것보다 성능과 디스크관리(?) 면에서는 뛰어난게 맞는건가요?
      만약 DB에서는 이미 unique key가 있는데 성능을 위해서 추가로 auto_increment를 쓰는 pk를 만드는것이 맞는 방향인지도 알고 싶습니다.

      감사합니다.

      1. 데이터 크기가 작거나 서버가 충분히 고사양이면 둘의 차이는 크지 않습니다.
        auto_increment PK 와 UniqueString UUID 를 비교하자면,

        우선 데이터 저장크기가 차이나고, 실행속도가 아주 조금 차이납니다.
        insert 시에 data ordering 이 일어나는데, 숫자형 정렬이 조금 더 빠릅니다.
        쿼리시에 PK 는 “최근 30개” 같은 쿼리를 사용가능하지만 UUID는 그런 것을 사용 못합니다.
        같은 이유로 PK 를 사용한 쿼리는 실사용에서 캐쉬를 더 잘타는 경향이 있습니다.

        요즘은 DBMS가 좋아지고 하드웨어가 좋아졌으므로,
        unique 의 무결성을 보장할 수 있다면 UUID 방식을 사용해도 큰 문제 없습니다.

  14. 선배님
    디비 책이나 디비 강의 영상 추천 좀 부탁드려도 될까요?

    1. 안녕하세요.
      천재님의 수준이 어느정도인지 모르겠습니다.
      생활코딩 https://opentutorials.org/course/195 의 강의를 보는 것도 좋을 것 같습니다.

  15. HYEONG HWAN, MUN 안녕하세요
    문의글을 남기려고합니다.

    온라인 쇼핑몰에 Mysql 5.5 버전을 사용중인데 쿼리캐시 값을 아래와 같이 설정 해서 사용중입니다.

    *** Query Cache ***
    query_cache_type = 1
    query_cache_size = 1G
    query_cache_limit = 32M

    Mysql 튜닝(최적화)를 진행하려고 합니다.

    1. 여러 정보를 보아야 합니다. select/insert 비율이나 slow query, 서버사양, hit rate 등을 보고 조절해야합니다.

      우선은 phpmyadmin 을 설치하시고 root 로 로그인하신다음에 [상태]>[시스템분석/조언] 을 살펴보세요.
      query cache hit rate 값을 보고 size 와 limit 을 조정하세요.
      고사양 cpu 와 큰 메모리를 사용한다면 결과셋이 크더라도 캐쉬를 하도록 설정하는게 좋습니다.

  16. 좋은 글 잘 봤습니다.

    select/insert 비용 등 프로파일링을 위해서 사용하기 좋은 도구를 추천해주실 수 있나요?

    1. 가장 유명한 프로그램인 phpmyadmin 을 추천합니다. 클릭 몇번으로 서버의 모든 쿼리의 비율을 확인하고, 특정 쿼리의 평가방법, 쿼리 프로파일링 기능을 실행할 수 있습니다.

  17. 안녕하세요 좋은 글 감사합니다.
    질문이 있어 글 올립니다.

    Q. mysql(mariadb)에서 auto_increment를 쓸 경우, Thread-Safe 하나요?

    현재 PostgreSQL에서 MariaDB로 포팅하고 있습니다. 예전에 PostgreSQL에서 serial(auto_increment)을 사용할 때 multi-user를 핸들링 못하고, 중복 값이 생성되어, PK 무결성 제약조건으로 요청이 거절되더라구요(Not-Thread-Safe). 해당사항은 SEQUENCE, nextval()로 대체해서 해결했습니다만, MariaDB에는 Sequnce가 10.3부터 있습니다… 그 이하 버전을 포팅할듯합니다.

    위의 질문과 같이 Thread-Safe하게 auto_increment를 쓸 방법이 없나요??

    도와주시면 정말 감사하겠습니다.

    1. 보내주신 메일주소로 답변드리겠습니다.

  18. 하루 약 1억건 파싱해서 넣을경우 MariaDB도 괜찮나요?

    NoSQL은 사용안할 거 같구.

    PostgreSQL이랑 고민되네요

    1. 그건 제가 답할수 없을것 같네요.
      하루 1억건 파싱 insert는 단순하게 생각할 문제는 아닙니다.

      1. 일단 궁금한 부분은 insert 보다

        select 시에 속도 문제가 궁금하네요..

        하루 1억이면 1년이면 365억인데…

        샤딩 무조건 해야겟죠?

        1. insert 를 고려하지 않고 select 를 생각할 수 없습니다.
          select 의 속도는 테이블 구조 및 insert 시의 indexing 에 따라서 달라집니다.
          쉽게 말해서 365억개를 다 스캔하지 않고 결과를 낼 수 있도록 insert 및 indexing 이 되어 있다면 select 속도가 빨라진다는 것이죠.
          MySQL은 indexing 탐색에 B+Tree 방법을 사용합니다. B+Tree방식은 search 에 시간복잡도가 O(logN) 입니다.
          [B+Tree] 를 참고해서 예상시간을 계산해보세요.

  19. 좋은정보 감사합니다. 업무에 참고를 하기위해 정보좀 퍼가도 되겠습니까?. 공유 감사드립니다.

    1. 안녕하세요?
      재배포/개작 모두 허용합니다.감사합니다.

  20. DB를 이것저것 쓰며 RDBMS 의 기본상식(?) 만 가지고 버텨왔는데.. 새로 이직한 회사에서 MySQL 로 서비스를 만들고 특히 성능적인 부분 때문에 많은 문제를 경험하게 되니.. 이 글이 공감되네요. MySQL 이 여태껏 버텨왔던건 좋은 성능과 신뢰성을 그만큼 인정받았기 때문이죠. 앞으로도 한동안 MySQL의 인기는 여전할거 같네요. 좋은 글 감사합니다. 많은 도움이 되었습니다.

    1. 대부분의 성능문제는 첫번째 항목인 “작게 생각하기”를 잘 고려하면 해결이 되곤 합니다.
      새 회사에서 겪고 계신 도전과제들이 실력향상에 많은 도움이 될 것이라 생각합니다. 화이팅입니다!

  21. 안녕하세요. duplicate on update 구문은 mysql5.7.21이전 버전에서는 데드락을 발생시킬수 있다고 하네요. 참고용으로 적어두면 좋을것 같아 글 남깁니다

    https://stackoverflow.com/questions/46379969/mysql-crash-with-update-on-duplicate-key-query

    1. 공유해주셔서 감사합니다. 본문에 추가하도록 하겠습니다.

Leave a Comment

작성하신 댓글은 관리자의 수동 승인 후 게시됩니다.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*