인덱스의 생성과 제거 문법

인덱스 생성의 간단한 형태만 보면

CREATE [UNIQUE] INDEX 인덱스_이름
	ON 테이블_이름 (열_이름) [ASC| DESC]

 

인덱스 제거 문법은

DROP INDEX 인덱스_이름 ON 테이블_이름

 

 

SHOW INDEX FROM member;

 

 

 

Data_length가 16364가 나오는데 전체의 데이터가 저장되는데 16kb가 나오게 되는것(한 페이지 짜리 책이라고 보면 된다.)

SHOW TABLE STATUS LIKE 'member';

 

보조인덱스를 추가해보자.

Non_unique가 1이면 중복을 허용

CREATE INDEX idx_member_addr 
   ON member (addr);

SHOW INDEX FROM member;

상세하게 데이터를 확인해보면

SHOW TABLE STATUS LIKE 'member';

 

INDEX_LENGTH를 볼려면 ANALYZE를 해주고 볼 수 있다. 역시 16kb 한 페이지 짜리다.

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

 

UNIQUE 인덱스를 만들어보면

CREATE UNIQUE INDEX idx_member_mem_number
	ON member (mem_number); -- 오류발생 사용 x UNIQUE 제거하고 만들어야함
    

CREATE UNIQUE INDEX idx_member_mem_name
    ON member (mem_name);

    
INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');
-- UNIQUE INDEX를 만들었기 때문에 에러가 난다.

ANALYZE TABLE member;  -- 지금까지 만든 인덱스를 모두 적용
SHOW INDEX FROM member;

 

실행하면 인덱스를 조회하지 않고 실행시킨 것을 Execution Plan을 통해서 알 수 있다. 

즉 SELECT 다음에 나온 것은 INDEX 사용여부와 관계가 없다.

SELECT * FROM member;

 

아래도 마찬가지로 SELECT 다음에 나온것은 INDEX와 관계가 없다.

SELECT mem_id, mem_name, addr FROM member;

 

 

INDEX를 사용하기 위해서는 WHERE절의 조건식이 나와야 한다.

SELECT mem_id, mem_name, addr 
    FROM member 
    WHERE mem_name = '에이핑크';

 

 

mem_number에도 INDEX를 적용해보면

CREATE INDEX idx_member_mem_number
    ON member (mem_number);
ANALYZE TABLE member; -- 인덱스 적용

 

 

 

 

WHERE절이 있으므로 INDEX를 사용한다.

SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 7;

 

 

 

만약 아래와 같이 >=1 이라고 하면 INDEX를 쓰지않는다. sql쪽에서 자체적으로 비효율적이라고 판단

SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 1;

 

첫번째꺼는 인덱스를 안쓰고 두번째꺼(더 효율적)는 인덱스를 쓰는데 결과는 똑같다.

SELECT mem_name, mem_number
	FROM member
    WHERE mem_number * 2 >=14;
    
    
SELECT mem_name, mem_number
	FROM member
    WHERE mem_number >= 14/2;

 

인덱스 이름을 아래와 같이 제거하고

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

 

 

PRIMARY KEY를 제거할려면 아래와 같이 한다. 맨처음에 하면 에러가 나는데 PK FK로 회원 테이블과 구매 테이블이 연결이 되어있어서 에러가 난다.

구매 테이블 외래키 먼저 지우고(외래키 이름은 아래 SELECT 구문을 이용해서) 기본키를 지우면 된다.

ALTER TABLE member 
    DROP PRIMARY KEY;


SELECT table_name, constraint_name
    FROM information_schema.referential_constraints
    WHERE constraint_schema = 'market_db';

ALTER TABLE buy 
    DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member 
    DROP PRIMARY KEY;

 

 

 

 

 

 

 

 

 

<출처 한빛미디어: 인덱스의 생성과 제거 문법(CREATE INDEX, DROP INDEX)> 

https://www.youtube.com/watch?v=aiMSluMNzI8&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=18