1. Stored procedures 란??
일련의 쿼리를 마치 하나의 함수 처럼 실행하기 위한 쿼리의 집합 = 일종의 메소드()
SQL의 저장 프로시저는 데이터베이스에 저장되어 있으며 프로시저 이름을 호출하여 실행할 수 있는 미리 컴파일된 SQL 문 블록입니다.
일반적으로 비즈니스 로직과 반복적인 작업을 캡슐화하는 데 사용되며, 클라이언트-서버 왕복 횟수를 줄여 성능을 향상시킬 수도 있습니다.
1-1 미리 컴파일 되어있는 SQL문 블록이란 무슨 뜻인가요 ??
SQL로 저장 프로시저를 만들면 프로시저 내의 SQL 코드가 데이터베이스 서버에 의해 컴파일되고 최적화됩니다. '
컴파일 및 최적화 프로세스에는 SQL 코드 분석, 실행 계획 생성, 데이터베이스 서버에서 보다 효율적으로 실행할 수 있는 머신 코드 생성 등이 포함됩니다.
즉, 저장 프로시저를 실행할 때 데이터베이스 서버는 컴파일 및 최적화 프로세스를 다시 수행할 필요가 없습니다. 대신 미리 컴파일되고 최적화된 코드를 재사용할 수 있으므로 쿼리 성능이 향상될 수 있습니다.
요약하자면, 저장 프로시저는 프로시저가 실행될 때마다 컴파일 및 최적화되는 것이 아니라 프로시저가 생성될 때 한 번 컴파일 및 최적화되기 때문에 미리 컴파일된 SQL 블록입니다. 따라서 처리 시간을 절약하고 성능을 향상시킬 수 있습니다.
1-2 컴파일 및 최적화 프로세스는 무슨 뜻인가요 ? What does the compilation and optimization process mean?
SQL의 컴파일 및 최적화 프로세스에는 실행을 위해 SQL 코드를 준비하기 위해 데이터베이스 서버에서 수행하는 여러 단계가 포함됩니다.
다음은 컴파일 및 최적화 프로세스와 관련된 일반적인 단계입니다:
구문 분석: 첫 번째 단계는 SQL 코드를 구문 분석하여 키워드, 테이블 이름, 열 이름, 연산자 등 문장의 다양한 구성 요소를 식별하는 것입니다.
의미 분석: SQL 코드가 파싱되면 데이터베이스 서버는 구문을 검사하고 SQL 문이 유효한지 확인합니다. 또한 데이터베이스 스키마를 확인하여 참조된 테이블과 열이 실제로 존재하는지, 사용자에게 해당 테이블과 열에 액세스하는 데 필요한 권한이 있는지 확인합니다.
쿼리 최적화: SQL 문의 유효성을 검사한 후 데이터베이스 서버는 데이터를 검색하는 방법을 간략하게 보여주는 실행 계획을 생성합니다. 이 계획은 테이블 크기, 인덱스 가용성, 쿼리 복잡성 등의 요소를 고려하여 쿼리 실행 시간을 최적화합니다.
코드 생성: 마지막으로 데이터베이스 서버는 데이터베이스 서버의 쿼리 엔진에서 실행할 수 있는 머신 코드를 생성합니다.
Parsing: The first step is to parse the SQL code and identify the various components of the statement, such as keywords, table names, column names, and operators.
Semantic Analysis: Once the SQL code has been parsed, the database server checks the syntax and verifies that the SQL statement is valid. It also checks the database schema to ensure that the referenced tables and columns actually exist and that the user has the necessary privileges to access them.
Query Optimization: After the SQL statement has been validated, the database server generates an execution plan that outlines how it will retrieve the data. This plan takes into account factors such as table size, index availability, and query complexity to optimize the query execution time.
4. Code Generation: Finally, the database server generates machine code that can be executed by the database server's query engine.
컴파일 및 최적화 프로세스는 데이터베이스 서버가 쿼리를 실행하기 위해 수행해야 하는 작업량을 최소화하여 SQL 쿼리의 성능을 개선하도록 설계되었습니다. SQL 코드를 머신 코드로 컴파일하고 실행 계획을 최적화함으로써 데이터베이스 서버는 쿼리를 더 빠르고 효율적으로 실행할 수 있습니다.
코드 생성 ???
코드 생성은 SQL 코드를 데이터베이스 서버의 쿼리 엔진에서 실행할 수 있는 기계어 코드로 변환하는 프로세스입니다. 머신 코드는 컴퓨터의 프로세서에서 직접 실행되는 저수준 프로그래밍 언어입니다.
SQL의 경우 데이터베이스 서버에서 생성되는 머신 코드는 일반적으로 쿼리 엔진이 SQL 문에서 요청한 데이터를 검색하기 위해 따를 수 있는 일련의 단계인 실행 계획의 형태입니다.
실행 계획은 관련된 테이블의 크기, 사용 가능한 인덱스 및 성능에 영향을 줄 수 있는 기타 요소를 고려하여 가능한 한 효율적으로 설계됩니다. 예를 들어, 실행 계획은 전체 테이블을 스캔하는 대신 인덱스를 사용하여 테이블에서 데이터를 검색할 수 있으며, 이는 훨씬 느릴 수 있습니다.
실행 계획이 생성되면 데이터베이스 서버의 쿼리 엔진이 이를 실행하여 요청된 데이터를 검색할 수 있습니다. 여기에는 일반적으로 데이터베이스의 스토리지 시스템에서 데이터를 읽고 메모리에서 처리한 후 결과를 요청한 사용자나 애플리케이션에 반환하는 작업이 포함됩니다.
요약하면, SQL 코드 생성에는 데이터베이스 서버의 쿼리 엔진이 요청된 데이터를 검색하기 위해 실행할 수 있는 실행 계획의 형태로 SQL 코드를 기계어 코드로 변환하는 작업이 포함됩니다. 이는 데이터 검색과 관련된 단계를 최적화하여 성능을 개선하는 데 도움이 됩니다.
예시를 보여주세요 ~ !
SELECT COUNT(*) AS order_count
FROM orders
WHERE customer_id = @customer_id이러한 쿼리를 매번 치는것은 조금 귀찮습니다.
또한 양이 많아진다면?? 너무 복잡하고 그렇습니다.
다음은 특정 고객의 주문 수를 검색하는 SQL 저장 프로시저의 예입니다:
CREATE PROCEDURE get_customer_order_count
@customer_id INT
AS
BEGIN
SELECT COUNT(*) AS order_count
FROM orders
WHERE customer_id = @customer_id
END
이 예제에서 CREATE PROCEDURE 문은 get_customer_order_count라는 새 저장 프로시저를 생성합니다.
호출자가 검색하려는 고객의 주문 수를 지정할 수 있도록 @customer_id 매개 변수가 정의되어 있습니다. 프로시저 내부의 SELECT 문은 주문 테이블에서 지정한 고객의 주문 수를 검색하고, AS 키워드는 결과에 별칭 order_count를 할당합니다.
저장 프로시저를 실행하려면 @customer_id 매개 변수의 값을 전달하여 EXEC 명령을 사용할 수 있습니다:
EXEC get_customer_order_count @customer_id = 12345;그러면 저장 프로시저가 실행되고 ID 12345의 고객이 주문한 주문 수가 반환됩니다.
또한 프로시저 호출시 CALL을 사용할수도 있습니다.
CALL [프로시저명];
2. 장점과 단점은 어떻게 되나요 ? (What are the advantages and disadvantages of Stored Procedures? )
장점
성능 향상: 저장 프로시저는 미리 컴파일되고 최적화되므로 데이터베이스 서버가 SQL 문을 구문 분석하고 최적화하는 데 소요되는 시간을 줄여 애플리케이션의 성능을 향상시킬 수 있습니다.예를들어 자주 실행되는 복잡한 쿼리가 있는 경우 쿼리를 미리 컴파일하여 저장 프로시저로 저장하면 성능이 크게 향상될 수 있습니다. 예를 들어 여러 테이블에서 데이터를 검색하는 저장 프로시저가 있는 경우 데이터베이스 서버가 쿼리를 최적화하고 실행 계획을 저장할 수 있으므로 저장 프로시저에 대한 후속 호출이 훨씬 더 빠르게 실행됩니다.
네트워크 트래픽 감소: 저장 프로시저는 데이터베이스 서버에서 실행되므로 애플리케이션과 데이터베이스 서버 간의 네트워크 트래픽 양을 줄일 수 있습니다.
중앙 집중식 로직: SQL 코드를 저장 프로시저로 캡슐화하면 애플리케이션의 비즈니스 로직을 데이터베이스에서 중앙 집중화할 수 있습니다. 이렇게 하면 애플리케이션 코드가 단순화되고 유지 관리가 쉬워집니다.데이터가 애플리케이션으로 반환되기 전에 데이터에 적용해야 하는 비즈니스 로직이 있는 경우, 해당 로직을 저장 프로시저에 캡슐화할 수 있습니다. 이렇게 하면 로직을 여러 곳에 복제할 필요가 없으므로 애플리케이션 코드를 더 간단하고 유지 관리하기 쉽게 만들 수 있습니다.
보안 향상: 저장 프로시저는 사용자와 애플리케이션이 데이터베이스 테이블에 직접 액세스할 수 있는 양을 제한하여 애플리케이션의 보안을 개선하는 데 도움이 될 수 있습니다.사용자에게 기본 테이블이 아닌 저장 프로시저에만 액세스 권한을 부여하면 사용자가 데이터에 대해 수행할 수 있는 작업을 제한할 수 있습니다. 이를 통해 민감한 데이터에 대한 무단 액세스를 방지할 수 있습니다.
코드 재사용: SQL 코드를 저장 프로시저로 캡슐화하면 애플리케이션의 여러 부분에서 코드를 재사용할 수 있습니다. 이렇게 하면 코드 중복을 줄이고 애플리케이션을 보다 모듈화할 수 있습니다.
애플리케이션의 여러 부분에서 동일한 SQL 쿼리를 실행해야 하는 경우, 해당 쿼리를 저장 프로시저에 캡슐화하여 여러 위치에서 호출할 수 있습니다. 이렇게 하면 코드 중복을 줄이고 애플리케이션을 더욱 모듈화할 수 있습니다.
단점:
복잡성 증가: 저장 프로시저는 애플리케이션과 데이터베이스 사이에 추가적인 추상화 계층을 도입하여 애플리케이션의 복잡성을 증가시킬 수 있습니다. 이로 인해 애플리케이션 코드를 디버깅하고 유지 관리하기가 더 어려워질 수 있습니다.
저장 프로시저는 애플리케이션과 데이터베이스 사이에 추가적인 추상화 계층을 도입하므로 애플리케이션 코드를 디버깅하고 유지 관리하기가 더 어려워질 수 있습니다. 예를 들어, 저장 프로시저의 로직을 변경해야 하는 경우 애플리케이션의 여러 부분에서 해당 변경 사항을 테스트하고 배포해야 할 수 있습니다
벤더 종속: 저장 프로시저는 일반적으로 데이터베이스에 따라 다르므로 공급업체 종속이 발생하여 애플리케이션을 다른 데이터베이스 플랫폼으로 마이그레이션하기가 더 어려워질 수 있습니다.저장 프로시저는 데이터베이스에 따라 달라지는 경우가 많기 때문에 애플리케이션을 다른 데이터베이스 플랫폼으로 마이그레이션하기가 더 어려울 수 있습니다. 예를 들어, SQL Server에서 복잡한 저장 프로시저를 개발했다면 MySQL로 전환할 경우 해당 코드를 다시 작성해야 할 수 있습니다.
테스트 및 디버깅: 저장 프로시저는 데이터베이스 서버에서 실행되고 추가 도구와 설정이 필요할 수 있으므로 임시 SQL 쿼리보다 테스트 및 디버깅이 더 어려울 수 있습니다.저장 프로시저를 테스트하고 디버깅하는 것은 애드혹 SQL 쿼리를 테스트하고 디버깅하는 것보다 더 어려울 수 있습니다. 저장 프로시저를 효과적으로 테스트하고 디버깅하기 위해 추가 도구와 인프라를 설정해야 할 수도 있습니다.
제한된 이동 가능성: 저장 프로시저는 다른 데이터베이스 플랫폼이나 버전 간에 이식되지 않을 수 있으며, 이로 인해 애플리케이션의 유연성이 제한될 수 있습니다.저장 프로시저는 다른 데이터베이스 플랫폼이나 버전 간에 이식되지 않을 수 있으며, 이로 인해 애플리케이션의 유연성이 제한될 수 있습니다. 예를 들어, 특정 기능에 의존하는 저장 프로시저를 Oracle에서 개발했다면 다른 데이터베이스 플랫폼에서 해당 코드를 사용하지 못할 수 있습니다.
성능 오버헤드: 저장 프로시저는 성능을 향상시킬 수 있지만, 특히 사전 컴파일 및 최적화의 이점이 없는 작거나 빈번하지 않은 쿼리의 경우 추가적인 오버헤드를 유발할 수 있습니다.저장 프로시저는 복잡한 쿼리의 성능을 향상시킬 수 있지만, 사전 컴파일 및 최적화의 이점을 누리지 못하는 작거나 빈번하지 않은 쿼리의 경우 추가적인 오버헤드가 발생할 수 있습니다. 이러한 오버헤드는 특히 동시성이 높은 환경에서 두드러지게 나타날 수 있습니다.
요약하면, 저장 프로시저는 성능, 보안 및 코드 재사용에 상당한 이점을 제공할 수 있지만 복잡성, 공급업체 종속성 및 테스트 문제를 야기할 수도 있습니다. 저장 프로시저를 사용할지 여부는 애플리케이션의 특정 요구 사항과 기꺼이 감수할 수 있는 장단점에 따라 달라집니다.
댓글