Category Archives: Databases

mysql 이벤트 스케쥴러 사용하기

정기적으로 무언가 DBMS에서 하고 싶은 경우가 있다. MS-SQL 은 MS-AGENT 가 있다. 그런데 mysql에서는 그런 기능을 없는 걸로 알고 있었다. 그런데 MySQL 5.16에서 Event Scheduler가 추가되어 이젠  MS-AGENT 가 부럽지 않다.

1. mysql 접속해서 환경변수 확인

– 리눅스에서 mysql에 접속한다.

– mysql 접속해서 환경 변수 조회 해 본다.

※ event_scheduler 이 OFF 면 이벤트스케쥴러가 활성화 되어 있지 않다는 의미다. 이벤트를 만들어 놓아도 정해진 시간에 자동으로 실행되지 않는다.

2. 이벤트가 동작하게 설정 한다.

– 이벤트 프로세스가 실행되고 있는 걸 확인 할 수 있다.

※ \G 옵견은 쿼리결과를 아래로 보기 좋게 나오게 하는 옵션이다.

3. 그런데 서버를 껏다가 다시 켜면 이벤트가 활성화 안되어 있다면

– 아래와 같이 mysqld 영역안에 추가 하고 저장하고 나온다.

– mysql 서비스 재시작해 준다.

※ 이제는 서버를 재부팅해도 이벤트가 항상 살아 있을 것이다.

1. 덤프뜨기

$> mysqldump -u[사용자아이디] -p 데이터베이스명 [테이블명] > 저장될 파일명

예) mysqldump -ukamkami -p mydatabase > dump.sql

이렇게 하면 디비(mydatabase)를 몽땅 덤프를 뜨게된다.

테이블만 덤프를 뜨고 싶다면

예) mysqldump -ukamkami -p mydatabase member_table > dump.member_table.sql

이렇게 하면 테이블만 덤프를 뜰 수 있다.

2. 복구하기

덤프 파일을 가지고 복구를 하는 방법도 간단하다.
$> mysql -u[사용자아이디] -p [디비명] < 덤프파일명 예) mysql -ukamkami -p < dump.sql 예) mysql -ukamkami -p mydatabase < dump.member_table.sql 1.특정 db의 특정 table에서 원하는 값만 덤프받기 >> edu라는 디비에 a,b,c라는 테이블이 있다. 여기서 a라는 테이블에서 no가 7번이상이고 10번
이하인 값만 덤프를 받고자 한다. 어떻게 하겠는가?
여기서 사용되는 옵션은 -w 이다.그럼 위 질문의 sql문은 아래와 같다

mysqldump -u mysql_admin -p edu a -w’no=>7 and no=<10' > edu_a_cond.sql

위와같이 하면 no가 7~10번까지가 덤프될것이다.
위에서 조건문은 -w 다음에 싱글쿼테이션으로 묶어준다. sql에서 사용하는 조건문이 다 될듯싶

다. 모두 테스트를 해보진 않았다.

2.디비 스키마(Schema)만 백업받기

>>초기에 작성해 놓은 테이블 스키마가 없을때 어떻게 하겠는가?
만약 하나의 테이블이라면 desc 해서 일일이 다 삽질을 하면 되것지만 만약 테이블이 100개라

면 ..크억…이럴때 사용하는 mysqldump옵션이 있습니다.
-d 입니다.

!.edu라는 디비의 모든 테이블 스키마를 백업받으려면

mysqldump -u mysql_admin -p -d edu > edu_db.sql

!.edu라는 디비의 a라는 테이블 스키마를 백업받으려면

mysqldump -u mysql_admin -p -d edu a> edu_a_table.sql

MySQL 필드의 데이터 타입 정리
(대부분 MS-SQL,Access 와 비슷하지만 몇가지가 틀림,
또한 Access 의 경우, 이제 더이상 업그레이드가 되지 않지만,
MS-SQL 의 경우, 계속 업그레이드 되면서 필드 타입도 추가되는 추세임
대표적예: MS-SQL2005 버전에서는 smallint,bigint 등 새로운 타입이 추가되었음?)

-----------------------------------------------------------------------

MySQL 데이터베이스에는 크게 다음과 같은 세 가지 데이터 타입이 있다.
===========================================================
데이터타입 내용 예제
-----------------------------------------------------------
숫자형 숫자를 저장하는 필드 TINYINT, INT, FLOAT
-----------------------------------------------------------
문자형 문자를 저장하는 필드 CHAR, VARCHAR, TEXT, ENUM
-----------------------------------------------------------
날짜형 날짜를 저장하는 필드 DATE, DATETIME, TIMESTAMP
===========================================================
MySQL 데이터 타입의 모든 종류를 외울 필요는 없다.
그러나, 다음의 네 가지 데이터 타입은 상당히 많이 사용되므로 꼭 익혀둘것

INT : 4bytes 정수를 저장할때 사용
CHAR(n) : 문자의 수가 n 개인 문자열을 저장할때 사용
VARCHAR(n) 문자의 수가 최대 n 개인 문자열을 저장할때 사용
TEXT : 문자의 수가 최대 65535 개인 문자열을 저장할때 사용

주의: char 타입은 무조건 n 길이만큼을 차지하고, 출력시에도 n 길이로 체크되지만, varchar 은 실제로 들어있는 문자길이만큼 인식된다.
또한, int 형의 경우, 앞에 0 을 붙여 저장해도 0 이 없어지고, 첫자리는 0 이 아닌 숫자가 나올때까지 0 이 없어진다.

다음은 숫자형, 날짜형, 그리고 문자형의 데이터 타입을 정리해둔 표이다.

숫자형 데이터 타입

=====================================================================================
데이터타입 설명
-------------------------------------------------------------------------------------
TINYINT 가장 작은 숫자형으로 부호가 있는 경우에는 -128 ~ 127,
 부호가 없는 경우에는 0 ~ 255 까지 데이터를 다룰 수 있다.
 저장할때 필요한 용량은 1바이트이다.
-------------------------------------------------------------------------------------
SMALLINT 부호가 있는 경우에는 -32768 ~ 32767,
 부호가 없는 경우에는 0 ~ 65535 까지 다룰 수 있으며,
 저장할때 필요한 용량은 2바이트이다.
-------------------------------------------------------------------------------------
MEDIUMINT 부호가 있는 경우에는 -8388608 ~ 8388607,
 부호가 없는 경우에는 0 ~ 16777215 까지 데이터를 다룰 수 있으며,
 저장할때 필요한 용량은 3바이트이다.
-------------------------------------------------------------------------------------
INT 일반적으로 사용하는 숫자형으로
 부호가 있는 경우에는 -2147483648 ~ 2144483647,
 부호가 없는 경우에는 0 ~ 4294967295 까지 데이터를 다룰 수 있으며,
 저장할때 필요한 용량은 4바이트이다.
-------------------------------------------------------------------------------------
INTEGER int 와 같이 사용한다.
 (int 와 같은 뜻인듯하며, 이름만 달리 사용)
-------------------------------------------------------------------------------------
BIGINT 부호가 있는 경우에는 -922337036854775808 ~ 922337036854775807,
 부호가 없는 경우에는 0 ~ 18446744073709551615 까지 데이터를 다룰 수 있으며,
 저장할때 필요한 용량은 8바이트이다.
-------------------------------------------------------------------------------------
FLOAT 최소값 +_1.175494351E-38 ~ 최대값 +_3.402823466E_38 까지 지원,
 4바이트의 크기를 가진다.
-------------------------------------------------------------------------------------
DOUBLE 최소 ±1.7976931348623157E-308, 최대 ±2.2250738585072014E+308 이며,
 저장할때 필요한 용량은 8바이트이다.
-------------------------------------------------------------------------------------
DECIMAL 소수를 저장하지만 내부적으로는 문자 형태로 저장되는 타입이다.
 예를들어 3.141592 의 경우, 3 이 char 하나의 공간에 저장된다.
=====================================================================================

 
문자형 데이터 타입
=====================================================================================
데이터타입 설명
-------------------------------------------------------------------------------------
CHAR char(0) 도 지원하며,
 실제값이 M보다 작을 때 남는 자릿수만큼 공백을 붙여서 저장한다.
 1부터 최대 255 의 자릿수를 지원하며, 지정한 용량만큼 바이트를 사용.
-------------------------------------------------------------------------------------
VARCHAR 지정할 수 있는 길이는 1부터 255까지이며,
 지정한 길이보다 작은 데이터를 저장할때 필요한 길이만큼 저장된다.
 char 형보다 기억장치를 효율적으로 사용할 수 있다. 지정한용량+1바이트 사용.
-------------------------------------------------------------------------------------
TINYBLOB 최대 255개의 문자를 저장. 지정한 용량+1바이트(L < 2^8 -1) 를 사용.
-------------------------------------------------------------------------------------
TINYTEXT tinyblob 와 같이 사용(이름만 다른듯)
-------------------------------------------------------------------------------------
BLOB 최대 65535 개의 문자를 저장. 지정한 용량+2바이트(L < 2^16 -1)의 용량 사용.
-------------------------------------------------------------------------------------
TEXT blob 와 같이 사용(이름만 다른듯)
-------------------------------------------------------------------------------------
MEDIUMBLOB 최대 16777215 개의 문자를 저장. 지정한 용량+3바이트(L < 2^24 -1) 를 사용.
-------------------------------------------------------------------------------------
MEDIUMTEXT mediumblob 와 같이 사용(이름만 다른듯)
-------------------------------------------------------------------------------------
LONGBLOB 최대 429496729 개의 문자를 저장. 지정한 용량+4바이트(L < 2^32 -1) 를 사용.
-------------------------------------------------------------------------------------
LONGTEXT longbolb 와 같이 사용(이름만 다른듯)
-------------------------------------------------------------------------------------
ENUM 문자 형태인 value 를 숫자로 저장.
 value 중에 하나만 저장하며, value 가 255 이하인 경우에는 1바이트,
 65535 이하인 경우에는 2바이트를 사용.
=====================================================================================

&nbsp;
날짜형 데이터 타입
PHP 의 날짜 및 시간 관련 함수와 비슷하다.
=====================================================================================
DATE '1001-01-01' 부터 '9999-12-31' 까지의 값이 들어갈 수 있으며,
 저장할때 3바이트가 필요. 'YYYY-MM-DD' 와 같은 형식.
-------------------------------------------------------------------------------------
DATETIME 날자와 시간을 같이 저장하며,
 '1001-01-01 00:00:00' 부터 '9999-12-31 23:59:59' 까지의 값을 다루고,
 8바이트가 필요.
 'YYYY-MM-DD HH:MM:SS' 와 같은 형식.
-------------------------------------------------------------------------------------
TIMESTAMP '1970-01-01 00:00:00' 이후부터 초를 숫자로 저장하는 자료형으로,
 4바이트가 필요.
 약 40억초를 저장할 수 있으므로 2037년까지의 값을 다룰 수 있다.
-------------------------------------------------------------------------------------
TIME '-838:59:59' 에서 '838:59:59' 의 값을 다루며,
 3바이트를 사용.
 HH:MM:SS 와 같은 형식
-------------------------------------------------------------------------------------
YEAR 연도만 저장하는 자료형이다.
 year(n)? 와 같은 형식.
 n 은 2와 4를 지정할 수 있으며, 2인 경우에 값의 범위는 70 에서 69,
 4인 경우에는 1970 에서 2069 이다.
 1바이트를 사용.
=====================================================================================

1. 테이블의 인덱스 확인하기

SHOW INDEX FROM tablename;

2-1. 테이블의 인덱스 추가하기 : 컬럼은 1개도 가능, 2개 이상도 가능
ALTER TABLE tablename ADD INDEX indexname (column1, column2);

2-2. 테이블의 유니크 인덱스 추가하기 : 컬럼은 1개도 가능, 2개 이상도 가능
ALTER TABLE tablename ADD UNIQUE INDEX indexname (column1, column2);

3. 테이블의 인덱스 삭제하기
ALTER TABLE tablename DROP INDEX indexname;

type (나쁜것부터 좋은것 순)
– ALL: full scan (extra에 “Using ditict”/”not exists” 가 있거나 LIMIT있는 쿼리는 예외)
– index: full scan but index order. 장점:정렬할 필요 없다. 단점: 전체 테이블을 인덱스 순서로 읽어야해서 random access가 일어남(비용 큼). (extra에 “Using index”가 나오면 커버링 인덱스를 사용하는 것임.(인덱스의 데이터만을 스캔한다는 것임))
– range: 제한된 형태의 index 스캔. index보다는 나은 성능을 보인다. 범위에 따라 성능차가 있다.
– ref: 어떤 조건 하나에 매치되는 행들을 반환해주는 인덱스 접근 방식. 여러개의 행을 찾게 될 수도 있으므로 탐색과 스캔이 함계 사용된다. unique하지 않은 인덱스 검색이 걸릴때 사용된다.
– eq_ref: MySQL이 기껏해야 값 하나만을 반환한다는 것을 알때 사용됨. 기본 키 혹은 unique 인덱스에 걸릴때 사용.
– const, system: 쿼리의 일부를 상수로 대체해서 최적화 할 수 있는 경우.
– NULL: 인덱스나 테이블에 접근조차 하지 않는 경우.

key: MySQL이 최적화를 위해 어떤 키를 사용하기로 했는지를 나타냄.

key_len: MySQL이 인덱스에 얼마나 많은 바이트를 사용하는지를 보여줌.

ref: key 에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼(또는 상수(const))이 사용되었는지를 나타냄.

rows: 원하는 행을 찾기위해 얼마나 많은 행을 읽어야 할지에 대한 예측값. 예측값은 인덱스의 선택도와 테이블 통계 정보에 의존적이므로 정확도가 상당히 떨어질 수도 있다.
MySQL이 조사해야 할 것이라고 생각하는 행 수를 의미할 뿐 결과에 있는 행 수를 의미하지 않는다. 여기서 보여지는 행 수가 그리 중요하지 않을 수도 있다. 예측한 모든 행을 읽지는 않을 것이며, 대체로도 그렇다.

extra: 앞 선 컬럼에 적합하지 않은 나머지 정보를 표시. (중요하고 빈번한 몇가지만 나열)
– Using index: 커버링 인덱스를 사용한다는 것을 알려줌.
– Using where: MySQL서버가 스토리지 엔진에서 값을 가져온 뒤 행을 필터링한다는 것을 의미.
– Using temporary: MySQL이 쿼리 결과를 정렬하기 위해 임시 테이블을 사용한다는 것을 의미.
– Using filesort: 이는 MySQL이 결과의 순서를 맞추기 위해 인덱스 순서로 테이블을 읽는 것이 아니라 외부 정렬을 사용해야 한다는 것을 의미. 메모리나 디스크에서 수행될 수 있으며, EXPLAIN으로는 어떤 방식을 사용하지는, 디스크에서 하는지 메모리에서 하는지 등은 알 수 없다.

1. – Range checked for each record(index map:N): 적합한 인덱스가 없으므로 각 레코드의 조인에서 각 인덱스들을 재평가한다는 것을 의미한다. N은 possible_keys에 나타나 있는 인덱스들의 비트맵 값이다.

7.2.1. EXPLAIN을 사용해서 쿼리 최적화 하기

EXPLAIN tbl_name

또는:
EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options
EXPLAIN 명령문은 DESCRIBE에 대한 동의어로 사용할 수 있거나 또는 MySQL이 SELECT 명령문을 실행하는 방법에 대한 정보를 얻기 위한 수단으로 사용할 수가 있다:

EXPLAIN tbl_name은 DESCRIBE tbl_name 또는 SHOW COLUMNS FROM tbl_name과 동일한 의미이다.
SELECT 명령문을 EXPLAIN 앞에 두면, MySQL은 쿼리 실행 플랜 (query execution plan) 정보를 옵티마이저 (optimizer)에서 가져 와서 출력 한다. 즉, MySQL은 테이블들이 어떤 순서로 조인 (join) 하는지에 대한 정보를 포함해서, SELECT를 처리하는 방법에 대해서 알려 준다.
EXPLAIN PARTITIONS는 MySQL 5.1.5부터 사용 가능하다. 이것은 파티션된 테이블을 포함하는 쿼리를 실행할 때에만 유용하다. 보다 자세한 내용은 Section 16.3.4, “파티션 관련 정보 얻기”를 참조하도록 한다.
이 섹션에서는 쿼리 실행 플랜 정보를 얻기 위한 EXPLAIN사용법을 설명한다. DESCRIBE and SHOW COLUMNS 명령문에 대한 설명은 Section 13.3.1, “DESCRIBE 신텍스”, and Section 13.5.4.4, “SHOW COLUMNS 신텍스”를 참조하기 바란다.
EXPLAIN를 사용함으로써, 여러분은 테이블의 어느 곳에 인덱스를 추가해야만 열을 찾기 위한 SELECT가 보다 빠르게 되는지를 알 수 있게 된다. 또한, EXPLAIN를 사용하면 옵티마이저가 최적의 (optimal) 순서로 테이블을 조인 (join)할 수 있는지 여부도 검사할 수가 있다. SELECT 명령문에 명명되어 있는 테이블의 순서와 상응하게 조인 (join) 순서를 사용하도록 옵티마이저를 만들기 위해서는, 명령문에 SELECT만을 사용하는 대신에 SELECT STRAIGHT_JOIN을 사용해서 시작을 하도록 한다.

만일 여러분 생각에는 사용 되어야만 했을 인덱스가 사용되지 않은 상태로 문제를 일으키게 되면, 키의 기수 (cardinality)와 같은 테이블 상태를 업데이트 하기 위해 ANALYZE TABLE을 구동 시켜야 하는데, 이것은 옵티마이저의 선택에 영향을 미치게 된다. Section 13.5.2.1, “ANALYZE TABLE 신텍스”를 참조할 것.

EXPLAIN은 SELECT 명령문에서 사용된 각 테이블 정보 열을 리턴한다. MySQL이 쿼리를 처리하는 동안 읽을 수 있도록 테이블들은 결과물 안에 목록으로 정리가 된다. MySQL 은 single-sweep multi-join 방법을 사용해서 모든 조인 (join)을 풀어 버린다 (reslove). 이것은 MySQL이 첫 번째 테이블에서 열을 읽고, 그 다음에 두 번째 테이블에서 매치 (match)가 되는 것을 찾으며, 세 번째, 네 번째 등으로 이동을 한다는 것을 의미하는 것이다. 모든 테이블을 처리한 후에, MySQL은 선택된 컬럼을 내 보내고 테이블에서 매치되는 다른 열이 있을 때까지 테이블을 역으로 검사한다. 그 테이블에서 그 다음 열을 읽고 다음 테이블로 이동을 해서 동일한 과정을 반복 진행한다.
EXTENDED 키워드가 사용되면, EXPLAIN은 EXPLAIN 명령문 다음에 SHOW WARNINGS 명령문을 입력해서 볼 수 있는 기타 정보를 리턴한다. 이 정보는 옵티마이저가 SELECT 명령문에 있는 컬럼 이름과 테이블을 얼마나 많이 검증을 하였는지를 보여주며, SELECT는 최적화 과정에 관한 어플리케이션 재 작성과 최적화 규칙, 그리고 다른 가능한 노트 (notes)를 보여준다. MySQL 5.1.12 이후부터는, EXPLAIN EXTENDED 또한 filtered 컬럼을 출력한다.

Note: 동일한 EXPLAIN 명령문에서는 EXTENDED and PARTITIONS 키워드를 함께 사용할 수가 없다.

EXPLAIN를 통해서 나오는 각각의 결과 열은 하나의 테이블에 대한 정보이며, 각 열은 아래의 컬럼을 가지고 있다:
id
SELECT 아이덴티파이어 (identifier). 이것은 쿼리 안에 있는 SELECT의 순차적인 번호(sequential number)이다.
select_type
SELECT에 대한 타입이며, 아래의 테이블에 있는 것 중에 하나가 된다:

SIMPLE
Simple SELECT (not using UNION or subqueries)
PRIMARY
Outermost SELECT
UNION
Second or later SELECT statement in a UNION
DEPENDENT UNION
Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT
Result of a UNION.
SUBQUERY
First SELECT in subquery
DEPENDENT SUBQUERY
First SELECT in subquery, dependent on outer query
DERIVED
Derived table SELECT (subquery in FROM clause)
UNCACHEABLE SUBQUERY
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION
The second or later select in a UNION that belongs to an uncachable subquery (see UNCACHEABLE SUBQUERY)

DEPENDENT는 전형적으로는 상호 연관된 (correlated) 서브 쿼리(subquery)의 사용을 의미한다.

“DEPENDENT SUBQUERY” 값 평가 (evaluation) 와 UNCACHEABLE SUBQUERY값 평가는 서로 틀린 것이다. “DEPENDENT SUBQUERY”의 경우, 서브쿼리는 자신의 외부 컨텍스와는 다른 값을 가진 변수에 대해서만 한 번 재 평가를 한다. UNCACHEABLE SUBQUERY의 경우에는, 외부 컨텍스트의 각 열에 대해서 모두 재 평가를 한다.
table
결과 열이 참조하는 테이블.
type
조인 (join) 타입. 서로 다른 타입의 조인 (join)이 아래에 있는데, 가장 좋은 것부터 가장 나쁜 것의 순서로 되어 있다:
system
테이블은 하나의 열만을 가지고 있다 (= 시스템 테이블). 이것은 const 조인 (join) 타입의 특별한 경우이다.
const
테이블은 적어도 하나의 매칭 (matching) 테이블을 가지고 있는데, 쿼리가 시작되는 시점에서 이 테이블을 읽게 된다. 여기에는 하나의 열만이 존재하기 때문에, 이 열에 있는 컬럼에서 얻는 값은 나머지 옵티마이저에 의해 상수 (constant)로 인식될 수 있다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다.

const는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값 (constant value)과 비교를 할 때 사용된다. 아래의 쿼리에서 보면, tbl_name은 const 테이블 형태로 사용되고 있다:

SELECT * FROM tbl_name WHERE primery_key=1;

SELECT * FROM tbl_name
WHERE primery_key_part1=1 AND primery_key_part2=2;
eq_ref
이전 테이블로부터 각 열을 조합 (combination)하기 위해서 이 테이블의 열을 하나읽는다. system and const 타입과는 달리, 이것이 가장 최선의 가능 조인 (join) 타입이다. 이것은 조인 (join)에 의해 인덱스의 모든 부분이 사용될 때 쓰이게 되며, 이때 인덱스는 PRIMARY KEY 또는 UNIQUE 인덱스가 된다.

eq_ref는 = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용될 수 있다. 비교 값은 이 테이블 전에 읽었던 테이블에서 컬럼을 사용한 수식 또는 상수 (constant)가 될 수 있다. 아래의 예제에서 보면, MySQL은 ref_table를 처리하기 위해서 eq_ref 조인 (join)을 사용하고 있다:

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref
이전 테이블에서 읽어온 각각의 열을 조합하기 위해 이 테이블에서 매칭 (matching)되는 인덱스 값을 가진 모든 열을 읽어온다. 만일 조인 (join)이 키의 좌측 끝(leftmost) 접두사 만을 사용하거나 또는 키 값이 PRIMARY KEY 또는 UNIQUE 인덱스가 아니라면 (달리 말하면, 만일 조인 (join)이 키 값을 기반으로 한 단일 (single) 열을 선택하지 않는다면), ref가 사용된다. 만일 사용된 키가 적은 수의 열에 대해서만 매치가 된다면, 그것은 좋은 조인 (join) 타입인 것이다.

ref는 = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다. 아래의 예제에서 본다면, MySQL은 ref_table 처리 과정에서 ref 조인 (join)을 사용한다:

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref_or_null
이 조인 (join) 타입은 ref과 유사하지만, MySQL이 NULL 값을 가지고 있는 열에 대해서도 검색을 한다는 점에서 차이가 있다. 이 조인 (join) 타입 최적화는 서브 쿼리(subqueries)를 해석할 때 자주 사용된다. 아래의 예제에서 보면, MySQL은 ref_table처리 과정에서 ref_or_null 조인 (join)을 사용하고 있다:

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
index_merge
이 조인 (join) 타입은 인덱스 병합 최적화가 사용되었음을 나타낸다. 이 경우에, 결과 열에 있는 key 컬럼은 사용된 인덱스 리스트를 가지고 있고, key_len는 사용된 인덱스에 대해서 가장 긴 키 부분의 리스트를 가지고 있다.
unique_subquery
이 타입은 아래 형태의 IN 서브 쿼리 (subqueries)에 대해서 ref를 대체한다:

value IN (SELECT prime_key FROM single_table WHERE some_expr)

unique_subquery는 효율성을 위해서 서브 쿼리 (subquery)를 대체하는 인덱스 룩업(lookup) 함수이다.
index_subquery
이것은 unique_subquery와 유사한 조인 (join) 타입이다. 이것은 IN 서브 쿼리 (subqueries)를 대체하지만, 아래 형태의 서브 쿼리 (subquery)에 있는 논-유니크 (non-unique)인덱스에 대해서도 동작을 한다:

value IN (SELECT key_column FROM single_table WHERE some_expr)
range
주어진 범위에 들어 있는 열만을 추출하며, 열 선택은 인덱스를 사용한다. 결과 열에 있는 key 컬럼은 어떤 인덱스가 사용되었는지를 가리킨다. key_len은 사용된 키에서 가장 긴 부분을 가진다. ref 컬럼은 이 타입에 대해서는 NULL 값이 된다.

range는 키 컬럼이 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 또는 IN 연산자를 사용하는 상수 (constant)와 비교할 때 사용될 수 있다:

SELECT * FROM tbl_name
WHERE key_column = 10;

SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 AND 20;

SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);
index
이 조인 (join) 타입은 ALL과 동일하지만, 인덱스 트리 (index tree)만을 스캔한다는 점에서 다르다. 일반적으로, 보통의 인덱스 파일이 데이터 파일보다 작기 때문에, 이것은 ALL 보다는 빠르게 동작한다.

MySQL은 쿼리가 단일 인덱스의 일부분인 컬럼만을 사용할 때 이 조인 (join) 타입을 사용한다.
ALL
이전 테이블에서 읽어온 각각의 열을 조합하기 위해 전체 테이블 스캔을 실행한다. 테이블이 const가 표시되지 않은 첫 번째 테이블이고, 다른 모든 경우에 있어서 매우 좋지 않은 경우라면, 이것은 그리 좋은 경우가 아니다. 일반적인 경우에는, 이전 테이블에서 가져온 상수(constant) 값 또는 컬럼 값을 사용해서 테이블 열을 추출하는 인덱스를 추가하면 ALL을 피할 수가 있다.
possible_keys
possible_keys 컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 컬럼은 EXPLAIN 결과에서 나타나는 테이블 순서와는 전적으로 별개의 순서가 된다. 이것은, possible_keys에 있는 키 중에 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을 수도 있음을 의미하는 것이다.

만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 그러한 경우라면, 적절한 인덱스를 하나 생성한 후에, EXPLAIN을 다시 사용해서 쿼리를 검사한다.

테이블이 어떤 인덱스를 가지고 있는지를 보기 위해서는, SHOW INDEX FROM tbl_name를 사용한다.
key
key 컬럼은 MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다. 만일 MySQL이 possible_keys 인덱스 중의 하나를 사용해서 열을 검사할 예정이라면, 그 인덱스는 키 값 형태로 리스트된다.

key 이름을 possible_keys 값에는 존재하지 않는 인덱스로 지정하는 것은 가능하다. 이러한 경우는 possible_keys 인덱스 중의 어떠한 것도 열을 검사하기에는 적당하지 않지만, 쿼리가 선택한 모든 컬럼이 다른 인덱스 컬럼인 경우에 나타난다. 즉, 네임드 인덱스가 선택된 컬럼을 커버하기 때문에, 비록 어떤 열을 추출할지 결정할 때 그것이 사용되지는 않더라도, 인덱스 스캔이 데이터 열 스캔보다 효율적인 방법이 된다.

InnoDB의 경우에는, InnoDB가 각각의 세컨더리 인덱스를 사용해서 프라이머리 키 값을 저장하기 때문에 쿼리 역시 프라이머리 키를 선택하더라도, 세컨더리 인덱스는 선택된 컬럼을 커버할 수도 있다. 만일 key가 NULL이라면, MySQL은 쿼리를 보다 효율적으로 실행하기 위한 인덱스를 찾지 못하게 된다.

MySQL로 하여금 possible_keys 컬럼에 있는 인덱스를 사용하거나 또는 무시하도록 만들기 위해서, FORCE INDEX, USE INDEX, 또는 IGNORE INDEX를 쿼리에서 사용하도록 한다.

MyISAM and BDB 테이블의 경우에는, ANALYZE TABLE를 구동시키면 옵티마이저가 보다 좋은 인덱스를 선택하도록 도움을 줄 수가 있다. MyISAM 테이블의 경우에는, myisamchk –analyze 가 동일한 역할을 한다.
key_len
key_len 컬럼은 MySQL이 사용하기로 결정한 키의 길이를 나타낸다. 만일 key 컬럼이 NULL이라면, 이 값도 NULL이 된다. key_len 값은 다중-부분 (multiple-part) 키 중에 얼마나 많은 부분을 MySQL이 실제로 사용하는지를 여러분이 알 수 있도록 해 준다.
ref
ref 컬럼은 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 보여준다.
rows
rows 컬럼은 MySQL이 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다.
filtered
filtered 컬럼은 테이블 정의문이 필터링하는 테이블 열을 추정한 비율이다. 즉, rows는 조사된 열의 추정 숫자이며, rows × filtered / 100는 이전 테이블과 조인이 될 예정인 열의 숫자가 된다. 여러분이 EXPLAIN EXTENDED를 사용하게 되면, 이 컬럼이 출력된다 (MySQL 5.1.12에서 추가됨).
Extra
이 컬럼은 MySQL이 쿼리를 어떻게 해석하는지에 관한 추가적인 정보를 제공한다. 이 컬럼이 가질 수 있는 값은 다음과 같다. 쿼리를 가능한 한 빠르게 실행하고자 한다면, Using filesort and Using temporary의 Extra 값을 분석해 보도록 한다.
Distinct
MySQL은 명확한 값 (distinct value)를 찾게 되며, 따라서 MySQL이 매칭되는 열을 찾게 되면 더 이상의 열에 대해서는 검색을 중단한다.
Full scan on NULL key
옵티마이저가 인덱스-룩업 (index-lookup) 접속 방식을 사용할 수 없을 때 펄백 (fallback) 방식으로 서브 쿼리 최적화를 할 때 이 값이 나온다.
Impossible WHERE noticed after reading const tables
MySQL이 모든 const (system) 테이블 값을 읽었으며, WHERE 구문이 항상 거짓 (false)이라는 것을 알고 있다.
No tables
쿼리에 FROM 구문이 없거나, 또는 FROM DUAL 구문을 하나 가지고 있다.
Not exists
MySQL은 쿼리상에서 LEFT JOIN 최적화를 실행 했으며, 이 최적화와 매치되는 열을 찾은 후에는 더 이상 이 테이블에서 이전 열 조합 검색을 하지 않게 된다. 이러한 방식으로 최적화가 되는 쿼리 타입의 예는 다음과 같다:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

t2.id를 NOT NULL로 정의했다고 가정하자. 이와 같은 경우, MySQL은 t1을 스캔하고 t1.id 값을 사용해서 t2에 있는 열을 검색한다. 만일 MySQL이 t2에서 매칭되는 열을 발견하면, MySQL은 t2.id 가 결코 NULL이 아님을 알게 되며, 따라서 동일한 id 값을 가지고 있는 t2에서는 더 이상 열을 스캔하지 않게 된다. 달리 표현하면, t1에 있는 각 열에 대해서, MySQL은 t2에서는 단일 검색 (lookup)만을 하게 되며, t2에서 실제로 얼마나 많은 열이 매치가 되는지는 상관이 없게 된다.
range checked for each record (index map: N)
MySQL은 사용하기에 좋은 인덱스를 찾지 못했으나, 이전 테이블에서 컬럼 값을 찾고 난 후에는 사용할 수도 있을 법한 인덱스는 알아냈다. 이전 테이블에 있는 각 열 조합에 대해서는, MySQL은 그 조합이 열을 추출하기 위해서 range 또는 index_merge 접근 방식을 사용할 수 있는지를 검사한다. 이 방법은 그리 빠른 방법은 아니지만, 인덱스를 전혀 사용하지 않는 것 보다는 빠르게 진행한다.
Select tables optimized away
쿼리는 인덱스, 또는 MyISAM용 COUNT(*)을 사용하되 GROUP BY 구문은 사용하지 않은 채로 모두 처리된 집단 함수 (MIN(), MAX())만을 가지고 있다. 옵티마이저는 오직 하나의 열만을 리턴한다.
Using filesort
MySQL은 저장된 순서에 따라서 열을 추출하는 방법을 찾기 위해 기타 과정을 진행한다. 정렬 (sort)은 조인 (join) 타입과 정렬 키 and WHERE 구문과 매치가 되는 모든 열에 대한 열 포인터 (pointer)를 사용해서 모든 열에 걸쳐 진행 된다. 그런 다음에 그 키는 저장이 되고 열은 저장 순서에 따라서 추출된다. Section 7.2.11, “ORDER BY 최적화 하기”를 참조할 것.
Using index
인덱스 트리에 있는 정보만을 가지고 테이블에서 컬럼 정보를 추출한다. 쿼리가 단일 인덱스의 일부 컬럼만을 사용하는 경우에, 이러한 전략을 사용할 수가 있다.
Using join cache
조인 캐시 버퍼는 테이블을 부분적으로 읽어온 후에, 읽어 온 열을 사용해서 조인을 실행한다.
Using temporary
쿼리를 해석하기 위해서는, 결과를 저장할 임시 테이블을 하나 생성해야 한다. 만일 쿼리가 컬럼을 서로 다르게 목록화 하는 GROUP BY and ORDER BY 구문을 가지고 있는 경우에 이런 것이 일어나게 된다.
Using where
WHERE 구문은 다음 테이블에 대한 열 매치 (match) 또는 클라이언트에 보내지는 열을 제한하기 위해 사용된다. 테이블에서 모든 열을 조사하거나 불러올 의도가 특별히 없다면, Extra 값이 Using where 가 아니고, 테이블 조인 (join) 타입이 ALL 또는 index일 경우에는 쿼리에 문제가 생길 수도 있다.
Using sort_union(…), Using union(…), Using intersect(…)
이것들은 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합 (merge)이 되는지를 나타낸다.
Using index for group-by
Using index의 테이블 접속 방식과 유사하게, Using index for group-by는 디스크에 있는 실제 테이블을 추가적으로 접속하지 않은 채로 GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출할 때 사용할 수 있는 인덱스를 가리킨다.
Using where with pushed condition
테이블 접근에 대한 Using index 방식과 유사한 Using index for group-by 방식은MySQL이 실제 테이블을 추가적으로 검색을 하지 않고서도, GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출 (retrieve)하기 위해 사용될 수 있는 인덱스를 찾았음을 가리킨다. 또한, 그 인덱스는 각 그룹에 대해 가장 효과적인 방식으로 사용되기 때문에, 적은 수의 인덱스 엔트리만이 읽혀지게 된다.
Using where with pushed condition
이 아이템은 NDB Cluster 테이블에만 적용된다. 이것은 MySQL 클러스터가 인덱스가 되지 않은 컬럼 (non-indexed column)과 상수 (constant) 간의 직접 비교 (direct comparision (=))의 효율성을 개선하기 위해서 조건문을 푸시 다운 (condition pushdown) 하는 중이라는 의미를 갖는다. 이와 같은 경우, 조건문은 동시에 값이 검사되는 클러스터의 모든 데이터 노드로 “푸시 다운 (pushed down)” 된다. 이것은 매치되지 않는 열을 네트워크 전체에 보낼 필요성을 없애 주며, 조건문 푸시 다운을 하지 않는 경우에 비해서 5 ~ 10배의 속도 향상을 얻을 수가 있다.

여러분이 아래와 같은 클러스터 테이블을 가지고 있다고 가정하자:

CREATE TABLE t1 (
a INT,
b INT,
KEY(a)
) ENGINE=NDBCLUSTER;

이와 같은 경우, 조건문 푸시 다운은 아래와 같은 쿼리와 함께 사용될 수 있다:

SELECT a,b FROM t1 WHERE b = 10;

이것은 EXPLAIN SELECT 결과를 통해서 볼 수가 있는데, 그 결과는 다음과 같다:

mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where with pushed condition

조건문 푸시 다운은 다음의 쿼리와 함께 사용할 수 없다:

SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;

첫 번째 쿼리의 경우에는 조건문 푸시 다운을 적용할 수가 없는데, 그 이유는 인덱스가 컬럼 a에 존재하기 때문이다. 두 번째 경우에 대해서 조건문 푸시 다운을 사용할 수 없는 이유는, 인덱스가 되지 않은 컬럼 b를 포함하는 비교문이 간접적이기 때문이다. (하지만, WHERE 구문에서 b + 1 = 10 을 b = 9 로 줄여서 비교를 한다면 푸시 다운을 사용할 수가 있다.)

하지만, > 또는 < 연산자를 사용해서 인덱스된 컬럼을 상수(constant)와 비교를 하는 경우에는 조건문 푸시 다운을 사용할 수가 있다:

mysql> EXPLAIN SELECT a,b FROM t1 WHERE a<2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 2
Extra: Using where with pushed condition

조건문 푸시 다운에 관련해서는 아래의 사항을 기억하기 바란다:
조건문 푸시 다운은 MySQL 클러스터에만 관련이 있으며, 다른 스토리지 엔진을 사용하고 있는 테이블에 대해서 쿼리를 실행할 경우에는 발생하지 않는다.
조건문 푸시 다운 기능은 디폴트로 사용되지 않는다. 이 기능을 활성화 시키기 위해서는, mysqld을 –engine-condition-pushdown 옵션과 함께 시작하거나, 또는 아래의 명령문을 실행한다:
SET engine_condition_pushdown=On;

Note: BLOB 또는 TEXT 타입의 컬럼에 대해서는 조건문 푸시 다운이 지원되지 않는다.
여러분은 EXPLAIN 결과에 있는 rows 컬럼 값을 사용하면 좋은 조인 (join)을 얻는 방법을 알아 낼 수가 있다. 이것은 MySQL이 쿼리를 실행하기 위해서 조사해야 하는 열의 수가 얼마나 되는지 대략적으로 알려준다. 만일 여러분이 max_join_size 시스템 변수를 사용해서 쿼리를 제한하면, 이러한 열은 다중-테이블 SELECT 명령문을 실행해야 하는지 아니면 무시해야 하는지를 결정할 때 이 열 값을 사용할 수도 있다.

아래의 예제는 EXPLAIN에 의해 얻어진 정보를 가지고서 다중-테이블 조인을 어떻게 최적화 시키는 지를 설명하는 것이다.
다음과 같은 SELECT 명령문을 가지고 있으며, EXPLAIN을 사용해서 이것을 조사한다고 가정하자:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
위 예제에 대해서, 다음과 같은 가정을 하도록 하자:

비교가 되는 컬럼은 아래와 같이 선언 되었다:
Table
Column
Data Type
Tt
ActualPC
CHAR(10)
tt
AssignedPC
CHAR(10)
tt
ClientID
CHAR(10)
et
EMPLOYID
CHAR(15)
do
CUSTNMBR
CHAR(15)
테이블은 아래와 같은 인덱스를 가지고 있다:
Table
Index
tt
ActualPC
tt
AssignedPC
tt
ClientID
et
EMPLOYID (primary key)
do
CUSTNMBR (primary key)
tt.ActualPC 값은 골고루 분산되지 않는다.
최적화가 실행되지 전에, EXPLAIN 명령문은 다음과 같은 정보를 생성한다:

table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC, NULL NULL NULL 3872
ClientID,
ActualPC
range checked for each record (key map: 35)
type 은 모든 테이블에 대해 ALL이기 때문에, 이 결과 값은 MySQL이 모든 테이블에 대해서 하나의 카르테시안 (Cartesian) 값을 생성하는 중이라는 것을 가리킨다; 즉, 열에 대한 모든 조합. 이 과정은 각각의 테이블에 있는 열의 숫자를 조사해야 하기 때문에 시간이 오래 걸리게 된다. 위의 경우에는, 74 × 2135 × 74 × 3872 = 45,268,558,720 열이 된다.

만일 컬럼 상의 인덱스들을 동일한 타입과 크기로 선언할 경우에는, MySQL이 이를 효과적으로 처리하는데 한가지 문제가 생긴다. 위의 문장에서 보면, VARCHAR and CHAR의 크기를 동일하게 선언하면, 이것들은 동일한 것으로 간주가 된다. tt.ActualPC는 CHAR(10)로 선언 되었고 et.EMPLOYID는 CHAR(15)로 선언 되었기 때문에, 길이가 맞지 않게 된다.
컬럼 길이 간의 이러한 문제를 해결 하기 위해서는, ALTER TABLE를 사용해서 ActualPC의 길이를 10 개 문자에서 15 문자로 늘리도록 한다:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

이제 tt.ActualPC and et.EMPLOYID는 모두 VARCHAR(15)가 된다. EXPLAIN 명령문을 다시 실행하면 아래와 같은 결과를 얻게 된다:

table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
이 결과 값은 완벽하지는 않지만 많이 개선이 된 것이다: rows 값은 74 보다 작다. 이와 같은 실행은 수초 정도 걸린다.
두 번째 대안으로는, tt.AssignedPC = et_1.EMPLOYID 와 tt.ClientID = do.CUSTNMBR 비교에서 컬럼 길이가 일치하지 않는 것을 제거하는 것이다:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
-> MODIFY ClientID VARCHAR(15);

이와 같이 수정을 하면, EXPLAIN은 다음과 같은 결과를 만들게 된다:

table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

이렇게 하고 나면, 쿼리는 거의 최적화가 이루어진다. 이제 남아 있는 문제는, MySQL이 tt.ActualPC 컬럼에 있는 값들은 골고루 분포되어 있으나, tt 테이블에 대해서는 그렇지 않다고 가정하고 있다는 점이다. 다행스러운 것은, MySQL로 하여금 키 분포도를 분석하도록 만드는 것이 쉽다는 점이다:

mysql> ANALYZE TABLE tt;

추가적인 인덱스 정보를 사용하면, 조인 (join)은 완벽해 지고 EXPLAIN은 아래와 같은 결과를 만들게 된다:

table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
ClientID, where
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

EXPLAIN 결과에 있는 rows 컬럼은 MySQL 조인 (join) 최적화를 통해서 개선된다는 점을 알아두자. rows 가 만들어 내는 값과 쿼리가 리턴하는 열의 실제 숫자를 비교함으로써 그 숫자가 거의 일치하는지를 검사해야 한다. 만일 그 숫자가 많이 차이가 난다면, SELECT 명령문에서 STRAIGHT_JOIN을 사용하고 FROM 구문에서 테이블 순서를 다르게 하면 보다 나은 성능을 얻을 수가 있을 것이다.

http://javakorean.com/wp2/wp-content/uploads/2015/02/하이브레인넷-부설연구소-MySQL-Replication-설정과-몇-가지-테스트.pdf

출처 : 하이브레인넷-부설연구소—-MySQL-Replication-설정과-몇-가지-테스트