DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
우선, 하나의 요소 (factor)가 모든 명령문에 영향을 준다: 퍼미션 (permission) 설정을 복잡하게 하면 할수록, 더 큰 오버헤드 (overhead)가 발생한다. GRANT 명령문을 사용할 때 보다 단순한 퍼미션을 사용하는 것이 클라이언트가 명령문을 실행할 때 퍼미션-검사 오버헤드를 낮추는 방법이다. 예를 들면, 테이블 레벨 또는 컬럼 레벨의 권한에 대해서는 전혀 승인을 하지 않으면, 서버는 tables_priv 및 columns_priv 테이블에 대해서는 전혀 검사를 하지 않게 된다. 비슷하게, 여러분이 어느 계정에 대해서는 자원 제한을 하지 않으면, 서버는 자원 카운팅을 실행하지 않는다. 만일 여러분이 매우 큰 명령문-처리 업무를 해야 한다면, 퍼미션-검사 오버헤드를 줄일 수 있도록 단순화된 승인 구조를 사용하는 것이 효율적인 것이다. 만일 특정 MySQL 수식 또는 함수에 문제가 있다면, mysql 클라이언트 프로그램을 사용해서 BENCHMARK() 함수를 호출하여 타이밍 테스트를 하도록 한다. 이것의 신텍스는 BENCHMARK(loop_count,expression)이다. 리턴 값은 항상 0 이다: 이 결과 값은 펜티엄(Pentium) II 400MHz 시스템을 가지고 얻은 것이다. 이것은 MySQL이 이 시스템에서 1,000,000개의 단순 추가 수식 (simple addition expression)을 처리하는데 0.32초가 걸렸다는 것을 나타내는 것이다. 모든 MySQL 함수는 고도로 최적화가 되어 있으나, 몇가지 예외는 있다. BENCHMARK()는 쿼리에 문제가 있을 경우에 이를 찾아내는 훌륭한 툴이다. EXPLAIN 명령문은 DESCRIBE에 대한 동의어로 사용할 수 있거나 또는 MySQL이 SELECT 명령문을 실행하는 방법에 대한 정보를 얻기 위한 수단으로 사용할 수가 있다: 이 섹션에서는 쿼리 실행 플랜 정보를 얻기 위한 EXPLAIN사용법을 설명한다. DESCRIBE 및 SHOW COLUMNS 명령문에 대한 설명은 Section 13.3.1, “DESCRIBE 신텍스”, 및 Section 13.5.4.3, “SHOW COLUMNS 신텍스”를 참조하기 바란다. EXPLAIN를 사용함으로써, 여러분은 테이블의 어느 곳에 인덱스를 추가해야만 열을 찾기 위한 SELECT가 보다 빠르게 되는지를 알 수 있게 된다. 또한, EXPLAIN를 사용하면 옵티마이저가 최적의 (optimal) 순서로 테이블을 조인 (join)할 수 있는지 여부도 검사할 수가 있다. SELECT 명령문에 명명되어 있는 테이블의 순서와 상응하게 조인 (join) 순서를 사용하도록 옵티마이저를 만들기 위해서는, 명령문에 SELECT만을 사용하는 대신에 SELECT STRAIGHT_JOIN을 사용해서 시작을 하도록 한다. 만일 여러분 생각에는 사용 되어야만 했을 인덱스가 사용되지 않은 상태로 문제를 일으키게 되면, 키의 기수 (cardinality)와 같은 테이블 상태를 업데이트 하기 위해 ANALYZE TABLE을 구동 시켜야 하는데, 이것은 옵티마이저의 선택에 영향을 미치게 된다. Section 13.5.2.1, “ANALYZE TABLE 신텍스”를 참조할 것. EXPLAIN은 SELECT 명령문에서 사용된 각 테이블 정보 열을 리턴한다. MySQL이 쿼리를 처리하는 동안 읽을 수 있도록 테이블들은 결과물 안에 목록으로 정리가 된다. MySQL 은 single-sweep multi-join 방법을 사용해서 모든 조인 (join)을 풀어 버린다 (reslove). 이것은 MySQL이 첫 번째 테이블에서 열을 읽고, 그 다음에 두 번째 테이블에서 매치 (match)가 되는 것을 찾으며, 세 번째, 네 번째 등으로 이동을 한다는 것을 의미하는 것이다. 모든 테이블을 처리한 후에, MySQL은 선택된 컬럼을 내 보내고 테이블에서 매치되는 다른 열이 있을 때까지 테이블을 역으로 검사한다. 그 테이블에서 그 다음 열을 읽고 다음 테이블로 이동을 해서 동일한 과정을 반복 진행한다. EXTENDED 키워드가 사용되면, EXPLAIN은 EXPLAIN 명령문 다음에 SHOW WARNINGS 명령문을 입력해서 볼 수 있는 기타 정보를 리턴한다. 이 정보는 옵티마이저가 SELECT 명령문에 있는 컬럼 이름과 테이블을 얼마나 많이 검증을 하였는지를 보여주며, SELECT는 최적화 과정에 관한 어플리케이션 재 작성과 최적화 규칙, 그리고 다른 가능한 노트 (notes)를 보여준다. EXPLAIN를 통해서 나오는 각각의 결과 열은 하나의 테이블에 대한 정보이며, 각 열은 아래 최적화가 실행되지 전에, EXPLAIN 명령문은 다음과 같은 정보를 생성한다: type 은 모든 테이블에 대해 ALL이기 때문에, 이 결과 값은 MySQL이 모든 테이블에 대해서 하나의 카르테시안 (Cartesian) 값을 생성하는 중이라는 것을 가리킨다; 즉, 열에 대한 모든 조합. 이 과정은 각각의 테이블에 있는 열의 숫자를 조사해야 하기 때문에 시간이 오래 걸리게 된다. 위의 경우에는, 74 × 2135 × 74 × 3872 = 45,268,558,720 열이 된다. 만일 컬럼 상의 인덱스들을 동일한 타입과 크기로 선언할 경우에는, MySQL이 이를 효과적으로 처리하는데 한가지 문제가 생긴다. 위의 문장에서 보면, VARCHAR 및 CHAR의 크기를 동일하게 선언하면, 이것들은 동일한 것으로 간주가 된다. tt.ActualPC는 CHAR(10)로 선언 되었고 et.EMPLOYID는 CHAR(15)로 선언 되었기 때문에, 길이가 맞지 않게 된다. 컬럼 길이 간의 이러한 문제를 해결 하기 위해서는, ALTER TABLE를 사용해서 ActualPC의 길이를 10 개 문자에서 15 문자로 늘리도록 한다: 이제 tt.ActualPC 및 et.EMPLOYID는 모두 VARCHAR(15)가 된다. EXPLAIN 명령문을 다시 실행하면 아래와 같은 결과를 얻게 된다: 이 결과 값은 완벽하지는 않지만 많이 개선이 된 것이다: rows 값은 74 보다 작다. 이와 같은 실행은 수초 정도 걸린다. 두 번째 대안으로는, tt.AssignedPC = et_1.EMPLOYID 와 tt.ClientID = do.CUSTNMBR 비교에서 컬럼 길이가 일치하지 않는 것을 제거하는 것이다: 이와 같이 수정을 하면, EXPLAIN은 다음과 같은 결과를 만들게 된다: 이렇게 하고 나면, 쿼리는 거의 최적화가 이루어진다. 이제 남아 있는 문제는, MySQL이 tt.ActualPC 컬럼에 있는 값들은 골고루 분포되어 있으나, tt 테이블에 대해서는 그렇지 않다고 가정하고 있다는 점이다. 다행스러운 것은, MySQL로 하여금 키 분포도를 분석하도록 만드는 것이 쉽다는 점이다: 추가적인 인덱스 정보를 사용하면, 조인 (join)은 완벽해 지고 EXPLAIN은 아래와 같은 결과를 만들게 된다: EXPLAIN 결과에 있는 rows 컬럼은 MySQL 조인 (join) 최적화를 통해서 개선된다는 점을 알아두자. rows 가 만들어 내는 값과 쿼리가 리턴하는 열의 실제 숫자를 비교함으로써 그 숫자가 거의 일치하는지를 검사해야 한다. 만일 그 숫자가 많이 차이가 난다면, SELECT 명령문에서 STRAIGHT_JOIN을 사용하고 FROM 구문에서 테이블 순서를 다르게 하면 보다 나은 성능을 얻을 수가 있을 것이다. 대부분의 경우, 여러분은 디스크 검색 (disk seek) 시간을 계산해서 쿼리의 성능을 추정할 수 있다. 작은 테이블의 경우에는 한번의 디스크 검색으로 열을 찾을 수가 있다 (왜냐하면 인덱스가 거의 캐시되어 있기 때문에). 보다 큰 테이블일 경우에는, B-트리 인덱스를 사용해서 이를 추정할 수가 있는데, 하나의 열을 찾기 위해서는 많은 검색을 하여야 한다: log(row_count) / log(index_block_length / 3 × 2 / (index_length + data_pointer_length)) + 1. MySQL에서는, 하나의 인덱스 블럭은 통상 1,024 바이트이며, 데이터 포인터 (data pointer)는 보통 4 바이트가 된다. 인덱스 길이가 3 바이트인 500,000개의 열이 있는 테이블의 경우 (MEDIUMINT 크기), 공식은 log(500,000)/log(1024/3×2/(3+4)) + 1 = 4 검색 (seek)이 된다. 이 인덱스는 대략 500,000 × 7 × 3/2 = 5.2MB (전형적인 인덱스 버퍼 공간 비율을 2/3로 가정함)의 스토리지 공간을 필요로 하기 때문에, 메모리에 충분한 인덱스를 가질 수가 있으며, 또한 한 두 번의 데이터 읽기만을 실행하더라도 쉽게 열을 찾을 수 있을 것이다. 하지만, 쓰기의 경우에는, 새로운 인덱스 값을 저장할 위치를 찾는데 4번의 검색을 해야 하고 인덱스를 업데이트 한 후에 열을 쓰기 위해서는 2번의 검색을 해야 한다. 위에서 설명하는 것은, 여러분의 어플리케이션이 로그 N에 의해서 성능이 저하된다는 것을 의미하는 것은 아니다. 모든 것이 OS 또는 MySQL에 의해 캐시가 되는 한, 테이블이 커지는 만큼만 느려지게 된다. 데이터가 캐시 되지 못할 정도로 커지게 된 후에는, 디스크 검색 (로그 N에 의해 늘어난)이 어플리케이션을 한정하기 전까지 점점 속도가 저하되기 시작한다. 이러한 현상을 없애기 위해서는, 키 캐시의 크기를 데이터가 늘어나는 만큼 늘려 주면 된다. MyISAM 테이블의 경우에는, key_buffer_size 시스템 변수가 키 캐시 크기를 제어한다. 일반적으로, 속도가 느린 SELECT ... WHERE 쿼리를 보다 빠르게 만들기 위해서는 우선 인덱스를 하나 추가 할 수 있는지를 검사해 본다. 서로 다른 테이블 간의 모든 참조 사항들은 보통 인덱스를 가지고서 이루어 진다. EXPLAIN 명령문을 사용하면 SELECT에 사용할 수 있는 인덱스에는 어떤 것들이 있는지를 알아볼 수가 있다. MyISAM 테이블 쿼리 속도를 향상 시키기 위한 방법에는 다음과 같은 것들이 있다: 이 섹션에서는 WHERE 구문 최적화 방법에 대해서 설명하기로 한다. 이 섹션에서 사용되는 예문들은 SELECT 명령문을 사용하지만, DELETE 와 UPDATE 명령문에 있는 WHERE 구문에도 적용할 수가 있다. MySQL 옵티마이저는 현재에도 계속 개발을 진행하고 있기 때문에, 이 섹션에서 설명하는 것들은 아직 완전하지가 않다. 하지만 MySQL은 이 문서에서 설명하는 것 이외의 많은 부분에서 최적화를 실행하고 있는 중이다. MySQL이 실행한 최적화 종류에는 다음과 같은 것들이 있다: 매우 빠른 쿼리의 예를 아래에서 보여 준다: MySQL은 인덱스 트리만을 가지고 아래의 쿼리를 해석하는데, 인덱스된 컬럼이 숫자 (numeric)라는 가정을 한다: 아래의 쿼리는 별개의 정렬 경로를 사용하지 않는 상태로 정렬된 순서에 있는 열을 추출하기 위해 인덱싱을 사용한다: range 접근 방식은 하나 또는 여러 개의 인덱스 값 인터벌 (interval) 내에 포함되어 있는 테이블 열 서브 셋 (subset)을 추출하기 위해서 단일 인덱스를 사용한다. 이것은 단일 부분 또는 다중 부분 인덱스를 위해 사용될 수 있다. 다음에 나오는 섹션에서는 WHERE 구문에서 인터벌 (interval)을 어떻게 얻을 수 있는지에 대해 자세히 설명을 하기로 한다. 단일 부분 인덱스의 경우, 인덱스 값 인터벌 (index value intervals)은 WHERE 구문에 있는 조건문에 상응해서 쉽게 표현될 수가 있기 때문에, 우리는 “인터벌 (interval)”이라는 표현 보다는 범위 조건 (range conditions)이라고 표현을 하겠다. 단일 부분 인덱스에 대한 범위 조건의 정의는 다음과 같다: 위의 설명에서 나오는 “상수 값 (Constant value)”이란 아래의 것 중에 하나를 의미한다: WHERE 구문에서 범위 조건을 가지고 있는 쿼리의 예가 아래에 있다: 상수를 전달하는 (propagation phase) 동안에는 어떤 비 상수 값 (non-constant value)들은 상수로 변환 된다는 것을 주의하자. MySQL은 WHERE 구문으로부터 각각의 모든 가능한 인덱스 범위 조건을 추출하고자 시도한다. 추출 과정 동안, 범위 조건을 구축하는데 사용되지 않는 조건들은 삭제되고, 범위 중첩 (overlapping range)을 만드는 조건들은 서로 연결이 되며, 비어 있는 범위를 만드는 조건들은 삭제된다. 아래의 명령문을 검토해 보면, key1은 인덱스된 컬럼이며, nonkey은 인덱스가 되지 않았다: 키 key1에 대한 추출 과정은 다음과 같다: 일반적인 경우 (위의 설명에서 예시한 대로), 범위 스캔용으로 사용된 조건은 WHERE 구문보다는 덜 제한적이다. MySQL은 WHERE 구문이 아닌 범위 조건을 충족하는 열을 걸러내기 위한 추가적인 검사를 진행한다. 범위 조건 추출 알고리즘은 임의적인 깊이 (arbitrary depth)의 네스티드 (nested) AND/OR 구조를 처리하며, 그 결과는 WHERE 구문에서 나오는 조건에 있는 순서와는 관련을 갖지 않는다. 다중 부분 인덱스 범위 조건은 단일 부분 인덱스에 대한 범위 조건의 확장이다. 다중 범위 인덱스 범위 조건은 한 개 또는 여러 개의 키 튜플 (key tuple) 인터벌 내에 인덱스 열을 놓는 것을 제한한다. 키 튜플 (Key tuple) 인터벌은 인덱스 순서를 사용해서 키 튜플 셋 전반에 걸쳐서 정의 된다. 예를 들면, 다중 부분 인덱스가 key1(key_part1, key_part2, key_part3)로 정의가 되고, 키 순서에서 나열된 키 튜플 셋이 다음과 같다고 가정하자: 조건 key_part1 = 1은 다음의 인터벌을 정의 한다: 인터벌은 위의 데이터 셋의 4번째, 5번째, 그리고 6번째 튜플을 커버 (cover)하며 범위 접근 방식에 의해 사용될 수 있다. 반대로, 조건 key_part3 = 'abc'는 단일 인터벌을 정의하지 않기 때문에 범위 접근 방식에 의해 사용될 수가 없다. 아래의 설명은 범위 조건이 어떻게 다중 부분 인덱스와 동작을 하는지를 보여 준다. 인덱스 병합 방식은 여러 개의 range 스캔을 가지고서 열을 추출하고 그 결과를 하나로 병합하기 위해 사용된다. 병합은 결합 (union), 교차 (intersection), 또는 시스템 내부에서 진행되는 스캔 (underlying scan) 교차 결합 (union-of-intersection)을 만든다. Note: 만일 여러분이 구형MySQ 버전에서 업그레이드를 했다면, 조인 (join) 최적화에 대한 이러한 형태는 5.0에서부터 소개되었고, 또한 인덱스에 관련해서 상당한 변화가 있다는 점을 유의해야 한다. (이전 버전에서는, 각각의 참조 테이블에 대해서는 하나의 인덱스만을 사용할 수 있었다.) EXPLAIN의 결과에서 보면, 인덱스 병합 방식은 type 컬럼 안에 있는 index_merge 형태로 나타난다. 이와 같은 경우, 키 컬럼은 사용된 인덱스 리스트를 가지며, key_len은 이러한 인덱스에 대한 가장 긴 키 부분 리스트를 가진다. 예제: 인덱스 병합 방식에는 여러 개의 접근 알고리즘이 있다 (EXPLAIN 결과의 Extra 필드에서 보여짐): 이러한 방식에 대해서는 아래에서 보다 자세한 설명을 하고 있다. Note: 인덱스 병합 최적화 알고리즘은 아래와 같이 이미 알려져 있는 결함을 가지고 있다: 인덱스 병합 접근 방식의 여러 가지 형태 중에 어떤 것을 선택할지는 다양한 옵션 추정 값에 따라서 진행된다. 이 접근 알고리즘은 WHERE 구문이 AND와 결합된 서로 다른 키에 있는 여러 개의 범위 조건으로 변환될 때 사용될 수 있으며, 각각의 조건은 아래의 것 중에 하나가 된다: 예제: 인덱스 병합 교차 알고리즘은 사용된 모든 인덱스에서 동시에 스캔을 실행하고, 병합된 인덱스 스캔으로부터 전달 받는 열 시퀀스에 대해 교차를 만들어 낸다. 만일 사용된 인덱스가 쿼리에서 사용된 모든 컬럼을 커버 한다면, 전체 테이블 열은 추출되지 않는다 (이 경우 EXPLAIN 결과는 Extra 필드 (field)에 있는 Using index를 가진다). 아래에는 이러한 쿼리의 예가 있다: 만일 사용된 인덱스가 쿼리에서 사용된 모든 컬럼을 커버하지 못한다면, 사용된 모든 키에 대한 범위 조건이 만족스러울 때에만 전체 열이 추출된다. 만일 병합된 조건 중의 하나가 InnoDB 또는 BDB 테이블의 주요 키 (primary key)에 걸쳐지는 조건이라면, 이 조건은 열 추출용으로 사용되지는 않지만, 다른 조건을 사용해서 추출된 열을 걸러 낼 때 사용된다. 이 알고리즘에 대한 표준은 인덱스 병합 방식 교차 알고리즘과 유사하다. 이 알고리즘은 OR과 결합된 서로 다른 키에서 테이블의 WHERE 구문이 여러 개의 범위 조건으로 변환될 때 적용될 수 있으며, 각 조건은 아래의 것 중에 하나가 된다: 예제: 이 접근 알고리즘은 WHERE 구문이 OR에 의해 결합된 여러 개의 범위 조건으로 변환될 때 적용되지만, 인덱스 병합 방식 결합 (union) 알고리즘에는 적용되지 않는다. 예제: 정렬-결합 알고리즘과 결합 알고리즘 간의 차이점은, 정렬 결합 알고리즘의 경우 모든 열의 ID를 우선 가져온 다음에 열을 돌려 주기 전에 정렬을 한다는 것이다. MySQL은 col_name = constant_value용으로 사용하는 col_name IS NULL에서 최적화를 동일하게 수행한다. 예를 들면, MySQL은 인덱스와 범위를 사용헤서 IS NULL이 있는 NULL을 검색할 수가 있다. 예제: 만일 WHERE 구문에 NOT NULL로 선언된 컬럼용 col_name IS NULL 조건이 포함되어 있다면, 그 수식은 최적화와는 거리가 멀게 된다. 컬럼이 NULL을 만들어 내는 경우에는 최적화가 일어나지 않는다 또한 col_name = expr AND col_name IS NULL과 같은 조합에 대해서도 최적화를 할 수가 있는데, 서브 쿼리를 해석하는 경우에는 이러한 것이 일반적이다. EXPLAIN은 이러한 최적화가 사용되는 경우에 ref_or_null를 표시한다. 이러한 최적화는 모든 키 부분에 대해서 하나의 IS NULL을 처리한다. 최적화된 쿼리의 예문 중에서, 테이블 t2의 a 와 b 컬럼에 하나의 인덱스가 존재한다고 가정하자: ref_or_null은 참조 키에서 한번의 읽기를 실행하고, 그 다음에 NULL 키 값을 가지고 열에 대한 별개의 검색을 실행한다. 최적화는 오직 한번의 IS NULL 레벨을 처리한다는 점을 기억하자. 아래의 쿼리에서 보면, MySQL 은 수식 (t1.a=t2.a AND t2.a IS NULL)에서만 키 룩업 (lookup)을 사용하며 b에 있는 키 부분은 사용할 수가 없다: ORDER BY와 연결되어 있는 DISTINCT 는 대부분의 경우에 임시 테이블을 필요로 한다. DISTINCT는 GROUP BY를 사용할 수도 있기 때문에, 여러분은 선택된 컬럼 부분이 아닌 ORDER BY 또는 HAVING 구문에 있는 컬럼과 함께 MySQL이 어떻게 동작을 하는지를 반드시 알아 두어야 한다. 대부분의 경우, DISTINCT 구문은 GROUP BY의 특별한 케이스로 간주된다. 예를 들면, 아래의 두 쿼리는 동일한 것이다: 위의 두 개는 동일한 것이기 때문에, GROUP BY 쿼리에 적용되는 최적화는 DISTINCT 구문을 가지고 있는 쿼리에도 적용될 수 있다. LIMIT row_count 를 DISTINCT에 연결을 할 때에는, row_count의 고유 열을 찾는 즉시 멈추게 된다. 만일 여러분이 쿼리에 명명되어 있는 테이블에 있는 컬럼을 사용하지 않는다면, MySQL은 매치되는 것을 처음으로 찾는 즉시 아직 사용되지 않은 테이블에 대해서는 스캐닝을 멈추게 된다. 아래의 경우, t1이 t2 보다 먼저 사용 된다고 가정하면 (여러분은 EXPLAIN로 검사할 수 있음), MySQL은 t2에서 맨 처음 열을 찾특정 열에 대해). MySQL은 아래와 같이 A LEFT JOIN B join_condition을 실행한다: RIGHT JOIN의 실행은 테이블 규칙을 반대로 하는 LEFT JOIN의 실행과 유사하다. 조인 (join) 옵티마이저는 조인되는 테이블의 순서를 계산한다. LEFT JOIN 또는 STRAIGHT_JOIN이 만들어 내는 테이블 읽기 순서는 조인 (join) 옵티마이저가 자신의 업무를 보다 빠르게 진행할 수 있도록 지원하는데, 그 이유는 검사를 해야 할 테이블 순열 (permutation)이 거의 없기 때문이다. 이것은 만일 여러분이 아래와 같은 타입의 쿼리를 실행한다면, MySQL LEFT JOIN이 MySQL로 하여금 d를 읽기 전에 b를 읽도록 하기 때문에 여기에서 전체 스캔을 진행한다는 점을 알아 두자. 이와 같은 경우에는 FROM 구문에 있는 a 와 b 의 순서를 바꾸어 준다: LEFT JOIN의 경우, 만일 WHERE 조건이 생성된 NULL 열에 대해 항상 거짓 (false)가 된다면, LEFT JOIN은 보통의 조인 (join)으로 변경된다. 예를 들면, t2.column1이 NULL이었다면, 아래의 쿼리에서는 WHERE 구문이 거짓이 될 것이다. 따라서, 쿼리를 일반적인 조인 (join)으로 변환하는 것이 안전하다: 이렇게 하는 것이 보다 빠르게 수행되게 만드는 것인데, 그 이유는 MySQL이 t2를 t1 보다 이전에 읽기 때문이다. 특정 테이블 순서를 실행하도록 하기 위해서는, STRAIGHT_JOIN를 사용한다. MySQL 5.0.1 이후에는, 조인 (join)을 표현하는 신텍스에서 네스티드 조인 (nested join)을 사용할 수 있게 되었다. table_factor 신텍스는 SQL 표준의 확장 형태이다. 표준 SQL에서는 table_reference만을 허용하며, 괄호 안에 이것을 넣을 수는 없다. 만일 내부 조인 (inner join)과 동일한 형태로서 table_reference 아이템 리스트에 있는 각각의 콤마를 고려한다면, 이것은 보수적인 확장 (conservative extension)이 된다. 예를 들면: MySQL에서는, CROSS JOIN은 INNER JOIN과 언어적으로 동일한 의미를 갖는다 (이 둘은 서로 대체해서 사용할 수가 있다). 하지만, 표준SQL의 경우에는 동일한 것이 아니다. INNER JOIN은 ON 구문과 함께 사용된다; 그렇지 않은 경우에는 CROSS JOIN이 사용된다. MySQL 5.0.1 이전 버전에서는, table_references에 있는 괄호는 생략 되었고 모든 조인(join) 연산은 왼쪽으로 그룹화가 되었다. 일반적으로, 괄호는 내부 조인 연산 (inner join operation)만을 가지고 있는 조인 수식 (join expression)에서만 생략될 수가 있다. 괄호를 생략하고 연산 동작을 왼쪽으로 그룹화 하고 나면, 조인 수식은 다음과 같이 된다: 하지만, 두 개의 수식은 동일한 것이 아니다. 이것을 알아 보기 위해, 테이블 t1, t2, 그리고 t3가 다음의 상태를 가지고 있다고 가정하자: 이와 같은 경우, 첫 번째 수식은 (1,1,101,101), (2,NULL,NULL,NULL)가 포함된 결과 셋을 만들어 내는데 비해서, 두 번째 수식은 (1,1,101,101), (2,NULL,NULL,101) 열을 만들게 된다: 아래의 예문에서 보면, 외부 조인 연산 (outer join operation)은 내부 조인 연산과 함께 사용된다: 이 수식은 아래와 같은 수식으로 변환될 수 없다: 주어진 테이블 상태로 보면, 두 개의 수식은 서로 다른 열 셋을 만들게 된다: 따라서, 만일 여러분이 외부 조인 연산자 (outer join operator)를 사용하는 조인 수식에서 괄호를 생략한다면, 우리는 원래의 수식에 대한 결과 셋을 변경해야 할지도 모른다. 보다 정확히 말하자면, 우리는 왼쪽 외부 조인 연산의 오른쪽 피 연산자 (operand)에 있는 괄호와 오른쪽 조인 연산의 왼쪽 피 연산자에 있는 괄호를 무시할 수가 없다. 달리 표현하면, 우리는 외부 조인 연산의 내부 테이블 수식에 대한 괄호를 무시할 수 없다는 것이다. 다른 피 연산자 (외부 테이블에 대한 피 연산자)에 대한 괄호는 무시할 수가 있다. 다음의 수식은: 아래의 수식과 동일한 것이다: 조인 수식 (join expression) (join_table)에 있는 조인 연산의 실행 순서가 왼쪽에서 오른쪽으로 되어 있지 않을 때마다, 우리는 네스티드 조인 (nested join)을 거론한다. 아래의 쿼리를 고려해 보자: 위의 쿼리는 아래의 네스티드 조인을 가지고 있다고 간주된다: 네스티드 조인은 왼쪽 조인 연산과 함께 처음 쿼리에서 형성되어 있는데 비해서, 두 번째 쿼리에서는 네스티드 조인이 내부 조인 연산과 함께 구성되어 있다. 처음 쿼리의 경우, 괄호는 생략될 수 있다: 조인 수식의 문법적인 구조는 조인 연산에 대한 실행 순서와 동일하다. 두 번째 쿼리의 경우, 비록 여기에서의 조인 수식은 명백하게 해석될 수 있다고 하더라도 괄호는 생략할 수 없다. (우리의 확장 신텍스의 경우, 비록 이론적으로는 두 번째 쿼리를 괄호 없이 파싱 (parsing)을 시킬 수는 있다고 하더라도, 이 쿼리의 (t2, t3)에서는 괄호가 필요하게 된다: 우리는 여전히 쿼리에 대해 명확한 이론적 구조를 가지고 있는데, 왜냐하면 LEFT JOIN 및ON 은 수식 (t2,t3)에 대한 왼쪽과 오른쪽의 구획 문자 (delimiter) 역할을 하기 때문이다.) 위에서 보여준 예제는 아래의 사항을 설명하는 것이다: 네스티드 외부 조인을 가지고 있는 쿼리는 내부 조인을 가지고 있는 쿼리와 동일한 파이프 라인 방식으로 실행이 된다. 보다 정확히 표현하면, 네스티드-루프 조인 알고리즘 (nested-loop join algorithm)의 변형된 형태가 많이 사용된다. 3 개의 테이블 T1,T2,T3 에 걸친 조인 쿼리를 가지고 있다고 가정하자: 여기에서, P1(T1,T2) 및 P2(T3,T3)은 일종의 조인 (join) 조건 (수식 상에서)인데 비해서, P(t1,t2,t3)는 테이블 T1,T2,T3 컬럼의 조건이 된다. t1||t2||t3는 “열 t1, t2, 및 t3의 컬럼을 연쇄적으로 연결해서 만든 열”을 의미한다. 아래에 있는 예제 중에서, 열의 이름이 나타나는 NULL은 이것을 해당 열의 각 컬럼에서 사용한다는 것을 의미한다. 예를 들면, t1||t2||NULL은 “열 t1 과 t2의 컬럼을 연쇄적으로 연결해서 만든 열, 그리고 t3의 각 컬럼에 대해서는 NULL”을 의미한다. 이 쿼리에 대해서는 네스티드 루프 패턴을 수정하였다: 일반적인 경우, 외부 조인 연산에서 첫 번째의 내부 테이블에 대한 네스티드 루프에 대해서는, 루프가 시작되기 전에 오프가 되고 (turned off), 루프를 수행한 후에 검사를 받는 플래그 (flag)가 추가된다. 플래그는 외부 테이블의 현재 열에 대해서, 내부 연산자를 표시하는 테이블에서 매치되는 것이 발견되면 온 된다 (turn on). 만일 루프 사이클 마지막까지 플래그가 여전히 꺼져 있다면, 외부 테이블의 현재 열에 대해 아무런 매치도 발견되지 않았다는 것이다. 이와 같은 경우, 열은 내부 테이블 컬럼에 대해서 NULL 값으로 보완 된다. 결과 열은 결과에 대한 마지막 검사를 진행하거나 또는 다음 네스티드 루프로 들어가게 되지만, 그 열이 모든 임베디드 외부 조인 (embedded outer join)의 조인 조건을 만족할 경우에만 해당된다. 내부 조인을 가지고 있는 쿼리에 대해서는, 옵티마이저가 아래의 것과 같은 별도의 네스티드 루프 순서를 선택한다: 외부 조인을 가지고 있는 쿼리의 경우, 옵티마이저는 외부 테이블에 대한 루프가 내부 테이블을 위한 루프보다 앞서는 경우의 순서만을 선택한다. 따라서, 외부 조인을 가지고 있는 우리의 쿼리에 대해서는, 단지 하나의 네스팅 순서 (nesting order)만이 가능하다. 아래 쿼리의 경우, 옵티마이저는 두 가지의 서로 다른 네스팅을 구하게 된다: 네스팅은 다음과 같다: 위의 두 네스팅에서 보면, T1은 외부 루프에서 처리되어야 하는데, 그 이유는 이것이 외부 조인에서 사용되기 때문이다. T2 및 T3는 내부 조인에서 사용되기 때문에, 이것들은 내부 루프에서 처리되어야 한다. 하지만, 조인이 내부 조인이기 때문에, T2 및 T3는 둘 중 하나의 순서로 처리될 수가 있다. 내부 조인에 대한 네스티드 루프 알고리즘을 설명할 때, 우리는 쿼리 실행 성능에 막대한 영향을 줄 수도 있는 것을 생략했다. 우리는 소위 “푸시 다운 (pushed-down)” 조건을 언급하지 않았다. WHERE 조건 P(T1,T2,T3)가 결합 공식 (conjunctive formula)으로 표현될 수 있다고 가정을 한다: 이와 같은 경우, MySQL은 실제로 내부 조인을 가지고 있는 쿼리를 실행하기 위해 아래의 네스티드 루프 스키마를 사용하게 된다: 여러분은 각각의 결합 C1(T1), C2(T2), C3(T3)들이 실행될 때 가장 내부에 있는 루프를 가장 외부 루프로 밀어 내는 것을 볼 수 있을 것이다. 만일 C1(T1)이 매우 제한적인 조건이라면, 이 조건 푸시 다운은 내부 루프에 전달된 테이블 T1에서 열의 숫자를 현격하게 줄일 수 있게 된다. 결과적으로, 쿼리의 실행 시간이 매우 많이 개선이 되는 것이다. 외부 조인을 가지고 있는 쿼리의 경우, WHERE 조건은 외부 테이블의 현재 열이 내부 테이블에서 매치가 되는 것을 발견한 후에만 검사된다. 따라서, 조건을 내부 네스티드 루프 밖으로 밀어 내는 최적화는 외부 조인을 가지고 있는 쿼리에는 직접적으로 적용할 수가 없다. 여기에서 우리는 하나의 매치를 발견할 때 켜지는 플래그가 보호하는 조건 푸시 다운 (conditional push-down) 술어 (predicate) 개념을 추가해야 했다. 외부 조인을 가지고 있는 예문에 대해서는: 보호가 된 푸시 다운 조건 (guarded pushed-down condition)은 다음과 같이 된다: 일반적으로, 푸시 다운 술어 (pushed-down predicates)는 P1(T1,T2) 및 P(T2,T3)와 같은 조인 조건 (join condition)에서 가져올 수 있다. 이와 같은 경우, 푸시 다운 술어 (pushed-down predicate)는 대응되는 외부 조인 연산이 만드는 NULL-보완 열에 대한 술어 검사를 막는 플래그가 보호한다. 하나의 내부 테이블에서 동일하게 네스티드 조인이 되어 있는 다른 테이블로의 접근은, 만일 이 접근이 WHERE 조건에서의 술어에 의해 발생될 경우에는 금지 된다. (이와 같은 경우, 우리는 조건 키 접근을 사용하였지만, 이 기법은 아직까지 5.0에는 추가되지 않았다.) 대부분의 경우, 쿼리의 FROM 구문에 있는 테이블 수식은 단순화 시킬 수가 있다. 파서 (parser) 단계에서, 오른쪽 외부 조인 연산 (right outer joins operation)이 있는 쿼리는 왼쪽 조인 연산만을 가지고 있는 동일한 쿼리로 변환시킬 수가 있다. 일반적인 경우, 이러한 변환은 아래의 규칙에 따라서 진행된다: T1 INNER JOIN T2 ON P(T1,T2) 형식의 모든 내부 조인 수식은 WHERE 조건문 결합 형태로 조인되는 (또는 만일 존재한다면, 임베디드 조인의 조인 조건으로) 리스트 T1,T2, P(T1,T2)로 대체가 된다. 옵티마이저가 외부 조인 연산을 가지고 있는 조인 쿼리에 대한 플랜을 계산할 때, 이것은 각각의 연산에 대해서는, 외부 테이블을 내부 테이블보다 먼저 접근하는 플랜에 대해서만 고려를 하게 된다. 옵티마이저의 옵션들은 제한적이 되는데, 그 이유는 이러한 플랜들만이 네스티드 루프 스키마에 의한 외부 조인 연산을 가진 쿼리를 실행할 수 있도록 하기 때문이다. 아래와 같은 쿼리를 가지고 있다고 가정하자: 위의 쿼리에서 R(T2)는 T2로부터 많은 수의 매칭 열을 가지고 있다고 가정한다. 만일 쿼리를 위와 같이 실행 시킨다면, 옵티마이저는 매우 비 효율적인 실행 플랜을 가지고 테이블 T1을 테이블 T2 전에 접속하는 것 이외에는 선택을 하지 못하게 된다. 다행스럽게도, WHERE 조건이 널 값 거부 (null-rejected)가 되는 경우에는, 이러한 쿼리를 외부 조인 연산이 없는 쿼리로 변화시켜 버린다. 만일 그러한 연산을 위해 구성된 NULL-보완 열에 대해서 어떤 조건이 FALSE 또는 UNKNOWN으로 계산된다면, 이러한 조건을 외부 조인 연산에 대한 널 값 거부 (null-rejected)라고 부르게 된다. 따라서, 아래와 같은 외부 조인에 대해서는: 아래와 같은 조건들이 널 값 거부 (null-rejected)가 된다: 하지만, 아래와 같은 조건들은 여기에 해당하지 않는다: 어떤 조건이 외부 조인 연산에 대해서 널 값 거부인지 아닌지를 검사하는 일반적인 규칙은 간단하다. 아래의 경우에 해당되면 널 값 거부 (null-rejected) 조건이 된다: 하나의 조건이 어떤 쿼리에서는 외부 조인 연산에 대한 널 값 거부가 되고 다른 쿼리에 대해서는 그렇지 않게 될 수도 있다. 아래의 쿼리에서 보면: WHERE 조건은 두 번째 외부 조인 연산에 대해서는 널 값 조건이 되지만, 첫 번째 것에 대해서는 그렇지 않게 된다. 만일 WHERE 조건이 하나의 쿼리에서 외부 조인 연산에 대한 널 값 거부가 되면, 그 외부 조인 연산은 내부 조인 연산으로 대체가 된다. 예를 들면, 앞에서 보여준 쿼리는 아래의 쿼리로 대체가 된다: 원래의 쿼리에 대해서는, 옵티마이저는 오직 하나의 접속 순서인T1,T2,T3 와 호환되는 플랜만을 계산한다. 대체되는 쿼리에 대해서는, 옵티마이저는 접속 시퀀스 T3,T1,T2에 대해서도 추가적으로 고려를 하게 된다. 하나의 외부 조인 연산에 대한 변환은 다른 변환을 트리거 할 수도 있다. 따라서 아래의 쿼리는: 다음과 같이 우선 변환 된다: 이것은 다음의 쿼리와 동일하게 된다: 이제 남아 있는 외부 조인 연산 역시 내부 조인으로 대체가 되는데, 그 이유는 T3.B=T2.B가 널 값 거부 조건이고 외부 조인이 전혀 없이 쿼리를 가져오기 때문이다: 어떤 경우에는, 하나의 임베디드 외부 조인 연산을 대체할 수도 있으나, 임베디드 외부 조인을 변환시킬 수는 없다. 다음의 쿼리는: 아래의 형태로 변환된다: 이것은 임베디드 외부 조인 연산을 여전히 가지고 있는 형태만으로 다시 쓰여질 수가 있다: 쿼리 안에 있는 임베디드 외부 조인 연산을 변환 시키고자 할 때에는, WHERE 조건과 함께 임베디드 외부 조인에 대한 조인 조건을 반드시 고려해야 한다. 아래의 쿼리에서 보면: WHERE 조건은 임베디드 외부 조인에 대해서는 널 값 거부 조건이 아니지만, 임베딩 외부 조인 (embedding outer join) T2.A=T1.A AND T3.C=T1.C의 조인에 대해서는 널 값 거부 조건이 된다. 따라서 위의 쿼리는 아래와 같이 변환 될 수 있다: 외부 조인 연산을 내부 조인으로 변환 시키는 알고리즘은 MySQL 5.0.1에서 전체적으로 구현되었다. MySQL 4.1 버전에서는 일부 기능만이 실행되었다. MySQL은 어떠한 추가 정렬 행위를 하지 않고서도 하나의 인덱스만을 사용해서 ORDER BY 구문을 만족 시킬 수 있는 경우가 있다. 인덱스의 사용하지 않는 모든 부분과 모든 여분의 ORDER BY 컬럼이 WHERE 구문에서 상수로 되어 있는 한, ORDER BY가 인덱스와 정확히 매치가 되지 않는다고 하더라도 위의 형태로 사용할 수가 있다. 아래의 쿼리들은 인덱스를 사용해서 ORDER BY 부분을 해석한다: 어떤 경우에는, MySQL이 WHERE 구문과 매치되는 열을 찾기 위해 인덱스를 여전히 사용하고 있다고 하더라도, 그 인덱스를 사용해서 ORDER BY를 해석하지 못하는 경우가 있다. 아래의 경우가 이에 해당한다: EXPLAIN SELECT ... ORDER BY를 사용하면, MySQL이 인덱스를 가지고 쿼리를 해석할 수 있는지를 알아볼 수가 있다. 만일 Extra 컬럼에 있는 Using filesort를 사용한다면, MySQL은 인덱스를 사용해서 쿼리를 해석할 수 없게 된다. filesort 최적화는 정렬 키 값과 열 위치 및 쿼리용으로 요구되는 컬럼 기록을 사용한다. filesort 알고리즘은 아래와 같이 동작을 한다: 속도가 느려지는 것을 막기 위해서, 이 알고리즘은 정렬 튜플에 있는 여분의 컬럼의 전체 크기가 max_length_for_sort_data 시스템 변수의 값을 초과하지 않는 경우에만 이러한 최적화를 사용하는 것이 좋다. 만일 ORDER BY의 속도를 증가시키고 싶다면, 추가적인 정렬을 실행하지 않고 인덱스를 사용할 수 있는지를 검사한다. 만일 이렇게 하는 것이 불가능하다면, 아래의 전략을 사용하도록 한다: 디폴트로, MySQL은 마치 쿼리 안에 ORDER BY col1, col2, ... 를 지정한 것처럼 모든 GROUP BY col1, col2, ... 쿼리를 정렬한다. 만일 여러분이 동일한 컬럼 리스트를 가지고 있는 ORDER BY 구문을 명확하게 포함시킨다면, MySQL은 비록 정렬이 여전히 일어날 지라도, 어떠한 속도 저하 없이 이것을 최적화 시키게 된다. 만일 쿼리가 GROUP BY를 포함하고는 있으나 여러분은 그 결과를 정렬하는 오버헤드를 피하고자 한다면, ORDER BY NULL을 지정해 줌으로서 정렬 동작을 억제 시킬 수가 있다. 예를 들면: GROUP BY 구문을 만족 시키는 가장 일반적인 방법은 테이블 전체를 스캔하고 각 그룹에서 나오는 모든 열들이 연속적으로 구성되는 임시 테이블을 생성을 한 후에, 그 테이블을 사용해서 그룹을 찾고 집합 함수 (aggregate function) (존재 한다면)을 적용하는 것이다. GROUP BY를 위한 인덱스 사용에 있어서 가장 중요한 선결 조건은 모든 GROUP BY 컬럼 참조가 동일한 인덱스에서 속성을 가져오고, 인덱스는 자신의 키를 순서대로 저장한다는 것이다. 임시 테이블 사용을 인덱스 접속으로 대체할 수 있는지는 쿼리에서 사용된 인덱스 부분과, 이 부분을 지정하는 조건, 그리고 선택된 집합 함수에 따라 정해진다. 인덱스 접속을 통한 GROUP BY 쿼리 실행에는 두 가지 방법이 있다. 첫 번째 방법은, 그룹 연산을 모든 범위 술어 (존재할 경우)와 함께 적용시키는 것이며, 두 번째로는 범위 스캔을 우선 실행하고, 그 다음에 결과 튜플 그룹에 대해 스캔을 하는 방법이다. 가장 효과적으로 GROUP BY를 처리하는 방법은, 인덱스를 사용해서 그룹 필드에서 직접 데이터를 추출하는 것이다. 이러한 방식을 통해서, MySQL은 키가 정렬되어 있는 인덱스 타입 특성을 사용한다 (예를 들면, BTREE). 이러한 특성은 모든 WHERE 조건문을 만족시키는 인덱스 내의 모든 키를 고려하지 않는 상태로 인덱스에 있는 룩업 그룹 (lookup group)을 사용할 수가 있다. 이러한 접속 방식은 인덱스 안에 있는 키 조각 (fraction)만을 고려하기 때문에 느슨한 인덱스 스캔 (loose index scan)이라고 부른다. 아무런 WHERE 구문이 존재하지 않을 경우, 느슨한 인덱스 스캔은 그룹의 수 만큼 키를 읽어 오며, 이것은 모든 키 숫자보다는 훨씬 작은 숫자가 된다. 만일 WHERE 구문이 범위 술어를 가지고 있다면, 느슨한 인덱스 스캔은 범위 조건을 만족시키는 각 그룹의 첫 번째 키를 조사하고, 최소 가능 키 숫자를 다시 읽게 된다. 이것은 아래의 조건 아래에서 가능해 진다: 이러한 쿼리에 대한 EXPLAIN 결과는 Extra 컬럼에 Using index for group-by를 보여준다. 아래의 쿼리들은 이러한 범주에 속하는 것이며, 여기에는 테이블 t1(c1,c2,c3,c4) 상에 idx(c1,c2,c3) 인덱스가 있다고 가정한다: 다음의 쿼리들은 이러한 선택 방식을 가지고서는 실행될 수 없는데, 그 이유는 다음과 같다: 타이트한 인덱스 스캔은 쿼리 조건에 따라서 전체 인덱스 스캔 또는 범위 인덱스 스캔이 될 수 있다. 느슨한 인덱스 스캔에 대한 조건이 맞지 않을 때에는, GROUP BY 쿼리를 위한 임시 테이블 생성을 하지 않을 수도 있다. 만일 WHERE 구문에 범위 조건이 존재한다면, 이 방식은 이 조건을 만족하는 키에 대해서만 읽기를 실행한다. 그렇지 않으면, 이 방식은 인덱스 스캔을 실행한다. 이 방식은 WHERE 구문이 정의한 각 범위 안에 있는 모든 키를 읽거나, 또는 만일 아무런 범위 조건이 없다면 전체 인덱스를 스캔하기 때문에, 우리는 이것을 타이트한 인덱스 스캔이라고 부른다. 타이트한 인덱스 스캔을 사용한다면, 그룹 연산 (grouping operation)은 범위 조건을 만족 시키는 모든 키를 찾고 난 후에만 실행된다는 점을 알아 두기 바란다. 이 방식을 구동하기 위해서는, GROUP BY 키의 부분 사이 또는 이 키 전에 나오는 키를 참조하는 쿼리의 모든 컬럼용 상수 등식 조건 (constant equality condition)이 존재하기만 하면 된다. 등식 조건에서 나오는 상수는 인덱스 접두사 (prefix)가 완벽한 형태를 갖추도록 하기 위해서 검색 키에 집어 넣어진다. 그렇게 되면 이 인덱스 접두사는 인덱스 루프를 실행하는데 사용될 수가 있다. 만일 우리가 GROUP BY 결과를 정렬 하도록 요구한다면, 인덱스 접두사인 검색 키를 만드는 것이 가능하며, MySQL은 순서화된 인덱스에 있는 접두사를 사용한 검색이 이미 순서별로 모든 키를 추출하였기 때문에 추가적인 정렬을 하지 않게 된다. 아래의 쿼리는 위에서 설명한 느슨한 인덱스 스캔 접속 방식과는 동작을 하지는 않지만, 타이트한 인덱스 스캔 접속 방식과는 동작을 한다 (인덱스 idx(c1,c2,c3) t1(c1,c2,c3,c4)에 인덱스 idx(c1,c2,c3)가 존재한다고 가정하자). MySQL은 LIMIT row_count은 사용하면서 HAVING은 사용하지 않으면 쿼리를 다른 방식으로 다루는 경우가 있다. EXPLAIN을 실행하면 MySQL이 쿼리를 해석하기 위해 테이블을 스캔할 때 type 컬럼에 있는 ALL을 보여준다. 이것은 일반적으로 아래와 같은 조건 아래에서 발생한다: 작은 테이블의 경우에는 테이블 스캔이 적절할 수도 있을 것이다. 대형 테이블의 경우에는, 옵티마이저가 올바르지 않은 테이블 스캔을 선택하지 못하도록 하기 위해ass="bu_point dep3"> 열을 삽입하는데 필요한 시간은 아래의 요소들을 사용해서 판단할 수가 있는데, 여기에서 보여주는 숫자는 대략적인 수치이다: 여기에는 테이블을 처음으로 열 때 발생하는 오버헤드가 고려되지 않았으며, 이러한 오버헤드는 동시에 구동하는 각 쿼리에 대해서 한번씩만 발생한다. 여러분은 아래의 방법을 통해서 삽입 속도를 개선 시킬 수가 있다: 업데이트 명령문은 추가적인 쓰기 동작 오버 헤드가 있는 SELECT 쿼리와 유사하게 최적화가 이루어진다. 쓰기 속도는 업데이트되는 데이터의 양과 인덱스의 숫자에 따라 다르게 나타난다. 변경되지 않는 인덱스는 업데이트가 되지 않는다. 업데이트의 속도를 빠르게 만들 수 있는 다른 방법은, 개별적인 업데이트는 지연을 시킨 후에 나중에 하나의 열에 대해서 업데이트를 한꺼번에 실행하는 것이다. 테이블을 잠가 놓았을 경우에는, 여러 개의 업데이트를 한꺼번에 실행하는 것이 한번에 하나의 업데이트를 실행하는 것 보다 훨씬 빠른 속도를 나타낸다. 동적 열 포멧을 사용하고 있는 MyISAM 테이블의 경우에는, 열 전체를 업데이트하면 열이 나누어질 수도 있다. 만일 이것을 자주 실행한다면, OPTIMIZE TABLE을 많이 사용하는 것이 중요하다. 개별적인 열을 삭제하는데 필요한 시간은 인덱스 숫자 비율과 정확히 일치한다. 열을 보다 빠르게 삭제를 하기 위해서는, key_buffer_size 시스템 변수를 증가 시켜서 키 캐시를 크게 만들면 된다. 하나의 테이블에 있는 모든 열을 삭제하기 위해서는, TRUNCATE TABLE tbl_name를 사용하는 것이 DELETE FROM tbl_name보다 빠를 것이다. 이 섹션에서는 쿼리 처리 속도를 개선 시키기 위한 여러 가지 최적화 팁에 대해서 설명??에 따른 오버 헤드를 피하기 위해서는 영구적인 접속 (persistent connection)을 하도록 한다. 만일 영구 접속을 할 수가 없고, 데이터 베이스에 대한 새로운 많은 접속을 초기화 시킨다면, 아마도 thread_cache_size 변수 값을 변경하고자 할 것이다.SELECT 및 다른 명령문 최적화 하기
SELECT 및 다른 명령문 최적화 하기
EXPLAIN 을 가지고 쿼리 최적화 하기
쿼리 성능 추정하기
SELECT 쿼리의 속도
WHERE 구문 최적화
범위 최적화
인덱스 병합 최적화
IS NULL 최적화
DISTINCT 최적화
LEFT JOIN 및 RIGHT JOIN 최적화
네스티드 조인(Nested Join) 최적화
외부 조인(Outer Join) 딘순화
ORDER BY 최적화
GROUP BY 최적화
LIMIT 최적화
테이블 스캔을 피하는 방법
INSERT 명령문 속도
UPDATE 명령문 속도
DELETE 명령문 속도
다른 여러 가지의 최적화 팁(Tips)
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
EXPLAIN 을 가지고 쿼리 최적화 하기
EXPLAIN tbl_name
또는:
EXPLAIN [EXTENDED] SELECT select_options
?
SELECT 아이덴티파이어 (identifier). 이것은 쿼리 안에 있는 SELECT의 순차적인 번호(sequential number)이다.
SELECT에 대한 타입이며, 아래의 테이블에 있는 것 중에 하나가 된다:
SIMPLE
Simple SELECT (not using UNION or subqueries)
PRIMARY
Outermost SELECT
UNION
Second or later SELECT statement in a UNION
DEPENDENT UNION
Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT
Result of a UNION.
SUBQUERY
First SELECT in subquery
DEPENDENT SUBQUERY
First SELECT in subquery, dependent on outer query
DERIVED
Derived table SELECT (subquery in FROM clause)
DEPENDENT는 전형적으로는 상호 연관된 (correlated) 서브 쿼리(subquery)의 사용을 의미한다.
결과 열이 참조하는 테이블.
조인 (join) 타입. 서로 다른 타입의 조인 (join)이 아래에 있는데, 가장 좋은 것부터 가장 나쁜 것의 순서로 되어 있다:
테이블은 하나의 열만을 가지고 있다 (= 시스템 테이블). 이것은 const 조인 (join) 타입의 특별한 경우이다.
테이블은 적어도 하나의 매칭 (matching) 테이블을 가지고 있는데, 쿼리가 시작되는 시점에서 이 테이블을 읽게 된다. 여기에는 하나의 열만이 존재하기 때문에, 이 열에 있는 컬럼에서 얻는 값은 나머지 옵티마이저에 의해 상수 (constant)로 인식될 수 있다. const 테이블은 한번 밖에 읽혀지지 않기 때문에 매우 빠르다. const는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값 (constant value)과 비교를 할 때 사용된다. 아래의 쿼리에서 보면, tbl_name은 const 테이블 형태로 사용되고 있다:
SELECT * FROM tbl_name WHERE primery_key=1;
SELECT * FROM tbl_name
WHERE primery_key_part1=1 AND primery_key_part2=2;
이전 테이블로부터 각 열을 조합 (combination)하기 위해서 이 테이블의 열을 하나읽는다. system 및 const 타입과는 달리, 이것이 가장 최선의 가능 조인 (join) 타입이다. 이것은 조인 (join)에 의해 인덱스의 모든 부분이 사용될 때 쓰이게 되며, 이때 인덱스는 PRIMARY KEY 또는 UNIQUE 인덱스가 된다. eq_ref는 = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용될 수 있다. 비교 값은 이 테이블 전에 읽었던 테이블에서 컬럼을 사용한 수식 또는 상수 (constant)가 될 수 있다. 아래의 예제에서 보면, MySQL은 ref_table를 처리하기 위해서 eq_ref 조인 (join)을 사용하고 있다.
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
이전 테이블에서 읽어온 각각의 열을 조합하기 위해 이 테이블에서 매칭 (matching)되는 인덱스 값을 가진 모든 열을 읽어온다. 만일 조인 (join)이 키의 좌측 끝(leftmost) 접두사 만을 사용하거나 또는 키 값이 PRIMARY KEY 또는 UNIQUE 인덱스가 아니라면 (달리 말하면, 만일 조인 (join)이 키 값을 기반으로 한 단일 (single) 열을 선택하지 않는다면), ref가 사용된다. 만일 사용된 키가 적은 수의 열에 대해서만 매치가 된다면, 그것은 좋은 조인 (join) 타입인 것이다. ref는 = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용될 수 있다. 아래의 예제에서 본다면, MySQL은 ref_table 처리 과정에서 ref 조인 (join)을 사용한다.
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
이 조인 (join) 타입은 ref과 유사하지만, MySQL이 NULL 값을 가지고 있는 열에 대해서도 검색을 한다는 점에서 차이가 있다. 이 조인 (join) 타입 최적화는 서브 쿼리(subqueries)를 해석할 때 자주 사용된다. 아래의 예제에서 보면, MySQL은 ref_table처리 과정에서 ref_or_null 조인 (join)을 사용하고 있다.
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
이 조인 (join) 타입은 인덱스 병합 최적화가 사용되었음을 나타낸다. 이 경우에, 결과 열에 있는 key 컬럼은 사용된 인덱스 리스트를 가지고 있고, key_len는 사용된 인덱스에 대해서 가장 긴 키 부분의 리스트를 가지고 있다.
이 타입은 아래 형태의 IN 서브 쿼리 (subqueries)에 대해서 ref를 대체한다:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery는 효율성을 위해서 서브 쿼리 (subquery)를 대체하는 인덱스 룩업(lookup) 함수이다.
이것은 unique_subquery와 유사한 조인 (join) 타입이다. 이것은 IN 서브 쿼리(subqueries)를 대체하지만, 아래 형태의 서브 쿼리 (subquery)에 있는 논-유니크(non-unique)인덱스에 대해서도 동작을 한다:
value IN (SELECT key_column FROM single_table WHERE some_expr)
주어진 범위에 들어 있는 열만을 추출하며, 열 선택은 인덱스를 사용한다. 결과 열에 있는 key 컬럼은 어떤 인덱스가 사용되었는지를 가리킨다. key_len은 사용된 키에서 가장 긴 부분을 가진다. ref 컬럼은 이 타입에 대해서는 NULL 값이 된다. range는 키 컬럼이 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 또는 IN 연산자를 사용하는 상수 (constant)와 비교할 때 사용될 수 있다:
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);
이 조인 (join) 타입은 ALL과 동일하지만, 인덱스 트리 (index tree)만을 스캔한다는 점에서 다르다. 일반적으로, 보통의 인덱스 파일이 데이터 파일보다 작기 때문에, 이것은 ALL 보다는 빠르게 동작한다. MySQL은 쿼리가 단일 인덱스의 일부분인 컬럼만을 사용할 때 이 조인 (join) 타입을 사용한다.
이전 테이블에서 읽어온 각각의 열을 조합하기 위해 전체 테이블 스캔을 실행한다. 테이블이 const가 표시되지 않은 첫 번째 테이블이고, 다른 모든 경우에 있어서 매우 좋지 않은 경우라면, 이것은 그리 좋은 경우가 아니다. 일반적인 경우에는, 이전 테이블에서 가져온 상수(constant) 값 또는 컬럼 값을 사용해서 테이블 열을 추출하는 인덱스를 추가하면 ALL을 피할 수가 있다.
possible_keys 컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 컬럼은 EXPLAIN 결과에서 나타나는 테이블 순서와는 전적으로 별개의 순서가 된다. 이것은, possible_keys에 있는 키 중에 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을 수도 있음을 의미하는 것이다. 만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이와 같은 경우, 여러분은 WHERE 구문을 검사해서, 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 그러한 경우라면, 적절한 인덱스를 하나 생성한 후에, EXPLAIN을 다시 사용해서 쿼리를 검사한다. 테이블이 어떤 인덱스를 가지고 있는지를 보기 위해서는, SHOW INDEX FROM tbl_name를 사용한다.
key 컬럼은 MySQL이 실제로 사용할 예정인 키 (인덱스)를 가리킨다. 만일 아무런 인덱스도 선택되지 않았다면, 그 키는 NULL이 된다. MySQL로 하여금 possible_keys 컬럼에 있는 인덱스를 사용하거나 또는 무시하도록 만들기 위해서, FORCE INDEX, USE INDEX, 또는 IGNORE INDEX를 쿼리에서 사용하도록 한다. MyISAM 및 BDB 테이블의 경우에는, ANALYZE TABLE를 구동시키면 옵티마이저가 보다 좋은 인덱스를 선택하도록 도움을 줄 수가 있다. MyISAM 테이블의 경우에는, myisamchk --analyze 가 동일한 역할을 한다.
ref 컬럼은 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 보여준다.
rows 컬럼은 MySQL이 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다.
이 컬럼은 MySQL이 쿼리를 어떻게 해석하는지에 관한 추가적인 정보를 제공한다. 이 컬럼이 가질 수 있는 값은 다음과 같다:
MySQL은 명확한 값 (distinct value)를 찾게 되며, 따라서 MySQL이 매칭되는 열을 찾게 되면 더 이상의 열에 대해서는 검색을 중단한다.
MySQL은 쿼리상에서 LEFT JOIN 최적화를 실행 했으며, 이 최적화와 매치되는 열을 찾은 후에는 더 이상 이 테이블에서 이전 열 조합 검색을 하지 않게 된다. 이러한 방식으로 최적화가 되는 쿼리 타입의 예는 다음과 같다:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
t2.id를 NOT NULL로 정의했다고 가정하자. 이와 같은 경우, MySQL은 t1을 스캔하고 t1.id 값을 사용해서 t2에 있는 열을 검색한다. 만일 MySQL이 t2에서 매칭되는 열을 발견하면, MySQL은 t2.id 가 결코 NULL이 아님을 알게 되며, 따라서 동일한 id 값을 가지고 있는 t2에서는 더 이상 열을 스캔하지 않게 된다. 달리 표현하면, t1에 있는 각 열에 대해서, MySQL은 t2에서는 단일 검색 (lookup)만을 하게 되며, t2에서 실제로 얼마나 많은 열이 매치가 되는지는 상관이 없게 된다.
WHERE t2.id IS NULL;
MySQL은 사용하기에 좋은 인덱스를 찾지 못했으나, 이전 테이블에서 컬럼 값을 찾고 난 후에는 사용할 수도 있을 법한 인덱스는 알아냈다. 이전 테이블에 있는 각 열 조합에 대해서는, MySQL은 그 조합이 열을 추출하기 위해서 range 또는 index_merge 접근 방식을 사용할 수 있는지를 검사한다. 이 방법은 그리 빠른 방법은 아니지만, 인덱스를 전혀 사용하지 않는 것 보다는 빠르게 진행한다.
MySQL은 저장된 순서에 따라서 열을 추출하는 방법을 찾기 위해 기타 과정을 진행한다. 정렬 (sort)은 조인 (join) 타입과 정렬 키 및 WHERE 구문과 매치가 되는 모든 열에 대한 열 포인터 (pointer)를 사용해서 모든 열에 걸쳐 진행 된다. 그런 다음에 그 키는 저장이 되고 열은 저장 순서에 따라서 추출된다.
인덱스 트리에 있는 정보만을 가지고 테이블에서 컬럼 정보를 추출한다. 쿼리가 단일 인덱스의 일부 컬럼만을 사용하는 경우에, 이러한 전략을 사용할 수가 있다.
쿼리를 해석하기 위해서는, 결과를 저장할 임시 테이블을 하나 생성해야 한다. 만일 쿼리가 컬럼을 서로 다르게 목록화 하는 GROUP BY 및 ORDER BY 구문을 가지고 있는 경우에 이런 것이 일어나게 된다.
WHERE 구문은 다음 테이블에 대한 열 매치 (match) 또는 클라이언트에 보내지는 열을 제한하기 위해 사용된다. 테이블에서 모든 열을 조사하거나 불러올 의도가 특별히 없다면, Extra 값이 Using where 가 아니고, 테이블 조인 (join) 타입이 ALL 또는 index일 경우에는 쿼리에 문제가 생길 수도 있다. 만일 여러분이 가능한 한 빠른 쿼리를 만들고 싶다면, Extra Using filesort 및 Using temporary 값을 조사하도록 한다.
이것들은 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합 (merge)이 되는지를 나타낸다. Section 7.2.6, “인덱스 병합 최적화”를 참조할 것.
테이블 접근에 대한 Using index 방식과 유사한 Using index for group-by 방식은MySQL이 실제 테이블을 추가적으로 검색을 하지 않고서도, GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출 (retrieve)하기 위해 사용될 수 있는 인덱스를 찾았음을 가리킨다. 또한, 그 인덱스는 각 그룹에 대해 가장 효과적인 방식으로 사용되기 때문에, 적은 수의 인덱스 엔트리만이 읽혀지게 된다.
이 아이템은 NDB Cluster 테이블에만 적용된다. 이것은 MySQL 클러스터가 인덱스가 되지 않은 컬럼 (non-indexed column)과 상수 (constant) 간의 직접 비교 (direct comparision (=))의 효율성을 개선하기 위해서 조건문을 푸시 다운 (condition pushdown) 하는 중이라는 의미를 갖는다. 이와 같은 경우, 조건문은 동시에 값이 검사되는 클러스터의 모든 데이터 노드로 “푸시 다운 (pushed down)” 된다. 이것은 매치되지 않는 열을 네트워크 전체에 보낼 필요성을 없애 주며, 조건문 푸시 다운을 하지 않는 경우에 비해서 5 ~ 10배의 속도 향상을 얻을 수가 있다. 여러분이 아래와 같은 클러스터 테이블을 가지고 있다고 가정하자:
CREATE TABLE t1 (
이와 같은 경우, 조건문 푸시 다운은 아래와 같은 쿼리와 함께 사용될 수 있다:
a INT,
b INT,
KEY(a)
) ENGINE=NDBCLUSTER;
SELECT a,b FROM t1 WHERE b = 10;
이것은 EXPLAIN SELECT 결과를 통해서 볼 수가 있는데, 그 결과는 다음과 같다:
mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
조건문 푸시 다운은 다음의 쿼리와 함께 사용할 수 없다:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where with pushed condition
SELECT a,b FROM t1 WHERE a = 10;
첫 번째 쿼리의 경우에는 조건문 푸시 다운을 적용할 수가 없는데, 그 이유는 인덱스가 컬럼 a에 존재하기 때문이다. 두 번째 경우에 대해서 조건문 푸시 다운을 사용할 수 없는 이유는, 인덱스가 되지 않은 컬럼 b를 포함하는 비교문이 간접적이기 때문이다. (하지만, WHERE 구문에서 b + 1 = 10 을 b = 9 로 줄여서 비교를 한다면 푸시 다운을 사용할 수가 있다.) 하지만, > 또는 < 연산자를 사용해서 인덱스된 컬럼을 상수(constant)와 비교를 하는 경우에는 조건문 푸시 다운을 사용할 수가 있다:
SELECT a,b FROM t1 WHERE b + 1 = 10;
mysql> EXPLAIN SELECT a,b FROM t1 WHERE a<2\G
조건문 푸시 다운에 관련해서는 아래의 사항을 기억하기 바란다: 조건문 푸시 다운은 MySQL 클러스터에만 관련이 있으며, 다른 스토리지 엔진을 사용하고 있는 테이블에 대해서 쿼리를 실행할 경우에는 발생하지 않는다.
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 2
Extra: Using where with pushed condition
조건문 푸시 다운 기능은 디폴트로 사용되지 않는다. 이 기능을 활성화 시키기 위해서는, mysqld을 --engine-condition-pushdown 옵션과 함께 시작하거나, 또는 아래의 명령문을 실행한다:
SET engine_condition_pushdown=On;
조건문 푸시 다운, Using where with pushed condition, 그리고 engine_condition_pushdown 등은 모두 5.0 클러스터에서 추가 되었다.
아래의 예제는 EXPLAIN에 의해 얻어진 정보를 가지고서 다중-테이블 조인을 어떻게 최적화 시키는 지를 설명하는 것이다.
다음과 같은 SELECT 명령문을 가지고 있으며, EXPLAIN을 사용해서 이것을 조사한다고 가정하자:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
위 예제에 대해서, 다음과 같은 가정을 하도록 하자:
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
Table
Column
Data Type
Tt
ActualPC
CHAR(10)
Tt
AssignedPC
CHAR(10)
Tt
ClientID
CHAR(10)
Tt
EMPLOYID
CHAR(15)
Tt
CUSTNMBR
CHAR(15)
Table
Index
Tt
ActualPC
Tt
AssignedPC
Tt
ClientID
Et
EMPLOYID (primary key)
Do
CUSTNMBR (primary key)
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC, NULL NULL NULL 3872
ClientID,
ActualPC
range checked for each record (key map: 35)
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
-> MODIFY ClientID VARCHAR(15);
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
mysql> ANALYZE TABLE tt;
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
ClientID, where
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
쿼리 성능 추정하기
SELECT 쿼리의 속도
WHERE 구문 최적화
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
(a>b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
아래의 테이블들은 상수 테이블로 사용된다:
SELECT * FROM t WHERE prime_key=1;
SELECT * FROM t1,t2
WHERE t1.prime_key=1 AND t2.prime_key=t1.id;
SELECT COUNT(*) FROM tbl_name;
SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
SELECT MAX(key_part2) FROM tbl_name
WHERE key_part1=constant;
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;
SELECT key_part2 FROM tbl_name GROUP BY key_part1;
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;
범위 최적화
단일 부분 인덱스에 대한 범위 접근 방식
다중 부분 인덱스에 대한 범위 접근 방식
단일 부분 인덱스에 대한 범위 접근 방식
SELECT * FROM t1
WHERE key_col > 1
AND key_col < 10;
SELECT * FROM t1
WHERE key_col = 1
OR key_col IN (15,18,20);
SELECT * FROM t1
WHERE key_col LIKE 'ab%'
OR key_col BETWEEN 'bar' AND 'foo';
SELECT * FROM t1 WHERE
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z');
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
이와 같은 조건문을 상수로 대체하면, 다음을 얻게 된다:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
필요 없는 TRUE 와 FALSE 상수를 제거하면, 다음을 얻게 된다:
(key1 < 'abc') OR (key1 < 'bar')
(key1 < 'bar')
다중 부분 인덱스에 대한 범위 접근 방식
key_part1 key_part2 key_part3
NULL 1 'abc'
NULL 1 'xyz'
NULL 2 'foo'
1 1 'abc'
1 1 'xyz'
1 2 'abc'
2 1 'aaa'
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
key_part1 cmp const1
여기에서, const1, const2, … 는 상수 (constants)이며, cmp는 =, <=>, 또는 IS NULL 비교연산자 중의 하나이며, 그리고 조건은 모든 인덱스 부분을 커버 (cover)한다. (즉, N 개의 조건이 존재하고, N-부분 인덱스의 각 부분에 대해 하나씩 존재한다.) 예를 들면, 아래의 것은 세 부분의 HASH 인덱스에 대한 범위 조건이 된다:
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
상수 정의에 대한 부분에 대해서는, Section 7.2.5.1, “단일 부분 인덱스에 대한 범위 접근 방식”을 참조할 것.
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
생성된 인터벌이 초기 조건 보다 많은 열을 가질 수도 있다. 예를 들면, 위의 인터벌은 ('foo', 11, 0) 값을 가지고 있으며, 이것은 원래의 조건을 만족시키지는 못한다.
단일 인터벌은:
('foo',10,10) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
만일 인터벌 안에 있는 열 셋을 충족하는 조건이 OR와 결합을 한다면, 그 조건은 자신의 인터벌 조합 내에 포함되어 있는 열 셋을 충족하는 조건을 만든다. 만일 그 조건이 AND와 결합이 된다면, 그 조건은 자신의 인터벌에 대한 교차 (intersection) 부분 안에 있는 열 셋을 충족하는 조건을 형성한다. 예를 들면, 두 개 부분의 인덱스에서의 조건에 대해서는:
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
이 예제에서 보면, 첫 번째 줄의 인터벌은 왼쪽 경계에 대해서는 하나의 키 부분을 사용하고 오른쪽 경계에 대해서는 두 개의 키 부분을 사용하고 있다. 두 번째 줄의 인터벌은 하나의 키 부분만을 사용하고 있다. EXPLAIN 결과에 있는 key_len 컬럼은 사용된 키 접두사 (prefix)의 최대 길이를 나타낸다.
인터벌은:
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
어떤 경우에는, key_len이 사용된 키 부분을 가리키기도 하지만, 여러분이 기대하였던 것이 아닌 경우도 있다. key_part1 및 key_part2 이 NULL 이라고 가정하자. 그렇게 되면 key_len 컬럼은 아래의 조건에 대해서 두 개의 키 부분을 표시한다:
key_part1 >= 1 AND key_part2 < 2
하지만, 실제로 조건은 아래와 같이 변환된다:
key_part1 >= 1 AND key_part2 IS NOT NULL
인덱스 병합 최적화
인덱스 병합 교차 접근 알고리즘
인덱스 병합 결합 접근 알고리즘
인덱스 병합 정렬-결합 접근 알고리즘
SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;
SELECT * FROM tbl_name
WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;
SELECT * FROM t1, t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
AND t2.key1=t1.some_col;
SELECT * FROM t1, t2
WHERE t1.key1=1
AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
이 쿼리에 대해서는, 두 가지 가능성이 생긴다:
하지만, 옵티마이저는 두 번째 스캔만을 고려한다. 만일 여러분이 원하는 것이 이것이 아니라면, IGNORE INDEX 또는 FORCE INDEX를 사용해서 옵티마이저가 인덱스 병합을 고려하도록 만들 수가 있다. 아래의 쿼리는 인덱스 병합을 사용해서 실행된다:
SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)
WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
SELECT * FROM t1 IGNORE INDEX(badkey)
WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
(x AND y) OR z = (x OR z) AND (y OR z)
(x OR y) AND z = (x AND z) OR (y AND z)
인덱스 병합 교차 접근 알고리즘
key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
SELECT * FROM innodb_table WHERE prime_key < 10 AND key_col1=20;
SELECT * FROM tbl_name
WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;
SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
인덱스 병합 결합 접근 알고리즘
key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
(key3='foo' AND key4='bar') AND key5=5;
인덱스 병합 정렬-결합 접근 알고리즘
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;
IS NULL 최적화
SELECT * FROM tbl_name WHERE key_col IS NULL;
SELECT * FROM tbl_name WHERE key_col <=> NULL;
SELECT * FROM tbl_name
WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;
SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
OR (t1.a=t2.a AND t2.a IS NULL AND ...);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
DISTINCT 최적화
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
LEFT JOIN 및 RIGHT JOIN 최적화
SELECT *
FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
SELECT *
FROM b JOIN a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
네스티드 조인(Nested Join) 최적화
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
은 아래와 동일하다:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
ON t1.a=t2.a
수식을 다음과 같이 변환한다:
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
ON t2.b=t3.b OR t2.b IS NULL
mysql> SELECT *
-> FROM t1
-> LEFT JOIN
-> (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
-> ON t1.a=t2.a;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+------+------+------+------+
mysql> SELECT *
-> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
-> LEFT JOIN t3
-> ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+------+------+------+------+
t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
t1 LEFT JOIN t2 ON t1.a=t2.a, t3.
mysql> SELECT *
-> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+------+------+------+------+
mysql> SELECT *
-> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+------+------+------+------+
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
WHERE t1.a > 1
SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1
t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3)
WHERE P(T1,T2,T3).
네스티드 루프 조인 알고리즘은 아래와 같이 이 쿼리를 실행한다:
FOR each row t1 in T1 {
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
이제, 네스티드 외부 조인을 가지고 있는 쿼리를 고려해 보자:
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3).
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
우리의 예제에서 보면, 아래의 수식으로 표현된 외부 조인 테이블 (outer join table)은 임베디드가 된다:
(T2 LEFT JOIN T3 ON P2(T2,T3))
FOR each row t3 in T3 {
FOR each row t2 in T2 such that P2(t2,t3) {
FOR each row t1 in T1 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
WHERE P(T1,T2,T3)
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t1,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f1:=TRUE
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
그리고:
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t3 in T3 such that P2(t1,t3) {
FOR each row t2 in T2 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f1:=TRUE
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
FOR each row t1 in T1 such that C1(t1) {
FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
FOR each row t1 in T1 such that C1(t1) {
BOOL f1:=FALSE;
FOR each row t2 in T2
such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
BOOL f2:=FALSE;
FOR each row t3 in T3
such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1 && P(t1,NULL,NULL)) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
외부 조인(Outer Join) 딘순화
(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
WHERE P(T1,T2) AND R(T2)
T1 LEFT JOIN T2 ON T1.A=T2.A
T2.B IS NOT NULL,
T2.B > 3,
T2.C <= T1.C,
T2.B < 2 OR T2.C > 1
T2.B IS NULL,
T1.B < 3 OR T2.B IS NOT NULL,
T1.B < 3 OR T2.B > 3
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T1.B
WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T1.B
WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T2.B
WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T2.B
WHERE T3.C > 0
SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
WHERE T3.C > 0 AND T3.B=T2.B
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
WHERE T3.C > 0 AND T3.B=T2.B
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A
WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN
(T2 INNER JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A
WHERE T3.C > 0,
SELECT * FROM T1 LEFT JOIN
(T2,T3)
ON (T2.A=T1.A AND T3.B=T2.B)
WHERE T3.C > 0.
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A AND T3.C=T1.C
WHERE T3.D > 0 OR T1.D > 0
SELECT * FROM T1 LEFT JOIN
(T2, T3)
ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
WHERE T3.D > 0 OR T1.D > 0
ORDER BY 최적화
SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1
WHERE key_part1=constant
ORDER BY key_part2;
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1=1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key1, key2;
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
GROUP BY 최적화
느슨한 인덱스 스캔 (Loose index scan)
타이트한 인덱스 스캔 (Tight index scan)
느슨한 인덱스 스캔 (Loose index scan)
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
SELECT c1,c2 FROM t1 GROUP BY c2, c3;
SELECT c1,c3 FROM t1 GROUP BY c1, c2;타이트한 인덱스 스캔 (Tight index scan)
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;LIMIT 최적화
테이블 스캔을 피하는 방법
?
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
INSERT 명령문 속도
데이터를 삽입시키고자 하는 MyISAM 테이블이 비어 있을 경우에는, LOAD DATA INFILE은 앞에서 설명하는 최적화 동작을 자동으로 실행한다는 점을 알아두자.
myisamchk을 사용하는 대신에 아래의 명령문을 사용해서 MyISAM 테이블에 대한 인덱스를 비활성화 또는 활성화 시킬 수도 있다. 이 명령문을 사용한다면, FLUSH TABLE은 실행하지 않아도 된다:
ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;
LOCK TABLES a WRITE;
이렇게 하면 속도를 개선 시킬 수가 있는데, 그 이유는 인덱스 버퍼는 모든 INSERT 명령문이 실행 완료된 후에 디스크에 오직 한번만 플러시 되기 때문이다. 보통의 경우에는, INSERT 명령문 수 만큼의 인덱스 버퍼 플러시가 발생한다. 만일 여러분이 단일 INSERT 명령문만을 가지고 모든 열을 삽입할 수가 있다면, 명확한 잠금 명령문은 필요하지 않게 된다.
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
...
UNLOCK TABLES;
트랜젝셔널 테이블에 대한 보다 빠른 삽입 성능을 얻기 위해서는, LOCK TABLES를 사용하는 대신에, START TRANSACTION 와 COMMIT를 사용해야 한다.UPDATE 명령문 속도
DELETE 명령문 속도
다른 여러 가지의 최적화 팁(Tips)
?
SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(col1,col2))
AND col1='constant' AND col2='constant';
UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
이것은 테이블-레벨 잠금 (단일 라이터 (writer)를 가지고 있는 다중의 리더 (reader))만을 가지고 있는 MyISAM 테이블과 같은 스토리지 엔진을 사용할 경우에는 매우 중요하다.
출처 : MySQL 코리아