데이터베이스 시스템은 경영정보시스템의 핵심입니다. 데이터베이스 기반 온라인 거래 처리(OLTP)와 온라인 분석 처리(OLAP)는 은행, 기업, 정부 및 기타 부서에서 가장 중요한 컴퓨터 애플리케이션 중 하나입니다. 이 글에서는 응용 사례를 바탕으로 데이터베이스 이론을 결합하여 실제 시스템에서 쿼리 최적화 기술을 적용하는 방법을 소개합니다. 대부분의 시스템의 응용 사례를 보면, 쿼리 작업은 다양한 데이터베이스 작업 중 가장 큰 부분을 차지하며, 쿼리 작업의 기반이 되는 SELECT 문은 SQL 문 중에서 가장 비용이 많이 드는 문입니다. 예를 들어, 은행의 계좌 데이터베이스 테이블 정보가 수백만, 심지어 수천만 개의 레코드로 누적되는 것처럼 데이터의 양이 특정 수준까지 누적되면 전체 테이블 스캔에 수십 분, 심지어 몇 시간이 걸리는 경우가 많습니다. 전체 테이블 스캔보다 더 나은 쿼리 전략을 채택하면 쿼리 시간을 몇 분으로 줄일 수 있는 경우가 많으며 이는 쿼리 최적화 기술의 중요성을 보여줍니다.
애플리케이션 프로젝트를 구현하는 동안 저자는 일부 프런트엔드 데이터베이스 개발 도구(예: PowerBuilder, Delphi 등)를 사용하여 데이터베이스 애플리케이션을 개발할 때 많은 프로그래머가 사용자 인터페이스의 아름다움에만 초점을 맞추고 비용을 지불하지 않는다는 사실을 발견했습니다. 질의문의 효율성을 중시하여 모든 문제가 발생합니다. 개발된 응용 시스템은 비효율적이며 심각한 자원 낭비를 초래합니다. 따라서 효율적이고 합리적인 쿼리문을 어떻게 디자인하느냐가 매우 중요하다. 이 글에서는 응용 사례를 바탕으로 데이터베이스 이론을 결합하여 실제 시스템에서 쿼리 최적화 기술을 적용하는 방법을 소개합니다.
문제 분석
많은 프로그래머들은 쿼리 최적화가 DBMS(데이터베이스 관리 시스템)의 작업이며 프로그래머가 작성한 SQL 문과는 거의 관련이 없다고 생각합니다. 이는 잘못된 것입니다. 좋은 쿼리 계획은 종종 프로그램 성능을 수십 배 향상시킬 수 있습니다. 쿼리 계획은 사용자가 제출한 SQL 문 모음이고, 쿼리 계획은 최적화 후 생성된 명령문 모음입니다. DBMS가 쿼리 계획을 처리하는 과정은 다음과 같습니다. 쿼리문의 어휘 및 구문 검사를 완료한 후 해당 명령문을 DBMS 쿼리 최적화 프로그램에 제출하고, 옵티마이저가 대수적 최적화 및 액세스 경로 최적화를 완료한 후 미리 컴파일된 모듈을 처리합니다. 문을 작성하고 쿼리 계획을 생성한 후 적절한 시점에 처리 및 실행을 위해 시스템에 제출하고 마지막으로 실행 결과를 사용자에게 반환합니다. 실제 데이터베이스 제품(Oracle, Sybase 등)의 상위 버전에서는 비용 기반 최적화 방법을 사용합니다. 이 최적화는 시스템 사전 테이블에서 얻은 정보를 기반으로 다양한 쿼리 계획의 비용을 추정한 후 선택할 수 있습니다. 더 나은 계획. 현재 데이터베이스 제품은 쿼리 최적화 측면에서 점점 더 좋아지고 있지만 사용자가 제출하는 SQL 문은 시스템 최적화의 기초가 됩니다. 따라서 원래 열악한 쿼리 계획이 시스템 최적화 후에 효율적이 될 것이라고 상상하기 어렵습니다. 사용자가 작성하는 진술은 매우 중요합니다. 지금은 시스템에서 수행되는 쿼리 최적화에 대해 논의하지 않습니다. 다음에서는 사용자 쿼리 계획을 개선하기 위한 솔루션에 중점을 둡니다.
문제를 해결하다
다음은 관계형 데이터베이스 시스템 Informix를 예로 들어 사용자 쿼리 계획을 개선하는 방법을 소개합니다.
1. 인덱스의 합리적인 활용
인덱스는 데이터베이스의 중요한 데이터 구조로, 기본 목적은 쿼리 효율성을 높이는 것입니다. 현재 대부분의 데이터베이스 제품은 IBM이 처음 제안한 ISAM 인덱스 구조를 사용합니다. 인덱스의 사용은 적절해야 하며, 사용원칙은 다음과 같다.
●연결 빈도가 높지만 외래 키로 지정되지 않은 컬럼에 대해 인덱스를 생성하고, 연결 빈도가 낮은 필드에 대해서는 최적화 프로그램이 자동으로 인덱스를 생성합니다.
● 자주 정렬되거나 그룹화되는(즉, 그룹화 또는 정렬 기준 작업) 열에 인덱스를 생성합니다.
●조건식에 자주 사용되는 다양한 값이 있는 열에 대한 검색을 생성합니다. 값이 거의 없는 열에는 인덱스를 생성하지 마세요. 예를 들어 직원 테이블의 '성별' 열에는 '남성'과 '여성'이라는 서로 다른 값 2개만 있으므로 인덱스를 생성할 필요가 없습니다. 인덱스를 생성하면 쿼리 효율성이 향상되지 않을 뿐만 아니라 업데이트 속도가 심각하게 저하됩니다.
●정렬할 열이 여러 개인 경우 해당 열에 복합 인덱스를 생성할 수 있습니다.
●시스템 도구를 사용하세요. 예를 들어 Informix 데이터베이스에는 의심스러운 인덱스를 검사할 수 있는 tbcheck 도구가 있습니다. 일부 데이터베이스 서버에서는 잦은 작업으로 인해 인덱스가 유효하지 않거나 읽기 효율이 저하될 수 있다. 특별한 이유 없이 인덱스를 사용하는 쿼리가 느려지는 경우에는 tbcheck 도구를 사용하여 인덱스의 무결성을 확인해 볼 수 있다. 필요한 경우 수리하십시오. 또한 데이터베이스 테이블이 대량의 데이터를 업데이트하는 경우 인덱스를 삭제하고 다시 작성하면 쿼리 속도를 향상시킬 수 있습니다.
2. 정렬을 피하거나 단순화하세요.
큰 테이블을 반복해서 정렬하는 것은 단순화하거나 피해야 합니다. 최적화 프로그램은 인덱스를 사용하여 자동으로 적절한 순서로 출력을 생성할 수 있는 경우 정렬 단계를 피합니다. 영향을 미치는 몇 가지 요소는 다음과 같습니다.
●색인에는 정렬할 하나 이상의 열이 포함되어 있지 않습니다.
●group by 또는 order by 절의 열 순서는 인덱스 순서와 다릅니다.
●정렬된 열은 다른 테이블에서 나옵니다.
불필요한 정렬을 피하려면 인덱스를 올바르게 추가하고 데이터베이스 테이블을 합리적으로 병합해야 합니다(때때로 테이블의 정규화에 영향을 미칠 수 있지만 효율성 향상은 그만한 가치가 있습니다). 부득이하게 정렬을 해야 한다면 정렬할 열의 범위를 줄이는 등 단순화를 시도해야 합니다.
3. 대규모 테이블 행 데이터에 대한 순차적 액세스 제거
중첩 쿼리에서 테이블에 대한 순차적 액세스는 쿼리 효율성에 치명적인 영향을 미칠 수 있습니다. 예를 들어 순차 액세스 전략을 사용하여 세 개의 중첩 수준이 있는 쿼리가 각 수준에서 1,000개의 행을 쿼리하는 경우 이 쿼리는 10억 행의 데이터를 쿼리합니다. 이를 방지하는 주요 방법은 조인된 열을 인덱싱하는 것입니다. 예를 들어 학생 테이블(학생 번호, 이름, 나이...)과 과목 선택 테이블(학생 번호, 과목 번호, 성적)이라는 두 개의 테이블이 있습니다. 두 개의 테이블을 연결하려면 연결 필드 "학생 번호"에 인덱스를 생성해야 합니다.
순차 액세스를 방지하기 위해 공용체를 사용할 수도 있습니다. 모든 검사 열에 인덱스가 있지만 일부 형태의 where 절은 최적화 프로그램이 순차 액세스를 사용하도록 강제합니다. 다음 쿼리는 주문 테이블에서 순차적 작업을 강제 실행합니다. SELECT * FROM 주문 WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
customer_num 및 order_num에 대한 인덱스가 있지만 최적화 프로그램은 위 명령문에서 전체 테이블을 스캔하기 위해 여전히 순차 액세스 경로를 사용합니다. 이 문은 별도의 행 컬렉션을 검색하므로 다음 문으로 변경해야 합니다.
SELECT * FROM 주문 WHERE customer_num=104 AND order_num>1001
노동 조합
SELECT * FROM 주문 WHERE order_num=1008
이를 통해 인덱스 경로를 사용하여 쿼리를 처리할 수 있습니다.
4. 상관된 하위 쿼리를 피하세요
기본 쿼리와 where 절의 쿼리 모두에 열 레이블이 나타나는 경우 기본 쿼리의 열 값이 변경되면 하위 쿼리를 다시 쿼리해야 할 가능성이 높습니다. 쿼리의 중첩 수준이 많을수록 효율성이 떨어지므로 하위 쿼리는 최대한 피해야 합니다. 하위 쿼리가 불가피한 경우 하위 쿼리에서 가능한 한 많은 행을 필터링하세요.
5. 어려운 정규식은 피하세요
MATCHES 및 LIKE 키워드는 기술적으로 정규식이라고 하는 와일드카드 일치를 지원합니다. 그러나 이런 종류의 일치에는 특히 시간이 많이 걸립니다. 예: SELECT * FROM customer WHERE 우편번호 LIKE “98_ _ _”
우편번호 필드에 색인이 생성되더라도 이 경우에는 여전히 순차 스캔이 사용됩니다. 명령문을 SELECT * FROM customer WHERE zipcode > "98000"으로 변경하면 쿼리를 실행할 때 인덱스가 쿼리에 사용되므로 속도가 크게 향상됩니다.
또한 시작하지 않는 부분 문자열을 피하세요. 예를 들어, SELECT * FROM customer WHERE zipcode[2, 3]>"80" 문은 where 절에서 시작하지 않는 하위 문자열을 사용하므로 이 문은 인덱스를 사용하지 않습니다.
6. 임시 테이블을 사용하여 쿼리 속도 향상
테이블의 하위 집합을 정렬하고 임시 테이블을 생성하면 쿼리 속도가 빨라지는 경우가 있습니다. 이는 다중 정렬 작업을 방지하고 최적화 프로그램의 작업을 단순화하는 데 도움이 됩니다. 예: SELECT cust.name, rcVBles.balance,...기타 열
SELECT cust.name,rcVBles.balance,...기타 열
고객으로부터,rcvbles
어디에 cust.customer_id = rcvlbes.customer_id
그리고 rcvblls.balance>0
AND 고객.우편번호>"98000"
고객 이름으로 주문
이 쿼리를 한 번이 아닌 여러 번 실행하려면 임시 파일에서 모든 미지급 고객을 찾아 고객 이름별로 정렬할 수 있습니다. SELECT cust.name, rcvbles.balance,...기타 열
SELECT cust.name,rcvbles.balance,...기타 열
고객으로부터,rcvbles
어디에 cust.customer_id = rcvlbes.customer_id
그리고 rcvblls.balance>0
고객 이름으로 주문
INTO TEMP cust_with_balance
그런 다음 다음과 같은 방법으로 임시 테이블을 쿼리합니다. SELECT * FROM cust_with_balance
WHERE 우편번호>"98000"
임시 테이블은 메인 테이블에 비해 행 수가 적고 물리적 순서도 필수 순서이므로 디스크 I/O가 줄어들어 쿼리 작업량을 대폭 줄일 수 있다.
참고: 임시 테이블이 생성된 후에는 기본 테이블의 수정 사항이 반영되지 않습니다. 메인 테이블의 데이터가 자주 수정되는 경우 데이터가 손실되지 않도록 주의하세요.
7. 정렬을 사용하여 비순차적 액세스 대체
비순차적 디스크 액세스는 가장 느린 작업이며 디스크 액세스 암의 앞뒤 움직임으로 나타납니다. SQL 문은 이러한 상황을 숨기므로 애플리케이션을 작성할 때 다수의 비순차적 페이지에 액세스해야 하는 쿼리를 쉽게 작성할 수 있습니다. 경우에 따라 비순차적 액세스 대신 데이터베이스의 정렬 기능을 사용하면 쿼리가 향상될 수 있습니다.
-