[데이터베이스] Explain SQL, Index

Explain SQL

SELECT/UPDATE/INSERT/DELETE 등의 쿼리가 어떻게 수행되는지 내부를 보여주는 SQL 명령

 

MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여주므로 이를 바탕으로 느리게 동작하는 쿼리의 최적화가 가능해집니다!

 

Explain SQL 사용 방식

Explain 뒤에 분석하고 싶은 쿼리 문을 작성

EXPLAIN
INSERT INTO comment (id, content, parent_comment_id, user_id, article_id)
VALUES (1, '오늘 저녁은 김치찌개', NULL, 9, 6);

 

 

 

---

Index

테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조

 

• 검색을 위해 자주 사용되는 컬럼이 있다면, 인덱스를 생성해주는 것이 큰 도움이 됩니다.
• Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 인덱스를 가집니다.
  • UNIQUE와 NOT NULL이 설정된 컬럼도 해당됩니다.
  • 하지만 한 테이블 내에 Primary Key와 UNIQUE+NOT NULL이 두 개가 있다면, Primary Key가 우선이 됩니다.
• INDEX와 KEY는 동의어

 

인덱스가 있어서 빨라지는 경우

- select, join 명령을 빠르게 수행하지만

 

인덱스가 있어서 느려지는 경우

- insert. delete 명령은 느리게 합니다!

(페이지 분할과 사용안함 표시로 인덱스의 조각화가 심해지기 때문)

 

➡️ 즉 테이블에 인덱스를 무분별하게 많이 추가하면, 인덱스의 로딩으로 인한 오버헤드로 인해 시스템이 전체적으로 느려질 수 있습니다!

 

➕ 데이터가 100만개가 넘으면, 인덱스 사용 시에 속도 차이를 실감할 수 있다고 합니다!

 

인덱스 만드는 방법들

1. 테이블 생성 시 지정하기

CREATE TABLE comment_info (
    id INT PRIMARY KEY, 
    content VARCHAR(255), 
    parent_comment_id INT, 
    user_id INT, 
    article_id INT,
    index parent_comment_id_index(parent_comment_id) -- 이거이거!!!
);

 

2. 테이블 생성 후에 추가하기

ALTER TABLE comment_info add index parent_comment_id_index(parent_comment_id);
CREATE INDEX parrent_comment_id_index on comment_info (parent_comment_id);

 

그렇다면 인덱스는 어떤 컬럼에 적용하는 것이 좋을까❓

1. 카디널리티가 높은(중복도가 낮은) 컬럼

 

2. WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼

 

3. INSERT/UPDATE/DELETE가 자주 발생하지 않는 컬럼

 

4. 규모가 작지 않은 테이블

 

인덱스 사용 시에 주의할 점❓

1. 잘 활용되지 않는 인덱스는 과감히 제거합시다.

- 성능저하의 원인이 될 수 있습니다.

 

2. 데이터 중복도가 높은 컬럼은 인덱스 효과가 적습니다.

 

3. 자주 사용되더라도 INSERT/UPDATE/DELETE가 자주 일어나는지 고려해야 합니다.

- 일반적인 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기의 비율이 2:8 또는 1:9라고 합니다.

- 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법입니다😀

 

➕아래 명령을 사용하면, 기존 테이블에 있는 레코드를 다른 테이블로 복사할 수 있습니다!

(컬럼 속성 등이 같은 경우에 한함)

INSERT INTO table_a SELECT * FROM table_b;

 

---

정리하면

Explain SQL은 쿼리 수행 과정을 볼 수 있어, 분석을 통해 쿼리 최적화를 진행해볼 수 있습니다.

이 때, 쿼리 최적화를 위해 인덱스가 종종 사용되는데, 인덱스는 찾기 작업을 빠르게 하기 위해 사용됩니다!

인덱스는 추가 공간을 필요로 하며, 조건 절이 없다면 인덱스가 사용되지 않는다는 점을 기억하고 사용하면 좋을 것 같습니다 👀 

 

https://www.youtube.com/watch?v=edpYzFgHbqs