Let's have a cup of coffee.

LOAD DATA(LOCAL) INFILE

​

​

1. 데이터 파일의 값과 테이블의 컬럼의 개수가 동일한 경우

​

desc employees;

LOAD DATA INFILE '/tmp/employees.csv'
IGNORE INTO TABLE employees
FIELDS
TREMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES
TERMINATED BY '\n'
STARTING BY ''
(emp_no, birth_date, first_name, last_name, gender, hire_date);

​

IGNORE : Primary Key나 Unique Key에 의해 중복 에러가 발생해도 해당 레코드는 무시하고 계속 작업을 진행

REPLACE : 저장하려는 레코드가 중복된 레코드면 UPDATE를 실행

​

/tmp/employees.csv

-------------------------------------------------------------------------------------------------------------------------------

10001,"1953-09-02","Georgi","Facello","M","1986-06-26"

10002,"1964-06-02","Bezalel","Simmel","F","1985-11-21"

10003,"1959-12-03","Parto","Bamford","M","1986-08-28"

10004,"1954-05-01","Chirstian","Koblick","M","1986-12-01"

10005,"1955-01-21","Kyoichi","Maliniak","M","1989-09-12"

10006,"1953-04-20","Anneke","Preusig","F","1989-06-02"

-------------------------------------------------------------------------------------------------------------------------------

​

LOAD DATA INFILE 문장에 명시되는 데이터 파일은 항상 MySQL 서버가 기동 중인 장비의 디렉터리에 존재해야 한다. 하지만 INFILE 옵션 앞에 LOCAL이라는 키워드를 추가하면 MySQL서버가 아니라 클라이언트 컴퓨터의 디스크에 있는 데이터 파일을 사용할 수도 있다. 또한, MySQL 서버나 클라이언트와 무관한 서버에 있는 데이터 파일을 URL 형태로 명시할 수도 있다.

​

MySQL 5.1.7부터는 mysqlimport 유틸리티에 "--use--threads" 옵션을 사용하면 병렬로 LOAD DATA INFILE 명령을 실행하기 때문에 파일이 많은 경우 빠르게 적재할 수 있다. LOAD DATA INFILE 명령이나 mysqlimport 유틸리티 모두 입력 파일은 CSV 포맷과 같이 구분자로 구분된 데이터 파일어어야 한다. mysqldump 유틸리티를 이용해 덤프받은 파일은 csv포맷의 파일이 아니라 사용할 수 없다.

​

​

​

2.데이터 파일의 값의 개수가 테이블의 컬럼 수보다 적은 경우

​

birth_date 컬럼에 값이 없을 때 현재 시간을 저장하기 위해 "SET birth_date=now()"절을 추가한다. NULL이 허용되는 컬럼이라면 SET절에 명시하지 않아도 된다.

​

LOAD DATA INFILE '/tmp/employees.csv'
IGNORE INTO TABLE employees
FIELDS
TREMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES
TERMINATED BY '\n'
STARTING BY ''
(emp_no, first_name, last_name, gender, hire_date)
SET birth_date=NOW();

​

LOAD DATA INFILE 명령의 입력 데이터는 하나의 라인에 값이 5개만 존재해야 한다.

​

/tmp/employees.csv

-------------------------------------------------------------------------------------------------------------------------------

10001,"Georgi,Facello","M","1986-06-26"

10002,"Bezalel,Simmel","F","1985-11-21"

10003,"Parto,Bamford","M","1986-08-28"

10004,"Chirstian","Koblick","M","1986-12-01"

10005,"Kyoichi","Maliniak","M","1989-09-12"

10006,"Anneke","Preusig","F","1989-06-02"

-------------------------------------------------------------------------------------------------------------------------------

​

​

​

3. 데이터 파일의 값의 개수가 테이블의 컬럼 수보다 많은 경우

​

데이터 파일의 두 번째 값인 "사원전화번호"는 employees 테이블에 저장할 필요가 없어서 @emp_tel_no 변수에 담아서 사용하지 않고 버린다.

​

LOAD DATA INFILE '/tmp/employees.csv'
IGNORE INTO TABLE employees
FIELDS
TREMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES
TERMINATED BY '\n'
STARTING BY ''
(emp_no, @emp_tel_no, birth_date, first_name, last_name, gender, hire_date);

​

/tmp/employees.csv

-------------------------------------------------------------------------------------------------------------------------------

10001,"010-1234-1234","1953-09-02","Georgi","Facello","M","1986-06-26"

10002,"010-1234-1234","1964-06-02","Bezalel","Simmel","F","1985-11-21"

10003,"010-1234-1234","1959-12-03","Parto","Bamford","M","1986-08-28"

10004,"010-1234-1234","1954-05-01","Chirstian","Koblick","M","1986-12-01"

10005,"010-1234-1234","1955-01-21","Kyoichi","Maliniak","M","1989-09-12"

10006,"010-1234-1234","1953-04-20","Anneke","Preusig","F","1989-06-02"

-------------------------------------------------------------------------------------------------------------------------------

​

​

​

4. 데이터 파일의 값을 연산해서 테이블의 컬럼에 저장하는 경우

​

데이터 파일에 있는 값을 연산을 거친 후 테이블에 저장할 때 사용자 변수에 값을 담아 SET절을 이용해 연산 후에 지정한 컬럼에 값을 저장한다. 아래 예제는 @middle_name 과 @last_name 으로 컬럼에 읽어 들이 후 SET절에서 두 개 변수의 값을 문자열 결합 함수를 이용해 last_name 에 저장한다.

​

LOAD DATA INFILE '/tmp/employees.csv'
IGNORE INTO TABLE employees
FIELDS
TREMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES
TERMINATED BY '\n'
STARTING BY ''
(emp_no, birth_date, first_name,@middle_name, @last_name, gender, hire_date)
SET last_name=concat(@middle_name, ' ', @last_name);

​

/tmp/employees.csv

-------------------------------------------------------------------------------------------------------------------------------

10001,"1953-09-02","Georgi","mid","Facello","M","1986-06-26"

10002,"1964-06-02","Bezalel","mid","Simmel","F","1985-11-21"

10003,"1959-12-03","Parto","mid","Bamford","M","1986-08-28"

10004,"1954-05-01","Chirstian","mid","Koblick","M","1986-12-01"

10005,"1955-01-21","Kyoichi","mid","Maliniak","M","1989-09-12"

10006,"1953-04-20","Anneke","mid","Preusig","F","1989-06-02"

-------------------------------------------------------------------------------------------------------------------------------

​

​

5. 데이터 파일이 MySQL 서버가 아닌 다른 컴퓨터에 있을 경우

​

적재하려는 데이터 파일이 MySQL 서버가 아니라 다른 원격 컴퓨터에 있을 때는 LOCAL 키워드를 추가한다. LOAD DATA LOCAL INFILE 문장이 실행되면 MySQL 서버는 적재할 데이터 파일이 MySQL 서버의 디스크가 아니라 클라이언트의 디스크에 있다고 판단한다. 그리고 MySQL 클라이언트나 JDBC 드라이버는 그 데이터 파일을 찾아서 MySQL 서버로 업로드한 후 파일을 테이블에 적재한다.

​

자바 프로그램에서 JDBC 드라이버를 사용해 LOAD DATA LOCAL INFILE .. 명령을 사용할 때는 파일 경로에 URL을 사용할 수 있다. 파일 경로에 URL을 사용할 수 있다는 것은 데이터 파일이 MySQL 서버와 클라이언트 컴퓨터 이외의 호스트에 위치할 수도 있다는 의미다.

​

HTTP 프로토콜을 이용해 제3의 서버에서 데이터 파일을 가져와 다시 원격지의 MySQL서버에 적재할 수 있다면 작업을 상당히 간단히 만들 수 있다. 아래 예제는 로컬에서 타겟 MySQL 접속 후 LOAD DATA 명령을 실행하는 것이다.

​

Connection conn = DriverManager.getConnection(
"jdbc:mysql://mysql_servier_ip:3306/employees?allowUrlInLocalInfile=true",userid, userpassword);
Statement stmt = conn.createStatement();
stmt.executeUpdate("LOAD DATA LOCAL INFILE 'http://another-server-url/employees.csv' "
+ "IGNORE "
+ "INTO TABLE temp_employees "
+ "FIELDS "
+ " TERMINATED BY ',' "
+ " OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' "
+ "LINES "
+ " TERMINATED BY '\r\n' "
+ " STARTING BY '' "
+ " (emp_no, birth_date, first_name, last_name, gender, hire_date) ");

​

LOAD DATA INFILE 명령의 입력으로 URL을 사용하려면 JDBC 커넥션을 생성할 때 allowUrlInLocaInfile 옵션을 ture로 설정해야 한다. 보안상의 이유로 기본값이 false로 설정돼어 있어서 LOAD DATA LOCAL INFILE 'URL' ... 명령을 사용할 수 없다.

​

​

6. LOAD DATA INFILE의 성능 향상

​

LOAD DATA INFILE 명령으로 대량의 데이터를 적재할 때 더욱 빠른 처리를 위해서는 다음 옵션들을 함께 사용하는 것이 좋다. AUTO-COMMIT 모드와 FOREIGN KEY는 InnoDB 테이블에 해당하며 유니크 인덱스는 모든 스토리지 엔진의 테이블에 해당하는 내용이다.

​

1) AUTO-COMMIT

InnoDB 스토리지 엔진에서는 트랜잭션을 사용할 수 있다. AUTO-COMMIT이 활성화(true)된 상태에서는 레코드 단위로 INSERT될 때마다 COMMIT을 실행하는데, 이 작업은 매번 레코드 단위로 로그 파일의 디스크 동기화(FLUSH) 작업을 발생시킨다. 단순히 쿼리 문장을 하나씩 실행하는 경우에는 크게 영향이 없을 수도 있지만 대량으로 INSERT 문장을 실행하는 경우에는 디스크 I/O에 상당히 많은 부하를 일으킨다.

​

set autocommit = 0;

LOAD DATA ...

COMMIT;

set autocommit = 1;

show variables like 'autocommit'

show variables like 'autocommit'

​

2) UNIQUE INDEX

만약 대량으로 데이터를 적재하는 대상 테이블에 UNIQUE 인덱스가 있다면 매번 레코드 단위로 중복 체크가 발생한다. 중복 체크라는 것은 결국 INSERT를 수행하기 전에 SELECT를 한 번씩 더 실행해야 한다는 것을 의미한다. unique_checks 설정을 변경해 중복 체크를 건너 띄도록 설정할 수 있다. 유니크 인덱스의 중복 체크 작업을 비활성화하면 중복 체크를 위한 SELECT를 생략할 수 있을 뿐더러 유니크 인덱스에 대해서도 InnoDB의 인서트 버퍼(Insert buffer)를 사용할 수 있기 때문에 상당히 많은 디스크 I/O를 줄일 수 있다.

​

set unique_checks = 0;

LOAD DATA ..

set unique_checks = 1;

show variables like 'unique_checks';

show variables like 'unique_checks';

​

3) FOREIGN KEY

데이터를 적재하는 테이블에 FOREIGN KEY가 있다면 매번 레코드의 INSERT 시점마다 FOREIGN KEY값이 존재하는지 여부를 확인해야 한다. 이 작업 또한 상당한 디스크 I/O를 유발한다. foreign_key_checks 설정을 변경하면 FOREIGN KEY의 무결성 체크를 수행하지 않고 바로 적재할 수 있다. 물론 이 경우 또한 FOREIGN KEY의 무결성을 해치는 데이터가 없다는 것을 먼저 확인해야 한다.

​

set foreign_key_checks=0;

LOAD DATA..

set foreign_key_checks=1;

show variables like 'foreign_key_checks';

show variables like 'foreign_key_checks';

​

​

7. LOAD DATA 명령의 문자집합 처리

​

LOAD DATA 명령을 이용해 파일을 테이블에 적재할 때는 특별히 문자 셋에 주의해야 한다. 먼저 LOAD DATA 명령으로 적재하려는 데이터 파일이 어떤 문자집합으로 저장됐는지 알고 있어야 한다. 그렇지 않으면 한글이나 일본어와 같이 아시아권 언어는 깨진 상태로 테이블에 적재될 수도 있다. 일반적으로 한글 윈도우에서 별다른 설정 없이 저장했다면 MS949 문자집합인 경우가 많다. 그 밖에 별도로 문자집합을 지정했다면 UTF-8일 가능성이 높다.

​

LAOD DATA 명령에 별도로 문자집합을 지정하지 않는 경우라면 적재하려는 데이터 파일의 문자집합은 MySQL클라이언트의 문자집합과 일치해야 한다. 우선 MySQL 커넥션의 문자집합을 다음과 같이 SHOW명령으로 확인해 보자.

​

show variables like 'character_set_%';

적재하려는 데이터 파일의 문자집합이 MS949나 EUC-KR이라면 클라이언트와 커넥션의 문자집합을 euckr로 변환해야 한다.

​

set names 'euckr';

show variables like 'character_set_%';

​

LOAD DATA 명령에서 데이터 파일의 문자집합을 명시할 수 있다.

​

LOAD DATA INFILE '/tmp/employees.csv'
IGNORE
INTO TABLE employees CHARACTER SET 'euckr'
FILEDS
TREMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES
TERMINATED BY '\n'
STARTING BY ''
(emp_no, birth_date, first_name, last_name, gender, hire_date);

​

데이터 파일의 문자집합이 UTF-8이라면 EUC-KR 대신 utf8을 사용해 커넥션이나 클라이언트의 문자집합을 변경하면 된다.

​

set names 'utf8';

show variables like 'character_set_%';

​

LAOD DATA 명령에서 파일의 문자집합을 utf8로 변경해도 된다.

​

LOAD DATA INFILE '/tmp/employees.csv'
IGNORE
INTO TABLE employees CHARACTER SET 'utf8'
FILEDS
TREMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES
TERMINATED BY '\n'
STARTING BY ''
(emp_no, birth_date, first_name, last_name, gender, hire_date);

INSERT INTO .. ON DUPLICATE KEY UPDATE..

​

테이블에 중복된 레코드가 존재할 때 기존 레코드의 컬럼값을 참조해서 업데이트하는 기능이다.

drop table tb_duplicate;

create table tb_duplicate(
col1 int not null,
col2 varchar(4) not null,
col3 int not null,
primary key (col1) );

insert into tb_duplicate values (1,'AA',1), (2,'AA',2), (3,'BB',1), (4,'BB',2) ;

​

​

유니크 키(PK and Unique Key 둘다 체크)에 중복값 존재시 주어진 값으로 update 한다.

​

insert into tb_duplicate (col1,col2,col3) values(4,'CC',1) 
on duplicate key update col2 = 'CC', col3 = 1;

select * from tb_duplicate;

select col2, count(*)
from tb_duplicate
group by col2;

​

UPDATE절에서 VALUES(col3)를 사용하면 INSERT 절에서 col3 컬럼에 저장하려고 시도했던 값을 반환한다. VALUES() 함수는 하나의 인자를 받는다.

​

create table tb_duplicate2(
col2 varchar(4) not null,
col3 int not null,
primary key (col2));

insert into tb_duplicate2 values('AA',1);

insert into tb_duplicate2 (col2, col3)
select col2, count(*)
from tb_duplicate
group by col2
on duplicate key update
col3 = values(col3);

select * from tb_duplicate2;

​

중복키 업데이트 인자가 두개 일 때 (col3 = values(col3), col4 = values(col4))

​

create table tb_duplicate3(
col2 varchar(4) not null,
col3 int not null,
col4 int not null,
primary key (col2));

insert into tb_duplicate3 values('AA',1,1);

insert into tb_duplicate3 (col2, col3, col4)
select col2, count(*), 1
from tb_duplicate
group by col2
on duplicate key update
col3 = values(col3), col4 = values(col4);

select * from tb_duplicate3;

AUTO_INCREMENT

​

create table tb_autoincrement(
member_id int not null auto_increment,
member_name varchar(30) not null,
primary key(member_id)
);

​

AUTO_INCREMENT의 현재 값을 저장하려면 AUTO_INCREMENT속성을 생략하거나 values에 해당 속성의 값에 NULL이나 0을 써주면 된다.

​

insert into tb_autoincrement (member_name) values ('Georgi Fellona');

select * from tb_autoincrement ;

select LAST_INSERT_ID();

​

강제 저장한 값이 AUTO_INCREMENT의 현재 값보다 작을 때는 AUTO_INCREMENT의 현재 값이 변하지 않는다. 강제 저장한 값이 AUTO_INCREMENT의 현재 값보다 클 때는 저장된 값에 1을 더한 값이 AUTO_INCREMENT의 다음 값으로 변경된다.

insert into tb_autoincrement (member_id,member_name) values (5,'Georgi Fellona');

select * from tb_autoincrement ;

select LAST_INSERT_ID();

insert into tb_autoincrement (member_id,member_name) values (null,'Georgi Fellona');

select * from tb_autoincrement ;

select LAST_INSERT_ID();

​

※ 테이블에서 AUTO_INCREMENT 컬럼을 사용할 때는 반드시 다음 규칙을 지켜야 한다.

1. AUTO_INCREMENT 속성을 가진 컬럼은 반드시 PRIMARY KEY나 UNIQUE KEY의 일부로 정의돼야 한다.

2. AUTO_INCREMENT 속성을 가진 컬럼 하나로 프라이머리 키를 생성할 때는 아무런 제약이 없다.

3. 여러개의 컬럼으로 프라이머리 키를 생성할 때

​

create table tb_auto1(
col1 char
,col2 int auto_increment
,primary key(col1, col2)
) ENGINE = INNODB;

SQL Error [1075] [42000]: Incorrect table definition; there can be only one auto column and it must be defined as a key

InnoDB에서는 autoincrement 속성이 젤 앞에 오거나 autoincrement 속성이 젤 앞에 오도록 unique key를 생성해야 한다.

​

create table tb_auto1(
 col1 char
,col2 int auto_increment
,primary key(col1, col2)
) ENGINE = MyISAM;

insert into tb_auto1 values('A', NULL),('A', NULL),('B', NULL),('B', NULL);
select * from tb_auto1;

MyISAM에서 autoincrement 속성이 두번째 오는 PK를 생성하여서 자동증가 값의 규칙이 col1을 따라서 증가하는 것을 알 수 있다.

​​

create table tb_auto2(
col1 char
,col2 int auto_increment
,primary key(col1, col2)
,unique key(col2)
) ENGINE = INNODB;

insert into tb_auto2 values('A', NULL),('A', NULL),('B', NULL),('B', NULL);
select * from tb_auto2;

create table tb_auto3(
col1 char
,col2 int auto_increment
,col3 char
,primary key(col1, col2)
,unique key(col2,col3)
) ENGINE = INNODB;

insert into tb_auto3 values('A', NULL, 'A'),('A', NULL, 'A'),('B', NULL, 'B'),('B', NULL, 'B');
select * from tb_auto3;

​​

auto_increment_offset은 초기값을 정의하며 auto_increment_increment는 증가값을 설정하는 시스템변수

show variables like 'auto_increment_%';

​※ innodb_autoinc_lock_mode

show variables like 'innodb_autoinc_lock_mode' -- 현재 설정된 값 : 2

​

1. innodb_autoinc_lock_mode = 0

MySQL5.0 버전의 AUTO_INCREMENT와 마찬가지로 항상 Autoincrement 잠금을 걸고 한 번에 1씩만 증가된다.

​

2. innodb_autoinc_lock_mode = 1

단순히 레코드 한 건씩 INSERT를 하는 쿼리에서는 Autoincrement 잠금을 사용하지 않고 뮤텍스를 이용해 더 가볍고 빠르게 처리한다. 하지만 하나의 INSERT 문장으로 여러 레코드를 INSERT 하거나 LOAD DATA 명령으로 INSERT하는 쿼리에서는 AutoIncrement 잠금을 사용한다.

​

3. innodb_autoinc_lock_mode = 2

LOAD DATA와 같이 벌크로 INSERT하는 경우에 AUTO_INCREMENT 값을 적당히 미리 할당받아서 처리할 수 있으므로 가장 빠른 방식이다. 하지만 쿼리 기반의 복제(SBR, Statement Based Replication)를 사용하는 MySQL에서는 마스터 MySQL과 슬레이브 MySQL의 AUTO_INCREMENT가 동기화되지 못하는 문제점이 발생할 수 있으므로 주의해야 한다. 복제를 사용하지 않거나 레코드 기반의 복제(RBR, Record Based Replication)를 사용하는 MySQL 서버에서 사용할 수 있다. 또한 innodb_autoinc_lock_mode를 2로 설정하면 AUTO_INCREMENT 값이 연속적이지 않고 띄엄띄엄 반환될 수도 있다.

​AUTO_INCREMENT 컬럼에 0을 INSERT 하려면 sql_mode라는 시스템 변수에 "NO_AUTO_VALUE_ON_ZERO"값을 추가하면 된다.-> 설정 못함​

show variables like '%sql_mode%';

set sql_mode = 'NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES,IGNORE_SPACE,PIPES_AS_CONCAT,ANSI_QUOTES';

​

SQL Error [1231] [42000]: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_VALUE_ON_ZERO'

MySQL8.0.20 에선 설정하지 못한다.

​

독립서브쿼리(Self-Contained subquery)

다음 예제 쿼리와 같이 외부쿼리의 컬럼을 사용하지 않고 서브 쿼리에서 정의된 컬럼만 참조한다. 외부의 쿼리와 상관없이 항상 같은 결과를 반환하므로 외부 쿼리보다 먼저 실행되어 외부 쿼리의 검색을 위한 상수로 사용되는 것이 일반적이다. 독립 서브 쿼리가 포함된 비교 조건은 범위 제한 조건으로 사용될 수 있다.

​

select de.dept_no, de.emp_no
  from dept_emp de
 where de.emp_no=(select e.emp_no
                    from employees e
                   where e.first_name= 'Georgi' and e.last_name = 'Facello'
                   limit 1);

​

​

1. 서브 쿼리의 제약 사항

​

1) 서브 쿼리는 대부분의 문장에서 사용할 수 있지만 LIMIT 절과 LOAD DATA INFILE의 파일명에는 사용할 수 없다. 서브 쿼리를 IN 연산자와 함께 사용할 때에는 효율적으로 처리되지 못한다.​

​

select *
  from employees e
 where e.emp_no in (select de.emp_no
                      from dept_emp de
                     where de.from_date between '2000-01-01' and '2011-12-30');

MySQL8.0에서는 효율적으로 처리된다.

​

2) IN/ALL/ANY/SOME 서브 쿼리에는 LIMIT를 사용할 수 없다.

select count(*) -- 10,485
  from employees e
 where exists (select 1
                 from dept_emp de
                where de.emp_no = e.emp_no
                  and de.from_date between '2000-01-01' and '2011-12-30');

select count(*) -- 10,485
  from employees e
 where exists (select 1
                 from dept_emp de
                where de.emp_no = e.emp_no
                  and de.from_date between '2000-01-01' and '2011-12-30'
                limit 1); /* LIMIT을 사용해도 결과는 동일하다. */

select *
  from employees e
 where e.emp_no in (select de.emp_no
                      from dept_emp de
                     where de.from_date between '2000-01-01' and '2011-12-30'
                     limit 10);
SQL Error [1235] [42000]: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
MySQL8.0에서는 IN/ALL/ANY/SOME 서브쿼리에서 LIMIT을 지원하지 않는다.

​

​

3) 서브 쿼리를 이용해 하나의 테이블에 대해 읽고 쓰기를 동시에 할 수 없다.

​

UPDATE departments2
SET dept_name=(SELECT concat(dept_name,'2') FROM departments2 WHERE dept_no='d009')
WHERE dept_no='d001';
SQL Error [1093] [HY000]: You can't specify target table 'departments2' for update in FROM clause

​

서브쿼리를 이용해 departments 테이블을 읽고, 조회된 값을 다시 departments 테이블에 업데이트하는 쿼리다. 실제 읽는 레코드와 변경하는 레코드는 다른 레코드이지만 MySQL의 현재 모든 버전에서는 허용하지 않는다.

하지만 departments 테이블을 읽는 서브 쿼리의 결과를 임시 테이블로 저장하도록 쿼리를 변경하여 MySQL 서버는 임시 테이블을 읽어서 departments 테이블을 변경하는 것으로 인식하기 때문에 문제없이 처리된다. 하지만 이러한 방식은 별도의 임시 테이블을 필요로하기에 피할 수 없는 경우에만 사용할 것을 권장한다. 또한 이러한 쿼리는 데드락의 원인이 되기도 한다.

​

UPDATE departments2
SET dept_name=
(SELECT dept_name FROM (SELECT concat(dept_name,'2') FROM departments2 WHERE dept_no='d009') temp )
WHERE dept_no='d001';

​

2. SELECT 절에 사용된 서브 쿼리

스칼라 서브 쿼리는 항상 컬럼과 레코드가 하나인 결과를 반환해야 한다. 그 값이 NULL이든 아니든 상관없이 하나의 컬럼으로 레코드 1건이 존재해야한다.

​

select emp_no, (select dept_name from departments where dept_name='Sales1')
  from dept_emp
 limit 10;

select emp_no, (select dept_name from departments)
  from dept_emp limit 10;
SQL Error [1242] [21000]: Subquery returns more than 1 row

select emp_no, (select dept_no, dept_name from departments where dept_name = 'Sales1')
  from dept_emp limit 10;
SQL Error [1241] [21000]: Operand should contain 1 column(s)

​

​

3. 서브 쿼리를 조인형태로 변환

​

바깥 쪽 테이블(dept)과 서브쿼리테이블(emp)의 관계가 1:M인 경우는 조인형태로 변환 시 결과의 건수가 늘어나기 때문에 GROUP BY와 같은 처리가 필요하다.

​

select d.deptno, d.dname, d.loc
  from dept d
 where exists (select 1 from emp e where e.deptno=d.deptno);

select d.deptno, d.dname, d.loc
  from dept d inner join emp e
    on d.deptno = e.deptno
 group by d.deptno, d.dname, d.loc;

create index ix_deptno on dept1(deptno);

​

select d.*

from dept1 d

where deptno not in (select deptno from emp1);

이경우는 양쪽 컬럼의 데이터에 NULL이 존재하여 결과가 없다. 서브쿼리에 deptno is not null 조건을 추가해야 한다.

select d.*
  from dept d
 where deptno not in (select deptno from emp);

d.deptno가 PK이어서 최적화 되어 anti join 형태로 풀린다.

​

select d.*
  from dept1 d
 where deptno not in (select deptno from emp);

d.deptno가 NULL값을 가지고 있어서 FULL TABLE 스캔을 통해 서브쿼리에서 만족하는 조건이 1건도 없을때 까지 필터링 한다.

​

​

select d.*
  from dept1 d
 where deptno not in (select deptno from emp)
  and d.deptno is not null;

1. GROUP BY

​​

1) FULL GOUPR-BY

GROUP BY에 명시되지 않은 컬럼은 집합 함수로 감싸서만 사용할 수 있게 하는 것을 "FULL GROUP-BY"라고 한다. MySQL에서는 "FULL GROUP-BY"를 문법적으로 강제하는 방법도 제공한다. sql_mode 시스템 변수에 "ONLY_FULL_GROUP_BY"값을 설정하면 "FULL GOUPR-BY"만 사용할 수 있다.

​

​show variables like 'sql_mode';

set sql_mode = 'STRICT_TRANS_TABLES';

set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

​

​select first_name from employees group by gender;

SQL Error [1055] [42000]: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.employees.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

​

​

2) GROUP BY .. ORDER BY NULL

​MySQL의 GROPU BY는 그룹핑 컬럼순서대로 정렬까지 수행한다.

MySQL에서 불필요한 정렬 작업을 하지 않게 하려면 GROUP BY를 수행할 때 "ORDER BY NULL"이라는 키워드를 사용하면 된다. GROUP BY절만 사용했는데 Extra 컬럼에 "Using filesort"가 표시된다.

하지만, MySQL8.0 에서는 "Using filesort"가 표시되지 않는다.

​

explain select from_date from salaries group by from_date;

explain select from_date from salaries group by from_date order by null;

select deptno, job, sum(sal)
  from emp
 group by deptno, job
  with rollup;

​

​

4) 레코드를 컬럼으로 변환해서 조회

​

​​[1] 레코드를 컬럼으로 변환 (pivot)

​

select dept_no, count(*) as emp_cnt from dept_emp group by dept_no;

select sum(case when dept_no='d001' then emp_count else 0 end) as count_d001​
     , sum(case when dept_no='d002' then emp_count else 0 end) as count_d002
     , sum(case when dept_no='d003' then emp_count else 0 end) as count_d003
     , sum(case when dept_no='d004' then emp_count else 0 end) as count_d004
     , sum(case when dept_no='d005' then emp_count else 0 end) as count_d005
     , sum(case when dept_no='d006' then emp_count else 0 end) as count_d006
     , sum(case when dept_no='d007' then emp_count else 0 end) as count_d007
     , sum(case when dept_no='d008' then emp_count else 0 end) as count_d008
     , sum(case when dept_no='d009' then emp_count else 0 end) as count_d009
  from (select dept_no, count(*) emp_count 
          from dept_emp 
         group by dept_no) tb_derived;

​

[2] 하나의 컬럼을 여러 컬럼으로 분리

​

[부서별로 입사년도 별 사원수를 구하는 쿼리]

select de.dept_no
     , sum(case when e.hire_date between '1980-01-01' and '1989-12-31' then 1 else 0 end) as cnt_1980
     , sum(case when e.hire_date between '1990-01-01' and '1999-12-31' then 1 else 0 end) as cnt_1990
     , sum(case when e.hire_date between '2000-01-01' and '2009-12-31' then 1 else 0 end) as cnt_2000
     , count(*) as cnt_total
 from dept_emp de, employees e
where de.emp_no = e.emp_no
group by de.dept_no;

​

​

​

2. ORDER BY

​InnoDB의 경우 Primary Key로 클러스터링 되어 있어서 풀 테이블 스캔의 경우 PK의 순서대로 레코드를 가져온다. 하지만 임시테이블을 사용하거나 MyISAM 테이블은 테이블에 저장된 순서대로 가져온다.

ORDER BY에서 인덱스를 사용하지 못하고 퀵 소트 알고리즘으로 정렬을 수행했다는 의미로 Extra 컬럼에 "Using Filesort"라고 표시된다. 정렬 대상이 많은 경우에는 여러 부분으로 나눠서 정렬하는데 정렬된 결과를 임시로 디스크나 메모리에 저장해둔다.

​

1) RAND

RAND함수로 발생하는 임의의 값을 각 레코드별로 부여하여 정렬을 수행한다. RAND를 이용하면 인덱스를 이용할 수 없다.

​

select * from emp order by rand();

​

​2) 쿼리를 나눠서 정렬

애플리케이션에서 정렬하려는 쿼리를 쪼개서 실행함으로써 인덱스를 이용하도록 할 수 있다.

​

create table tb_member(
region varchar(20),
name varchar(20),
primary key(name, region));

insert into tb_member values('경기','김길동');
insert into tb_member values('서울','홍길동');
insert into tb_member values('경기','강길동');

select * from tb_member order by name asc, region desc;

​

[애플리케이션을 이용하여 정렬을 인덱스 사용]

ResultSet rs1 = stmt.execute.Query("select name from tb_member​ group by name order by name asc");

​while(rs1.next()){

int currentName = rs1.getString("name");

ResultSet rs2 = stmt1.executeQuery ("select * from tb_member where name="+ currentName+"order by region desc");

while(rs2.next()){

//여기서 rs2의 레코드를 순서대로 출력하면 그 결과가 최종 정렬된 순서

System.out.println(rs2)

}

}

​

위의 경우는 정렬해야 할 레크도 건수가 너무 많아서 디스크를 이용해야 할 경우이거나 첫 번째 정렬 컬럼에 중복된 값이 많아서 두 번째 쿼리의 반복 실행 횟수가 적은 경우 사용할 때 유용하다.

​

​

3) 함수나 표현식을 이용한 정렬

연산 결과를 이용해 정렬​하는 것은 인덱스 사용을 할 수 없다. 아래에는 정렬을 인덱스를 사용할 수 없는 경우 인덱스를 사용하도록 하는 방법이다.

​

[1] 표현식의 결과 순서가 컬럼의 원본 값 순서와 동일할 때

order by 절에 표현식에 의해 변형된 값 자체가 변형 이전의 값과 순서가 동일하다면 변형되지 않은 컬럼을 그대로 사용하여 인덱스를 이용한 정렬을 사용하여야 한다.

select * from employees order by emp_no;
select * from employees order by emp_no + 10 ;

​

​

[2] 표현식의 정렬 순서가 컬럼의 원본 값과 다른 경우(연산의 결과가 컬럼의 값에만 의존적인 경우)

표현식의 연산 결과를 별도의 컬럼을 추가해서 레코드가 INSERT되거나 UPDATE될 때 해당 컬럼을 계속 업데이트하는 방식이다.

​

-- 회원의 나이에 30을 뺀 후 그 절대값으로 정렬
select * from tb_member order by abs(member_age-30);
create table tb_member(
member_id varchar(2) not null,
member_age smallint not null,
member_age_diff smallint not null,
...
index ix_agediff(member_age_diff));
select * from tb_member order by member_age_diff;

​

URL과 같이 긴 문자열에 프라이머리 키나 보조 인덱스를 만들어야 하는 경우도 있다. 하지만 MySQL에서 인덱스 키는 최대 756바이트 이상을 넘을 수 없다는 제약이 있으며, 이로 인해 URL과 같이 긴 문자열은 MD5 같은 메시지 다이제스트(Message Digest)알고리즘을 이용해 16바이트로 축소시켜 별도로 관리하는 방법을 사용하기도 한다. 테이블 원본 컬럼으로 부터 어떤 연산의 결과를 따로 저장하는 컬럼을 "추출 컬럼" 이라고도 한다.

JOIN

JOIN이 어떻게 인덱스를 사용하는지에 대해 각 쿼리 패턴별로 살펴보자.

​

1. 조인의 순서와 인덱스

INDEX RANGE SCAN의 동작 순서

[1] INDEX SEEK : 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.

[2] INDEX SCAN : 탐색된 위치부터 필요한 만큼 인덱스를 순서대로 읽는다.

[3] 2번에서 읽은 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽는다.

​

인덱스 풀 스캔이나 테이블 풀 스캔 작업은 인덱스 탐색(Index seek) 과정이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다. 인덱스 레인지 스캔 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔(INDEX SCAN) 과정은 부하가 작지만 특정 인덱스 키를 찾는 인덱스 탐색(INDEX SEEK) 과정이 상대적으로 부하가 높은 편이다.

조인 작업에서는 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업은 한 번만 수행하고, 그 이후부터는 스캔만 실행하면된다. 하지만 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다. 드라이빙 테이블과 드리븐 테이블이 1:1 조인이 되더라도 드리븐 테이블을 읽는 것이 훨씬 큰 부하를 차지하는 것이다. 그래서 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.

​

​

두 테이블의 emp_no 컬럼에 각각 INDEX가 있을 때와 없을때 조인 순서가 어떻게 되는지 살펴보자.

​

select *
  from employees e, dept_emp de
 where e.emp_no = de.emp_no;

select count(*), count(distinct emp_no) from employees ;-- 300024 300024
select count(*), count(distinct emp_no) from dept_emp ; -- 331603 300024

​

[1] 두 컬럼(emp_no)모두 각각 인덱스가 있는 경우

어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다. 이럴 때 옵티마이저가 통계 정보를 이용해 드라이빙 테이블을 선택하게 된다. 각 테이블의 통계 정보에 있는 레코드 건수에 때라 employees가 드라이빙 테이블이 될 수도 있고, dept_emp 테이블이 드라이빙 테이블로 선택될 수도 있다. 보통의 경우 어느 쪽 테이블이 드라이빙 테이블이 되든 옵티마이저가 선택하는 방법이 최적일 때가 많다.

​

[2] employees.emp_no 에만 인덱스가 있는 경우

dept_emp 테이블이 드리븐 테이블이라면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 스캔해서 조건(e.emp_no = de.emp_no)에 일치하는 레코드를 찾을 수 있다. 옵티마이저는 dept_emp 테이블을 드라이빙 테이블로 선택하고 employees 테이블을 드리븐 테이블로 선택하게 된다.

​

[3] dept_emp.emp_no 에만 인덱스가 있는 경우

employees 테이블의 반복된 풀 스캔을 막기 위해 employees 테이블을 드라이빙 테이블로 선택하고 dept_emp 테이블을 드리븐 테이블로 조인을 수행하도록 실행 계획을 수립한다.

​

[4] 두 컬럼(emp_no) 모두 인덱스가 없는 경우

어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블은 풀 스캔을 발생해야 하기 때문에 옵티마이저가 레코드 건수가 적은 테이블을 드리븐 테이블로 선택하게 된다. 또한 드리븐 테이블을 읽을 때 조인 버퍼가 사용되기 때문에 실행 계획의 Extra 컬럼에 "Using join buffer" 가 표시 된다. 조인버퍼 사용 시 드라이빙 테이블의 결과는 조인 버퍼에 담아 두고, 드리븐 테이블을 먼저 읽고 조인버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다.

​

드라이빙 테이블을 풀 테이블 스캔을 할 수 있어도 드리븐 테이블을 풀 테이블 스캔으로 접근하는 실행 계획은 옵티마이저가 거의 만들어 내지 않는다. 하지만 조인이 수행될 때 양쪽 테이블의 컬럼에 모두 인덱스가 없다면 드리븐 테이블을 풀 테이블 스캔할 수 밖에 없다.

​

​

2. JOIN 컬럼의 데이터 타입

조인 컬럼 간의 비교에서 각 컬럼의 데이터 타입이 일치하지 않으면 인덱스를 이용할 수 없을 수도 있다.

create table tb_a (id int, type int, primary key(col1);
create table tb_b (type char(1), description varchar(20), primary key(col1);

select *
  from tb_a a, tb_b b -- driving : a , driven : b
 where a.type = b.type ; -- b.type 컬럼을 가공(int)하여 index 사용 불가

​

인덱스 사용에 영향을 미치는 데이터 타입 불일치는 CHAR와 VARCHAR, INT와 BIGINT 그리고 SMALLINT, DATE와 DATETIME 사이에서는 발생하지 않는다.

​

[문제가 발생하는 비교 패턴 유형]

- CHAR와 INT타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우

- 같은 CHAR 타입이라도 문자집합이나 콜레이션이 다른 경우

- 같은 INT 타입이더라도 부호(Sign)가 있는지 여부가 다른 경우

​

사용자가 명시적으로 값을 변환할 경우와 MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 컬럼에는 "func"가 출력된다. 문자집합이 일치하지 않는 두 문자열 컬럼을 조인한다거나, 숫자 타입의 컬럼과 문자열 타입의 컬럼으로 조인할 때가 대표적인 예다. 가능하다면 MySQL 서버가 이런 변환을 하지 않도록 조인 컬럼의 타입은 일치시키는 편이 좋다.

​

[charset 다른 경우 테스트]

CREATE TABLE departments_utf8 (
dept_no CHAR(4) NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY (dept_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

insert into departments_utf8 select * from departments;
commit;

​

[CHARACTER SET 조회]

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
  FROM INFORMATION_SCHEMA.COLUMNS C
 WHERE C.TABLE_SCHEMA = 'EMPLOYEES'
   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 ;

​

​

3. OUTER JOIN의 주의사항

OUTER JOIN에서 OUTER로 조인되는 테이블의 컬럼에 대한 조건은 모두 ON절에 명시해야 한다. 그렇지 않고 OUTER 테이블의 컬럼이 WHERE절에 명시하면 옵티마이저가 INNER JOIN과 같은 방법으로 처리한다. ON절은 조인과정중에 처리하지만 WHERE 절은 체크조건으로 조인이 끝난 후에 처리된다.

​

SELECT D.DEPTNO, D.LOC, E.EMPNO, E.ENAME
  FROM DEPT D
  LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
 WHERE E.DEPTNO = 10;

SELECT D.DEPTNO, D.LOC, E.EMPNO, E.ENAME
  FROM DEPT D
  LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO AND E.DEPTNO = 10;

​​

​

4. OUTER JOIN과 COUNT(*)​

​드라이빙 테이블(dept_emp)와 드리븐 테이블(employees)은 M:1(or 1:1)관계이며 아래 쿼리는 dept_emp 테이블의 조건에 해당하는 건수를 세는 쿼리이다. 쿼리의 결과가 모두 동일하므로 count(*)를 사용할 때 불필요한 아우터 조인을 작성하지 말자.

​

select count(*) -- 20211
  from dept_emp de left join employees e on de.emp_no = e.emp_no
 where de.dept_no = 'd001';

select count(*) -- 20211
  from dept_emp de left join employees e on de.emp_no = e.emp_no and e.gender='F'
 where de.dept_no = 'd001';

select count(*) -- 20211
  from dept_emp de
 where de.dept_no = 'd001';

​

하지만, 드라이빙 테이블과 드리븐 테이블의 관계가 1:M 이라면 COUNT(*)의 결과는 달라지므로 주의해서 작성해야 한다. 아래 쿼리는 모두 결과가 다르다.

​​

select * from dept; -- 4건

select d.deptno, d.loc, e.empno, e.ename, count(*) over() cnt
  from dept d
  left join emp e on d.deptno = e.deptno and e.deptno = 10;

select d.deptno, d.loc, e.empno, e.ename, count(*) over() cnt
  from dept d
  left join emp e on d.deptno = e.deptno;

​

​

5. OUTER JOIN을 이용한 ANTI JOIN

​exists나 in 쿼리를 outer join으로 변경할 때 1:M은 결과 건수가 다르다. exist나 in은 1쪽 집합의 데이터의 존재여부만 (세미조인 or 필터)으로 결과 집합을 만들지만 outer join 은 1쪽 집합에 대해 M쪽 집합의 조인건수가 많으면 데이터가 늘어나기 때문이다.

​

-- 1:M exists

select d.deptno, d.loc
  from dept d
 where exists (select 1 from emp e where e.deptno = d.deptno);

-- 1:M outer join

select d.deptno, d.loc, e.empno, e.ename
  from dept d
  left join emp e on d.deptno = e.deptno
 where e.deptno is not null;

​

​하지만, exists나 Not in 쿼리를 outer join을 이용한 anti join 형태로 바꿀 때 1:1, 1:M, M:1 모두 결과 건수는 동일하다. 1:M의 경우 outer join 으로 변경되더라도 존재하지 않는경우(조인실패)만 결과 집합에 포함시키기 때문에 결과 집합이 늘어나지 않는다. MySQL의 경우 서브 쿼리에 대한 최적화가 부족하여 OUTER JOIN을 이용한다. (MySQL5.5 까지.. 그 이후 버전은 확인해야 한다.)

​

-- 1:M not exists

select d.deptno, d.loc
  from dept d
 where not exists (select 1 from emp e where e.deptno = d.deptno);

-- 1:M outer join (anti join)

select d.deptno, d.loc, e.empno, e.ename
  from dept d
  left join emp e on d.deptno = e.deptno
 where e.deptno is null;

​

당연히 WHERE 절의 IS NULL 컬럼은 조인조건으로 사용된 컬럼을 사용하여야 한다.

조인컬럼에 NULL 데이터가 포함되더라도 결과 건수는 동일하다. 여기서도 당연히 1:M관계의 exists 쿼리는 결과가 늘어난다.

​

[1:M 조인컬럼 : null 컬럼일 때 테스트 스크립트]

CREATE TABLE `emp1` (
`empno` int NOT NULL,
`ename` varchar(10) NOT NULL,
`job` varchar(9) NOT NULL,
`mgr` int DEFAULT NULL,
`hiredate` date NOT NULL,
`sal` decimal(7,2) NOT NULL,
`comm` decimal(7,2) DEFAULT NULL,
`deptno` int,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into emp1 select * from emp;
update emp1 set deptno=null where empno=7900;

CREATE TABLE `dept1` (
`deptno` int,
`dname` varchar(14) NOT NULL,
`loc` varchar(13) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into dept1 select * from dept;
insert into dept1 values(null, 'test','test');
select * from dept1;

​

-- 1:M not exists

select d.deptno, d.loc
  from dept1 d
 where not exists (select 1 from emp1 e where e.deptno = d.deptno);

-- 1:M exists

select d.deptno, d.loc
from dept1 d
where exists (select 1 from emp1 e where e.deptno = d.deptno);

-- 1:M outer join

select d.deptno, d.loc, e.empno, e.ename
  from dept1 d
  left join emp1 e on d.deptno = e.deptno
 where e.deptno is not null;

​UNION, UNION ALL은 모두 내부적인 임시테이블을 사용하므로 결과가 버퍼링돼야 하고, 그로 인해 쿼리가 느리게 처리된다. 버퍼링으로 인한 성능이 걱정된다면 mutex(단순히 레코드를 n개 만큼 복제) 테이블을 사용하면 된다.

​

select *
  from mutex m
  left join dept1 d on m.no=0; -- dept 테이블의 데이터 생성

select *
  from mutex m
 left join dept1 d on m.no=0
 left join emp1 e on m.no=1; -- emp 테이블의 데이터 생성

select *
from mutex m
left join dept1 d on m.no=0
left join emp1 e on m.no=1 or d.deptno = e.deptno; -- dept와 emp의 조인데이터 생성

select *
  from mutex m
  left join dept1 d on m.no=0
  left join emp1 e on m.no=1 or d.deptno = e.deptno
 where no=0 or (no=1 and e.deptno is null);

이제 FULL OUTER JOIN이 완성 되었다. 하지만 책에선 한번 더 LEFT JOIN을 한다...

select *
  from mutex m
  left join dept1 d on m.no=0
  left join emp1 e on m.no=1 or d.deptno = e.deptno
  left join dept1 d2 on m.no=1 and e.deptno = d2.deptno ;

아래 두개의 쿼리의 결과는 동일하다.

select *
  from mutex m
  left join dept1 d on m.no=0
  left join emp1 e on m.no=1 or d.deptno = e.deptno
  left join dept1 d2 on m.no=1 and e.deptno = d2.deptno
 where d2.deptno is null;

select *
  from mutex m
  left join dept1 d on m.no=0
  left join emp1 e on m.no=1 or d.deptno = e.deptno
  left join dept1 d2 on m.no=1 and e.deptno = d2.deptno
 where d2.deptno is null OR e.deptno is null;

​

​

7. Delayed Join(지연된 조인) => 페이징 쿼리 튜닝(조인범위 줄이기)

​

[튜닝 전 : 수행시간 2.9 sec]

select s.emp_no, e.first_name, e.last_name, sum(s.salary)
  from salaries s, employees e
 where e.emp_no = s.emp_no
   and s.emp_no between 10001 and 130000
 group by s.emp_no

드라이빙 : employees, 드리븐 : salaries, 조인을 수행한 횟수 :149,822

조인결과 약 13만5천건(1,348,398(149,822 * 9) 을 GROUP BY 처리를 통해 3만건으로 줄인 후에 ORDER BY를 처리해서 상위 10건 가져옴

​

[튜닝 후 : 수행시간 0.9 sec]

select e.emp_no, e.first_name, e.last_name
  from (select s.emp_no
          from salaries s
         where s.emp_no between 10001 and 130000
         group by s.emp_no
         order by sum(s.salary) desc
         limit 10) x, employees e
 where e.emp_no = x.emp_no;

드라이빙 : salaries, 드리븐 : employees, 조인을 수행한 횟수 : 최종 10건만 조인​

salaries 테이블에서 where, group by, order by 처리후에 limit으로 상위 10건 가져와서 최종 10건만 employees로 조인

​

[튜닝 전 : 수행시간 0.02 sec]

select *
from employees e
left join dept_emp de on e.emp_no = de.emp_no and now() between de.from_date and de.to_date
limit 0, 10;

[튜닝 후 : 수행시간 0.02 sec]

select *
from (select * from employees e limit 0,10) e
left join dept_emp de on e.emp_no = de.emp_no and now() between de.from_date and de.to_date;

[튜닝 전 : 수행시간 0.7 sec]
select *
from employees e
left join dept_emp de on e.emp_no = de.emp_no and now() between de.from_date and de.to_date
limit 9990, 10;

드라이빙 : employees, 드리븐 : dept_emp 조인을 수행한 횟수 : 300,024

조인결과 300,024건을 limit으로 9990건을 버리고 10건을 가져옴

​

[튜닝 후 : 수행시간 0.12 sec]

select *
  from (select * from employees e limit 9990,10) e
  left join dept_emp de on e.emp_no = de.emp_no and now() between de.from_date and de.to_date;

LIMIT n

LIMIT은 오라클의 ROWNUM과 MS-SQL의 TOP n과 비슷하다. 하지만 작동방식이 조금 다르다고 할 수 있다.

​

select *
  from employees
 where emp_no between 100001 and 100100 -- Index range scan(PK), 조건에 해당하는 레코드를 전부 읽어온다.
 order by first_name -- filesort , , 5개까지만 정렬한다.
 LIMIT 0, 5;

[실행순서]

1. empployees 테이블에서 where절의 검색 조건에 일치하는 레코드를 전부 읽어 온다.

2. 1번에서 읽어온 레코드를 first_name 컬럼 값에 따라 정렬한다.

3. 정렬된 결과에서 상위 5건만 사용자에게 반환한다.

​

MySQL의 LIMIT는 WHERE 조건이 아니기 때문에 항상 쿼리의 가장 마지막에 실행된다. WHERE 절의 조건에 해당하는 레코드를 전부 읽어와서 모든 레코드의 정렬이 완료되지 않았다 하더라도 상위5건까지만 정렬이 되면 작업을 멈춘다. 하지만 아래의 쿼리에서도 오라클과 달리 WHERE절의 조건에 해당하는 레코드를 전부 읽어와서 정렬해야 하는 부하가 발생한다.

​

select * 
  from employees
 where emp_no between 100001 and 100100 -- Index range scan(PK), 조건에 해당하는 레코드를 전부 읽어온다.
 order by emp_no -- Index range scan(PK) , 5개까지만 읽는다.
 limit 0, 5;

DISTINCT

집합함수(MIN, MAX, COUNT,..)와 DISTINCT가 사용되는 쿼리의 실행계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블을 사용하지만 Extra 컬럼에 "Using temporary" 메시지가 출력되지 않는다.

​

1. SELECT DISTINCT ...

GROUP BY와 같은 방식으로 처리되지만 정렬이 보장되지 않는다. 아래 두 쿼리는 정렬 작업 부분만 제외하면 내부적으로 같은 작업을 수행한다. 여기선 두 쿼리 모두 인덱스를 사용하여 부가적인 정렬 작업이 필요하지 않아 결과가 동일하다.

​

SELECT DISTINCT emp_no FROM salaries;

SELECT emp_no FROM salaries GROUP BY emp_no;

​

​

2. 집합 함수와 함께 사용된 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;

실행계획의 Extra 컬럼에는 출력되지 않지만 내부적으로 "count(distinct s.salary)"의 처리에는 인덱스 대신 임시테이블을 사용한다. employees 테이블과 salaries를 조인한 결과에서 salary 컬럼을 값을 임시테이블을 만들어서 저장한다. salary 컬럼에는 유니크 인덱스를 생성되기 때문에 레코드 건수가 많아진다면 느려질 수 있다.

​

explain
select count(distinct s.salary), count(distinct e.last_name)
from employees e, salaries s
where e.emp_no = s.emp_no
and e.emp_no between 100001 and 100100;

위의 쿼리는 s.salary 컬럼의 값과 e.last_name 컬럼의 값을 저장하는 임시테이블 2개를 사용한다.

아래와 같이 Index(range)를 사용한 DISTINCT 집합 함수쿼리에서는 Extra 컬럼에 "Using index from group-by" 메시지가 나타난다.

​

alter table dept_emp drop index ix_deptno;
create index ix_deptno on dept_emp(dept_no);
explain select count(distinct dept_no) from dept_emp;

explain select count(distinct emp_no) from dept_emp group by dept_no;