MySQL의 하위 쿼리를 최적화하는 방법은 무엇입니까?

실제 개발에서는 특정 필드와 일치하는 값 집합에 해당하는 in 하위 쿼리를 자주 사용하므로 보다 편리하게 필터링하고 쿼리할 수 있습니다. 그러나 이 하위 쿼리 방법은 데이터 양이 많을 경우 성능에 큰 문제를 일으킬 수 있습니다. 이 기사에서는 MySQL이 하위 쿼리를 최적화하는 방법을 소개합니다.

1. 서브 쿼리에서는 사용하지 마세요

실제 프로젝트에서는 이런 식으로 쓰는 경우가 많습니다.

SELECT *
FROM table
WHERE col1 IN (SELECT col1 FROM table2 WHERE condition);

table2의 조건에 따르면 col1의 여러 행 값은 다음과 같습니다. 을 꺼내서 테이블의 값을 일치시키고 일치하는 행을 반환합니다. 그러나 이런 방식으로 작성하면 MySQL이 내부적으로 하위 쿼리를 실행하는 방식이 하위 쿼리의 결과 집합을 메모리(또는 디스크)에 캐시하기 때문에 성능 병목 현상이 발생합니다. disk)이 캐시됩니다. 이로 인해 많은 I/O 작업이 발생하고 하위 쿼리의 결과 집합이 커지면 많은 양의 메모리도 차지하게 됩니다.

따라서 실제 개발에서는 서브쿼리에서의 사용을 지양하시고, 대신 Join을 사용해도 됩니다.

2. 서브쿼리 대신 조인을 사용하세요

서브쿼리에서 대체하려면 조인을 사용하세요. 서브쿼리의 작성 방식과 서브쿼리의 원본을 조인으로 변환하여 SQL 구문을 최적화할 뿐입니다. , 하위 쿼리보다 실행 효율성이 훨씬 높습니다. col1을 찾은 다음 아래와 같이 table1의 col1과 조인합니다.

SELECT table.*
FROM table
JOIN table2 ON table.col1 = table2.col1
WHERE table2.condition;

in 하위 쿼리와 비교하여 Join을 사용하면 하위 쿼리의 결과 집합을 테이블과 연결할 수 있어 많은 메모리(디스크) 읽기를 줄일 수 있습니다. 작업.

3. 하위 쿼리 대신 존재를 사용하는 것은 실제로는 조인을 사용하는 것입니다. in 서브쿼리와 달리,exist 서브쿼리는 결과 집합의 크기에 관계없이 간단한 판단만 수행하면 됩니다. 다음은exists 하위 쿼리의 구문 예입니다.

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition);

하위 쿼리 대신 기존을 사용하면 효율성이 크게 향상되고 I/O 및 메모리 소비를 많이 절약할 수 있습니다.

4. 인덱스를 사용하여 in 문 최적화

인덱스를 사용하여 쿼리 중에 in 하위 쿼리 속도를 높일 수 있다면 쿼리 효율성도 크게 향상됩니다. MySQL 인덱스는 기본 키 인덱스, 고유 인덱스, 일반 인덱스의 세 가지 유형으로 구분됩니다. 적절한 인덱스를 생성할 수 있으면 MySQL에서 전체 테이블 스캔을 수행하지 않고 쿼리 효율성을 높일 수 있습니다.

CREATE INDEX idx_col1 ON table (col1);

col1 값이 큰 경우 인덱스를 사용하면 쿼리 효율성이 크게 최적화되고 하위 쿼리에서 사용하여 발생하는 효율성 문제가 줄어듭니다.

5. 하위 쿼리에서 최적화하기 위해 제한 및 존재 사용

하위 쿼리의 결과 집합이 매우 큰 경우 쿼리 효율성 최적화 목적을 달성하기 위해 전체 테이블 스캔을 피하면서 제한 및 존재를 사용하여 이에 대한 페이징 쿼리를 수행할 수 있습니다. .

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition LIMIT 1000, 20);

이 SQL 문의 기능은 table2의 결과 집합을 찾은 다음 col1과 table을 사용하여 존재를 수행하고 쿼리 결과 집합을 20으로 제한하고 1000번째 행부터 쿼리하는 것입니다.

6. 문에서 메모리 최적화의 적절한 사용

질의에 사용된 in 하위 질의 결과에 행이 많지 않은 경우 in 대신 set을 사용할 수 있습니다. set은 후속 쿼리 일치를 위해 in 하위 쿼리의 결과 집합을 메모리에 저장합니다. 메모리를 사용하여 in 문을 최적화하면 성능이 크게 향상될 수도 있습니다.

SET @col1 = (SELECT GROUP_CONCAT(DISTINCT col1) FROM table2 WHERE condition);
SELECT *
FROM table
WHERE FIND_IN_SET(table.col1, @col1);

이 문은 먼저 데이터 일치를 위해 select를 사용한 다음 GROUP_CONCAT을 사용하여 col1 값 목록을 @col1에 저장되는 문자열로 연결합니다. 후속 쿼리에서는 일치를 위해 FIND_IN_SET이 사용되고 쿼리 효율성을 최적화하기 위해 메모리 캐싱이 사용됩니다.

7. 요약

in 하위 쿼리를 사용할 때는 특히 데이터 양이 많은 경우 전체 테이블 스캔을 피하십시오. 그렇지 않으면 심각한 성능 문제가 발생할 수 있습니다. 조인, 존재, 인덱스 최적화, 적절한 제한 사용, 메모리 및 기타 방법 사용을 통해 쿼리 효율성을 높이고 하위 쿼리의 성능을 최적화할 수 있습니다. 실제 프로젝트에서는 최상의 성능 최적화 효과를 달성하기 위해 특정 상황에 따라 최상의 솔루션을 선택해야 합니다.

위 내용은 MySQL의 하위 쿼리를 최적화하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!