이 순서가 바뀌어서 실행되는 형태의 쿼리는 거의 없다. ORDER BY나 GROUP BY 절이 있다 하더라도 인덱스를 이용해 처리할 때는 그 단계 자체가 불필요하므로 생략된다.
2) 쿼리 각 절의 실행 순서(예외적으로 ORDER BY가 조인보다 먼저 실행되는 경우
ORDER BY가 사용된 쿼리에서 예외적인 순서로 실행되는 경우를 보여준다. 첫 번째 테이블만 읽어서 정렬을 수행한 뒤에 나머지 테이블을 읽는데 주로 GROUP BY 절이 없는 ORDER BY 만 사용된 쿼리에서 사용될 수 있는 순서이다.
위 두 개의 실행 순서를 벗어나는 쿼리가 필요하다면 서브쿼리로 작성된 인랴인 뷰(Inline View)를 사용해야 한다. 예를 들어 LIMIT를 먼저 적용하고 ORDER BY를 실행하고자 하면 아래와 같이 인라인 뷰를 사용해야 한다.
select emp_no, first_name, max_salary
from (select s.emp_no, first_name, max(s.salary) max_salary
from salaries s
inner join employees e on s.emp_no = e.emp_no
where s.emp_no in (100001, 100002, 100003)
group by s.emp_no
having max(s.salary) > 1000
limit 10
) temp_view
order by max_salary;
만약 LIMIT을 GROUP BY전에 실행하고자 할 때도 서브쿼리로 인라인 뷰를 만들어서 그 뷰 안에서 LIMIT을 적용한 후에 GROUP BY 와 ORDER BY를 인라인뷰 외부에서 적용해야 한다. 하지만 인라인뷰가 사용되면 임시테이블이 사용되기 때문에 주의해야한다. MySQL의 LIMIT는 오라클의 ROWNUM과 조금 성격이 달라서 WHERE 조건으로 사용하지 않고 항상 모든 처리의 결과에 대해 레코드 건수를 제한하는 형태로 사용한다. MySQL의 LIMIT은 항상 쿼리의 마지막에 실행된다. 하지만 LIMIT에서 필요한 레코드 건수만 준비되면 바로 쿼리를 종료시킨다. 아래의 쿼리에서 상위 5건까지만 정렬이 되면 작업을 멈춘다
select *
from employees
where emp_no between 100001 and 100100 -- Index range scan(PK)
order by first_name -- filesort
LIMIT 0, 5;
2. WHERE 조건과 GROUP BY, ORDER BY 절의 인덱스 사용
1) 인덱스를 사용하기 위한 기본 규칙
컬럼의 값을 가공하지 않아야 인덱스를 사용할 수 있다.
select * from salaries where salary*10 > 150000; -- salary 컬럼의 가공
select * from salaries where salary > 150000/10 ; -- 컬럼의 가공이 없어서 인덱스 이용 가능
WHERE절에서 사용되는 비교 조건에서 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야 한다.
drop table tb_test;
create table tb_test (col1 varchar(10), col2 integer, primary key(col1));
insert into tb_test values ('1', 1), ('2', 2), ('3',3), ('4',4), ('5',5);
explain select * from tb_test where col1=3; -- Table Full Scan
explain select * from tb_test where col1='3'; -- Primary Key
위 쿼리에서 문자열 컬럼에 숫자 타입을 비교하여 문자열 컬럼이 CAST(col1 as unsigned) 로 숫자 타입으로 변환되어 인덱스 컬럼이 가공되어 Primary Key를 사용하지 못하게 된다. 이는 문자열 보다 숫자 값의 비교가 빨라서 MySQL 옵티마이저가 숫자 타입에 우선권을 부여하기 때문이다.
2) WHERE 절의 인덱스 사용
WHRE조건이 인덱스를 사용하는 방법은 크게 범위제한조건과 체크조건으로 두 가지 방식으로 구분해 볼 수 있다. 범위 제한 조건은 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 컬럼과 얼마나 좌측부터 일치하는가에 따라 달라진다. 범위(>, <, >=, <=, Between, Like '%:비교값:')조건 뒤의 조건은 인덱스의 엑세스조건으로 사용될 수 없다.
ORDER BY a, b, c, d, e -- e는 인덱스에 없어서 인덱스를 사용하지 못한다.
create index ix_test on employees(emp_no desc, first_name);
show index from employees;
explain
select emp_no, first_name
from employees
order by emp_no, first_name; -- ix_test 인덱스 사용못함
explain
select emp_no, first_name
from employees
order by emp_no desc, first_name; -- ix_test 인덱스 사용
explain
select emp_no, first_name
from employees
order by emp_no , first_name desc; -- ix_test 인덱스 사용
5) WHERE 조건과 ORDER BY(또는 GROUP BY) 절의 인덱스 사용
WHERE 절과 ORDER BY 절이 같이 사용된 하나의 쿼리 문장은 다음 3가지 중 한 가지 방법으로만 인덱스를 사용한다.
[1] WHERE 절과 ORDER BY 절이 동시에 같은 같은 인덱스를 사용
WHERE 절의 동등 비교 조건으로 비교된 컬럼과 ORDER BY 절에 명시된 컬럼이 순서대로 인덱스 컬럼의 왼쪽부터 일치해야 한다. ORDER BY의 경우엔 정렬 방식(ASC, DESC)도 한 방향으로 일치해야 한다.
[2] WHERE 절만 인덱스를 이용
인덱스를 통해 검색된 결과 레코드를 별도의 정렬 처리 과정(Filesort)을 거쳐서 정렬을 수행한다. WHERE 절의 조건에 일치하는 레코드의 건수가 많지 않을 때 효율적이다.
[3] ORDER BY 절만 인덱스를 이용
ORDER BY 절의 순서대로 인덱스를 읽으면서 레코드 한 건씩을 WHERE 절의 조건에 일치하는지 비교해 일치하지 않을때는 버리는 형태로 처리한다. 주로 아주 많은 레코드를 조회해서 정렬해야 할 때 사용하기도 한다.
[인덱스 사용 예제]
인덱스 : a+b+c
select * from tb_test where a>1 order by a, b, c; -- 인덱스 이용 가능
select * from tb_test where a>1 order by b, c; -- a컬럼이 범위조건으로 사용돼서 인덱스 사용 불가
6) GROUP BY 절과 ORDER BY 절의 인덱스 사용
GROUP BY절에 명시된 컬럼과 ORDER BY에 명시된 컬럼이 순서와 내용이 모두 같아야 인덱스를 사용한다.
GROUP BY와 ORDER BY가 같이 사용된 쿼리에서는 둘 중 하나라도 인덱스를 이용할 수 없을 때는 둘다 인덱스를 사용하지 못한다.
WHERE절과 GROUP BY절, 그리고 ORDER BY 절이 모두 포함된 쿼리가 인덱스를 사용하는지 판단하는 방법을 알아 보자
[1] WHERE 절이 인덱스를 사용하는가?
[2] GROUP BY절이 인덱스를 사용할 수 있나?
[3] GROUP BY절과 ORDER BY절이 동시에 인덱스를 사용할 수 있는가?
3. WHERE 절의 비교 조건 사용 시 주의 사항
WHERE절에 사용되는 비교 조건의 표현식을 알아보자. 쿼리가 최적으로 실행되려면 적합한 인덱스와 WHERE 절에 사용되는 비교 조건의 표현식을 적절하게 사용해야 한다.
1) NULL 비교
MySQL에서는 NULL값이 포함된 레코드도 인덱스로 관리된다. 쿼리에서 NULL인지 비교하려면 "IS NULL"연산자를 사용해야 한다.
select ISNULL(null) ; -- 1
explain select * from titles where to_date is null; -- index range scan
explain select * from titles where ISNULL(to_date); -- index range scan
explain select * from titles where ISNULL(to_date) = 1; -- index full scan
explain select * from titles where ISNULL(to_date) = true; -- index full scan
컬럼의 값이 NULL인지 확인할 때는 ISNULL()이라는 함수를 사용해도 된다. 하지만 위의 예제에서 처럼 3,4번째 쿼리는 인덱스 레인지 스캔으로 처리되지않고 인덱스 나 테이블 풀 스캔하는 형태로 처리된다. NULL 비교를 할때는 IS NULL 연산자를 사용하길 권장한다.
2) 문자열이나 숫자 비교
문자열 컬럼이나 숫자 컬럼을 비교할 때는 반드시 그 타입에 맞춰서 상수를 사용할 것을 권장한다. 즉 비교 대상 컬럼이 문자열 컬럼이라면 문자열 리터럴을 사용하고, 숫자 타입이라면 숫자 리터럴을 이용해야 한다.
-- 컬럼과 상수값이 동일한 타입 : 인덱스 사용
select * from employees where emp_no = 100001;
-- 컬럼과 상수값이 동일한 타입 : 인덱스 사용
select * from employees where first_name = 'Smith';
-- 컬럼이 숫자 타입 -> 문자열 상수값을 숫자타입 형변환 : 인덱스 사용
select * from employees where emp_no = '100001';
-- 상수값이 숫자 -> 문자열 컬럼을 숫자타입 형변환 : 인덱스 미사용
select * from employees where first_name = 100001;
옵티마이저가 어떤 경우에 어떻게 타입 변환을 유도하는지 정확히 알고 있는 것도 필요하지만, 컬럼의 타입에 맞게 상수 리터럴을 비교 조건에 사용하는 것이 중요하다.
3) 날짜 비교
DATE와 DATETIME 비교 방식과 TIMESTAMP와 DATETIME 비교에 대해 살펴 보자
- DATETIME 타입 'YYYY-MM-DD HH:MM:SS' 형식으로 문자형으로 변환되어 저장 8bytes
- TIME 타입은 HH:MM:SS 으로 시간에 대한 정보
- TIMESTAMP 타입은 1980-01-01 00:00:01 ~ 2038-01-19 03:14:07 UTC 로 숫자형으로 변환되어 저장 4bytes
- DATETIME은 데이터값을 입력해 주어야 날짜가 입력되지만 TIMESTAMP는 자동으로 현재 날짜가 입력이 가능하다(CURRENT_TIMESTAMP)
[1] DATE나 DATETIME과 문자열 비교
DATE나 DATETIME 타입의 값과 문자열을 비교할 때는 문자열 값을 자동으로 DATETIME 타입의 값으로 변환해서 비교를 수행한다.
select count(*)
from employees
where hire_date > STR_TO_DATE('2011-07-23','%Y-%m-%d');
select count(*)
from employees
where hire_date > '2011-07-23' -- date 타입으로 MySQL이 내부적으로 변환
컬럼의 타입이 DATE나 DATETIME 타입이면 문자열을 DATE나 DATETIME으로 명시적으로 변환하지 않아도 MySQL 내부적으로 변환을 수행한다.
DATE나 DATETIME 타입의 컬럼을 변경하지 말고 상수를 변경하는 형태로 조건을 사용해야 한다.
[인덱스 컬럼이 가공된 형태]
select count(*)
from employees
where date_format(hire_date, '%Y-%m-%d') > '2011-07-23'
select count(*)
from employees
where date_add(hire_date, INTERVAL 1 YEAR) > '2011-07-23'
[인덱스 컬럼이 아니라 상수를 변형하는 형태]
select count(*)
from employees
where hire_date > data_sub ('2011-07-23' , INTERVAL 1 YEAR);
[2] DATE와 DATETIME의 비교
DATETIME 값에서 시간 부분을 버리고 비교하면 다음 예제와 같이 쿼리를 작성하면 된다.
select count(*)
from employees
where hire_date > DATE(NOW());
DATETIME 타입의 값을 DATE 타입으로 만들지 않고 비교하면 MySQL 서버가 DATE 타입의 값을 DATETIME으로 변환해서 비교를 수행한다. '2021-02-01'의 DATE 타입의 값이 '2021-02-01 00:00:00' 의 DATETIME 타입으로 변환되어서 비교를 수행한다.
select str_to_date('2021-02-01','%Y-%m-%d') < str_to_date('2021-02-01 00:00:01','%Y-%m-%d %H:%i:%s');
결과 : 1(TRUE)
select str_to_date('2021-02-01','%Y-%m-%d') >= str_to_date('2021-02-01 00:00:01','%Y-%m-%d %H:%i:%s');
결과 : 0(FALSE)
DATETIME과 DATE 타입의 비교에서 타입 변환은 인덱스의 사용 여부에 영향을 미치지 않기 때문에 성능보다는 쿼리의 결과에 주의해서 사용한다.
[3] DATETIME과 TIMESTAMP의 비교
UNIX_TIMESTAMP 함수의 결과 값은 단순 숫자 값에 불과하므로 반드시 비교 값으로 사용되는 상수 리터럴을 비교 대상 컬럼의 타입에 맞게 변환해서 사용한다. 컬럼이 DATETIME 타입이라면 FROM_UNIXTIME 함수를 이용해서 TIMESTAMP값을 DATETIME 타입으로 만들어서 비교해야 한다. 컬럼의 타입이 TIMESTAMP라면 UNIX_TIMESTAMP 함수를 이용해 DATETIME을 TIMESTAMP로 변환해서 비교해야 한다.
select count(*) from employees where hire_date < '2021-02-01 01:01:01';
결과 : 300024
select count(*) from employees where hire_date < UNIX_TIMESTAMP();
결과 : 300024
MySQL 8.0.21 (select version();) 에서는 unix_timestamp() 로 date 값과 비교해도 정상 작동한다. 첫 번째 쿼리와 같이 Index range Scan을 못하고 Index Full Scan을 하는 것을 보아서 hire_date를 가공해서 비교하는 것으로 추정된다. 아래와 같이 수행하면 다시 index range scan으로 작동한다. 결론은 결과 값은 같지만 컬럼의 타입은 일치 시켜줘야 한다.
explain select count(*) from employees where hire_date < FROM_UNIXTIME(UNIX_TIMESTAMP());
MySQL에서만 사용되는 연산자나 표기법도 함께 살펴보겠지만 SQL 가독성을 높기기 위해 ANSI 표준 형태의 연산자를 사용하길 권장한다.
1.리터럴 표기법
1) 문자열
[ANSI_QUOTES 설정 OFF]
show variables like '%sql_mode%';
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,IGNORE_SPACE,PIPES_AS_CONCAT';
SQL 표준에서 문자열은 항상 홑따옴표(')를 사용해서 표시한다. 하지만 MySQL에서는 다음과 같이 쌍 따옴표를 사용해서 문자열을 표기할 수도 있다.
select * from departments where dept_no = 'd001';
select * from departments where dept_no = "d001"; -- MySQL에서만 가능
SQL 표준에서 문자열 값에 홑따옴표(')가 포함돼 있을 떄 홑따옴표를 두 번 연속해서 입력하면 된다. 하지만 MySQL에서는 쌍따옴표와 홑따옴표를 혼합해서 사용하기도 한다.
select * from departments where dept_no = 'd''001';
select * from departments where dept_no = 'd"001';
select * from departments where dept_no = "d'001"; -- MySQL에서만 가능
select * from departments where dept_no = "d""001"; -- MySQL에서만 가능
SQL에서 사용되는 식별자(테이블이나 컬럼명 등)가 키워드와 충돌할때 오라클이나 MS-SQL에서는 쌍따옴표나 대괄호로 감싸서 충돌을 피한다. MySQL에서는 역따옴표로 감싸서 사용하면 예약어와의 충돌을 피할 수 있다.
drop table tb_test;
-- DDL에서 식별자에 쌍따옴표 사용하면 에러난다.
create table tb_test ("table" varchar(20) not null, "column" int);
-- 역따옴표 항상 가능
create table tb_test (`table` varchar(20) not null, `column` int);
select `column` from tb_test; -- 역따옴표 항상 가능
select "column" from tb_test; -- 쌍따옴표 항상 가능
ANSI_QUOTES 설정하면 쌍따옴표는 문자열 리터럴 표기에 사용할 수 없다.
테이블명이나 컬럼명의 충돌을 피할려면 역 따옴표(')가 아니라 쌍따옴표를 사용해야 한다.
[ANSI_QUOTES 설정 ON]
set sql_mode = 'ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,IGNORE_SPACE,PIPES_AS_CONCAT';
select * from departments where dept_no = 'd001';
-- 문자열에 쌍따옴표 사용하면 에러난다.
select * from departments where dept_no = "d001";
drop table tb_test;
create table tb_test (`table` varchar(20) not null, `column` int); -- 역따옴표 항상 가능
create table tb_test ("table" varchar(20) not null, "column" int); -- 쌍따옴표 가능
select `column` from tb_test; -- 역따옴표 항상 가능
select "column" from tb_test; -- 쌍따옴표 항상 가능
전체적으로 MySQL의 고유한 방법은 배제하고 SQL표준 표기법만 사용할 수 있게 강제하려면 sql_mode 시스템 변수 값에 "ANSI"를 설정하면된다. 하지만 이 설정은 대부분 쿼리의 작동 방식에 영향을 미치므로 프로젝트 초기에 적용해야한다. 운용중인 애플리케이션에서 sql_mode 설정을 변경하는 것은 상당히 위험하므로 주의해야 한다.
2) 숫자
두 비교 대상이 문자열과 숫자 타입으로 다를 때는 자동으로 타입의 변환이 발생한다. MySQL은 숫자 타입과 문자열 타입간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비료를 수행한다.
select * from tab_test where number_column = '10001';
위 쿼리는 상수값을 숫자로 변환하므로 성능과 관련된 문제가 발생하지 않는다.
select * from tab_test where string_column = 10001;
위 쿼리는 주어진 상수값이 숫자 값인데 비교되는 컬럼은 문자열 컬럼이다. MySQL은 문자열 컬럼을 숫자로 변환해서 비교한다. 즉, string_column 컬럼의 모든 문자열 값을 수잦로 변환해서 비교해야 하므로 string_column에 인덱스가 있더라도 이용하지 못한다. 만약 string_column에 알파벳 같은 문자가 포함된 경우에는 숫자 값으로 변환할 수 없으므로 쿼리 자체가 실패할 수도 있다.
3) 날짜
다른 DBMS에서 날짜 타입을 비교하거나 INSERT 하려면 반드시 문자열을 DATE 타입으로 변환하는 코드가 필요하다. 하지만 MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIMEE 값으로 변환하기 때문에 복잡하게 STR_TO_DATE()와 같은 함수를 사용하지 않아도 된다.
select * from dept_emp where from_date = '2011-04-29';
select * from dept_emp where from_date = STR_TO_DATE('2011-04-29','%Y-%m-%d');
첫 번째 쿼리와 같이 날짜 타입의 컬럼과 문자열 값을 비교하는 경우, MySQL서버는 문자열 값을 DATE 타입으로 변환해서 비교한다. 두 번째 쿼리는 SQL에서 문자열을 DATE 타입으로 강제 변환해서 비교하는 예제인데, 이 두 쿼리의 차이점은 거의 없다. 첫 번째 쿼리에서 from_date 타입을 문자열로 변환해서 비교하지 않기 때문에 from_date 컬럼으로 생성된 인덱스를 이용하는데 문제가 되지 않는다.
4) BOOLEAN
BOOL이나 BOOLEAN이라는 타입이 있지만 이것은 TINYINT 타입에 대한 동의어일 뿐이다. 테이블의 컬럼을 BOOL로 생성한 뒤에 조회해보면 컬럼의 타입이 BOOL이 아니라 TINYINT라는 점을 알 수 있다. MySQL에서는 다음 예제 쿼리와 같이 TRUE 또는 FALSE 형태로 비교하거나 값을 저장할 수 있다. 하지만 이는 BOOL 타입뿐 아니라 숫자 타입의 컬럼에도 모두 적용되는 비교 방법이다.
drop table tb_boolean;
CREATE TABLE tb_boolean(
col_int int NOT NULL,
col_boolean BOOLEAN,
PRIMARY KEY (col_int)
) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin;
insert into tb_boolean values(0,FALSE);
insert into tb_boolean values(1,true);
insert into tb_boolean values(2,2);
insert into tb_boolean values(3,3);
select * from tb_boolean where col_boolean = false;
select * from tb_boolean where col_boolean = true;
select * from tb_boolean where col_boolean in (true,false);
select * from tb_boolean where col_boolean in (0,1);
select * from tb_boolean where col_int in (true,false);
TRUE나 FALSE로 비교했지만 실제로는 값을 조회해 보면 0 또는 1 값이 조회된다. 즉 MySQL은 C/C++ 언어에서처럼 TRUE 또는 FALSE 같은 불리언 값을 정수로 맵핑해서 사용하는 것이다. MySQL에서는 FASLE가 정수값 0 이고 TRUE는 1만을 의미한다. 그래서 숫자 값이 저장된 컬럼을 TRUE나 FALSE로 조회하면 0 이나 1 이외의 숫자 값은 조회되지 않는다.
모든 숫자 값이 TRUE나 FALSE로 매핑되지 않기 때문에 BOOLEAN 타입을 사용하고 싶다면 ENUM 타입으로 관리하는 것이 명확하다. 실제로 ENUM 컬럼은 데이터 값만 저장해야하는 테이블 컬럼에 데이터 값을 명시하여 정규화에 위반되고 데이터 수정이 어려워 사용을 권장하지 않는다.
2. MySQL 연산자
1) 동등(Equal) 비교 ( =, <=> )
MySQL에서는 동등 비교를 위해 "<=>" 연산자도 제공한다. "<=>" 연산자는 "=" 연산자의 기능에 부가적으로 NULL 값에 대한 비교까지 수행한다. MySQL에서는 이 연산자를 NULL-safe 비교 연산자라고 한다.
위 결과에서도 알 수 있듯이 NULL은 "IS NULL"연산자 이외에는 비교할 방법이 없다. 그래서 첫번째 쿼리에서 한쪽이 NULL이면 비교 결과도 NULL로 반환한 것이다. 하지만 NULL-safe 비교 연산자를 이용해 비교한 결과를 보면 양쪽 비교 대상 모두 NULL이라면 TRUE를 반환하고, 한쪽만 NULL이라면 FALSE를 반환하다. 즉 "<=>" 연산자는 NULL을 하나의 값으로 인식하고 비교하는 방법이라고 볼수 있다.
2) 부정(Not-Equal) 비교 ( <>, != )
"같지 않다" 비교를 위한 연산자로 "<>", "!=" 를 사용한다. 가독성을 위해서 하나만 사용하는 것을 권장한다.
3) NOT 연산자 (!)
TRUE 또는 FALSE 연산의 결과를 반대로 만드는 연산자로 "NOT"과 "!"를 사용한다.
select ! 1, ! false, ! true, not 1, not 0, not (1=1);
4) AND(&&) 와 OR(||) 연산자
BOOLEAN 표현식의 결과를 결합하기 위해 AND나 OR를 사용한다. MySQL에서는 AND와 OR뿐만이 아니라 "&&"와 "||"의 사용도 허용하고 있다. "&&"는 AND 연산자와 같으며, "||"는 OR 연산자와 같다. set sql_mode = 'PIPES_AS_CONCAT'; 추가시 "||" 는 concat 연산자로 작동한다. "&&" 연산자는 그대로 작동한다.
set sql_mode = 'PIPES_AS_CONCAT';
select 'abc'||'def' as concated_string, 1 && 0 , 1 and 0, 1 or 0, 1||0;
5) 나누기(/, DIV)와 나머지(%, MOD) 연산자
나누기 연산자로 / 는 몫의 정수값과 소수값을 전부 반환한다. 나눈 몫의 정수 부분만 가져오려면 DIV연산자를 사용한다. 나머지를 가져오는 연산자로 %, MOD를 사용한다.
select 25/4, 25 div 4, mod(25,4), 25 mod 4, 25 % 4 ;
6) REGEXP 연산자
문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자이며, RLIKE는 REGEXP와 똑같은 비교를 수행하는 연산자다. RLIKE는 가끔 문자열 값의 오른쪽 일치용 LIKE 연산자(Right LIKE)로 혼동할 때가 있는데, MySQL의 RLIKE는 정규 표현식(Regular expression)을 비교하는 연산자다.
REGEXP 연산자의 정규 표현식은 POSIX 표준으로 구현돼 있어서 POSIX 정규 표현식에서 사용하는 패턴 키워드를 그대로 사용할 수 있다.
REGEXP 연산자를 문자열 컬럼 비교에 사용할 때 REGEXP 조건의 비교는 인덱스 레인지 스캔을 사용할 수 없다. 따라서 WHERE 조건절에 REGEXP 연산자를 사용한 조건을 단독으로 사용하는 것은 성능상 좋지 않다. 가능한 범위를 줄일 수 있는 조건과 함께 REGEXP 연산자를 사용하길 권장한다.
REGEXP 연산자는 인덱스를 전혀 사용하지 못한다는 단점이 있지만, LIKE 연산자는 인덱스를 이용해 처리할 수도 있다. LIKE연산자는 어떤 상수 문자열이 있는지 없는지를 판단한다.
select 'abcdef' like 'abc%' -- 1
, 'abcdef' like '%abc' -- 0
, 'abcdef' like '%ef' -- 1
, 'abcdef' like 'abc_ef'; -- 1
% : 0 또는 1개 이상의 모든 문자에 일치
_ : 정확히 1개의 문자에 일치
와일드카드 문자인 '%' 나 '_' 문자 자체를 비교한다면 ESCAPE절을 LIKE 조건 뒤에 추가해 설정할 수 있다.
select 'abc' like 'a/%' escape '/'; -- 0
select 'a%' like 'a/%' escape '/'; -- 1
LIKE 연산자는 와일드카드 문자인 (%,_)가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용할 수 있지만 와일드 카드가 검색어의 앞쪽에 있다면 인덱스 레인지 스캔을 사용할 수 없다.
"rstian"으로 끝나는 이름을 검색할 때는 와일드카드가 검색어의 앞쪽에 있으면 인덱스의 left-most 특성으로 인덱스 레인지 스캔을 사용하지 못하고 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식으로 쿼리가 처리됨을 알 수 있다.
explain
select count(*)
from employees where first_name like '%rstian';
type 의 index 는 index full scan을 의미한다.
8) BETWEEN 연산자
BETWEEN 연산자는 "크거나 같다"와 "작거나 같다"라는 두 개의 연산자를 하나로 합친 연산자다.
BETWEEN는 범위를 읽어야 하는 연산자라서 뒤에 동등조건이 오더라도 엑세스 조건으로 사용되지 못한다.
-- 두 조건 모두 인덱스를 이용해 범위를 줄여줄 수 있다.(인덱스 엑세스 조건 : dept_no, emp_no)
select * from dept_emp where dept_no = 'd003' and emp_no = 10001;
-- dept_no만 인덱스를 이용해 범위를 줄여줄 수 있다.(인덱스 엑세스 조건 : dept_no)
select * from dept_emp where dept_no between 'd003' and 'd005' and emp_no = 10001;
-- 두 조건 모두 인덱스를 이용해 범위를 줄여줄 수 있다.(인덱스 엑세스 조건 : dept_no, emp_no)
select * from dept_emp where dept_no in ('d003', 'd004', 'd005') and emp_no = 10001;
9) IN 연산자
IN은 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다. 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리된다.
IN 연산자를 이용해 NULL 값을 검색할 수는 없다. 값이 NULL인 레코드를 검색하려면 NULL-Safe 연산자인 "<=>" 또는 IS NULL 연산자 등을 사용해야 한다. (MySQL8.0.20 에서 index range scan을 한다.)
select * from employees where emp_no in (10001,10002,null);
select * from employees where first_name in ('Georgi','Bezalel', null);
NOT IN의 실행 계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교라서 인덱스를 이용해 처리 범위를 줄이는 조건으로는 사용할 수 없기 때문이다.
NOT IN 연산자가 Primary Key와 비교될 때 가끔 쿼리의 실행 계획의 인덱스 레인지 스캔으로 표시될 수도 있다. 하지만 이는 InnoDB 테이블에서 Primary Key가 클러스터링 키이기 때문일 뿐 실제 IN과 같이 효율적으로 실행된다는 것을 의미하지는 않는다.
explain select * from employees where first_name not in ('Georgi','Bezalel');
explain select * from employees where first_name not in ('Georgi','Bezalel');
3. MySQL 내장 함수
MySQL의 함수는 MySQL에서 기본적으로 제공하는 내장 함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의 함수( UDF)로 구분된다. MySQL에서 제공하는 C/C++ API를 이용해 사용자가 원하는 기능을 직접 함수로 만들어 추가할 수 있는데 이를 사용자 정의 함수라고 한다. 여기서 언급하는 내장 함수나 사용자 정의 함수는 스토어드 프로그램으로 작성되는 프로시저나 스토어드 함수와는 다르므로 혼동되지 않도록 주의하자.
1) NULL 값 비교 및 대체 (IFNULL, ISNULL)
IFNULL(첫 번째 인자, 두 번째 인자) 함수의 반환 값은 첫 번째 인자가 NULL이 아니면 첫 번째 인자의 값을, 첫 번째 인자의 값이 NULL이면 두 번째 인자의 값을 반환한다.
ISNULL(표현식) 함수는 인자의 표현식이 NULL이면 TRUE(1), NULL이 아니면 FALSE(0)을 반환한다.
하나의 SQL에서 NOW()함수는 같은 값을 가지고 SYSDAT() 함수는 하나의 SQL 내에서도 호출되는 시점에 따라 결과 값이 달라진다.
select now(), sleep(2), now();
select sysdate(), sleep(2), sysdate();
NOW()함수는 호출되는 시점의 시간을 반환하지만 SYSDATE() 함수는 Sleep() 함수의 대기 시간인 2초 동안의 차이가 있음을 알 수 있다.
[SYSDATE() 함수의 특성으로 인한 문제]
- SYSDATE() 함수가 사용된 SQL은 복제가 구축된 MySQL의 슬레이브에서 안정적으로 복제(Replication)되지 못한다.
- SYSDATE() 함수와 비교되는 컬럼은 인덱스를 효율적으로 사용하지 못한다.
[SYSDATE() 함수 인덱스 사용 예제]
salaries Table PK : emp_no + from_date
emp_no+from_date 인덱스 사용 => key_len : 7bytes
explain
select emp_no, salary, from_date, to_date
from salaries where emp_no=10001 and from_date>now();
emp_no+from_date 인덱스 사용 => key_len : 4bytes, emp_no만 사용
explain
select emp_no, salary, from_date, to_date
from salaries where emp_no=10001 and from_date>sysdate();
SYSDATE() 함수는 호출될 때마다 다른 값을 반환하므로 사실은 상수가 아니다. 그래서 인덱스를 스캔할 때도 매번 비교되는 레코드마다 함수를 실행해야만 한다. 하지만 NOW() 함수는 쿼리가 실행되는 시점에서 값을 할당받아서 그 값을 SQL문장의 모든 부분에서 사용하기 때문에 쿼리가 1시간 동안 실행되더라도 실행되는 위치나 시점에 관계없이 항상 같은 값을 가진다.
MySQL 서버의 설정 파일(my.cnf나 my.ini 파일)에 sysdate-is-no 를 설정하면 sysdate() 함수도 now() 함수와 같이 함수의 호출 시점에 관계없이 하나의 SQL에서는 같은 값을 갖게 설정해야 한다.
3) 날짜와 시간의 포맷 (DATE_FORMAT, STR_TO_DATE)
DATE_FORMAT() 함수는 DATETIME 타입의 컬럼이나 값을 원하는 형태의 문자열로 변환한다.
select date_format(now(), '%Y-%m-%d') as current_dt ;
select date_format(now(), '%Y-%m-%d %H:%i:%s') as current_dttm;
SQL에서 표준형태(년-월-일 시:분:초)로 입력된 문자열은 필요한 경우 자동으로 DATETIME타입으로 변환되어 처리된다. 또한 STR_TO_DATE() 함수를 이용해 문자열을 DATETIME 타입으로 변환할 수 있다.
select str_to_date('2021-02-01', '%Y-%m-%d') as current_dt;
select str_to_date('2021-02-01 10:11:27', '%Y-%m-%d %H:%i:%s') as current_dttm;
4) 날짜와 시간의 연산 (DATE_ADD, DATE_SUB)
특정 날짜에서 년도나 월일 또는 시간등을 더하거나 뺄 때는 DATE_ADD() 함수나 DATE_SUB() 함수를 이용한다.
DATE_ADD() 함수로 더하거나 빼는 처리를 모두 할 수 있다. 두 함수는 두개의 인자를 받는다. 첫 번째 인자는 연산을 수행할 날자이며, 두 번째 인자는 INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK] 형태로 입력해야 한다.
select date_add(now(), interval 1 day) as tomorrow;
select date_add(now(), interval -1 day) as yesterday;
5) 타임 스탬프 연산 (UNIX_TIMESTAMP, FROM_UNIXTIME)
UNIX_TIMESTAMP() 함수는 '1970-01-01 00:00:00' 을 기준으로 경과된 초의 수를 반환하는 함수다. UNIX_TIMESTAMP() 함수는 인자가 없으면 현재 날짜와 시간의 타임스탬프 값을, 인자로 특정 날짜를 전달하면 그 날짜와 시간의 타임스탬프를 반환한다. FROM_UNIXTIME()함수는 UNIX_TIMESTAMP() 함수와 반대로, 인자로 전달한 타임스탬프 값을 DATETIME 타입으로 변환하는 함수다.
문자열을 연결해서 하나의 문자열로 반환하는 함수로, 인자의 개수는 제한이 없다. 숫자 값을 인자로 전달하면 문자열 타입으로 자동 변환한 후 연결한다.
select CONCAT('abc','efg',123,CAST(4 AS CHAR)) ;
select CONCAT_WS('-','abc','efg','hij');
8) GROUP BY 문자열 결합 (GROUP_CONCAT)
COUNT()나 MAX(), MIN(), AVG()등과 같은 그룹함수(Aggregate, 여러 레코드의 값을 병합해서 하나의 값을 만들어내는 함수) 중 하나다. 주로 GROUP BY와 함께 사용하며, GROUP BY가 없는 SQL에서 사용하면 단 하나의 결과 값만 만들어낸다. GROUP_CONCAT()함수는 값들을 정렬, 구분자 설정, 중복 제거를 하고 연결하는 것도 가능하다.
select group_concat(dept_no) from departments;
select group_concat(dept_no separator '-') from departments;
select group_concat(dept_no order by dept_no desc) from departments;
select group_concat(distinct dept_no order by dept_no desc) from departments;
GROUP_CONCAT() 함수는 지정한 컬럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용한다. GROUP_CONCAT() 함수가 JDBC로 실행 될 때는 경고가 아니라 에러로 취급되어 SQLException이 발생하므로 GROUP_CONCAT()의 결과가 지정된 버퍼 크기를 초과하지 않도록 주의해야 한다.
GROUP_CONCAT() 함수가 사용하는 메모리 버퍼의 크기는 group_concat_max_len 시스템 변수로 조정할 수 있다. 기본으로 설정된 버퍼의 크기가 1KB밖에 안되기 때문에 GROUP_CONCAT() 함수를 자주 사용한다면 버퍼의 크기를 적절히 늘려서 설정하는 것을 고려해야 한다.
show variables like 'group_concat_max_len';
9) 값의 비교와 대체 (CASE WHEN .. THEN .. END)
CASE WHEN 구문을 이용하여 서브쿼리를 실행할 수도 있다.
select e.emp_no, e.first_name, e.gender
,case when e.gender='F' then
(select s.salary from salaries s where s.emp_no = e.emp_no order by from_date desc limit 1)
else 0
end as last_salary
from employees e
order by hire_date desc
limit 10;
10) 타입의 변환 (CAST, CONVERT)
Prepared Statements를 제외하고 SQL은 텍스트(문자열) 기반으로 작동하기 때문에 SQL에 포함된 모든 입력 값은 문자열처럼 취급한다. 이럴 때 명시적으로 타입의 변환이 필요하다면 CAST() 함수를 이용한다.
CAST함수를 통해 변환할 수 있는 데이터 타입은 DATE, TIME, DATETIME, BINARY, CHAR, DECIMAL, SIGNED INTEGER, UNSIGNED INTEGER이다.
select CAST('1234' as SIGNED INTEGER) as converted_integer;
select CONVERT('1234' , SIGNED INTEGER) as converted_integer;
select CAST('2021-02-01' as DATE) AS converted_date;
select CONVERT('2021-02-01', DATE) as converted_date;
일반적으로 문자열과 숫자 그리고 날짜의 변환은 명시적으로 해주지 않아도 MySQL이 자동으로 필요한 형태로 변환하는 경우가 많다. 하지만 SIGNED나 UNSIGNED와 같은 부호 있는 정수 또는 부호 없는 정수 값의 변환은 그렇지 않을 때가 많아 명시적인 타입 변환을 할 필요가 있다.
select 1-2, cast(1-2 as unsigned);
select 1-2, CONVERT(1-2 , unsigned);
CONVERT 함수는 CAST 함수와 같이 타입을 변환하는 용도와 문자열의 문자집합을 변환하는 용도라는 두 가지로 사용할 수 있다.
select convert('ABC' USING 'utf8') ;
11) 이진값과 16진수 (Hex String) 문자열 변환 (HEX, UNHEX)
HEX 함수는 이진값을 사람이 읽을 수 있는 16진수의 문자열(Hex string)로 변환하는 함수이고, UNHEX 함수는 16진수 문자열을 읽어서 이진값(BINARY)으로 변환하는 함수이다.
12) 암호화 및 해시 함수 (MD5, SHA)
MD5와 SHA 모두 비대칭형 암호화 알고리즘인데, 인자로 전달한 문자열을 각각 지정된 비트 수의 해시 값을 만들어내는 함수다. SHA 함수는 SHA-1 암호화 알고리즘을 사용하며, 결과로 160비트(20bytes) 해시 값을 반환한다. MD5는 메시지 다이제스트(Message Digest) 알고리즘을 사용해 128비트(16bytes) 해시 값을 반환한다.
두 함수 모두 사용자의 비밀번호와 같은 암호화가 필요한 정보를 인코딩하는 데 사용되며, 특히 MD5 함수는 말 그대로 입력된 문자열(Message)의 길이를 줄이는(Digest) 용도로도 사용된다. 두 함수의 출력 값은 16진수로 표시되기 때문에 저장하려면 저장 공간이 각각 20Bytes와 16Bytes의 두 배씩 필요하다. 암호화된 값을 저장해 두기 위해 MD5 함수는 CHAR(32), SHA 함수는 CHAR(40)의 타입을 필요로 한다.
select md5('abc');
select sha('abc');
저장 공간을 16Bytes, 20Bytes로 줄이고 싶다면 BINARY형태의 타입에 저장하면 된다. 컬럼의 타입을 BINARY(16), BINARY(20)으로 정의하고 MD5 함수나 SHA 함수의 결과를 UNHEX 함수를 이용해 이진값으로 변환해서 저장하면 된다. BINARY 타입에 저장된 이진값을 사람이 읽을 수 있는 16진수 문자열로 되돌릴 때는 HEX 함수를 사용한다.
create table tab_binary(col_md5 binary(16), col_sha binary(20));
insert into tab_binary values(unhex(md5('abc')), unhex(sha('abc')));
select hex(col_md5), hex(col_sha) from tab_binary;
MD5, SHA함수는 비대칭형 암호화 알고리즘이다. 이 두 함수의 결과 값은 중복 가능성이 매우 낮기 때문에 길이가 긴 데이터를 크기를 줄여서 인덱싱(해시)하는 용도로도 사용된다. 예를 들어 URL과 같은 값은 1KB를 넘을 때도 있으며 전체적으로 값의 길이가 긴 편이다. 이러한 데이터를 검색하려면 인덱스가 필요하지만, 긴 컬럼에 대해 전체값으로 인덱스를 생성하는 것은 불가능(Prefix 인덱스 제외)할 뿐더러 공간 낭비도 커진다. URL의 값을 MD5() 함수로 단축하면 16Bytes로 저장할 수 있고 16Bytes로 인덱스를 생성하면 되기 때문에 상대적으로 효율적이다.
13) 처리 대기 (SLEEP)
SLEEP 함수는 프로그래밍 언어나 쉘 스크립트 언어에서 제공하는 "sleep" 기능을 수행한다.
select sleep(2)
from employees
where emp_no between 100001 and 100003;
SLEEP 함수는 레코드 건수만큼 SLEEP 함수를 호출하기 때문에 위 쿼리는 3*2초=6초 동안 쿼리를 실행한다.
14) 벤치마크 (BENCHMARK)
BENCHMARK 함수는 SLEEP 함수와 같이 디버깅이나 간단한 함수의 성능 테스트용으로 아주 유용한 함수다. BENCHMARK 함수는 2개의 인자를 필요로 한다. 첫 번째 인자는 반복해서 수행할 횟수이며, 두 번째 인자로는 반복해서 실행할 표현식을 입력하면 된다. 두 번째 인자의 표현식은 반드시 스칼라 값을 반환하는 표현식이어야 한다. 즉 SELECT 쿼리를 BENCHMARK 함수에 사용하는 것도 가능하지만 반드시 스칼라 값(하나의 컬럼을 가진 하나의 레코드)만 반환하는 SELECT 쿼리만 사용할 수 있다.
BENCHMARK 함수의 반환값은 중요하지 않으며, 단지 지정한 횟수만큼 반복 실행하는 데 얼마나 시간이 소요됐는지가 중요할 뿐이다.
[MD5 함수를 10만번 실행 0.04초 소요시간 확인]
select benchmark(100000, md5('abcdefghijk'));
[employees 테이블에서 건수만 세는 SQL의 성능 확인]
select benchmark(100,(select count(*) from employees));
"SELECT BENCHMARK(10, expr)"와 "SELECT expr"을 10번 직접 실행하는 것과는 차이가 있다. 직접 실행한느 경우에는 매번 쿼리의 파싱이나 최적화, 테이블 락이나 네트워크 비용 등이 소요된다. 하지만 "SELECT BENCHMARK(10,expr)" 로 실행하는 경우에는 벤치마크 횟수에 관계없이 단 1번의 네트워크, 쿼리파싱 및 최적화 비용이 소요된다는 점을 고려해야 한다. 또한 "SELECT BENCHMARK(10,expr)"를 사용하면 한 번의 요청으로 expr 표현식이 10번 실행되는 것이므로 이미 할당받은 메모리 자원까지 공유되고, 메모리 할당도 직접 "SELECT expr" 쿼리로 10번 실행하는 것보다 1/10밖에 일어나지 않는다. BENCHMARK 함수로 얻은 쿼리나 함수의 성능은 그 자체로는 별로 의미가 없으며, 두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용할 것을 권장한다.
15) IP 주소 변환 (INET_ATON, INET_NTOA)
IP주소는 4Bytes의 부호 없는 정수(Unsigned integer)이다. IP정보를 VARCHAR(15) 타입에 '.'으로 구분해서 저장하면 저장공간을 훨씬 많이 필요로 한다. 일반적으로 IP 주소를 저장할 때 "127.0.0.1" 형태로 저장하므로 IP 주소 자체를 A, B, C 클래스로 구분하는 것도 불가능하다.
MySQL에서는 INET_ATON 함수와 INET_NTOA 함수를 이용해 IP주소를 문자열이 아닌 부호 없는 정수 타입(UNSIGED INTEGER)에 저장할 수 있게 제공한다. INET_ATON 함수는 문자열로 구성된 IP 주소를 정수형으로 변환하는 함수이며, INET_NTOA 함수는 정수형의 IP 주소를 '.'로 구분된 문자열로 변환하는 함수이다.
아래 예제는 IP주소를 UNSIGNED INTEGER 타입에 저장하고, '127.0.0.128' ~ '127.0.0.255' 사이의 IP로부터 접근했던 이력만 조회해 보는 과정을 통해 어떻게 IP 주소를 저장하고 검색할 수 있는지 보여준다.
create table tab_accesslog(access_dttm DATETIME, ip_addr INTEGER UNSIGNED);
insert into tab_accesslog values(now(), inet_aton('127.0.0.130'));
select * from tab_accesslog;
select access_dttm, inet_ntoa(ip_addr) as ip_addr
from tab_accesslog
where ip_addr between inet_aton('127.0.0.128') and inet_aton('127.0.0.255');
16) MySQL 전용 암호화 (PASSWORD, OLD_PASSWORD)
현재 사용하는 MySQL버전은 5 이상이므로 신경 쓰지 않아도 된다.
[PASSWORD 함수의 변화]
MySQL의 PASSWORD() 함수는 MySQL DBMS 유저의 비밀번호를 관리하기 위한 함수이다.
일반 서비스의 고객 정보를 암호화하기 위한 용도로는 MD5, SHA 함수를 이용하는 것을 권장한다.
17) VALUES()
이 함수는 INSERT INTO ... ON DUPLICATE KEY UPDATE ... 형태의 SQL문장에서만 사용할 수 있다.
Primary Key나 Unique Key가 중복되는 경우에는 UPDATE를 수행하고, 그렇지 않으면 INSERT를 수행하는 문장이다.
create table tb_access(emp_no int, visit_date datetime, primary key(emp_no, visit_date));
insert into tb_access values (1, now());
insert into tb_access values (1, now());
insert into tb_access values (1, now());
insert into tb_access values (1, now());
insert into tb_access values (1, now());
insert into tb_stat(emp_no, visit_count)
select emp_no, count(*) as cnt
from tb_access
group by emp_no
on DUPLICATE KEY
UPDATE visit_count = visit_count + values(visit_count);
-- update절에서 values() 함수를 이용해 해당 컬럼에 insert 하려고 했던 값을 참조
-- values 함수의 인자값으로는 INSERT 문장에서 값을 저장하려고 했던 컬럼의 이름을 입력하면 된다.
select * from tb_stat;
18) COUNT()
COUNT 함수는 레코드 건수를 반환하는 함수로 '*'는 SELECT 절에 사용된 모든 컬럼을 가져오라는 것이 아니라 레코드 자체를 의미한다. 그래서 COUNT(1)과 COUNT(*)는 같은 속도로 처리된다
MyISAM 스토리지 엔진을 사용하는 테이블은 항상 테이블의 메타 정보에 전체 레코드 건수를 관리한다. WHERE절이 없는 COUNT(*) 쿼리의 결과는 바로 반환 된다.
인덱스를 제대로 사용하도록 튜닝하지 못한 COUNT(*) 쿼리는 페이징해서 데이터를 가져오는 쿼리보다 몇 배에서 몇 십배까지 느릴 수 있으므로 주의 깊게 작성해야 한다.
COUNT 함수는 컬럼명이나 표현식이 인자로 사용되면 인자의 결과가 NULL이 아닌 레코드 건수만 반환하므로 NULL이 포함된 컬럼이나 표현식을 사용할 때 의도대로 쿼리가 작동하는지 확인하는 것이 필요하다.
4. SQL 주석
-- 이 표기법은 한 라인만 주석으로 처리합니다.
/* 이 표기법은 여러 라인을
주석으로 처리합니다. */
/*!로 시작하는 주석은 선별적으로 기능이나 옵션을 적용하게 할 수 있다. 아래 예제는 /*! 주석 표기 뒤에 5자리 숫자가 나열되어 있다. MySQL 버전은 메이저 버전을 나타내는 1글자와 마이너 버전을 나타내는 2글자 그리고 패치버전으로 2글자를 사용해서 5글자의 숫자로 구성된다. 아래 주석 문장은 MySQL 버전에 따라 주석이 될수도 있고 실제 쿼리 문장의 일부가 될 수도 있다.
drop table tb_test;
create /*!50154 temporary */ table tb_test(fd int, primary key(fd));
-- MySQL 5.1.54 이상
create temporary table tb_test(fd int, primary key(fd));
-- MySQL 5.1.54 미만
create table tb_test(fd int, primary key(fd));
show create table tb_test;
CREATE TEMPORARY TABLE `tb_test` (
`fd` int NOT NULL,
PRIMARY KEY (`fd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
MySQL 5.1.54 이상에서는 임시 테이블을 생성하고, 그 이하 버전에서는 일반 테이블을 생성한다. MySQL5.0에서 쿼리나 프로시저에 포함된 주석은 모두 삭제 되기도 하는데 스토어드 프로그램의 코드에 주석을 추가할때 기준 버전을 최댓값으로 설정애두면 MySQL은 항상 주석으로만 인식하지만 삭제하지는 않게 된다.
create function sf_getstring()
returns varchar(20) character set utf9
begin
/*!99999 이 함수는 문자집합 테스트용 프로그램임 */
return '한글 테스트';
end;;
일반적으로 MySQL에서는 저장하려는 값의 길이가 컬럼의 길이보다 더 긴 경우라 하더라도 에러가 발생하지 않는다. 컬럼의 길이를 초과하는 부분은 버리고 저장 가능한 만큼만 컬럼에 저장한다. sql_mode 시스템 변수에 STRICT_ALL_TABLES가 설정되면 컬럼의 정해진 길이보다 큰 값을 저장할 때 경고가 아닌 오류가 발생하고 쿼리 실행이 중지된다.
2) STRICT_TRANS_TABLES (use)
컬럼의 타입과 호환되지 않는 값을 저장할 때, MySQL 서버는 비슷한 값으로 최대한 바꿔서 저장하려고 한다. STRICT_TRANS_TABLES를 설정하면 원하지 않는 데이터 타입의 변환이 필요할 때 MySQL 서버는 강제 변환하지 않고 에러를 발생시킨다.
3) TRADITIONAL
STRICT_TRANS_TABLES나 STRICT_ALL_TABLES와 비슷하지만 조금 더 엄격한 방법으로 SQL의 작동을 제어한다. STRINCT_ALL_TABLES,STRICT_TRANS_TABLES,TRADITIONAL등의 설정은 MySQL 서버가 조금 더 ANSI 표준 모드로 작동하도록 유도한다.
4) ANSI_QUOTES (use or no use)
MySQL에서는 문자열 값(리터럴)을 표현하기 위해 홑따옴표(')와 쌍따옴표(")를 동시에 사용할 수 있다. 하지만 오라클과 같은 DBMS에서는 single quotation(') 는 문자열 값을 표기하는데 사용하고, double quotation(")은 컬럼명이나 테이블명과 같은 식별자(identifier)를 구분하는 용도로만 사용한다.
sql_mode 시스템 설정에 ANSI_QUOTES를 설정하면 single quotation만 문자열 값 표기로 사용할 수 있고, double quotation은 식별자를 표기하는 데만 사용할 수 있다.
set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
select * from emp where ename = "SMITH"; -- 에러안남
-- ANSI_QUOTES 적용 시
set sql_mode = 'STRICT_TRANS_TABLES,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION';
select * from emp where ename = 'SMITH'; -- 싱글 쿼테이션만 문자를 표기하는데 사용할 수 있다.
5) ONLY_FULL_GROUP_BY (use)
MySQL의 쿼리에서는 GROUP BY절에 포함되지 않은 컬럼이라도 집합 함수의 사용 없이 그대로 SELECT절이나 HAVING절에 사용할 수 있다. sql_mode 시스템 설정에 ONLY_FULL_GROUP_BY를 설정하면 GROUP BY 문법에 맞게 작성해야 오류가 안난다.
[ONLY_FULL_GROUP_BY 설정 안할 때]
select deptno, count(*) from emp;
select deptno, empno, sal from emp group by deptno;
select deptno, empno, sal from emp group by deptno having sal>2000;
[ONLY_FULL_GROUP_BY 설정 할 때]
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
-- 위에 쿼리 실행 시 모두 오류난다.
-- 아래 쿼리는 sql 문법에 맞게 group by절을 작성해서 오류없이 작동한다.
select deptno, count(*) from emp group by deptno;
select deptno, empno, sal from emp group by deptno, empno, sal;
select deptno, empno, sal from emp group by deptno, empno, sal having sal >2000;
select deptno, count(*) from emp group by deptno having count(*) > 5;
6) PIPES_AS_CONCAT (use)
MySQL에서 "||"는 OR 연산자와 같은 의미로 사용된다. 하지만 sql_mode 시스템 설정에 PIPES_AS_CONCAT값을 설정하면 문자열 연결(CONCAT) 연산자로 사용할 수 있다.
set sql_mode = 'STRICT_TRANS_TABLES';
select 1 and 1 from dual; -- 1
select 0 or 1 from dual; -- 1
select 0 or 0 from dual; -- 0
select 0 || 0 from dual; -- 0
select 'a' || 'b' from dual; -- 0
set sql_mode = 'PIPES_AS_CONCAT,STRICT_TRANS_TABLES';
select 0 or 0 from dual; -- 0
select 0 || 0 from dual; --'00'
select 'a' || 'b' from dual; --'ab'
7) PAD_CHAR_TO_FULL_LENGTH
MySQL에서는 CHAR타입이라 하더라도 VARCHAR와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환된다. CHAR 타입의 컬럼값을 가져오는 경우, 뒤쪽의 공백이 제거되지 않고 반환돼야 한다면 sql_mode 시스템 설정에 PAD_CHAR_TO_FULL_LENGTH를 추가하면 된다.
CREATE TABLE tb_pad_char(
col_no int NOT NULL,
col_char char(5) COLLATE euckr_bin NOT NULL,
col_varchar varchar(5) COLLATE euckr_bin NOT NULL,
PAD_CHAR_TO_FULL_LENGTH 를 sql_mode에 추가 했을 때 char는 자릿수에서 문자를 뺀 나머지 공간을 공백으로 채운다.(문자+공백 = 자릿수) 그래서 이 설정값은 잘 쓰이지 않는다.
8) NO_BACKSLASH_ESCAPES
MySQL에서도 일반적인 프로그래밍 언어에서처럼 역 슬래시 문자를 이스케이프 문자로 사용할 수 있다. sql_mode 시스템 설정에 NO_BACKSLASH_ESCAPES를 추가하면 역 슬래시를 문자의 이스케이프 용도로 사용하지 못한다. 이 설정을 활성화하면 백 슬래시 문자도 다른 문자와 동일하게 취급한다.
9) IGNORE_SPACE (use)
MySQL에서 스토어드 프로시저나 함수의 이름 뒤에 공백이 있으면 "스토어드 프로시저나 함수가 없습니다"라는 에러가 출력될 수도 있다. MySQL에서는 스토어드 프로시저나 함수명과 괄호 사이에 있는 공백까지도 스토어드 프로시저나 함수의 이름으로 간주한다. 이 동작 방식이 기본 모드이므로 몇 번이고 함수가 있는지 확인하기도 한다. sql_mode 시스템 설정에 IGNORE_SPACE를 추가하면 프로시저나 함수명과 괄호 사이의 공백은 무시한다.
10) ANSI
이 값은 위에서 설명한 여러가지 옵션을 조합해서 MySQL서버가 최대한 SQL 표준에 맞게 동작하게 만들어 준다.
2. 영문 대소문자 구분
MySQL 서버가 운영체제에 상관없이 DB나 테이블명의 대소문자 구분의 영향을 받지 않게 하려면 MySQL 서버의 설정 파일에 "lower_case_table_names" 시스템 변수를 설정하면 된다. 이 변수를 1로 설정하면 모두 소문자로만 저장되고, MySQL 서버가 대소문자를 구분하지 않게 해준다. 이 변수가 0이면 DB나 테이블명에 대해 대소문자를 구분하지 않는다. 또한 이 설정 값에 2를 설정할 수도 있는데 이경우에는 저장은 대소문자를 구분해서 하지만 MySQL 쿼리에서는 대소문자를 구분하지 않게 해준다. (기본값은 1)
show variables like 'lower_case_table_names';
3. MySQL 예약어
데이터베이스나 테이블, 그리고 컬럼의 이름을 예약어와 같은 키워드로 생성하면 해당 컬럼이나 테이블을 SQL에서 사용하기 위해서는 항상 역따옴표(')나 쌍따옴표(")로 감싸줘야 한다. MySQL에서 이미 등록된 예약어의 개수는 적지 않으며, 예약어별로 문제가 되지 않는 키워드들도 있다. 메뉴얼을 통해 예약어인지 아닌지를 찾아 보든지 예약어로 MySQL에 직업 테이블을 생성해보면 MySQL서버가 에러로 알려준다.
성능에 미치는 영향이 큰 실행계획과 연관이 있는 단위 작업에 대해서 살펴보자. FULL Table Scan을 제외한 나머지는 모두 스토리지 엔진이 아니라 MySQL엔진에서 처리된다. MySQL엔진에서 부가적으로 처리하는 작업은 대부분 성능에 미치는 영향이 큰데 대부분 쿼리의 성능 저하의 원인이 된다. 스토리지 엔진에서 읽은 레코드를 MySQL엔진이 아무런 가공 작업도 하지 않고 사용자에게 반환한다면 최상의 성능을 보장하는 쿼리가 되겠지만, 우리가 필요로 하는 대부분의 쿼리는 그렇지 않다. MySQL 엔진에서 처리하는데 시간이 오래 걸리는 작업의 원리를 파악하여 쿼리를 튜닝하는데 사용하자.
1. FULL TABLE SCAN
FULL TABLE SCAN 은 INDEX를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 일거엇 요청된 작업을 처리하는 작업이다. MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.
1) 테이블의 레코드 건수가 너무 작어서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
2) WHERE절이나 ON절에 인덱스를 이용할 수 잇는 적절한 조건이 없는 경우
3) 인덱스 레인지 스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
4) max_seeks_for_key 변수를 특정 값(N)으로 설정하면 MySQL 옵티마이저는 인덱스의 기수성(Cardinality)이나 선택도(Selectivity)를 무시하고, 최대 N건만 읽으면 된다고 판단하게 된다. 이 값을 작게 설정할수록 MySQL 서버가 인덱스를 더 사용하도록 유도함
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahread) 작업이 자동으로 시작된다. Read ahead란 어떤 영역의 데이터가 앞으로 필요해지리라는것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다. 즉, 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 Foreground thread(client thread)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한번에 4개 또는 8개씩의 페이지를 읽으면서 계속 그 수를 증가시킨다. 이때 한번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장해 둔다. 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하여 쿼리 처리속도가 빨라진다.
MySQL5.1의 InnoDB 플러그인 버전부터는 언제 Read ahead를 시작할지 "innodb_read_ahead_threshold" 시스템 변수를 이용해 변경할 수 있다. 일반적으로 default 설정으로도 충분하지만 데이터웨어하우스용으로 MySQL을 사용한다면 이 옵션을 더 낮은 값으로 설정해서 더 자주 Read ahead가 시작되도록 유도할 수도 있다.
2. ORDER BY 처리 (Using filesort)
정렬을 처리하기 위해서 인덱스를 이용하는 방법과 쿼리가 실행될때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있다. MySQL이 인덱스를 이용하지 않고 별도의 정렬 처리를 수행하면 Extra 컬럼에 "Using Filesort"라는 코멘트가 표시된다.
장점
단점
인덱스
INSERT, UPDATE, DELETE 쿼리가 실행될 때 정렬되어 있는 인덱스를 사용하여 처리 속도가 빠르다
INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하다
인덱스 때문에 디스크 공간이 더 많이 필요하다
인덱스 개수가 늘어날수록 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시용 메모리가 많이 필요하다
Filesort
인덱스를 생성하지 않아도 되므로 인덱스 생성에 대한 부하가 없다
정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리 된다
정렬 작업이 쿼리 실행시 처리되므로 레코드 대상 건수가 많아질수록 정렬 작업에 대한 부하가 커서 처리 속도가 느려진다
[정렬부하를 없애기 위해서 인덱스를 이용하기 어려운 경우]
- 정렬기준이 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
- GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬해야 하는 경우
- UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
- Random하게 결과 레코드를 가져와야 하는 경우(때로는 인덱스를 이용할 수 있도록 개선할 수 있다. 임의(랜덤) 정렬을 참고하자)
1) 소트 버퍼(Sort buffer)
MySQL이 정렬을 수행하기 위한 할당받는 메모리 공간을 소트 버퍼라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다. 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.
정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면 정렬해야 할 레코드를 여러 조각으로 나눠서 처리한다. 이 과정에서 임시 저장을 위해 디스크를 사용한다.
메모리에서 소트 버퍼에서 정렬을 수행하고 그 결과를 디스크에 기록한다. 버퍼에서 그 다음 레코드를 가져와서 다시 소트버퍼에서 정렬해서 반복적으로 디스크에 임시 저장한다. 각 버퍼 크기 만큼 씩 정렬되어 임시 저장된 레코드를 다시 병합하면서 정렬을 수행해야 한다. 이 병합 작업을 Multi-merge라고 하며 수행된 멀티 머지 횟수는 Sort_merge_passes라는 상태 변수에 누적 된다.
show variables like 'sort_buffer_size';
show status like 'sort_merge_passes';
이 작업 들은 모두 디스크의 쓰기와 읽기를 유발하며 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아진다. 소트 버퍼를 크게 설정하면 디스크를 사용하지 않아서 더 빨라질 것으로 예상되지만 실제 벤치마크 결과로는 거의 차이가 없다. 소트버퍼 크기가 256kb에서 512kb 사이에서 최적의 성능을 보이며 더 크기가 확장되도 성능상 차이가 없었다. 하지만 8MB 이상일 때 성능이 조금 더 향상되는 것으로 벤치마킹됐다는 자료도 있는데 이는 OLTP 성격의 쿼리가 아니라 대용량의 정렬 작업에 해당되는 내용일 것이다. 소트버퍼의 이러한 특성은 리눅스의 메모리 할당 방식이 원인일 것으로 예측하는 사람들이 많지만 정확한 원인은 확인된 바가 없다.
MySQL은 글로벌 메모리 영역과 세션(로컬) 메모리 영역으로 나눌 수 있는데 소트 버퍼는 세션 메모리 영역에 해당한다. 즉 커넥션이 많을수록 정렬 작업이 많을수록 소트 버퍼로 소비되는 메모리 공간이 커진다. 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 MySQL 서버가 메모리 부족을 겪을 수도 있다. 또한 운영 체제의 OOM-Killer가 여유메모리를 확보하기 위해 메모리를 가장 많이 사용하고 있는 프로세스를 강제로 종료시킬 것이다. 일반적으로 MySQL서버가 가장 많은 메모리를 사용하기 때문에 강제 종료 1순위 이다.
권장 소트 버퍼의 크기는 56kb ~ 1MB미만이다.
2) 정렬 알고리즘
레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 2가지로 정렬 알고리즘을 나눌수 있다.
2-1) 싱글 패스(Single pass) 알고리즘
소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT되는 컬럼 전부를 담아서 정렬을 수행하는 방법이며, MySQL5.0 이후 버전에서 도입된 정렬 방법이다.
select emp_no, first_name, last_name
from employees
order by first_name;
emp_no.first_name, last_name을 first_name으로 정렬해서 select하는 쿼리를 싱글 패스 정렬 알고리즘으로 처리 한다. employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name 컬럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한다. 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트에 넘겨준다.
2-2) 투 패스(Two pass) 알고리즘
정렬 대상 컬럼과 Primary key 값만을 소트 버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 PK로 테이블을 읽어서 SELECT할 컬럼을 가져오는 알고리즘으로 예전 버전의 MySQL에서 사용하던 방법이다. 하지만 MySQL 5.0 5.1 그리고 5.5 버전에서도 특정 조건이 되면 이 방법을 사용한다.
위 그림은 MySQL의 예전 방식인 투 패스 알고리즘으로 정렬하는 괒어을 표현한 것이다. 먼저 정렬에 필요한 first_name 컬럼과 PK인 emp_no만 읽어서 정렬을 수행한다. 정렬이 완료되면 정렬 순서대로 employees 테이블을 한 번 더 읽어서 last_name을 가져오고 최종적으로 그 결과를 클라이언트 쪽으로 넘긴다.
MySQL의 예전 정렬방식인 투 패스 알고리즘은 테이블을 두 번 읽어야 하지만 새로운 정렬 방식인 싱글 패스 알고리즘은 이러한 불합리가 없다. 하지만 싱글 패스 알고리즘은 더 많은 소트 버퍼 공간이 필요하다. 즉 128KB의 정렬 버퍼를 사용한다면 이 쿼리는 투 패스 알고리즘에서 대략 7천건의 레코드를 정렬할 수 있지만 싱글 패스 알고리즘에서는 그서의 반 정도밖에 정렬할 수 없다. 물론 이것은 소트 버퍼 공간의 크기와 레코드의 크기에 의존적이다.
MySQL5.x 버전에서는 일반적으로 싱글패스 방식을 사용한다. 하지만 아래와 같을 때 싱글 패스 방식을 사용하지 못하고 투 패스 정렬 알고리즘을 사용한다.
- 레코드의 크기가 max_leghth_for_sort_data 파라미터로 설정된 값보다 클 때
- BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 떄
싱글패스 알고리즘은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며 투 패스 알고리즘은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라고 볼 수 있다.
*SELECT 쿼리에서 모든 컬럼(*)을 가져오면 정렬 버퍼를 몇 배에서 몇 십배까지 비효울적으로 사용하게 만들 가능성이 크다. 이런 이유로 꼭 필요한 컬럼만 조회하도록 쿼리를 작성하는 것이 좋다. 특히 정렬이 필요한 쿼리에서 그러하다. 이는 정렬 버퍼에만 영향을 미치는 것이 아니라 임시 테이블이 필요한 테이블에서도 영향을 미친다.
3) 정렬의 처리 방식
쿼리에 ORDER BY가 사용되면 3가지 처리 방식 중 하나로 정렬이 처리된다. 일반적으로 뒤의 정렬 방법일수록 처리가 느려 진다.
- 인덱스를 사용한 정렬
- 드라이빙 테이블만 정렬 (조인이 없는 경우 포함) : "Using filesort"가 표시
- 조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 정렬 : "Using temporary; Using filesort"가 같이 표시
인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)할 것이다. 이때 MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 두가지 방법중 하나를 선택한다.
1. 드라이빙 테이블만 정렬한 다음 조인을 수행
2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
조인이 수행된후 레코드 건수는 거의 배수로 늘어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다. 그래서 두 번째 방법보다는 첫 번째 방법이 더 효율적으로 처리된다.
3-1) 인덱스를 이용한 정렬
인덱스를 이용한 정렬을 위해서 반드시 ORDER BY에 명시된 컬럼이 먼저 읽는 드라이빙 테이블에 속하고 ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. WHERE절에 첫 번째 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. 인덱스를 이용한 정렬은 B-TREE인덱스와 NL조인방식에서만 사용할 수 있다. 또한 인덱스로 정렬이 처리될 때는 ORDER BY가 쿼리에 명시된다고해서 작업량이 더 늘지는 않는다. 조인 버퍼가 사용되면 순서가 바뀔수도 있으므로 정렬이 필요한 쿼리에서 ORDER BY절은 항상 명시해야 한다.
3-2) 드라이빙 테이블만 정렬
일반적으로 조인이 수행되면 결과 레코드 건수가 몇 배로 늘어난다. 그래서 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다. 이 방법은 조인에서 첫 번째 읽히는드라이빙 테이블의 컬럼만으로 ORDER BY절이 작성돼야 한다.
explain
select *
from employees e, salaries s
where s.emp_no = e.emp_no
and e.emp_no between 100002 and 100010
order by e.last_name;
다음 두 조건을 가지고 있어서 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것이다.
1. WHERE 절의 검색조건(emp_no between 100001 and 100010)은 employees 테이블의 Primary Key를 이용해 검색하면 작업량을 줄일 수 있다.
2. 드리븐 테이블(salaries)의 조인 컬럼인 emp_no 컬럼에 인덱스가 있다.
검색은 Index range scan으로 처리할 수 있지만 ORDER BY 절에 명시된 컬럼은 employees 테이블의 PK와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능하다. 그런데 ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블에 포함된 컬럼임을 알 수 있다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한 것이다.
[SQL 수행과정]
1. 인덱스를 이용해 emp_no between 100001 and 100010 조건을 만족하는 9건을 검색
2. 검색 결과를 last_name 컬럼으로 정렬을 수행(Filesort)
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해서 86건의 최종 결과를 가져옴
3-3) 임시 테이블을 이용한 정렬
2개 이상의 테이블을 정렬해야 한다면 임시 테이블이 필요할 수도 있다. 2개의 조인 테이블에서 ORDER BY절의 정렬 기준 컬럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 컬럼이면 조인결과를 임시테이블에 저장하고 그 결과를 정렬하게 된다.
explain
select *
from employees e, salaries s
where s.emp_no = e.emp_no
and e.emp_no between 100002 and 100010
order by s.salary;
쿼리의 실행계획을 보면 Extra 컬럼에 Using temporary; Using filesort가 표시된다. 이는 조인의 결과를 임시테이블에 저장하고, 그결과를 다시 정렬 했음을 의미한다.
3-4) 정렬 방식의 성능 비교
웬 서비스용 쿼리에서는 ORDER BY와 함께 LIMIT가 거의 필수적으로 사용되는 경향이 있다. 일반적으로 LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL서버가 처리해야 할 작업량을 줄이는 역할을 한다. 그런데 ORDER BY나 GROUP BY와 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼만 가져와서는 처리될 수 없다. 우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그룹핑 작업을 실행해야만 비료서 LIMIT로 건수 제한을 할 수 있다. WHERE절의 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 GROUP BY나 ORDER BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.
쿼리에서 인덱스를 사용하지 못하는 정렬이나 그룹핑 작업이 왜 느리게 작동할 수 밖에 없는지 살펴보자. 이를 위해 쿼리가 처리되는 방법을 "스트리밍 처리"와 "버퍼링 처리"라는 2가지 방식으로 구분해보자.
[스트리밍(Streaminig) 방식]
서버 쪽에서 처리해야 할 데이터가 얼마나 될지에 관계없이 조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식으로 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받을 것이다.
클라이언트는 MySQL서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다. OLTP 환경에서는 쿼리의 요청에서부터 첫 번재 레코드를 전달받게 되기까지의 응답 시간이 중요하다. 스트리밍 방식은 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장한다.
스트리밍 방식으로 처리되는 쿼리에서 LIMIT과 같이 결과 건수를 제한하는 조건들은 쿼리의 실행 시간을 줄여줄 수 있다. 매우 큰 테이블을 아무런 조건 없이 SELECT 하면 첫 번째 레코드는 아주 빨리 가져온다는 사실을 알 수 있다. 이것은 풀 테이블 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍됙 때문이다. 이 쿼리에 LIMIT 조건을 추가하면 전체적으로 가져오는 레코드 건수가 줄어들어 마지막 레코드를 가져오기까지의 시간을 상당히 줄일 수 있다.
스트리밍 처리는 어떤 클라이언트 도구나 API를 사용하느냐에 따라 그 방식에 차이가 있다. JDBC는 MySQL서버로부터 받는 레코드를 일단 자체적인 버퍼에 모두 담아둔다. 마지막 레코드가 전달될 때까지 기다렸다가 모든 결과를 전달받으면 클라이언트의 애플리케이션에 반환한다. 하지만 JDBC를 사용하지 않는 SQL 클라이언트 도구는 이러한 버퍼링을 하지 않기 때문에 큰 테이블이라 하더라도 첫 번째 레코드는 매우 빨리 가져온다.
JDBC 라이브러리와 MySQL 서버가 대화형으로 데이터를 주고받는 것이 아니라 MySQL서버는 데이터의 크기에 관계없이 무조건 보내고 JDBC는 MySQL 서버로부터 전송되는 데이터를 받아서 저장만 하여 불필요한 네트워크 요청을 최소화하여 전체 처리량이 뛰어나다. JDBC 버퍼링 처리 방식은 기본 작동 방식이며 아주 대량의 데이터를 가져와야 할 때는 MySQL서버와 JDBC간의 전송 방식을 스트리밍 방식으로 변경할 수 있다.
[버퍼링(Buffering) 방식]
ORDER BY나 GROUP BY와 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능 하게 한다. 우선 WHERE조건에 일치하는 모든 레코드를 가져온 후 정렬하거나 그룹핑을 해서 차례대로 보내야 하기 때문이다. MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려지는 것이다.
버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다. 그래서 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다. 네트워크로 전송되는 레코드의 건수를 줄일 수는 있지만 MySQL서버가 해야 하는 작업량에는 그다지 변화가 없기 때문이다.
ORDER BY는 인덱스를 사용한 정렬방식만 스트리밍 형태의 처리이며 나머지는 모두 버퍼링된 후에 정렬된다. 즉 인덱스를 사용한 정렬 방식은 LIMIT으로 제한된 건수만큼만 읽으면서 즉시 클라이언트로 결과를 전송해줄 수 있다.
하지만 인덱스를 사용하지 못하는 경우에 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로서 LIMIT으로 제한된 건수만큼 잘라서 클라이언트로 전송해 줄 수 있다.
조인과 함께 ORDER BY절과 LIMIT절이 함께 사용될 경우, 정렬의 각 처리 방식별로 어떤 차이가 있는지 보자
select *
from tb_test1 t1, tb_test2 t2
where t1.col1 = t2.col1
order by t1.col2
limit 10;
tb_test1 테이블의 레코드가 100건이고, tb_test2 테이블의 레코드가 1,000건(tb_test1의 레코드 1건당 tb_test2의 레코드가 10건씩 존재한다고 가정)이며, 두 테이블의 조인 결과는 전체 1,000건이라고 가정하고 정렬의 처리 방식별로 읽어야 한는 레코드 건수와 정렬을 수행해야 하는 레코드 건수를 비교한다.
tb_test1이 드라이빙이 되는 경우
정렬방식
읽어야할건수
조인횟수
정렬해야할대상건수
인덱스 사용
tb_test1 : 1건
tb_test2 : 10건
1번
0건
드라이빙테이블만 정렬
tb_test1 : 100건
tb_test2 : 10건
10번
100건 (드라이빙 테이블의 레코드 건수만큼 정렬 필용)
임시 테이블 사용후 정렬
tb_test1 : 100건
tb_test2 : 1000건
100번 (tb_test1 테이블의 레코드 건수만큼 조인 발생)
1000건 (조인된 결과 레코드 건수를 전부 정렬해야 함)
tb_test2이 드라이빙이 되는 경우
정렬방식
읽어야할건수
조인횟수
정렬해야할대상건수
인덱스 사용
tb_test2 : 10건
tb_test1 : 10건
10번
0건
드라이빙테이블만 정렬
tb_test2 : 1000건
tb_test1 : 10건
10번
1000건 (드라이빙 테이블의 레코드 건수만큼 정렬 필용)
임시 테이블 사용후 정렬
tb_test2 : 1000건
tb_test1 : 100건
1000번 (tb_test2 테이블의 레코드 건수만큼 조인 발생)
1000건 (조인된 결과 레코드 건수를 전부 정렬해야 함)
인덱스를 사용한 정렬을 유도하고 그렇지 못한다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하여 쿼리를 작성해야 한다.
정렬해야 할 대상 레코드가 1000건인 쿼리에서 LIMIT 10 이라는 조건이 있다면 MySQL서버는 필요한 순서(ASC OR DESC)대로 정렬해서 상위 10건만 퀵 소트 알고리즘을 사용하여 결과를 반환한다. 퀵소트 알고리즘의 특성상 정렬해야할 레코드 건수 N개를 전부 한번씩 PIVOT으로 설정하고 작은값, 큰값을 찾아서 비교 정렬을 수행하여야 한다. 그러므로 인덱스를 사용하지 못하는 쿼리를 페이징 처리를 사용하는 경우 LIMIT 10으로 조회한다고 하더라도 아주 빨라지지는 않는다.
4) 정렬 관련 상태 변수
MySQL 서버는 처리하는 주요 작업에 대해서 해당 작업의 실행 횟수를 상태 변수로 저장하고 있다. 정렬과 관련해서도 지금까지 몇 건의 레코드나 정렬 처리를 수행했는지, 소트 버퍼 간의 병합 작업(멀티 머지)은 몇 번이나 발생했는지 등을 다음과 같은 명령으로 확인한다.
show session status like 'Sort%';
Variable_name
Value
Sort_merge_passes
0
Sort_range
0
Sort_rows
27
Sort_scan
3
- Sort_merge_passes : 멀티 머지(소트 버퍼 간의 병합 작업) 처리 횟수를 의미한다.
- Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수이다
- Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다. Sort_scan과 Sort_range는 둘 다 정렬 작업 횟수를 누적하고 있는 상태 값이다.
- Sort_rows는 지금까지 정렬한 전체 레코드 건수를 의미한다.
3. GROUP BY 처리
GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 요소 중 하나다 GROUP BY절이 있는 쿼리에서의 HAVING절은 GROUP BY결과에 대해서 필터링 역할을 한다. GROUP BY작업이 인덱스를 사용하는 경우에 인덱스 스캔방법과 인덱스를 SKIP하면서 읽는 루스 인덱스 스캔방법으로 나뉜다. 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시테이블을 사용한다.
1) 인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그룹핑 할 때 GROUP BY 컬럼의 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과로 조인을 처리한다. GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹함수등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이므로 추가적인 정렬 작업은 필요하지 않다. 이런 그룹핑 방식을 사용하는 쿼리의 실행계획에서는 Extra 컬럼에 별도로 GROUP BY 관련 코멘트(Using index for group by)나 임시 테이블이나 정렬 관련 코멘트(Using temporary, Using filesort)가 표시되지 않는다.
2) 루스(loose) 인덱스 스캔을 이용하는 GROUP BY
루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미한다.
salaries PK : emp_no+from_date
explain
select emp_no
from salaries
where from_date = '1985-03-01'
group by emp_no;
Extra 컬럼의 메시지를 보면 GROUP BY 처리를 인덱스를 사용했다는 것을 알 수 있다.
1. emp_no+from_date 인덱스를 차례대로 스캔하면서, emp_no의 첫 번째 유일한 값(그룹 키) "100001" 을 찾아낸다.
2. emp_no+from_date 인덱스에서 emp_no가 '10001'인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져온다. 이 검색 방법음 1번 단계에서 알아낸 '10001' 값과 쿼리의 조건 from_date='1985-03-01' 조건을 합쳐서 "emp_no=10001 and from_date'1985-03-01'" 조건으로 PK인덱스를 검색하는 것과 흡사하다.
3. emp_no+from_date 인덱스에서 emp_no의 그 다음 유니크한(그룹 키) 값을 가져온다.
4. 3번단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행한다.
MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY처리에서만 사용할 수 잇다. 또한 Prefix index(컬럼의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없다. 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값이 수가 적을수록 성능이 향상된다. 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어 낸다. 루스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.
TB_TEST IDX(col1+col2+col3)
[루스 인덱스 스캔을 사용할 수 있는 쿼리]
SELECT col1, col2 FROM tb_test GROUP BY col1, col2;
SELECT disctinct col1, col2 FROM tb_test;
SELECT col1, min(col2) FROM tbl-test GROUP BY col1;
SELECT col1, col2 FROM tb_test WHERE col1 < const GROUP BY col1, col2;
SELECT MAX(col3), MAX(col3), col1, col2 FROM tb_test WHERE col2 > const GROUP BY col1, col2;
SELECT col2 FROM tb_test WHERE col1 < const GROUP BY col1, col2;
SELECT col1, col2 FROM tb_test WHERE col3 = const GROUP BY col1, col2;
[루스 인덱스 스캔을 사용할 수 없는 쿼리]
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1; -- MIN, MAX이외의 집합 함수에서 루스 인덱스 스캔은 사용 불가
SELECT col1, col2 FROM tb_test GROUP BY col2, col3; -- GROUP BY에 사용된 인덱스 구성 컬럼과 왼쪽부터 불일치
SELECT col1, col3 FROM tb_test GROUP BY col1, col2; -- SELECT 절의 컬럼이 GROUP BY와 일치하지 않다
일반적으로 B-Tee 인덱스는 인덱스를 구성하는 컬럼이 왼쪽부터 일치하는 형태로 사용될때만 사용할 수 있다. 하지만 루스 인덱스 스캔은 인덱스의 첫 번째 컬럼이 WHERE조건이나 GRUOP BY에 사용되지 않아도 B-Tree인덱스를 사용할 수 있는 방식이기도 하다. 옵티마이저가 인덱스의 첫 번째 컬럼에 대한 조건을 마음대로 만들어서 추가하는 형태로 이런 기능이 구현돼 있다. MySQL의 루스 인덱스 스캔의 아직 이정도로 최적화 되진 않았다.
3) 임시 테이블을 사용하는 GROUP BY
GRUOP BY 의 기준 컬럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할때는 임시테이블을 사용한다.
explain
select e.last_name, avg(s.salary)
from employees e, salaries s
where e.emp_no = s.emp_no
group by e.last_name;
Extra 컬럼에 "Using temporary" 메시지가 표시됐다. 임시 테이블이 사용된 것은 employees 테이블을 풀 스캔(ALL) 하기 때문이 아니라 인덱스를 전혀 사용할 수 없는 GROU P BY이기 때문이다.
[실행계획의 처리 절차]
1. Employees 테이블을 풀 테이블 스캔 방식으로 읽는다.
2. 1번 단계에서 읽은 employees 테이블의 emp_no 값을 이용해 salaries 테이블을 검색한다.
3. 2번 단계에서 얻은 조인 결과 레코드를 임시 테이블에 저장한다. 이 단계에서 사용되는 임시 테이블은 우너본 쿼리에서 GROUP BY절에 사용된 컬럼과 SELECT하는 컬럼만 저장한다. 이 임시 테이블에서 중요한 것은 GROUP BY 절에 사용된 컬럼으로 유니크 키를 생성한다는 점이다. 즉, GROUP BY가 임시테이블로 처리되는 경우 사용되는 임시 테이블은 항상 유니크 키를 가진다.
4. 1번 단계부터 3번 단계를 조인이 완료될 때까지 반복한다. 조인이 완료되면 임시 테이블의 유니크 키 순서대로 읽어서 클라이언트로 전송된다. 만약, 쿼리의 ORDER BY절과 GROUP BY절에 명시된 컬럼이 다르다면 Filesort 과정을 거치면서 다시 한번 정렬 작업을 수행한다.
4. DISTINCT 처리
DISTINCT는 MIN(), MAX() 또는 COUNT()와 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우로 두가지로 구분할 수 있다. 집합함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다. 하지만 실행 계획의 Extra 컬럼에는 "Using temporary" 메시지가 출력되지 않는다.
1) SELCT DISTINCT
SELECT DISTINCT는 GROUP BY와 거의 같은 방식이지만 정렬이 보장되지 않는다. 인덱스를 사용한다면 인덱스 순서대로 출력한다. MySQL 서버는 DISTINCT 뒤의 괄호는 그냥 의미없이 사용된 괄호로 해석하고 제거해 버린다.
select distinct(deptno), job from emp; => select distinct deptno, job from emp;
2) 집합 함수와 함께 사용된 DISTINCT
집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼 값이 유니크한 것들을 가져온다.
explain
select count(distinct s.salary)
from employees e, salaries s
where e.emp_no = s.emp_no
and e.emp_no between 100001 and 100100;
이 쿼리는 내부적으로 "COUNT(distinct s.salary)"를 처리하기 위해 임시 테이블을 사용한다. 하지만 이쿼리의 실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않는다. employees 테이블과 salaries 테이블을 조인한 결과에서 salary 컬럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용한다. 이때 임시 테이블의 salary 컬럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리다.
만약 위의 쿼리에 COUNT(DISTINCT...)를 하나 더 추가해서 다음과 같이 변경해보자. COUNT()함수가 두 번 사용된 다음 쿼리의 실행 계획은 위의 쿼리와 똑같이 표시된다. 하지만 다음 쿼리를 처리하려면 s.salary 컬럼의 값을 저장하는 임시 테이블과 e.last_name 컬럼의 값을 저장하는 또 다른 임시 테이블이 필요하므로 2개의 임시 테이블을 사용한다.
아래 쿼리와 같이 인덱스된 컬럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있다.
explain
select count(distinct emp_no) from employees;
explain
select count(distinct emp_no) from employees where emp_no between 10001 and 10100;
explain
select count(distinct emp_no) from dept_emp group by dept_no;
explain
select count(distinct emp_no) from dept_emp where dept_no between 'd001' and 'd002' group by dept_no;
5. 임시 테이블 (Using temporary)
MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 때는 내부적인 임시 테이블을 사용한다. "내부적" 이라는 단어가 포함된 것은 여기서 이야기하는 임시 테이블은 "CREATE TEMPORARY TABLE"로 만든 임시 테이블과 다르기 때문이다. 일반적으로 MySQL엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. 물론 특정 예외 케이스엔느 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 한다. 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.
1) 임시테이블이 필요한 쿼리
다음과 같은 패턴의 쿼리는 MySQL엔진에서 별도의 데이터 가공 작업을 필요로 하여 대표적으로 내부 임시 테이블을 생성하는 케이스다. 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 할 때가 많다
[1] ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
[2] ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
[3] DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
[4] UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 컬럼이 UNION RESULT인 경우)
[5] UNION ALL이 사용된 쿼리(select_type컬럼이 UNION RESULT인 경우)
[6] 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
Extra 컬럼에 "Using temporary"가 표시되면 임시 테이블을 사용하는 쿼리이다. "Using temporary"가 표시되지 않아도 임시 테이블을 사용할 수 있는데 위의 예에서 마지막 3개 패턴이 그런 예이다. 1-4번은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어 진다. 5-6번은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다. 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 상당히 처리 성능이 느리다.
2) 임시 테이블이 디스크에 생성되는 경우(MyISAM 스토리지 엔진을 사용)
내부 임시 테이블은 기본적으로는 메모리상에 만들어지지만 다음과 같은 조건을 만족하면 메모리에 임시 테이블을 생성할 수 없으므로 디스크상에 MyISAM 테이블로 만들어진다.
[1] 임시 테이블에 저장해야 하는 내용 중 BLOB(Binary Large Object)나 TEXT와 같은 대용량 컬럼이 있는 경우
[2] 임시 테이블에 저장해야 하는 레코드의 전체 크기나 UNION이나 UNION ALL에서 SELECT 되는 컬럼 중에서 길이가 512bytes 이상인 크기의 컬럼이 있는 경우
[3] GROUP BY나 DISTINCT 컬럼에서 512bytes 이상인 크기의 컬럼이 있는 경우
[4] 임시 테이블에 저장할 데이터의 전체 크기(데이터의 바이트 크기)가 tmp_table_size 또는 max_heap_table_size 시스템 설정 값보다 큰 경우
1-3번은 처음부터 디스크에 MyISAM 스토리지 엔진을 사용해서 내부 임시 테이블이 만들어진다. 하지만 4번은 처음에는 MEMORY 스토리지 엔진을 이용해 메모리에 내부 임시 테이블이 생성되지만 테이블의 크기가 시스템 설정 값을 넘어서는 순간 디스크의 MyISAM 테이블로 변환된다.
3) 임시 테이블 관련 상태 변수
임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 파악하려면 MySQL 서버의 상태변수를 확인하면 된다.
show session status like 'Created_tmp%';
select first_name, last_name
from employees
group by first_name, last_name;
show session status like 'Created_tmp%';
[Created_tmp_tables]
: 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값.
이 값은 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지를 구분하지 않고 모두 누적한다.
[Created_tmp_disk_tables]
: 디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값.
[Created_tmp_files]
: mysqld가 생성한 임시 파일의 개수
[mysql과 mysqld, mysqladmin의 차이점]
mysqld : server executable, 서버에서 실행가능한 것들 중 하나
mysql : command line client, 커맨드 라인 클라이언트
mysqladmin : maintainance or administarative utility, 유지 및 관리 유틸리티
Mysqld = Mysql + D, (D는 Daemon의 약자) mysqld는 백그라운드에서 돌아가고 있는 프로세스 즉, MYSQL 서버이고
mysql은 sql문을 실행시켜주는 command-line client이다.
4) 임시 테이블 관련 주의사항
레코드 건수가 많지 않으면 내부 임시 테이블이 메모리에 생성되고 MySQL의 서버의 부하에 크게 영향을 미치지 않는다. 성능상의 이슈가 될만한 부분은 내부 임시 테이블이 MyISAM 테이블로 디스크에 생성되는 경우다
explain select * from employees group by last_name order by first_name;
이 쿼리는 GROUP BY와 ORDER BY 컬럼이 다르고, last_name 컬럼에 인덱스가 없기 때문에 임시 테이블과 정렬 작업까지 수행해야 하는 가장 골칫거리가 되는 쿼리 형태다
Rows 컬럼의 값을 보면 이 쿼리는 대략 처리해야 하는 레코드 건수가 30만 건 정도라는 사실을 알 수 있다. 이 실행 계획의 내부적인 작업 과정을 살펴보자
[1] Employees 테이블의 모든 컬럼을 포함한 임시 테이블을 생성(Memory 테이블)
[2] Employees 테이블로부터 첫 번째 레코드를 InnoDB 스토리지 엔진으로부터 가져와서
[3] 임시 테이블에 같은 last_name이 있는지 확인
[4] 같은 last_name이 없으면 임시테이블에 INSERT
[5] 같은 last_name이 있으면 임시 테이블에 UPDATE 또는 무시
[6] 임시 테이블의 크기가 특정 크기보다 커지면 임시 테이블을 MyISAM 디스크로 이동
[7] Employees 테이블에서 더 읽을 레코드가 없을 때까지 2-6번 괒어 반복(이 쿼리에서는 약 30만 회 반복)
[8] 최종 내부 임시 테이블에 저장된 결과에 대해 정렬 작업을 수행
[9] 클라이언트에 결과 반환
디스크에 임시 테이블이 저장된 경우라면 30만건을 임시테이블로 저장하려면 적지 않은 부하가 발생한다. 가능하다면 인덱스를 이용해 처리하고, 내부 임시테이블이 메모리에만 저장될 수 있게 가공 대상 레코드를 적게 만들어야 한다. 가공 데이터를 줄일 수 없어서 tmp_table_size 또는 max_heap_table_size 시스템 설정 변수를 크게 설정하면 MySQL서버가 사용할 여유 메모리를 내부 임시 테이블이 모두 사용해버릴 수도 있으므로 주의해야 한다.
임시 테이블이 MEMORY(HEAP) 테이블로 물리 메모리에 생성될 때도 주의해야 할 점이 있다. MEMORY 테이블의 모든 컬럼은 고정 크기 컬럼이다. first_name 컬럼이 varchar(512)라면 512*3(문자집합을 utf8로 가정)bytes 공간을 차지 할 것이다. 실제 first_name 1글자이든 2글자이든 테이블에서 정의된 크기만큼 메모리 테이블에서 공간을 차지하므로 SELECT하는 컬럼은 최소화하고(특히, 불필요한 BLOB, TEXT 컬럼은 배제), 컬럼의 데이터 타입 선정도 가능한 작게 해주는 것이 좋다.
6. 테이블 조인
조인의 처리에서 어느 테이블을 먼저 읽을지를 결정 하는 것은 상당히 중요하며, 그에 따라 처리할 작업량이 상당히 달라진다. INNER JOIN은 어떤 테이블을 먼저 읽어도 결과가 달라지지 않으므로 MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다.
1) JOIN (INNER JOIN)
Nested-Loop 방식의 조인은 일반적으로 프로그램을 작성할 때 두 개의 FOR나 WHILE과 같은 반복 루프 문장을 실행하는 형태로 조인이 처리되는 것을 의미한다.
FOR( record1 IN TABLE1) { // 외부 루프(OUTER) : 드라이빙 테이블
FOR( record2 IN TABLE2) { //내부 루프(INNER) : 드리븐 테이블
IF (record1.join_column == record2.join_column) {
join_record_found(record1.*, record2.*);
}else{
join_record_notfound();
}
}
}
위의 의사 코드에서 알수 있듯이 2개의 반복루프로 두 개의 테이블을 조건에 맞게 연결해주는 작업이다. 중첩된 반복 루프에서 최종적으로 선택될 레코드가 안쪽 반복 루프(INNER TABLE)에 의해 결정되는 경우를 INNER JOIN이라고 한다. 즉, 두 개의 반복 루프를 실행하면서 TABLE2(INNER TABLE)에 " IF (record1.join_column == record2.join_column)" 조건을 만족하는 레코드만 조인의 결과로 가져온다.
2) OUTER JOIN
INNER JOIN에서 살펴본 의사 코드를 조금만 수정해서 살펴보자
FOR( record1 IN TABLE1) { // 외부 루프(OUTER) : 드라이빙 테이블
FOR( record2 IN TABLE2) { //내부 루프(INNER) : 드리븐 테이블
IF (record1.join_column == record2.join_column) {
join_record_found(record1.*, record2.*);
}else{
join_record_notfound(record1.*, NULL);
}
}
}
위 코드에서 TABLE2에 일치한느 레코드가 있으면 INNER 조인과 같은 결과를 만들어내지만, TABLE2에 조건을 만족하는 레코드가 없는 경우 TABLE1의 레코드에 TABLE2의 컬럼을 모두 NULL로 채워서 가져온다. 즉, OUTER 테이블의 레코드가 조인에 실패해도 결과에 포함된다. MySQL은 Full outer join을 지원하지 않는다. Full outer join을 UNION을 이용해 구현 할 수 있다.
select e.empno, e.deptno, d.deptno, d.dname
from emp e left join dept d on e.deptno = d.deptno
union all
select e.empno, e.deptno, d.deptno, d.dname
from emp e right join dept d on e.deptno = d.deptno
where e.empno is null;
LEFT OUTER JOIN에서 주의할점은 드리븐 되는 테이블은 모든 조인 조건을 ON절에 명시해야한다. 조인할 시점에 필터할 조건도 ON절에 명시해야한다. OUTER JOIN으로 연결되는 테이블의 컬럼의 조건이 ON절에 명시되지 않고 WHERE절에 명시되어 on절의 조건과 where절의 조건이 서로 충돌되면 옵티마이저는 쿼리를 INNER JOIN으로 변경해 버린다.
[LEFT OUTER JOIN]
select *
from employees e
left outer join salaries s on e.emp_no = s.emp_no
where s.salary > 155000;
[INNER JOIN]
select *
from employees e
inner join salaries s on e.emp_no = s.emp_no
where s.salary > 155000;
위와 같은 쿼리는 다음 2가지 중의 한 방식으로 수정해야 쿼리 자체의 의도나 결과를 명확히 할 수 있다.
[순수하게 OUTER JOIN으로 표현한 쿼리]
select *
from employees e
left outer join salaries s on e.emp_no = s.emp_no and s.salary > 155000;
[순수하게 INNER JOIN으로 표현한 쿼리]
select *
from employees e
inner join salaries s on e.emp_no = s.emp_no
where s.salary > 155000;
INNER JOIN 에서는 조인 조건을 WHERE절이나 ON절 중에서 어느 곳에 명시해도 성능상의 문제나 결과의 차이가 나지 않는다.
3) CARTESIAN JOIN
카테시안 조인은 조인조건이 없어서 2개 테이블의 모든 레코드 조합을 결과로 가져오는 조인 방식이다. MySQL에서 Cross join과 Inner join은 같은 조인 방식을 의미한다. 조인 조건이 있다면 INNER JOIN으로 처리되고 조인조건이 없으면 카테시안 조인으로 처리된다. 아래 SQL의 결과는 같다.
select d.*, e.*
from dept d
inner join emp e on d.deptno = e.deptno;
select d.*, e.*
from dept d
cross join emp e on d.deptno = e.deptno;
4) NATURAL JOIN
MySQL에서 INNER JOIN의 조건을 명시하는 방법은 여러가지가 있다. 아래의 3쿼리는 INNER JOIN을 명시한다. 네 번째 쿼리는 NATURAL JOIN을 명시한다. 아래의 네 쿼리는 모두 같은 결과를 가져온다.
select d.deptno, d.loc, e.*
from dept d, emp e
where d.deptno = e.deptno;
select d.deptno, d.loc, e.*
from dept d
inner join emp e on d.deptno = e.deptno;
select d.deptno, d.loc, e.*
from dept d
inner join emp e using(deptno);
-- using 키워드는 조인되는 두 테이블의 조인 컬럼이 같은 이름을 가지고 있어야 사용 가능하다.
select d.deptno, d.loc, e.*
from dept d
natural join emp e ;
NATURAL JOIN 은 dept, emp 테이블에서 서로 같은 이름의 컬럼을 모두 조인 조건으로 사용한다. 조인 조건이 같다면 NATURAL JOIN 은 INNER JOIN과 같은 결과를 가져온다. NATURAL JOIN은 테이블의 구조가 변경되면 두 테이블의 조인조건으로 사용했던 컬럼의 변경이 있는지 체크해야 하는등의 어려움이 있어서 실제로 사용되진 않는다.
5) Single-sweep multi join
MySQL의 Nested Loop JOIN을 Single-sweep multi join이라고 표현하기도 한다. 반복 루프를 돌면서 레코드 단위로 모든 조인 대상 테이블을 차례대로 읽는 방식이다. MySQL 조인의 결과는 드라이빙 테이블을 읽은 순서대로 레코드가 정렬되어 반환 된다. 조인에서 드리븐 테이블들은 드라이빙 테이블의 레코드를 읽는 순서대로 검색(Lookup) 한다.
6) 조인 버퍼를 이용한 조인(Using join buffer)
조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리된다. 예를 들어 드라이빙 테이블에서 일치하는 레코드가 1,000건이었다면 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1,000번의 풀 테이블 스캔을 해야한다. 그래서 MySQL 옵티마이저는 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립한다.
어떤 방법으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시(조인버퍼) 를 조인하는 형태로 처리한다. 조인버퍼는 join_buffer_size라는 시스템 설정 변수로 크기를 제한할 수 있으며, 조인이 완료되면 바로 해제된다.
select *
from dept_emp de, employees e
where de.from_date < '1985-12-31' and e.emp_no<109004;
[실행순서]
[1] dept_emp 테이블의 ix_from_date 인덱스를 이용해서 (from_date < '1985-12-31') 조건을 만족하는 레코드를 검색한다.
[2] 조인에 필요한 나머지 컬럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장한다.
[3] employees 테이블의 PK를 이용해 (emp_no<109004) 조건을 만족하는 레코드를 검색한다.
실제 위 쿼리의 실행순서는 드라이빙 테이블의 결과는 조인 버퍼에 담아 두고, 드리븐 테이블을 먼저 읽고 조인버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다. 일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해서 결정되지만 조인 버퍼가 사용되는 조인에서는 결과의 정렬순서가 보장되지 않는다.
조인버퍼가 사용되는 경우 처음 읽은 테이블의 결과가 너무 많아서 조인 버퍼에 담지 못하면 조인쿼리에 필요로 하는 컬럼만 저장된다.
7) 조인 실행 결과의 정렬 순서
MySQL은 nested-loop join 방법만 가지고 있어서 드라이빙 테이블로부터 레코드를 읽는 순서가 전체 쿼리의 결과 순서에 그대로 적용되는 것이 일반적이다. 그러나 실행 계획에 따라 인덱스를 사용하거나 Full Table Scan 등의 방법등으로 쿼리의 실행계획이 바뀌면서 항상 똑같은 정렬된 결과를 보장하지 않으므로 정렬된 결과를 원한다면 Order by절을 명시해야 한다. Order by절이 쿼리에 명시됐다고 해서 옵티마이저는 항상 정렬 작업을 수행하는 것이 아니다. 실행 계획상에서 이 순서를 보장할 수 있다면 옵티마이저가 자동으로 별도의 정렬 작업을 생략하고 반환한다. 만약 정렬이 보장되지 않는다면 정렬 작업을 통해서 정렬을 보장해준다. 즉, SQL쿼리 결과의 정렬을 항상 보장하는 방법은 ORDER BY절을 사용하는 것 밖에 없다.
8) INNER JOIN과 OUTER JOIN의 선택
OUTER JOIN과 INNER JOIN은 쿼리의 결과 레코드 건수가 같다면 성능 차이가 거의 없다. 아래의 두 쿼리를 비교해 보면 실제 비교를 수행하는 건수나 최종적으로 가져오는 결과 건수가 같다. Outer Join은 조인되는 inner 테이블의 해당 레코드의 존재 여부를 판단하는 별도의 트리거 조건이 한 번씩 실행되기 때문에 0.7초 정도 더 걸린 것으로 보인다. INNER JOIN과 OUTER JOIN은 성능을 고려해서 선택하는 것이 아니라 업무 요건에 따라 선택해야 한다.
쿼리 캐시는 성능을 향상시키기 위한 것 이었으나 심각한 확장성 문제가 있으며 쉽게 심각한 병목 현상이 발생할 수 있으므로 MySQL 8.0은 쿼리 캐시를 지원하지 않는다 (MySQL 5.7까지만 지원)
-- 331,603 rows
-- 29.499 sec
select straight_join count(*)
from dept_emp de
inner join employees e on de.emp_no = e.emp_no;
-- 331,603 rows
-- 30.224 sec
select straight_join count(*)
from dept_emp de
left join employees e on de.emp_no = e.emp_no;
7. 해시 조인
MySQL 8.0.18부터 각 조인에 동등조인 조건이 있으나 조인조건에 적용될 수 있는 인덱스가 없는 쿼리에 대해서 해시 조인을 사용한다. MySQL 8.0.20부터 블록 중첩 루프(Block Nested-Loop join)에 대한 지원이 제거되고 해시조인을 사용한다.EXPLAIN FORMAT=TREE형태로 Hash join 뿐만 아니라 나머지 실행계획을 확인 할 수 있다.
DERIVED는 FROM절에 사용된 서브 쿼리로부터 발생한 임시 테이블을 의미한다. 임시 테이블은 메모리에 저장될 수도 있고 디스크에도 저장될 수도 있다. 메모리에 저장하는 경우에는 성능에 영향이 미미하지만 데이터의 크기가 커서 임시 테이블을 디스크에 저장하면 성능이 떨어진다.
1.2 UNCACHEABLE SUBQUERY
쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리는 가능하면 MySQL 옵티마이저가 최대한 캐시되어 재사용될 수 있게 유도한다. 하지만 사용자 변수나 일부 함수가 사용된 경우에는 이러한 캐시 기능을 사용할 수 없게 만든다. 이런 실행 계획이 사용된다면 사용자변수나 함수를 제거해서 사용가능한지 검토해야 한다.
1.3 DEPENDENT SUBQUERY
쿼리의 FROM절 이외의 부분에서 사용하는 서브 쿼리가 자체적으로 실행되지 못하고, 외부 쿼리에서 값을 전달받아 실행되는 경우 DEPENDENT SUBQUERY가 표시된다. 서브쿼리가 외부 쿼리의 결과 값에 의존적이기 때문에 전체 쿼리의 성능을 안좋게 만든다. 서브 쿼리가 불필요하게 외부 쿼리의 값을 전달받고 있는지 검토해서 가능하다면 외부 쿼리와의 의존도를 제거하는 것이 좋다.
2. Type 컬럼
ALL, index
index는 인덱스 풀 스캔을 의미하면, ALL은 풀 테이블 스캔을 의미한다. 전체 레코드를 대상으로 스캔하는 작업 방식이라서 데이터가 많은 경우 성능 저하의 원인이 된다. 일반적인 OLTP 환경의 적합한 접근 방식은 아니므로 새로운 인덱스를 추가하거나 쿼리의 요건을 변경해서 이러한 접근 방법을 제거하는 것이 좋다.
3. Key 컬럼
쿼리가 인덱스를 사용하지 못할 때 실행 계획의 Key 컬럼에 아무 값도 표시되지 않는다. 쿼리가 인덱스를 사용할수 있게 인덱스를 추가하거나 WHERE 조건을 변경하는 것이 좋다.
4. Rows 컬럼
쿼리가 실제 가져오는 레코드 수보다 훨씬 더 큰 값이 Rows 컬럼에 표시되는 경우에는 쿼리가 인덱스를 정상적으로 사용하고 있는지 여부와 그 인덱스가 충분히 작업 범위를 좁혀 줄 수 있는 컬럼으로 구성됐는지 검토해보자. 인덱스가 효율적이지 않다면 충분히 식별성을 가지고 있는 컬럼을 선정해 인덱스를 다시 생성하거나 쿼리의 요건을 변경해보는 것이 좋다.
Rows 컬럼의 수치를 판단할 때 주의해야 할 점은 LIMIT가 포함된 쿼리라 하더라도 LIMIT의 제한은 Rows 컬럼의 고려 대상에서 제외된다는 것이다. 즉 "LIMIT 1"로 1건만 SELECT 하는 쿼리라 하더라도 Rows 컬럼에는 훨씬 큰 수치가 표현될 수 있으며, 성능상 아무런 문제가 없고 최적화된 쿼리일 수도 있다는 것이다.
5. Extra 컬럼
실행계획의 Extra 컬럼에는 쿼리를 실행하면서 처리한 주요 작업에 대한 내용이 표시되기 때문에 쿼리를 튜닝할 때 주용한 단서가 되는 내용이 표시된다.
5.1 쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우
- Full scan on NULL key
- Impossible HAVING (MySQL5.1부터)
- Impossible WHERE (MySQL5.1부터)
- Impossible WHERE noticed after reading const tables
- No matching min/max row (MySQL5.1부터)
- No matching row in const table (MySQL5.1부터)
- Unique row not found (MySQL5.1부터)
위와 같은 코멘트가 Extra 컬럼에 표시된다면 우선 쿼리가 요건을 제대로 반영해서 작성됐거나 버그가 생길 가능성이 없는지 확인해야 한다. 또는 개발용 데이터베이스에 테스트용 레코드가 제대로 준비돼 있는지 확인해 보자. 이 항목들은 성능과 관계가 깊지 않고 단지 "그런 레코드가 없음" 이라는 의미가 강하기 때문에 이 쿼리로 인한 버그의 가능성이 있을지 집중적으로 검토하는 것이 좋다. 물론 쿼리가 업무적인 요건을 제대로 반영하고 있다면 무시하자.
5.2 쿼리의 실행 계획이 좋지 않은 경우
- Range checked for each record (index map:N)
- Using filesort
- Using join buffer (MySQL5.1부터)
- Using temporary
- Using where
위와 같은 comments가 Extra 컬럼에 표시된다면 쿼리를 더 최적화하는 방안을 검토해보자. Using where는 대부분의 쿼리에서 표시되는 경향이 있기 때문에 그냥 지나치기 쉬운데 만약 실행 계획의 Rows 컬럼의 값이 실제 SELECT되는 레코드 건수보다 상당히 높은 경우에는 반드시 보완해서 Rows 컬럼의 값과 실제 SELECT 되는 레코드수의 차이를 최대한 줄이는 것이 중요하다.
5.3 쿼리의 실행 계획이 좋은 경우
- Distinct
- Using index
- Using index for group by
위 항목은 최적화되어 처리되고 있음을 알려주는 지표이다. 특히 Using index는 쿼리가 커버링 인덱스로 처리되고 있음을 알려주는데 MySQL에서 제공할 수 있는 최고의 성능을 보여준다. 만약 쿼리를 최적화해도 성능 요건에 미치지 못한다면 인덱스만으로 쿼리가 처리(커버링 인덱스)되는 형태로 유도해 볼 수 있다.
const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 컬럼에 이 내용이 표시된다. Extra 컬럼에 이런 메시지가 표시되는 경운에는 테이블에 적절히 테스트용 데이터를 저장하고 실행 계획을 확인해 보는 것이 좋다.
2) DISTINCT
Extra 컬럼에 Distinct 키워드가 표시
departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no만 중복 없이 유니크하게 가져오기 위한 쿼리이다. 두 테이블을 조인해서 그 결과에 다시 DISTINCT 처리를 넣은 것이다.
쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인 했으며, dept_emp 테이블에서 꼭 필요한 레코드만 읽는다.
explain
select distinct d.dept_no
from departments d, dept_emp de where d.dept_no = de.dept_no ;
[DISTINCT 처리 방식]
쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인, dept_emp 테이블에서는 꼭 필요한 레코드만 읽는다.
3) Full scan on NULL key
"col1 IN (SELECT col2 FROM ...)" col1이 NULL을 만나면 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드이다. col1이 NULL값이 된다면 "NULL IN (SELECT col2 FROM ...)" 과 같이 바뀐다.SQL 표준에서는 NULL을 알수 없는 값으로 정의하며 NULL에 대한 연산 규칙을 정의하고 있다. 그 정의에 따라 이조건은 다음과 같이 비교 된다.
- 서브 쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
- 서브 쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
이 비교과정에서 col1이 NULL이면 FULL TABLE SCAN을 해야만 결과를 알아낼 수 있다. 하지만, col1이 NOT NULL이라면 해당사항이 없다.
EXPLAIN
SELECT d.dept_no, NULL IN (SELECT id.dept_name FROM departments id)
FROM departments d;
컬럼이 NOT NULL로 정의되지는 않았지만 NULL 비교 규칙을 무시해도 된다면 "col1 IS NOT NULL" 이라는 조건을 지정한다. col1이 NULL이라면 False가 되기 때문에 col1 IN (SELECT col2 FROM ...) 조건을 실행하지 않는다.
explain
select *
from emp e
where mgr is not null
and mgr in (select empno from emp);
"Full scan on NULL key" 코맨트가 실행계획의 Extra 컬럼에 표시 됐다고 하더라도 IN, NOT IN 연산자의 왼쪽에 있는 값이 실제로 NULL이 없다면 풀 테이블 스캔은 발생하지 않는다.
4) Impossible HAVING (MySQL 5.1부터)
HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra 컬럼에는 "Impossible HAVING" 키워드가 표시된다.
explain
select e.emp_no, count(*) as cnt
from employees e
where e.emp_no=10001
group by e.emp_no
having e.emp_no is null;
e.emp_no 컬럼은 Primary Key 이면서 NOT NULL 컬럼이다. e.emp_no is null 조건을 만족할 수 없으므로 Extra 컬럼에서 "Impossible HAVING"이라는 키워드를 표시한다. 테이블 구조상 불가능한 조건뿐 아니라 실제 데이터 때문에 이런 현상이 발생하기도 하므로 주의해야 한다. 대부분이 쿼리가 제대로 작성되지 못한 경우이므로 쿼리 내용을 다시 점검해야 한다.
5) Impossible WHERE (MySQL 5.1부터)
WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우 "Impossible WHERE" 가 표시된다.
explain
select * from employees e where e.emp_no is null;
emp_no 컬럼은 NOT NULL 이므로 emp_no is null 조건은 항상 FALSE가 되고 쿼리의 실행 계획에는 "불가능한 WHERE 조건"으로 Extra 컬럼에 출력된다.
6) no matching row in const table (Impossible WHERE noticed after reading const tables)
위의 "Impossible WHERE" 경우 실제 데이터를 읽어보지 않고 바로 테이블 구조상으로 불가능한 조건으로 판단할 수 있다.
explain
select * from employees e where e.emp_no=0;
이 쿼리는 실제로 실행되지 않으면 emp_no = 0 인 레코드가 있는지 없는지 판단할 수 없다. 그런데 이 쿼리의 실행 계획만 확인했을 뿐인데, 옵티마이저는 사번이 0인 사원이 없다는 것까지 확인한 것이다.
이를 토대로 MySQL이 실행 계획을 만드는 과정에서 쿼리의 일부분을 실행해 본다는 사실을 알 수 있다. 또한 이 쿼리는 employees 테이블의 프라이머리 키를 동등 조건으로 비교하고 있다. 이럴 때는 const 접근 방식을 사용한다.
위의 SQL 예제는 MySQL8.0에서 Extra에선 no matching row in const table 로 표시된다. Impossible WHERE noticed after reading const tables 로 표시되는 예제 못 찾았다.
*const 접근방식은 Primary Key or Unique key를 사용하여 반드시 1건을 반환한다.
쿼리에서 const 접근 방식이 필요한 부부은 실행 계획 수립 단계에서 옵티마이저가 직접 쿼리의 일부를 실행하고, 실행된 결과 값을 원본 쿼리의 상수로 대체한다.
select *
from employees oe
where oe.first_name = (select ie.first_name from employees ie where ie.emp_no=10001);
즉, 위와 같은 쿼리를 실행하면 WHERE 조건절의 서브쿼리는(Primary key통한 조회여서 const접근 방식을 사용) 옵티마이저가 실행한 결과를 다음과 같이 대체한 다음, 본격적으로 쿼리를 실행한다.
select *
from employees oe
where oe.first_name = 'Georgi';
explain
select *
from employees oe
where oe.first_name = (select ie.first_name from employees ie where ie.emp_no=10001);
7) No matching min/max row (MySQL 5.1부터)
MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때는 Extra 컬럼에 "No matching min/max row"라는 메시지가 출력된다. 그리고 MIN()이나 MAX()의 결과로 NULL이 반환된다.
explain
select min(dept_no), max(dept_no)
from dept_emp
where dept_no = '';
select min(dept_no), max(dept_no)
from dept_emp
where dept_no = '';
* Extra 컬럼에서 출력되는 내용 중에서 "No matching ..." 이나 "Impossible WHERE ..." 등의 메시지는 잘못 생각하면 쿼리 자체가 오류인 것처럼 오해하기 쉽다. 하지만 Extra 컬럼에 출력되는 내용은 단지 쿼리의 실행 계획을 산출하기 위한 기초 자료가 없음을 표현하는 것뿐이다. Extra 컬럼에 이러한 메시지가 표시된다고 해서 실제 쿼리 오류가 발생하는 것은 아니다.
8) no matching row in const table
아래 쿼리와 같이 조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없을 때 표시 된다. 이 메시지 또한 "Impossible WHERE ..."와 같은 종류로 실행 계획을 만들기 위한 기초 자료가 없음을 의미한다.
explain
select *
from dept_emp de,
(select emp_no from employees where emp_no=0) tb1
where tb1.emp_no = de.emp_no
and de.dept_no = 'd005';
9) No tables used (MySQL 5.0의 "No table"에서 키워드 변경됨
FROM 절이 없거나, FROM절에 상수 테이블을 의미하는 DUAL(컬럼과 레코드를 각각 1개씩만 가지는 상수 테이블)이 사용될 때는 Extra 컬럼에 "No tables used"라는 메시지가 표시된다.
explain select 1;
explain select 1 from dual;
10) Not exists
A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회할 때 NOT IN (subquery) 형태나 NOT EXISTS 연산자를 주로 사용한다. 이러한 형태의 조인을 Anti-JOIN 이라고 한다. 똑같이 아우터 조인(LEFT OUTER JOIN)을 이용해도 구현할 수 있다. 일반적으로 ANTI-JOIN으로 처리해야 하지만 레코드 건수가 많을 때는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.
아우터 조인을 활용해 dept_emp 테이블에는 있지만 departments 테이블에는 없는 dept_no를 조회하는 쿼리를 예제로 살펴보자. departments 테이블을 아우터로 조인해서 ON절이 아닌 WHERE 절의 아우터 테이블(departments)의 dept_no 컬럼이 NULL인 레코드만 체크해서 가져온다. 즉, 안티 조인은 일반 조인(INNER JOIN) 했을 때 나오지 않는 결과만 가져오는 방법이다.
explain
select *
from dept_emp de
left join departments d on de.dept_no = d.dept_no
where d.dept_no is null;
이렇게 아우터 조인을 이용해 안티 조인을 수행하는 쿼리에서는 Extra 컬럼에 Not exists 메시지가 표시된다. Not exists 메시지는 이 쿼리를 NOT EXISTS 형태의 쿼리로 변환해서 처리했음을 의미하는 것이 아니라 MySQL 내부적으로 어떤 최적화를 했는데 그 최적화의 이름이 "Not exists"인 것이다.
11) Range checked for each record (index map : N)
조인 조건에 상수가 없고 둘 다 변수(e1.emp_no, e2.emp_no) 인 경우, MySQL 옵티마이저는 e1 테이블을 먼저 읽고 조인을 위해 e2를 읽을 때, 인덱스 레인지 스캔과 풀 테이블 스캔 중에서 어느 것이 효율적일지 판단할 수 없게 된다. 즉, e1 테이블의 레코드를 하나씩 읽을 때마다 e1.emp_no 값이 계속 바뀌므로 쿼리의 비용 계산을 위한 기준 값이 계속 변하는 것이다. 사번이 1번부터 1억까지 있다고 가정해 보면 e1.emp_no=1인 경우에는 e2 테이블의 1억건 전부를 읽어야 한다. 하지만, e1.emp_no=100000000인 경우에는 e2 테이블 한 건만 읽으면 된다.
"매 레코드마다 어떤 인덱스 레인지 스캔을 할지 체크한다." 고 할 수 있다.
explain
select *
from employees e1, employees e2
where e1.emp_no <= e2.emp_no;
Extra 컬럼에 "Range checked for each record" 가 표시되면 type컬럼에는 ALL로 표시 된다. index map에 표시된 후보 인덱스를 사용할지 여부를 검토해서 후보 인덱스를 안쓰면 풀 테이블 스캔을 사용하여 ALL로 표시된다.
[index map]
CREATE TABLE tb_member(
mem_id INTEGER NOT NULL,
mem_name VARCHAR(100) NOT NULL,
mem_nickname VARCHAR(100) NOT NULL,
mem_region TINYINT,
mem_gender TINYINT,
mem_phone VARCHAR(25),
PRIMARY KEY (mem_id),
INDEX ix_nick_name (mem_nickname, mem_name),
INDEX ix_nick_region (mem_nickname, mem_region),
INDEX ix_nick_gender (mem_nickname, mem_gender),
INDEX ix_nick_phone (mem_nickname, mem_phone)
);
index map:0x19 라고 하면 0x19값의 이진 값은 11001이다. Show create table employees 명령으로 테이블 구조를 조회했을때 나열된 인덱스의 순번을 의미한다.
실행 계획에서 index map : 0x19 의 의미는 위의 표에서 각 자리 수의 값이 1인 인덱스를 사용 가능한 인덱스 후보로 선정했음을 의미한다. 각 레코드 단위로 후보 인덱스 중에 어떤 인덱스를 사용할지 결정 하는데 비트맵의 자리 수가 1인 순번의 인덱스가 대상이다.
*실행 계획의 Extra 컬럼에 "Range checked for each record"가 표시되는 쿼리가 많이 실행되는 MySQL 서버에서는 "SHOW GLOBAL STATUS" 명령으로 표시되는 상태 값 중에서 "Select_range_check"의 값이 크게 나타난다.
12) Scanned N databases (MySQL 5.1부터)
MySQL5.0부터 제공되는 INFORMATION_SCHEMA DB는 MySQL 서버 내에 존재하는 DB의 메타 정보(테이블, 컬럼, 인덱스 등의 스키마 정보) 를 모아두고 읽기 전용이며 실제로 테이블에 레코드가 있는 것이 아니라 조회시 메타 정보를 MySQL 서버의 메모리에서 가져와서 보여준다. 그래서 많은 테이블을 조회할 경우 시간이 걸린다.
MySQL5.1 부터는 INFOMATION_SCHEMA DB의 조회 성능이 개선 되었고 Extra 컬럼에 "Scanned N databases"라는 메시지가 표시된다. 여기서 N의 의미는 아래와 같다.
- 0 : 특정 테이블의 정보만 요청되어 데이터베이스 전체의 메타 정보를 읽지 않음
ex) employees DB의 employees 테이블 정보만 읽는 경우 "Scanned 0 databases" 표시
- 1 : 특정 데이터베이스내의 모든 스키마 정보가 요청되어 해당 데이터베이스의 모든 스키마 정보를 읽음
- ALL : MySQL 서버 내의 모든 스키마 정보를 다 읽음
explain
select table_name
from information_schema.tables
where table_schema = 'employees' and table_name = 'employees';
실제 실행계획은 "Scanned 0 databases"라고 표시 되지 않는다.
13) Select tables optimized away
MIN(), MAX()만 SELECT 절에 사용되어 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 Extra 컬럼에 표시된다.
INFORMATION_SCHEMA DB의 메타 정보를 조회하는 SELECT 쿼리에서만 표시되는 내용이다. 테이블의 메타 정보가 저장된 파일(*.FRM)과 트리거가 저장된 파일(*.TRG) 또는 데이터 파일 중에서 필요한 파일만 읽었는지 또는 불가피하게 모든 파일을 다 읽었는지 등의 정보를 보여준다. Extra 컬럼에 표시되는 4가지 메시지를 살펴보자.
- Skip_open_table : 테이블의 메타 정보가 저장된 파일을 별도로 읽을 필요가 없음
- Open_frm_only : 테이블의 메타 정보가 저장된 파일(*.FRM)만 열어서 읽음
- Open_trigger_only : 트리거 정보가 저장된 파일(*.TRG)만 열어서 읽음
- Open_full_table : 최적화되지 못해서 테이블 메타 정보 파일(*.FRM)과 데이터(*.MYD) 및 인덱스 파일(*.MYI)까지 모두 읽음
위의 내용에서 데이터(*.FRM)파일이나 인덱스(*.MYI)에 관련된 내용은 MyISAM(마이아이삼)에만 해당하며, InnoDB 스토리지 엔진을 사용하는 테이블에는 적용되지 않는다.
[ 테이블 위치 ]
show variables like 'datadir' ;
employees.ibd (IBD 파일: INNO DB 각 테이블당 데이터가 저장되는 파일)
salaries_part1 파티션의 경우 아래와 같이 저장됨.
employee_name.MYD (MyISAM Data파일)
employee_name.MYI (MyISAM Index파일)
perfomance_schema 파일은 SDI파일로 저장된다.
[데이터베이스 내 각각의 테이블을 IBD파일로 저장여부]
show variables like 'innodb_file_per_table';
'innodb_file_per_table' 값이 OFF(0) 이면 ibdata1(시스템 테이블스페이스)에 저장한다.
디스크 스토리지 영역(Filesystem) 중에 테이블스페이스는 유저테이블 스페이스(IBD)와 시스템테이블 스페이스(ibdata1)로 구분할 수 있다. 유저테이블스페이스에는 인덱스파일과 데이터 파일이 저장되고, 시스템테이블스페이스에는 dictionary 및 undo등이 저장된다.
order by 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 컬럼에는 "Using filesort" 코멘트가 표시된다.
MySQL 옵티마이저는 레코드를 읽어서 소트 버퍼(sort buffer)에 복사해 퀵 소트 알고리즘을 수행하여 정렬결과를 클라이언트에 보낸다.
[employees index : PRIMARY(emp_no), ix_first_name(first_name), ix_hiredate(hire_date)]
explain
select * from employees order by last_name desc;
실행 계획의 Extra 컬럼에 "Using filesort" 가 출력되는 쿼리는 부하가 발생하므로 쿼리를 튜닝하거나 정렬에 사용할 수 있는 인덱스를 생성하여 부하를 줄여 준다.
17) Using index (커버링 인덱스)
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있을 때 Extra 컬럼에 "Using index"가 표시된다. 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스를 검색해 일치하는 레코드의 주소값(rowid or Innodb : PK)을 이용해 데이터 파일의 레코드값을 가져오는 작업이다. 최악의 경우는 레코드 한 건 마다 디스크를 한번씩 읽어야 할 수도 있다.
explain
select first_name from employees where first_name = 'Aamer' ;
위 쿼리는 first_name 컬럼이 있는 ix_first_name 인덱스만 읽고 처리를 하여 Extra컬럼에 "Using index"라는 메시지가 출력된다. 인덱스만으로 처리되는 것을 커버링 인덱스(Covering index)라고 한다.
explain
select emp_no, first_name from employees where first_name = 'Aamer' ;
InnoDB 테이블의 보조 인덱스는 데이터 레코드의 주소 값으로 Primary Key를 사용한다. first_name만으로 인덱스를 만들어도 emp_no컬럼이 같이 저장되는 클러스터링 인덱스 특성 때문에 위 쿼리가 커버링 인덱스로 처리 된다.
* Extra 컬럼에 표시되는 "Using index"와 접근 방법(type컬럼)의 "index"를 자주 혼동할 때가 있는데 성능상 반대되는 개념이라서 반드시 구분해서 이해하자. "Using index"는 커버링 인덱스가 사용되어 인덱스만 읽고 쿼리를 처리하는 방식이고 type컬럼에 표시되는 "index"는 인덱스 풀 스캔으로 처리하는 방식이다.
18) Using index for group-by
Group by 처리를 위해 MySQL서버는 그룹핑 기준 컬럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그룹핑하는 형태의 고부하 작업을 필요로 한다. 하지만 group by 처리가 인덱스(B-Tree 인덱스만)를 이용하면 정렬된 인덱스 컬럼을 순서대로 읽으면서 그룹핑 작업만 수행한다. Group by 처리가 인덱스를 이용할 때 쿼리의 실행 계획에서는 Extra 컬럼에 "Using index for group-by" 메시지가 표시된다. 이를 "루스 인덱스 스캔" 이라고도 한다.
[루스 인덱스 스캔]
Group by 처리를 위해 단순히 인덱스를 순서대로 쭉 읽는 타이트 인덱스 스캔과는 달리 루스 인덱스 스캔은 인덱스에서 필요한 부분만 듬성 듬성 읽는다.
explain
select dept_no, min(emp_no), max(emp_no)
from dept_emp
group by dept_no;
explain
select dept_no, min(emp_no),max(emp_no)
from dept_emp
where dept_no between 'd002' and 'd004'
group by dept_no;
[타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리]
인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG()나 SUM() 또는 COUNT(*)와 같이 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성 듬성 읽을 수가 없다. 이런 쿼리는 단순히 GROUP BY를 위해 인덱스를 사용하기는 하지만 이를 루스 인덱스 스캔이라고 하지는 않는다.
explain
select dept_no, count(*) from dept_emp group by dept_no;
GROUP BY에서 인덱스를 사용하려면 GROUP BY 조건의 인덱스 사용 요건이 갖춰줘야 한다.
# WHERE 절에 조건이 없는 경우
Where 절의 조건이 전혀 없는 쿼리는 GROUP BY에 쓰인 컬럼과 MIN, MAX로 조회하는 컬럼이 인덱스가 있으면 루스 인덱스 스캔을 사용한다. 그렇지 못한 쿼리는 타이트 인덱스 스캔(인덱스 스캔)이나 별도의 정렬 과정을 통해 처리 된다.
[employees index : PRIMARY(emp_no), ix_first_name(first_name), ix_hiredate(hire_date)]
explain select dept_no from dept_emp group by dept_no;
explain select dept_no, min(emp_no) from dept_emp group by dept_no;
GROUP BY절은 인덱스를 사용할 수 있지만 WHERE 조건절이 인덱스를 사용하지 못할 때는 먼저 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야만 한다. 타이트 인덱스 스캔(인덱스 스캔) 과정을 통해 GROUP BY가 처리된다.
explain select dept_no, min(emp_no) from dept_emp where to_date = '9999-01-01' group by dept_no;
조건절에 사용된 인덱스를 GROUP BY 처리가 다시 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다. 만약 WHERE 조건절이 사용할 수 있는 인덱스와 GROUP BY가 사용할 수 있는 인덱스가 다른 경우라면 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있다.
explain
select dept_no, min(emp_no),max(emp_no)
from dept_emp
where dept_no between 'd002' and 'd004'
group by dept_no;
*루스 인덱스 스캔의 손익분기점
WHERE 절의 조건이 검색을 위해 인덱스를 이용하고 GROUP BY가 같은 인덱스를 사용할 수 있는 쿼리라 하더라도 인덱스 루스 스캔을 사용하지 않을 수 있다. 즉, WHERE 조건에 의해 검색된 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문이다. 루스 인덱스 스캔은 주로 대량의 레코드를 GROUP BY 하는 경우 성능 향상 효과가 있을 수 있기 때문에 옵티마이저가 손익 분기점을 판단한다.
다음 예제에서 WHERE 절의 검색 범위를 더 좁히면 Extra 컬럼에서 "Using index for group-by" 처리가 사라진 것을 확인할 수 있다.
explain
select emp_no
from salaries where emp_no between 10001 and 10099
group by emp_no;
explain
select emp_no
from salaries -- where emp_no between 10001 and 10099
group by emp_no;
19) Using join buffer (MySQL 5.1부터)
MySQL 옵티마이저는 조인되는 두 테이블에 있는 각 컬럼에서 인덱스를 조사하여 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다. 뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 큰 악영향을 미친다. 드리븐 테이블에 검색을 위한 적절한 인덱스가 없으면 드라이빙 테이블로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔이나 인덱스 풀 스캔해야 할것이다. 이때 드리븐 테이블의 비효율적인 검색을 보안하기 위해 드라이빙 테이블에서 읽은 레코드를 조인버퍼에 임시로 보관한다. 조인버퍼가 사용되면 Extra 컬럼에 "Using join buffer"란느 메시지가 표시된다.
조인버퍼는 join_buffer_size라는 시스템 설정 변수에 최대 사용 가능한 버퍼 크기를 설정할 수 있다. 온라인 웹 서비스용 서버라면 1MB정도로 충분하다. 아래 쿼리는 조인 조건이 없는 카테시안 조인을 수행하는 쿼리로 조인 버퍼를 사용한다.
explain
select *
from dept_emp de, employees e
where de.from_date > '2005-01-01' and e.emp_no < 10900;
[조인버퍼 사이즈]
show variables like 'join_buffer_size';
select 262144/1024/1024; --0.25
20) Using sort_union(...), Using union(...), Using intersect(...)
Index_merge 접근 방식(type='index_merge')로 실행되는 경우에 Extra 컬럼에 두 인덱스를 읽은 결과를 어떻게 병합했는지 설명하기 위해 아래 3개의 메시지중에 하나를 출력한다.
- Using intersect(...)
각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과의 교집합을 추출하는 작업을 수행
- Using union(...)
각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과의 합집합을 추출하는 작업을 수행
- Using sort_union(...)
Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로
대량의 range 조건들) 이 방식으로 처리. Using sort_union은 PK만 먼저 읽어서 졍렬하고 병합한 후에
레코드를 읽어서 반환한다.
실제로는 레코드 건수에 거의 관계없이 조건절에 사용된 비교 조건이 동등 조건이면 Using union()이 사용되며, 그렇지 않으면 Using sort_union() 이 사용된다. Using union은 싱글 패스 정렬 알고리즘을 사용하면 Using sort_union은 투 패스 정렬 알고리즘을 사용한다.
21) Using temporary
쿼리를 처리하는 동안 중간 결과를 저장하기 위해 임시 테이블(Temporary table)을 사용한다. 임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있다. Extra 컬럼에 "Using temporary"로 표시되며 임시 테이블이 메모리에 생성되었는지 디스크에 생성되었는지는 PLAN만으로 알 수 없다.
아래의 쿼리는 GROUP BY 컬럼과 ORDER BY 컬럼이 다르기 때문에 임시 테이블이 필요한 작업이다. 인덱스를 사용하지 못하는 GROUP BY 쿼리는 실행 계획에서 "Using temporary" 메시지가 표시되는 대표적인 형태의 쿼리다.
explain
select * from employees group by gender order by emp_no;
실행계획에 Extra 컬럼에 "Using temporary"가 표시되지 않지만 내부적으로 임시 테이블을 사용 할 때도 많다. 대표적으로 메모리나 디스크에 임시 테이블을 생성하는 쿼리는 다음과 같다.
- FROM절에 사용된 서브 쿼리는 무조건 임시 테이블을 생성한다. 이 테이블을 파생 테이블(Derived table)이라고 부르긴 하지만 결국 실체는 임시 테이블 이다.
- COUNT(DISTINCT column) 를 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에는 임시 테이블이 만들어 진다.
- UNION이나 UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용해서 결과를 병합한다.
- 인덱스를 사용하지 못하는 정렬 작업 또한 임시 버퍼 공간을 사용하는데 정렬해야 할 레코드가 많아지면 디스크를 사용한다. 정렬에 사용되는 버퍼도 실체는 임시 테이블과 같다. 쿼리가 정렬을 수행할 때는 실행 계획의 Extra 컬럼에 "Using filesort"라고 표시된다.
임시테이블이나 버퍼가 메모리에 저장됐는지 디스크에 저장됐는지는 MySQL 서버의 상태 변수 값으로 확인할 수 있다.
22) Using where
MySQL은 내부적으로 크게 MySQL엔진과 스토리지 엔진이라는 두 개의 레이어로 나눠서 볼 수 있다. 각 스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할을 하며, MySQL엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행한다. MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에만 Extra 컬럼에 "Using where" 코멘트가 표시된다.
각 스토리지 엔진에서 100건의 레코드를 읽었는데 MySQL 엔진에서 필터링이나 가공 없이 그대로 클라이언트로 전달하면 "Using where"가 표시되지 않느다. 작업 범위 제한 조건은 각 스토리지 엔진 레벨에서 처리되지만 체크 조건은 MySQL 엔진 레이어에서 처리된다.
explain
select * from employees where emp_no between 10001 and 10100 and gender = 'F';
위 쿼리에서 작업 범위 제한 조건은 "emp_no between 10001 and 10100"이며 "gender='F'" 는 체크 조건이다. 스토리지엔진은 100건의 데이터를 읽어서 MySQL엔진에 넘겨주고 MySQL엔진은 63건의 레코드를 필터링해서 37건을 클라이언트에게 전송한다. 여기서 filtered 컬럼의 값이 100 미만이라면 체크조건을 실행하여 필터링 했다는 의미로 볼 수 있다.(MySQL 엔진에 의해 필터링되고 50프로만 남았다)
MySQL엔진과 스토리지 엔진은 이원화된 구조이다. WHERE category=10 and name like '%abc%' 에서 category조건을 만족하는 레코드가 100건, name 조건을 만족하는 레코드가 10건이라면 MySQL엔진은 10건의 레코드를 위해 그 10배의 작업을 스토리지 엔진에 요청한다. InnoDB나 MyISAM과 같은 스토리지 엔진과 MySQL엔진은 모두 하나의 프로세스로 동작하기 때문에 성능에 미치는 영향이 작다. 하지만, 스토리지 엔진이 MySQL 엔진 외부에서 작동하는 NDB클러스터는 네트워크 전송 부하까지 겹치기 때문에 성능에 미치는 영향이 큰 편이다.
MySQL 5.1의 InnoDB 플러그인 버전부터는 이원화된 구조의 불합리를 제거하기 위해 WHERE절의 범위 제한 조건 뿐만 아니라 체크 조건까지 모두 스토리지 엔진으로 전달된다. 스토리지 엔진에서는 그 조건에 정확히 일치하는 레코드만 읽고 MySQL 엔진으로 전달하기 때문에 비효율이 사라진다. 위의 예제에서 스토리지 엔진이 꼭 필요한 10건의 레코드만 읽게 된다. MySQL에서 이런 기능을 "Condition push down"이라고 표현한다.
23) Using where with pushed condition
실행계획의 Extra 컬럼에 표시되는 "Using where with pushed condition" 메시지는 "Condition push down"이 적용됐음을 의미하는 메시지다. MySQL 5.1부터는 "Condition push down"이 InnoDB나 MyISAM 스토리지 엔진에도 도입되어 각 스토리지 엔진의 비효율이 상당히 개선됐다고 볼 수 있다.
하지만 MyISAM이나 InnoDB 스토리지 엔진을 사용하는 테이블의 실행 계획에는 "Using where with pushed condition" 메시지가 표시되지 않는다. 이 메시지는 NDB 클러스터 스토리지 엔진을 사용하는 테이블에서만 표시되는 메시지다. NDB 클러스터는 MySQL 엔진의 외부에서 작동하는 스토리지 엔진이라서 스토리지 엔진으로부터 읽은 레코드는 네트워크를 통해 MySQL 엔진으로 전달된다. NDB 클러스틑 여러 개의 노드로 구성되는데 "SQL 노드"는 MySQL 엔진 역할을 담당하며, "데이터 노드"는 스토리지 엔진 역할을 담당한다. 그리고 데이터 노드와 SQL 노드는 네트워크를 통해 TCP/IP 통신을 한다. 그래서 실제 "Condition push down"이 사용되지 못하면 상당한 성능 저하가 발생할 수 있다.
select 문장은 하나인데 여러개의 테이블이 조인되는 경우에는 ID값이 증가하지 않고 같은 ID를 부여한다.
2. select_type 컬럼
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다.
2-1. SIMPLE
UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT쿼리인 경우, 해당 쿼리 문장의 select_type은 SIMPLIE로 표시
2-2. PRIMARY
UNON이나 서브쿼리가 포함단 쿼리의 실행 계획에서 가장 outer에 있는 단위 쿼리는 Primary로 표시된다.
2-3. UNION
UNION으로 결합하는 쿼리의 첫 번째를 제외한 두 번째 이후 단위 쿼리의 select_type은 UNION으로 표시된다.
2-4. DEPENDENT UNION
UNION, UNION ALL로 결합된 단위 쿼리가 외부의 영향을 받는 것을 의미한다.
2-4. UNION RESULT
UNION 결과를 담아두는 테이블을 의미한다. MySQL에서 UNON ALL이나 UNION(DISTINCT) 쿼리의 결과는 임시테이블로 생성한다. 실행계획에서 이 임시테이블을 가리키는 라인의 select type이 UNION RESULT이다.
UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도로 id값을 부여하지 않는다.
2-5. SUBQUERY(서브쿼리)
여기서 SUBQUERY라고 하는 것은 From절 이외에서 사용되는 서브 쿼리만을 의미한다.
1). 서브쿼리의 사용 위치에 따른 구별
- Nested Query(중첩된 쿼리) : SELECT List에 사용된 서브쿼리
- Sub Query(서브 쿼리) : WHERE 절에 사용된 경우
- Derived(파생 테이블) : From 절에 사용된 서브 쿼리
2). 서브쿼리가 반환하는 값의 특성에 따른 구분
- Scalar SubQuery(스칼라 서브 쿼리) : 하나의 값만 반환하는 쿼리
- Row SubQuery(로우 서브 쿼리) : 컬럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리
2-6. DEPENDENT SUBQUERY
서브 쿼리가 OUTER 쿼리에서 정의된 컬럼을 사용하는 경우 inner 서브쿼리가 outer 쿼리의 컬럼에 의존적이라서 Dependent라는 키워드가 붙는다. 또한 outer쿼리가 실행된 후 inner쿼리가 실행돼야 하므로 일반 서브 쿼리보다는 처리 속도가 느릴 때가 많다.
2-7. DERIVED
서브쿼리가 FROM절에 사용된 경우 select_type=DERIVED인 실행계획 이다. Derived는 단위 SELECT의 결과를 메모리나 디스크에 임시 테이블(파생 테이블)을 생성 한다. Derived 서브쿼리의 파생테이블은 인덱스가 전혀 없으므로 다른 테이블과 조인시 성능상 불리 하다. MySQL6.0이상 버전부터는 From절의 서브쿼리에 대한 최적화 부분이 개선 예정이다. 자신이 사용하는 버전의 FROM절의 실행계획을 확인할 필요가 있다.
[MySQL8.0] 에서 아래 쿼리는 select_type이 Derived가 아니다 -> 최적화
EXPLAIN
SELECT *
FROM (SELECT de.emp_no FROM dept_emp de) tb
, employees e
WHERE e.emp_no = tb.emp_no;
2-9. UNCACHEABLE SUBQUERY
서브쿼리의 결과를 내부적인 캐시 공간에 담아두는 서브쿼리캐시
- SubQuery는 바깥쪽(Outer)의 영향을 받지 않고 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
- Dependent SubQuery는 의존하는 Outer 쿼리의 컬럼의 값 단위로 캐시해두고 사용한다.
[캐시를 사용하지 못하는 요소]
1) 사용자 변수가 서브 쿼리에 사용된 경우
2) NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
3) UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브 쿼리에 사용된 경우
[MySQL8.0]
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no = (SELECT @status FROM dept_emp de WHERE de.dept_no='d005')
;
id
select_type
table
type
possible_keys
key
key_len
ref
rows
filtered
Extra
1
PRIMARY
e
ALL
299645
100
Using where
2
UNCACHEABLE SUBQUERY
de
ref
PRIMARY,ix_fromdate,ix_empno_fromdate
PRIMARY
16
const
165571
100
Using where; Using index
2-10. UNCACHEABLE UNION
union 쿼리가 사용자변수나 UUID()같은 함수를 사용하여 UNCACHEABLE할 때 SELECT_TYPE에 표시된다.
3. TABLE 컬럼
MySQL의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 만약 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다. 별도의 테이블을 사용하지 않는 select 쿼리의 경우에는 NULL로 표시된다.
MySQL은 FROM절에 사용된 서브쿼리(Derived, 파생테이블)는 반드시 별칭을 가져야 한다.
select dttm from (select now() as dttm) ;
-----------------------------------------------------------------------
SQL Error [1248] [42000]: Every derived table must have its own alias
-----------------------------------------------------------------------
select dttm from (select now() as dttm) derived_table_alias ;
-----------------------------------------------------
DTTM
2021-01-09 19:48:10
-----------------------------------------------------
실행계획 분석 예제)
Table 컬럼에 <derived> 또는 <union>과 같이 "<>"로 둘러싸인 경우 이 테이블은 임시 테이블을 의미한다. 또한 "<>" 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id를 지칭한다.
1) 첫 번째 라인의 테이블이 <derived2>라는 것으로 보아 이라인보다 쿼리의 id가 2번인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비돼야 한다
2) 세 번째 라인의 쿼리 id 2번을 보면, select_type의 컬럼 값이 derived로 표시되어 있다. 즉, dept_emp테이블을 읽어서 파생테이블을 생성한다.
3) 세 번째 라인의 분석이 끝났으므로 다시 실행 계획의 첫 번째 라인으로 돌아가자
4) 첫 번째 라인과 두 번째 라인은 같은 id 값을 가지고 있으므로 2개의 테이블 <derived2>와 e 테이블은 조인되는 쿼리이다. 선행되는 <derived2>가 드라이빙 테이블이고, e 테이블이 드리븐 테이블이 된다. <derived2> 테이블을 먼저 읽어서 e테이블로 조인이 실행 된다.
4. type 컬럼
실행 계획에서 type 이후의 컬럼은 MySQL서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다.
type컬럼은 각 테이블의 접근 방식(Access type)으로 해석하면 된다.
[type컬럼에 표시될 수 있는 값] MySQL에서 부여한 우선순위로 성능이 빠른 순서대로 나열
system
const
eq_ref
ref
fulltext
ref_or_null
unique_subquery
index_subquery
range
index_merge
index
ALL
4-1. system
레코드가 1건 or 한건도 존재하지 않는 테이블을 참조하는 접근방법으로 MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다.
explain
select * from tb_dual;
id
select_type
table
type
key
key_len
rows
filtered
Extra
1
SIMPLE
tb_dual
index
PRIMARY
1
1
100
Using index
4-2. const
Primary key or Unique key 컬럼을 이용하는 조건절을 가지고 있어서 반드시 1건을 반환하는 처리방식 이다.
다른 DBMS에서는 UNIQUE INDEX SCAN이라 한다
explain
select * from employees where emp_no = 10001;
id
select_type
table
type
possible_keys
key
key_len
ref
rows
filtered
1
SIMPLE
employees
const
PRIMARY
PRIMARY
4
const
1
100
다중 키로 구성된 Primary key or Unique key 중에서 인덱스 일부 컬럼만 조건으로 사용할 때는 const 타입의 접근방법을 사용할 수 없다. 실제 레코드가 1건인지 2건이상인지는 데이터를 읽어봐야 알 수 있다.
show index from dept_emp;
primary : dept_no + emp_no
1) Primary Key 일부만 조건으로 사용할 때는 접근 방식이 const가 아닌 ref로 표시된다.
explain
select * from dept_emp where dept_no = 'd005';
2) Primary Key or Unique Index의 모든 컬럼을 동등 조건으로 사용하면 const 접근 방법을 사용한다.
explain
select *
from dept_emp
where dept_no = 'd005'
and emp_no = 10001;
*show index from employees
3) 실행계획의 type컬럼이 const인 실행계획은 쿼리의 최적화 단계에서 모두 상수화 한다.
explain
select count(*)
from employees e1
where first_name in (select first_name from employees e2 where emp_no = 100001);
위 쿼리는 옵티마이저에 의해 최적화 되는 시점에 아래와 같이 변환되어 쿼리 실행기로 전달되기 때문에 접근 방식이 const인 것이다.
select count(*)
from employees e1
where first_name = 'Jasminko';
4-3. eq_ref
테이블이 조인되는 쿼리의 Plan에서 표시 된다. 먼저 읽은 테이블의 컬럼 값을, 나중에 읽어야 할 테이블의 PK이나 Unique key 컬럼의 검색 조건에 사용한다. 뒤에 읽는 테이블을 unique key로 검색할 때 인덱스는 not null이어야 한다. 다중 컬럼으로 만들어진 PK나 유니크 인덱스는 모든 컬럼이 비교 조건에 사용되어야 eq_ref 접근 방법이 사용된다. 즉 조인에서 inner 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있다.
*show index from dept_emp;
explain
select *
from dept_emp de, employees e
where e.emp_no = de.emp_no and de.dept_no ='d005';
조인에서 뒤에오는 employees 테이블의 emp_no는 PK 이어서 실행 계획의 두 번째 라인은 type컬럼이 eq_ref로 표시된 것이다.
4-4. ref
ref 접근 방법은 eq_ref와 달리 조인의 순서와 관계없이 사용되며 인덱스의 종류와 상관없이 Equal조건으로 검색할 때 사용된다. 레코드가 반드시 1건이라는 보장이 없어서 const나 eq_ref보다는 빠르지 않다. 하지만 동등조건으로만 비교 되므로 매우 빠른 조회 방법의 하나이다.
explain
select * from dept_emp where dept_no='d005';
* type = ref 접근방법 , ref = const --> ref 비교 방식으로 사용된 입력 값이 상수('d005')
explain
select * from dept_emp where from_date = '1986-06-26';
* 인덱스 삭제 후 검색하면 ref 접근방법을 사용 못한다. 인덱스 ix_empno_fromdate는 선행조건인 emp_no가 없어서 사용하지 못한다.
drop index ix_fromdate on dept_emp;
explain
select * from dept_emp where from_date = '1986-06-26';
create index ix_fromdate on dept_emp(from_date);
[동등 비교 연산자를 쓰는 접근 방식(type)]
const
조인의 순서에 관계없이 PK, Unique 인덱스의 모든 컬럼에 대해 동등조건으로 검색(결과 1건)
eq_ref
조인에서 첫 번째 테이블의 컬럼값을 이용하여 두 번째 테이블을 PK, Unique 인덱스로 동증 조건 검색(두 번째 테이블의 결과 1건)
ref
조인의 순서와 인덱스의 종류에 관계없이 동등조건으로 검색(결과 1건이상)
동등조건은 "=" 또는 "<=>"을 의미한다. "=" 연산자는 null비교를 못하여 null=null, null=1 의 결과로 null을 반환한다. 하지만 "<=>" 연산자는 null비교를 수행하여 null<=>null 은 1을 null<=>1은 0을 반환한다.
fulltext 접근 방법은 Mysql의 전문 검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다. 지금 보는 type의 순서가 일반적인 처리 성능의 순서이지만 실제 데이터 분포나 레코드의 건수에 따라 처리 속도가 달라 질 수 있다. 하지만 전문 검색 인덱스는 통계정보가 관리되지 않으며, 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 한다. 그래서 MySQL 옵티마이저는 fulltext 접근 방법 보다 빠른 const나 eq_ref, ref 접근 방법등을 사용할 수 없을 때에 전문 인덱스를 사용할 수 있는 SQL에서는 쿼리의 비용과 관계없이 fulltext 접근 방법을 사용한다.
MySQL에서는 전문 검색 조건은 우선순위가 높다. 쿼리에서 전문인덱스를 사용하는 조건과 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방법이 const, eq_ref, ref가 아니면 일바적으로 MySQL은 전문 인덱스를 사용하는 조건을 선택해서 처리한다.
*전문 검색(fulltext) 인덱스 사용방법
CREATE TABLE `employee_name` (
`emp_no` int NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
PRIMARY KEY (`emp_no`),
FULLTEXT KEY `fx_name` (`first_name`,`last_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
show index from employee_name;
explain
select *
from employee_name
where emp_no=10001 /*PK를 한건만 조회하는 const 타입의 조건*/
and emp_no between 10001 and 10005 /* range 타입의 조건*/
and MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE); /*전문검색(FULLTEXT)조건*/
explain
select *
from employee_name
where emp_no between 10001 and 10005
and MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE); /*전문검색(FULLTEXT)조건*/
전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 사용하는 range 접근 방법이 더 빨리 처리된느 경우가 많아서 전문 검색 쿼리를 사용할 때는 각 조건별로 성능을 확인해 봐야 한다.
4-6. unique_subquery
where 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방식으로 서브쿼리에서 유니크한 값만 반환할 때 사용한다.
실제 Mysql 5.7, 8.0 테스트시 type=unique_subquery 형태의 실행계획 안나온다
explain
select *
from departments
where dept_no in (select dept_no from dept_emp where emp_no = 10001);
4-7. index_subquery
IN (subaquery) or IN (상수나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 제거돼야 한다. 이때 중복된 값을 인덱스를 이용해 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.
실제 Mysql 5.7, 8.0 테스트시 type=index_subquery 형태의 실행계획 안나온다
explain
select *
from departments
where dept_no in (select dept_no from dept_emp where dept_no between 'd001' and 'd003');
4-8. range
인덱스를 범위로 검색하는 경우를 의미한다 주로 "<, >, IS NULL, BETWEEN, IN, LIKE"등의 연산자를 이용해 인덱스를 검색할 때 사용된다. MySQL이 가지고 있는 접근 방법 중에서 우선순위가 낮지만 빠른 접근 방법 이다.
* 인덱스 레인지 스캔 = const, ref, range 라는 세 가지 접근 방법을 모두 묶어서 지칭한다.
4-9. index_merge
2개 이상의 인덱스를 이용해 각각의 검색 결과를 병합하는 처리 방식이다.
- 여러 인덱스를 읽어야 하므로 range 보다 효율성이 떨어진다.
- AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화 되지 못한다.
- 전문 검색(fulltext) 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
- index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두집합의 교집합ㄴ이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.
explain
select *
from employees
where emp_no between 10001 and 11000
or first_name = 'Smith';
4-10. index
인덱스를 처음부터 끝까지 읽는 INDEX FULL SCAN을 의미한다. 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 효울적이므로 FULL TABLE SCAN 보다는 빠르게 처리된다. 또한 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 풀 테이블 스캔보다 효율적일 수도 있다. index 접근 방법은 아래 1),2) 조건 1),3) 조건을 충족한느 쿼리에서 사용되는 읽기 방식이다.
1) range나 const 또는 ref와 같은 접근 방식으로 인덱스를 사용하지 못하는 경우
2) 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우
3) 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우
TABLE_NAME : departments
PRIMARY(dpet_no), ux_deptname(dept_name)
explain
select *
from departments
order by dept_name desc limit 10; /*정렬 처리를 안하고 index full scan*/
explain
select *
from departments
where dept_no > 'd004' /*정렬 처리를 하고 PK 사용*/
order by dept_name desc limit 3;
explain
select *
from departments
where dept_no > 'd004'
order by dept_name desc limit 2; /*정렬 처리를 안하고 index full scan*/
4-11. ALL
Full Table Scan 접근 방식이다. InnoDB에서 Full Table Scan 이나 Index Full Scan과 같은 대량의 디스크 I/O 작업을 위해 한번에 많은 페이지를 읽어 들이는 Read Ahead 기능을 제공한다.
*Read Ahead(리드 어헤드)
MySQL에서는 연속적으로 인접한 페이지가 연속해서 몇 번 읽히게 되면 백그라운드로 작동하는 읽기 스레드가 최대 한 번에 64개의 페이지씩 한꺼번에 디스크로부터 읽어들이기 때문에 한 번에 페이지 하나씩 읽어들이는 작업보다는 상당히 빠르게 레코드를 읽을 수 있다.
5. possible_keys
옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록일 뿐이다. 실제로 사용되는 인덱스 목록이 아니기 때문에 possible_keys 컬럼은 무시한다.
6. key
실행 계획에서 사용하는 인덱스를 표시한다. 실행 계획의 type이 ALL일 때와 같이 인덱스를 전혀 사용하지 못하면 Key 컬럼은 NULL로 표시된다.
MySQL에서 Primary key는 별도의 이름을 부여할 수 없으며, 기본적으로 PRIMARY라는 이름을 가진다. 그밖의 나머지 인덱스는 모두 테이블을 생성하거나 인덱스를 생성할 때 이름을 부여할 수 있다. 실행 계획뿐만 아니라 쿼리의 힌트를 사용할 때도 프라이머리 키을 지칭하고 싶다면 PRIMARY라는 키워드를 사용하면 된다.
7. key_len
다중 컬럼으로 구성된 인덱스에서 몇개의 컬럼까지 사용했는지 알려준다. 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다.
PRIMARY(dept_no+emp_no)
explain
select * from dept_emp where dept_no = 'd005';
접근 방법이 ref 방식일 때 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여 준다. 만약 상수 값을 지정했다면 ref 컬럼의 값은 const로 표시되고, 다른 테이블의 컬럼값이면 그 테이블 명과 컬럼 명이 표시된다.
ref 컬럼의 값이 "func" 라고 표시 될 때가 있다. Function의 줄임말으로 참조용으로 사용되는 값을 그대로 사용한 것이 아니라, 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미한다.
explain
select *
from employees e, dept_emp de
where e.emp_no = de.emp_no ;
explain
select *
from employees e, dept_emp de
where e.emp_no = (de.emp_no -1) ;
사용자가 명시적으로 값을 변환할 경우와 MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 컬럼에는 "func"가 출력된다. 문자집합이 일치하지 않는 두 문자열 컬럼을 조인한다거나, 숫자 타입의 컬럼과 문자열 타입의 컬럼으로 조인할 때가 대표적인 예다. 가능하다면 MySQL 서버가 이런 변환을 하지 않도록 조인 컬럼의 타입은 일치시키는 편이 좋다.
AND C.TABLE_NAME IN ('DEPARTMENTS','DEPARTMENTS_UTF8')
AND C.COLUMN_NAME = 'DEPT_NO'
ORDER BY ORDINAL_POSITION;
explain
select *
from departments d, dept_emp de
where d.dept_no = de.dept_no ;
explain
select *
from departments_utf8 d, dept_emp de
where d.dept_no = de.dept_no ;
9. rows
MySQL옵티마이저는 각 조건에 대해 가능한 처리 방식의 비용을 비교해 하나의 실행 계획을 수립한다. 이때 비용을 산정하는 방법은 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해 보는 것이다. 대상 테이블에 레코드, 인덱스 분포도를 통계정보를 기준으로 예측한다.
rows 컬럼은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. 이는 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 일고 체크해야 하는지를 의미한다.
explain
select * from dept_emp where from_date >= '1985-01-01';
*rows:331143 -> SQL 처리를 위해 대략 331,143건의 레코드를 읽어야 할것이라고 예측
explain
select * from dept_emp where from_date >= '2002-07-01';
*rows:292 -> SQL 처리를 위해 대략 292건의 레코드를 읽어야 할것이라고 예측, Date 타입의 key_len은 3바이트
explain
select * from dept_emp where from_date >= '1985-01-01' limit 10;
*풀 테이블 스캔을 사용하면 rows값이 331,143, LIMIT 10 조건을 추가하면 165,571로 반정도 줄었다. LIMIT이 포함되는 쿼리는 rows에 표시되는 값이 오차가 심해서 도움이 되지 않는다.
10. Extra
쿼리의 실행계획에서 성능상 중요한 내용인 자주 표시 된다.
1) const row not found
const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 컬럼에 이 내용이 표시된다. Extra 컬럼에 이런 메시지가 표시되는 경운에는 테이블에 적절히 테스트용 데이터를 저장하고 실행 계획을 확인해 보는 것이 좋다.
2) DISTINCT
Extra 컬럼에 Distinct 키워드가 표시
departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no만 중복 없이 유니크하게 가져오기 위한 쿼리이다. 두 테이블을 조인해서 그 결과에 다시 DISTINCT 처리를 넣은 것이다.
쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인 했으며, dept_emp 테이블에서 꼭 필요한 레코드만 읽는다.
explain
select distinct d.dept_no
from departments d, dept_emp de where d.dept_no = de.dept_no ;
[DISTINCT 처리 방식]
쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인, dept_emp 테이블에서는 꼭 필요한 레코드만 읽는다.
3) Full scan on NULL key
"col1 IN (SELECT col2 FROM ...)" col1이 NULL을 만나면 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드이다. col1이 NULL값이 된다면 "NULL IN (SELECT col2 FROM ...)" 과 같이 바뀐다.SQL 표준에서는 NULL을 알수 없는 값으로 정의하며 NULL에 대한 연산 규칙을 정의하고 있다. 그 정의에 따라 이조건은 다음과 같이 비교 된다.
- 서브 쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
- 서브 쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
이 비교과정에서 col1이 NULL이면 FULL TABLE SCAN을 해야만 결과를 알아낼 수 있다. 하지만, col1이 NOT NULL이라면 해당사항이 없다.
EXPLAIN
SELECT d.dept_no, NULL IN (SELECT id.dept_name FROM departments id)
FROM departments d;
컬럼이 NOT NULL로 정의되지는 않았지만 NULL 비교 규칙을 무시해도 된다면 "col1 IS NOT NULL" 이라는 조건을 지정한다. col1이 NULL이라면 False가 되기 때문에 col1 IN (SELECT col2 FROM ...) 조건을 실행하지 않는다.
explain
select *
from emp e
where mgr is not null
and mgr in (select empno from emp);
"Full scan on NULL key" 코맨트가 실행계획의 Extra 컬럼에 표시 됐다고 하더라도 IN, NOT IN 연산자의 왼쪽에 있는 값이 실제로 NULL이 없다면 풀 테이블 스캔은 발생하지 않는다.
4) Impossible HAVING (MySQL 5.1부터)
HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra 컬럼에는 "Impossible HAVING" 키워드가 표시된다.
explain
select e.emp_no, count(*) as cnt
from employees e
where e.emp_no=10001
group by e.emp_no
having e.emp_no is null;
e.emp_no 컬럼은 Primary Key 이면서 NOT NULL 컬럼이다. e.emp_no is null 조건을 만족할 수 없으므로 Extra 컬럼에서 "Impossible HAVING"이라는 키워드를 표시한다. 테이블 구조상 불가능한 조건뿐 아니라 실제 데이터 때문에 이런 현상이 발생하기도 하므로 주의해야 한다. 대부분이 쿼리가 제대로 작성되지 못한 경우이므로 쿼리 내용을 다시 점검해야 한다.
5) Impossible WHERE (MySQL 5.1부터)
WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우 "Impossible WHERE" 가 표시된다.
explain
select * from employees e where e.emp_no is null;
emp_no 컬럼은 NOT NULL 이므로 emp_no is null 조건은 항상 FALSE가 되고 쿼리의 실행 계획에는 "불가능한 WHERE 조건"으로 Extra 컬럼에 출력된다.
6) no matching row in const table (Impossible WHERE noticed after reading const tables)
위의 "Impossible WHERE" 경우 실제 데이터를 읽어보지 않고 바로 테이블 구조상으로 불가능한 조건으로 판단할 수 있다.
explain
select * from employees e where e.emp_no=0;
이 쿼리는 실제로 실행되지 않으면 emp_no = 0 인 레코드가 있는지 없는지 판단할 수 없다. 그런데 이 쿼리의 실행 계획만 확인했을 뿐인데, 옵티마이저는 사번이 0인 사원이 없다는 것까지 확인한 것이다.
이를 토대로 MySQL이 실행 계획을 만드는 과정에서 쿼리의 일부분을 실행해 본다는 사실을 알 수 있다. 또한 이 쿼리는 employees 테이블의 프라이머리 키를 동등 조건으로 비교하고 있다. 이럴 때는 const 접근 방식을 사용한다.
위의 SQL 예제는 MySQL8.0에서 Extra에선 no matching row in const table 로 표시된다. Impossible WHERE noticed after reading const tables 로 표시되는 예제 못 찾았다.
*const 접근방식은 Primary Key or Unique key를 사용하여 반드시 1건을 반환한다.
쿼리에서 const 접근 방식이 필요한 부부은 실행 계획 수립 단계에서 옵티마이저가 직접 쿼리의 일부를 실행하고, 실행된 결과 값을 원본 쿼리의 상수로 대체한다.
select *
from employees oe
where oe.first_name = (select ie.first_name from employees ie where ie.emp_no=10001);
즉, 위와 같은 쿼리를 실행하면 WHERE 조건절의 서브쿼리는(Primary key통한 조회여서 const접근 방식을 사용) 옵티마이저가 실행한 결과를 다음과 같이 대체한 다음, 본격적으로 쿼리를 실행한다.
select *
from employees oe
where oe.first_name = 'Georgi';
explain
select *
from employees oe
where oe.first_name = (select ie.first_name from employees ie where ie.emp_no=10001);
7) No matching min/max row (MySQL 5.1부터)
MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때는 Extra 컬럼에 "No matching min/max row"라는 메시지가 출력된다. 그리고 MIN()이나 MAX()의 결과로 NULL이 반환된다.
explain
select min(dept_no), max(dept_no)
from dept_emp
where dept_no = '';
select min(dept_no), max(dept_no)
from dept_emp
where dept_no = '';
* Extra 컬럼에서 출력되는 내용 중에서 "No matching ..." 이나 "Impossible WHERE ..." 등의 메시지는 잘못 생각하면 쿼리 자체가 오류인 것처럼 오해하기 쉽다. 하지만 Extra 컬럼에 출력되는 내용은 단지 쿼리의 실행 계획을 산출하기 위한 기초 자료가 없음을 표현하는 것뿐이다. Extra 컬럼에 이러한 메시지가 표시된다고 해서 실제 쿼리 오류가 발생하는 것은 아니다.
8) no matching row in const table
아래 쿼리와 같이 조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없을 때 표시 된다. 이 메시지 또한 "Impossible WHERE ..."와 같은 종류로 실행 계획을 만들기 위한 기초 자료가 없음을 의미한다.
explain
select *
from dept_emp de,
(select emp_no from employees where emp_no=0) tb1
where tb1.emp_no = de.emp_no
and de.dept_no = 'd005';
9) No tables used (MySQL 5.0의 "No table"에서 키워드 변경됨
FROM 절이 없거나, FROM절에 상수 테이블을 의미하는 DUAL(컬럼과 레코드를 각각 1개씩만 가지는 상수 테이블)이 사용될 때는 Extra 컬럼에 "No tables used"라는 메시지가 표시된다.
explain select 1;
explain select 1 from dual;
10) Not exists
A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회할 때 NOT IN (subquery) 형태나 NOT EXISTS 연산자를 주로 사용한다. 이러한 형태의 조인을 Anti-JOIN 이라고 한다. 똑같이 아우터 조인(LEFT OUTER JOIN)을 이요해도 구현할 수 있다. 일반적으로 ANTI-JOIN으로 처리해야 하지만 레코드 건수가 많을 때는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.
아우터 조인을 활용해 dept_emp 테이블에는 있지만 departments 테이블에는 없는 dept_no를 조회하는 쿼리를 예제로 살펴보자. departments 테이블을 아우터로 조인해서 ON절이 아닌 WHERE 절의 아우터 테이블(departments)의 dept_no 컬럼이 NULL인 레코드만 체크해서 가져온다. 즉, 안티 조인은 일반 조인(INNER JOIN) 했을 때 나오지 않는 결과만 가져오는 방법이다.
explain
select *
from dept_emp de
left join departments d on de.dept_no = d.dept_no
where d.dept_no is null;
이렇게 아우터 조인을 이용해 안티 조인을 수행하는 쿼리에서는 Extra 컬럼에 Not exists 메시지가 표시된다. Not exists 메시지는 이 쿼리를 NOT EXISTS 형태의 쿼리로 변환해서 처리했음을 의미하는 것이 아니라 MySQL 내부적으로 어떤 최적화를 했는데 그 최적화의 이름이 "Not exists"인 것이다.
11) Range checked for each record (index map : N)
조인 조건에 상수가 없고 둘 다 변수(e1.emp_no, e2.emp_no) 인 경우, MySQL 옵티마이저는 e1 테이블을 먼저 읽고 조인을 위해 e2를 읽을 때, 인덱스 레인지 스캔과 풀 테이블 스캔 중에서 어느 것이 효율적일지 판단할 수 없게 된다. 즉, e1 테이블의 레코드를 하나씩 읽을 때마다 e1.emp_no 값이 계속 바뀌므로 쿼리의 비용 계산을 위한 기준 값이 계속 변하는 것이다. 사번이 1번부터 1억까지 있다고 가정해 보면 e1.emp_no=1인 경우에는 e2 테이블의 1억건 전부를 읽어야 한다. 하지만, e1.emp_no=100000000인 경우에는 e2 테이블 한 건만 읽으면 된다.
"매 레코드마다 어떤 인덱스 레인지 스캔을 할지 체크한다." 고 할 수 있다.
explain
select *
from employees e1, employees e2
where e1.emp_no <= e2.emp_no;
Extra 컬럼에 "Range checked for each record" 가 표시되면 type컬럼에는 ALL로 표시 된다. index map에 표시된 후보 인덱스를 사용할지 여부를 검토해서 후보 인덱스를 안쓰면 풀 테이블 스캔을 사용하여 ALL로 표시된다.
[index map]
CREATE TABLE tb_member(
mem_id INTEGER NOT NULL,
mem_name VARCHAR(100) NOT NULL,
mem_nickname VARCHAR(100) NOT NULL,
mem_region TINYINT,
mem_gender TINYINT,
mem_phone VARCHAR(25),
PRIMARY KEY (mem_id),
INDEX ix_nick_name (mem_nickname, mem_name),
INDEX ix_nick_region (mem_nickname, mem_region),
INDEX ix_nick_gender (mem_nickname, mem_gender),
INDEX ix_nick_phone (mem_nickname, mem_phone)
);
index map:0x19 라고 하면 0x19값의 이진 값은 11001이다. Show create table employees 명령으로 테이블 구조를 조회했을때 나열된 인덱스의 순번을 의미한다.
실행 계획에서 index map : 0x19 의 의미는 위의 표에서 각 자리 수의 값이 1인 인덱스를 사용 가능한 인덱스 후보로 선정했음을 의미한다. 각 레코드 단위로 후보 인덱스 중에 어떤 인덱스를 사용할지 결정 하는데 비트맵의 자리 수가 1인 순번의 인덱스가 대상이다.
*실행 계획의 Extra 컬럼에 "Range checked for each record"가 표시되는 쿼리가 많이 실행되는 MySQL 서버에서는 "SHOW GLOBAL STATUS" 명령으로 표시되는 상태 값 중에서 "Select_range_check"의 값이 크게 나타난다.
12) Scanned N databases (MySQL 5.1부터)
MySQL5.0부터 제공되는 INFORMATION_SCHEMA DB는 MySQL 서버 내에 존재하는 DB의 메타 정보(테이블, 컬럼, 인덱스 등의 스키마 정보) 를 모아두고 읽기 전용이며 실제로 테이블에 레코드가 있는 것이 아니라 조회시 메타 정보를 MySQL 서버의 메모리에서 가져와서 보여준다. 그래서 많은 테이블을 조회할 경우 시간이 걸린다.
MySQL5.1 부터는 INFOMATION_SCHEMA DB의 조회 성능이 개선 되었고 Extra 컬럼에 "Scanned N databases"라는 메시지가 표시된다. 여기서 N의 의미는 아래와 같다.
- 0 : 특정 테이블의 정보만 요청되어 데이터베이스 전체의 메타 정보를 읽지 않음
ex) employees DB의 employees 테이블 정보만 읽는 경우 "Scanned 0 databases" 표시
- 1 : 특정 데이터베이스내의 모든 스키마 정보가 요청되어 해당 데이터베이스의 모든 스키마 정보를 읽음
- ALL : MySQL 서버 내의 모든 스키마 정보를 다 읽음
explain
select table_name
from information_schema.tables
where table_schema = 'employees' and table_name = 'employees';
실제 실행계획은 "Scanned 0 databases"라고 표시 되지 않는다.
13) Select tables optimized away
MIN(), MAX()만 SELECT 절에 사용되어 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 Extra 컬럼에 표시된다.
INFORMATION_SCHEMA DB의 메타 정보를 조회하는 SELECT 쿼리에서만 표시되는 내용이다. 테이블의 메타 정보가 저장된 파일(*.FRM)과 트리거가 저장된 파일(*.TRG) 또는 데이터 파일 중에서 필요한 파일만 읽었는지 또는 불가피하게 모든 파일을 다 읽었는지 등의 정보를 보여준다. Extra 컬럼에 표시되는 4가지 메시지를 살펴보자.
- Skip_open_table : 테이블의 메타 정보가 저장된 파일을 별도로 읽을 필요가 없음
- Open_frm_only : 테이블의 메타 정보가 저장된 파일(*.FRM)만 열어서 읽음
- Open_trigger_only : 트리거 정보가 저장된 파일(*.TRG)만 열어서 읽음
- Open_full_table : 최적화되지 못해서 테이블 메타 정보 파일(*.FRM)과 데이터(*.MYD) 및 인덱스 파일(*.MYI)까지 모두 읽음
위의 내용에서 데이터(*.FRM)파일이나 인덱스(*.MYI)에 관련된 내용은 MyISAM(마이아이삼)에만 해당하며, InnoDB 스토리지 엔진을 사용하는 테이블에는 적용되지 않는다.
[ 테이블 위치 ]
show variables like 'datadir' ;
employees.ibd (IBD 파일: INNO DB 각 테이블당 데이터가 저장되는 파일)
salaries_part1 파티션의 경우 아래와 같이 저장됨.
employee_name.MYD (MyISAM Data파일)
employee_name.MYI (MyISAM Index파일)
perfomance_schema 파일은 SDI파일로 저장된다.
[데이터베이스 내 각각의 테이블을 IBD파일로 저장여부]
show variables like 'innodb_file_per_table';
'innodb_file_per_table' 값이 OFF(0) 이면 ibdata1(시스템 테이블스페이스)에 저장한다.
디스크 스토리지 영역(Filesystem) 중에 테이블스페이스는 유저테이블 스페이스(IBD)와 시스템테이블 스페이스(ibdata1)로 구분할 수 있다. 유저테이블스페이스에는 인덱스파일과 데이터 파일이 저장되고, 시스템테이블스페이스에는 dictionary 및 undo등이 저장된다.
두 개의 테이블이 각각 Unique(PK포함) 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 Extra 컬럼에 표시된다.
create table test1(col1 int, col2 int, primary key(col1));
create table test2(col1 int, col2 int, primary key(col1));
insert into test1 values (1,1), (2,2);
insert into test2 values (1,1) ;
explain
select *
from test1 t1
left join test2 t2 on t1.col1 = t2.col1
where t1.col1 = 2;
16) Using filesort
order by 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 컬럼에는 "Using filesort" 코멘트가 표시된다.
MySQL 옵티마이저는 레코드를 읽어서 소트 버퍼(sort buffer)에 복사해 퀵 소트 알고리즘을 수행하여 정렬결과를 클라이언트에 보낸다.
[employees index : PRIMARY(emp_no), ix_first_name(first_name), ix_hiredate(hire_date)]
explain
select * from employees order by last_name desc;
실행 계획의 Extra 컬럼에 "Using filesort" 가 출력되는 쿼리는 부하가 발생하므로 쿼리를 튜닝하거나 정렬에 사용할 수 있는 인덱스를 생성하여 부하를 줄여 준다.
17) Using index (커버링 인덱스)
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있을 때 Extra 컬럼에 "Using index"가 표시된다. 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스를 검색해 일치하는 레코드의 주소값(rowid or Innodb : PK)을 이용해 데이터 파일의 레코드값을 가져오는 작업이다. 최악의 경우는 레코드 한 건 마다 디스크를 한번씩 읽어야 할 수도 있다.
explain
select first_name from employees where first_name = 'Aamer' ;
위 쿼리는 first_name 컬럼이 있는 ix_first_name 인덱스만 읽고 처리를 하여 Extra컬럼에 "Using index"라는 메시지가 출력된다. 인덱스만으로 처리되는 것을 커버링 인덱스(Covering index)라고 한다.
explain
select emp_no, first_name from employees where first_name = 'Aamer' ;
InnoDB 테이블의 보조 인덱스는 데이터 레코드의 주소 값으로 Primary Key를 사용한다. first_name만으로 인덱스를 만들어도 emp_no컬럼이 같이 저장되는 클러스터링 인덱스 특성 때문에 위 쿼리가 커버링 인덱스로 처리 된다.
* Extra 컬럼에 표시되는 "Using index"와 접근 방법(type컬럼)의 "index"를 자주 혼동할 때가 있는데 성능상 반대되는 개념이라서 반드시 구분해서 이해하자. "Using index"는 커버링 인덱스가 사용되어 인덱스만 읽고 쿼리를 처리하는 방식이고 type컬럼에 표시되는 "index"는 인덱스 풀 스캔으로 처리하는 방식이다.
18) Using index for group-by
Group by 처리를 위해 MySQL서버는 그룹핑 기준 컬럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그룹핑하는 형태의 고부하 작업을 필요로 한다. 하지만 group by 처리가 인덱스(B-Tree 인덱스만)를 이용하면 정렬된 인덱스 컬럼을 순서대로 읽으면서 그룹핑 작업만 수행한다. Group by 처리가 인덱스를 이용할 때 쿼리의 실행 계획에서는 Extra 컬럼에 "Using index for group-by" 메시지가 표시된다. 이를 "루스 인덱스 스캔" 이라고도 한다.
[루스 인덱스 스캔]
Group by 처리를 위해 단순히 인덱스를 순서대로 쭉 읽는 타이트 인덱스 스캔과는 달리 루스 인덱스 스캔은 인덱스에서 필요한 부분만 듬성 듬성 읽는다.
explain
select dept_no, min(emp_no), max(emp_no)
from dept_emp
group by dept_no;
explain
select dept_no, min(emp_no),max(emp_no)
from dept_emp
where dept_no between 'd002' and 'd004'
group by dept_no;
[타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리]
인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG()나 SUM() 또는 COUNT(*)와 같이 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성 듬성 읽을 수가 없다. 이런 쿼리는 단순히 GROUP BY를 위해 인덱스를 사용하기는 하지만 이를 루스 인덱스 스캔이라고 하지는 않는다.
explain
select dept_no, count(*) from dept_emp group by dept_no;
GROUP BY에서 인덱스를 사용하려면 GROUP BY 조건의 인덱스 사용 요건이 갖춰줘야 한다.
# WHERE 절에 조건이 없는 경우
Where 절의 조건이 전혀 없는 쿼리는 GROUP BY에 쓰인 컬럼과 MIN, MAX로 조회하는 컬럼이 인덱스가 있으면 루스 인덱스 스캔을 사용한다. 그렇지 못한 쿼리는 타이트 인덱스 스캔(인덱스 스캔)이나 별도의 정렬 과정을 통해 처리 된다.
[employees index : PRIMARY(emp_no), ix_first_name(first_name), ix_hiredate(hire_date)]
explain select dept_no from dept_emp group by dept_no;
explain select dept_no, min(emp_no) from dept_emp group by dept_no;
#WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우
GROUP BY절은 인덱스를 사용할 수 있지만 WHERE 조건절이 인덱스를 사용하지 못할 때는 먼저 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야만 한다. 타이트 인덱스 스캔(인덱스 스캔) 과정을 통해 GROUP BY가 처리된다.
explain select dept_no, min(emp_no) from dept_emp where to_date = '9999-01-01' group by dept_no;
#WHRER 절에 조건이 있지만 검색을 위해 인덱스를 사용하는 경우
조건절에 사용된 인덱스를 GROUP BY 처리가 다시 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다. 만약 WHERE 조건절이 사용할 수 있는 인덱스와 GROUP BY가 사용할 수 있는 인덱스가 다른 경우라면 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있다.
explain
select dept_no, min(emp_no),max(emp_no)
from dept_emp
where dept_no between 'd002' and 'd004'
group by dept_no;
*루스 인덱스 스캔의 손익분기점
WHERE 절의 조건이 검색을 위해 인덱스를 이용하고 GROUP BY가 같은 인덱스를 사용할 수 있는 쿼리라 하더라도 인덱스 루스 스캔을 사용하지 않을 수 있다. 즉, WHERE 조건에 의해 검색된 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문이다. 루스 인덱스 스캔은 주로 대량의 레코드를 GROUP BY 하는 경우 성능 향상 효과가 있을 수 있기 때문에 옵티마이저가 손익 분기점을 판단한다.
다음 예제에서 WHERE 절의 검색 범위를 더 좁히면 Extra 컬럼에서 "Using index for group-by" 처리가 사라진 것을 확인할 수 있다.
explain
select emp_no
from salaries where emp_no between 10001 and 10099
group by emp_no;
explain
select emp_no
from salaries -- where emp_no between 10001 and 10099
group by emp_no;
19) Using join buffer (MySQL 5.1부터)
MySQL 옵티마이저는 조인되는 두 테이블에 있는 각 컬럼에서 인덱스를 조사하여 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다. 뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 큰 악영향을 미친다. 드리븐 테이블에 검색을 위한 적절한 인덱스가 없으면 드라이빙 테이블로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔이나 인덱스 풀 스캔해야 할것이다. 이때 드리븐 테이블의 비효율적인 검색을 보안하기 위해 드라이빙 테이블에서 읽은 레코드를 조인버퍼에 임시로 보관한다. 조인버퍼가 사용되면 Extra 컬럼에 "Using join buffer"란느 메시지가 표시된다.
조인버퍼는 join_buffer_size라는 시스템 설정 변수에 최대 사용 가능한 버퍼 크기를 설정할 수 있다. 온라인 웹 서비스용 서버라면 1MB정도로 충분하다. 아래 쿼리는 조인 조건이 없는 카테시안 조인을 수행하는 쿼리로 조인 버퍼를 사용한다.
explain
select *
from dept_emp de, employees e
where de.from_date > '2005-01-01' and e.emp_no < 10900;
[조인버퍼 사이즈]
show variables like 'join_buffer_size';
select 262144/1024/1024; --0.25
20) Using sort_union(...), Using union(...), Using intersect(...)
Index_merge 접근 방식(type='index_merge')로 실행되는 경우에 Extra 컬럼에 두 인덱스를 읽은 결과를 어떻게 병합했는지 설명하기 위해 아래 3개의 메시지중에 하나를 출력한다.
- Using intersect(...)
각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과의 교집합을 추출하는 작업을 수행
- Using union(...)
각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과의 합집합을 추출하는 작업을 수행
- Using sort_union(...)
Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로
대량의 range 조건들) 이 방식으로 처리. Using sort_union은 PK만 먼저 읽어서 졍렬하고 병합한 후에
레코드를 읽어서 반환한다.
실제로는 레코드 건수에 거의 관계없이 조건절에 사용된 비교 조건이 동등 조건이면 Using union()이 사용되며, 그렇지 않으면 Using sort_union() 이 사용된다. Using union은 싱글 패스 정렬 알고리즘을 사용하면 Using sort_union은 투 패스 정렬 알고리즘을 사용한다.
21) Using temporary
쿼리를 처리하는 동안 중간 결과를 저장하기 위해 임시 테이블(Temporary table)을 사용한다. 임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있다. Extra 컬럼에 "Using temporary"로 표시되며 임시 테이블이 메모리에 생성되었는지 디스크에 생성되었는지는 PLAN만으로 알 수 없다.
아래의 쿼리는 GROUP BY 컬럼과 ORDER BY 컬럼이 다르기 때문에 임시 테이블이 필요한 작업이다. 인덱스를 사용하지 못하는 GROUP BY 쿼리는 실행 계획에서 "Using temporary" 메시지가 표시되는 대표적인 형태의 쿼리다.
explain
select * from employees group by gender order by emp_no;
실행계획에 Extra 컬럼에 "Using temporary"가 표시되지 않지만 내부적으로 임시 테이블을 사용 할 때도 많다. 대표적으로 메모리나 디스크에 임시 테이블을 생성하는 쿼리는 다음과 같다.
- FROM절에 사용된 서브 쿼리는 무조건 임시 테이블을 생성한다. 이 테이블을 파생 테이블(Derived table)이라고 부르긴 하지만 결국 실체는 임시 테이블 이다.
- COUNT(DISTINCT column) 를 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에는 임시 테이블이 만들어 진다.
- UNION이나 UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용해서 결과를 병합한다.
- 인덱스를 사용하지 못하는 정렬 작업 또한 임시 버퍼 공간을 사용하는데 정렬해야 할 레코드가 많아지면 디스크를 사용한다. 정렬에 사용되는 버퍼도 실체는 임시 테이블과 같다. 쿼리가 정렬을 수행할 때는 실행 계획의 Extra 컬럼에 "Using filesort"라고 표시된다.
임시테이블이나 버퍼가 메모리에 저장됐는지 디스크에 저장됐는지는 MySQL 서버의 상태 변수 값으로 확인할 수 있다.
22) Using where
MySQL은 내부적으로 크게 MySQL엔진과 스토리지 엔진이라는 두 개의 레이어로 나눠서 볼 수 있다. 각 스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할을 하며, MySQL엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행한다. MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에만 Extra 컬럼에 "Using where" 코멘트가 표시된다.
각 스토리지 엔진에서 100건의 레코드를 읽었는데 MySQL 엔진에서 필터링이나 가공 없이 그대로 클라이언트로 전달하면 "Using where"가 표시되지 않느다. 작업 범위 제한 조건은 각 스토리지 엔진 레벨에서 처리되지만 체크 조건은 MySQL 엔진 레이어에서 처리된다.
explain
select * from employees where emp_no between 10001 and 10100 and gender = 'F';
위 쿼리에서 작업 범위 제한 조건은 "emp_no between 10001 and 10100"이며 "gender='F'" 는 체크 조건이다. 스토리지엔진은 100건의 데이터를 읽어서 MySQL엔진에 넘겨주고 MySQL엔진은 63건의 레코드를 필터링해서 37건을 클라이언트에게 전송한다. 여기서 filtered 컬럼의 값이 100 미만이라면 체크조건을 실행하여 필터링 했다는 의미로 볼 수 있다.(MySQL 엔진에 의해 필터링되고 50프로만 남았다)
MySQL엔진과 스토리지 엔진은 이원화된 구조이다. WHERE category=10 and name like '%abc%' 에서 category조건을 만족하는 레코드가 100건, name 조건을 만족하는 레코드가 10건이라면 MySQL엔진은 10건의 레코드를 위해 그 10배의 작업을 스토리지 엔진에 요청한다. InnoDB나 MyISAM과 같은 스토리지 엔진과 MySQL엔진은 모두 하나의 프로세스로 동작하기 때문에 성능에 미치는 영향이 작다. 하지만, 스토리지 엔진이 MySQL 엔진 외부에서 작동하는 NDB클러스터는 네트워크 전송 부하까지 겹치기 때문에 성능에 미치는 영향이 큰 편이다.
MySQL 5.1의 InnoDB 플러그인 버전부터는 이원화된 구조의 불합리를 제거하기 위해 WHERE절의 범위 제한 조건 뿐만 아니라 체크 조건까지 모두 스토리지 엔진으로 전달된다. 스토리지 엔진에서는 그 조건에 정확히 일치하는 레코드만 읽고 MySQL 엔진으로 전달하기 때문에 비효율이 사라진다. 위의 예제에서 스토리지 엔진이 꼭 필요한 10건의 레코드만 읽게 된다. MySQL에서 이런 기능을 "Condition push down"이라고 표현한다.
23) Using where with pushed condition
실행계획의 Extra 컬럼에 표시되는 "Using where with pushed condition" 메시지는 "Condition push down"이 적용됐음을 의미하는 메시지다. MySQL 5.1부터는 "Condition push down"이 InnoDB나 MyISAM 스토리지 엔진에도 도입되어 각 스토리지 엔진의 비효율이 상당히 개선됐다고 볼 수 있다.
하지만 MyISAM이나 InnoDB 스토리지 엔진을 사용하는 테이블의 실행 계획에는 "Using where with pushed condition" 메시지가 표시되지 않는다. 이 메시지는 NDB 클러스터 스토리지 엔진을 사용하는 테이블에서만 표시되는 메시지다. NDB 클러스터는 MySQL 엔진의 외부에서 작동하는 스토리지 엔진이라서 스토리지 엔진으로부터 읽은 레코드는 네트워크를 통해 MySQL 엔진으로 전달된다. NDB 클러스틑 여러 개의 노드로 구성되는데 "SQL 노드"는 MySQL 엔진 역할을 담당하며, "데이터 노드"는 스토리지 엔진 역할을 담당한다. 그리고 데이터 노드와 SQL 노드는 네트워크를 통해 TCP/IP 통신을 한다. 그래서 실제 "Condition push down"이 사용되지 못하면 상당한 성능 저하가 발생할 수 있다.
11. EXPLAIN EXTENDED(Filtered 컬럼)
MySQL 5.1.12 버전부터는 필터링이 얼마나 효율적으로 실행됐는지를 사용자에게 알려주기 위해 실행 계획에 Filltered라는 컬럼이 새로 추가됐다. 실행계획에서 Filtered 컬럼을 함계 조회하려면 Explain 명령 뒤에 EXTENDED 라는 키워드를 지정하면 된다. MySQL 8.0 에서는 extended 키워드를 사용하면 SQL Error [1064] [42000]: You have an error in your SQL syntax; 오류가 난다. explain만 사용해도 filtered가 표시된다.
explain
select *
from employees
where emp_no between 10001 and 10100 and gender = 'F';
filtered 컬럼에는 MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는지의 비율(Percentage)이 표시된다. 스토리지 엔진이 전체 100건(rows=100)의 레코드를 읽어서 MySQL엔진에 전달 했는데 MySQL 엔진에 의해 필터링되고 50%만 남았다는 것을 의미한다. (50건=100건*1/2)
filtered 컬럼의 정보 또한 실제 값이 아니라 통계 정보로부터 예측된 값일 뿐이다. 아래 그림은 위의 쿼리가 실행되면서 스토리지 엔진과 MySQL 엔진에서 얼마나 레코드가 읽히고 버려졌는지를 표현한 것이다.
[rows와 filtered 컬럼의 의미]
12. EXPLAIN EXTENDED(추가 옵티마이저 정보)
MySQL 엔진에서 쿼리의 실행 계획을 산출하기 위해 쿼리 문장을 분석해 파스 트리를 생성한다. 또한 일부 최적화 작업에도 이 파스 트리를 이용해 수행한다. "EXPLAIN EXTENED" 명령어의 또 다른 기능은 분석된 파스 트리를 재조합해서 쿼리 문장과 비슷한 순서대로 나열해서 보여주는 것이다.(MySQL8.0에선 explain만 사용)
explain
show warnings; (cmd 창에서만 작동)
/* select#1 */
select 'Georgi' AS `first_name`
, (
/* select#2 */
select count(0)
from `employees`.`dept_emp` `de` join `employees`.`dept_manager` `dm`
where (`employees`.`de`.`dept_no` = `employees`.`dm`.`dept_no`)) AS `cnt`
from `employees`.`employees` `e`
where true
explain 명령어 실행 후에 바로 "show warnings" 명령을 실행하면 옵티마이저가 분석해서 다시 재조합한 쿼리 문장을 확인 할 수 있다. 위의 예제에서는 count(*)가 내부적으로는 count(0)으로 변환되어 처리 되었고 emp_no=10001 조건을 옵티마이저가 미리 실행해서 상수화된 값으로 'Georgi' 가 사용됐다는 것을 알 수 있다.
show warnigs 명령을 이용해서 옵티마이저가 어떻게 쿼리해석, 쿼리변환, 특수한 처리를 수행 했는지를 판단할 수 있다.
13. EXPLAIN PARTITIONS(Partitions 컬럼)
EXPLAIN 에서 partitions 컬럼은 테이블의 파티션 중에서 어떤 파티션을 사용 했는지 표시 된다. 아래 쿼리에서 조회하려는 데이터는 p2001파티션에 저장되어 있다. 옵티마이저는 이 쿼리를 처리하기 위해서 p2001 파티션만 접근하도록 실행 계획을 수립한다. 쿼리를 수행하기 위해 접근 해야할 테이블만 골라내는 과정을 파티션 프루닝(Partition Pruning)이라고 한다.
-- Partition Table 생성
drop table salaries_part1;
CREATE TABLE salaries_part1 (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
)
partition by range columns(from_date)
(
partition p1985 values less than ('1986-01-01'),
partition p1986 values less than ('1987-01-01'),
partition p1987 values less than ('1988-01-01'),
partition p1988 values less than ('1989-01-01'),
partition p1989 values less than ('1990-01-01'),
partition p1990 values less than ('1991-01-01'),
partition p1991 values less than ('1992-01-01'),
partition p1992 values less than ('1993-01-01'),
partition p1993 values less than ('1994-01-01'),
partition p1994 values less than ('1995-01-01'),
partition p1995 values less than ('1996-01-01'),
partition p1996 values less than ('1997-01-01'),
partition p1997 values less than ('1998-01-01'),
partition p1998 values less than ('1999-01-01'),
partition p1999 values less than ('2000-01-01'),
partition p2000 values less than ('2001-01-01'),
partition p2001 values less than ('2002-01-01'),
partition p2002 values less than ('2003-01-01'),
partition p9999 values less than (MAXVALUE)
);
-- DDL 확인
show create table salaries_part1;
-- 데이터 삽입
insert into salaries_part1 select * from salaries;
commit;
-- 건수 확인
select count(*) cnt from salaries
union all
select count(*) cnt from salaries_part1;
explain
select * from salaries_part1
where from_date between '2001-01-01' and '2001-12-31'
TO_DAYS 함수는 입력된 날짜 값의 포맷이 잘못돼 있다면 NULL을 반환할 수도 있다. 이렇게 MySQL의 파티션 키가 TO_DAYS()와 같이 NULL을 반환할 수 있는 함수를 사용할 때는 쿼리의 실행 계획에서 partitions 컬럼의 테이블의 첫 번째 파티션에 포함되기도 한다. range 파티션을 사용하는 테이블에서 NULL은 항상 첫 번째 파티션에 저장되기 때문에 실행 계획의 partitions 컬럼에 첫 번째 파티션도 함께 포함된다. 하지만 실제 필요한 파티션과 테이블의 첫 번째 파티션이 함께 partitions 컬럼에 표시된다 하더라도 성능 이슈는 없다.
* 파티션 프루닝이 가능한 함수 YEAR, TO_DAYS
데이터 컬럼을 조작해야 한다면 데이터를 정수형으로 반환하고 파티션 프루닝이 가능한 year함수와 to_days 함수만을 사용해야 한다.
[파티션 : YEAR]
CREATE TABLE salaries_party (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
PRIMARY KEY (emp_no, from_date)
)
partition by range (year(from_date))
(
partition p1985 values less than (1986),
partition p1986 values less than (1987),
partition p1987 values less than (1988),
partition p1988 values less than (1989),
partition p1989 values less than (1990),
partition p1990 values less than (1991),
partition p1991 values less than (1992),
partition p1992 values less than (1993),
partition p1993 values less than (1994),
partition p1994 values less than (1995),
partition p1995 values less than (1996),
partition p1996 values less than (1997),
partition p1997 values less than (1998),
partition p1998 values less than (1999),
partition p1999 values less than (2000),
partition p2000 values less than (2001),
partition p2001 values less than (2002),
partition p2002 values less than (2003),
partition p9999 values less than (MAXVALUE)
);
insert into salaries_party select * from salaries;
commit;
explain
select * from salaries_party
where from_date between '2001-01-01' and '2001-12-31';
[파티션 : TO_DAYS]
CREATE TABLE salaries_partd (
emp_no INT NOT NULL,
salary INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
KEY (emp_no),
PRIMARY KEY (emp_no, from_date)
)
partition by range (to_days(from_date))
(
partition p1985 values less than (to_days('1986-01-01')),
partition p1986 values less than (to_days('1987-01-01')),
partition p1987 values less than (to_days('1988-01-01')),
partition p1988 values less than (to_days('1989-01-01')),
partition p1989 values less than (to_days('1990-01-01')),
partition p1990 values less than (to_days('1991-01-01')),
partition p1991 values less than (to_days('1992-01-01')),
partition p1992 values less than (to_days('1993-01-01')),
partition p1993 values less than (to_days('1994-01-01')),
partition p1994 values less than (to_days('1995-01-01')),
partition p1995 values less than (to_days('1996-01-01')),
partition p1996 values less than (to_days('1997-01-01')),
partition p1997 values less than (to_days('1998-01-01')),
partition p1998 values less than (to_days('1999-01-01')),
partition p1999 values less than (to_days('2000-01-01')),
partition p2000 values less than (to_days('2001-01-01')),
partition p2001 values less than (to_days('2002-01-01')),
partition p2002 values less than (to_days('2003-01-01')),
partition p9999 values less than (MAXVALUE)
);
show create table salaries_partd;
CREATE TABLE `salaries_partd` (
`emp_no` int NOT NULL,
`salary` int NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`from_date`))
(PARTITION p1985 VALUES LESS THAN (725372) ENGINE = InnoDB,
PARTITION p1986 VALUES LESS THAN (725737) ENGINE = InnoDB,
PARTITION p1987 VALUES LESS THAN (726102) ENGINE = InnoDB,
PARTITION p1988 VALUES LESS THAN (726468) ENGINE = InnoDB,
PARTITION p1989 VALUES LESS THAN (726833) ENGINE = InnoDB,
PARTITION p1990 VALUES LESS THAN (727198) ENGINE = InnoDB,
PARTITION p1991 VALUES LESS THAN (727563) ENGINE = InnoDB,
PARTITION p1992 VALUES LESS THAN (727929) ENGINE = InnoDB,
PARTITION p1993 VALUES LESS THAN (728294) ENGINE = InnoDB,
PARTITION p1994 VALUES LESS THAN (728659) ENGINE = InnoDB,
PARTITION p1995 VALUES LESS THAN (729024) ENGINE = InnoDB,
PARTITION p1996 VALUES LESS THAN (729390) ENGINE = InnoDB,
PARTITION p1997 VALUES LESS THAN (729755) ENGINE = InnoDB,
PARTITION p1998 VALUES LESS THAN (730120) ENGINE = InnoDB,
PARTITION p1999 VALUES LESS THAN (730485) ENGINE = InnoDB,
PARTITION p2000 VALUES LESS THAN (730851) ENGINE = InnoDB,
PARTITION p2001 VALUES LESS THAN (731216) ENGINE = InnoDB,
PARTITION p2002 VALUES LESS THAN (731581) ENGINE = InnoDB,
PARTITION p9999 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
insert into salaries_partd select * from salaries;
commit;
explain
select * from salaries_partd
where from_date between '2001-01-01' and '2001-12-31';
-- p1985에 NULL 데이터 없다.
select * from salaries_partd partition(p1985) where from_date is null ;
-- 테이블 salaries_partd의 총 건수 : 2844047
select count(*) from salaries_partd ;
-- partition p2001 건수 : 247652
select count(*) from salaries_partd partition(p2001);
-- 247652 ÷ 2844047 * 100 = 8.7%
select (select count(*) from salaries_partd partition(p2001))/(select count(*) from salaries_partd);
