MySQL 문 최적화를 위한 30가지 팁

1. where 절에 != 또는 <> 연산자를 사용하지 마십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다.

2. 쿼리를 최적화하려면 전체 테이블 스캔을 피하십시오. 먼저 where 및 order by와 관련된 열에 인덱스를 생성하는 것을 고려하십시오.

3. where 절에 있는 필드의 null 값을 판단하지 마십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 다음과 같이 전체 테이블 스캔을 수행합니다.

select id from t where num is null

set on num 기본값은 0입니다. 테이블의 num 열에 null 값이 없는지 확인한 후 다음과 같이 쿼리합니다.

select id from t where num=0

4. where 절에서 or를 사용하지 마세요. 그렇지 않으면 엔진은 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다. 예:

select id from t where num=10 or num=20

는 다음과 같이 쿼리할 수 있습니다:

select id from t where num=10union allselect id from t where num=20

5. 다음 쿼리도 전체 테이블 검색: (백분율 기호 앞에 올 수 없음)

select id from t where name like '�c%'

If 효율성을 높이려면 전체 텍스트 검색을 고려하세요.

6. In과 not in도 주의해서 사용해야 합니다. 그렇지 않으면 다음과 같이 전체 테이블 스캔이 발생합니다.

select id from t where num in(1,2,3)

연속 값의 경우 between을 사용할 수 있으면 다음을 수행하세요. 다음에서는 사용하지 마세요:

select id from t where num between 1 and 3

7. 절에 매개변수를 사용하면 전체 테이블 스캔이 발생합니다. SQL은 런타임 시에만 지역 변수를 분석하므로 옵티마이저는 런타임까지 액세스 계획 선택을 연기할 수 없습니다. 컴파일 시 선택해야 합니다. 란 그러나 액세스 계획이 컴파일 시간에 빌드되면 변수 값을 여전히 알 수 없으며 인덱스 선택을 위한 입력으로 사용할 수 없습니다. 예를 들어 다음 문은 전체 테이블 검색을 수행합니다.

select id from t where num=@num

는 쿼리에서 인덱스를 사용하도록 강제로 변경할 수 있습니다.

select id from t with(index(索引名)) where num=@num

8. where 절은 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행하도록 합니다. 예:

select id from t where num/2=100

는 다음으로 변경되어야 합니다.

select id from t where num=100*2

9. where 절의 필드에 기능적 작업을 수행하지 마십시오. 그러면 엔진이 인덱스 사용을 포기하고 다음 작업을 수행하게 됩니다. 전체 테이블 스캔. 예:

select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

는 다음과 같이 변경되어야 합니다.

select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10. where 절의 "=" 왼쪽에서 함수, 산술 연산 또는 기타 표현식 연산을 수행하지 마십시오. 그렇지 않으면 시스템이 제대로 작동하지 않을 수 있습니다. 인덱스를 사용하십시오.

11. 인덱스 필드를 조건으로 사용할 때 인덱스가 복합 인덱스인 경우 시스템이 인덱스를 사용하도록 하기 위해 인덱스의 첫 번째 필드를 조건으로 사용해야 합니다. 그렇지 않으면 인덱스가 사용되지 않으며 필드 순서는 가능한 한 인덱스 순서와 일치해야 합니다.

12. 의미 없는 쿼리를 작성하지 마세요. 예를 들어 빈 테이블 구조를 생성해야 하는 경우:

select col1,col2 into #t from t where 1=0

이 유형의 코드는 결과 집합을 반환하지 않지만 시스템을 소모합니다. 다음과 같이 표시됩니다.

create table #t(…)

13. 대부분의 경우 in 대신 presents를 사용하는 것이 좋습니다.

select num from a where num in(select num from b)

다음 문으로 바꾸세요.

select num from a where exists(select 1 from b where num=a.num)

23. 임시 테이블을 생성할 때 한 번에 삽입되는 데이터의 양이 많은 경우, 데이터 양이 많아지면 로그가 많아지는 것을 방지하기 위해 테이블 ​​생성 대신 select into를 사용할 수 있습니다. 규모가 크지 않기 때문에 시스템 테이블의 리소스를 용이하게 하기 위해서는 먼저 테이블을 생성한 후 삽입해야 합니다.

24. 임시 테이블을 사용하는 경우 저장 프로시저가 끝날 때 모든 임시 테이블을 명시적으로 삭제해야 합니다. 이렇게 하면 시스템 테이블이 장기간 잠기는 것을 방지할 수 있습니다.

커서로 작동하는 데이터가 10,000행을 초과하는 경우 커서의 효율성이 떨어지므로 사용을 피하는 것이 좋습니다.

26. 커서 기반 방법이나 임시 테이블 방법을 사용하기 전에 먼저 문제를 해결하기 위한 집합 기반 솔루션을 찾아야 합니다. 일반적으로 집합 기반 방법이 더 효과적입니다.

27. 임시 테이블과 마찬가지로 커서를 사용할 수 없습니다. 작은 데이터 세트에 FAST_FORWARD 커서를 사용하는 것이 다른 행별 처리 방법보다 나은 경우가 많습니다. 특히 필요한 데이터를 얻기 위해 여러 테이블을 참조해야 하는 경우에는 더욱 그렇습니다. 결과 집합에 "합계"를 포함하는 루틴은 일반적으로 커서를 사용하는 것보다 빠르게 수행됩니다. 개발 시간이 허락한다면 커서 기반 방법과 세트 기반 방법을 모두 시도하여 어떤 방법이 더 효과적인지 확인할 수 있습니다.

28. 모든 저장 프로시저 및 트리거의 시작 부분에 SET NOCOUNT ON을 설정하고 끝 부분에 SET NOCOUNT OFF를 설정합니다. 저장 프로시저 및 트리거의 각 문 후에 클라이언트에 DONE_IN_PROC 메시지를 보낼 필요가 없습니다.

29. 클라이언트에 많은 양의 데이터를 반환하지 않도록 하세요. 데이터 양이 너무 많으면 해당 요구 사항이 합리적인지 고려해야 합니다.

30. 대규모 트랜잭션 작업을 피하고 시스템 동시성을 개선하세요.