데이터베이스 최적화 방법 1 (23.04.27)

옵티마이저의 종류와 DBMS 종류에 따라 다를순 있지만 일반적인 쿼리 튜닝방법에 대해서 정리한 글을 공유하려한다.

 

저장해두고 보기 위해 글 사용합니다.

OR 연산자의 사용을 피하라. 대부분의 경우에 WHERE 절의 조건에 OR 연산자를 가지고 있다면 SQL은 인덱스를 사용하지 않는다. 이러한 명령문은 두가지 방법으로 다시 작성할 수 있는데, 어떤 환경에서는 IN 연산자를 가지고 있는 조건으로 대치하거나 UNIO으로 연결된 두개의 SELECT 문을 사용하여 전체 명령문을 대치할 수 있다. 이렇게 대치하면 SQL은 인덱스를 사용할 것이다. 주의사항 : OR 연산자가 UNION으로 대체 될 때, 자동적으로 중복된 모든 행을 삭제한다.  원래의 SELECT 문에서 기본키를 포함하고 있다면 상관없다. WHERE HAKBUN='9941203' OR HAKBUN='9841101' OR HAKBUN='0042123' --> WHERE HAKBUN IN(9941203, 9841101, 0042123) 불필요한 UNION 연산자의 사용을 피하라. UNION 연산자의 명령문을 처리하는 동안 SQL은 전체 FEE테이블을 두번 검색해야 한다는 것이다. UNION에서 중복된 행을 모두 출력하게 만들 필요가 있다면 UNION ALL을 사용한다. NOT 연산자를 피하라. 만약 WHERE절에 있는 조건에 NOT연산자를 가지고 있다면 일반적으로 SQL은 인덱스를 사용하지 않을 것이다. 따라서 가능하다면 NOT연산자를 관계연산자로 대치하는게 좋을 것이다. WHERE NOT(BIRTH_YEAR < 1980) --> WHERE BIRTH_YEAR >= 1980 WHERE NOT(SUBSTR(JUMINNO, 8, 1)='1') --> WHERE (SUBSTR(JUMINNO, 8, 1)='2') 조건에 열을 분리하라.  계산 또는 스칼라 함수에서 사용된 열에 인덱스가 정의 되었을 때 그 인덱스는 사용되지 않는다. 1983년 보다 12년 전에 태어난 학생의 ....를 구하라 WHERE BIRTH_YEAR + 12 = 1983  --> WHERE BIRTH_YEAR = 1971 BETWEEN 연산자의 사용 특별한 범위에 포함된 값을 찾기 위해서 WHERE 절의 조건식 AND 연산자를 사용하여 탐색을 한다면 일반적으로 SQL은 인덱스를 사용하지 않을 것이다. 이러한 조건을 BETWEEN 연산자를 가지는 조건으로 대체할 수 있다. WHERE BIRTH_YEAR >=1971 AND BIRTH_YEAR < =1979 --> WHERE BIRTH_YEAR BETWEEN 1971 AND 1979 LIKE 연산자의 특별한 형식을 피하라 WHERE 절의 조건에서 LIKE 연산자와 함께 사용된 열에 대한 인덱스가 정의되었을 때 인덱스는 고려되지 않을 것이다. 만약 LIKE 연산자에 있는 마스크가 퍼센트 기호 또는 밑줄 기호와 함께 사용된다면 인덱스틑 사용할 수 없다. WHERE ENMAE LIKE '%n' 인덱스는 사용되지 않을 것이며 불행히도 이 예제를 대체할 만한 해결책이 없다. 조인에 여분의 조건을 추가하라 때로는 WHERE 절에 최종 결과를 변경시키지 않는 여분의 조건을 추가함으로써 조인을 빠르게 할 수 있다. where f.stu_no=s.stu_no and f.stu_no = '20071405' --> where f.stu_no=s.stu_no and f.stu_no = '20071405' and s.stu_no = '20071405' HAVING 절을 피하라 SELECT 명령문에서는 두 위치에서 조건을 지정할 수 있는데 이 위치는 WHERE절과 HAVING 절이다. 이 때 WHERE 절에는 가능하다면 많은 조건을 지정하려고 할 것이며 HAVING 절에는 가능하다면 적은 조건을 지정하려고 할 것이다. 이러한 이유는 HAVING 절에서 지정된 조건에 대해서는 인덱스를 사용할 수 없기 때문이다. SELECT HAKBUN, COUNT(*) FROM DUNGROK GROUP BY HAKBUN HAVING HAKBUN >='9911304'; 대신 WHERE 절을 사용하여 불필요한 HAVING절을 없애자 SELECT HAKBUN, COUNT(*) FROM DUNGROK WHERE HAKBUN >= '9911304' GROUP BY HAKBUN; 가능한 작은 SELECT 절을 만들어야 한다.  주 질의어의 SELECT 절은 출력될 데이터를 구성하기 위해서 사용된다. 따라서 불필요한 열을 사용한 것을 피해야 하는데, 이는 처리속도를 저하시키는 원인이 되기 때문이다. 만약 부속 질의어가 EXISTS 연산자를 사용하여 주 질의어에 결합되어 있다면 부속 질의어의 SELECT 절에서 여러개의 수식을 지정 할 수 있다. 그러나 SELECT 절의 결과는 지정된 수식에만 영향을 받지 않는다. 따라서 SELECT 절에는 하나의 리터럴로 구성된 단일 수식으로만 구성하는 것이 좋다. 예제)적어도 한 번 등록을 한 학생의 학번과 이름을 출력하라 SELECT HAKBUN, NAME FROM HAKBUN H WHERE EXITES (SELECT '1' FROM DUNGROK D WHERE D.HAKBUN = H.HAKBUN); 위의 예제에서 EXIST 연산자를 사용할 때에는 부속질의어 SELECT 명령문에서 '1'은 아무런 의미가 없이 존재 유무만을 나타내므로 되도록 간단하게 표현한 것이다. DISTINCT 사용을 피하라 SELECT 절에서 DISTINCT를 지정하는 것은 결과에서 중복된 행을 제거하기 위해서이다. 이는 처리시간에는 비효율적이다. 따라서 DISTINCT가 요구되지 않거나 불필요할 때는 DISTINCT 사용을 피해야 한다. SELECT 절에서 테이블의 기본 키를 가지고 있다면 DISTINCT 절은 불필요하다. 자료형의 변환을 피해야 한다.  SQL은 자동적으로 자료형을 변환한다. 예를 들면 수치형 GRADE 열이 문자열 리터럴과 비교할 수 있다면 다음 조건은 정확하다. WHERE GRADE='3' 자료형을 변환하는 것은 처리속도에 역효과를 낸다. 만약 이러한 변환형이 실제로 요구되지 않는다면 피하는 것이 좋다. 가장 큰 테이블을 마지막에 위치하라  JOIN을 구성할 때 FROM 절에서 테이블의 속도는 처리 속도에 영향을 줄 수 있다. 이 때 테이블의 순서를 결정하는 규칙은 FROM 절에서 가장 큰 테이블을 마지막에 두는 것이다. 다음의 FROM 절을 보자. FROM HAKJUK, GWAMOK 위의 경우보다 다음과 같이 바꾸는 것이 좋다. 그 이유는 HAKJUK 테이블이 GWAMOK 테이블보다 더 크기 때문이다. FROM GWAMOK, HAKJUK ANY와 ALL 연산자의 사용을 피하라.  많은 최적화기는 ALL 연산자를 가지고 있는 조건을 처리할 때 인덱스를 사용하지 않을 것이다. 가능하면 ALL 연산자를 통계 함수 MIN이나 MAX로 대체하는 것이 좋다. 예제) 가장 나이가 많은 학생의 학번, 이름, 출생년도를 출력하라 SELECT HAKBUN, NAME, BIRTH_YEAR FROM HAKJUK WHERE BIRTH_YEAR <= ALL (SELECT BIRTH_YEAR FROM HAKJUK); 여기서 ALL 연산자를 MIN 함수로 대치할 수 있다. SELECT HAKBUN, NAME, BIRTH_YEAR FROM BIRTH_YEAR = (SELECT MIN(BIRTH_YEAR FROM HAKJUK); 동일한 이유가 ANY 연산자에서도 적용된다. SELECT HAKBUN, NAME, BIRTH_YEAR FROM HAKJUK WHERE BIRTH_YEAR > ANY (SELECT BIRTH_YEAR FROM HAKJUK); 여기서 ANY 연산자를 MIN 함수로 대체할 수 있다. SELECT HAKBUN, NAME, BIRTH_YEAR FROM HAKBUN WHERE BIRTH> (SELECT MIN(BIRTH_YEAR) FROM HAKJUK); 미래의 최적화기 본 치적화기가 아직은 최적이 아니라는 것을 명확히 보여준다. 어떤 경우에 최적화기는 가장 효율적인 처리 기법을 결정하지 못하기 때문에 이는 처리 시간을 길게 할 수 있다. 이는 데이터베이스 언어로서 SQL을 데어터베이스에 적용하지 못할 뿐만 아니라 처리 기법 그 자체를 결정하지 못하는 경우가 발생한다.