데이터베이스-인덱스 내부작동2

지환·2023년 8월 31일

Mysql

목록 보기

14/17

출처| https://www.youtube.com/watch?v=85Zg0HUv_Eo&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=38
https://daegwonkim.tistory.com/177

내부작동2

클러스터형 인덱스에서 검색해보자. 만약 조관우를 검색한다면 단순히 몇 개 페이지를 읽을까? 루트 페이지(100번)의 리프 페이지(1000번) 한 개 씩만 검색하면 된다. 총 2개 페이지를 읽게 된다.

만약 보조 인덱스에서 검색한다면? 조관우를 검색 시에 인덱스 페이지의 루트 페이지(10번), 리프 페이지(100번), 그리고 데이터 페이지(1002번)를 읽게 되어 총3개의 페이지를 읽게된다.

클러스터형 인덱스가 보조 인덱스보다 빠르다.

INSERT INTO clustertbl VALUES('FNT', '푸니타');
INSERT INTO clustertbl VALUES('KAI', '카아이');

클러스터형 인덱스

이렇게 페이지 분할이 일어났다.

보조인덱스인 경우 fnt를 입력하는 경우

보조인덱스

정렬할 필요가 없기 때문에 추가만 했다.

실습

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS mixedtbl;
CREATE TABLE mixedtbl
( userID  CHAR(8) NOT NULL ,
  name    VARCHAR(10) NOT NULL,
  addr     char(2)
);
INSERT INTO mixedtbl VALUES('LSG', '이승기', '서울');
INSERT INTO mixedtbl VALUES('KBS', '김범수', '경남');
INSERT INTO mixedtbl VALUES('KKH', '김경호', '전남');
INSERT INTO mixedtbl VALUES('JYP', '조용필',  '경기');
INSERT INTO mixedtbl VALUES('SSK', '성시경', '서울');
INSERT INTO mixedtbl VALUES('LJB', '임재범',  '서울');
INSERT INTO mixedtbl VALUES('YJS', '윤종신',  '경남');
INSERT INTO mixedtbl VALUES('EJW', '은지원', '경북');
INSERT INTO mixedtbl VALUES('JKW', '조관우', '경기');
INSERT INTO mixedtbl VALUES('BBK', '바비킴',  '서울');


ALTER TABLE mixedtbl
	ADD CONSTRAINT PK_mixedtbl_userID
		PRIMARY KEY (userID);

ALTER TABLE mixedtbl
	ADD CONSTRAINT UK_mixedtbl_name
		UNIQUE (name) ; -- 보조 인덱스를 만듦

SHOW INDEX FROM mixedtbl;

SELECT addr FROM mixedtbl WHERE name = '임재범';

보조 클러스터 페이지 내부 작동

인덱스를 검색하기 위한 일차 조건은 WHERE절에 해당 인덱스를 생성한 열의 이름이 나와야 한다.

물론 WHERE절에 해당 인덱스를 생성한 열 이름이 나와도 인덱스를 사용하지 않는 경우도 많다.

인덱스 문법

CREATE INDEX

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용해야 한다.

UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.

CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다.

그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.

ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어준다. 기본은 ASC로 만들어지며, 일반적으로 DESC로 만드는 경우는 거의 없다.

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

CREATE INDEX idx_member_addr
    ON member (addr);
    
SHOW INDEX FROM member;

CREATE UNIQUE INDEX idx_member_name
    ON member (mem_name);

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

인덱스를 실질적으로 활용하기 위해서는 WHERE 문을 사용해야 한다.

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

MySQL의 경우 WHERE 문에 작성된 조건에 따라 그 효율성을 먼저 따져, 만약 인덱스 검색의 효율이 더 좋다면 인덱스 검색을, 그렇지 않다면 테이블 전체 검색을 수행한다.

따라서 되도록 WHERE 절에 작성된 열 이름에는 가공을 하지 않는 것이 바람직하다.

DROP INDEX

인덱스를 제거하기 위해서는 DROP INDEX 문을 사용하고 형식은 다음과 같다.

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

주의할 점은 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다는 것이다.

이 경우 ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.

ALTER TABLE member
    DROP PRIMARY KEY;

실습

USE sqldb;
SELECT * FROM usertbl;

USE sqldb;
SHOW INDEX FROM usertbl;

SHOW TABLE STATUS LIKE 'usertbl';

CREATE INDEX idx_usertbl_addr -- unique를 쓰지 않았기 때문에 중복 허용이다. Non_unique 0[중복허용x] / 1[중복허용]
   ON usertbl (addr);
   
SHOW INDEX FROM usertbl;

SHOW TABLE STATUS LIKE 'usertbl';

ANALYZE TABLE usertbl;
SHOW TABLE STATUS LIKE 'usertbl';

CREATE UNIQUE INDEX idx_usertbl_birtyYear
	ON usertbl (birthYear);

CREATE UNIQUE INDEX idx_usertbl_name
	ON usertbl (name);

SHOW INDEX FROM usertbl;

INSERT INTO usertbl VALUES('GPS', '김범수', 1983, '미국', NULL  , NULL  , 162, NULL);

CREATE INDEX idx_usertbl_name_birthYear
	ON usertbl (name,birthYear);
DROP INDEX idx_usertbl_name ON usertbl;

SHOW INDEX FROM usertbl;

SELECT * FROM usertbl WHERE name = '윤종신' and birthYear = '1969';

CREATE INDEX idx_usertbl_mobile1
	ON usertbl (mobile1);

SELECT * FROM usertbl WHERE mobile1 = '011';

SHOW INDEX FROM usertbl;

DROP INDEX idx_usertbl_addr ON usertbl;
DROP INDEX idx_usertbl_name_birthYear ON usertbl;
DROP INDEX idx_usertbl_mobile1 ON usertbl;

ALTER TABLE usertbl DROP INDEX idx_usertbl_addr;
ALTER TABLE usertbl DROP INDEX idx_usertbl_name_birthYear;
ALTER TABLE usertbl DROP INDEX idx_usertbl_mobile1;

ALTER TABLE usertbl DROP PRIMARY KEY;

SELECT table_name, constraint_name
    FROM information_schema.referential_constraints
    WHERE constraint_schema = 'sqldb'; -- 이 쿼리문으로 fk 키를 알아내고 /// 이후에 fk 제거하고  - pk 제거

ALTER TABLE buyTbl DROP FOREIGN KEY buytbl_ibfk_1;
ALTER TABLE usertbl DROP PRIMARY KEY;