성능 튜닝

Explain SQL

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

• 실행하려고 하는 SQL문 앞에 EXPLAIN 명령어 붙여주면 됨

Index

• Index는 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조를 말함
• 컬럼별로 만들어짐
• Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 갖게 됨
• 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있음

• Index와 Key는 동의어

• Index는 SELECT/DELETE/JOIN 명령을 빠르게 하지만 대신 INSERT/UPDATE 명령은 느리게 하는 단점이 존재
• 테이블에 너무 많은 인덱스를 추가하면 인덱스 메모리 로딩으로 인한 오버헤드로 인해 시스템이 전체적으로 느려질 수 있음

CREATE TABLE example (
	id INT NOT NULL AUTO_INCREMENT,
	index_col VARCHAR(20),
	PRIMARY KEY (id),
	INDEX index_name (index_col) -- index_name 주로 
-- index_col 이름과 같게 함

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

ALTER TABLE table_name ADD INDEX (column1);
ALTER TABLE table_name ADD UNIQUE (column1); -- unique한 index 값(같은 값이라도)을 가지도록 해라
ALTER TABLE table_name ADD FULLTEXT(column1);
ALTER TABLE table_name DROP INDEX(column1);

CREATE UNIQUE INDEX index_name ON table_name(col1, col2..);

• FULLTEXT - text field가 있을 때 text 검색을 많이 시행하게 되면 이거 사용함. text field로 다양한 검색을 할 경우 성능 개선이 많이 됨

왜 count 쿼리가 result set 쿼리보다 빠른가?

• 네트워크를 통해서 더 큰 데이터를 보내서 result set이 더 느린것 이외에 DB engine 자체로도 count 쿼리가 빠른가?
• 조인, 필터 연산의 결과로 어떠한 행들을 찾아야 할지가 정해지고 난 뒤에, 쿼리는 추가 컬럼들을 갖고 오기 위해 data page를 읽어야 함
• 테이블 설계와 쿼리가 모든 테이블 스캔이 일어나도록 되어 있으면 두 개의 쿼리는 똑같이 성능이 좋지 않음
• 또한 클러스터 index를 지원하는 DB라면, 쿼리가 cluster key를 사용하고 나서 더 읽을게 없을지도 모름. covering index를 사용하는 경우에도 비슷한 효과를 낼 수 있음( 더 읽을 필요가 없는 것. 해당 index를 보고나서는)

• SET STATISTICS IO ON 설정을 하면 접근한 page의 갯수를 볼수 있는데 single column과 count 쿼리의 경우에는 logical read가 2 인 반면 추가 컬럼들을 가져와야 하는 경우에는 logical read가 213이 나옴
• The I/O from an instance of the SQL Server Database Engine includes logical and physical reads.
• logical read : Database Engine이 buffer cache 로부터 page를 요청할 때마다 logical read가 일어남
• physical read : page가 buffer cache에 있지 않으면 disk로 부터 cache로 page를 복사하는 작업이 일어나는데 이 작업이 physical read