MySQL이 대용량 데이터를 처리할 때 쿼리 속도를 최적화하는 방법에 대한 자세한 설명
- 怪我咯원래의
- 2017-04-30 10:10:371502검색
최근 업무상 필요로 인해 Mysql 데이터베이스의 select 쿼리 문에 대한 관련 최적화 방법에 주목하기 시작했습니다. 필요한 사람은 참고하면 됩니다
실제로 참여한 프로젝트로 인해 mysql 테이블의 데이터 양이 수백만에 도달하면 일반 SQL 쿼리의 효율성이 급격하게 떨어지며, 쿼리 속도가 참을 수 없는 쿼리 조건입니다. 한번은 400만 개 이상의 레코드( 인덱스 포함)가 포함된 테이블에서 조건부 쿼리를 테스트한 적이 있는데, 쿼리 시간이 40초나 걸렸습니다. 그렇게 높은 쿼리 지연으로 인해 모든 사용자가 미치게 될 것이라고 생각합니다. . 따라서 SQL 문 쿼리의 효율성을 어떻게 향상시키는가는 매우 중요합니다. 다음은 인터넷에서 널리 유포되는 30가지 SQL 쿼리문 최적화 방법입니다.
1. where 절에 != 또는 <>연산자를 사용하지 마세요. will 엔진은 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다.
2. 쿼리를 최적화하려면 전체 테이블 스캔을 피하십시오. 먼저 where 및 order by와 관련된 열에 인덱스를 생성하는 것을 고려하십시오.
3. where 절에 있는 필드의 null 값을 판단하지 마십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 다음과 같이 전체 테이블 검색을 수행합니다.
select id from t where num is null
num에 기본값 0을 설정하고 테이블의 num 열에 null 값이 없는지 확인한 후 다음과 같이 쿼리할 수 있습니다.
select id from t where num=0
4. where 절에서 또는 연결 조건을 사용하지 마십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 다음과 같이 전체 테이블 스캔을 수행합니다.
select id from t where num=10 또는 num =20
다음과 같이 쿼리할 수 있습니다.
select id from t where num=10
union all
select id from t where num=20
5. 전체 테이블 스캔도 발생합니다: (백분율 기호 앞에 올 수 없음)
'�c%'와 같은 이름이 있는 t에서 ID를 선택하세요
효율성을 높이려면 전체 텍스트 검색을 고려할 수 있습니다.
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. 함수 연산은 필드 내의 필드에서 수행되며, 이로 인해 엔진은 인덱스 사용을 포기하고 전체 테이블 스캔을 수행하게 됩니다. 예:
select id from t where substring(name,1,3)='abc'–name id start with abc
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 createate>='2005- 11 -30′ 및 createate<'2005-12-1′
10. where 절의 "=" 왼쪽에서 함수, 산술 연산 또는 기타 표현식 연산을 수행하지 마십시오. 그렇지 않으면 시스템이 작동할 수 있습니다. 인덱스를 올바르게 사용하지 마십시오.
11. 인덱스 필드를 조건으로 사용할 때 인덱스가 복합 인덱스인 경우 시스템이 인덱스를 사용하도록 하기 위해 인덱스의 첫 번째 필드를 조건으로 사용해야 합니다. 그렇지 않으면 인덱스는 사용되지 않으며 필드 순서는 가능한 한 인덱스 순서와 일치해야 합니다.
12. 의미 없는 쿼리를 작성하지 마세요. 예를 들어 빈 테이블 구조를 생성해야 하는 경우:
1=0인 경우 #t에서 col1,col2를 선택하세요.
이 유형은 코드는 아무것도 반환하지 않지만 시스템 리소스를 소비하므로
create table #t(…)
13으로 변경해야 합니다. 좋은 선택입니다:
select num from a where num in(select num from b)
다음 명령문으로 바꾸세요:
select num from a where presents(select 1 from b where num=a.num )
14. 모든 인덱스가 쿼리에 효과적인 것은 아닙니다. SQL은 테이블의 데이터를 기반으로 쿼리를 최적화합니다. 인덱스 열에 중복된 데이터가 많은 경우 SQL 쿼리는 인덱스를 사용하지 않을 수 있습니다. 예를 들어, 테이블에 성별이 있는 경우 남성과 여성이 거의 다르므로 성별을 기반으로 인덱스를 구축하더라도 쿼리 효율성에는 아무런 영향을 미치지 않습니다.
15. 인덱스는 많을수록 좋습니다. 인덱스는 해당 선택의 효율성을 향상시킬 수 있지만 삽입 또는 업데이트 중에 인덱스가 다시 작성될 수 있으므로 삽입 및 업데이트의 효율성도 감소합니다. 색인이 필요합니다. 사례별로 신중하게 고려하세요. 하나의 테이블에 6개 이상의 인덱스를 두지 않는 것이 가장 좋으며, 인덱스가 너무 많으면 일반적으로 사용되지 않는 일부 컬럼에 인덱스를 구축할 필요가 있는지 고려해야 합니다.
16. 클러스터형 인덱스 데이터 열의 순서는 테이블 레코드의 물리적 저장 순서이므로 가능한 한 업데이트를 피해야 합니다. 전체 테이블 레코드가 영향을 받습니다. 순서를 조정하면 상당한 리소스가 소모됩니다. 응용 프로그램 시스템이 클러스터형 인덱스 데이터 열을 자주 업데이트해야 하는 경우 인덱스를 클러스터형 인덱스로 구축해야 하는지 여부를 고려해야 합니다.
17. 숫자 필드를 사용하십시오. 숫자 정보만 포함된 필드인 경우에는 쿼리 및 연결 성능이 저하되고 저장 오버헤드가 증가합니다. 엔진은 쿼리 및 연결 처리 시 문자열 의 각 문자를 하나씩 비교하는데, 숫자 유형의 경우 한 번의 비교만으로 충분하기 때문입니다.
18. char/nchar 대신 varchar/nvarchar를 최대한 사용하세요. 우선 가변 길이 필드는 저장 공간이 작아서 저장 공간을 절약할 수 있기 때문입니다. 작은 필드 분명히 더 높습니다.
19. 어디에서나 select *를 사용하지 말고, "*"를 특정 필드 목록으로 바꾸고, 사용하지 않는 필드를 반환하지 마세요.
20. 임시 테이블 대신 테이블 변수를 사용해 보세요. 테이블 변수에 많은 양의 데이터가 포함되어 있는 경우 인덱스가 매우 제한적이라는 점에 유의하세요(기본 키 인덱스만 해당).
21. 시스템 테이블 리소스 소비를 줄이려면 임시 테이블을 자주 만들고 삭제하지 마세요.
22. 임시 테이블을 적절하게 사용하면 특정 루틴을 더 효율적으로 만들 수 있습니다. 예를 들어 큰 테이블이나 자주 사용하는 테이블을 참조해야 할 때입니다. 데이터세트. 그러나 일회성 이벤트의 경우 내보내기 테이블을 사용하는 것이 좋습니다.
23. 임시 테이블을 생성할 때 일회성 24. 임시 테이블을 사용하는 경우저장 프로시저 끝에서 모든 임시 테이블을 명시적으로 삭제해야 합니다. 이렇게 하면 테이블을 삭제할 수 있습니다. 시스템 테이블이 오랫동안 잠겨 있습니다.
25. 커서의 효율성이 떨어지므로커서 사용을 피하세요. 커서 작업의 데이터가 10,000행을 초과하는 경우 다시 작성하는 것이 좋습니다.
26. 커서 기반 방법이나 임시 테이블 방법을 사용하기 전에 먼저 문제를 해결하기 위한 집합 기반 솔루션을 찾아야 합니다. 일반적으로 집합 기반 방법이 더 효과적입니다. 27. 임시 테이블과 마찬가지로 커서를 사용할 수 없습니다. 작은 데이터 세트에 대해 FAST_28. 모든 저장 프로시저의 시작 부분에 SET NOCOUNT를 ON으로 설정하고 가 를 트리거하고 마지막에 SET NOCOUNT OFF를 설정합니다. 저장 프로시저 및 트리거의 각 문 후에 클라이언트에 DONE_IN_PROC 메시지를 보낼 필요가 없습니다.
29. 클라이언트에 많은 양의 데이터를 반환하지 않도록 하세요. 데이터 양이 너무 많으면 해당 요구 사항이 합리적인지 고려해야 합니다. 30. 시스템 동시성을 향상하려면 대규모트랜잭션 작업을 피하세요.
위 내용은 MySQL이 대용량 데이터를 처리할 때 쿼리 속도를 최적화하는 방법에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!