use-the-index-luke 저자 Markus Winand
Markus Winand는 SQL에 대한 통찰력을 제공하고 다양한 시스템이 SQL을 지원하는 방법을 modern-sql.com 에서 보여줍니다. 이전에 그는 use-the-index-luke.com 을 만들었는데, 지금도 활발하게 유지되고 있습니다. Markus는 winand.at 를 통해 강사, 연사 및 컨설턴트로 고용될 수 있습니다.
You can upload a Korean translation of use-the-index-luke.com on your blog
Thank you from the bottom of my heart to author Makus Winand for allowing me.
These are translations that I use for studying by using a papago (google translate)
The translations may not be correct or there may be a typo.
I'd appreciate it if you could point it out in the comments.
use-the-index-luke.com 의 한글번역본을 블로그에 업로드 해도 된다고
허락해주신 Makus Winand 저자님께 진심으로 감사합니다.
이 번역본들은 제가 공부용도로 번역기(papago, google transrate)를 돌려서
번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.
댓글로 지적해주시면 감사하겠습니다.
https://use-the-index-luke.com/sql/myth-directory/dynamic-sql-is-slow
Dynamic SQL은 느리다.
"동적 SQL은 느리다" 미신의 진정한 본질은 다소 단순합니다. 동적 SQL은 잘못 수행될 경우 속도가 느려질 수 있습니다.
문제는 동적 SQL이 종종 잘못된 이유로, 때로는 알지도 못하면서 사용된다는 것입니다.
혼동을 명확히 하기 위해 다음과 같은 용어를 사용하겠습니다:
Embedded SQL
SQL을 프로그램 소스 코드에 직접 포함하는 것은 Oracle PL/SQL 또는 Microsoft Transacr-SQL과 같은 저차 데이터베이스 언어에서 매우 일반적입니다.
C와 같은 다른 언어에 SQL을 포함하는 것도 가능합니다.
임베디드 SQL의 이점은 각 프로그래밍 언어와의 원활한 통합입니다. 그러나 임베디드 SQL은 프로그램에 컴파일됩니다. 런타임은 변경할 수 없습니다. 정적입니다.
동적 SQL
동적 SQL은 응용프로그램 내에서 문자열로 처리됩니다.
응용 프로그램은 SQL 문자열을 데이터베이스 계층에 전달하기 전에 런타임에 변경할 수 있습니다. 실제로 데이터베이스에 액세스하는 가장 일반적인 방법입니다.
정적 SQL
런타임에 변경되지 않는 SQL문을 설명하기 위해 정적 SQL이라는 용어를 사용합니다. 런타임에 변경할 수 없는 임베디드 SQL이든, 변경할 수 있지만 변경되지 않은 동적 SQL이든 상관 없습니다.
이러한 정의의 핵심은 문이 동적 및 정적 SQL일 수 있다는 것입니다. 즉, 다양한 수준의 동적 SQL이 있습니다.
다음 예를 생각해 보십시오:
String sql = "SELECT first_name, last_name" + " FROM employees" + " WHERE employee_id = " + employeeId;
ResultSet rs = con.executeQuery(sql);
동적 SQL 입니까? 위의 정의에 따르면 그렇습니다.
SQL 문은 문자열로 준비되어 데이터베이스 계층으로 전달됩니다. 하지만 정적 SQL도 마찬가지 입니까?
employeeld 변수 값이 변경된다고 가정하면 런타임에 SQL 문자열이 변경되므로 정적 SQL이 아닙니다. 이는 실제로 성능을 저하시키는 동적 SQL의 예입니다.
문제는 동적 SQL이 아니라 바인딩 변수를 사용하지 않는다는 것입니다. SQL 바인딩 변수(예 물음표 ?, 또는 :name )는 실행 중에 변경되는 값에 대한 자리 표시자입니다. 즉, employeeld 변수의 실제 값 대신 bind 변수를 사용하여 예제를 정적 SQL로 변환할 수 있습니다.
Important
바인딩 매개 변수를 사용하지 않으면 동적 SQL이 잘못 사용됩니다.
바인딩 매개변수는 보안 및 성능에 매우 중요합니다.
동적 SQL을 합리적으로 사용하는 방법은 런타임에 문장 구조를 변경하는 것입니다.
이는 바인딩 매개 변수로는 수행할 수 없는 작업입니다.
예: 조건부 where 문 :
String where = ""; if (subsidiaryId != null) { where += (where == "") ? " WHERE " : " AND " + "subsidiary_id = " + subsidiaryId; } if (employeeId != null) { where += (where == "") ? " WHERE " : " AND " + "employee_id = " + employeeId; } if (lastName != null) { where += (where == "") ? " WHERE " : " AND " + "UPPER(last_name) = '"+lastName.toUpperCase()+"'"; } String SQL = "SELECT employee_id, first_name, last_name " + " FROM employees" + where; // execute SQL
이 코드 세 가지 필터 기준의 조합을 기반으로 직원을 가져오기 위한 SQL문을 작성합니다.
다소 어색하지만 생성된 SQL은 사용 가능한 최상의 인덱스를 사용하여 실행할 수 있습니다 . 그럼에도 불구하고 이 접근 방식은 SQL 인젝션 취약성과 높은 최적화 오버헤드로 인해 문제가 됩니다.
데이터베이스는 실행 계획을 매번 다시 만들어야 합니다.
매번 다를 수 있는 검색 용어는 캐싱을 방해하기 때문입니다.
“Parameterized Queries”는 최적화 오버헤드를 자세히 설명합니다. 다시 말하지만 동적 SQL은 문제가 아니지만 바인딩 매개 변수를 사용하지 않는 것이 문제입니다.
where 절을 종적으로 빌드하고 바인딩 매개 변수를 사용하는 예제는 위의 예제처럼 횔씬 더 어색하기 때문에 생략됩니다. 그러나 대부분의 ORM 프레임워크는 바인딩 매개 변수를 사용하여 SQL을 동적으로 생성하는 매우 평리한 방법을 제공합니다.
다음 개요에서는 일부 샘플을 보여 줍니다.
Java
다음 샘플은 Hibernate's Criteria 기능을 보여줍니다:
Criteria criteria = session.createCriteria(Employees.class);
if (subsidiaryId != null) {
criteria.add(Restrictions.eq("subsidiaryId", subsidiaryId)); } if (employeeId != null) { criteria.add(Restrictions.eq("employeeId", employeeId)); } if (lastName != null) { criteria.add( Restrictions.eq("lastName", lastName).ignoreCase() ); }
LAST_NAME만 제공하는 경우 최대 절전(Oracle)에서 다음 SQL이 생성됩니다.
select this_.subsidiary_id as subsidiary1_0_0_,
[... other columns ...] from employees this_where lower(this_.last_name)=?
ignoreCase()기능을 구현하기 위해 bind 매개변수와
LOWER 함수가 사용됩니다.
ilike 제한도 마찬가지입니다. 이는 기능 기반 인덱싱에 매우 중요한 사실입니다.
Java Persistence API(JPA)의 기능은 다음과 같습니다:
그러나 이는 덜 직관적이며 대/소문자를 구분하지 않는 기본 검색을 지원하지 않습니다:
List<Predicate> predicates = new ArrayList<Predicate>();
if (lastName != null) {
predicates.add(queryBuilder.equal( queryBuilder.upper(r.get(Employees_.lastName)) , lastName.toUpperCase()) ); } if (employeeId != null) { predicates.add(queryBuilder.equal( r.get(Employees_.employeeId) , employeeId) ); } if (subsidiaryId != null) { predicates.add(queryBuilder.equal( r.get(Employees_.subsidiaryId) , subsidiaryId) ); }
query.where(predicates.toArray(new Predicate[0]));
이 예제는 컴파일 시간 유형 안전을 선호하는 경향이 덜 뚜렷하다는 것을 알 수 있습니다. 또 다른 차이점은 JPA가 대소문자를 구분하지 않는 기본 연산자를 지원하지 않는다는 접입니다. 즉, 명시적인 대소문자 변환이 필요합니다.
그것을 인식하고 통제하는 것은 아마도 좋을 것입니다.
참고로 네이티브 Hibernate API는 명시적인 사례 변환도 지원합니다.
Tip
complete sample code를 다운로드하여 사용해 보십시오.
바인딩 매개 변수와 함께 동적 SQL을 사용하면 최적화 도구가 where 절의 특정 조합에 가장 적합한 실행 계획을 선택할 수 있습니다. 그것은 설명된 i와 같은 구조물보다 더 나은 성능을 산출할 것입니다.
SELECT first_name, last_name FROM employeesWHERE ( employee_id = ? OR ? IS NULL) AND ( subsidiary_id = ? OR ? IS NULL) AND (UPPER(last_name) = ? OR ? IS NULL)
동적 SQL이 느리다는 관측은 바인딩 매개변수를 사용하지 않는 경우가 많습니다.
즉, 잘못된 이유로 동적 SQL을 사용하는 경우가 많습니다.
하지만, 몇 가지는 동적 SQL이 위와 같이 "smart logic"보다 느릴 수 있는 경우는 드물다고 생각합니다.
이 경우 매우 저렴한 (빠른) SQL문이 매우 높은 빈도로 실행됩니다. 하지만 우선, 두 가지 용어를 더 설명 해야 합니다:
Har Parsing
하드 파싱은 SQL 문을 기반으로 실행 계획을 구성하고 있습니다. SQL의 모든 부분을 검사하고, 모든 인덱스를 고려하고, 모든 조인 순서를 고려하는 등의 작업이 매우 중요합니다. 하드 파싱은 리소스 집약적입니다.
Soft Parsing
소프트 파싱은 캐시된 실행 계획을 검색, 탐색 및 사용하는 것입니다. 액세스 권한과 같은 일부 사소한 검사가 수행되지만 실행 계획은 그대로 재사용할 수 있습니다.
그것은 꽤 빠른 작업입니다.
캐시의 키는 기본적으로 리터럴 SQL 문자열(일반적으로 해시)입니다. 정확한 일치 항목이 없으면 하드 구문 분석이 트리거됩니다. 그렇기 때문에 인라인 리터럴은 바인딩 매개 변수와 달리 동일한 검색 용어를 다시 사용하지 않는 한 하드 구문 분석을 트리거합니다. 그러나 이 경우에도 새로운 실행 계획이 반복적으로 발생하기 때문에 이전 실행 계획이 캐시에서 이미 만료되었을 가능성이 높습니다.
그러나 구문 분석 없이 문을 실행할 수 있는 방법이 있습니다. 소프트 구문 분석도 마찬가지입니다.
이 방법은 구문 분석된 문을 열러 두는 것입니다.
예를 들어, 다음 Java 유사 코드에서와 같습니다:
PreparedStatement pSQL = con.prepareStatement("select ..."); for (String last_name:last_names) { pSQL.setString(1, last_name.toUpperCase()); ResultSet rs = pSQL.executeQuery(); // process result } pSQL.close();
준비된 문은 한 번만 열고 닫을 수 있지만 여러 번 실행할 수 있습니다. 즉, 준비 중에 하나의 구문 분석 작업만 수행할 수 있지만 루프 내부에는 구문 분석 작업이 없습니다.
문제는 문을 동적 SQL로 변환하면
prepareStatement 호출이 루프로 이동하여 각 실행에 대해 소프트 구문 분석이 발생한다는 것입니다. 네트워크 지연 시간도 포함할 수 있는 구문 분석 오버헤디는 문이 자주 실행되고 아무리 빨리 실행되더라도 더 나은 실행 계획의 절약량을 초과할 수 있습니다. 실제 실행 계획이 다양한 where 절에 대해 변경되지 않는 경우(예:잘 색인된 where 절이 항상 존재하기 때문) 특히 그렇습니다.
"루프 전 준비" 트릭은 명시적으로 사용되지 않지만 저장 프로시저에서는 매우 일반적이지만 암묵적입니다.
PL/SQL과 같은 언어(실제 static SQL 포함)는 프로시저가 컴파일될 때 또는 실행당 최대 한 번씩 SQL을 준비합니다. 동적 SQL로 변경하면 성능이 쉽게 저하될수 있습니다.
Tip
- [ ] Correctly using Bind-Parameters
- [ ] Article: “Planning for Re-Use” about execution plan caching
- [ ] Avoid Smart-Logic for conditional where clauses
'use-the-index-luke' 카테고리의 다른 글
| D.Glossary (0) | 2024.02.22 |
|---|---|
| 5.Select * is Bad (0) | 2024.02.19 |
| 3.Oracle Cannot Index NULL (0) | 2024.02.12 |
| 2.Most Selective First (0) | 2024.02.08 |
| 1.Indexes Can Degenerate (0) | 2024.02.05 |
