[Real MySQL] 7장 쿼리 작성 및 최적화

7.2 매뉴얼의 SQL 문법 표기를 읽는 방법

• MySQL 매뉴얼의 SQL 문법 표기

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORTY] [IGNORE] [INTO] tbl_name SET col_name = {expr | DEFAULT}, … [ ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] …]

 

7.3 MySQL 연산자와 내장 함수

7.3.1 리터럴 표기법

• 문자열
• 숫자
• 날짜
• 불리언

 

7.3.2 MySQL 연산자

• 동등 비교 (=, <=>)
• 부정 비교 (<>, !=)
• NOT 연산자 (!)
• AND(&&)와 OR(||) 연산자
• 나누기(/, DIV)와 나머지(%, MOD) 연산자
• REGEXP 연산자
• LIKE 연산자
• BETWEEN 연산자
• IN 연산자

 

7.3.3 MySQL 내장 함수

• NULL 값 비교 및 대체(IFNULL, ISNULL)
• IFNULL
• 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체
• ex. SELECT IFNULL(NULL, 1);
• ISNULL
• 표현식이나 칼럼의 값이 NULL인지 아닌지 비교 (1: TRUE, 0: FALSE)
• ex. SELECT ISNULL(0);
• 현재 시각 조회(NOW, SYSDATE)
• NOW : 하나의 SQL에서 같은 값
• SYSDATE : 호출 시점에 따라 결과 값이 달라짐
• 슬레이브에서 안정적으로 복제되지 못함
• 인덱스를 효율적으로 사용하지 못함(p. 399 예제)
• 날짜와 시간의 포맷(DATE_FORMAT, STR_TO_DATE)
• SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’) AS current_dttm;
• SELECT STR_TO_DATE(‘2011-04-30 15:13:25’, ‘%Y-%m-%d %H:%i:%s’)
• 날짜와 시간의 연산(DATE_ADD, DATE_SUB)
• SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow;
• INTERVAL n [YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, …]
• 타임 스탬프 연산(UNIX_TIMESTAMP, FROM_UNIXTIME)
• TIMESTAMP : 4바이트 숫자 타입으로 저장
• SELECT UNIX_TIMESTAMP(‘2005-03-27 03:00:00);
• 문자열 처리(RPAD, LPAD / RTRIM, LTRIM, TRIM)
• SELECT RPAD(‘Cloee’, 10, ‘0’); -> Cloee00000
• SELECT TRIM(‘           Cloee           ‘); -> Cloee
• 문자열 결합(CONCAT)
• SELECT CONCAT(‘Georgi’, ‘Chrisian’) -> GeorgiChristian
• 인자의 개수는 제한이 없다.
• SELECT CONCAT_WS(‘,’, ‘Georgi’, ‘Christian’); -> Georgi,Christian
• 첫 번째 인자로 구분자
• GROUP BY 문자열 결합(GROUP_CONCAT)
• 그룹함수 : GROUP BY 와 함께 사용하며, GROUP BY가 없는 경우엔 단 하나의 결과 값만 도출
• SELECT GROUP_CONCAT(dept_no) FROM departments;
• 메모리 버퍼 공간을 사용
• 시스템 변수에 지정된 크기를 초과하면 경고
• 값의 비교와 대체(CASE WHEN … THEN … END)
• SQL 구문

SELECT emp_no, first_name, CASE gender WHEN ‘M’ THEN ‘Man’ WHEN ‘F’ THEN ‘Woman’ ELSE ‘Unknown’ END AS gender FROM employees LIMIT 10;

 

• 타입의 변환(CAST, CONVERT)
• CONVERT 가 문자열의 문자집합을 변환하는 용도를 가졌다는 것과 인자 사용 규칙만 다르고 같은 역할을 한다
• SELECT CAST(‘1234’ AS SIGNED INTEGER);
• SELECT CONVERT(‘1234’, INTEGER);
• SELECT CONVERT(‘ABC’ USING ‘utf8’);
• 이진값과 16진수(Hex String) 문자열 변환(HEX, UNHEX)
• INSET INTO tab_binary VALUES(UNHEX(MD5(‘abc’)), UNHEX(SHA(‘abc’)));
• SELECT HEX(col_md5), HEX(col_sha) FROM tab_binary;
• 암호화 및 해시 함수(MD5, SHA)
• SHA()
• SHA-1 암호화 알고리즘 사용
• 결과로 160비트(20바이트)의 해시 값을 반환
• MD5()
• 메시지 다이제스트 알고리즘 사용
• 128비트(16바이트)의 해시 값을 반환
• 두 함수의 출력 값은 16진수로 표시되기 때문에 저장 공간이 두 배로 필요
• 저장 공간을 줄이고 싶다면 BINARY 타입으로 정의하고 UNHEX와 HEX 이용
• 처리 대기(SLEEP)
• 개발이나 디버깅 용도
• SELECT SLEEP(1) FROM employees;
• 벤치마크(BENCHMARK)
• 디버깅이나 간단한 함수의 성능 테스트용
• 반복할 횟수, 실행할 표현식을 인자로 받음
• SELECT BENCHMARK(100, (SELECT COUNT(*) FROM employees));
• IP 주소 변환(INET_ATON, INET_NTOA)
• IP 주소는 4바이트의 부호 없는 정수
• VARCHAR(15) 타입으로 저장하면 저장 공간을 훨씬 많이 필요
• INSERT INTO tab_accesslog VALUES(NOW(), INET_ATON(‘127.0.0.1’));
• SELECT access_dttm, INET_NTOA(ip_addr) FROM tab_accesslog;
• MySQL 전용 암호화(PASSWORD, OLD_PASSWORD)
• 암호화 함수
• 4.0 이하 버전에서 쓰이던 PASSWORD 가 OLD_PASSWORD로 수정됨
• 이후에도 업그레이드 가능성이 있으니 사용에 주의
• MD5(), SHA() 추천
• VALUES()
• INSERT INTO … ON DUPLICATE KEY UPDATE … 구문에서만 사용 가능

INSERT INTO tab_statistice(member_id, visit_count) SELECT  member_id, COUNT(*) AS cnt FROM tab_accesslog GROUP BY member_id ON DUPLICATE KEY UPDATE visit_count = visit_count + VALUES(visit_count);

 

• COUNT()
• COUNT(*) 형태로 사용, 여기서 *은 모든 칼럼이 아닌 레코드 자체를 의미
• 인덱스를 제대로 사용한 COUNT()는 SELECT 쿼리보다 빠르다 (반대로 인덱스를 제대로 사용하지 못한 경우엔 페이징해서 데이터를 가져오는 SELECT 쿼리보다 훨씬 느리게 수행 될 수도 있다)

 

7.3.4 SQL 주석

• SQL 표준에서 정의한 주석 표기 방식
• -- : 한 라인만 주석
• /* … */ : 여러 라인을 주석
• MySQL
• # : 한 라인만 주석
• /*! … */ : 선택적인 처리나 힌트

 

7.4 SELECT

7.4.1 SELECT 각 절의 처리 순서

• 쿼리 구분
• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• 쿼리 절의 실행 순서

1. WHERE 적용 및 조인 실행
2. GROUP BY 적용(레코드 그룹핑)
3. DISTINCT 적용
4. HAVING 조건 필터링
5. ORDER BY 정렬
6. LIMIT 적용

• 예외적 경우(주로 GROUP BY 절이 없이 ORDER BY만 사용된 쿼리에서 사용)

1. 드라이빙 테이블
2. ORDER BY 정렬
3. WHERE 적용 및 조인 실행
4. LIMIT 적용

 

7.4.2 WHERE 절과 GROUP BY 절, 그리고 ORDER BY 절의 인덱스 사용

• 인덱스를 사용하기 위한 기본 규칙
• 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용
• 연사자 양쪽의 비교 대상 값은 데이터 타입이 일치
• WHERE 절의 인덱스 사용
• 인덱스 사용 방법
• 범위제한 조건

• 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 칼럼과 얼마나 좌측부터 일치하는가에 따라
• 체크 조건(p.424 그림 설명)
• OR 연산자 사용시
• OR로 연결된 각 조건이 모두 각 인덱스를 가진다면, index_merge 접근 방법 실행
• 그 이외의 경우에는 풀 테이블 스캔
• GROUP BY 절의 인덱스 사용
• 규칙
• GROUP BY절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야
• 인덱스의 앞쪽에 있는 칼럼은 GROUP BY 절에 반드시 명시되야
• GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 인덱스를 이용하지 못한다.
• WHERE 조건절에 앞쪽에 있는 칼럼이 동등 비교 조건으로 사용된다면, 그 칼럼이 있는 것과 같은 효과
• ORDER BY 절의 인덱스 사용
• GROUP BY와 조건 동일 + 모든 칼럼이 오름차순이거나 내림차순일 때만
• WHERE 조건과 ORDER BY(또는 GROUP BY)절의 인덱스 사용
• WHERE절과 ORDER BY절이 동시에 같은 인덱스 이용
• WHERE 절의 비교 조건에서 사용하는 칼럼과 ORDER BY절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함
• 가장 빠른 성능
• WHERE절만 인덱스 이용
• 인덱스를 통해 검색된 결과 레코드를 정렬 처리 과정을 거쳐서 정렬(Filesort)
• WHERE 절의 조건에 일치하는 레코드의 건수가 적을 때
• ORDER BY절만 인덱스 이용
• ORDER BY 절의 순서대로 인덱스를 읽으면서, 레코드 한 건씩을 WHERE 절의 조건에 일치하는지 비교해 버리는 형태로 처리
• 아주 많은 레코드를 조회해서 정렬해야 할 때
• p.429 그림 설명
• GROUP BY절과 ORDER BY 절의 인덱스 사용
• GROUP BY 조건과 ORDER BY 조건을 모두 만족할 때만 사용 가능
• WHERE 조건과 ORDER BY 절, 그리고 GROUP BY 절의 인덱스 사용
• WHERE 조건과 ORDER BY 절, 그리고 GROUP BY 절의 인덱스 사용 여부
• WHERE 절이 인덱스를 사용할 수 있는가?
• GROUP BY 절이 인덱스를 사용할 수 있는가?
• GROUP BY절과 ORDER BY절이 동시에 인덱스를 사용할 수 있는가?
• p.432 그림 설명

 

7.4.3 WHERE 절의 비교 조건 사용 시 주의사항

• NULL 비교
• SQL 표준 NULL의 정의 : 비교할 수 없는 값
• IS NULL 연산자 외에는 칼럼의 값이 NULL인지 알 수 있는 방법X
• 주의점

SELECT * FROM titles WHERE to_date IS NULL; SELECT * FROM titles WHERE ISNULL(to_date); SELECT * FROM titles WHERE ISNULL(to_date) = 1; //인덱스 사용 X SELECT * FROM titles WHERE ISNULL(to_date) = true; //인덱스 사용 X

 

• 문자열이나 숫자 비교
• 문자열이나 숫자 칼럼을 비교할 때는 반드시 그 타입에 맞춰서 상수를 사용할 것을 권장

SELECT * FROM employees WHERE emp_no=10001; SELECT * FROM employees WHERE first_name =’Smith’; SELECT * FROM employees WHERE emp_no=’10001’; SELECT * FROM employees WHERE first_name=10001; // 인덱스 사용 X

 

• 날짜 비교
• DATE나 DATETIME과 문자열 비교
• MySQL이 내부적으로 변환
• STR_TO_DATE()함수로 형변환을 한 것과 동일하게 처리
• But, 형변환을 한다면 칼럼을 변경하지 말고, 상수를 변경할 것!
• DATE와 DATETIME의 비교
• DATE(date_time) : DATETIME값을 DATE 타입으로 변환해서 비교
• DATE 타입을 DATETIME으로 변환하면 시간은 00:00:00으로
• DATETIME과 DATE의 타입 변환은 인덱스 사용 여부에 영향X
• DATETIME과 TIMESTAMP의 비교
• UNIX_TIMESTAMP()함수의 결과 값은 내부적으로는 단순 숫자 값
• FROM_UNIXTIME() 이나 UNIX_TIMESTAMP() 함수로 변환해서 비교

 

7.4.4 DISTINCT

• SELECT DISTINCT …
• DISTINCT와 GROUP BY는 정렬의 차이만 있을 뿐 같은 방식으로 처리
• DISTINCT는 SELECT 되는 레코드를 유니크하게 SELECT하는 것이지 칼럼을 유니크하게 조회하는 것X -> 칼럼 일부만 유니크하게 조회하는 방법은 없다!
• 집합 함수와 함께 사용된 DISTINCT
• 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값들 중에 중복을 제거하고 남은 값만 가져온다.

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 칼럼에는 출력되지 않지만 내부적으로 임시 테이블을 사용
• salary 칼럼의 값을 위한 임시 테이블을 만들고 유니크 인덱스가 생성되기 때문에 상당히 느려질 수 있는 형태의 쿼리
• 인덱스된 칼럼에 대한 DISTINCT를 사용해 효율적으로 처리 가능

 

7.4.5 LIMIT n

• LIMIT가 사용된 예제

SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 10010 ORDER BY first_name LIMIT 0, 5;

• employees 테이블에서 WHERE 절의 검색 조건에 일치하는 레코드를 전부 읽어
• 읽은 레코드를 first_name 칼럼 값에 따라 정렬
• 정렬된 결과에서 상위 5건만 사용자에게 반환
• 특징
• 정렬이나 그룹핑 또는 DISTINCT가 없는 쿼리에선 성능 향상이 큼
• GROUP BY나 ORDER BY와 같은 전체 범위 작업은 LIMIT보다 먼저 실행되기 때문에 작업 내용을 크게 줄여주진 못하지만 성능 향상에 도움
• DISTINCT는 정렬에 대한 요건이 없이 유니크한 그룹만 만들어 내면 되어 레코드를 읽음과 동시에 중복 제거 작업을 진행하는데, LIMIT에 지정한 건수만 작업하면 되기 때문에 성능 향상이 큼
• 만약 인덱스를 이용한다면, GROUP BY, ORDER BY, DISTINCT 절이 포함되었다고 하더라도 필요한 만큼의 레코드만 읽도록 만들어주기 때문에 쿼리의 작업량을 상당히 줄여 줌
• 제한 사항
• LIMIT의 인자로 표현식이나 별도의 사브 쿼리는 불가

 

7.4.6 JOIN

• JOIN의 순서와 인덱스
• 인덱스 레인지 스캔으로 레코드를 읽는 작업 정리

1. 인덱스에서 조건을 만족하는 값이 저장된 위치 탐색 : 인덱스 탐색
2. 탐색된 위치부터 필요한 만큼 인덱스 스캔 : 인덱스 스캔
3. 읽어드린 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어옴

• 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 한 번만 수행하고, 스캔 But 드리븐 테이블은 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복 -> 옵티마이저는 드리븐 테이블을 최적으로 읽는 실행 계획 수립
• 옵티마이저 선택

SELECT * FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no;

• 두 칼럼 모두 각각 인덱스가 있는 경우

• 어느 테이블을 드라이빙으로 선택하든 검색 작업을 빠르게 처리
• 통계 정보를 이용해 적절히 드라이빙 테이블을 선택
• 옵티마이저가 선택하는 방법이 최적일 때가 다수
• 한 쪽에만 인덱스가 있는 경우
• 인덱스가 있는 테이블을 드리븐 테이블로 선택
• 인덱스가 있는 테이블을 드라이빙 테이블로 선택하는 것이 더 효율적이더라도 옵티마이저는 드리븐 테이블로 선택할 가능성이 높다  
• 두 칼럼 모두 인덱스가 없는 경우
• 옵티마이저가 적절히 드라이빙 테이블을 선택
• 레코드 건수가 적은 테이블을 드리븐 테이블로 선택하는 것이 훨씬 효율적

• JOIN 칼럼의 데이터 타입
• 조인 칼럼 간의 비교에서 각 칼럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 이용 불가능
• CHAR / VARCHAR, INT / BIGINT, DATE/DATETIME 사이는 타입 불일치 발생X
• 문제가 될 가능성이 높은 경우
• CHAR타입과 INT타입의 비교와 같이 데이터 타입의 종류가 다른 경우
• 같은 CHAR 타입이더라도 문자집합이나 콜레이션이 다른 경우
• 같은 INT 타입이더라도 부호가 있는지 여부가 다른 경우
• OUTER JOIN의 주의사항
• OUTER JOIN의 조건은 ON절에 명시
• WHERE 절에 명시하면 옵티마이저가 INNER JOIN과 같은 방법으로 처리

SELECT * FROM employees e            LEFT JOIN dept_manager mgr ON mgr.`emp_no` = e.emp_no WHERE mgr.`dept_no`='d001';

SELECT * FROM employees e            LEFT JOIN dept_manager mgr ON mgr.`emp_no` = e.emp_no AND mgr.`dept_no`='d001';

 

• OUTER JOIN과 COUNT(*)
• 일반적으로 페이징 처리는 테이블의 레코드를 가져오는 쿼리와 단순히 레코드 건수만 가져오는 쿼리가 쌍으로 사용
• 조인에서 불필요한 테이블을 제거하면 같은 결과를 더 빠르게 가져올 수
• OUTER JOIN 제거 가능 조건
• 드라이빙 테이블과 드리븐 테이블의 관계가 1:1 혹은 M:1
• 드리븐 테이블에 조인 조건 이외의 별도 조건X

SELECT COUNT(*) FROM dept_emp de LEFT JOIN emplyees e ON e.emp_no = de.emp_no WHERE de.dept_no = ‘d001’;

SELECT COUNT(*) FROM dept_emp de WHERE de.dept_no = ‘d001’;

 

• OUTER JOIN을 이용한 ANTI JOIN
• 한쪽 테이블에는 있지만 다른 쪽 테이블에는 없는 레코드를 검색할 때
• 보통 NOT IN이나 NOT EXISTS로 처리(레코드 건수가 많으면 성능상의 문제)
• ANTI JOIN

SELECT t1.id FROM tab_test1 t1 LEFT JOIN tab_test2 t2 ON t1.id = t2.id WHERE t2.id IS NULL;

 

• INNER JOIN과 OUTER JOIN의 선택
• OUTER JOIN과 INNER JOIN은 성능을 고려해서 선택할 것이 아니라 업무 요건에 따라 선택하는 것
• FULL OUTER JOIN 구현
• MySQL에서는 FULL OUTER JOIN 기능 제공 X
• FULL OUTER JOIN 구현하는 방법

SELECT e.yearmonth, e.event_name, n.news_title FROM tab_event e            LEFT JOIN tab_news n ON n.yearmonth = e.yearmonth UNION SELECT n.yearmonth, e.event_name, n.news_title FROM tab_news n            LEFT JOIN tab_event e ON e.yearmonth = n.yearmonth;

SELECT e.yearmonth, e.event_name, n.news_title FROM tab_event e            LEFT JOIN tab_news n ON n.yearmonth = e.yearmonth UNION ALL SELECT n.yearmonth, e.event_name, n.news_title FROM tab_news n            LEFT JOIN tab_event e ON e.yearmonth = n.yearmonth WHERE e.yearmonth IS NULL;

• UNION은 두 집합의 결과에서 중복 제거가 필요하기 때문에 UNION LL을 사용하는 것이 더 빠르게 처리
• JOIN과 FOREIGN KEY
• FOREIGN KEY는 데이터의 무결성을 보장하기 위해
• 지연된 조인
• MySQL 서버는 모든 조인이 실행하고 난 다음 GROUP BY나 ORDER BY 처리
• 조인이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식

SELECT * FROM dept_emp de, employees e WHERE de.dept_no = ‘d001’ AND e.emp_no = de.emp_no LIMIT 100, 10;

dept_emp가 드라이빙 테이블이 되고, dept_no가 d001인 레코드를 한 건씩 읽으면서 employees 테이블과 조인하면서 LIMIT 100, 10조건이 만족될 때까지 조인이 수행 -> 총 110건 읽고, 110번 조인

SELECT * FROM (SELECT * FROM dept_emp WHERE dept_no = ‘d001’ LIMIT 100, 10) de, employees e WHERE e.emp_no = de.emp_no;

dept_emp로 부터 가져온 10건의 레코드와만 10번 조인

 

7.4.7 GROUP BY

• GROUP BY 사용 시 주의사항
• GROUP BY 절에 명시된 칼럼이 아닌 칼럼은 일반적으로 집합 함수를 감싸서 사용해야

SELECT first_name, last_name, COUNT(*) FROM employees GROUP BY first_name ORDER BY first_name;

• 오라클 같은 DBMS 에서는 에러
• 쿼리의 가독성 떨어뜨림
• GROUP BY … ORDER BY NULL
• MySQL의 GROUP BY는 그룹핑 칼럼 순서대로 정렬까지 수행

EXPLAIN SELECT from_date FROM salaries GROUP BY from_date;

• ORDER BY NULL 을 추가하여 정렬을 막을 수 있다

EXPLAIN SELECT from_date FROM salaries GROUP BY from_date ORDER BY NULL;

 

• GROUP BY col1 ASC col2 DESC
• GROUP BY 절 칼럼에 순서 명시 가능
• ORDER BY와 같은 수행을 하므로 정렬을 혼용하면 인덱스 사용 불가
• GROUP BY … WITH ROLLUP
• 그룹핑된 그룹별로 소계를 가져올 수 있는 기능

SELECT dept_no, COUNT(*) FROM dept_emp GROUP BY dept_no WITH ROLLUP;

• 제한사항
• ORDER BY와 함께 사용 불가
• LIMIT 와 함께 사용되는 경우에는 결과가 혼란스러울 가능성

• GROUP BY … WITH ROLLUP 처리 후 LIMIT 수행
• 레코드를 칼럼으로 변환해서 조회
• 레코드를 칼럼으로 변환
• GROUP BY 결과를 SUM(CASE WHEN …) 기능을 이용해 한번 더 변환
• p. 466 코드
• 하나의 칼럼을 여러 칼럼으로 분리
• 특정 조건으로 소그룹을 나눠서 카운팅 가능

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 e.emp_no = de.emp_no GROUP BY de.dept_no WITH ROLLUP;

7.4.8 ORDER BY

• ORDER BY 절이 사용되지 않을 때의 정렬
• 인덱스를 사용한 경우에는 인덱스의 정렬 순서
• 풀 테이블 스캔의 경우엔 테이블에 저장된 순서(MyISAM, INSERT 순서X)
• InnoDB 경우엔 프라이머리 키로 크러스터링돼 있기 때문에 프라이머리 키 순서
• 처리 절차에 따라 어떠한 정렬도 보장하지 않는다.
• ORDER BY 사용법 및 주의사항
• 1개 이상의 칼럼으로 정렬 수행 가능
• 정렬 순서는 각 칼럼별로 다르게 명시 가능
• SELECT 되는 칼럼의 순번으로 명시 가능 (ex. ORDER BY 2)
• 숫자 값이 아닌 문자열 상수 사용 시엔 ORDER BY 절 자체를 무시
• ORDER BY RAND()
• 추첨 또는 임의의 사용자 조회를 처리할 때 사용
• RAND() 함수로 발생되는 임의의 값을 각 레코드 별로 부여, 그 임의값으로 정렬
• 문제점 및 해결책
• 인덱스 이용 불가
• 임의 값을 별도의 칼럼으로 생성해두고, 그 칼럼에 인덱스를 생성해 임의 정렬 방법 추천
• 여러 방향으로 동시 정렬
• 정렬 순서가 혼용되면 인덱스 이용 불가
• 칼럼 값을 변형시켜 저장하는 것이 유일한 해결책
• 숫자 타입 :  반대 부호로 변환
• 날짜 타입 : 타임스탬프로 변환하여 음수로 저장
• NULL 값은 항상 최소의 값으로 간주하고 정렬
• 칼럼 값을 변형시키기 어려운 경우(문자열)
• 어플리케이션에서 쿼리를 나눠서 정렬(p.472 코드)

• 정렬해야 할 레코드 건수가 너무 많아서 디스크를 이용할 경우
• 첫 번째 정렬 칼럼에 중복 값이 많아서 두 번째 쿼리의 반복 실행 횟수가 적은 경우
• 함수나 표현식을 이용한 정렬
• 표현식의 결과 순서가 칼럼의 원본 값 순서와 동일할 때(ex. emp_no + 10)
• 변형되지 않은 칼럼을 그대로 사용해줄 것
• 표현식의 정렬 순서가 칼럼의 원본 값과 다른 경우
• 연산의 결과가 칼럼의 값에만 의존적인 경우(ex. ABS(member_age - 30))
• 연산의 결과를 저장하는 칼럼을 만들고, 그 칼럼에 인덱스 생성하여 수행
• 칼럼 이외의 값에 의존적인 경우(ex. ABS(member_age - ?))
• ORDER BY 자체를 튜닝하기는 어려워
• WHERE 절의 조건을 최적화해서 정렬해야 할 레코드 수를 최대한 줄이는 형태로 튜닝

 

7.4.9 서브 쿼리

• 서브 쿼리 종류
• 상관 서브 쿼리
• 외부에서 정의된 테이블의 칼럼을 참조해서 검색을 수행
• 외부 쿼리가 실행된 후 서브 쿼리 실행
• 독립 서브 쿼리
• 서브 쿼리에서 정의된 칼럼만 참조
• 외부 쿼리보다 먼저 실행되어 외부 쿼리의 검색을 위한 상수로 사용되는 것이 일반적
• 서브 쿼리의 제약 사항
• LIMIT 절과 LOAD DATA INFILE의 파일명에는 사용 X
• IN 연산자와 함께 사용할 땐 비효율적
• IN 연산자 안에서 사용하는 서브 쿼리에는 ORDER BY와 LIMIT를 동시에 사용X
• FROM 절에 사용하는 서브 쿼리는 상관 서브 쿼리 형태 X
• 서브 쿼리를 이용해 하나의 테이블에 대해 읽고 쓰기 동시 작업 X
• SELECT 절에서 사용된 서브 쿼리
• 임시테이블 생성X, 쿼리 비효율적 실행X
• 적절히 인데스를 사용할 수 있게만
• 주의점
• 서브 쿼리의 결과가 NULL이거나 1건만 존재해야
• 조인으로 처리 가능한지 확인 (조인으로 처리가 훨씬 성능이 좋기 때문)
• WHERE 절에 단순 비교를 위해 사용된 서브 쿼리
• WHERE 절에선 상관 서브 쿼리는 범위 제한 조건으로 사용되지 못함
• 독립 서브 쿼리 경우는 서브 쿼리를 먼저 실행 후 상수로 변환하고, 그 조건을 범위 제한 조건으로 사용하는 것이 일반적 RDBMS
• MySQL에서 독립 서브 쿼리 처리 방법

SELECT * 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);

• MySQL 5.1 이하

• 풀 테이블 스캔으로 레코드 한 건씩 읽으면서, 서브 쿼리를 매번 실행해서 서브 쿼리가 포함된 조건이 참인지 비교
• MySQL 5.5
• dept_emp 테이블을 읽기 위해 인덱스를 사용해서 레인지 스캔
• 서브 쿼리가 먼저 실행되어 그 결과를 외부 쿼리 비교 조건의 입력으로 전달했음을 의미
• WHERE 절에 IN과 함께 사용된 서브 쿼리
• WHERE절에 IN 연산자를 상수와 함께 사용할 때는 동등 비교와 똑같이 처리
• MySQL 옵티마이저는 IN(subquery)부분을 EXISTS(subquery)형태로 변환
• 상당히 비효율적이기 때문에 개선 필요
• 바깥 쪽 테이블과 서브 쿼리 테이블의 관계가 1:1 혹은 M:1
• 조인으로 풀어서 성능 개선 가능
• 바깥 쪽 테이블과 서브 쿼리 테이블의 관계가 M:1
• 최종 결과의 건수가 달라질 수 있기 때문에 단순히 변경 불가
• GROUP BY를 추가해 강제로 중복 제거
• 서브 쿼리를 분리시켜서 2개의 쿼리 실행(p.482 코드)
• WHERE 절에 NOT IN과 함께 사용된 서브 쿼리
• IN보다 더 비효율적 처리 (NOT IN -> NOT EXISTS)
• IN 왼쪽값이 NULL 가능성을 가졌기 때문
• 왼쪽 값이 NULL이 아닐 땐 NOT EXISTS 처리
• NULL일 경우엔 NOT EXISTS로 처리 불가
• subquery 결과 확인

• 서브 쿼리가 결과 레코드를 가진다면 NULL
• 서브 쿼리가 결과 레코드를 가지지 않는다면 FALSE
• 이런 경우엔 인덱스 사용 불가 -> 풀 테이블 스캔
• 개선 방안
• 칼럼에 NOT NULL 옵션 혹은 WHERE 절에 IS NOT NULL 명시
• LEFT JOIN으로 개선(p.484)
• FROM절에 사용된 서브 쿼리
• FROM절에 사용된 서브 쿼리는 항상 임시 테이블 사용 -> 비효율적
• 서브 쿼리가 만들어 내는 데이터가 작을 경우엔 메모리에 임시 테이블이 생성돼 큰 문제는 없나, 아닐 경우엔 디스크에 생성되어 디스크의 읽고 쓰기 작업이 병목 지점이 될 가능성
• FROM 절의 서브 쿼리가 꼭 필요한 경우가 아니라면 풀어서 사용하는 것 추천

 

7.4.10 집합 연산

• UNION(DISTINCT, ALL)
• UNION ALL
• 두 개의 집합에서 중복된 레코드에 대해 별도의 처리 과정을 거치지 않고 반환
• UNION DISTINCT
• 두 개의 집합에서 중복된 레코드를 제거한 후 합집합을 사용자에게 반환
• 성능 비교
• 두 연산 모두 버퍼 역할을 하는 임시 테이블을 사용
• UNION DISTINCT는 모든 칼럼을 이용해 UNIQUE 인덱스를 생성
• 두 집합이 중복된 레코드가 없는 것이 확실한 경우엔 UNION ALL 명령을 사용(UNION만 사용하면 DISTINCT가 실행)
• INTERSECT
• 두 개의 집합에서 교집합 부분만 반환
• INNER JOIN과 동일
• MySQL은 INTERSECT 집합 연산을 제공 X
• MINUS
• 첫 번째 결과 집합에서 두 번째 결과 집합의 내용을 뺀 결과 반환
• NOT IN 이나 NOT EXISTS로 구현 가능
• 레코드 건수가 많아지면 많아질수록 LEFT JOIN을 이용한 ANTI-JOIN 형태가 더 빠른 성능

 

7.4.11 LOCK IN SHARE MODE와 FOR UPDATE

• InnoDB 테이블에선 SELECT할 때 잠금X
• 잠금이 필요할 때 쓰는 명령
• LOCK IN SHARE MODE
• SELECT된 레코드에 대해 읽기 잠금을 설정
• 다른 세션에서 해당 레코드 변경 금지
• 잠금이 걸린 레코드를 읽는 것은 가능
• FOR UPDATE
• 쓰기 잠금 설정
• 다른 트랜잭션에서 변경, 읽기 모두 금지
• 예시

SELECT * FROM employees WHERE emp_no=10001 LOCK IN SHARE MODE; SELECT * FROM employees WHERE emp_no=10001 FOR UPDATE;   SELECT * FROM employees WHERE emp_no=10001 /*! LOCK IN SHARE MODE */; SELECT * FROM employees WHERE emp_no=10001 /*! FOR UPDATE */;

• 주의점
• 잠금 경합 유발, 데드락 발생 가능성 -> 모니터링 필수

 

7.4.12 SELECT INTO OUTFILE

• 파일로 저장하는 명령어
• 주의점
• MySQL 서버가 기동 중인 장비의 디스크로 저장
• 결과를 저장할 파일, 디렉터리는 MySQL 서버를 기동 중인 운영체제의 계정이 쓰기 권한 필요
• 이미 동일 디렉터리에 동일 이름이 존재할 땐 에러를 발생하고 종료
• 예시

SELECT emp_no, first_name, last_name INTO OUTFILE ‘c:\\temp\\result.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“‘ ESCAPED BY ‘“’ LINES TERMINATED BY ‘\r\n’ FROM employees WHERE emp_no BETWEEN 10001 AND 10100;

7.5 INSERT

 

7.5.1 INSERT와 AUTO_INCREMENT (p.495)

• AUTO_IMCREMENT 제약 및 특성 ( p.495)
• 강제 저장한 값이 현재 값보다 작으면 AUTO_INCREMENT 의 현재 값이 변하지 않음
• MyISAM 의 경우 프라이머리 키 값이 뒤에 있으면 앞 칼럼에 의존해서 증가

 

CREATE TABLE tb_innodb (

           fd1 CHAR,

           fd2 INT AUTO_INCREMENT,

           PRIMARY KEY(fd1, fd2),

           UNIQUE KEY(fd2)

)ENGINE = INNODB;

 

CREATE TABLE tb_myisam (

           fd1 CHAR,

           fd2 INT AUTO_INCREMENT,

           PRIMARY KEY(fd1, fd2)

)ENGINE = MYISAM;

 

INSERT INTO tb_innodb VALUES ('A', NULL), ('A', NULL), ('B', NULL),('B', NULL);

INSERT INTO tb_myisam VALUES ('A', NULL), ('A', NULL), ('B', NULL),('B', NULL);

 

• auto_increment_offset : 속성의 칼럼 초기 값 정의
• auto_increment_increment : 얼마씩 증가할건지 결정

 

• AUTO_INCREMENT(AI) 잠금 (p.498)
• 여러커넥션에서 AI 동시에 사용할때 AutoIncrement 잠금이라는 테이블 단위의 잠금 사용
• AutoIncrement 잠금 : AI 현재값 가져올때만 잠금 걸렸다가 즉시 해제
• AI 가 insert 문장으로 한번 증가하면 트랜잭션이 rollback 되더라도 원래 값으로 돌아가지 않아
• innodb_autoinc_mode
• 0 : AutoIncrement 잠금 걸고 한번에 1씩 증가
• 1 : 뮤텍스 이용해 처리

 

• AUTO_INCREMENT(AI) 증가 값 가져오기 (p.498)
• 가장 최근 저장된 AI 값 가져오려면? select max(member_id) from… 사용...NO
• 현재 커넥션 뿐 아니라 다른 커넥션에서 증가된 AI 값도 가져옴
• last_insert_id() 사용하자
• 현재 커넥션에서 마지막에 가져온 값 반환

 

CREATE TABLE tb_autoincrement(

           seq_no INT NOT NULL AUTO_INCREMENT,

           NAME VARCHAR(30),

           PRIMARY KEY(seq_no)

);

INSERT INTO tb_autoincrement VALUES(NULL, 'hyeonseok lim');

SELECT LAST_INSERT_ID();

 

• last_insert_id() 는 쿼리를 실행 해서 시간 잡아 먹어
• Statement.getGeneratedKeys() 사용하면 추가적인 네트워크 통신 필요없어 ( p.499쪽 코드 참고)

 

7.5.2 INSERT IGNORE (p.501)

• Insert 문장에 Ignore 추가하면 primary key 나 unique key 로 인한 중복 에러가 발생해도 무시하고 작업 진행

 

7.5.3 REPLACE (p.502)

• replace 는 insert 와 update 기능을 묶은 쿼리와 같은 기능 수행
• 저장하려는 레코드가 중복이면 update 아니면 insert 실행
• update 의 경우 해당 레코드 delete 후 insert 실행
• 중복된 레코드를 찾을때는 체이블의 모든 유니크 값에서 동일 값이 존재하는지 비교
• 테이블에 p 키 와 u 키가 있다면 p 와 u 모두 중복된 값이 존재하는지 비교

 

CREATE TABLE tb_replace (

           fd1 INT NOT NULL,

           fd2 INT NOT NULL,

           PRIMARY KEY(fd1),

           UNIQUE INDEX ux_fd2 (fd2)

);

 

INSERT INTO tb_replace(fd1, fd2) VALUES (1,1), (2,2), (3,3), (4,4), (5,5);

 

mysql> select * from tb_replace;

+-----+-----+

| fd1  | fd2  |

+-----+-----+

|   1   |   1   |

|   2   |   2   |

|   3   |   3   |

|   4   |   4   |

|   5   |   5   |

+-----+-----+

replace into tb_replace(fd1, fd2) values (5,3); -- (5,3 ) 존재 하지 않는 값

 

mysql> select * from tb_replace;

+-----+-----+

| fd1  | fd2  |

+-----+-----+

|   1   |   1   |

|   2   |   2   |

|   5   |   3   |

|   4   |   4   |

+-----+-----+

 

(3,3) (5,5) 삭제 되고 (5,3) 투입

 

7.5.4 INSERT INTO ... ON DUPLICATE KEY UPDATE … (IU) (p.504)

• replace 문과 흡사하게 작동하는 insert 쿼리
• 차이점 replace : 중복된 레코드 선 삭제 후 삽입
• IU : 중복된 레코드 delete 하지 않고 업데이트

 

CREATE TABLE tb_insertondup (

           fd1 INT NOT NULL,

           fd2 INT NOT NULL,

           PRIMARY KEY(fd1)

);

 

INSERT INTO tb_insertondup VALUES (1,1), (2,2);

 

mysql> select * from tb_insertondup;

+-----+-----+

| fd1 | fd2   |

+-----+-----+

|   1   |   1   |

|   2   |   2   |

+-----+-----+

 

INSERT INTO  tb_insertondup (fd1, fd2) VALUES (1, 100) ON DUPLICATE KEY UPDATE fd2= fd2 +100;

 

mysql> select * from tb_insertondup;

+-----+-----+

| fd1  | fd2  |

+-----+-----+

|   1   | 101 |

|   2   |   2   |

+-----+-----+

 

7.5.5 INSERT … SELECT … (p.506)

• 레코드를 읽어 그 결과를 insert 하는 법
• 주의 : 이 쿼리 사용할려면 읽는 테이블 대상 레코드에 읽기 잠금 필요

 

INSERT INTO temp_employees SELECT * FROM employees LIMIT 10;

 

7.5.6 LOAD DATA(LOCAL) INFILE …

• CSV 파일 포맷 또는 일정한 규칙을 지닌 구분자로 구분된 데이터 파일을 읽어 MySQL 서버의 테이블로 적재

 

• CSV 파일 포맷
• 10001, “1943-04-23”, “HYEONSEOK”, “LIM”, “M”, “2015-02-09”
• 10001, “1943-04-23”, “SEHYEONG”, “KIM”, “F”, “2015-02-09”
• …

 

7.6 UPDATE

7.6.1 UPDATE … ORDER BY … LIMIT n (p. 516)

• MySQL 에서는 update 문장에order by 절과 limit 절을 동시에 사용해 특정값으로 정렬해 그 중 상위 몇건만 업데이트 하는게 가능

 

UPDATE salaries

           SET salary=salary * 1.1

ORDER BY salary DESC LIMIT 10;

 

사용자 변수 이용하면…

 

SET @ranking := 0;

UPDATE salaries

           SET ranking = (@ranking := @ranking + 1)

ORDER BY salary DESC;

 

이런식으로 아주 유용하게 만들수 잇음 -> 자세한 얘기는 챕터 9에서...to be continued...

 

• 주의 : LIMIT 절은 있지만 ORDER BY 절이 없는 update 문장은 복제에서 마스터 역할을 하는 MySQL 서버에서 사용하지 않는 편이 좋아
• where  조건절에 일치하는 레코드 가운데 일부를 변경하지만 레코드의 정렬 규칙이 없기 때문에 업데이트 대상으로 선정되 레코드가 마스터와 슬레이브에서 100% 달라질수 밖에 없기 때문

 

7.6.2 JOIN UPDATE (p.517)

• 두 개 이상의 테이블을 조인해 조인된 결과 레코드를 업데이트 하는 쿼리
• 주의 :  join update 문장이 웹서비스 와 같은 OLAP 환경에서는 데드락을 유발할 가능성이 높으므로 사용하지 않는 것이 좋음
• 배치 프로그램이나 통계용 update 문장에는 유용하게 사용됨

 

CREATE TABLE tb_test1 (emp_no INT, first_name VARCHAR(14), PRIMARY KEY(emp_no));

 

INSERT INTO tb_test1

VALUES (10001, NULL), (10002, NULL), (10003, NULL), (10004, NULL);

 

UPDATE tb_test1 t1, employees e

SET t1.first_name= e.first_name

WHERE e.emp_no = t1.emp_no;

 

// 쿼리를 우선조인 하기 때문에 테이블의 조인 순서에 따라 update 문장의 성능이 달라짐

// MySQL 에서는 update 에 대해서는 실행 계획을 확인 할 수 없음

// 이럴때는 select 쿼리로 변경해 실행 계획을 확인 해봐야돼

SELECT * FROM tb_test1 t1, employees e

WHERE e.emp_no = t1.emp_no;

• 주의 : JOIN UPDATE 문장에는 group by 나 order by 절을 사용 할 수 없음

 

UPDATE departments d, dept_emp de

SET d.emp_count = COUNT(*)

WHERE de.dept_no = d.dept_no

GROUP BY de.dept_no;    // 이러지마 에러

 

UPDATE departments d,

           (SELECT de.dept_no, COUNT(*) AS emp_count FROM dept_emp de GROUP BY de.dept_no) dc

SET d.emp_count = dc.emp_count

WHERE dc.dept_no = d.dept_no;

 

• STRAIGHT JOIN : 조인의 순서를 결정 (왼쪽: 드라이빙 / 오른쪽 : 드리븐)

7.7 DELETE

• 조인을 이용해 두 개 이상의 테이블에서 동시에 레코드 삭제 가능 / 정렬해서 상위 몇건만 삭제도 가능

 

7.7.1 DELETE … ORDER BY … LIMIT n (p.520)

• update 문장이랑 유사

 

DELETE FROM employees ORDER BY first_name LIMIT 10;

 

7.7.2 JOIN DELETE (p. 520)

• 여러 테이블을 조인해서 레코드를 삭제
• 하나의 테이블에서 레코드 삭제

 

DELETE e FROM employees e, dept_emp de, departments d

WHERE e.emp_no = de.emp_no AND de.dept_no = d.dept_no

AND d.dept_no = 'd001';

 

• 3개의 테이블 모두 삭제

 

DELETE e, de, d

FROM employees e,  dept_emp de, departments d

WHERE e.emp_no  = de.emp_no AND de.dept_no = d.dept_no

AND d.dept_no = 'd001';

 

7.8 스키마 조작 (DDL)

• 데이터베이스의 구조 및 정의를 생성하거나 변경하는 쿼리

 

7.8.1 데이터베이스 (p. 522)

• 데이터베이스 생성 (p.522)
• IF NOT EXISTS : 이미 있다면 해당 문장 무시

 

CREATE DATABASE IF NOT EXISTS employees;

 

• 데이터베이스 목록(p.523)
• 데이터베이스 목록 나열

 

SHOW DATABASES;

 

• 데이터베이스 선택 (p.523)
• 기본 데이터베이스 선택

 

USE employees;

 

• 데이터 베이스 속성 변경 (p.523)
• 생성할 때 지정한 문자 집합이나 콜레이션 변경

 

ALTER DATABASES employees CHARACTER SET=euckr COLLATE= euckr_korean_ci;

 

• 데이터베이스 삭제 (p.523)
• IF EXISTS : 해당 DB 존재하면 삭제 아니면 무시

 

DROP DATABSE IF EXISTS employees;

 

7.8.2 테이블

• 테이블 구조 조회 (p.525)

 

SHOW CREATE TABLE employees ; // 칼럭의 목록과 인덱스, 외래키 정보를 동시에 보여줌

 

CREATE TABLE `employees` (

 `emp_no` INT(11) NOT NULL,

 `birth_date` DATE NOT NULL,

 `first_name` VARCHAR(14) NOT NULL,

 `last_name` VARCHAR(16) NOT NULL,

 `gender` ENUM('M','F') NOT NULL,

 `hire_date` DATE NOT NULL,

 PRIMARY KEY (`emp_no`),

 KEY `idx_first_name` (`first_name`)

) ENGINE=INNODB DEFAULT CHARSET=latin1

 

DESC employees; // 인덱스, 외래키 테이블 자체의 속성을 보여주지는 않음

 

• 테이블 구조 변경 (p.526)
• ALTER TABLE : 테이블 자체의 속성을 변경 뿐 아니라 인덱스의 추가 삭제, 칼럼 추가 삭제 하는 용도로 사용

 

ALTER TABLE employees CHARACTER SET ‘euckr’; // 기존 문자열을 변경하는게 아니고 앞으로 추가 될 문자열 변경

 

• RENAME TABLE (p.528)
• 테이블의 이름 변경

 

CREATE TABLE temp_emp_stat(hire_year INT NOT NULL, emp_count INT);

 

RENAME TABLE emp_stat TO backup_emp_stat, temp_emp_stat TO emp_stat;

 

// 하나의 rename table 명령으로 두 테이블의 이름 변경 작업을 처리

           MySQL 은 네임 락이라는 잠금 이용해 rename table 명령에 있는 테이블에 잠금 걸고

           작업 진행. 테이블을 바꾸는 동안 잠시 테이블이 없어질수 있지만 select 쿼리시

테이블이 없다는 에러를 받는게 아니고 rename table 이 완료될때 까지 기다림

 

• 테이블의 DB 변경
• 테이블 이름 변경 뿐 아니라 A db 에서 B db 로 옮길때도 사용

 

RENAME TABLE db1.employees TO db2.employees;

 

• 테이블의 상태 조회 (p.530)
• 어떤 스토리지 엔진 사용하는지 데이터 포맷을 뭘 사용하는지 등등을 조회 가능

 

SHOW TABLES status LIKE ‘employees’\G;

 

\G : 레코드의 칼럼을 라인당 하나씩 표현하게 하는 옵션

 

• 테이블 구조 복사
• CREATE TABLE LIKE 이용하면 구조 같은 테이블 생성 가느

 

CREATE TABLE temp_employees LIKE employees;

 

• 테이블 구조 및 데이터 복사
• CREATE TABLE … AS SELECT 명령 이용해서 다른 테이블로 부터 SELECT 된 결과를 이용해 새로운 테이블 생성 가능

 

CREATE TABLE temp_empolyees (

           birth_date DATE not null…

           ...

 

) ENGINE = INNODB

AS

SELECT emp_no, birth_date, first name.. from employees LIMIT 10;

 

CREATE TABLE AS SELECT 명령은 SELECT 되는 칼럼과 저장할 칼럼의 이름이 일치해야 함

 

• 테이블 삭제
• 레코드 건수가 많은 테이블을 삭제하는 작업은 상당히 부하가 큰 작업 -> 서비스 도중에 삭제 작업(DROP TABLE )은 수해하지 않는게 좋음

 

7.8.3 칼럼 변경

• 칼럼 추가
• ALTER TABLE employees ADD emp_telno varchar(20);
• 칼럼 삭제
• ALTER TABLE employees DROP [COLUMN] emp_telno;
• 칼럼 이름이나 칼럼 타입 변경
• 칼럼을 변경하는 경우
• ALTER TABLE employees CHANGE [COLUMN] first_name name VARCHAR(14) NOT NULL;
• 실제 테이블의 데이터는 변경하지 않고 테이블의 구조 정보(메타 정보)만 변경
• 칼럼명 이외의 타입이나 NULL 여부를 변경하는 경우
• ALTER TABLE employees MODIFY COLUMN first_name VARCHAR(200) NOT NULL;
• 칼럼 타입이나 null 여부 변경 할때는 change column 대신 modify column  사용
• 칼럼 변경을 위한 ALTER TABLE 진행 상황
• MySQL 서버 상태 값을 보면 현재 어느정도 ALTER TABLE 이 진행됐고 얼마나 더 기다려야 할지 대략적으로 예측 가능
• ALTER TABLE 명령을 이용해 테이블의 칼럼을 추가/삭제 할때는 MySQL 서버에 두개의 커넥션을 만들어 하나에는 ALTER TABLE 명령을 실행하고 다른 한쪽에는 SHOW GLOBAL STATUS LIKE ‘Handler%’ 명령을 실행하면서 진행상황을 확인 하면 됨

 

7.8.4 인덱스 변경

• 인덱스 조회 : SHOW INDEX FROM employees;
• 인덱스 삭제 : ALTER TABLE employees DROP INDEX ix_emptelno;
• 칼럼 및 인덱스 변경을 모아서 실행
• ALTER TABLE employees
• DROP INDEX ix_fristname,
• ADD INDEX ix_new_firstname(first_name),
• ADD COLUMN emp_telno VARCHAR(15);

 

7.8.5 프로세스 조회

• SHOW PROCESSLIST 로 서버에 접속된 사용자의 목록이나 각 클라이언트 사용자가 어떤 쿼리를 실행하고 있는지 확인 할 수 있음

7.8.6 프로세스 강제 종료

• SHOW PROCESSLIST 에서 Id 는 접속된 커넥션의 요청을 처리하는 전용 스레드 번호를 의미함  만약 특정 스레드에서 실행중인 쿼리나 커넥션 자체를 강제 종료 하려면….
• KILL QUERY 4228;    // 4228 번 스레드 종료
• KILL 4228;
•  

7.8.7 시스템 변수 조회 및 변경

• 시스템 변수는 SHOW VARIABLES 라는 명령으로 조회 가능    
• SHOW GLOBAL VARIABLES;
• SHOW GLOBAL VARIABLES LIKE ‘%timeout%’;

7.8.8 경고나 에러 조회

• 쿼리 실행중 에러가 발생하면 쿼리 실행을 중지하고, 에러 메세지를 화면에 자동으로 표시 : SHOW WARNINGS…
• INSERT INTO departments(dept_no, dept_name) VALUES ('12345', 'TestDepartments');
• SHOW WARNINGS; // 워닝의 경우
• 에러메세지가 표시되지 않는 경우 “SHOW ERRORS” 명령으로 조회 가능

 

7.8.9 권한 조회 (p.546)

• 사용자에게 부여할 수 있는 모든 권한의 이름과 간단한 설명이 표시됨

• SHOW PRIVILEGES;

 

7.9 SQL 힌트

• 성능 개선 위해 사용됨

7.9.1 힌트의 사용법

• 잘못 사용하면 오류 발생 - 주의 하자

 

SELECT * FROM employees USE INDEX (PRIMARY) WHERE emp_no = 10001;

 

SELECT * FROM employees /*! USE INDEX (PRIMARY) */ WHERE emp_no = 10001;

 

이런식으로 /* 뒤에 바로 ! 표시해서 힌트가 기술 될거란걸 SQL 서버에 알려줌

주석이 SQL 의 일부로 해석됨

 

7.9.2 STRAIGHT_JOIN

• 조인의 순서를 고정하는 역할

 

SELECT *

FROM employees e, dept_emp de, departments d

WHERE  e.emp_no = de.emp_no AND d.dept_no = de.dept_no;

 

이 쿼리의 경우 어느테이블이 드라이빙이 되고 드리븐이 될지 알수 없음

 

SELECT /*! STRAIGHT_JOIN */ e.frist_name, e.last_name, d.dept_name

FROM employees e, dept_emp de, departments d

WHERE e.emp_no = de.emp_no AND d.dept_no = de.dept_no;

 

employees -> dept_emp -> departments 순으로 조인이 수행됨

 

힌트가 있으면 옵티마이저는 힌트를 맹신하고 그 힌트에 맞게 쿼리를 실행 함

 

7.9.3 USE INDEX / FORCE INDEX / IGNORE INDEX

• USE INDEX : 옵티마이저에게 특정테이블의 인덱스를 사용하도록 권장
• FORE INDEX : USE INDEX 보다 옵티마이저에게 미치는 영향이 더 큰 힌트
• IGNORE INDEX : USE INDEX 나 FORCE INDEX 와는 반대로 특정 인덱스를 사용하지 못하게 하는 용도로 사용
• USE INDEX FOR JOIN : 테이블 간의 조인 뿐 아니라 레코드를 검색하는 용도까지 포함
• USE INDEX FOR ORDER BY : 명시된 인덱스를 ORDER BY 용도로만 사용하도록 제한
• USE INDEX FOR GROUP BY : 명시된 인덱스를 GROUP BY 용도로만 사용하도록 제한

 

7.9.4 SQL_CACHE / SQL_NO_CACHE

• SELECT 쿼리의 결과를 쿼리 캐시에 담아 둘지 여부를 쿼리에서 직접 선택하는데 이때 사용하는 힌트가 SQL_CACHE , SQL_NO_CACHE
• SQL_NO_CACHE 는 성능을 비교하거나 분석하는데 사용
• SELECT COUNT(*) FROM employees WHERE last_name = “faf”;
• 두번째부터는 쿼리 사용해서 소요시간 확인 애매해.. 이럴때는...
• SELECT /*! SQL_NO_CACHE */ COUNT(*) FROM employees WHERE last_name = “faf”;
• 쿼리 캐시 결과는 사용하지 않고 MySQL 서버가 쿼리를 실행해서 그 결과를 가져오는 전체 과정에서 소요된 시간 확인 할 수 있음

7.9.5 SQL_CALC_FOUND_ROWS

• SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리를 실행한 다음  FOUND_ROWS() 라는 함수를 이용해 LIMIT 절과 상관 없이 조건에 일치하는 레코드가 몇건이었는지 가져 올 수 있음

 

SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;

// 처음엔 5개만

SELECT FOUND_ROWS() ;

// 두번째엔 limit 와 관계없이 처음 select 쿼리 만족했던 모든 레코드 건수를 화면에 출력

 

7.9.6 기타 힌트

• 시간이 아주 남아돌면 매뉴얼을 토대로 천천히 공부해 보기 바람 by 저자

 

7.10 쿼리 성능 테스트

 

7.10.2 쿼리의 성능 테스트

• SELECT SQL_NO_CACHE STRAIGHT_JOIN
• e.first_name, d.dept_name
• FROM employees e, dept_emp de, departments d
• WHERE e.emp_no = de.emp_no AND d.dept_no = de.dept_no;
• 출력할때까지 엄청난 시간이 걸림 이런거보단
• SELECT SQL_NO_CACHE COUNT(*) FROM
• ( SELECT STRAIGHT_JOIN
• e.first_name, d.dept_name
• FROM employees e, dept_emp de, departments d
• WHERE e.emp_no = de.emp_no AND d.dept_no = de.`dept_no`) dt;
• 임시테이블을 사용하게 되면서 속도가 엄청 빨리짐, but 조금의 오차는 생길수 잇어

 

7.10.3 쿼리 프로파일링

• MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 잇으면 쿼리의 성능을 예측하거나 개선하는데 많은 도움이 될것. 이를 위해 MySQL 은 쿼리 프로파일링 기능을 제공

• 프로파일링을 활성화 하는 방법 : SET PROFILING
• SHOW VARIABLES LIKE ‘PROFILING’;
• 프로파일링 활성화 set profiling = 1;
• 저장된 프로파일링 정보는 한꺼번에 모든것을 볼수 있고 각 쿼리별로 시스템 자원의 영역별로 구분해서 조회하는 것도 가능
  
  
  

SELECT * FROM employees WHERE emp_no=10001;

 

SHOW PROFILES;