mysql in 또는 인덱스 오류

MySQL의 IN 문은 여러 값을 한 번에 쿼리할 수 있는 매우 일반적으로 사용되는 쿼리 문입니다. 예:

SELECT * FROM table WHERE id IN (1,2,3,4);

이 쿼리 문 will ID가 1, 2, 3, 4인 행을 반환합니다. IN 문의 장점은 여러 OR 문을 사용하지 않고도 한 번에 여러 값을 쿼리할 수 있다는 것입니다. 하지만 IN 문에 값이 많으면 인덱스 오류가 발생할 수 있어 쿼리 효율성에 심각한 영향을 미칠 수 있습니다.

인덱스 실패가 발생하는 이유와 이를 방지하는 방법을 살펴보겠습니다.

1. 인덱스 실패 원인

IN 문을 사용할 때 MySQL은 각 값을 개별적으로 비교하므로 인덱스 실패가 발생합니다.

주문 세부 정보가 포함된 주문 테이블이 있고 필드 중 하나가 주문의 고객 ID를 나타내는 customer_id라고 가정합니다. 고객 ID가 1, 2, 3, 4인 모든 주문을 쿼리하려고 합니다. 다음 쿼리 문을 사용할 수 있습니다.

SELECT * FROM 주문 WHERE customer_id IN (1,2,3,4);

customer_id 필드에 인덱스가 있는 경우 MySQL은 이 인덱스를 사용하여 쿼리합니다. 그러나 MySQL이 IN 문의 각 값에 대해 별도의 비교를 수행해야 하는 경우 전체 인덱스를 스캔해야 하므로 인덱스가 유효하지 않게 됩니다.

고객 ID 1인 500개, 고객 ID 2인 200개, 고객 ID 3인 100개, 고객 ID 4인 50개를 포함하여 1,000개의 주문이 있다고 가정합니다. 위의 쿼리 문을 사용하는 경우 MySQL은 전체 인덱스의 500+200+100+50=850 행을 스캔해야 하며 이는 분명히 성능에 심각한 영향을 미칠 것입니다.

2. 인덱스 오류를 방지하는 방법

인덱스 오류를 방지하기 위해 몇 가지 기술을 사용하여 쿼리 문을 다시 작성할 수 있습니다. 다음은 몇 가지 일반적인 기술입니다.

2.1 여러 OR 문 사용

IN 문의 값 수가 적으면 몇 개만 사용하면 여러 OR 문을 사용할 수 있습니다. 예:

SELECT * FROM 주문 WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 3 OR customer_id = 4;

이렇게 하면 MySQL은 성능 문제 없이 쿼리에 인덱스를 사용하게 됩니다. 그러나 IN 문에 값이 많으면 이 접근 방식은 실현 가능하지 않습니다.

2.2 EXISTS 문 사용

또 다른 방법은 EXISTS 문을 사용하여 다음을 쿼리하는 것입니다.

SELECT *
FROM 주문 o
WHERE EXISTS (
SELECT *
FROM (VALUES (1), (2), (3), ( 4)) AS c(customer_id)
WHERE c.customer_id = o.customer_id
);

IN 문의 값을 서브 쿼리에 넣은 후, EXISTS 문을 이용하여 쿼리하는 쿼리문입니다. 이 방법을 사용하면 IN 문으로 인해 발생하는 인덱스 오류 문제를 방지할 수 있습니다.

2.3 임시 테이블 사용

또 다른 방법은 임시 테이블을 사용하여 IN 문에 값을 저장한 다음 JOIN 문을 사용하여 쿼리하는 것입니다.

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_customer_ids (
customer_id INT PRIMARY KEY
);

INSERT IGNORE INTO tmp_customer_ids (customer_id)
VALUES (1), (2), (3), (4);

SELECT *
FROM 주문 o
JOIN tmp_customer_ids tci ON tci.customer_id = o. customer_id;

this 메소드는 IN 문에 값을 저장하기 위해 임시 테이블을 생성한 다음 JOIN 문을 사용하여 임시 테이블과 주문 테이블을 연결하여 쿼리합니다. 이 방법을 사용하면 IN 문으로 인한 인덱스 오류 문제를 피할 수 있으며 쿼리 결과를 캐시하는 것도 더 쉽습니다(MySQL은 캐시 JOIN 문을 쿼리합니다).

3. 요약

IN 문은 매우 편리한 쿼리 문이지만, IN 문에 값이 많을 경우 인덱스 오류가 발생하여 성능에 심각한 영향을 줄 수 있습니다. 이 문제를 방지하려면 여러 OR 문을 사용하거나 EXISTS 문을 사용하거나 임시 테이블을 사용하여 쿼리 문을 다시 작성할 수 있습니다. 이러한 방법을 사용하면 인덱스 오류를 방지하고 쿼리 성능을 향상할 수 있습니다.

위 내용은 mysql in 또는 인덱스 오류의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!