Jins' Dev Inside

 EXPLAIN 은 어느정도 수준있는 DB를 다루는 개발자라면 꼭 알아야할 정도로 중요한 MySQL 의 쿼리문이다.

 실제로 많은 데이터베이스 관련 서적들에서도 최적화를 위한 선행 단계로 추천하고 있을 만큼 MySQL 의 강력하고 효과적인 쿼리문이다.

 EXPLAIN 키워드는 MySQL 에게 쿼리문의 실행 계획을 물어보는 키워드이다.

 Explain 구문을 이용해서 SQL Query를 수행하기 전에 데이터를 어떻게 가져올 건지에 대한 시스템의 실행계획을 받아볼 수 있다.

주로 쿼리 퍼포먼스 측정을 위해 Explain 을 많이 사용하지만 매 쿼리를 코드에 삽입할 때 테스트해보는 습관을 들이는 것이 좋다.

 SELECT 구문에서 explain 을 사용하는 방법은 단순히 키워드 앞에 붙여주기만 하면 된다. 단 SELECT 구문이 아닐 경우에는 INSERT, UPDATE, DELETE 등의 구문을 SELECT로 재구성시켜줘야 한다.

(ex) EXPLAIN select * from members

      UPDATE name from members where member_id = ‘1’

       EXPLAIN SELECT name from members (UPDATE 후)

 EXPLAIN 에서 각 칼럼은 다음과 같은 의미를 갖는다.

Select_type – 간단한 쿼리인지 복잡한 쿼리인지를 나타낸다.

Table – 어떤 테이블에 접근하는지를 나타낸다. 복잡한 쿼리문에서도 어떤 테이블에 실질적으로 접근하는지를 알 수 있다.

Type – 조인 방식이자, 테이블에서 해당 레코드를 어떻게 찾아가는지를 나타내며 퍼포먼스 측정에 있어서 중요한 지표이다. 

(ALL-풀스캔, INDEX-유사 풀스캔, RANGE-제한된 인덱스 스캔, REF-부분적인 값에 매칭되는 부분만 검사, EQ_REF-단 하나의 

값만 참조하는 경우, CONST-쿼리 일부를 상수로 대채시켜서 찾음. SYSTEM-무조건 하나의 열만을 갖는 테이블)

Possible Key- 해당 조회문에서 MySQL이 선택한 인덱스를 나타낸다.

Key – MySQL이 실제로 사용할 키를 나타낸다. 

Key len – 인덱스 필드가 가질 수 있는 최대길이

Ref – 키 칼럼의 인덱스를 찾기 위해 선행 테이블의 어떤 칼럼이 사용되었는지

Row – 원하는 행을 찾기 위해 읽어야 하는 예측 Row 카운트

Extra – 각종 조건문이 사용되는지 여부를 나타낸다.

 특히 많은 종류의 웹서비스가 SELECT 에 대한 처리를 주로 하는 경우가 많다.(SELECT 가 주가 아닌 데이터라면 MySQL 을 사용하지 않을것이다.) 

가령 Key 로 잡히지 않은 Column 을 조건으로 SELECT 쿼리를 수행하는 경우, 특히 유저 DB라면, FULL SCAN 이 발생해 막대한 비용이 들게 된다...

 이런 참사를 개발시에 눈치챌 수도 있지만, 본인이 DB 구조를 잘 모르거나 구조가 매우 복잡한 레거시 프로젝트에 투입된 상황이라면, EXPLAIN 의 생활화는 큰 도움이 될 것이다.

 Primary key(이하 PK)는 한 개 혹은 여러 개의 칼럼으로 테이블 내의 각 행들을 구별하기 위한 목적을 갖고 있다.

 PK는 그 자체만으로 Unique 하며 만약 여러 개의 Column이 PK로 묶여 있다면, 해당 값들의 조합이 반드시 Unique 해지게 된다.

 PK는 NULL 값을 갖을 수 없고(NOT NULL), Unique key라는 특징을 갖게 된다. 단순하게는 NOT NULL & UNIQUE == PK 라고 생각하면 편하다. 물론 좀더 디테일하게 보자면 차이가 있다.

 MySQL에서는 Int 작업이 빠르기 때문에 PK는 Int형으로 지정하는 것이 좋다. 

 Unique Key(Unique index)는 value의 값을 유일하게 만드는 column의 제약으로 PK와는 조금 다르게 NULL값이 가능하며, 역시 여러 개로 묶어서 Combination Index 를 만드는 것이 가능하다.

 * MySQL에서 Key와 Index는 동의어이다. (PRIMARY KEY 가 아니라 Key 가 Index와 동의어이다. 물론 PK 는 Index로 취급되긴 한다.)

 Unique Key 와 PK 의 차이점은, NULL 허용 여부와 의미 상의 차이가 있다. PK 로 지정된 Column 들은 Table 의 Row 를 구분하는 대표 값들이라는 Identity 를 갖지만, Unique Key 는 그보다는 좀더 제약(Constraint) 에 가깝다. 물론 "Unique" 해야 하기 때문에 구분하는 대표값으로 사용해도 무방하지만, PK 처럼 테이블의 대표값라고 취급하는 것은 위험하다.

 MySQL PK 지정 예시. (생성시)

(ex) Create table t(

id int not null, 

name char(10) not null, 

contents text, 

primary key(id, name));

 MySQL PK 추가 예시. (테이블에 추가)

(ex) Alter table 테이블 Add Primary key(칼럼)

 Key 값 조회하는 방법 예시.

(ex) Show Keys from 테이블

 물론 복잡한 DB 쿼리를 다루었던 프로젝트에서는 데이터 무결성을 효율적으로 관리하던 Constraint 를 본적이 있었지만, 경험상 실무에서 Unique Key Constraint 보다는 주로 PK로 값 연결하는 일이 많았다. 

 Unique Key Constraint 는 데이터 무결성에 있어서 좋은 도구이기는 하나... Constraint 도구들이 실 서비스에서도 유용하다고 볼순 없기 때문에 PK 만큼 유용하게 사용되지는 않는 듯 하다.