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) **



티스토리 툴바