'mysql index'에 해당되는 글 1건

NULL 은 INDEX 만들어지지 않는다. NULL 이 많은 항목은 INDEX 부적합하다.

INDEX란, 데이터의 고유성을 확인하거나 검색 성능을 향상시키기 위해 사용하는 객체이다.

특정 칼럼에 INDEX를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.

INDEX의 가장 중요한 기능은 데이터의 조회 속도를 빠르게 하는 것이다.

속도 향상을 위해 index를 설정한다.
INDEX 를 걸었지만, SELECT 문이 제대로 인덱스를 타고 있는지 알아볼 필요가 있다.
MySQL 에서 쿼리의 실행 계획을 확인하려면 explain 명령을 사용하면 된다.

explain 명령은 SELECT 문 앞에 explain SELECT 를 붙이기만 하면 된다.

const : 매치되는 행이 하나만 존재

하나의 행이기 때문에 각 컬럼값은 나머지 연산에서 상수로 간주되며, 처음 한번만 읽어들이면 되기 때문에 엄청 빠르다.

possible_keys : 쿼리를 튜닝하는데 도움이 안되므로 possible_keys 칼럼은 그냥 무시해도 된다.

key : 사용된 인덱스를 나타내고, 쿼리를 튜닝할 때 key 칼럼에 의도했던 인덱스가 표시되는지

        확인하는 것이 중요하다.

        실행계획의 type 이 ALL 이면 key 칼럼은 NULL로 표시된다.

        실행계획의 type 이 index_merge 가 아닌 경우에는 반드시 테이블당 하나의 인덱스만 이용할 수 있다.

key_len : 사용된 가장 긴 key 부분을 나타. 실제 업무는 다중 칼럼으로 만들어진 인덱스가 더 많다.

             쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇개의 칼럼이 사용되었는지 알려준다.

ref : 접근 방법이 ref 방식이면 참조 조건으로 어떤 값이 제공되었는지 보여준다.

range : 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 <, >, <=, >=, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.

모든 쿼리가 이 접근 방법만 사용해도 어느 정도의 성능을 보장된다고 볼 수 있다.

- ref 컬럼은 이 타입의 조인에서 NULL 임

LIKE 검색을 할 경우 FULL SCAN 하는지 확인이 가능하다.

ALL : Full TABLE Scan 된다.

대부분의 경우에 아주 느린 성능을 보인다.가장 마지막에 선택되는 가장 비효율적인 방법이다. 빠른 응답을 사용자에게 보내줘야 하는 웹서비스와 같은 환경에서는 적합하지 않다.
LIKE 검색시 앞에 %를 붙이지 않으면 INDEX 를 탄다는 걸 알 수 있다.

이제 인덱스를 제거하고 다시 explain 해보자.

mysql > ALTER TABLE `bbs_file` DROP INDEX `mp3file`;

WHERE 조건에 사용되는 칼럼 mp3file 을 INDEX를 삭제하고 나서 LIKE 검색을 했더니 ALL(Full Scan)으로 변경되었다.

WHERE 조건에 사용되는 칼럼에 INDEX 가 설정되어 있어야만 검색속도가 빠르게 될 수 있다는 걸 확인했다.

mysql > ALTER TABLE `bbs_file` ADD UNIQUE (`mp3file`); -- mp3file 칼럼에 인덱스 추가

이제 두개의 테이블을 조인해서 원하는 결과를 조회하는 걸 해보자.

explain select d.uid, d.no, f.rand from bbs_data d, bbs_file f where d.no = f.no;
explain select d.uid, d.no, f.rand from bbs_file f, bbs_data d where f.no = d.no;

조인하는 테이블 순서는 상관이 없다는 걸 테스트해서 확인했다.

처음에는 ALL(Full Scan) 하고 다음에는 eq_ref 가 나왔다.

eq_ref : 조인수행을 위해 각 테이블에서 하나씩의 행만이 읽혀지는 형태.

조인(INNER JOIN) 에서 두번째 이후에 읽는 테이블(bbs_file)에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

- const 타입이외에 가장 훌륭한 조인타입이다.

- 조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우.

- 인덱스된 컬럼이 = 연산에 사용되는 경우.

- 비교되는 값은 상수이거나 이전조인결과의 컬럼값일 수 있다.

explain SELECT uid, no, (select classname from bbs_category where uid=bbs_data.category1) as cate1, (select classname from bbs_category where uid=bbs_data.category2) as cate2 from bbs_data;

더 많은 정보를 알고 싶으면 http://database.sarang.net/?inc=read&aid=24199&criteria=mysql 를 참조하세요.

성능 튜닝 문제 유형

ㅇ 조건절에서 비교하는 칼럼에 인덱스가 없는 경우

    - 조건절에 사용되는 칼럼 가운데 '='로 비교하는 칼럼을 선두에 두고 결합 인덱스를 생성하는 것이 좋다.

    - 조건절에서 사용하는 칼럼이 결합 인덱스의 선두 칼럼을 구성되어 있어야 성능 검색에 유리하다.

ㅇ 조인 조건으로 사용된 칼럼에 인덱스가 없는 경우

    - DBMS가 먼저 접근하는 테이블을 드라이빙 테이블이라고 한다.

    - 드라이빙 테이블을 어떤 테이블로 하느냐에 따라 전체적인 조인 수행 속도에 영향을 끼칠 수 있다.

ㅇ 스칼라 서브쿼리의 조건으로 사용된 칼럼에 인덱스가 없는 경우

ㅇ 조건절에 사용된 칼럼의 내/외부 변형으로 인덱스를 사용할 수 없는 경우

ㅇ IS NULL, IS NOT NULL을 사용한 비교로 인덱스를 사용할 수 없는 경우

ㅇ LIKE 로 비교하는 값의 앞에 %를 사용하여 인덱스를 사용할 수 없는 경우

    - 비교할 값의 앞에는 가급적 %를 사용하지 않아야 한다.

ㅇ OR로 연결된 조건 비교로 인덱스를 사용할 수 없는 경우

    - OR 구문을 사용해 여러 조건을 나열할 때에는,

      결과를 만족하는 값이 많은 조건을 OR 뒤에 두는 것이 성능면에서 유리하다.

    - OR로 연결된 조건을 비교하는 작업을 수행할 때에는 OR 뒤에서 부터 비교를 시작하기 때문이다.

ㅇ MINUS 구문 사용으로 인덱스를 사용할 수 없는 경우

ㅇ 부정형 비교로 인덱스를 사용할 수 없는 경우

    - <>, !=, NOT IN, NOT EXISTS 등의 표현식을 사용한 SQL

ㅇ 조인 후 GROUP BY 수행으로 비용이 증가한 경우

ㅇ IF조건절 및 함수를 과다하게 사용한 경우

ㅇ DISTINCT 구문 사용으로 테이블 전체를 검색하는 경우

ㅇ UNION ALL 구문 사용으로 테이블 전체를 검색하는 경우

※ 아주 기본적인 SQL만 제대로 이해하고 구현해도 SQL 성능의 많은 부분을 튜닝하는 것이 가능하다.

   SQL 튜닝의 최종적인 책임은 DBMS가 아닌 개발자에게 있음을 잊지 말아야 한다.