[MySQL] 6장 인덱스 | 인덱스 개념, 인덱스 내부 작동, 인덱스 실제 사용

 

인덱스 개념

데이터를 빠르게 찾을 수 있도록 도와주는 도구

인덱스가 없다면 MySQL은 전체 테이블 검색 ( Full Table Scan) 을 실행할 것 => 시간 측면에서 비효율적

 

인덱스의 종류

• 클러스터형 인덱스 : 영어사전, 하나만 가능, 알파벳 순서 정렬
• 보조 인덱스 : 여러개 설정 가능, 정렬 상관 X

 

USE market_db;

CREATE TABLE table1 (
	col1 INT PRIMARY KEY,
    col2 INT,
    col3 INT
);

SHOW INDEX FROM table1;

CREATE TABLE table2 (
	col1 INT PRIMARY KEY,
    col2 INT UNIQUE,
    col3 INT UNIQUE);

SHOW INDEX FROM table2;

DROP TABLE IF EXISTS member;
CREATE TABLE member (
  mem_id CHAR(8),
  mem_name VARCHAR(10),
  mem_number INT,
  addr CHAR(2)
);
  
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');

ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY (mem_id); -- PK 설정

ALTER TABLE member DROP PRIMARY KEY; -- PK 삭제

ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY (mem_name);

INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울'); -- 새 레코드 추가

ALTER TABLE member
	ADD CONSTRAINT
    UNIQUE (mem_id); -- 보조 인덱스 설정

ALTER TABLE member
	ADD CONSTRAINT
    UNIQUE (mem_name);
    
SELECT * FROM member;

 

---

 

인덱스의 내부 작동

 

• 노드 : 데이터가 저장되는 공간, 페이지로도 불림, 하나의 데이터에 하나의 페이지 필요

 

SELECT (검색)  : 속도 빠름

INSERT, UPDATE, DELETE (데이터 변경) : 속도 느림 => 페이지 분할 필요하기 때문

 

보조 인덱스 : 인덱스가 별도의 공간에 생성

 

 

---

 

인덱스 문법

 

CREATE (UNIQUE) INDEX : 인덱스 생성 ( 보조 인덱스 한정)

DROP (UNIQUE) INDEX : 인덱스 제거 (보조 인덱스 한정)

ANALYZE TABLE : 인덱스 적용

 

인덱스 활용

• SELECT 절에서 인덱스를 활용해서 검색을 하고 싶다면 WHERE 조건 속성에 인덱스가 설정되어 있어야 한다
• WHERE 조건 속성 가공 X ( ex. mem_name / 2 >= 14 )

 

 

SHOW INDEX FROM member;

SHOW TABLE STATUS LIKE 'member';

-- 단순 보조 인덱스 생성
CREATE INDEX idx_member_addr
	ON member (addr);
CREATE INDEX idx_member_mem_number
	ON member (mem_number);

-- 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_number
	ON member (mem_number); -- 오류 발생
CREATE UNIQUE INDEX idx_member_mem_name
	ON member (mem_name);

-- 생성된 인덱스 적용
ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

SELECT * FROM member;
SELECT mem_id, mem_name, addr FROM member;
SELECT mem_id, mem_name FROM member WHERE mem_name = '마마무';
SELECT * FROM member WHERE mem_number > 4;
SELECT * FROM member WHERE mem_number >= 1; -- MySQL이 알아서 효율적인 방법 판단
SELECT * FROM member WHERE mem_number*2 >= 14; -- 열 가공 시 인덱스 사용 X

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
ALTER TABLE member
	DROP PRIMARY KEY; -- PK 인덱스 제거

SELECT table_name, constraint_name
	FROM information_schema.referential_constraints
    WHERE constraint_schema = 'market_db'; -- 데이터베이스의 외래키 조회

ALTER TABLE buy
	DROP FOREIGN KEY buy_ibfk_1; -- 외래키 제거