MySQL 개체 훑어보기 : ALTER, 인덱스, 뷰, Stored Procedure, 트리거

인덱스

 

mongodb에 설정한 인덱스와 기능이 똑같습니다. 많은 양의 데이터를 검색할 때 전체 테이블을 읽지 않고 정렬된 순서대로 인덱스를 스캔하여 이루어져서 쿼리 결과의 반환이 빨라집니다. 설정된 컬럼값을 포함한 데이터 CRUD가 이루어 질 때 인덱스도 함께 수정되어야 합니다.

 

필자는 특이하게 index를 mongoDB에서 먼저 배웠다. 동일한 개념이 MySQL과 같은 관계형 DB에서도 사용된다.

DB 튜닝을 위해서 적절하게 index를 설정해야 하는 건 필수라고 할 수 있다. 

 

아래와 같이 Full Table Scan을 실행했다면 잘못사용하고 있는 것이다. Workbench의 Execution Plan을 통해 살펴보면 특정 쿼리를 처리하는 실행 과정을 살펴볼 수 있다.

이렇게 Full Table Scan을 하면 망한거다

 

 

1. 인덱스 생성 및 일반 인덱스, 유니크 인덱스

 

create index 명령어를 통해 인덱스를 생성할 수 있습니다.

아래 코드는 col1Indx라는 이름의 인덱스를 test 테이블의 col1 열에 생성하라는 것입니다.

CREATE INDEX col1Idx ON test (col1)

 

테이블에 적용된 인덱스를 확인하고 싶다면 show index 명령을 사용하면 됩니다.

SHOW INDEX FROM test

 

col1 열에 col1Idx라는 인덱스가 설정된 것을 볼 수 있습니다.

그런데 PRIMARY KEY에는 자동으로 인덱스가 붙어있네요? 자동으로 PK에는 인덱스가 붙어 있습니다.

따라서 무언가를 찾을 때 PK로 찾으면 좀 더 빠르게 찾을 수 있겠죠?

 

유니크 인덱스

 

create unique index를 이용하면 유니크한 (중복값을 허용하지 않는) 인덱스를 만들 수 있습니다.

CREATE UNIQUE INDEX col2Idx
ON test (col2)

 

desc test를 통해 테이블 정보를 표시하도록 만들었더니 KEY에서 col2가 unique한 키를 가졌다고 표시되네요.

 

 

2. FULLTEXT INDEX

 

일반적인 인덱스와 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색할 수 있게 됩니다. 각 테이블의 모든 텍스트 컬럼을 검색

 

ALTER TABLE test
ADD FULLTEXT Col3Idx(col3);

 

index_type을 살펴보면 FULLTEXT가 사용된 것을 알 수 있습니다.

 

 

3. 인덱스 삭제

 

drop index [key 이름]을 통해 인덱스를 삭제할 수 있습니다.

// alter table [테이블] drop index [키 이름]
ALTER TABLE test drop index Col3Idx;  

// drop index [키 이름] on [테이블]
DROP INDEX col2Idx ON test;

 

 

 

뷰(view)

 

mongodb에도 뷰가 있죠? 그겁니다. 보여주는 역할을 합니다.

 

뷰는 DB에 존재하는 일종의 가상 테이블로, 행/열을 가지고는 있지만 실제로 데이터를 저장하지는 않습니다. MySQL에서의 뷰는 다른 테이블, 뷰에 저장되어 있는 데이터를 보여주는 역할만 합니다.

 

뷰를 사용하면 여러 테이블, 뷰를 하나의 테이블처럼 볼 수 있습니다.

 

뷰의 장점

 - 특정 사용자에게 테이블 전체가 아닌 필요한 컬럼만 보여줄 수 있음(mongodb로 치면 projection)

 - 복잡한 쿼리를 단순화해서 사용하며 쿼리를 재사용할 수 있다.

 

뷰의 단점

 - 한 번 정의된 뷰는 변경할 수 없음

 - 삽입, 삭제, 갱신 작업에 제한 사항이 있으며 자신만의 인덱스를 가질 수 없음

 

 

create view를 통해 view를 생성할 수 있습니다.

CREATE VIEW testView AS 
SELECT col1, col2
FROM test

 

 

생성한 View를 보고 싶으면 테이블을 보듯 그냥 보면 됩니다.

SELECT * FROM testView;

 

수정하고 싶을 때는 alter view를 이용하면됩니다.

ALTER VIEW testview AS
SELECT col1, col2, col3
FROM test;

 

삭제할 때는 drop view를 이용하면 됩니다

DROP VIEW testview

 

자, 그러면 도대체 view를 어떨 때 사용해야 하느냐,

 

다음과 같이 복잡한 쿼리의 결과물을 allview라는 뷰로 생성합니다.

CREATE VIEW allview as
SELECT city.Name, country.SurfaceArea, city.population, countrylanguage.Language FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode;
WHERE city.CountyCode = "KOR";

그리고 필요할 때 해당 뷰를 통해 보면 됩니다. 참 쉽죠!

SELECT * FROM allview;

 

 

Stored Procedure

 

MySQL에서 제공하는 프로그래밍 기능을 말한다. 

간단하게 긴 줄의 명령어들을 함수처럼 묶어서 처리할 수 있다고 보면 된다.

 

대강 아래와 같은 꼴로 사용한다.

DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
    SELECT * FROM membertbl;
    SELECT * FROM producttbl;
END //
DELIMITER ;

CALL myProc() ;

 

트리거

 

TypeORM의 Listener와 같은 개념이다. (darrengwon.tistory.com/886?category=921419)

Update, Insert, Delete 작업이 발생하면 자동으로 처리되는 코드를 말한다. 

 

추후에 알아보도록하자.