[MySQL 쿼리 및 인덱스의 이해]기타 SQL

High_Priority
 - 높은 우선순위로 결과를 받아오도록 질의함
 - mysql> SELECT HIGH_PRIORITY * FROM USER

 

 

Query_Cache
 - 쿼리결과를 캐슁
 - 해당 테이블이 변경(Insert,Update)가 처리되면 모든 캐쉬 Flush

 

 

Low_Priority
 - INSERT 시 옵션을 주면 큐의 모든 Select가 완료될때까지 기다림
 - 슬레이브(백업)서버의 경우 서버옵션으로 지정해놓으면 동기화는 느려도 Select는 빨라짐

 

 

대용량 INSERT
 - MultiRow를 사용하면 성능이 높아짐
 - MyISAM의 경우 8배, InnoDB에서는 30배 성능 향상
 - 대용량 처리시 disable_key를 사용해서 인덱스 생성을 멈추고 완료후 활성화
 

DELETE 성능향상
 - DELETE QUICK : MyISAM에서만 사용가능. 내부적인 인덱스 정리작업 처리안함
 - 모든 데이터를 삭제할 경우에는 Drop Table 처리
 - MyISAM 경우 Truncate Table를 사용가능(내부적으로 Drop Table처리)

 

Count(*)은 위험하다
 - Count를 위해서 별도의 테이블을 만드는 것이 좋은 해결책
 

Count(*) 쿼리가 필요할까?
 - Google같이 대략적인 COUNT로 해결
 - Limit를 사용하여 1000개 이하는 정확한 수를 1000개 이상은 1000+라고 표시

 

 

Count(*)의 속도 높히기
 - MyISAM는 전체 테이블 카운터를 가지므로 빠른 결과
 - InnoDB는 풀스캔처리
 - Count결과에 영향을 주지 않는 Join을 제거
 - 인덱스를 사용하도록 처리(UsingIndex)가 explain의 extra의 결과에 나오도록 처리

 

 

높은 Limit값 다루기
 - SELECT * FROM users ORDER BY last_online DESC limit 100000,10
 - 처음10만row 검색후 버리게 되므로 비용이 높다
 - DDos공격대상
 - 검색엔진 봇은 높은 Page를 자추 찾아들어감
 - 빠르게 동작하는걸 보장하지 못할경우 제한하는 방법 : Google 등

 

 

높은 Limit : 가능한 결과를 미리 생성
 - SELECT * FROM sites ORDER BY visits DESC LIMIT 100,10
 - 아래와 같이 레이팅을 처리하는 테이블을 추가하는 방법
 - SELECT * FROM sites_rating WHERE position BETWEEN 101 and 110 ORDER BY position
 - 실시간이 필요 없는 경우 통계테이블을 추가하면 성능 향상이 있음

 

 

배치작업에서의 LIMIT의 사용
 - 배치작업에서는 Limit n,10000 보다 Where id Between n and n+99999 형태로 변경
 - 정확한 개수는 필요 없을 경우
 

파생테이블(Derived Table)사용하기
 - 파생테이블이란 : WHERE id IN (SELECT ID FROM tb)
 - 부분적으로 인덱스를 사용할 수 있도록 함
 - 파생테이블 두개를 조인하게 되면 MySQL에서는 풀조인을 하므로 주의

 

 

FROM절의 서브쿼리
 - SELECT ... FROM (SELECT ...) WHERE ...
 - 서브쿼리를 완전 별개로 처리함. 즉 Temp Table로 사용. Temp Table은 인덱스 사용암함
 - Join으로 분할 해야함

 

 

Sorting시 File Sort 피하기
 - SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5
 - MySQL에서는 정렬시 인덱스 사용못함(모든 컬럼에서 = 비교를 하는 경우를제외)
 - 정렬작업을 위해서 File Sort함
 - 데이터 양이 많을 경우 시간이 오래 걸림
 - UNION으로 쪼개면 인덱스만 사용하게 됨
 - (
   SELECT * FROM TBL WHERE A=1 ORDER BY B LIMIT 5
   UNION
   SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5
   ) ORDER BY B LIMIT 5

 

[MySQL 쿼리 및 인덱스의 이해]인덱스 사용 전략

인덱스 사용전략
 - Mysql> SHOW INDEX FROM index_name \G
 
 - 필드 해석
   Table : 테이블명
   Non_unique : 중복이면 1 아니면 0
   Key_name : 인덱스에 할당된 키 이름
   Seq_in_index : 멀티컬럼인덱스일 경우 순서
   Column_name : 컬럼이름
   Collation : 인덱스의 정렬방식 A(Asc), D(Desc-지원안함) 이나
   Cardinality : 인덱스에서 유니크한 값의 개수. 값이 낮다는 것이 중복이 많다는 것(예:성별은 남,여로 2)
   Sub_part : 컬럼이 부분적으로 인덱싱 되었을때 길이
   Packed : 인덱스의 압축여부. 압축하지 않았을때 Null
   Null : 컬럼이 Null값을 가질수 있으면 Yes, 아니면 NO
   Index_type : 인덱스의 타임(RTREE,FULL TEXT,HASH,BTREE)

 

 

인덱스와 관련된 로그
  - 로그를 통해서 추가적인 정보획득 가능
  - "--log_queries-not-using-indexes" 슬로우 쿼리 로그에 인덱스를 사용하지 않은 쿼리 로그를 남김
  - 슬로우 로그사용시 "-log-slow_queries"옵션을 통해서 활성화 해야함

 

MyISAM의 인덱스 구조
 - PrimaryKey : Leaf 노드에는 ROW Number가 들어 있다
 - Secondary Index : Leaf 노드에는 ROW Number가 들어 있다.
 - Key Cache :
    인덱스를 메모리에 저장.
    인덱스를 디스크에서 읽을때보다 10배빠름.
    없을경우 시스템 캐쉬사용.
    전체물리메모리의 512M-1G정도 지정.
   

InnoDB의 인덱스 구조
 - 내부적으로 Clustered인덱스라는 구조를 통해 저장. 인덱스의 순서에 따라 물리적 데이터 저장.
 - 프라이머리키가 존재하면 : Clustered인덱스
 - 프라이머리키가 없으면 : 유니크 인덱스를 자동으로 지정
 - 프라이머리키, 유니크 없으면 : 자체적으로 rowID라는 6바이트 유니크 컬럼을 생성해서 입력순서에 따라 생성. show index로 보이지 않음
 - Clustered 인덱스 : Leaf노드에 데이터가 저장됨
 - 다른 모든 인덱스는 Leaf노드에 Clustered인덱스 주소를 가짐
 - 어떠한 컬럼이 Clustered 인덱스가 되느냐가 중요 (크기가 크지 않으며 자주사용되는 컬럼을 이용)

 

 

인덱스 선정 절차
 - 해당 테이블 엑세스 유형 조사
 - 대상 컬럼의 선정 및 분포도 분석
 - 반복 수행되는 엑세스 경로의 해결
 - 인덱스 컬럼의 조합 및 순서 결정
 - 시험생성 및 테스트
 - 수정한 필요한 애플리케이션 조사 및 조사
 - 일괄 적용

 

 

인덱스의 선정기준
 - 분포도가 좋은 컬럼은 단독적으로 생성
 - 자주 조합되어 사용되는 경우 결합인덱스
 - 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스간의 역활분담
 - 가능한 수정이 빈번하지 않는 컬럼
 - 기본키 및 외부키(조인의 연결고리 컬럼)
 - 결합 인덱스의 경우 컬럼 순서 주의
 - 반복수행 되는 조건은 가장 빠른 수행속도를 내게 할 것

 

 

인덱스의 활용시 고려사항
 - 추가된 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음
 - 지나치게 많은 인덱스는 오버헤드 발생
 - 넓은 범위를 인덱스 처리시 많은 오버헤드발생
 - 옵티마이저를 위한 통계데이터를 주기적으로 갱신
 - 인덱스의 개수는 적절히 생성
 - 분포가 양호한 컬럼도 처리범위에 따라 분포도가 다를수 있음
 - 인덱스 사용원칙을 준수해야 인덱스가 사용되어진다
 - 조인시 인덱스 사용여부에 주의
 

Index Cardinality
 - SHOW INDEX에서 확인 가능
 - 가능하면 높은 값을 유지하도록 하는 것이 좋음
 - 높은 컬럼에 인덱스가 필요한 경우 복합 인덱스를 고려
 

문자형 인덱스 VS 숫자형 인덱스
 - 문자형 인덱스 성능 < 숫자형 인덱스 성능
 - 숫자형 컬럼이 작은 싸이즈

최적의 컬럼 타입 선택을 위한 툴 - Procedure Analyse
 - 사용법
   SELECT * FROM 데이블명 PROCEDURE Analyse(처리할컬럼수)

 

 

다중컬럼사용시 빈번한 실수
 - Index(columnA, columnB) 일 경우 WHERE columnB='ABCD' 일경우 인덱스 사용안함
 - WHERE columnA LIKE '%rane' 일 경우 와일드카드가 앞에 있으므로 인덱스 사용안함
 - ORDER BY columnB, columnA 일 경우 인덱스 사용안함

 

[MySQL 쿼리 및 인덱스의 이해]쿼리 옵티마이저

쿼리 수행경로

   일반쿼리 > Update,Delete일경우       > Parser > Optimizer > Excutor > QueryCache
              > Select일경우 > QueryCache > Parser
   mysql_prepared_statment                              > Optimizer

 - Parser는 질의를 바이너리형태로 변경해서 Optimizer에 전달
 - Prepared는 QueryCache를 현재는 사용하지 않음.
 - Prepared는 10%정도의 성능향상이 있으나 Select에서 캐쉬를 이용하지 않으므로 업데이트나 입력이 많을때만 사용
 - Executor은 Having, Order By, Group By, Limit 등의 존재여부에 따라 공간 할당
 - 처리된 결과는 QueryCache에 저장됨

옵티마이저란?
 - 가장 빠른 길을 찾아가는 네비게이션
 - MySQL은 Cost-Based(비용기반)사용. 예전 DB들은 Rule_Based(룰기반)를 사용
 - 옵티마이저는 항상 최적의 답을 찾는것은 아님
 - 질의문의 Logic Fomula를 Data statistics와 Metadata를 참고해서 최적의 방법을 도출
 

비용기반 옵티마이져
 - 비용이란것은 디스크 엑세스라고 볼수 있음
 - 비용의 단위 = 페이지(4Kbyte) 단위, 랜덤한 읽기
 - 비용계산을 위한 데이터 통계 : 데이터의 수, 데이터의 카디널리티, 키분포도, row와 key의 길이
 - 비용계산을 위한 스키마의 요소 - 유니크, Null유무

 

옵티마이저 진단 및 튜닝
 - 사용하는 통계데이터의 업데이트는 자동으로 처리함
 - 유저가 수동업데이트 할때는 "Analyze Table" 커맨드 사용
 - 정기적으로 실행(단 대용량에서는 ReadLock이 걸리므로 사용주의가 필요)
 - MyISAM, InnoDB, BDB에서 사용가능

 

데이터베이스(DB)의 효율적인 SQL 사용법

고려사항
 - SQL의 성능을 악화시키는 최대 요인은 불필요한 I/O
 - 최소의 Block Read를 통해서 조회
 - 결과 UI의 페이징처리로 데이터 범위를 최소화
 - 간결한 Query

 

 

효율적인 사용법

 - 조건절에 사용되는 컬럼에 외부적 변형 금지

  WHERE SUBSTR(DNAME,1,3)='ABC'   ===> WHERE DNAME LIKE 'ABC%'
  WHERE SAL*12=1200   ===>  WHERE SAL=1200/12
  WHERE TO_CHAR(HIREDATE,'YYMMDD')='940101' ===> WHERE HIREDATE=TO_DATE('940101','YYMMDD')

 

 - 컬럼비교시 같은 데이터 타입으로 비교
  CHA CHAR(10)
  NUM NUMBER(2,3)
  VAR VARCHAR2(20)
  DAT DATE

 

  아래와 같이 자동으로 변경됨으로 주의!!

  WHERE CHA=10 ==자동==> WHERE TO_NUMBER(CHA)=10
  WHERE VAR=10 ==자동==> WHERE TO_NUMBER(VAR)=10
  WHERE NUM LIKE '9410%' ==자동==> WHERE TO_CHAR(NUM) LIKE '9410%'

 

 - NULL값의 비교시
  WHERE ENAME IS NOT NULL ===> ENAME>'' /*SPACE*/
  WHERE COMM IS NOT NULL ===> COMM>0  /*숫자형이며 양수만 가능*/
  WHERE COMM IS NULL ===> COMM의 디폴트값을 0으로 하고 COMM=0으로 검색

 

 - 부정형 비교시
  WHERE EMPNO<>'1234' ===> WHERE NOT EXISTS (SELECT 'X' FROM EMP WHERE EMPNO='1234')

 

 - 기타
  힌트 사용 제한
  SELECT 절에는 반드시 필요한 컬럼만 나열
  반드시 필요한 경우에 대해서만 Outer Join 사용
  불필요한 Distinct 사용제한
  가능하다면 UINON 보다 UNION ALL을 사용
  복잡한 OR 사용은 IN 이나 UNION ALL로 변경