Skip to content

Latest commit

 

History

History
222 lines (174 loc) · 17.5 KB

7-1.md

File metadata and controls

222 lines (174 loc) · 17.5 KB

쿼리 작성및 최적화

  • Query의 패턴별로 어떻게 처리 되는가?

쿼리와 연관된 시스템 설정(p.378)

SQL Mode(p.378)

* SQL이 어떤 SQL syntax를 지원하고 있는지 그리고 데이터의 validation을 검사하는 수준과 방법은 어떤것들인지를 나타내는것
* 대표적인 설정값(P.378)
	*  STRICT_ALL_TALBES … etc

대,소문자의 구분

* 테이블이 디스크의 디렉토리나 파일로 매핑되기 때문에 운영체제에 따라 대소문자의 구분여부가 결정된다.
* 윈도우에 설치되면 대소문자를 구분하지 않지만 유닉스 계열의 운영체제에서는 대소문자를 구분한다.

리터럴(literal) 표기법

* Boolean
	* BOOL 이나 BOOLEAN 같은 타입이 있지만 사실은 TINYINT 타입에 대한 동이어일 뿐이다. ( 실제로 테이블을 BOOL로 생성후에 칼럼 타입을 조회해보면 TINYINT 임)
	* MySQL에선 FALSE가 0을 의미 TRUE는 1만을 의미한다. (ex> P.385)

MySQL 연산자

* MySQL은 `<=>` 와 같은 null-safe한 연산자를 제공함 (P.385)
* `<>`는 not-equal
* `||` (or 연산자) 는 SQL Mode에 따라 concat의 기능으로 사용될 수 있음. `||` 와 `&&` 의 사용은 자제하는게 가독성에 도움이 될것이다.
* LIKE 연산자는 `%, _` 와같은 와일드 카드 문자가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔을 사용 할 수있지만 검색어의 앞쪽에 있으면 인덱스 레인지 스캔을 사용하지 못한다. ( 인덱스의 left-most 특성)
* BETWEEN 과 IN
	* BETWEEN은 크다, 작다 비교연산을 묶어둔것, IN은 여러개의 동등비교(=) 연산을 묶어둔것
	* BETWEEN은 선형으로 인덱스를 검색해야함, IN은 동등비교를 여러번 수행하는 것과 같은 효과 (P.394)
	* IN 연산자의 비교값이 상수가 아닌경우는 상당히 느려질 수 있음. (P.397)
	* NOT-IN은 부정형 비교이어서 처리 범위를 줄이는 조건으로는 사용 할 수 없기때문에 인덱스 풀 스캔을 한다.

내장 함수

* NOW, SYSDATE
	* NOW는 쿼리가 실행되는 시점에 값을 할당 받아서 쿼리의 모든 부분에 같은 값을 사용하게 되지만 SYSDATE는 함수가 호출되는 시점에 값을 받아온다.
	* 이때문에 SYSDATE가 사용된 구문은 slave에서 안정적으로 복제가 되지 못함.
	* SYSDATE는 꼭 써야할 상황이 아니라면 피하는게 ㅎㅎ;
* GROUP_CONCAT
	* 지정한 칼럼들의 값을 연결하기위해 제한적인 메모리 버퍼 공간을 사용한다
	* 기본값이 1KB 이기때문에 group_concat을 자주 사용한다면 이 설정값을 늘려주는게 좋음.
* INET_ATON, INET_NATON
* COUNT
	* count의 인자 `*`은 레코드 자체를 의미한다. 모든 칼럼을 읽는 형태로 처리하지는 않는다.
	* count 만을 가져오는 쿼리라면 orderby 나 left join같은 의미없는것들은 지우도록 하자.
	* Count(column1) 이라고 사용하면 column1이 null이 아닌 건수를 가져오므로 nullable한 컬럼에 대해서는 주의하자

SELECT

처리 순서 (P.420)OR연산의 경우 읽어와야할 레코드가 훨 늘어나기때문에

인덱스를 사용하기 위한 기본 규칙 (p.422)

* 인덱스된 칼럼의 값을 변환하지 않고 그대로 사용해야 한다.
	* 인덱스는 칼럼의 값을 아무런 변환없이 저장함.
* WHERE 절에 사용되는 비교대상의 값은 데이터 타입이 일치해야한다.
	* 데이터 타입이 다르면 데이터를 옵티마이저가 좀 더 처리하기 효율적인 타입으로 형변환을 하기 때문에 인덱스 레인지 스캔을 사용하지 못하고 인덱스 풀 스캔을 사용하게 된다.

WHERE의 INDEX 사용 (p.424)

* 각 조건들이 명시된 순서보다는 인덱스가 걸린 칼럼에대한 조건이 있는지 없는지가 중요.
* OR 연산자를 사용을 조심해야한다.
	* OR연산에 사용된 인자들이 인덱스가 없는 인자가 포함되어있다면. 풀 테이블 스캔을 할 수 밖에 없고
	* 각 인자들의 인덱스가 존재 한다고해도, index_merge방식으로 실행을 하게된다. 이는 제대로된 인덱스하나를 레인지 스캔하는것 보다는 느리기때문에

GROUP BY의 INDEX 사용(P.426)

* group by 절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야한다.
* 인덱스를 구성하는 칼럼중에서 뒷쪽에 있는 칼럼은 group by절에 명시되어있지 않아도 인덱스를 사용할 수 있지만 앞쪽에 있는 칼럼이 group by절에 명시 되지않으면 인덱스를 사용 할 수 없다.
* group by절에 명시된 칼럼이 하나라도 인덱스에 없으면 인덱스를 이용하지 못한다.

ORDER BY의 INDEX 사용(P.426)

* group by와 비슷 하지만 오름차순및 내림차순 옵션이 인덱스와 같거나 또는 정반대의 경우에만 사용가능
* Mysql의 인덱스는 모두 오름차순 정렬이어서 order by절의 모든 칼럼이 오름차순이나 내림 차순일때만 가능.

WHERE 조건과 ORDER BY(OR GROUP BY) 절의 인덱스 사용 (p.428)

* 일반적으로 where과 group by 혹은 order by는 같이 사용하게됨.
* where 절의 비교조건에서 사용하는 칼럼과 정렬 대상칼럼이 모두 하나의 인덱스에 연속해서 포함 되어있을 경우에 where 과 order by절이 같은 인덱스를 사용할 수 있음
* where 절만 인덱스 사용이 가능한 경우 별도의 정렬 과정 (filesort)를 거쳐서 정렬을 수행 where절의 조건에 일치하는 레코드가 많지 않을때 효율적인 방식
* order_by절만 인덱스를 이용하는 경우, order_by절의 순서대로 인덱스를 읽으면서 레코드 한건씩을 where의 조건에 일치하는지 비교함. 아주많은 레코드를 조회해서 정렬할때 이런식으로 튜닝 하기도함.
* where절에서 equal 조건으로 비교된 칼럼과 order_by 절에 명시된 칼럼의 순서가 인덱스 칼럼의 왼쪽부터 일치해야한다. (p.430)
* where 과 group by의 조합도 같은 기준이 적용된다.

Group by 절과 order by 절의 인덱스 사용

* 인덱스를 사용하려면 group by절에 명시된 칼럼과 order by에 명시된 칼럼의 순서와 내용이 모두 같아야 하낟.
* MySQL의 group by는 칼럼에 대한 정렬까지 함께 수행하는것이 기본 작동방식이어서 group by와 order by 칼럼의 내용과 순서가 같다면 order by를 생략해도 무방함.

Where절의 비교조건 사용시 주의 사항(p.433)

* NULL 비교
	* MySQL에서는 NULL값이 포함된 레코드도 인덱스로 관리됨.
	* SQL 표준에서  NULL은 비교 될 수 없는 값임. 그래서 한쪽이라도 NULL 이면 결과도 NULL임.
	* NULL비교를 할때는 가급적 IS NULL 연산자를 사용하자
* 문자열 숫자 비교
	* 문자열이나 숫자 칼럼을 비교할때는 반드시 그 타입에 맞추어 상수를 사용할것

날짜 비교 (p.435)

* DATE나 DATETIME의 값과 문자열을 비교할때는 문자열 값을 자동으로 DATETIME타입의 값으로 변환을 해서 비굘르 수행함. 문자열을 명시적으로 변환해줄 필요는 없다.
* 칼럼을 변형해 비교하지 말고 상수를 변경하는 형태로 조건을 사용하는것이 좋음
* TIME_STAMP의 경우는 비교값으로 사용되는 상수 리터럴을 비교 대상 칼럼의 타입에 맞게 변환해서 사용하는것이 좋음.

DISTINCT (p.438)

* group by와 비슷하게 처리되나 distinct는 정렬을 보장하지 않음.
* SELECT DISTINCT …는 SELECT 되는 레코드를 유니크하게 처리하는것이지, 칼럼을 유니크하게 처리하는것이 아니다.
* 칼럼만 유니크하게 조회하는 방법은 없음.
* 집함 함수와 사용된 distinct는 임시테이블을 만들어 낸다. 인덱스된 칼럼에 대한 distinct는 인덱스를 활용해 처리할 수있음.

LIMIT

* LIMIT에서 필요한 레코드 건수만 준비되면 쿼리를 종료 시킨다.
* 정렬이나 grouping 또는 distinct가 없는 쿼리에서 limit을 사용하면 상당히 빨리 끝날 수 있다.

JOIN

* 드리븐 테이블에서는 인덱스 탐색 작업과 스캔작업을 드라이빙 테이블에서 읽은 레코드 수만큼 반복함.  그래서 옵티마이져는 항상 드리븐 테이블을 최적으로 읽을 수 있게 플랜을 수립함
	* 예시 : `SELECt * from employees e, dept_emp de where e.emp_no=de.emp_no`
		* 두칼럼 모두 각각 인덱스가 있는 경우
			* 어떤 테이블을 드라이빙 테이블로 선택하든 드리븐 테이블의 검색작업을 빠르게 처리 할 수 있음, 옵티마이져가 통계 정보를 조회하여 드라이빙 테이블을 선택함.
		* employees.emp_no 에만 인덱스가 있는 경우
			* dept_emp가 드리븐 테이블이 될경우 employees 건수 만큼 dept_emp을 풀스캔 해야하므로 옵티마이져는 항상 dept_emp 을 드라이빙 테이블로 선택한다. 반대의 경우에도 같음
		* 두칼럼 모두 인덱스가 없는 경우
			*  어느 테이블을 드라이빙으로 선택해도 풀스캔이 발생하므로, 레코드 건수가 적은 테이블을 드리븐으로 선택한다. 드리븐 테이블을 읽을때 조인 버퍼를 사용함.
* 조인칼럼간의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 사용 할 수 없다. (p.445)
	* CHAR - VARCHAR, INT-BIGINT-SMALLINT, DATE-DATETIME 의 경우에는 타입 불일치 문제가 생기지 않음
	* 같은 CHAR 타입이라도 charset 혹은 collation이 다른경우 타입 불일치 문제가 생김 ( mysql5.0 이상 에서는 charset은 같지만 collation이 다른 경우 바교를 하면 에러를 뿜음)
	* Signed - Unsigned는 타입 불일치 문제가 생긴다.
* Mysql에서는 조인되는 칼럼에 대한 조건은 전부 on절에 표시 해야한다 그렇지 않으면 InnerJoin같은 방법으로 처리함(p.448)
* count 쿼리를 날릴때 드라이빙 테이블과 드리븐테이블의 관계가 1:1, M:1인 경우, 드리븐 테이블에 조인 조건 이외의 별도 조건이 필요 없는 경우에는 outerJoin이 불필요하다. (p.449)
* ANTI-JOIN을 사용할때에는 조건절에 반드시 NOT NULL인 칼럼을 사용해야한다. 1:1 이거나 M:1일때만 사용 하는것이 좋다, 1:M이라면 NOT_EXIST를 활용한 subquery가 더 효율적
* INNER JOIN과 OUTER JOIN은 실제 가져와야할 쿼리 레코드가 같다면 별 성능차이는 없음.
* MYSQL에서 FULL-OUTER-JOIN은 지원하지 않아서 UNION이나 UNION_ALL을 활용해 구현해야한다. 일반적으로 UNION은 중복제거가 필요하기 때문에 각 쿼리에서 중복을 미리 제거하고 UNION_ALL을 사용한다면 더 빠르다.
* UNION, UNION_ALL 은 모두 임시테이블을 사용하므로 성능이 걱정된다면 mutex 테이블을 사용하자(p.452)
* Mysql 은 ANSI 표준인 INNER-JOIN, LEFT-JOIN 구문을 기본문법으로 제공, 혼용하거나 조건의 순서에 신경써야함(p.454)

JOIN과 FOREIGN KEY

* FORIGN KEY는 조인과는 관계가 없다. 주 목적은 데이터의 무결성을 보장하기 위함임.

Delayed Join (p.455) ~

* JOIN이 실행되기 이전에 GROUP BY 혹은 ORDER BY를 처리하는 방식 주로 LIMIT과 함께 사용된 쿼리에서 효과를 얻는다
* OUTER JOIN의 경우 드라이빙 테이블과 드리븐 테이블이 1:1 혹은 M:1 관계여야 사용가능
* INNER JOIN의 경우 드라이빙 테이블과 드리븐 테이블은 1:1 혹은 M:1 관계임과 동시에 드라이빙 테이블에 있는 모두 드리븐 테이블에 존재 해야한다.
* GROUP_BY나 ORDER_BY 처리가 필요한 레코드의 전체크기를 줄이는 역할도 함.

조인 버퍼 사용으로 인한 정렬의 흐트러짐

* Mysql 에서는 nested-loop 방식의 조인만 지원함. 특성상 드라이빙 테이블의 순서로 결과가 정렬이 된다.
* 그러나 조인 버퍼가 사용되면 드라이빙 테이블과 드리븐 테이블을 읽은 순서와는 무관하게 정렬이 흐트러짐

Group By

* Group Key가 아닌 칼럼은 일반적으로 집합 함수를 감싸서 이용하는것이 일반적 감싸지 않은 값들은 어떤 값을 가져올지 예측할 수 없다(제일 큰값, 중간값, 작은값)
* Group by는 정렬까지 보장하므로 정렬작업때문에 느려지는데 order by null을 활용하면 정렬을 하지 않는다.
* group by절에 순서를 명시하는 문법도 지원함. group by title desc, from_date asc
* GROUP BY WITH ROLLUP

ORDER BY

* ORDER BY를 사용하지 않으면?
	* 인덱스를 사용한 SELECT인 경우에는 인덱스의 정렬 순서대로 정렬됨
	* MyISAM의 경우 테이블에 저장된 순서(insert된 순서가 아님, 중간에 삭제된값이있으면 삭제된 공간에 채워넣기에)
	* InnoDB의 경우 PK의 순서대로
	* 임시테이블을 거쳐 처리되면 순서를 알기 어렵다
* 정렬 순서는 칼럼별로 명시가능
* ORDER BY 2 같은 문법도 존재, 이는 두번째 칼럼 기준으로 정렬 한다는 의미
* ORDER BY RAND()는 인덱스를 이용 할 수 없다.
* 여러 칼럼을 조합하여서 정렬할때는 정렬 순서가 오름차순과 내림차순이 혼용되면 인덱스를 이용할 수 없음.
* NULL은 항상 최소값으로 간주하고 정렬함. DESC면 맨 처음 ASC 면 맨 마지막
* ORDER BY절에 인덱스에 명시된 칼럼의 값을 변형하여 정렬을 수행하면 인덱스를 활용한 정렬이 불가능해짐
* MySQL의 인덱스키는 756바이트를 넘을 수 없음

Sub Query

* From 절에 사용되는 서브쿼리나 where 절의 IN구문은 그다지 효율적으로 처리 되지 못함. (5.6 부터 개선됨..)
* 외부쿼리에서 정의된 칼럼을 참조하는 여부에 따라 Correlated subquery , self-contained subquery로 나뉨
* LIMIT절 과 LOAD DATA INFILE의 파일명에서는 사용할 수 없음
* IN연산자와 함께 사용할때는 효율적으로 처리 되지못함, ORDER BY 와 LIMIT을 동시에 사용할 수도 없다
* FROM 절에 사용하는 서브쿼리는 Correlated subquery형태로 사용할 수 없다.
* SELECT 절에 사용된 Sub Query
	* 서브쿼리가 적절히 인덱스를 사용할 수 있으면 크게 비효율적으로 처리되지는 않는다
	* 항상 칼럼과 레코드가 하나인 결과를 반환해야한다.(p.477)
	* (Scalar subquery는 레코드의 칼럼이 각각 하나인 결과를 만들어내는 서브쿼리, scalar subquery 보다 레코드 건수가 많거나 칼럼수가 많은 결과를 만들어내는 subquery 는 row subquery 라고함.)
	* 일반적으로 join으로 처리할때가 더 빠름(처리해야할 건수가 많을 수록), join으로 처리할건 join으로 하자
* Where 절에 사용된 Sub Query(p.480)
	* self-contained subquery가 where문 안에 있을때는 서브쿼리를 매번 실행시키는 비효율 적인 방법으로 실행됨(p.479)(mysql 5.5 에선 최적화 됨)
	* In subquery의 형태는 EXISTS 패턴으로 변형시켜 실행한다.
	* NOT-IN절과 함께 사용되는경우, 서브쿼리의 결과가 NULL인지 아닌지에 따라 Mysql이 NOT EXIST로 최적화를 할지 안할지를 판단하는 작업을 수행한다.
* From 절에 사용된 Sub Query
	* from 절에 사용되는 sub query는 항상 임시테이블을 사용하므로 제대로 최적화 되지 못하고 비효율적일때가 많다
	* 용량이 클 경우 임시 테이블을 디스크에 만듬 더욱 병목을 만들 수 있다.
	* join으로 풀자..

집합 연산

* 집합 연산도 모두 임시테이블이 필요한 작업임. 레코드 건수가 많다면 디스크를 사용하기때문에 문제가 될수도
* 집합 연산은 크게 세가지
	* Union - 두개의 집합을 하나로 묶는 역할을 함 두 집합에서 중복되는 레코드를 제거할지 말지에 따라 UNION DISTICT와 UNION ALL로 나뉜다.
	* INTERSECT는 두 집합의 교집합을 반환
	* MINUS는 첫번째 집합에서 두번째 집합을 뺀 차집합의 결과를 반환
	* mysql에서는 union만 지원한다.
* UNION
	* Union뒤에 아무것도 명시하지 않으면 기본적으로 DISTINCT가 적용됨, UNION ALL은 중복제거 과정을 거치지 않고 반환.
	* 중복제거를 위해 UNION(DISTINCT)는 집합의 모든 칼럼을 사용해 UNIQUE인덱스를 사용하는 방식으로 동작함. 성능적으로 큰 차이.. 조심하자
	* 교집합을 제거하고 UNION ALL 로 바꾸는것이 좋다, 그게 어렵다면 어플리케이션 단에서 독립적으로 실행시키고 합치는것이 좋다
* INTERSECT
	* Inner join 과 같다.
* MINUS
	* NOT EXIST 혹은 ANTI-JOIN의 형태로 변환

LOCK IN SHARE MODE , FOR UPDATE

* InnoDB에서는 SELECT 할때 아무런 잠금을 걸지 않는데, 혹여 잠금이 필요할때 사용할 수 있음
* 두 가지다 AUTO-INCREMENT 가 비활성화 된 상태 혹은 BEGIN 이나 START Transaction으로 트랜젝션이 시작된 경우 에만 잠금이 유지된다.
* LOCK IN SHARE MODE는 레코드에 대해 shared lock을 설정하고 다른 세션에서 해당 레코드를 변경하지 못하게 한다. 다른 세션에서 잠금된 레코드를 읽는건 가능
* FOR UPDATE는 Exclusive lock( 베타잠금)을 설정하고 다른 세션에서는 write 뿐만아니라 select도 못하게 한다.
* COMMIT 혹은 ROLLBACK 으로 해제 할 수 있음.
* 잠금경합을 발생시키고 데드락을 유발시킬수도 있으니 주의하자