데이터베이스 SQL 선택 쿼리 작동 방식

저는 전문 DBA는 아니지만 B/S 아키텍처를 갖춘 개발자로서 항상 데이터베이스와 떼려야 뗄 수 없는 존재입니다. 일반적으로 개발자는 SQL의 네 가지 기본 문인 선택, 삽입, 삭제 및 업데이트만 사용합니다. 하지만 나는 이것이 어떻게 작동하는지 연구한 적이 없습니다. 이 기사에서는 데이터베이스에서 select가 어떻게 작동하는지 이야기하고 싶습니다.

B/S 아키텍처의 가장 고전적인 주제는 크게 데이터 계층, 비즈니스 로직 계층, 프리젠테이션 계층으로 나눌 수 있는 3계층 아키텍처입니다. 레코드 쿼리와 같은 데이터베이스와 상호 작용합니다. 우리는 종종 쿼리 SQL을 작성한 다음 프로그램을 호출하여 SQL을 실행합니다. 하지만 내부 작업 흐름은 무엇입니까? 어떤 단계를 먼저 해야 할지, 다음 단계는 무엇인지 등 대부분의 친구들이 저처럼 확신이 없는 것 같아요.

1단계: 애플리케이션이 쿼리 SQL 문을 서버로 전송하여 실행합니다.

SQL 문을 실행할 때 데이터 계층에서 애플리케이션은 해당 데이터베이스 서버에 연결하고 처리를 위해 SQL 문을 서버로 보냅니다.

2단계: 서버가 요청한 SQL 문을 구문 분석합니다

1. SQL 계획 캐시 쿼리 분석기를 자주 사용하는 친구는 이 사실을 처음으로 실행하는 경우가 많습니다. 실행하는데 매우 오랜 시간이 걸리지만, 동일한 문을 즉시 실행하거나 일정 시간 내에 실행하면 짧은 시간 안에 쿼리 결과가 반환됩니다.

이유:

서버는 쿼리 요청을 받은 후 즉시 데이터베이스로 이동하여 쿼리하지 않고 데이터베이스의 계획 캐시에 해당 실행 계획이 있는지 확인합니다. 존재하는 경우 컴파일된 실행 계획을 직접 호출하여 실행 계획의 컴파일 시간을 절약합니다.

쿼리된 행이 데이터 버퍼 저장 영역에 이미 존재하는 경우 실제 파일을 쿼리할 필요가 없습니다. 대신 이러한 방식으로 메모리에서 데이터를 가져옵니다. 데이터 버퍼 저장 영역에 대해서는 하드 디스크에서 데이터를 읽는 것보다 훨씬 빠르고 쿼리 효율성도 향상됩니다.

2. SQL 계획 캐시에 해당 실행 계획이 없는 경우 서버는 사용자가 요청한 SQL 문에 대해 먼저 구문 확인을 수행하고 구문 오류가 있는 경우 쿼리를 종료합니다. 작업을 수행하고 이를 호출하는 애플리케이션에 해당 오류 메시지를 반환합니다.

참고: 이때 반환되는 오류 메시지에는 select로 작성된 select 등 기본 구문 오류 정보만 포함됩니다. 오류 메시지에 목록에 없는 열이 포함된 경우 서버는 확인하는 것은 구문 검증뿐이며 의미가 올바른지 여부는 다음 단계로 남겨집니다.

3. 구문이 일치하면 의미가 올바른지 확인합니다. 예를 들어 테이블 이름, 열 이름, 저장 프로시저 등의 데이터베이스 개체가 실제로 존재하는지 확인합니다. 존재하지 않는 것이 있으면 애플리케이션에 오류가 보고되고 쿼리가 종료됩니다.

4. 다음 단계는 객체의 구문 분석 잠금을 획득하는 것입니다. 이는 테이블이 잠겨 있지 않은 경우 먼저 객체를 잠급니다. , 데이터가 삽입되지만 잠금이 없기 때문에 쿼리는 이미 이 레코드를 읽었으며 일부 삽입은 트랜잭션 실패로 인해 롤백되어 더티 읽기 현상이 발생합니다.

5. 다음 단계는 데이터베이스 사용자 권한을 확인하는 것입니다. 이때 데이터베이스 사용자에게 해당 액세스 권한이 없으면 쿼리 결과를 얻지 못할 수 있습니다. 서버가 권한을 보고합니다. 대규모 프로젝트에서는 하나의 프로젝트에 여러 데이터베이스 연결 문자열이 포함되는 경우가 많으며, 일부는 읽기 전용 권한을 갖고 일부는 쓰기 전용 권한을 갖습니다. 읽고 쓸 수 있습니다. 다른 작업에 따라 실행할 다른 사용자를 선택하십시오. 주의를 기울이지 않으면 SQL 문이 아무리 완벽해도 쓸모가 없습니다.

6. 분석의 마지막 단계는 최종 실행 계획을 결정하는 것입니다. 구문, 의미 및 권한이 모두 확인되면 서버는 결과를 즉시 반환하지 않고 대신 SQL을 최적화하고 다른 쿼리 알고리즘을 선택하여 가장 효율적인 형식으로 애플리케이션에 반환합니다. 예를 들어, 테이블 조인트 쿼리를 수행할 때 서버는 비용, 어떤 인덱스가 더 효율적인지 등에 따라 최종적으로 hashjoin, mergejoin 또는 loopjoin을 사용하기로 결정합니다. 그러나 효율적인 쿼리를 작성하기 위해 자동화된 최적화는 제한됩니다. SQL은 여전히 ​​자체 SQL 쿼리 문을 최적화해야 합니다.

실행 계획이 결정되면 실행 계획이 SQL 계획 캐시에 저장됩니다. 다음에 동일한 실행 요청이 있을 때 실행 계획을 다시 컴파일하지 않도록 계획 캐시에서 직접 가져옵니다.

3단계: 명령문 실행

서버는 SQL 문의 구문 분석을 완료한 후 해당 문의 내용이 무엇을 의미하는지 알게 되고 실제로 SQL 문을 실행하게 됩니다.

이때는 두 가지 상황이 있습니다.

쿼리문에 포함된 데이터 행을 데이터 버퍼 저장 영역으로 읽어 들인 경우 서버는 데이터 버퍼에서 데이터를 직접 읽습니다. 저장 영역을 저장하고 애플리케이션으로 반환하여 실제 파일에서 읽는 것을 방지하여 쿼리 속도를 향상시킵니다.

데이터 행이 데이터 버퍼에 없는 경우 실제 파일에서 레코드를 읽어 애플리케이션에 반환하고, 다음 사용을 위해 데이터 행을 데이터 버퍼에 기록합니다.

참고: SQL 캐시에는 여러 유형이 있습니다. 여기에 관심이 있는 친구는 검색할 수 있습니다. 때로는 캐시의 존재로 인해 두 번째로 최적화 결과를 즉시 확인하기가 어렵습니다. 실행에는 캐시가 있으므로 속도가 매우 빠르므로 일반적으로 먼저 캐시를 제거한 다음 최적화 전후의 성능을 비교해야 합니다.

DBCCDROPCLEANBUFFERS

버퍼 풀에서 모두 삭제 버퍼를 지웁니다.

DBCCFREEPROCCACHE

프로시저 캐시에서 모든 요소를 ​​제거합니다.

DBCCFREESYSTEMCACHE

모든 캐시에서 사용되지 않은 캐시 항목을 모두 해제합니다. SQLServer2005 데이터베이스 엔진은 백그라운드에서 사용되지 않는 캐시 항목을 미리 정리하여 현재 항목에 사용할 수 있는 메모리를 만듭니다. 그러나 이 명령을 사용하면 모든 캐시에서 사용되지 않는 항목을 수동으로 제거할 수 있습니다.

이것은 기본적으로 SQL 캐시의 영향을 제거할 수 있을 뿐입니다. 캐시를 완전히 제거할 수 있는 해결책은 없는 것 같습니다.

결론: 서비스 실행 애플리케이션에서 제출한 SQL의 연산 과정을 알아야만 애플리케이션을 잘 디버깅할 수 있다.

SQL 구문이 올바른지 확인하세요.

SQL 의미가 올바른지, 즉 개체가 존재하는지 확인하세요.

데이터베이스 사용자에게 해당 액세스 권한이 있는지 확인하세요. 진상.