최적화 프로그램
이 문서에서는 MySQL 쿼리 최적화 프로그램의 작동 방식을 설명합니다. MySQL 쿼리 최적화 프로그램은 주로 실행된 쿼리에 대해 가장 효율적인 경로(루틴, 방향)를 결정합니다.
하나. 소스 코드 및 개념
이 부분에서는 옵티마이저의 주요 개념과 용어를 설명하고, 이것이 MySQL 소스 코드와 어떻게 대응되는지 설명합니다.
1. 정의
좁은 정의: 옵티마이저는 DBMS가 쿼리할 때 사용할 실행 경로를 결정하는 데 사용하는 일련의 경로입니다.
MySQL은 쿼리 경로를 조정하는 경우가 많기 때문에 이 문서에 설명된 로직과 소스 코드의 로직을 비교해야 합니다. 비교를 쉽게 하기 위해 소스 코드/sql/sql_select.cc,optim_cond() 함수 등 관련 파일 및 경로에 대한 주석이 여기에 포함됩니다.
쿼리를 다른 쿼리로 변환하여 결과가 동일할 경우 명령문 변환이 발생합니다. 다음 쿼리
SELECT ... WHERE 5 = a
는
SELECT ... WHERE a = 5
로 변환됩니다. 가장 확실한 명령문 변환이 있습니다. 개수가 적으며 더 빠른 실행을 위해 일부 명령문이 변환됩니다.
2. 옵티마이저 소스 코드
다음 의사코드는 /sql/sql_select.cc에 있는 handler_select() 함수의 논리적 구조를 보여줍니다. (소스 코드/sql/sql_select.cc가 SQL 쿼리를 처리합니다.)
handle_select()
mysql_select()
JOIN::PRepare()
setup_fields()
JOIN::optimize () /* 최적화 도구는 여기에서 제공됩니다. ... ()
/* 테이블에 액세스하는 가장 좋은 방법을 찾으세요 */
/* 사용자가 지정한 대로 */
optim_straight_join()
best_access_path ()
/* 전체 또는 하위 집합 중에서 (차선)최적의 계획을 찾습니다* / / * 사용자가 검색의 완전성을 제어하는 모든 가능한 계획 중에서 * /
Greedy_Search ()
item_search ()
Best_access_path ()
/ * 최적의 계획을 위한 철저한 검색 수행 * /
Find_best ()
Make_join_Select () / * ... to 여기서 * /
Join :: Exec ()
어떤 함수가 어떤 함수를 호출하는지, 예를 들어 handler_select() 함수는 mysql_select() 함수를 호출하고 mysql_select() 함수는 JOIN::prepare()를 호출합니다. JOIN::optimize(), JOIN::exec() 등. mysql_select() 함수의 첫 번째 부분은 JOIN::prepare()를 호출하는 것입니다. 이 함수는 컨텍스트 분석, 메타데이터 생성 및 일부 명령문 변환에 사용됩니다. 쿼리 최적화 함수 JOIN::optimize() 및 최적화 프로세스의 모든 하위 경로. JOIN::optimize() 함수가 실행된 후 JOIN::exec()가 JOIN::optimize() 함수의 최적화 결정 후 실행 작업을 인계 받아 완료합니다.
JOIN이라는 단어가 나오긴 하지만 실제로 쿼리 옵티마이저는 JOIN 조인 쿼리뿐만 아니라 모든 쿼리 유형을 처리합니다.
둘. 주요 최적화
이 섹션에서는 서버에서 수행되는 가장 중요한 최적화에 대해 설명합니다.
1. 상수 관계 최적화
상수 등가 전이
WHERE 컬럼1 = 컬럼2 AND 컬럼2 = 'x'
WHERE column1='x' AND column2='x'
다음과 같은 경우에만 연산자는 다음과 같습니다. 다음 중 하나에 해당하면 명령문 변환은 컬럼1 <연산자> 컬럼2 조건에서 발생합니다:
=, <, >, <=, >=, <>, <= >, LIKE
참고: 등가 전송 변환은 BETWEEN에 적합하지 않습니다. LIKE에도 적합하지 않을 수 있지만 그건 나중에 이야기하겠습니다.
소스 코드,change_cond_ref_to_const() 함수는 /sql/sql_select.cc를 참조하세요. 또는 /sql/sql_select.cc, propagate_cond_constants() 함수.
데드 코드 제거
항상 TRUE인 조건은 다음과 같은 명령문 변환을 거칩니다.
WHERE 0=0 AND column1= ' y'
이 경우 첫 번째 조건이 제거되고 최종 조건은 다음과 같습니다.
column1='y'
소스 코드는 /sql/을 참조하세요. sql_select.cc,remove_eq_conds( ).
항상 FLASE인 조건은 다음과 같은 명령문 변환도 수행합니다.
WHERE (0 = AND s1 = OR s1 = 7
괄호 및 처음 두 개 첫 번째 조건은 항상 FLASE이고 마지막 조건은 다음과 같습니다.
WHERE s1 = 7
WHERE 문이 나타내는 경우도 있습니다. 불가능한 조건인 경우 쿼리 최적화 프로그램은 다음과 같이 모든 문을 제거할 수 있습니다.
WHERE (0 = AND s1 = 5)
이 조건은 결코 TRUE가 아니기 때문에 Impossible WHERE가 EXPLAIN 분석에 표시됩니다. 간단히 말해서 MySQL은 WHERE 조건이 최적화되었다고 말합니다.
필드가 NULL이 될 수 없는 경우 최적화 프로그램은 관련 없는 IS NULL 조건을 모두 제거하므로
WHERE not_null_column IS NOT NULL
이 조건은 항상 FLASE이고
WHERE not_null_column IS NOT NULL
이 조건은 항상 TRUE이므로 이 필드 쿼리의 조건도 제거됩니다. . 이 판단은 매우 미묘합니다. 예를 들어 OUT JOIN 외부 조인에서 필드는 NOT NULL로 정의되고 여전히 NULL 값을 포함합니다. 이 특별한 경우에는 최적화 프로그램이 IS NULL 조건을 제외합니다. >
CREATE TABLE Table1 (column1 CHAR( 1));
...SELECT * FROM Table1 WHERE 컬럼1 = 'Popgo';
CREATE TABLE 정의 조건에서 불가능하더라도 최적화 프로그램은 이 쿼리의 조건을 제거하지 않습니다.
결합 가능한 상수 값
다음 표현식은 문 변환을 거칩니다.
WHERE 컬럼1 = 1 + 2
마지막으로:
WHERE 컬럼1 = 3
2. 테이블의 표현식은 WHERE 조건에 의해 제한되며 컬럼 = "상수", 테이블 기본 키의 모든 필드 또는 고유 키의 모든 필드(고유 키 필드는 NOT NULL로 정의됨) 형식의 표현식을 포함합니다. 🎜>
예를 들어 Table0 테이블의 정의에는 다음이 포함됩니다.
그런 다음 쿼리 표현식은
FROM Table0.. WHERE 컬럼1=5 AND 컬럼2=7 ...
은 상수 테이블을 반환합니다. 더 간단히 말하면, Table1 테이블의 정의에
... Unique_not_null_column INT NOT NULL UNIQUE
가 포함되어 있으면 쿼리 표현식은
FROM Table1 입니다. . WHERE Unique_not_null_column=5
도 상수 테이블을 반환합니다.
SELECT Table1.unique_not_null_column, Table2.any_column
WHERE Table1.unique_not_null_column = Table2.any_column
AND Table1.unique_not_null_column = 5;MySQL의 경우 이 명령문을 평가하면 먼저 상수 테이블의 두 번째 정의에 따라 쿼리 조건이 Unique_not_null_column인 Table1 테이블이 상수 테이블(상수 테이블)이고 이 값을 얻는다는 것을 알 수 있습니다.
값이 실패하면, 즉 테이블 Table1에 고유_not_null_column = 값이 없으면 EXPLAIN 이후의 결과:
불가능 WHERE는 const 테이블을 읽은 후 알 수 있습니다
On 반대로 값을 성공적으로 구한 경우, 즉 Unique_not_null_column = Table1의 레코드 값인 경우 MySQL은 이를 다음 명령문으로 변환합니다.
SELECT 5, Table2.any_column
FROM Table1, Table2
AND 5 = 5;
실제로 이는 좋은 예입니다. 최적화 프로그램은 앞에서 언급한 지속적인 동등 전달로 인해 일부 명령문 변환을 수행합니다. 또한, 상수 동일 값 전송을 먼저 설명해야 하는 이유는 MySQL이 상수 테이블이 무엇인지 확인하기 전에 수행되기 때문입니다. 최적화 단계의 순서는 때때로 다릅니다.
2. JOIN 연결 최적화
이 섹션에서는 JOIN 연결을 최적화하는 다양한 방법에 대해 설명합니다. 참고: JOIN 연결은 JOIN 유형뿐만 아니라 모든 조건부 쿼리 유형도 참조합니다. 어떤 사람들은 이를 액세스 유형이라고 부르는 것을 선호합니다.
JOIN 조인 유형 결정
쿼리 조건 표현식을 평가할 때 MySQL은 해당 표현식이 속한 JOIN 조인 유형을 결정합니다.
다음 JOIN 유형이 파일에 기록되며 최고에서 최악으로 정렬됩니다.
system: 시스템 유형의 상수 테이블(상수 테이블)
const: 상수 테이블(상수 테이블)
eq_ref: 등식 관계의 고유 또는 기본 키 인덱스
ref: 등식 관계의 인덱스, 이 인덱스의 값은 NULL이 될 수 없습니다.
ref_or_null: 등식 관계의 인덱스, 값 이 인덱스는 NULL일 수 있습니다.
range: BETWEEN, IN, >=, LIKE 등과 같은 관련 인덱스
index: 순차 스캔 인덱스
ALL: 전체 테이블 데이터의 순차 스캔
소스 코드 sql_select.h, enum Join_type{}은 /sql/을 참조하세요. 또한 JOIN 연결형 서브쿼리의 경우 파일에 기록되지 않는 부분이 조금 있습니다.
옵티마이저는 다음과 같이 JOIN 연결 유형을 사용하여 구동 표현식을 선택합니다.
SELECT *
FROM Table1
WHERE indexed_column = AND unindexed_column = 6
indexed_column이 더 나은 JOIN 연결 유형을 가지면 추진력 있는 표현이 될 가능성이 더 높습니다. 다양한 예외도 발생하지만 이 설명은 첫 번째 간단한 최적화 규칙입니다.
운전자에게 가장 의미 있는 일은 무엇인가요? 다음 쿼리에는 두 가지 실행 경로가 있습니다.
최악의 실행 계획: 읽기 테이블의 모든 행을 스캔합니다. 이를 Table1의 순차 스캔 또는 단순 테이블 스캔이라고도 합니다. 각 행을 쿼리하여 indexed_column과 unindexed_column의 값이 쿼리 조건과 일치하는지 확인합니다.
최고의 실행 계획: indexed_column = 인덱스를 통한 값으로 레코드를 검색합니다. 이를 색인 검색이라고도 합니다. 각 행을 쿼리하여 unindexed_column 컬럼의 값이 쿼리 조건과 일치하는지 확인합니다.
인덱스 검색은 일반적으로 순차 스캔보다 적은 액세스가 필요하며, 액세스되는 테이블이 크고 인덱스가 고유한 경우 테이블 액세스가 매우 적습니다. 이것이 좋은 실행 계획을 가진 액세스 테이블이 더 좋은 이유이고, indexed_column이 드라이버로 자주 사용되는 이유입니다.
조인 및 액세스 방법
단일 테이블 검색에서 잘못된 JOIN 조인 실행 선택은 잘못된 실행 선택보다 더 많은 성능 손상을 초래합니다. 따라서 MySQL 개발자는 쿼리의 테이블이 최적의 순서로 조인되고 테이블 데이터를 확인하기 위해 최상의 액세스 방법(종종 액세스 경로라고 함)이 선택되는지 확인하는 데 더 많은 시간을 소비합니다. 고정된 테이블 조인 순서와 모든 테이블에 대한 해당 테이블 액세스 방법의 조합을 QEP(쿼리 실행 계획)이라고 합니다. 쿼리 최적화 프로그램의 목적은 가능한 모든 계획 중에서 최상의 QEP를 찾는 것입니다. JOIN 연결 우선순위에는 몇 가지 일반적인 개념이 있습니다.
각 계획 또는 계획의 일부는 COST로 정의됩니다. 계획 비용은 계획대로 쿼리를 계산하는 데 필요한 리소스를 대략적으로 반영하며, 주요 요소는 쿼리를 계산할 때 액세스한 총 레코드 수입니다. QEP를 다양한 비용에 할당하는 방법이 있으면 이를 비교할 수 있는 방법이 있습니다. 이러한 방식으로 옵티마이저의 목적은 가능한 모든 계획 중에서 가장 비용이 낮은 QEP를 찾는 것입니다.
MySQL에서는 최고의 QEP 검색이 상향식 접근 방식으로 구현됩니다. 옵티마이저는 먼저 하나의 테이블에 대한 모든 계획을 확인한 다음 두 테이블에 대한 모든 계획을 확인하는 방식으로 완전한 최적의 QEP가 설정될 때까지 계속됩니다. 쿼리에 테이블과 조건자 중 일부만 포함하는 쿼리 계획을 부분 계획이라고 합니다. 옵티마이저는 부분 계획에 더 많은 테이블이 추가될수록 비용이 높아진다는 사실에 의존합니다(참고: 비용이 높을수록 실행 효율성이 낮아집니다). 이를 통해 최적화 프로그램은 현재 최고의 전체 계획보다 저렴한 부분 계획만 사용하여 더 많은 테이블로 확장할 수 있습니다.
최고의 QEP를 검색하는 핵심 경로를 완성하려면 sql/sql_select.cc, find_best()를 참조하세요. 가능한 모든 계획을 철저하게 검색하여 결국 최적의 계획을 찾도록 보장합니다.
아래에서는 find_best() 메소드의 의사코드를 설명합니다. 이는 재귀적이므로 입력 변수 중 일부는 지금까지 이전 반복의 위치를 나타내기 위해 표시됩니다.
remaining_tables = {t1, ..., tn} /* 쿼리에서 참조되는 모든 테이블 */
프로시저 find_best(
부분 계획 in, /* in, 지금까지 조인된 테이블의 부분 계획 */
부분_계획_비용, /* in, 부분 계획 비용 */
잔여_테이블, /* in , 부분 계획에서 참조되지 않은 테이블 세트 */
best_plan_so_far, /* in/out, 지금까지 찾은 최상의 계획 */
best_plan_so_far_cost)/* in/out, best_plan_so_far의 비용 */
{
남은_테이블의 각 테이블 T에 대해
{
/* 테이블 T 사용 비용을 계산합니다.
최적화 프로그램이 고려하는 요소는 다음과 같습니다.
테이블의 행이 많음(불량)
지금까지 테이블과 공통된 핵심 부분이 많음(매우 좋음)
WHERE 절에 언급된 제한 사항(좋음)
긴 키(좋음)
고유 키 또는 기본 키(좋음)
전체 텍스트 키 (BAD)
기타 요인을 고려할 가치가있는 다른 요인 : 키 짧은 평균/최대 키 길이
작은 테이블 파일
인덱스 모든 수준 ORDER BY / GROUP 열은 이 테이블에서 나옵니다. */
cost = complex-series-of-calculations;
/* 지금까지의 비용에 비용을 더합니다. */
부분_계획_비용+= 비용;
if (partial_plan_cost >= best_plan_so_far_cost)
계속;
부분_계획= best_access_method로 부분 계획 확장;
if (remaining_tables가 빈 세트가 아님)
{
find_best(partial_plan, 부분_계획_비용,
잔여_테이블 ,
best_plan_so_far, best_plan_so_far_cost);
}
else
{
best_plan_so_far_cost= 부분 계획_비용;
best_plan_so_far= 부분 계획;
}
}
}
这里优화器利果评估比起目前为止最好的评估，变得更差，它将停止搜索。扫描的顺序依赖于出现FROM语句中的表的顺序。
RANGE联接类型
有些条件可以使用索引，但是在一个键的范围（range）或宽島内。这些称为范围条件，最常看到的是带> ;，>=，<，<=，IN，LIKE，BETWEEN的查询表达式。
对优化器来说，如下表达式：
column1 IN (1,2,3)
和这个是一样的：
MySQL同样对待这种语句，无需对查询条件的IN到OR或OR到IN做转变。
如下语句，优化器也会用到索引（范围查询범위 검색)
column1 LIKE 'x%'
但这种就不行：
也就是说，如果匹配条件的第一个字符是配符，那就没范围查询。
同样，如下个语句也是一样的
and
열1 >= AND 열1 <= 7
쿼리 조건이 너무 많은 인덱스 키를 검색하는 경우 최적화 프로그램이 RANGE 조인 유형을 ALL JOIN 조인 유형으로 변경할 수 있습니다. 이와 같은 변환은 특히 < 및 > 조건과 다중 레벨 보조 인덱스에서 가능합니다. 소스 코드: /myisam/mi_range.c, mi_records_in_range()(MyISAM 인덱스)를 참조하세요.
INDEX 조인 유형
이 쿼리를 고려하십시오.
SELECT column1 FROM Table1;
column1이 인덱싱된 경우 최적화 프로그램은 테이블이 아닌 추가된 인덱스에서 값을 가져오도록 선택할 수도 있습니다(전체 테이블 스캔). 이와 같은 인덱스를 일반적으로 커버링 인덱스(COVERING INDEX)라고 합니다. EXPLAIN Extra 설명에서 MySQL은 단순히 INDEX라는 단어를 사용하여 포함 인덱스(COVERING INDEX)를 나타냅니다.
명령문:
SELECT column1, column2 FROM Table1; 인덱스가 다음과 같이 정의된 경우에만 옵티마이저는 JOIN 연결 유형 INDEX를 사용합니다: 조인 유형 = 인덱스 CREATE INDEX. . ON Table1 (column1,column2);
즉, 쿼리된 필드(예:column1,column2)는 인덱스가 있어야 하며 인덱스가 있는 여러 필드는 순서가 없습니다. 따라서 다중 열 인덱스를 COVERING INDEX로 엄격하게 정의하여 검색 고려 사항과 관계없이 사용하는 것이 더 합리적입니다.
INDEX MERGE 조인 유형
개요
테이블 조건을 변환할 수 있는 경우 인덱스 병합(INDEX MERGE) 사용
cond_1 OR cond_2 ... OR cond_N
변환 조건은 다음과 같습니다. 각 cond_i(cond_1, cond_2...) 조건을 범위 검색에 사용할 수 있으며 거기에 동일한 인덱스를 가진 조건 쌍( cond_i, cond_j)이 없습니다. cond_i 및 cond_j 조건이 동일한 인덱스를 사용하는 경우 cond_i 또는 cond_j 조건을 단일 범위 스캔으로 결합할 수 있으며 병합할 필요가 없습니다.
다음 쿼리는 인덱스 병합(INDEX MERGE)을 사용합니다.
SELECT * FROM t WHERE key1=c1 OR key2 SELECT * FROM t WHERE (key1=c1 OR key2 INDEX MERGE Optimizer key1 < c1 OR key2 < c2 sel_imerge_cond = (t_1 OR t_1 OR ... OR t_n) 인덱스 병합(INDEX MERGE) 최적화 프로그램은 인덱스 병합(INDEX MERGE)이 행에 액세스할 수 있는 모든 경로 목록을 수집합니다. 이 SEL_IMERGE 구조 목록은 RANGE 최적화 프로그램 범위 RANGE 쿼리의 경우 MySQL 최적화 프로그램은 다음 형식으로 SEL_TREE 객체를 구축합니다. range_cond = (cond_key_1 AND cond_key_2 AND ... AND cond_key_N) 2. 다음 주요 구성 요소에 대한 조건이 있는 SEL_ARG 개체를 구현하는 것은 다음 주요 구성 요소에 대한 조건이 있는 SEL_ARG 개체에 대한 것입니다. 행 검색 알고리즘 '인덱스만' 활성화; { while(clustered_pk_scan에 대한 다음 행 검색) } 그러나 MySQL은 다음과 같은 연산을 사용한 전치를 지원하지 않습니다. WHERE 5 = -column1 이처럼 열 = 상수 표현식을 전치하는 것은 더 나은 인덱스 검색을 위한 것입니다. 이 형식의 명령문에 인덱스 필드가 있는 경우 MySQL은 테이블 크기에 관계없이 항상 인덱스 필드를 사용합니다. (예외: 테이블에 레코드가 없거나 레코드 행이 하나만 있는 경우 상수 테이블이므로 특별한 처리가 필요합니다. 상수 값 및 상수 테이블을 참조하세요.) WHERE 컬럼1 = 'x' AND 컬럼2 = 'y' 2. 이전 사항 외에도 조건 중 하나에 더 나은 JOIN 조인 유형이 있는 경우 JOIN 조인 유형이 있는 드라이버를 선택합니다. (JOIN 연결 유형 결정 참조) CREATE TABLE Table1 (s1 INT, s2 INT); ... WHERE 컬럼1 = 'x' OR 컬럼2 = 'y' 특정 상황에서 인덱스 병합(INDEX MERGE)을 사용하는 옵션도 있습니다. 자세한 내용은 INDEX MERGE Optimizer 및 Index Merge Optimization을 참조하세요. WHERE 컬럼1 = 'x' OR 컬럼1 = 'y' UNION 쿼리 UNION이 포함된 모든 SELECT 쿼리 문은 별도로 최적화됩니다. 따라서 이 쿼리는 다음과 같습니다. SELECT * FROM Table1 WHERE 컬럼1 = 'x' NOT(<>) 관계 논리 조건은 다음과 같습니다. 다음과 같은 논리 조건도 있습니다. WHERE NOT (column1 != 5) WHERE 컬럼1 = 5 위의 두 가지 상황에 대한 새로운 최적화 방법이 추가되기를 기대합니다. 일반적으로 옵티마이저는 행 레코드가 어쨌든 정렬된 것을 발견하면 ORDER BY 문으로 점프합니다. 정렬 프로세스. 그러나 확인해야 할 몇 가지 예외가 있습니다. 예: 최적화 프로그램은 데드 코드 삭제의 예이기도 한 ORDER BY 문을 버립니다. SELECT 컬럼1 FROM Table1 ORDER BY 컬럼1; 최적화 프로그램은 컬럼1의 인덱스(존재하는 경우)를 사용합니다. 그러나 혼동하지 마십시오. 인덱스는 레코드 값을 찾는 데만 사용됩니다. 또한, 인덱스를 순차적으로 스캔하는 비용은 전체 테이블을 순차적으로 스캔하는 비용보다 저렴하므로(일반적으로 인덱스의 크기는 데이터 값의 크기보다 작습니다), 이는 INDEX JOIN 연결 유형이 더 많은 이유입니다. ALL 유형보다 최적화되었습니다. JOIN 조인 유형 결정을 참조하세요. SELECT * FROM Table1 소스 코드 보기: /sql/sql_select.cc, test_if_order_by_key() 및 /sql/sql_select.cc, test_if_skip_sort_order(). GROUP BY 및 관련 조건(HAVING, COUNT(), MAX(), MIN(), SUM에 대한 주요 최적화 (), AVG(), DISTINCT()). GROUP BY는 인덱스가 있으면 해당 인덱스를 사용합니다. SELECT COUNT(column1) FROM Table1; MAX() 및 MIN()에 대한 새로운 최적화 방법. 예: 다음 형식으로 쿼리를 최적화하고 문 변환을 수행합니다. DISTINCT 문이 항상 GROUP BY로 변환되는 것은 아니기 때문에 DISTINCT가 포함된 쿼리 문이 항상 정렬된 결과 집합을 가질 것이라고 기대하지 마세요. 그러나 쿼리에 ORDER BY NULL이 포함되지 않는 한 GROUP BY 최적화 규칙을 사용할 수 있습니다. 셋. 기타 최적화 1. ref 및 eq_ref에 대한 NULL 값 필터링 액세스 초기 NULL 값 필터링 다음과 같은 조인 시퀀스가 있다고 가정합니다: ..., tblX, ..., tblY , . .. 이 기능의 구현 코드는 다음과 같습니다. 모든 방정식에 IS NOT NULL 조건자를 추가하면 (실제로 사용되는 유형이 아닌) 참조 액세스 유형에서 사용될 수 있습니다. 그러나 현재는 이 작업이 수행되지 않습니다. 늦은(Late) NULL 필터링 ref 액세스 유형 tblX를 통해 액세스되는 테이블 tblX 쿼리 계획이 있다고 가정합니다. key_part1 = expr1 AND tblX.key_part2 = expr2 AND ... 2. 파티션 관련 최적화 이 부분에서는 MySQL 파티션 관련 최적화에 대해 설명합니다. MySQL5.1 파티셔닝과 관련된 개념 및 구현은 파티셔닝을 참조하세요. 파티션 프루닝(pruning) 파티션 프루닝 동작은 다음과 같이 정의됩니다. "파티션 프루닝(partition pruning)과 같은 쿼리를 제공합니다. 이 분할된 테이블의 DDL 문과 쿼리의 WHERE 또는 ON 문을 검색하고 이 쿼리에서 액세스한 최소 파티션 세트를 찾습니다.” 결과 파티션 세트 비율입니다. 테이블의 모든 파티션 세트는 훨씬 작으며 이 파티션 세트는 후속 쿼리 문에서도 사용됩니다. 이 파티션 세트에 추가되지 않은 다른 파티션은 액세스되지 않습니다. 즉, 파티션이 정리되었습니다. 이로 인해 쿼리가 더 빠르게 실행됩니다. 파티션 프루닝은 테이블의 스토리지 엔진에 의존하지 않으므로 이 기능은 MySQL 쿼리 최적화 프로그램의 일부입니다. 다음 섹션에서는 파티션 정리에 대해 자세히 설명합니다. 파티션 정리 개요 파티션 정리의 구현 단계는 다음과 같습니다. 1. WHERE 문 조건을 분석하고 분석 결과를 설명하는 간격 그래프를 구성합니다. 2. 간격 그래프를 통해 각 간격별로 방문한 파티션(하위 파티션 포함)의 집합을 찾습니다. 3. 쿼리에 필요한 파티션 집합을 구성합니다. Partitioning Intervals Single-Point Intervals 가장 간단한 경우부터 N개라고 가정합니다. 열의 파티션 테이블은 다음과 같이 표시됩니다. 파티션 유형 p_type 및 파티션 함수 p_func는 다음과 같습니다. CREATE TABLE t (columns) 참고: 이 프로세스는 WHERE 조건이 위의 형식인 경우에만 작동하며 테이블의 각 열은 임의의 상수와 동일한지 확인해야 합니다. 각 열에 대해 동일한 상수여야 합니다). 예를 들어 위 예의 WHERE 문에 col1=const1이 없으면 p_func 파티션 함수의 값을 계산하지 않으며 사용된 실제 파티션 세트를 제한하지 않습니다. 간격 걷기(Walking) 파티션 테이블 t가 열 집합으로 정의되고 파티션 유형이 p_type이고 파티션 함수 p_func가 다음을 사용한다고 가정합니다. 정수 유형 필드 int_col , 다음과 같습니다: CREATE TABLE t (columns) > 소스 코드에서는 이 변환을 간격 걷기(Walking)라고 합니다. . 짧은 간격으로 이동하는 데 드는 비용이 비싸지 않으므로 작은 파티션을 스캔하기 위해 파티션 수를 줄일 수 있습니다. 그러나 긴 범위를 탐색하는 것은 그리 효율적이지 않으며 많은 수의 파티션을 검사해야 하며, 이 경우 모든 파티션을 검사할 수 있습니다. 다음 매개변수는 간격 걷기(Walking)의 값을 결정합니다. #define MAX_RANGE_TO_WALK=10 참고: 다음 조건부 관계도 위의 간격 논리를 사용하여 걷기: const1 >= int_col >= const2 다음 파티션을 가정합니다. 테이블 정의: const1 <= t.column <= const2 const1 <= t.column 자체 파티셔닝 함수는 오름차순으로 다음 관계를 살펴보세요. A와 B를 사용하여 이 관계의 가장 왼쪽과 가장 오른쪽 부분을 나타내면 관계를 다음과 같이 다시 작성할 수 있습니다. A < ;= p_func(t .column) <= B 테이블 파티션 ------x------x---------x---------> 파티션에 액세스해야 하는 경우 해당 간격과 검색 간격 [A, B]에는 빈 교차점이 없습니다. 검색 간격 ----x==================x------> 이전 섹션에서는 기본 WHERE 조건에서 활성 파티션 세트를 추론하는 여러 가지 방법을 설명했습니다. 범위 파티셔닝(RANGE 파티셔닝)과 열거 파티셔닝(LIST 파티셔닝)의 하위 파티션을 제외하고 모든 것이 이러한 파티션의 추론 방법이 하위 파티션에 적합하다는 것을 보여줍니다. 각 파티션은 동일한 방식으로 분자 분할되어 있으므로 각 파티션 내에서 어떤 하위 파티션에 액세스할지 알아봅니다. 파티션 정리의 경우 RANGE 분석기는 파티션 나누기와 하위 파티션 나누기 함수에서 사용하는 테이블 열 목록인 WHERE 문을 사용하여 호출됩니다. subpart_col1, subpart_col2, ... subpart_colM) PARTITION BY LIST (pf) pf=1 AND ( sp1= 'foo' AND sp2 IN (40,50)) (루트) 上述图表，竖的边界（|)代表OR，横的 代表 代表 代表 代表 and, 横 的 的 和 竖 竖 的 也 也 代表 代表 代表 代表 代表 代表 代表 代表 代表 代表 代表 代表 分区 裁剪 裁剪 裁剪 裁剪 裁剪 裁剪 裁剪 裁剪 裁剪 裁剪 裁剪 代码 代码 游历 从 上方 到 下方 下方 下方 下方 下方 从 从 左边 到 到 右边 游历 游历 游历 游历 游历 游历 游历，并做了如下的推论 1。지금은 最上와 最左的区间，从使用分区的공공集合开始游历： 2。 执行pf=3의 분할기, 找到分区P1의 상대점수, 右移到sp1='bar' 分析sp1='bar' AND sp2=33区间，여기某SP3子分区找到行。推论三：在每个分区组成集合P1，标识子分区SP3“被使用” 4。 执行pf=8的区间分析，找到分区P3 五集合，右移到sp1='baz' 이제 sp1='baz'에 도달했으므로 더 이상 오른쪽으로 이동할 수 없으며 하위 파티션 간격을 구축할 수도 없습니다. pf=8을 기록하고 반환합니다 6. pf=9에서 아래로 이동하여 끝에 도달한 것을 확인하면 투어 그래프가 완성됩니다. 참고: 특정 상황에서 범위 분석기(RANGE 분석기)의 결과에는 OR 또는 AND 연산자로 구성된 여러 SEL_ARG 그래프가 있습니다. 이는 매우 복잡하거나 단일 범위 목록 구성을 허용하지 않는 WHERE 문에서 발생합니다. 이 상황에서 파티션 정리 코드는 적절한 작업을 사용합니다. 예: 소스 코드에서 파티션 정리 구현 소스 코드에 대한 간단한 설명: sql/opt_range.cc: sql/partition_info.h: class partition_info { /* }; 이 코드에는 모든 파티션 간격 분석 유형을 구현하는 방법이 포함되어 있습니다. 파티션 검색 일련의 인덱스 검색(예: ref, eq_ref, ref_or_null 조인 액세스 모드)으로 파티션 테이블에 액세스하는 경우 MySQL은 모든 파티션이 있는지 확인합니다. 인덱싱해야 함 특정 파티션에 대한 액세스를 검색하거나 제한합니다. 예: CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,1),(2,2),(3,3); CREATE TABLE t2 ( INSERT INTO t2 VALUES (1,1),(2,2),(3,3); 쿼리 조건은 다음과 같습니다. SELECT * FROM t1, t2 위 내용은 MySQL Internals Optimizer 내용입니다. 더 많은 관련 글은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!
단일 SEL_TREE 객체는 다음 조건과 같이 OR 문의 다른 멤버를 가진 키를 갖도록 구성할 수 없습니다.
MySQL 5.0부터 이러한 조건은 INDEX MERGE 메소드와 SEL_IMERGE 클래스에 의해 처리되는 범위 구조에 의해 색인화됩니다. SEL_IMERGE는 다음과 같이 표현되는 여러 SEL_TREE 개체의 분리를 나타냅니다.
각 t_i(t_1, t_2...)는 SEL_TREE에 대해 다음을 나타냅니다. , 서로 다른 SEL_TREE 객체의 쌍(t_i, t_j)은 단일 SEL_TREE 객체로 병합될 수 없습니다.
현재 구현 방법은 분석된 쿼리의 일부로 단일 SEL_TREE 개체를 생성할 수 없는 경우에만 SEL_IMERGE를 생성합니다. 단일 SEL_TREE 개체를 생성할 수 있는 것으로 확인되면 즉시 SEL_TREE를 삭제합니다. . 이는 실제로 제한 사항이며 최악의 행 검색 전략을 사용하게 될 수 있습니다. 다음 쿼리:
SELECT * FROM t WHERE (goodkey1=c1 OR goodkey1=c2) AND badkey=c3
(goodkey1, goodkey1) 인덱스에서도 badkey에 대한 스캔이 선택됩니다. 병합(INDEX MERGE)이 더 빨라집니다.
(t_11 OR t_12 OR ... OR t_1k) AND
(t_21 OR t_22 OR ... OR t_2l) AND
조건을 나타냅니다. .
인덱스 병합(INDEX MERGE) 생성자에 대한 자세한 내용은 소스 코드 sql/opt_range.cc, imerge_list_and_list(), imerge_list_or_list() 및 SEL_IMERGE 클래스의 멤버 함수를 참조하세요.
단일 cond_key_i 조건은 SEL_ARG 개체의 포인터 연결 네트워크로 표시됩니다. 각 SEL_ARG 객체는 키의 특정 부분을 참조하고 다음 조건을 나타냅니다.
sel_arg_cond= (inf_val < key_part_n AND key_part_n < sup_val) (1)
(2)
>
1. 구현 간격에는 상한 또는 하한 임계값이 없을 수도 있고 임계값이 포함되거나 포함되지 않을 수도 있습니다.
MySQL은 모든 깊이의 중첩된 AND-OR 조건을 위의 연결된 형식으로 변환합니다.
준비 단계:
while (key_i에서 다음 (키, rowid) 쌍 검색)
{
if (클러스터형 PK 스캔 없음) ||
사용 사용 사용 사용 클러스터형 PK 검색 조건 사용 ‐ out out out out of off ' ‐ ‐ ‐ ‐ rw‐ 및 🎜>행 검색 단계:
Unique
{
의 각 rowid는 행을 검색하여 출력에 전달합니다.
}
if(clustered_pk_scan)
3. 전치
MySQL은 단순 명령문 표현식의 전치(관계 연산자의 피연산자 순서 반전)를 지원합니다. 즉,
WHERE - 5 = 컬럼1
이 명령문은 다음과 같이 변환될 수 있습니다:
WHERE 컬럼1 = -5
그리고 이 문장은 동일하게 취급될 수 없습니다:
WHERE column1 = -5
AND 쿼리 형식은 다음과 같이 조건1 AND 조건2입니다.
CREATE INDEX Index1 ON Table1(s2);
SELECT * FROM Table1 WHERE s1 = AND s2 = 5;
이 쿼리를 해결하기 위한 전략을 선택할 때 최적화 프로그램은 s2의 인덱스가 먼저 생성되므로 s2 = 5를 드라이버로 선택합니다. . 이를 규칙이 아닌 우연한 효과로 간주하고 언제든지 변경될 수 있습니다.
OR 관계
OR 쿼리 형식은 다음과 같이 조건1 OR 조건2입니다.
이 쿼리 최적화 프로그램의 결정은 순차적 전체 테이블 스캔을 사용하는 것입니다.
위의 특정 상황은 두 조건의 필드가 동일한 경우 사용할 수 없습니다.
이 경우 명령문은 RANG 쿼리이므로 인덱싱됩니다. 이 주제는 IN 술어에 대한 논의에서 다시 볼 수 있습니다.
UNION ALL
SELECT * FROM TABLE1 WHERE 컬럼2 = 'y'
컬럼1과 컬럼2가 모두 인덱스된 경우, 각 SELECT는 인덱스 스캔을 사용하고 해당 결과 세트가 병합됩니다. 참고: 이 쿼리는 쿼리에서 순차 스캔 OR 예를 사용하는 경우 동일한 결과 집합을 생성할 수 있습니다.
column1 <> 5
다음과 동일:
column1 < 5 OR column1 > 5
그러나 MySQL은 이 조건에 대한 변환 문을 수행하지 않습니다. RANG을 사용하여 쿼리하면 더 나은 결과를 얻을 수 있다고 생각되면 직접 문을 수동으로 변환해야 합니다.
은
이 경우 MySQL은 명령문 변환을 수행하지 않습니다.
최적화 프로그램은 컬럼1의 인덱스(존재하는 경우)를 사용합니다.
WHERE column1 > x' AND 컬럼2 > 'x'
ORDER BY 컬럼2;
컬럼1과 컬럼2 모두 인덱스가 있는 경우 최적화 프로그램은 컬럼1의 인덱스를 사용합니다. 이 쿼리에서는 column2 값의 순서가 드라이버 선택에 영향을 주지 않습니다.
GROUP BY는 인덱스가 없으면 정렬을 사용합니다. 최적화 프로그램은 HASH 테이블 정렬을 사용하도록 선택할 수 있습니다.
GROUP BY x ORDER BY x의 경우 GROUP BY가 x를 기준으로 정렬되므로 옵티마이저는 ORDER BY가 불필요하다고 생각합니다.
최적화 프로그램에는 특정 HAVING 조건을 전송하는 WHERE 문의 코드가 포함되어 있습니다. 그러나 이 코드는 작성 당시에는 유효하지 않습니다. 소스 코드를 참조하세요: /sql/sql_select.cc, JOIN::optimize(), #ifdef HAVE_REF_TO_FIELDS 뒤.
테이블 핸들러에 유효한 빠른 행 개수가 있는 경우 다음 쿼리는 다음과 같습니다.
SELECT COUNT(*) FROM Table1;
모든 행 값을 스캔하지 않고도 행 개수를 가져올 수 있습니다. 이는 MyISAM 테이블에만 해당되고 InnoDB 테이블에는 해당되지 않습니다. 또한 이 쿼리
은 column1이 NOT NULL로 정의되지 않는 한 동일한 최적화를 갖지 않습니다.
SELECT MAX(column1)
FROM Table1
WHERE 컬럼1 < 'a';
컬럼1이 인덱스된 경우 'a' 값을 쿼리하여 최대값을 쉽게 찾을 수 있습니다. 인덱스 그리고 그 전에 인덱스 키가 반환됩니다.
SELECT DISTINCT column1 FROM Table1;
은 다음과 같습니다.
SELECT 컬럼1 FROM Table1 GROUP BY 컬럼1;
이 두 조건이 true인 경우에만:
* GROUP BY는 인덱스를 통해 완료될 수 있습니다. 이는 FROM 문에 테이블이 하나만 있고 WHERE 문이 없음을 의미합니다.
* LIMIT 문이 없습니다.
이 섹션에서는 기타 보다 특별한 최적화 방법에 대해 설명합니다.
이 섹션에서는 ref 및 eq_ref 조인 유형에 대한 NULL 값 필터링 최적화 방법에 대해 설명합니다.
추가로 테이블 tblY가 ref 또는 eq_ref 공용체 유형을 통해 액세스된다고 가정합니다.
tblY.key_column = tblX.column
또는 여러 주요 부분이 있는 참조를 사용합니다. :
... AND tblY.key_partN = tblX.column AND ...
tblX.column은 NULL일 수 있습니다. ref(또는 eq_ref) 유형에 접근할 때 초기 단계에서 NULL 필터링이 적용됩니다. 다음과 같이 추론합니다.
(tblY.key_partN = tblX.column) => (tblX.column IS NOT NULL)
원래 방정식은 tblX 및 테이블을 읽은 후에만 확인할 수 있습니다. 현재 행이 기록된 후 tblY입니다. IS NOT NULL 조건자는 tblX 테이블의 현재 행 레코드를 읽은 후에만 확인됩니다. tblX 및 tblY의 통합 유형 테이블에
개의 다른 테이블이 있는 경우 IS NOT NULL 조건자 검사를 통해 해당 테이블에 대한 액세스를 건너뛸 수 있습니다.
ref 분석기(update_ref_and_keys() 메소드 포함)는 다음과 같은 검사 및 플래그를 지정합니다. 위 유형의 쿼리 방정식 .
JOIN 연결 정렬을 선택한 후 add_not_null_conds()는 해당 테이블의 관련 조건에 적절한 IS NOT NULL 조건자를 추가합니다.
인덱스 검색을 호출하기 전에 expr(expr1, expr2, expr3...) 값이 NULL인지 확인합니다. 그렇다면 검색을 호출하지 않고 즉시 일치 항목이 없는 배열을 반환합니다.
이 최적화 방법은 초기 NULL 필터링으로 생성된 null_rejecting 속성을 재사용합니다. 이 검사의 소스 코드는 Join_read_always_key() 함수를 참조하세요.
비트랜잭션 테이블 엔진 MyISAM에 트랜잭션 스토리지 엔진이 없으면 전체 파티션 테이블에 잠금이 추가됩니다. 이론적으로는 사용 중인 파티션에 잠금을 추가하기만 하면 파티션 정리를 통해 동시성을 향상시킬 수 있습니다. 하지만 이 기능은 아직 구현되지 않았습니다.
구간 그래프는 상향식으로 구성되며 위 단계에 대한 설명을 나타냅니다. 논의에 이어 먼저 간격 그래프라는 용어를 정의한 다음 분할 간격을 사용하여 간격 그래프를 형성하는 방법을 설명하고 마지막으로 간격 그래프의 작업 흐름을 설명합니다.
PARTITION BY p_type(p_func(col1, col2,... colN)...);
쿼리의 WHERE 조건이 다음 형식이라고 가정합니다.
WHERE t.col1=const1 AND t.col2=const2 AND ... t.colN=constN
p_func(const1, const2 ... constN)을 계산하고 어떤 파티션에 포함되어 있는지 알아낼 수 있습니다. 레코드 WHERE 조건과 동일합니다. 참고: 이 프로세스는 모든 파티션 유형과 모든 파티션 기능에서 작동합니다.
PARTITION BY
p_type(p_func(int_col))
...
WHERE 조건이 있다고 가정합니다. 다음 형식의 쿼리:
WHERE const1 <= int_col <= const2
이 상황의 조건을 일련의 단일 지점 간격(Single-Point Intervals)으로 줄일 수 있습니다. , 다음과 같이 이 WHERE 문을 다음 관계로 변환합니다.
int_field = const1 또는
int_field = const1 + 1 또는
int_field = const1 + 2 또는
🎜
int_field = const2
간격 매핑(mapping)
CREATE TABLE t (columns)
PARTITION BY RANGE|LIST(unary_ascending_function(column))
=> p_func(const1) <=
범위 분할(RANGE partitioning)과 같이 각 분할은 분할 함수 값의 축에서 간격을 점유하며, 각 간격은 다음과 같이 연결되지 않습니다.
방문, 교차로가 하나 이상 있습니다. 검색 간격 [A, B]. 사용된 파티션 세트는 A에서 B로 실행되는 파티션을 결정하고 이 검색 범위 내에서 해당 지점을 수집합니다.
범위 분석기(RANGE Analyser) 작업의 결과를 SEL_ARG 그래프라고 합니다. 이것은 매우 복잡한 구조이므로 여기서는 설명하지 않겠습니다. 이 문화적 논의의 현재 초점은 모든 파티션을 탐색하고 파티션 및 하위 파티션의 범위를 수집할 수 있다는 것입니다.
해시별 하위 분할(sp1, sp2) (
(1)의 파티션 p0 값,
(2)의 파티션 p1 값,
(3)의 파티션 p2 값,
파티션 p3 값( 4),
PARTITION p4 VALUES IN (5),
);
이제 테이블 t에 대해 매우 복잡한 WHERE 문 쿼리를 가정합니다.
| :
| 파티셔닝 : 하위 파티셔닝
| :
| :
| :
| +------+ : +------------+ +---------+
---| pf=1 |----:------| sp1='foo' |---| sp2=40 |
+------+ : +------------+ +---------+
| : |
| : +---------+
| : | sp2=50 |
| : +---------+
| :
| :
+------+ : +------------+ +---------+
| pf=3 |----:--+--| sp1='바' |---| sp2=33 |
+------+ : | +----------+ +---------+
| : |
+------+ : |
| pf=4 |---:--+
+------+ :
| :
| :
+------+ : +------------+
| pf=8 |----:------| sp1='baz' |
+------+ : +------------+
执行pf=1的区间분할 ，找到分区P0的区集合，右移到sp1='foo'
再次右移，到sp2=40
分析sp1='foo' AND sp2=40区间，某SP1子分区找到行.推论一: 在每个分区组成集合P0，标识子分区SP1“被使用”
下移到sp2=50
分析sp1='foo'区间和sp2=50区间，현재某SP2子分区找到行。推论五：在每个分区组成集合P0，标识子分区SP2“被使用”
移回到pf=1,然后下称到pf=3
3。
再次右移,到sp2=33
移回到pf=3，然后下移到pf=4
执行pf=4的区间分析，找到分区P2 的找到分区合，右移到sp2='bar'
执行移动와类似的推论已已寁pf=3验证옳습니다.移回到pf=3,然后下称到pf=8
이전 프로세스에서는 더 이상 하위 파티션 범위를 제한할 수 없으므로 추론: P3 파티션 세트의 각 파티션에서 모든 하위 파티션이 유효하고 사용 중이라고 가정합니다. .
SELECT * FROM t1 WHERE partition_id=10 OR subpartition_id=20
이 예에서는 단일 범위가 없습니다. 구성되었지만 파티션 정리 코드는 사용된 파티션 세트가 합집합이라고 올바르게 추론합니다.
파티션의 모든 하위 파티션에는 partition_id=10인 행이 포함되어 있고, 각 파티션의 하위 파티션에는 subpartition_id=인 행이 포함되어 있습니다. 20.
이 코드에는 WHERE 절에서 간격까지, 메소드 prune_partitions()의 구현이 포함되어 있습니다. PartitionPruningModule 코드에서 시작하여 파티션 정리에 대한 자세한 줄별 코드 주석이 있습니다.
...
/*
사용된(즉, 정리되지 않은) 파티션의 비트맵입니다. 여기에는 파티션 정리 결과
가 저장됩니다.
*/
MY_BITMAP Used_partitions;
"가상 함수"는 이 분할된 테이블에서 간격 분석
을 수행하는 함수에 대한 포인터(opt_range.cc의 코드에서 사용됨)
*/
get_partitions_in_range_iter get_part_iter_for_interval;
get_partitions_in_range_iter get_subpart_iter_for_interval;
sql/sql_partition.cc:
keypart1 INT,
keypart2 INT,
KEY(keypart1, keypart2)
)
PARTITION BY HASH(keypart2);
WHERE t2.keypart1=t1.a
AND t2.keypart2=t1.b;
다음 알고리즘을 사용하여 실행합니다.
(t1의 각 레코드에 대해: )
{
t2->index_read({현재 값-of(t1.a), 현재 값-of(t1.b)});
while( t2->index_next_same( ) )
행 조합을 쿼리 출력에 전달합니다.
}
index_read() 호출에서 파티션 테이블 핸들은 식별된 모든 파티션 열의 값을 찾아냅니다. 이 예에서는 단일 열 b를 찾은 다음 파티션 액세스를 찾습니다. 이 파티션을 정리하면 다른 파티션에 액세스할 수 없습니다.
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
