[MariaDB] 실행 계획 분석시 주의사항

위키북스 - Real MariaDB p.240~242


4.5.1 Select_type 칼럼의 주의 대상
DERIVED
DERIVED는 FROM 절에 사용된 서브 쿼리로부터 발생한 임시 테이블을 의미한다. 임시 테이블은 메모리에 저장될 수도 있고 디스크에 저장될 수도 있다. 일반적으로 메모리에 저장하는 경우에는 크게 성능에 영향을 미치지 않지만 데이터의 크기가 커서 임시 테이블을 디스크에 저장하면 성능이 떨어진다.


UNCACHEABLE SUBQUERY
쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리는 가능하면 MariaDB 옵티마이저가 최대한 캐시되어 재사용될 수 있게 유도한다. 하지만 사용자 변수나 일부 함수가 사용된 경우에는 이러한 캐시 기능을 사용할 수 없게 만든다. 이런 실행 계획이 사용된다면 혹시 사용자 변수를 제거하거나 다른 함수로 대체해서 사용 가능할지 검토해보는 것이 좋다.


DEPENDENT SUBQUERY
쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리가 자체적으로 실행되지 못하고, 외부 쿼리에서 값을 전달받아 실행되는 경우 DEPENDENT SUBQUERY가 표시된다. 이는 서브 쿼리가 먼저 실행되지 못하고, 서브 쿼리가 외부 쿼리의 결과 값에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만든다. 서브 쿼리가 불필요하게 외부 쿼리의 값을 전달 받고 있는지 검토해서 가능하다면 외부 쿼리와의 의존도를 제거하는 것이 좋다.


4.5.2 Type 칼럼의 주의 대상
ALL, index
index는 인덱스 풀 스캔을 의미하며, ALL은 풀 테이블 스캔을 의미한다. 둘 다 대상의 차이만 있지 전체 레코드를 대상으로 하는 작업 방식이라서 빠르게 결과를 가져오기는 어렵다. 일반적인 OLTP 환경에 적합한 접근 방식은 아니므로 새로운 인덱스를 추가하거나 쿼리의 요건을 변경해서 이러한 접근 방법을 제거하는 것이 좋다.


4.5.3 Key 칼럼의 주의 대상
쿼리가 인덱스를 사용하지 못할 때 실행 계획의 Key 칼럼에 아무 값도 표시되지 않는다. 쿼리가 인덱스를 사용할 수 있게 인덱스를 추가하거나 WHERE  조건을 변경하는 것이 좋다.


4.5.4 Rows 칼럼의 주의 대상
• 쿼리가 실제 가져오는 레코드 수보다 훨씬 더 큰 값이 Rows 칼럼에 표시되는 경우에는 쿼리가 인덱스를 정상적으로 사용하고 있는지, 그리고 그 인덱스가 충분히 작업 범위를 좁혀 줄 수 있는 칼럼으로 구성됐는지 검토해보는 것이 좋다. 인덱스가 효율적이지 않다면 충분히 식별성을 가지고 있는 칼럼을 선정해 인덱스를 다시 생성하거나 쿼리의 요건을 변경해보는 것이 좋다.


• Rows 칼럼의 수치를 판단할 때 주의해야 할 점은 LIMIT가 포함된 쿼리라 하더라도 LIMIT의 제한은 Rows 칼럼의 고려 대상에서 제외된다는 것이다. 즉 "LIMIT 1"로 1건만 SELECT 하는 쿼리라 하더라도 Rows 칼럼에는 훨씬 큰 수치가 표현될 수도 있으며, 성능상 아무런 문제가 없고 최적화된 쿼리일 수도 있다는 것이다.


4.5.5 Extra 칼럼의 주의 대상

실행계획의 Extra 칼럼에는 쿼리를 실행하면서 처리한 주요 작업에 대한 내용이 표시되기 때문에 쿼리를 튜닝할 때 중요한 단서가 되는 내용이 많이 표시된다. 주요 키워드는 기억했다가 실행 계획상에 해당 단어가 표시될 때는 더 자세히 검토하는 것이 좋다.


4.5.5.1 쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우

• Full scan on NULL key
• Impossible HAVING(MariaDB 5.1 부터)
• Impossible WHERE(MariaDB 5.1 부터)

• Impossible WHERE noticed after reading const tables

• No matching min/max row(MariaDB 5.1 부터)

• No matching row in const table(MariaDB 5.1 부터)

• Unique row not found(MariaDB 5.1 부터)


위와같은 코멘트가 Extra 칼럼에 표시된다면 우선 쿼리가 요건을 제대로 반영해서 작성됐거나 버그의 가능성은 없는지 확인해야 한다. 또는 개발용 데이터베이스에 테스트용 레코드가 제대로 준비돼 있는지 확인해보는 것도 좋다. 이 항목들은 성능과 관계가 깊지 않고 단지 "그런 레코드가 없음"이라는 의미가 강하기 때문에 이 쿼리로 인한 버그의 가능성이 있을지를 집중적으로 검토하는 것이 좋다. 물론 쿼리가 업무적인 요건을 제대로 반영하고 있다면 무시해도 된다.

4.5.5.2 쿼리의 실행 계획이 좋지 않은 경우
• Range checked for each record (index map: N)
• Using filesort

• Using join buffer (MariaDB 5.1부터)

• Using temporary

• Using where


위와같은 코멘트가 Extra 칼럼에 표시된다면 먼저 쿼리를 더 최적화할수 있는지 검토해보는 것이 좋다. 마지막의 Using where는 사실 대부분의 쿼리에서 표시되는 경향이 있기 때문에 그냥 지나치기 쉬운데, 만약 실행 계획의 Rows 칼럼의 값이 실제 SELECT되는 레코드 건수보다 상당히 많은 경우에는 반드시 보완해서 Rows 칼럼의 값과 실제 SELECT 되는 레코드의 수의 차이를 최대한 줄이는 것이 중요하다. 쿼리의 실행 계획에서 이러한 문구가 사라질 수 있다면 최선이겠지만 그렇지 않더라도 성능상 허용가능하다면 넘어가도 좋을 듯하다. 단 반드시 자세히 검토해야한다는 사실을 잊지 말자.

4.5.5.3 쿼리의 실행 계획이 좋은 경우
• Distinct

• Using index

• Using index for group-by


여기에 표시된 항목은 최적화되어서 처리되고 있음을 알려주는 지표 정도로 생각하자. 특히 두 번째의 Using index는 쿼리가 커버링 인덱스로 처리되고 있음을 알려주는 것인데, MamDB에서 제공할 수 있는 최고의 성능을 보여줄 것이다. 만약 쿼리를 아무리 최적화해도 성능 요건에 미치지 못한다면 인덱스만으로 쿼리가 처리(커버링 인덱스)되는 형태로 유도해보는 것도 좋다.

Posted by incree

2018/04/28 11:40 2018/04/28 11:40
,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/393

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

MySQL Troubleshooting(MySQL 문제 해결 가이드) - P.152


트랜잭션이 기다리는 모든 락에 대한 정보 :
 SELECT * FROM INNODB_LOCK_WAITS

블럭된 트랜잭션의 목록 :
SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS)

또는
SELECT INNODB_LOCKS.* FROM INNODB_LOCKS JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID=INNODB_LOCK_WAITS.BLOCKING_TRX_ID)

특정 테이블에 대한 락의 목록 :
SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE='<데이터베이스_이름>.<테이블_이름>'

락을 기다리는 모든 트랜잭션의 목록 :
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID,TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE='LOCK WAIT'

Posted by incree

2018/04/08 01:13 2018/04/08 01:13
,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/390

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

[MySQL] MySQL server has gone away error


몇일전 운영하는 사이트에서 이 에러가 발생해서 정리

MySQL 공식사이트에서 이 에러에 대한 페이지를 보면(https://dev.mysql.com/doc/refman/5.0/en/gone-away.html)
이 에러는 증상에 따라 에러 코드가 두가지로 나뉜다.

2006 : CR_SERVER_GONE_ERROR : The client couldn't send a question to the server.
2013 : CR_SERVER_LOST : The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.


원인을 크게 세가지로 나눌수 있는데

1. 어떤 이유에서든 MySQL 서버와 연결이 끊어진 경우(wait_timeout 등)
 - 재연결하거나 끊어진 원인을 파악해서 끊어지지 않게 처리

2. 쿼리가 너무 큰 경우( max_allowed_packet variable 에 설정된 값보다 큰 쿼리를 실행하면 발생)
 - variable 값을 키우거나 쿼리를 작게 만든다

3. --skip-networking 옵션을 사용하여 MySQL을 시작한 경우
 - 이 옵션을 빼고 시작한다.


Posted by incree

2015/07/29 12:30 2015/07/29 12:30
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/364

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

[MySQL] Explain 결과의 칼럼들

1. id

2. select_type
[MySQL] Explain 결과의 select_type 칼럼

3. table
[MySQL] Explain 결과의 table 칼럼

4. type
[MySQL] Explain 결과의 type 칼럼

5. possible_keys
 - MySQL 옵티마이저가 최적의 실행계획을 만들기 위해 후부로 선정했던 접근 방식에서 사용되는 인덱스 목록
 - 무시해도 되는 칼럼이다.

6. key
 - 최종 선택된 실행 계획에서 사용하는 인덱스

7. key_len
 - 인덱스의 각 레코드에서 사용된 byte값

8. ref
 - 접근방식(type)이 ref 일때 참조 조건으로 제공된 값을 보여준다.
 - 상수면 const, 다른 테이블의 칼럼값이면 그 테이블 명과 칼럼명
 - func라고 나오는 경우 가급적 이 값이 안나오게 해결하는게 좋다.

9. rows
 - 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해서 산출해낸  쿼리를 처리하기 위해 읽어야할 레코드의 예상 값
 - 산출치이고, 실제 쿼리 결과 레코드 건수와는 다르다.

10. Extra

Posted by incree

2014/08/25 13:19 2014/08/25 13:19
, , , , , , ,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/357

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

[MySQL] Explain 결과의 type 칼럼

1. system
 - MyISAM or MEMORY table중 레코드가 1건 이하로 존재하는 테이블의 참조 형태 접근
 * InnoDB에는 없는 형태

2. const
 - primary key or unique key 를 where 조건에 이용해서 결과가 1건만 반환되는 쿼리의 처리 방식

3. eq_ref
 - join에서 처음 읽은 테이블의 칼럼 값을 그 다음 읽어야 할 테이블의 primary key or unique key 칼럼의 검색 조건에 사용할때를 eq_ref 라고 하고
두번째 이후에 읽는 테이블의 type 칼럼에 표시된다.
 - 두번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용가능

4. ref
 - join 순서와 상관없고, index 종류와 관계없이 도등 조건으로 검색할 때는 ref 접근 방법이 사용된다.

5. fulltext
 - 

6. ref_or_null
 - ref 접근 방식과 같은데, NULL 비교가 추가된 형태

7. index_merge
 - 2개 이상의 index를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식

8. unique_subquery
 - where 조건절에 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방식

9. index_subquery
 - in (subquery) 형태에서 subquery의 결과가 중복된 값을 반환할 수 있지만, 중복을 index를 이용해 제거할 수 있는 접근 방법

10. range
 - <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스 검색할때
 * 일반적으로 range scan은 const, ref, range 세가지 접근 방법을 말한다.

11. index
 - index full scan

12. ALL
 -


* All, index
index는 인덱스 풀 스캔을 의미하며, ALL은 풀 테이블 스캔을 의미한다. 둘 다 대상의 차이만 있지 전체 레코드를 대상으로 하는 작업 방식이라서 빠르게 결과를 가져오기는 어렵다.
일반적인 OLTP 환경에 적합한 접근 방식은 아니므로 새로운 인덱스를 추가하거나 쿼리의 요건을 변경해서 이러한 접근 방법을 제거하는 것이 좋다.

Posted by incree

2014/08/25 13:03 2014/08/25 13:03
, ,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/356

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

[MySQL] Explain 결과의 table 칼럼

1. NULL
 - 별도의 테이블 사용하지 않는 경우(ex : select NOW())

2. derived or union
 - 임시테이블
 - 뒤에 나오는 숫자는 id값

Posted by incree

2014/08/25 12:48 2014/08/25 12:48
, ,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/355

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

[MySQL] Explain 결과의 select_type 칼럼

1. SIMPLE
 - UNION이나 sub query를 사용하지 않는 단순한 SELECT
 - 일반적으로 제일 바깥 SELECT query가 SIMPLE로 표시된다.

2. PRIMARY
 - UNION이나 sub query가 포함된 SELECT 쿼리의 가장 바깥쪽에 있는 쿼리

3. UNION
 - UNION으로 결합하는 SELECT query에서 두번째 이후의 SELECT의 select_type
 - 첫번째는 UNION으로 결합된 전체 집합의 select_type가 표시

4. DEPENDENT UNION
 - UNION으로 결합된 쿼리가 외부의 영향을 받는다.
*** "DEPENDENT" 키워드가 포함된 sub quiery는 비효율적인 경우가 많다.

5. UNION RESULT
 - UNION 결과

6. SUBQUERY
 - FROM절에서 사용하는 쿼리
 * Nested Query : select column에 사용하는 sub query
 * Sub Query : where 절에 사용하는 sub query
 * Derived : from 절에 사용하는 sub query(inline view, sub select)

7. DEPENDENT SUBQUERY
 - 바깥쪽 select query에서 정의된 칼럼을 사용하는 sub query
쿼리의 from 절 이외의 부분에서 사용하는 서브 쿼리가 자체적으로 실행되지 못하고, 외부 쿼리에서 값을 전달받아 실해오디는 경우 DEPENDENT SUBQUERY가 표시된다.
이는 서브 쿼리가 먼저 실행되지 못하고, 서브 쿼리가 외부 쿼리의 결과 값에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만든다.
서브 쿼리가 불필요하게 외부 쿼리의 값을 전달받고 있는지 검토해서, 가능하다면 외부 쿼리와의 의존도를 제거하는 것이 좋다.

8. DERIVED
 - sub query가 from 절에 사용된 경우
 *** 가능하면 sub query보다는 join을 사용하라
(DERIVED는 from 절에 사용된 서브 쿼리로부터 발생한 임시 테이블을 의미한다.
임시테이블은 메모리에 저장될 수도 있고 디스크에 저장될 수도 있다.
일반적으로 메모리에 저장하는 경우에는 크게 성능에 영향을 미치지 않지만, 데이터의 크기가 커서 임시 테이블을 디스크에 저장하면 성능이 떨어진다.)

9. UNCACHEABLE SUBQUERY
 - 쿼리의 from 절 이외의 부분에서 사용하는 서브 쿼리는 가능하면 MySQL 옵티마이저가 최대한 캐시되어 재사용 될 수 있게 유도한다.
하지만 사용자 변수나 일부 함수가 사용된 경우에는 이러한 캐시 기능을 사용할 수 없게 만든다.
이런 실행 계획이 사용된다면 혹시 사용자 변수를 제거하거나 다른 함수로 대체해서 사용 가능할지 검토해보는 것이 좋다.

10. UNCACHEABLE UNION

Posted by incree

2014/08/25 12:45 2014/08/25 12:45
, ,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/354

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

[MySQL] explain type의 빠른 순서

system 
const 
eq_ref 
ref 
ref_or_null 
index_merge 
unique_subquery 
index_subquery 
range 
index 
ALL

Posted by incree

2014/08/13 03:00 2014/08/13 03:00
,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/352

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

mysql user defined global variables

기본으로 제공하는 mysql 기능에는 global 사용자 변수를 설정할 수 없다.

udf로 처리해야 한다.

https://github.com/jedisct1/MySQL-global-user-variables-UDF

Posted by incree

2014/05/07 00:22 2014/05/07 00:22
, , , ,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/327

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다