고품질, 고성능 SQL 쿼리문을 작성하는 방법

1. 먼저 실행계획이 무엇인지 이해해야 겠죠?

실행 계획은 SQL 문과 관련 테이블의 통계 정보를 기반으로 데이터베이스가 작성한 쿼리 계획입니다. 예를 들어 SQL 문을 사용하여 테이블을 쿼리하는 경우 이 계획은 쿼리 최적화 프로그램에서 자동으로 분석됩니다. 100,000개의 레코드, 1개의 레코드, 쿼리 최적화 프로그램은 "인덱스 검색" 방법을 선택합니다. 테이블이 아카이브되고 5,000개의 레코드만 남아 있으면 쿼리 최적화 프로그램은 계획을 변경하고 "전체 테이블 스캔" 방법을 사용합니다.

실행 계획이 정해져 있지 않고 "개인화"되어 있음을 알 수 있습니다. 올바른 "실행 계획"을 생성하는 데는 두 가지 중요한 점이 있습니다.

(1) SQL 문이 쿼리 최적화 프로그램에 수행하려는 작업을 명확하게 알려주나요?

(2) 쿼리 최적화 프로그램에서 얻은 데이터베이스 통계가 최신이고 정확합니까?

2. SQL 문 작성 방식을 통일

다음 두 SQL 문에 대해 프로그래머는 동일하다고 생각하지만 데이터베이스 쿼리 최적화 프로그램은 서로 다르다고 생각합니다.

select*from Dual

select*From Dual

사실 경우가 다릅니다. 쿼리 분석기는 이를 두 개의 다른 SQL 문으로 간주하여 두 번 구문 분석해야 합니다. 2개의 실행 계획을 생성합니다. 따라서 프로그래머로서 동일한 쿼리 문이 모든 곳에서 일관성을 유지하는지 확인해야 합니다. 공백이 하나 더 있어도 작동하지 않습니다!

3. SQL 문 작성 시 주의할 점

다음은 SQL 문 작성 시 주의할 점에 대해 자세히 소개합니다. 이러한 where 절에서는 일부 열에 대한 인덱스가 있어도 작성된 SQL 품질이 좋지 않아 시스템이 SQL 문을 실행할 때 인덱스를 사용할 수 없으며 전체 테이블 스캔도 사용하므로 응답 속도가 매우 느립니다. 절감.

1.IS NULL 및 IS NOT NULL

null을 인덱스로 사용할 수 없으며 null 값을 포함하는 열은 인덱스에 포함되지 않습니다. 인덱스에 여러 개의 열이 있더라도 해당 열 중 하나에 null이 포함되어 있으면 해당 열은 인덱스에서 제외됩니다. 즉, 열에 Null 값이 있으면 해당 열에 인덱스를 구축해도 성능이 향상되지 않습니다.

where 절에서 is null 또는 is not null을 사용하는 모든 문 최적화 프로그램은 인덱스를 사용할 수 없습니다.

2. 호환되지 않는 데이터 유형을 사용하지 마세요.

호환되지 않는 데이터 유형은 전체 테이블 검색 데이터의 유형 변환을 나타내며 액세스는 전체 테이블 스캔이 됩니다.

select * from Employee where last_name = 100; last_name은 varchar 유형입니다

3.

조인 열이 있는 경우 최종 조인 값이 정적 값이더라도 최적화 프로그램은 인덱스를 사용하지 않습니다. 예를 함께 살펴보겠습니다. 직원 테이블(employee)이 있다고 가정합니다. 직원의 이름과 성이 두 개의 열(FIRST_NAME 및 LAST_NAME)에 저장되어 있습니다. 이제 Bill Cliton이라는 직원을 쿼리하려고 합니다.

다음은 조인 쿼리를 이용한 SQL 문입니다.

select * from Employmentss where first_name||''||last_name ='Beill Cliton';

위 문은 Bill Cliton 직원이 있는지 여부를 완전히 쿼리할 수 있습니다. 그러나 시스템 최적화 프로그램은 last_name을 기반으로 생성된 인덱스를 사용하지 않는다는 점에 유의해야 합니다.

다음 SQL 문을 작성하면 Oracle 시스템은 last_name을 기준으로 생성된 인덱스를 사용할 수 있습니다.

*** 여기서 first_name ='Beill' 및 last_name ='Cliton';

4. 와일드카드 문자(%)로 시작하는 Like 문

현재 요구 사항은 다음과 같습니다. 다음을 포함하는 이름을 직원 테이블에 쿼리해야 합니다. 클리톤 사람들. 다음 쿼리 SQL 문을 사용할 수 있습니다:

select * from Employee where last_name like '%cliton%'. 와일드카드 문자(%)가 검색어 시작 부분에 나타나므로 Oracle 시스템은 last_name의 인덱스를 사용하지 않습니다. . 그러나 문자열의 다른 곳에 와일드카드가 나타나면 최적화 프로그램은 인덱스를 활용할 수 있습니다. 인덱스는 다음 쿼리에서 사용됩니다.

select * from Employee where last_name like 'c%'

5. 인덱스 필드에서 작업을 수행하면 인덱스가 무효화됩니다.

WHERE 절의 필드에 함수 또는 표현식 작업을 수행하지 마십시오. 그러면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행하게 됩니다.

예: SELECT * FROM T1 WHERE F1/2=100은 다음과 같이 변경되어야 합니다. SELECT * FROM T1 WHERE F1=100*2

6. Order by 문

ORDER BY 문은 Oracle이 반환된 쿼리 결과를 정렬하는 방법을 결정합니다. Order by 문에서는 정렬할 열에 특별한 제한이 없으며, 열에 조인, 추가 등의 함수를 추가할 수도 있습니다. Order by 문에 색인이 아닌 항목이나 계산된 표현식이 있으면 쿼리 속도가 느려집니다.

인덱싱되지 않은 항목이나 표현식이 있는지 주의 깊게 명령문별 순서를 확인하세요. 이로 인해 성능이 저하될 수 있습니다. 이 문제에 대한 해결책은 인덱스를 사용하도록 order by 문을 다시 작성하는 것입니다. 동시에 사용된 열에 대해 다른 인덱스를 생성할 수도 있으며 order by 절에 표현식을 사용하는 것을 절대 피해야 합니다.

7. NOT

쿼리할 때 where 절에 보다 큼, 보다 작음, 같음, 같지 않음 등과 같은 일부 논리 표현식을 사용하는 경우가 많습니다. 또한 and(and) 또는 또는(or)도 사용할 수 있습니다. 그리고 (아님). NOT은 논리 연산 기호를 부정하는 데 사용할 수 있습니다. 다음은 NOT 절의 예입니다.

... where not (status ='VALID')

NOT를 사용하려면 부정문 앞에 괄호를 넣고, 문구 앞에 NOT 연산자를 넣어야 합니다. NOT 연산자는 같지 않음(<>) 연산자인 다른 논리 연산자 내에 포함되어 있습니다. 즉, 쿼리의 where 절에 NOT 단어가 명시적으로 추가되지 않더라도 여전히 연산자에 NOT이 있는 경우 다음 예를 참조하세요.

... where status <>'INVALID'

이 쿼리의 경우 NOT을 사용하지 않도록 다시 작성할 수 있습니다.

select * from Employee where Salary<3000 or Salal>3000;

이 두 쿼리의 결과는 동일하지만 두 번째 쿼리 계획은 두 번째 쿼리 계획보다 빠릅니다. 첫 번째 쿼리 계획. 두 번째 쿼리에서는 Oracle이 급여 열에 인덱스를 사용할 수 있지만 첫 번째 쿼리에서는 인덱스를 사용할 수 없습니다.

8. IN 및 EXISTS

때때로 열은 일련의 값과 비교됩니다. 가장 간단한 방법은 where 절에 하위 쿼리를 사용하는 것입니다. where 절에서는 두 가지 형식의 하위 쿼리를 사용할 수 있습니다.

첫 번째 형식은 IN 연산자를 사용하는 것입니다:

... where columns in (select * from ... where ...)

두 번째 형식은 EXIST 연산자를 사용하는 것입니다:

... where presents(...where ...에서 'X' 선택);

첫 번째 형식은 작성하기 쉽기 때문에 대부분의 사람들이 첫 번째 형식을 사용할 것이라고 생각하지만 실제로는 두 번째 형식이 첫 번째 형식보다 훨씬 낫습니다. 이 형식은 매우 효율적입니다. Oracle에서는 거의 모든 IN 연산자 하위 쿼리를 EXISTS를 사용하여 하위 쿼리로 다시 작성할 수 있습니다.

두 번째 형식에서는 하위 쿼리가 'X' 선택으로 시작됩니다. EXISTS 절을 사용하면 하위 쿼리가 테이블에서 어떤 데이터를 추출하더라도 where 절만 살펴봅니다. 이런 방식으로 옵티마이저는 전체 테이블을 탐색할 필요 없이 인덱스만을 기반으로 작업을 완료할 수 있습니다. (여기서는 where 문에 사용된 열에 인덱스가 있다고 가정합니다.) IN 절과 비교하여 EXISTS는 IN 하위 쿼리보다 구성하기가 더 어려운 연결된 하위 쿼리를 사용합니다.

EXIST를 사용하면 Oracle 시스템은 기본 쿼리를 먼저 확인한 다음 첫 번째 일치 항목을 찾을 때까지 하위 쿼리를 실행하므로 시간이 절약됩니다. Oracle 시스템은 IN 하위 쿼리를 실행할 때 먼저 하위 쿼리를 실행하고 얻은 결과 목록을 인덱스된 임시 테이블에 저장합니다. 서브 쿼리를 실행하기 전에 시스템은 먼저 메인 쿼리를 일시 중지한 후 서브 쿼리를 임시 테이블에 저장한 후 메인 쿼리를 실행합니다. 이것이 일반적인 쿼리에 IN을 사용하는 것보다 EXISTS를 사용하는 것이 더 빠른 이유입니다.

동시에 가능한 한 NOT IN 대신 NOT EXISTS를 사용해야 합니다. 둘 다 NOT을 사용하지만(인덱스를 사용하여 속도를 줄일 수는 없음) NOT IN 쿼리보다 NOT EXISTS가 더 효율적입니다.

9 조건을 연결하기 위해 where 절에 or 를 사용하지 마세요. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다.

예: num=10 또는 num=20인 직원에서 ID를 선택하세요.

알겠습니다. 다음과 같이 쿼리해 보세요. num=10인 직원의 ID를 선택하고 num=20

10인 직원의 ID를 선택하세요. where 절의 필드에 대한 표현식 작업을 피해야 합니다.

이렇게 하면 엔진이 다음과 같이 됩니다. 인덱스 사용을 포기하고 전체 테이블 스캔을 수행하십시오. 예: select id from t where num/2=100은 다음과 같이 변경되어야 합니다: select id from t where num=100*2

11. where 절의 필드에 기능적 연산을 수행하지 마십시오

이로 인해 엔진을 포기합니다. 인덱스를 사용하여 전체 테이블 스캔을 수행합니다. 예: substring(name,1,3)='abc'인 t에서 id 선택, 이름이 abc로 시작하는 id는 다음과 같이 변경되어야 합니다.

select id from t where name like 'abc%'

12. where를 사용하지 마십시오. 절에서 "="의 왼쪽은 함수, 산술 연산 또는 기타 표현식 연산을 수행해야 합니다. 그렇지 않으면 시스템이 인덱스를 올바르게 사용하지 못할 수 있습니다.

13. 인덱스 필드를 조건으로 사용할 때 인덱스가 복합 인덱스인 경우 시스템이 인덱스를 사용하는지 확인하기 위해 인덱스의 첫 번째 필드를 조건으로 사용해야 합니다. 그렇지 않으면 인덱스가 사용되지 않습니다. 그리고 필드 순서는 인덱스 순서와 최대한 일치해야 합니다.

14. 인덱스는 많을수록 좋습니다

인덱스는 해당 선택의 효율성을 향상시킬 수 있지만 삽입 및 업데이트의 효율성도 감소시킵니다. 삽입 또는 업데이트 중에 인덱스가 다시 작성될 수 있으므로 인덱스를 구축하는 방법은 무엇입니까? 필요합니다. 사례별로 신중하게 고려하세요. 하나의 테이블에 6개 이상의 인덱스를 두지 않는 것이 가장 좋으며, 인덱스가 너무 많으면 일반적으로 사용되지 않는 일부 컬럼에 인덱스를 구축할 필요가 있는지 고려해야 합니다.

15. 숫자 필드를 사용해 보십시오. 필드에 숫자 정보만 포함되어 있으면 쿼리 및 연결 성능이 저하되고 저장 오버헤드가 증가합니다. 엔진은 쿼리 및 연결 처리 시 문자열의 각 문자를 하나씩 비교하는데, 숫자 유형의 경우 한 번의 비교만으로 충분하기 때문입니다.

16. char/nchar 대신 varchar/nvarchar를 사용하세요. 첫째, 가변 길이 필드의 저장 공간이 작아서 저장 공간을 절약할 수 있기 때문입니다. 둘째, 쿼리의 경우 상대적으로 작은 필드에서 검색 효율성이 향상됩니다. 분명히 더 높습니다.

17. 어디에서나 select * fromt를 사용하지 말고, "*"를 특정 필드 목록으로 바꾸고, 사용하지 않는 필드를 반환하지 마세요.

위 내용은 고품질, 고성능 SQL 쿼리문을 작성하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!