- 파티셔닝은 단순히 쓸게 아니었군
- 5.1이상에서 지원되는지라 오라클보다 기능도 작고 주의할 점도 많다.
- InnoDB 를 쓰기때문에 5.1을 쓰지만 파티션락프루닝은 크게 고려사항이 아닌듯?
- 그외 중요한 이슈들이 많았군... ㅠ



* 파티션을 사용하는 이유

a) 단일 INSERT,  단일 SELECT, 범위 SELECT의 빠른 처리

b) 데이터의 물리적인 저장소를 분리
  b-1) 파티션을 통해 파일의 크기를 조절
  b-2) 각 파티션별 파일들이 저장될 위치를 구분해서 지정

c) 이력 데이터의 효율적인 관리
  - 로그테이블에서 불필요해진 데이터를 백업&삭제하는 작업은 상당히 고부하 작업.
  - 로그테이블을 파티션테이블로 관리한다면 삭제작업은 단순히 파티션을 추가하거나 삭제하는 간편한 방법으로 처리 가능
  - ex) 201307 처럼 '년월' 이름으로 파티셔닝해서 파티셔닝을 지우는 방법

* 단어
 a) 파티션 프루닝 : 접근이 불필요한 파티션은 전혀 접근하지 않는것, 실행계획은 EXPLAIN PARTITIONS 를 사용


* MySQL파티션 제한사항

0) 참고 : http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html

a) 5.5 이상에서만 '숫자 타입' 뿐 아니라 '문자, 날자'타입 모두 파티션이 가능 (5.1이하는 숫자형만 가능)

b) KEY PARTITION은 Hash함수를 MySQL이 직접 선택하기 때문에 칼럽 타입 제한이 없음

c) 최대 1024개의 파티션을 가질수 있음 (서브 파티션 포함)

d) 외래키, FULLTEXT index 사용불가 등등...

e) 정상적인 프루닝을 지원하는 함수는 YEAR(), TO_DAYS(), TO_SECOND() (5.5이상) 단 3개 뿐이다.!

f) 파티션 단위로 인덱스를 변경할 수 없다


* MySQL파티션 주의사항

a) 파티션 테이블은 (파티션 갯수 * 2~3개)의 파일을 가진다. 예를 들어 1024개의 파티션을 가진 테이블을 프루닝으로 2개의 파티션에만 접근해도, 

   일반은 1024개의 모든 파티션의 데이터 파일이 오픈되어야 한다. 그래서 파티션을 크게 쓰는 경우에 OPEN-FILES-LIMIT를 적절히 크게 설정(5.6기본:5000)

b) 파티션 테이블 성능이 일반 테이블보다 더 떨어질 수 있다.
 b-1) 파티션을 선별하는 파티션 프루닝은 쿼리 최적화 단계에서 수행되어서 알 수 있다.
 b-2) 쿼리 최적화는 '테이블 잠금' 이후에 수행된다. 즉, 테이블 잠금에서는 프루닝을 알 수 없기 때문에 모든 테이블의 파티션이 잠금상태가 된다.

 b-3) 그래서 파티션 갯수가 많을 수록 '열고' '잠금'하는 작업이 많아져서 더 느려질 수 있다. 
   - http://blog.naver.com/PostView.nhn?blogId=seuis398&logNo=70105826106
   - 5.1v, 5.5v에서 발생. 
   - 5.6.6 에서는 처리됨 (  MySQL 5.6.6 implements partition lock pruning ,  http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locking.html )
   - 하지만 InnoDB 테이블에서 테이블 락은 큰 역할은 아니다?
   - 파티션이 많은 테이블에 INSERT,DELETE하려면 처음부터 LOCK TABLES 명령을 사용하라.


* 파티션 테이블의 검색

a) 아래의 2가지가 효율적인 파티션 테이블 검색에 미치는 조건이다
  a-1) WHERE 절의 조건으로 검색해야 할 파티션을 "선택" 할 수 있는가?
  a-2) WHERE 절의 조건이 인덱스를 효율적으로 사용(인덱스 레인지 스캔) 할 수 있는가?

b) 케이스
  b-1) 파티션 선택 가능, 인덱스 사용 가능
  - 가장 효율적, 파티션 갯수에 상관없이 꼭 필요한 파티션만을 인덱스 레인지 스캔

  b-2) 파티션 선택 불가, 인덱스 사용 가능
  - 파티션 갯수만큼 테이블에 대해 인덱스 레인지 스캔을 한 다음 "병합"하는 것과 같다 

  b-3) 파티션 선택 가능, 인덱스 불가
  - 선택된 파티션을 풀 스캔

  b-4) 파ㅌ션 선택 불가, 인덱스 불가
  - 가장 비 효율적, 테이블 모든 파티션 검색, 풀 테이블 스캔 


* MySQL의 파티션 테이블과 인덱스

a) MySQL의 파티션 테이블의 인덱스는 "전부 로컬 인덱스!!!"

b) MySQL은 파티션 테이블의 글로벌 인덱스는 "지원 안한다!!"

c) 파티션된 테이블에서 ORDER는 '파티션 내에서만 소팅'되므로 큰 의미가 없다. 
 -p201308, p201309 에 order by day로 해도.   2013-08-01, 2013-09-01 순서대로 되지 않는다. (각 파티션별로 소팅)
 -where로 각 파티션에
 -실제 MySQL 처리 방법

 c-1) 각 파티션에서 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위큐에 임시 저장

 c-2) 다시 큐에서 필요한 순서(인덱스의 정렬순위)대로 데이터를 가져간다.

* 특정 Patition의 Data를 Truncate 하는 방법  : ALTER TABLE CachedList TRUNCATE PARTITION p0;



신고

7.4.7 GROUP BY, ORDER BY, 7.4.9 서브쿼리

RealMysql 2013.01.07 17:58 posted by dev.bistro

7.4.7 GROUP BY

- "특정 칼럼의 값으로 레코드를 매핑", "각 그룹의 집계된 결과를 하나의 레코드로 조회할 때 사용"


* MySQL은 그룹 키가 아닌 칼럼이더라도 집합함수없이 GroupBy에 사용할 수 있지만, 제대로 된 사용은 아니다.

- SELECT  first_name FROM employees GROUP BY gender;

-- gender로 그루핑을 해서 M/F 2개의 결과로 묶지만 SELECT는 first_name이다. 이게 M/F그루핑한것중 최소/최대/중간값인지 알수없다.

-- "이러한 사용은 가독성 저하" ==> 반드시 GROUP BY에 명시되지 않은 칼럼은 집합함수로 감싸라.

-- 이렇게 집합함수를 감싸서 사용할 수 있게 하는것을 'FULL_GROUP_BY' , 옵션으로 enable가능하다.


* GROUP BY .. ORDER BY NULL

- MySQL의 GROUP BY는 '그루핑 칼럼 순'으로 정렬까지 수행한다. 정렬이 필요없다면 "ORDER BY NULL"을 추가


* GROUP BY col1 ASC col2 DESC

- MySQL의 GROUP BY가 정렬도 수행하기 때문에 이처럼 정렬 순서도 명시할 수 있다.

- 이것도 ORDER BY와 동일하기 때문에, 정렬순서가 혼용되면 인덱스 사용이 불가능하다.

- 많은 사용자가 익숙치 않기 때문에 그냥 ORDER BY에서 사용하자.


* GROUP BY .. WITH ROLLUP

- 그루핑된 그룹별로 TOTAL COUNT를 가져올수 있는 기능.


* 레코드를 칼럼으로 변환해서 조회

- 레코드를 칼럼으로 변경

- 하나의 칼럼을 여러 칼럼으로 분리 을 할 수 있다 (ref: 468page)


7.4.8 ORDER BY

* 만약 ORDER BY가 없다면 기본 정렬 상태는 ?

- 인덱스를 사용하면, 인덱스의 정렬 순서대로

- 인덱스 사용 X, 풀테이블 스캔

-- MyISAM : 저장된 순, 단 INSERT순은 아니다. 빈 공간이 있으면 거기에 저장하기 때문

-- InnoDB : 항상 PK로 클러스트링 되어서, 풀 스캔의 경우 기본 PK 순으로 가져옴

- SELECT가 임시테이블을 거친다면 순서를 예측할 수 없다.



* ORDER BY 사용법 및 주의사항

- ORDER BY 2 : 2번째 필드로 정렬

- ORDER BY "var" : MySQL은 ""로 묶인 문자열은 무시한다. <- 주의하자.


* ORDER BY RAND()

- 단순추첨, 임의사용자 조회를 위한 가장 단순한 방법

- '절대' 인덱스를 이용할 수 없다.


* 여러 방향으로 동시 정렬

- 오름/내림차순이 혼용되면 인덱스를 이용할 수 없다.

- 숫자타입은 반대부호를 붙여서 소팅에 이용할 수 있다(ref 471page)

- 또는 여러 쿼리로 나누어서 처리를 한다.

-- 두번째쿼리에 반복횟수가 작은 경우

-- 정렬갯수가 많아서 디스크를 이용할 경우 유용하다.


* 함수나 표현식을 이용한 정렬

- SELECT * FROM e ORDER BY emp_no+10 처럼 식은 인덱스 이용안됨


* ORDER BY를 튜닝하기 힘들때는 WHERE을 최적화해서 건수를 최대한 줄인다.




7.4.9 서브쿼리

- 가독성은 좋지만, 최적실행은 되지 않는다.


* Correlated subquery

 - 외부에서 정의된 테이블을 참조해서 검색을 수행

 - 독립적으로 실행되지 않고, 외부테이블 이후 순차실행


* Self-Contained subquery

 - 외부쿼리와 상관없이 항상 같은 값을 반환

 - 외부 쿼리보다 먼저 실행 => 외부 검색을 위한 상수로 사용됨이 일반적


* 서브쿼리의 제약 사항

 - LIMIT 절은 사용할 수 없다.

 - 서브쿼리를 이용해 한 테이블을 동시에 읽고/쓰기 할 수 없다.


* SELECT 절에 사용된 서브 쿼리

- 일반적으로 SELECT에 사용된 서브쿼리는 결과가 1개를 반환해야한다.


* FROM 절에 사용된 서브 쿼리

- FROM 절에 사용된 서브 쿼리는 항상 임시테이블을 사용, 비유율적일때가 많다.

- 서브쿼리가 반환하는 결과가 크거나, 대용량 칼럼이 있다면 디스크에 임시테이블을 만들고, 병목이 생긴다.

 




신고

7.4.6. JOIN

RealMysql 2013.01.04 20:07 posted by dev.bistro


7.4.6 JOIN


* JOIN의 순서와 인덱스

인덱스 탐색(Index seek) => 인덱스 스캔(Index scan) => 최종 레코드 Read


(일반적으로 가장 먼저 읽히는 테이블을 '드라이빙 테이블'이라 하고 가끔은 아우터 테이블을 드라이빙 테이블이라 하기도 한다)


- 드라이빙 테이블을 읽을 때는 '인덱스 탐색'1번 -> 스캔만 계속 실행

- 드리븐 테이블은 '탐색', '스캔'을 레코드 건수만큼 반복한다.

-- 그래서 1:1로 조인되더라도 드리븐 테이블을 읽는것이 훨씬 부하가 크다.

-- 그래서 옵티마이저는 '드리븐 테이블'을 최적으로 읽을 수 있게 최적화한다.



* JOIN 칼럼의 데이터 타입

- JOIN 에서도 각 칼럼의 타입이 일치하지 않으면 인덱스가 효율적이지 않다.


* OUTER JOIN의 주의사항

 OUTER로 조인되는 테이블의 칼럼에 대한 조건은 모두 ON 절에 명시해야한다.


* INNER JOIN과 OUTER JOIN의 선택

- INNER : 조인의 양쪽 테이블에 모두 존재하는 경우만 반환

- OUTER : OUTER에 존재하면 무조건 반환한다.

- OUTER가 느리다는 편견은 버려라, 성능의 차이는 거의 없다. 중요한것은 업무rule에 따라 선택하는것.


* FULL OUTER JOIN 구현

- MySQL은 FULL OUTER JOIN을 제공하지 않는다, 단 UNION으로 효과를 비슷하게 구현할 수 있다.

- 두 테이블을 OUTER 조인을 연결해서 UNION 으로 더하면 된다.

- UNION은 중복제거를 하기 때문에, 각 OUTER JOIN에서 중복을 제거하고 UNION ALL을 하면 좀 더 빠르다.


* 조인 순서로 인한 쿼리 실패

- MySQL은 ANSI 표준으로 INNER JOIN, LEFT JOIN을 제공한다 ANSI 표준에서는 반드시 JOIN 키워드의 좌우측에 명시된 테이블의 칼럼만 ON 절에 사용가능

- A-B-C순서로 INNER JOIN을 할 경우는 순서에 맞춰서 A-B ==> A or B - C 순으로 JOIN 해야한다.


* JOIN과 FOREIGN KEY

- FOREIGN KEY와 JOIN은 아무런 연관이 없다. FK의 주 목적은 무결성을 보장하기 위해서이다.



* 지연된 조인(Delayed Join)

- 지연된 조인이란 조인이 실행되기 전 GroupBy/OrderBy를 처리하는 방식을 의미(Limit가 특히 큰 효과)

- 할수있는 모든 수행을 다하고 마지막에 join을 하면 JOIN 횟수를 줄일 수 있다.


***  페이징에 관한 이슈 457 Page 꼭 참고할것 , 지연된 조인!***



신고


출처 :http://dev.kthcorp.com/

http://dev.kthcorp.com/2011/06/10/mysql-innodb-engine-3-tips-you-must-know/


추가적으로 알게 된것...

2. PK는 Auto_increment를 적용하는것이 이롭다.



신고

7.4.SELECT ( 7.4.3 WHERE ~ 7.4.5 LIMIT )

RealMysql 2012.12.11 21:44 posted by dev.bistro


7.4.3 WHERE 절의 비교 조건 사용시 주의 사항


 1) NULL 비교

   - MySQL에서는 NULL값도 인덱스로 관리된다. SQL정의에 의해 NULL은 "비교할 수 없는 값" 그래서 NULL은 ISNULL을 사용하라.

     SELECT CASE WHEN NULL  IS NULL THEN 1 ELSE 0 END;

     SELECT * FROM titles WHERE to_date IS NULL (type:ref)


     ISNULL( )함수를 사용해도 되지만, 주의하라. 인덱스를 이용할 수 없을지도 모른다.

     이용X : SELECT * FROM titles WHERE ISNULL(to_date) = 1;

     이용O : SELECT * FROM titles WHERE ISNULL(to_date);


2) 문자열, 숫자 비교

  - 반드시 그 타입에 맞춰 상수를 사용하라.

    SELECT * FROM emp where emp_no = '10001'

    => 숫자타입, 문자열을 비교하지만, 옵티마이저가 문자열의 숫자를 숫자타입으로 변경하기때문에 큰 성능저하는 없다.

    SELECT * FROM emp where first_name = 10001;

    => 옵티마이저는 우선순위로 first_name을 숫자형으로 변경하려고 한다.그래서 first_name이 가지는 원래 인덱스를 사용하지 못한다.


3) 날자 비교

  DATE :날자 저장

  DATETIME, TIMESTAMP : 날자,시간 저장

  TIME : 시간 저장



7.4.4 DISTINCT

 * 집함 함수와 같이 사용될때, 인덱스를 사용하지 못하면 항상 임시 테이블이 필요하다 하지만 Extra에 Using temp 표시가 나오지 않으므로 주의.

 

 1) SELECT DISTINCT

   유니크한 레코드를 찾을때, GROUP BY와 거의 유사한 방식. 단 이방법은 정렬이 보장되지 않는다.

   SELECT DISTINCT emp_no FROM sal = SELECT emp_no FROM sal GROUP BY emp_np 

   DISTINCT는 레코드 유니크이지, 칼럼 유니크는 아니다!


   DISTINCT는 함수가 아니므로 뒤의 괄호는 무시한다

   SELECT DISTINCT(first_name) , last_name === SELECT DISTINCT first_name , last_name 

   ==>절대로 일부 칼럼에만 유니크하게 조회 하는 방법은 없다. 아래는 예외


2) 집함 함수와 DISTINCT

  집합 함수 내에서 사용된 DISTINCT는 그 함수의 인자의 조합 가운데서 유일한 값만 가져온다.

  SELECT COUNT(DISTINCT s.salary) FROM emp e, sal s WHERE e.emp_no = s.emp_no

  e,s테이블의 조인 결과에서 salary 칼럼만 저장하기 위한 임시 테이블을 생성하지만 실행계획에서 using temp는 나오지 않는다.



 SELECT DISTINCT first_name, last_name

 SELECT COUNT(DISTINCT first_name) , COUNT(DISTINCT last_name)

 SELECT COUNT(DISTINCT first_name, last_name) 은 전부 다 다르다.



7.4.5 LIMIT n

 (MySQL)전용 키워드, 오라클의 ROWNUM과 비슷하다. 항상 쿼리의 제일 마지막에 실행된다.

 ** LIMIT가 필요한 건수만 준비되면 쿼리를 종료한다 

 SELECT  * FROM emp WHERE emp_no BETWEEN 10001 AND 10010 ORDER BY first_name LIMIT 0, 5

 ==> 정렬하다가 5건만 되면 더 이상 정렬 안하고 쿼리를 종료한다.


 SELECT * FROM emp LIMIT 0, 10 => 원래는 풀 스캔인데 LIMIT 때문에 위에서 10개의 레코드를 읽는 순간 읽기 작업이 멈춘다.

 ORDER BY, GROUP BY, DISTINCT가 적절히 인덱스를 이용하면, LIMIT은 꼭 필요한 만큼만을 정하므로 성능 향상이 있다.


  * LIMIT제약 : 인자로 표현식이나 별도의 서브 쿼리를 사용 할 수 없다. ( ex: SELECT * FROM emp LIMIT (100-10) )

  * LIMIT 0 : MySQL옵티마이저는 쿼리를 실행하지 않고 최적화만 실행하고 즉시 응답한다. 이건, 커넥션 풀에서 커넥션의 유효성을 체크하는데 유용하게 사용한다


신고


7.4 SELECT  


7.4.1 SELECT의 처리 순서

 1) 대부분의 처리 순서  - 드라이빙 테이블 - 드리븐 테이블1 - 드리븐 테이블2  - GROUP BY - DISTINCT - HAVING - ORDER BY - LIMIT


 2) 아주 예외적인  - 드라이빙 테이블 - ORDER BY - 드리븐 테이블1 - 드리븐 테이블2 - LIMIT

    (첫 테이블 읽어, 오더링 후 나머지 테이블을 읽는 경우로, 주로 GROUP BY없이 ORDER BY만 사용된 쿼리에서 볼 수 있다)


 3) 이외의 처리 순서가 필요하다면, 서브 쿼리를 사용해야 한다. (LIMIT를 GROUP BY 전에 실행 하는것처럼, 하지만 임시 테이블이 사용되므로 주의할 것)


7.4.2 GROUp BY, ORDER BY의 인덱스 사용

  1) 기본 규칙 

   a) 인덱스 컬럼의 값 자체를 변환하지 않고 그대로 사용 (인덱스는 값을 B-tree에 정렬해서 저장하기 때문)

      SELECT * FROM salaries WHERE salary * 10 > 15000 은 인덱스를 이용하지 못하는 사례이다.

   =>SELECT * FROM salaries WHERE salary > 1500으로 변경하면 인덱스를 이용할 수 있는 최적의 사례이다.

   이렇게 튜닝이 힘들땐, 미리 계산된 값을 컬럼에 추가하고 그 칼럼에 인덱스를 생성하는 것이 좋다. 


   b) type이 일치해야 한다. (암시적인 형 변환이 아닌, 명시적으로 일치해야한다)

     문자열 비교보다, 숫자의 비교가 빨라서 mySQL 옵티마이저는 숫자 타입에 우선권을 부여한다. 

     varchar age , where age = 2 => type:index 풀 인덱스

     int age, where age = '2'  => type:ref, 인덱스 사용 (옵티마이저의 효과!)



 2) WHERE절의 인덱스 사용

   WHERE 절에서 각 조건이 명시된 순서는 중요하지 않고, 그 칼럼에대한 조건이 있는지 없는지가 더 중요하다.-and로만 이용될때이다.(5.3.7, B-tree참조)

   A='value1' or B='value2'는 옵티마이저는 풀 스캔을 이용 할 것이다. (A에 인덱스, B는 인덱스 없고)

   (풀 스캔+인덱스 레인지 스캔) 보다는 (풀 스캔) 1번이 더 빠르기 때문이다. 

   A='value1' and B='value2' 였다면 A의 인덱스를 이용 했을 것이다.



 3) GROUP BY절의 인덱스 사용

   * GROUP BY 절의 명시된 칼럼의 순서가 인덱스 구성 칼럼 순서와 같으면 GROUP BY 절은 인덱스를 사용할 수 있다.

   * GROUP BY절에 명시된 컬럼중 하나라도 인덱스에 없으면 전혀 인덱스를 이용 할 수 없다.

   * 인덱스 뒤쪽의 컬럼이 GROUP BY에 없어도 사용가능하지만, 인덱스 앞에 있는 칼럼이 GROUP BY에 없다면 사용 불가.

     예제)     인덱스 : C1, C2, C3, C4     GROUP BY : C1, C2, C3

    인덱스를 이용하지 못하는 경우

      GROUP BY C2, C1 (순서 불일치)

      GROUP BY C1, C3, C2 (순서 불일치)

      GROUP BY C1, C3 (COL2가 GROUP BY에서 나오지 않았다)

      GROUP BY C1, C2,C3,C4,C5 (GROUP BY 마지막의 C5가 인덱스에 없다) 

    인덱스를 사용 할 수 있는 경우

      GROUP BY C1 

      GROUP BY C1,C2

      GROUP BY C1,C2,C3

      GROUP BY C1,C2,C3,C4

      WHERE COL1='상수' ... GROUP BY COL2, COL3 (WHERE가 상수로 앞쪽의 인덱스 칼럼을 비교하기 때문에 GROUP BY에 없어도 인덱스 사용 가능)

      WHERE COL1='상수' AND COL2='상수' ... GROUP BY COL3

 4) ORDER BY 절의 인덱스 사용

   MySQL에서는 GROUp BY, ORDER BY의 처리가 흡사해서 인덱스 사용 여부도 GROUP BY와 거의 흡사하다. 추가 조건은

    * ASC, DESC 옵션이 인덱스와 같거나, 또는 정반대의 경우에만 사용 가능하다. (MySQL인덱스가 모두 오름차순일때, ORDER BY 절의 모든 칼럼이 오름차순or내림차순일때만 사용) 


 5) WHERE, ORDER BY(또는 GROUP BY)의 인덱스 사용

  WHERE, ORDER BY절이 '같이' 사용된 쿼리는 다음 3가지 중 한가지 로만 인덱스를 이용한다.

  1) WHERE, ORDER BY 동시에 같은 인덱스를 이용

    WHERE, ORDERBY의 칼럼이 모두 하나의 인덱스에 연속해서 포함될 때, 이 방식 - 다른 2가지보다 훨씬 빠르다.

  2) WHERE 절만 인덱스를 이용

    ORDER BY 인덱스 사용이 불가능할때, WHERE절에 일치하는 조건의 건수가 많지 많을때 효율적

  3) ORDER BY절만 인덱스를 이용

    ORDER BY의 인덱스를 이용하면서 한 건식 WHERE 조건에 일치하는지 처리하는 형태 


 6) GROUP BY, ORDER BY절의 인덱스 사용

   동시 사용된 쿼리에서 인덱스를 사용해서 처리하려면, 두 곳의 명시된 칼럼의 '순서,내용'이 모두 같아야 한다. 만약 둘 중 하나라도 인덱스를 사용하지 못하면 둘다 인덱스를 사용하지 못한다.


신고

7.3.3 MySQL 내장 함수 (MySQL전용 함수)

RealMysql 2012.11.22 18:06 posted by dev.bistro


7.3.3 MySQL 내장 함수 (MySQL전용 함수)


- IFNULL(A, B) : A가 null 이면 B를 A가 null이 아니면 A를 그대로 반환한다.

- ISNULL(A) : A가 null 이면 true(1) , null 이 아니면 false(0) 을 반환한다.


- NOW() : 현재시간 반환

- SYSDATE() : 현재시간 반환

** 일반적인 웹서비스에서 NOW()가 아닌 SYSDATE()를 꼭 사용해야 이유는 없다. 기능은 동일하지만 SYSDATE()는 '호출시점'에 따라 값이 결정되기 떄문에 인덱싱 등에 문제를 가진다.

    where from_data > SYSDATE() 는 실행때마다 SYSDATE값이 달라진다, 이건 상수가 아닌상태이다!!



- DATE_FOMRAT : Date Type -> String

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H시 %i분 %s초') AS cur_str;

- STR_TODATE : 문자열을 날자 타입으로 변경

  SELECT STR_TO_DATE( '2011-04-30', '%Y-%m-%d') as cur_dt;


- DATE_ADD, DATE_SUB : 날자 타입의 가감

  SELECT DATE_ADD( NOW(), INTERVAL 1 DAY ) as tomorrow;

  SELECT DATE_ADD( NOW(), INTERVAL -1 DAY ) as yesterday;

** YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 의 값이 있고 [ INTERVAL n 단위 ] 형식으로 입력하면 된다.


- RPAD(),  LPAD() : 문자의 좌우측에 문자를 덧붙여 지정된 길이로 만든다.

  SELECT RPAD("Cloee', 10, '_'); -> Cloee_____

- RTRIM(), LTRIM(), TRIM() : 문자의 우측, 좌측, 양측의 공백(Space, NewLine, Tab)을 제거

- CONCAT : 문자열을 연결, 인자의 갯수는 제한이 없다.

- CONCAT : 문자열을 구분자를 넣어서 연결해준다.

  SELECT CONCAT_WS(',', 'A', 'B', 'C') --> A,B,C


- GROUP_CONCAT :  값들을 정렬한후, 연결하거나 구분자설정, 중복제거등 유용하다.


  ex1-SELECT GROUP_CONCAT(dept_no) 

  ==> d001, d002, d003, d004 

  FROM dept (dept모든 레코드에서 dept_no 칼럼을 기본 구분자, 로 연결한 값을 반환)

  


  ex2- SELECT GROUP_CONCAT(dept_no, SEPARATOR '|') FROM dept

  ==> d001|d002|d003}d004 

  

  ex3- SELECT GROUP_CONCAT(dept_no, ORDER BY dept_name DESC) from dept

  ==> d007,d008,d004,d001

  dept_name 역순 정렬 -> dept_no 들의 연결값을 가져옴


  ex4- SELECT GROUP_CONCAT(DISTINCT dept_no ORDER BY dept_name DESC) from dept

  ==> d007,d008,d006,d004

  ex3과 동일하지만 중복된 dept_no가 있다면 제거하고 가져온다.

  

** 제한적인 메모리 버퍼를 사용하는데 TOAD나 SQLYog에서는 단순한 Warnning지만, JDBC로 연결할 때는 SQLException이 발생하므로, GROUP_CONCAT의 결과가 버퍼를 초과하지 않도록 주의해야한다.


  

- CASE WHEN (Function가 아닌 Syntax)

SELECT emp_no, frst_name 

CASE gender WHEN 'M TEHN 'Man' 

WHEN 'F' THEN 'Woman' 

ELSE 'Unknown' 

END AS gender

FROM emp LIMIT 10

 ** CASE WHEN 절이 일치해야만 THEN 이하도 실행이 된다.   서브쿼리->CASE WHEN으로 성능 향상을 꾀할 수 있다.(P407)



- MD5, SHA : 비대칭형 암호화 알고리즘 (SHA - SHA-1알고리즘, 160bit 해시 값, MD5 - Message Digest알고리즘 128bit 해시 값)

 중복의 가능성이 매우 낮기 때문에, 길이가 긴 데이터를 줄여서 인덱싱하는 용도로도 사용한다.(url값 인덱싱)



- COUNT() : 칼럼이나 표현식을 인자로 받고, *을 사용하지만 '모든 칼럼이 아니라, RECORD 자체를 의미'한다.
  - MyISAM : 메타테이블의 전체 레코드수가 있어서 where가 없는 count(*)는 결과를 바로 반환가능
  - 그외 스토리지 : 직접 읽어야만 하므로, 큰 테이블에서 count(*)는 주의해야한다.

- 표준 주석
  -   --공백하나 : 한줄의 주석
  -   /* 내용 */ : 여러준 주석
  -   # 라인 이후 주석 : 비표준 

  - 변형 주석
   CREATE /*! 50154 TEMPORARY */ TABLE tb_test (fd INT, PRIMARY KEY(fd));
   -> 5.1.54이상 CREATE TEMPORARY TABLE tb_test (fd INT, PRIMARY KEY(fd));
   -> 5.1.54미만 CREATE TABLE tb_test (fd INT, PRIMARY KEY(fd));
    MySQL의 버전에 따라 주석이 될수도, 쿼리문장이 될 수도 있다. !뒤에가 기준 버전이 된다.

    MySQL 5.0에서 쿼리나 프로시저에 포함된 주석은 모두 삭제되는데 이를 트릭으로 막을 수 있다.

    BEGIN
        /*! 99999 주석 내용 */
        RETURN '테스트'
    END 

신고

7. 쿼리 작성 및 최적화 (ANSI SQL명령)

RealMysql 2012.11.12 21:14 posted by dev.bistro

7. 쿼리 작성 및 최적화


-

- 키워드는 대소문자를 특별히 구분하지 않고 사용 할 수 있다.

- 예약어를 테이블 명이나 칼럼명으로 사용하려면 `index` 처럼 하면 되지만, 쓰지 말자.


- 숫자 형변환 (비교 대상의 타입이 다를 경우는 자동 형 변환이 발생하고, 숫자를 우선시해서 문자열을 숫자로 바꾸는 작업을 먼저함)

  - SELECT * FROM tab_test WHERE number_column = '10001'

    10001을 상수로만 변경하면 되므로 성능에 이슈가 없다.

  - SELECT * FROM tab_test WHERE string_column = 10001

    sring_column을 항상 상수로 변경하려므로(숫자우선) 성능상 이슈가 발생, 그리고 숫자로 변경안되는 문자열도 있어서 쿼리 오류가 발생할수도 있다.


 - 날자 (MySQL은 자동으로 DATE, DATETIME으로 변환하기 때문에 STR_TO_DATE()같은 함수를 사용하지 않아도 된다)

    SELECT * FROM dept_emp WHERE from_date = '2011-04-29'

    SELECT * FROM dept_emp WHERE from_date=STR_TO_DATE('2011-04-29', '%Y-%m-%d');


- 불리언 (BOOL, BOOLEAN이 있지만, TINYINT에 대한 동의어 일 뿐이다)


- 동등비교 <=>

  = 연산자와 같으며 부가적으로 NULL 에 대한 비교도 수행한다. NULL-SAFE라고 한다

     SELECT 1=1, NULL=NULL, 1=NULL, 1 <=> 1, NULL <=> NULL, 1 <=> NULL; ---> 1, NULL, NULL, 1, 1, 0

     <=>는 한쪽만 NULL이면 false를 반환한다.


- LIKE연산 

  인덱스를 사용가능하다.

  와일드카드인 %, _가 뒤에 있다면 인덱스 레인지 스캔 가능, 앞에 있다면 사용할 수 없다.


- IN 연산

  여러개의 값에 대해 동등 비교 연산을 수행., 여러개의 값이 비교되지만, 범위가 아닌 여러번의 동등 비교로 실행 => 일반적으로 빠르다.

  IN의 입력값이 서브 쿼리라면 상당히 느려질 수 있다.

신고

6장 실행계획 (6.3.6 테이블 조인)

RealMysql 2012.11.09 18:15 posted by dev.bistro

6.3.6 테이블 조인


INNER JOIN(일반적으로 부르는 JOIN) : 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로, 옵티마이저가 조인의 순서를 결정해서 최적화 할 수 있다.

 SELECT * FROM emp e, sal s WHERE e.emp_no = s.emp_np;

 SELECT * FROM emp e INNER JOIN sal s ON s.emp_no = e.emp_np;

 SELECT * FROM emp e INNER JOIN sal s USING (emp_no) 

 문법은 다르지만, 전부 같은 쿼리이다. 


OUTER JOIN : 반드시 OUTER가 되는 테이블을 먼저 읽어야 해서 최적화 할 수 없다. (어느 테이블을 먼저 읽느냐는 조인에서 아주 중요한 요소이다)


 outer table은 반드시 먼저 읽어야 하며, 주도적인 역할을 한다고 해서 driving table이라고 한다. inner table은 driven table이라고도 한다.




 INNER JOIN pseudo - IF처럼 매칭되는것만 조인의 결과로 가져온다.

 FOR ( r1 in TABLE1)

    FOR ( r2 in TABLE2 ){

      IF(r1.c == r2.c) {

 found(r1.* , r2.*);

      }else{

          not_found();

     ...



OUTTER joint pseudo  - INNER JOIN과 달리 IF의 매칭이 안되더라도 OUTER TABLE의 결과를 가져온다.

 FOR ( r1 in TABLE1)

    FOR ( r2 in TABLE2 ){

      IF(r1.c == r2.c) {

 found(r1.* , r2.*);

      }else{

          found(r1.*, NULL)

     ...

* inner 테이블의 조인의 결과에 전혀 영향을 미치지 않고, outer에 따라 결과가 결정된다. (결과는 최소한 outer 의 갯수보다 같거나 크다)


* left outer join : SELECT * FROM employees e LEFT OUTER JOIN salaries s ON s.emp_np = e.emp_no;

  (outer : employees)

* right outer join : SELECT * FROM salaries s  RIGHT OUTER JOIN employees e ON s.emp_np = e.emp_no;

  (outer : employees)

* 둘은 같은 결과를 낸다. 그래서 혼동을 막기 위해 보통 LEFT OUTER JOIN으로 통일해서 사용하는것이 일반적

* 유의사항

 - 실행계획은 inner join인지 outer join인지 알려주지 않는다. 

 - 최적화 계획에 의해 outer -> inner join으로 실행 될 수 있으므로 '반드시 ON 절에 사용되는 조건을 명시적으로 작성하는 것'이 좋다.

   SELECT  * FROM employees e LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no WHERE s.salary > 5000

   ===> (위를 실행하면 최적화에 의해 inner join이 되므로 아래처럼 명시적으로 작성)

   SELECT  * FROM employees e LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no AND s.salary > 5000

* LEFT OUTER JOIN 이 아닌 쿼리는 검색조건,조인조건을 WHERE / ON 어느곳에 명시해도 결과&성능 차이가 나지 않는다.


카테시안 조인 (FULL JOIN , CROSS JOIN)

- 조건 자체가 없이, 2개의 테이블의 모든 조합을 결과로 가져온다. (MySQL을 응답 불능 상태로 만들 수 있다)

- 한건만 조회하는 여러 쿼리를 한번에 실행하기 위해 사용하기 도 한다

  SELECT * FROM de WHERE dept_no = 'd0001';

  SELECT * FROM emp WHERE emp_no = '1000001'; 

  ==>

  SELECT d.*, e.* from dep d , emp e WHERE dept_no = 'd0001' AND emp_no= 1000001;

  (조인 조건이 없는것을 확인 할 수 있다, 그리고 사용에 주의를 해야한다)


NATURAL JOIN

  SELECT * FROM emp e NATURAL JOIN sal s;

  emp와, sal테이블에 존재하는 칼럼중에서 이름이 같은 칼럼을 모두 조인 조건으로 사용한다!

  조인 조건을 명시 안해도 되는 편리함이 있지만, 칼럼명이 바뀔 이슈가 없는지 꼭 확인을 해야한다.

  이런 방식이 있다는 것 정도로 알고 , 사용은 안해도 될듯하다.


*** 조인에서 중요한 2가지 ***

1. 실행 결과의 정렬 순서

  

2. INNER, OUTER 조인의 선택

 실제로 가져와야하는 레코드가 같다면 성능의 차이는 거의 없다. 용도가 다르므로 적절한 사용을 하는것이 더 중요하다.



6.4. 실행 계획 분석 시 주의사항

6.4.1 Select_type

  DERIVED : FROM절에 사용된 서브쿼리로부터 발생한 '임시테이블'을 뜻한다. (만약 디스크에 저장된다면 성능 저하)

  UNCACHEABLE SUBQUERY : FROM절 이외에서 사용되는 서브 쿼리는 가능한 MySQL이 재사용할 수 있게 유도하지만, 없을때 이렇게 노출

  DEPENDENT SUBQUERY : 외부쿼리에서 값을 받아올 경우, 이 쿼리는 먼저 실행되지 못하고 의존적이라 전체 성능 저하. (제거하는 것이 좋다)

6.4.2 Type

 index:인덱스 풀 스캔, ALL: 풀 테이블 스캔


6.4.5 Extra 

  쿼리 실행 계획이 좋은 경우

    Distinct

    Using index

    Using index for group by

   쿼리 실행 계획이 좋지 않은 경우

    Using filesort

    Using join buffer

    Using temporary

    등이 나오면 좀 더 최적화 할 수 없는지 검토

   쿼리를 검수해봐야 하는 경우

    Full scan on NULL key

    Unique row not found 

   쿼리가 요구사항을 제대로 반영했는지 , 버그가 없는지 확인해야한다.


신고


6.3 MySQL의 주요 처리 방식

  스토리지 엔진에서 읽은 레코드를 MySQL엔진이 아무 작업도 하지 않고 사용자에게 반환하면 최고의 베스트 하지만 대부분의 쿼리는 그렇지 않다. MySQL엔진이 처리하는데 오래 걸리는 작업의 원리를 알아두면 도움이 될 것


6.3.1 풀 테이블 스캔

 - 레코드 건수가 매우 작아, 인덱스보다 유리할 경우(보통 1개 page)

 - WHERE, ON절에 적절한 인덱스를 이용할 수 없을 경우

 - 인덱스 레이지 스캔이지만, 실행계획에서 일치 레코드 개수가 너무 많을 경우 등등


 MySQL의 MyISAM은 Read ahead 작업의 갯수를 선언할 수 없다. 그래서, 한번에 한개씩 읽어오지만, InnoDB는 다르다. 

 처음에는 Foreground thread가 페이지를 1개씩 읽지만 이후 4개 , 8개, 최대64개 페이지를 동시에 읽어온다. (시스템 변수 : innodb_read_ahead_threshold)


6.3.2 ORDER BY 처리

 - 인덱스 기반 : 인덱스를 이용해 빠르지만,  INSERT, UPDATE, DELETE 시 부가적으로 인덱스 관련 작업이 필요하고, 디스크 공간도 필요.

 -  FILE SORT : 인덱스의 단점과, 레코드수가 적으면 메모리에서 SORT가 가능해 충분히 빠르지만, 레코드 수가 많으면 느리다.(Using filesort)

 모든 정렬을 인덱스를 이용하기는 불가능

 - 정렬기준이 많아, 모든 인덱스 생성 불가

 - GROUP BY, DISTINCT를 쓰거나 UNION결과 처럼 임시 테이블을 SORT해야하는 경우


 Sort Buffer : MySQL이 정렬을 수행하기 위한 별도 메모리 공간. 정렬에만 이용, 가변적으로 증가하지만 최대 사이즈는 soft_buffer_size 시스템 변수, 그리고소트 완료후 즉시 시스템으로 반납되는 메모리공간이다. (이 사이즈를 넘길 경우 이제 문제가 된다)

 읽고->SortBuffer에서정렬->Disk에 임시저장 -> 다음 읽고 -> 정렬 -> 임시저장 (Multi- Merge방식) 512K가 적당(P334)

 SOrtBuffer를 크게한다고 해서 빨리 지지는 않지만, DISK I/O의 횟수를 줄일수 있다.


 Single Pass 알고리즘 : SELECT되는 모든 컬럼을 버퍼에 담아서 정렬 -> 결과를 바로 보내줌 (최근 5.0이후 일반적 방식)

 Two Pass 알고리즘 : 정렬대상, KEY값으로 정렬 -> 정렬순으로 Key를 읽어서 결과를 보내줌 

 - Single Pass는 한번에 되지만, 대신 더 많은 Sort Buffer 공간이 필요

 - 5.0 이상이더라도, BLOB나 TEXT가 사용되면 Two Pass알고리즘을 사용한다.


 *** SELECT * 가 안좋은 이유 : Sort Buffer를 쓸데없이 다 사용하기 때문이다. 임시테이블결과도 마찬가지 (필요한것만 select하는게 좋다)

 


 * 인덱스를 이용한 정렬

   - ORDER BY 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY순대로 인덱스가 생성되어 있어야한다!

   - 실제로 인덱스값이 정렬되어 있어 '읽기만'하면 된다.

   - 인덱스에 인해 자동 정렬된다고 ORDER BY를 제거하지 마라. 명시적 쿼리이며 성능손해따윈 없다(P339)

 * 드라이빙 테이블만 정렬

 * 임시 테이블을 이용한 정렬


 * 스트리밍 방식 : 데이터가 얼마인지 상관없이 조건에 일치하는 레코드가 검색될때마다 클라이언트측으로 전송 (첫 응답은 매우 빠르다) 하지만 Group By, Order는 이 작업이 불가능하다. 이를 '버퍼링 방식'이라 한다. 그래서 Order By 에 LIMIT를 걸더라도 성능에 크게 도움이 되지는 않는다. 네트워크 전송양이 줄지, MySQL 서버 작업량 자체는 그다지 줄지 않는다.. JDBC는 기본 버퍼링 방식임을 참고하자. (P343)

 * SHOW SESSION STATUS LIKE 'sort%' 으로 특정 실행 횟수를 확인해 볼 수 있다.



6.3.3 GROUP BY 처리

 * 스트리밍 처리가 불가능한 요소, HAVING는 일반적으로 임시 영역에 있는 GROUP BY결과를 필터링하므로 HAVING절을 튜닝하거나 인덱스를 이용하려는 수고를 하지 않아도 된다.,

 * GROUP BY칼럼이 인덱스 있다면, Using index fro grup-by나 Using temporary, Using filesort와 같은 코멘트가 표시 되지 않을것이다.


6.3.4 DISTINCT 처리

 * SELECT DISTINCT 는 GROUP BY와 거의 유사하게 처리된다.

 * 주의!) DISTINCT는 컬럼을 유니크하게 조회하는게 아닌, 튜플을 유니크하게 가져오는것을 까먹지 말것 (P351)

   SELECT DISTINCT (first_name), last_name FROM === SELECT DISTINCT first_name, last_name FROM employees


 집합함수와 함께 사용된 DISTINCT

  COUNT, MAX, MIN과 사용되면 조금 다른 형태로 해석된다. - 그 함수의 인자로 전달된 칼럼만 유니크한걸로 가져온다.

 SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last_name) != SELECT COUNT(DISTINCT first_name, last-name)


6.3.5 임시 테이블

 * MySQL의 임시테이블은 메모리에 생성되었다가 커지만 디스크로 옮겨진다. (특이 케이스도 있다0

 * 임시 테이블은 다른 세션이나 쿼리에서는 볼수, 사용할 수 없다. 그리고 자동삭제된다.


임시 테이블이 필요한 쿼리는 실행 계획에서 Using Temporary 라는 키워드로 확인하면 된다. (표시가 안되도 임시테이블이 사용될 수 있는데 인덱스를 가지는 내부 임시테이블이 생성될수도있다)

임시 테이블이 디스크에 만들어지는 경우

- 임시로 저장해야 하는 내용중 BLOB, TEXT 같은 대용량 칼럼이 있을경우

- 길이가 512바이트 이상인 칼럼이 있을 경우

- GROUP BY, DISTINCT 512바이트 이상 칼럼이 있을 경우

- 임시 테이블 전체 크기가 tmp_table_size 또는 max_heap_table_size보다 큰 경우

임시테이블에 관한것을 파악하려면 SHOW SESSION STATUS LIKE 'Create_tmp%'; 로 확인할 수 있다.

** VARCHAR(512) * 3(utf8) = 1500정도이다. 가능한한 필요한 만큼의 SIZE를 지정하는게 좋다(P358) **

신고


티스토리 툴바