[MySQL] INDEX 확인, 추가, 삭제

NULL 은 INDEX 가 만들어지지 않는다. 따라서 NULL 이 많은 항목은 INDEX 로는 부적합하다.

INDEX 와 JOIN 에 대한 이해만 정확하게 해도 최적화된 SQL 을 작성하는데 큰 도움이 된다.

INDEX란, 데이터의 고유성을 확인하거나 검색 성능을 향상시키기 위해 사용하는 객체이다. 특정 칼럼(column)에 INDEX를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.

INDEX를 생성할 때 수행하는 정렬 작업은 이후 해당 데이터를 빠르게 검색하는데 중요한 역할을 한다.

INDEX 설계는 이론적으로 설계 단계에서 완성해야 한다. 프로젝트 운영중에도 INDEX 설계를 해야 하는 경우는 자주 발생한다. 요구사항 변경으로 설계 단계에서 예측하지 못했던 INDEX 가 필요해질 수 있기 때문이다.

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

기본적으로 조건절(WHERE) 에 등장하는 칼럼과 조인 조건으로 사용되는 칼럼에는 INDEX를 만드는 것이 성능면에서 유리하다.

조인 조건으로 사용된 칼럼에 INDEX 가 없으면 FULL TABLE SCAN 이 발생하면서 성능이 저하된다.

스칼라 서브쿼리 내에서 사용하는 조인 조건 칼럼에도 INDEX 를 생성해야 한다.

조회는 거의 하지 않고 데이터의 입력 및 변경 작업을 주로 하는 테이블은 최소한의 INDEX를 생성하는 것이 좋다.

ORDER BY 절에 제시되는 칼럼에도 INDEX를 생성하면 정렬 부하를 줄일 수 있지만, 조건절에 자주 등장하는 칼럼과 적절하게 조합하여 INDEX 를 만들면 훨씬 큰 성능을 기대할 수 있다.

UNIQUE INDEX 란 인덱스 안에 있는 Key 값에 중복되는 데이터가 없다는 뜻이다. UINQUE INDEX 가 설정되어 있다는 뜻은 해당 테이블의 칼럼에 중복된 값이 없다는 뜻이고, 앞으로도 중복된 값이 들어올 수 없다는 뜻이다.

mysql > show index from 테이블명;  -- 테이블에 생성된 인덱스 조회

mysql > alter table 테이블명 ADD UNIQUE INDEX (칼럼명);

(칼럼1, 칼럼2) 등 다중으로 할 수도 있다.

mysql > alter table 테이블명 ADD INDEX (칼럼명);

mysql > alter table 테이블명 DROP INDEX 칼럼명;

INDEX 를 추가/삭제를 한 테이블을 내보내기를 해서 보자.

CREATE TABLE IF NOT EXISTS `rand_mp3file` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `relatedno` int(11) NOT NULL DEFAULT '0',
  `mp3file` varchar(12) NOT NULL,
  `mp3rand` varchar(30) NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `display` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `mp3rand` (`mp3rand`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

처음 테이블 생성시에 INDEX 추가 명령어를 몰라도 추가하고 난 다음에 테이블을 내보내기해서 보면 인덱스가 어떻게 설정되어 있는지 확인할 수 있다.

phpmyadmin 상에서는 더 쉽게 인덱스를 추가할 수 있다.

그런데 간혹 동작이 안되는 경우가 있다. MySQL 에서 직접 해보면 에러가 발생해서 안되는 것이었다.

에러 메시지가 어떤 것인지 확인이 안되다보니...

인덱스(INDEX)를 사용하지 못하는 경우

ㅇ 칼럼의 내외부 변형

ㅇ LIKE 검색시 앞에 %를 사용

ㅇ 여러 칼럼에 대한 OR 조건 사용 : OR 를 사용하여 여러 칼럼에 대한 조건을 나열할 때,

     INDEX를 포함하지 않는 칼럼이 있다면, FULL TABLE SCAN 을 한다.

     OR 로 연결된 조건을 비교하는 작업을 수행할 때는 OR 뒤부터 비교를 시작하므로,

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

ㅇ 부정형(<>, !=, NOT IN, NOT EXISTS) 비교, IS NULL, IS NOT NULL 을 사용한 비교