5-5) MySQL 고급기능(성능 튜닝-Explain SQL, Index)

Explain SQL(어려움 → 그냥 알고만 있자)

• 쿼리(SELECT/UPDATE/INSERT/DELETE 등)가 어떻게 수행되는지 내부를 보여주는 SQL 명령
  • MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여줌. 이를 바탕으로 느리게 동작하는 쿼리의 최적화가 가능해짐
  • 보통 느린 쿼리의 경우 문제가 되는 테이블에 인덱스를 붙이는 것이 일반적

-- EXPLAIN
EXPLAIN SELECT
	LEFT(s.created, 7) AS mon,
	c.channel,
	COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN channel c ON c.id = s.channel_id
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

---

Index 소개

• 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조 → 컬럼별로 만들어짐
  • Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index O
  • 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 사용하자
• INDEX와 KEY는 동의어
• 장점 : SELECT/DELETE/JOIN 명령은 빠름
• 단점
  • INSERT/UPDATE 명령은 느림
  • 테이블에 너무 많은 인덱스를 추가하면 인덱스의 로딩으로 인한 오버헤드로 인해 시스템이 전체적으로 느려질 수 있음

Index 생성 방법

1. CREATE TABLE시 지정 가능 (컬럼 속성)

CREATE TABLE example (
	id INT NOT NULL AUTO_INCREMENT,
	index_col VARCHAR(20),
	PRIMARY KEY (id),
	INDEX index_name (index_col)
);

1. 테이블 생성 후 나중에 ALTER TABLE 혹은 CREATE INDEX 함수로 생성하는 것도 가능

ALTER TABLE testalter_tbl ADD INDEX (column1);
ALTER TABLE testalter_tbl ADD UNIQUE (column1);
ALTER TABLE testalter_tbl ADD FULLTEXT (column1); --> text필드에서 사용(검색에 사용)
ALTER TABLE testalter_tbl DROP INDEX (column1); --> INDEX가 필요없을 때 삭제

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

Index 실습

• 인덱스가 있는 경우와 없는 경우의 SELECT 필터링의 성능을 비교해보자 → Group By 할거임
• 대상 테이블들은 prod.session과 prod.session_with_index
  • 인덱스는 user_id에 적용 (id와 channel_id에는 이미 걸려있음)

CREATE TABLE prod.session_with_index (
	id int NOT NULL auto_increment,
	user_id int not NULL,
	created timestamp not NULL default CURRENT_TIMESTAMP,
	channel_id int not NULL,
	PRIMARY KEY(id),
	FOREIGN KEY(channel_id) references channel(id),
	INDEX user_id(user_id)
);

[예제]

• 다음 2개의 GROUP BY 작업의 성능을 비교 → user_id로 비교

SELECT user_id, COUNT(1)
FROM prod.session
GROUP BY 1;

user_id에 index X

SELECT user_id, COUNT(1)
FROM prod.session_with_index
GROUP BY 1;

user_id에 index O → 이게 더 빠름