20장. SQL명령문의 최적화 1. OR연산자의 사용을 피하라. 2. 불필요한 UNION연산자의 사용을 피하라.

Presentation on theme: "20장. SQL명령문의 최적화 1. OR연산자의 사용을 피하라. 2. 불필요한 UNION연산자의 사용을 피하라."— Presentation transcript:

1 20장. SQL명령문의 최적화 1. OR연산자의 사용을 피하라. 2. 불필요한 UNION연산자의 사용을 피하라.
3. NOT연산자를 피하라 조건에 열을 분리하라. 5. BETWEEN연산자를 사용하라 LIKE 연산자의 특별한 형식을 피하라. 7. 조인에 여분의 조건을 추가하라 HAVING절을 피하라. 9. 가능한 작은 SELECT절을 만들어야 한다. 10. DISTINCT사용을 피하라 자료형의 변화를 피하라. 12. 가장 큰 테이블을 마지막에 위치하라. 13. ANY와 ALL 연산자의 사용을 피하라. 14. 미래의 최적화기 15. 디스크 최적화 16. 운영체제의 최적화 쉽게 배우는 MySQL 5.x

2 20.1 OR 연산자의 사용을 피하라. -WHERE 절의 조건에 OR 연산자가 있다면 SQL은 인덱스를 사용하지 않는다.
-IN 연산자를 가지고 있는 조건으로 대치 -UNION으로 연결된 2개의 SELECT 명령문을 사용 [예제20-1] , , 번의 학번과 이름, 주민등록번호를 출력하라. mysql> select stu_name, id_num, stu_no     -> from student     -> where stu_no = ' '     -> or stu_no = ' '     -> or stu_no = ' '; | stu_name | id_num         | stu_no   | | 김유신   | | | | 연개소문 | | | | 이순신   | | | 3 rows in set (0.02 sec) STU_NO 열이 인덱스로 정의되어 있어도 인덱스를 사용하지 않음 SELECT 명령문의 조건을 IN 연산자로 사용하면 SQL은 인덱스를 사용 UPDATE나 DELETE 명령문에 대해서도 동일하게 적용된다. mysql> select stu_name, id_num, stu_no     -> from student     -> where stu_no in ( , , ); in연산자 사용하면 인덱스 사용하여 질의 쉽게 배우는 MySQL 5.x

3 [예제 20-2] 1988년에 출생하거나 2반인 학생의 학번과 이름, 반, 출생년도를 학번 오름차순으로 출력하라.
mysql> select stu_no, stu_name, class, birth_year     -> from student     -> where birth_year = '1988'     -> or class = 2     -> order by stu_no; | stu_no   | stu_name | class | birth_year | | | 정인정   |     2 | 1983       | | | 홍길동   |     3 | 1988       | | | 유하나   |     1 | 1988       | | | 김문영   |     3 | 1988       | | | 최차영   |     2 | 1988       | 5 rows in set (0.00 sec) -CLASS과 BIRTH_YEAR 열에 대한 인덱스의 존재에 관계없이 순차적 처리 기법으로 전개 -OR 연산자를, UNION으로 결합된 2개의 SELECT 명령문으로 대체할 수 있다. -OR 연산자를 가지고 있는 UPDATE와 DELETE 명령문은 UNION 연산자로 대체할 수 없다. mysql> select stu_no, stu_name, class, birth_year     -> from student     -> where birth_year = '1988'     -> union     -> select stu_no, stu_name, class, birth_year     -> where class = 2     -> order by stu_no; 쉽게 배우는 MySQL 5.x

4 위의 예제의 결과를 확인하기 위해서 다음과 같은 SELECT명령문을 사용
[예제 20-3] 장학금이 200,000원 이하이거나 2007년08월10일에 등록한 학생의 장학금을 100,000원으로 갱신하라.(OR연산자 사용) mysql> update fee     -> set jang_total =     -> where jang_total <=     -> or fee_date = ' '; Query OK, 7 rows affected (0.02 sec) Rows matched: 7  Changed: 7  Warnings: 0 위의 예제의 결과를 확인하기 위해서 다음과 같은 SELECT명령문을 사용 mysql> select stu_no, fee_year, fee_term, jang_total, fee_date     -> from fee; | stu_no   | fee_year | fee_term | jang_total | fee_date   | | | 2000     |        1 |    | | | | 2000     |        2 |    | | | | 2001     |        1 |    | | | | 2001     |        2 |    | | | | 2006     |        1 |    | | | | 2006     |        2 |    | | | | 2007     |        1 |    | | | | 2007     |        2 |    | | | | 2006     |        1 |     | | | | 2006     |        2 |    | | | | 2007     |        1 |    | | | | 2007     |        2 |     | | | | 2007     |        1 |     | | | | 2007     |        2 |     | | | | 2007     |        1 |     | | | | 2007     |        2 |     | | | | 2007     |        1 |     | | | | 2007     |        2 |     | | | | 2007     |        1 |     | | | | 2007     |        2 |     | | | | 2007     |        1 |     | | | | 2007     |        2 |     | | | | 2007     |        1 |     | | | | 2007     |        2 |     | | 24 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

5 UPDATE 명령문을 사용할 때 UNION연산자를 사용한 경우 오류가 발생된다.
mysql> update fee     -> set enter_fee =     -> where jang_total >=     -> union     -> update fee     -> where fee_date = ' '; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union update fee set enter_fee = where fee_date = ' '' at line 4 UPDATE 명령문을 사용할 때 UNION연산자를 사용한 경우 오류가 발생된다. UPDATE FEE              SET ENTER_FEE = 200,000 WHERE jang_TOTAL >= 200,000; UPDATE FEE                SET  ENTER_FEE = 200,000 WHERE REG_DATE = ' '; 쉽게 배우는 MySQL 5.x

6 20.2 불필요한 UNION 연산자의 사용을 피하라. UNION 연산자는 주의해서 사용되어야 한다.
[예제20-5] 각 학생에 대하여 학번, 등록금과 장학금의 차를 출력하라. mysql> select stu_no, fee_price - jang_total     -> from fee     -> where fee_total >= jang_total     -> union     -> select stu_no, fee_price - jang_total     -> where fee_total < jang_total; | stu_no   | fee_price - jang_total | | |                | | |                   5000 | | |                 | | |                 | | |                | | |                | | |                 | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | | |                | 21 rows in set (0.01 sec) -문제점은 이 명령문을 처리하는 동안 SQL은 전체 FEE 테이블을 두 번 검색해야 한다. -이러한 연산자에 ALL 옵션을 추가함으로써 결과에서 중복된 행을 삭제하지 않도록 하는 효과를 얻을 수 있다. 쉽게 배우는 MySQL 5.x

7 ALL 옵션을 추가함으로써 중복된 행을 삭제하지 않도록 하는 효과를 얻을 수 있다.
[예제20-6]등록년도가 ‘2000’, ‘2001’에 등록한 학생의 학번과 등록년도를 모두 출력하라. (union all 사용) mysql> select stu_no, fee_year     -> from fee     -> where fee_year = '2000'     -> union all     -> select stu_no, fee_year     -> where fee_year = '2001'; | stu_no   | fee_year | | | 2000     | | | 2001     | 4 rows in set (0.00 sec) ALL 옵션을 추가함으로써 중복된 행을 삭제하지 않도록 하는 효과를 얻을 수 있다. 쉽게 배우는 MySQL 5.x

8 20.3 NOT 연산자를 피하라. -WHERE 절에 있는 조건에 NOT 연산자가 있으면 인덱스를 사용하지 않음
[예제 20-7] 1984년 이후에 태어난 학생의 학번과 이름, 출생년도를 출력하라. mysql> select stu_no, stu_name, birth_year     -> from student     -> where not  birth_year < 1984; | stu_no   | stu_name | birth_year | | | 고혜진   | 1987       | | | 김영호   | 1986       | | | 장수인   | 1989       | | | 홍길동   | 1988       | | | 이순신   | 1989       | | | 유하나   | 1988       | | | 김문영   | 1988       | | | 최차영   | 1988       | 8 rows in set (0.08 sec) WHERE 절의 not  birth_year < 1984을 대체 WHERE BIRTH_YEAR >= 1984 쉽게 배우는 MySQL 5.x

9 [예제 20-8] 남자가 아닌 학생의 학번과 이름, 주민등록번호를 출력하라.
mysql> select stu_no, stu_name, id_num      -> from student      -> where not (substring(id_num, 8, 1) = '1'); | stu_no   | stu_name | id_num         | | | 김유미   | | | | 정인정   | | | | 고혜진   | | | | 유하나   | | | | 김문영   | | | | 최차영   | | 6 rows in set (0.00 sec) 성별은 오직 1('남')과 2('여')만의 값을 가질 수 있다는 것을 알고 있다. 따라서 명령문은 다음과 같이 작성된다.     -> from student     -> where substring(id_num, 8, 1) = '2'; 쉽게 배우는 MySQL 5.x

10 20.4 조건에 열을 분리하라. [예제 20-9] 1990년 보다 3년 전에 태어난 학생의 학번, 이름, 출생년도를 출력하라.
20.4 조건에 열을 분리하라. [예제 20-9] 1990년 보다 3년 전에 태어난 학생의 학번, 이름, 출생년도를 출력하라. mysql> select stu_no, stu_name, birth_year     -> from student     -> where birth_year + 3 = 1990; | stu_no   | stu_name | birth_year | | | 고혜진   | 1987       | 1 row in set (0.03 sec) 수치계산이나 스칼라 함수에서 사용된 열에 인덱스가 정의되었을 때 그 인덱스는 사용되지 않는다. BIRTH_YEAR 열에 대한 인덱스사용은 다음과 같은 형식에서만 기대할 수 있다. mysql> SELECT STU_NO, STU_NAME, BIRTH_YEAR     -> FROM STUDENT     ->  WHERE BIRTH_YEAR = 1987; 관계 연산자 왼쪽에 있는 수식은 오직 하나의 열의 이름만 가지고 있다. 다시 말하면 열이 분리되어 있다. (인덱스 사용 가능) 쉽게 배우는 MySQL 5.x

11 20.5 BETWEEN 연산자를 사용하라. -WHERE 절의 조건에서 AND 연산자를 사용하면 인덱스를 사용하지 않음
-AND조건을 BETWEEN 연산자를 가지는 조건으로 대체하여 인덱스 사용 [예제 20-10] 1983년부터 1987년까지 태어난 학생의 학번, 이름, 출생년도를 출력하라. mysql> select stu_no, stu_name, birth_year     -> from student     -> where birth_year >= 1983     -> and birth_year <= 1987; | stu_no   | stu_name | birth_year | | | 김유미   | 1983       | | | 정인정   | 1983       | | | 박정인   | 1983       | | | 고혜진   | 1987       | | | 김영호   | 1986       | 5 rows in set (0.00 sec) BIRTH_YEAR 열에 대한 인덱스는 사용되지 않음 mysql> SELECT  STU_NO, NAME, BIRTH_YEAR     ->  FROM  STUDENT     ->  WHERE BIRTH_YEAR BETWEEN 1983 AND 1987; BETWEEN 연산자를 사용하여 인덱스 사용 쉽게 배우는 MySQL 5.x

12 20.6 LIKE 연산자의 특별한 형식을 피하라. -WHERE 절의 조건에서 LIKE 연산자를 사용하면 인덱스를 사용하지 않음
[예제 20-11] 영문이름의 끝이 문자 'g'인 학생의 학번, 영문이름을 출력하라. mysql> select stu_no, stu_ename     -> from student     -> where stu_ename like '%g'; | stu_no   | stu_ename       | | | Jeung Yin-Jeung | | | Hong Gil-Dong   | | | Kim Moon-Young  | | | Choi Cha-Young  | 4 rows in set (0.00 sec) 인덱스는 사용되지 않을 것이며, 이 예제를 대체할 만한 해결책이 없다. 쉽게 배우는 MySQL 5.x

13 20.7 조인에 여분의 조건을 추가하라. -WHERE 절에 최종 결과를 변경시키지 않는 여분의 조건을 추가함으로써 조인을 빠르게 할 수 있다. [예제 20-12] 학번 번에 부과된 모든 등록금에 대한 학번, 이름, 등록년도, 등록학기, 등록일자를 출력하라. mysql> select f.stu_no, stu_name, fee_year, fee_term, fee_date     -> from fee f, student s     -> where f.stu_no = s.stu_no     -> and f.stu_no = ' '; | stu_no   | stu_name | fee_year | fee_term | fee_date   | | | 최차영   | 2007     |        1 | | | | 최차영   | 2007     |        2 | | 2 rows in set (0.06 sec) mysql> SELECT S.STU_NO, STU_NAME, FEE_YEAR, FEE_TERM, FEE_DATE     ->  FROM   FEE F, STUDENT S     ->  WHERE  F.STU_NO = S.STU_NO     ->  AND F.STU_NO = ' '     ->  AND S.STU_NO = ' '; 여분의 조건을 가지도록 확장하여도 동일한 결과 출력 쉽게 배우는 MySQL 5.x

14 20.8 HAVING 절을 피하라. -SELECT 명령문에서는 WHERE 절과 HAVING 절이 있다.
[예제 20-13] 학번이 번 보다 큰 각 학생에 대하여 학번과 등록금을 납입한 횟수를 출력하라 mysql> select stu_no, count(*)     -> from fee     -> group by stu_no     -> having stu_no > ' '; | stu_no   | count(*) | | |        2 | 1 row in set (0.00 sec) HAVING 절에서 나타낸 조건은 WHERE 절에서도 지정할 수 있다. mysql> SELECT STU_NO, COUNT(*)       ->  FROM FEE     ->  WHERE STU_NO > ' '     ->  GROUP BY STU_NO; 쉽게 배우는 MySQL 5.x

15 20.9 가능한 작은 SELECT 절을 만들어야 한다. -부속질의어 사용시 주 질의어의 SELECT 절은 출력될 데이터를 구성
-불필요한 열을 사용하면 처리 속도를 저하시키는 원인이 된다. -부속 질의어가 EXISTS 연산자를 사용하면 SELECT 절의 결과는 지정된 수식에 영향을 받지 않는다. [예제 20-14] 적어도 한 번 등록을 한 학생의 학번과 이름을 출력하라. mysql> select stu_no, stu_name     -> from student s     -> where exists     -> (select '1'     -> from fee f     -> where f.stu_no = s.stu_no); | stu_no   | stu_name | | | 박도준   | | | 박정인   | | | 장수인   | | | 홍길동   | | | 이순신   | | | 유하나   | | | 김문영   | | | 최차영   | EXISTS 연산자를 사용할 때는  부속질의어의 SELECT 명령문에서 ‘1’ 은 아무런 의미가 없이 존재유무만을 나타내므로 되도록 간단하게 표현한 것이다. 쉽게 배우는 MySQL 5.x

16 20.10 DISTINCT 사용을 피하라. -DISTINCT를 지정하는 것은 중복된 행을 제거하나, 처리 시간에는 비효율적
[예제 20-15] 2006년에 등록금을 납부한 학생의 학번, 이름, 등록년도, 등록학기, 등록일자를 출력하라. mysql> select distinct f.stu_no, stu_name, fee_year,     -> fee_term, fee_date     -> from fee f, student s     -> where f.stu_no = s.stu_no     -> and fee_year = 2006 ; | stu_no   | stu_name | fee_year | fee_term | fee_date   | | | 박도준   | 2006     |        1 | | | | 박도준   | 2006     |        2 | | | | 박정인   | 2006     |        1 | | | | 박정인   | 2006     |        2 | | 4 rows in set (0.00 sec) SELECT 절은 STUDENT 테이블의 기본 키에 대한 조건과 마찬가지로 FEE 테이블의 기본 키를 가지고 있기 때문에 여기서 DISTINCT는 불필요하다. 쉽게 배우는 MySQL 5.x

17 20.11 자료형의 변환을 피해야 한다. 20.12 가장 큰 테이블을 마지막에 위치하라.
자료형의 변환을 피해야 한다. -SQL은 자동적으로 자료형을 변환한다. 예를 들면, 수치형 GRADE 열이 문자열 리터럴과 비교할 수 있다면 다음 조건은 정확하다. WHERE GRADE = '3' -자료형을 변환하는 것은 처리 속도에 역효과를 내므로 피하는 것이 좋다. 가장 큰 테이블을 마지막에 위치하라. -Join을 구성할 때 FROM 절에서 테이블의 순서는 처리 속도에 영향을 준다. -FROM 절에서 가장 큰 테이블을 마지막에 두는 것이 처리속도 향상 FROM  STUDENT, SUBJECT STUDENT 테이블이 SUBJECT 테이블보다 더 크기 때문에 다음과 같이 바꾸어 준다. FROM  SUBJECT, STUDENT 쉽게 배우는 MySQL 5.x

18 20.13 ANY와 ALL 연산자의 사용을 피하라. -ALL 연산자를 가지고 있는 조건을 처리할 때 인덱스를 사용하지 않는다.
-가능하다면 ALL 연산자를 통계 함수 MIN 이나 MAX로 대체하는 것이 좋다. [예제 20-16] 가장 나이가 많은 학생의 학번, 이름, 출생 년도를 출력하라. mysql> select stu_no, stu_name, birth_year     -> from student     -> where birth_year <= all     -> (select birth_year     -> from student); | stu_no   | stu_name | birth_year | | | 이상길   | 1975       | 1 row in set (0.09 sec) 여기서 ALL 연산자를 MIN 함수로 대치할 수 있다.     -> where birth_year =     -> (select min(birth_year) 쉽게 배우는 MySQL 5.x

19 [예제 20-17] 가장 나이가 많은 학생을 제외한 학생의 학번, 이름, 생년을 출력하라.
mysql> select stu_no, stu_name, birth_year     -> from student     -> where birth_year > any     -> (select birth_year     -> from student); | stu_no   | stu_name | birth_year | | | 김유신   | 1981       | | | 박도준   | 1978       | | | 김유미   | 1983       | | | 정인정   | 1983       | | | 연개소문 | 1981       | | | 박정인   | 1983       | | | 고혜진   | 1987       | | | 김영호   | 1986       | | | 장수인   | 1989       | | | 홍길동   | 1988       | | | 이순신   | 1989       | | | 유하나   | 1988       | | | 김문영   | 1988       | | | 최차영   | 1988       | 14 rows in set (0.00 sec) 여기서 ANY 연산자를 MIN 함수로 대치할 수 있다. mysql> SELECT STU_NO, STU_NAME, BIRTH_YEAR     -> FROM   STUDENT     -> WHERE  BIRTH_YEAR >     -> (SELECT MIN(BIRTH_YEAR)     ->  FROM  STUDENT); 쉽게 배우는 MySQL 5.x

20 미래의 최적화기 -제 20 장에서는 최적화기가 아직은 최적이 아니라는 것을 명확히 보여준다. -어떤 경우에 최적화기는 가장 효율적인 처리 기법을 결정하지 못하기 때문에 이는 처리 시간을 길게 할 수 있다. -연구를 통하여 향상된 최적화기를 만들고 있기 때문에 최근에 발표된 SQL 제품은 그 이전의 것 보다 많이 향상되었다. 디스크 최적화 -시스템, 프로그램, 임시 파일들을 위한 전용 디스크를 갖추어 저장 -내용이 자주 변경되면 갱신 기록, 트랜잭션 기록 파일을 별도의 디스크에 배치 -데이터베이스 디스크에 있어서는 빠른 탐색 시간(seek time)이 결정적인 요인 -매우 큰 데이터베이스에 경우, 디스크 탐색 속도에 의해 성능이 좌우 -데이터가 증가할 때 마다 탐색 수는 N log N 씩 증가한다. -임시파일 또는 쉽게 갱신될 수도 있는 데이터에 대해서 미러링이나 RAID(RAID 0는 예외)를 사용하지 않는다. 쉽게 배우는 MySQL 5.x

21 운영체제 최적화 -메모리 문제가 있다면 시스템이 적은 메모리를 사용하도록 설정하기 보다는 메모리를 증설하는 것이 좋다. -데이터는 NFS 디스크를 사용하지 않는다. (NFS locking 문제에 봉착 가능) -시스템과 SQL 서버를 위해 open file 한계 수치를 증가시킨다. -프로세스와 쓰레드의 제한 개수를 늘려준다. -큰 테이블을 사용할 일이 드물다면, 파일을 여러 실린더에 분산시켜 저장하지 않도록 설정한다. -솔라리스는 큰 파일을 지원하는 파일시스템을 사용한다. 쉽게 배우는 MySQL 5.x