[SQL] 값 (대형?) 수에 MySQL의 "IN"연산자 성능

값 (대형?) 수에 MySQL의 "IN"연산자 성능

요즘 레디 스 및 MongoDB를 실험 한 그것을 당신이 MongoDB를 나 레디 스 중 하나에서 ID의 배열을 저장 할 경우가 종종 있다는 것을 보인다. 나는 MySQL의 IN 연산자에 대해 요구하고 있기 때문에 나는이 질문에 대한 레디 스 스틱 것입니다.

나는 어떻게이처럼 보일 것이다 ID의 IN 연산자 내부에 많은 수의 (300-3000)를 나열하는 것입니다 확대됨에 궁금 해서요 :

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)

당신은 일반적으로 특정 카테고리에서 제품을 얻기 위해서 함께 가입 수있는 제품 및 카테고리 테이블로 간단하게 뭔가를 상상 해보세요. 내가 ID를 4 범주의 모든 제품 ID를 반환하고, IN 연산자 내부 SELECT 위의 쿼리에서 장소를 (product_ids가 : 4 종류)의 예에서 당신은 레디 스에서 주어진 범주에 것을 볼 수 있습니다 위.

이 얼마나 성능이 좋은입니까?

이것은 "이 달려있다"상황인가? 또는 구체적인 "빠른"또는 "느린"또는 나는 LIMIT (25)를 추가해야하거나하지 않습니다 도움이 또는 "이 (받아 들일 수있다"는 무엇입니까?

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
LIMIT 25

아니면 제품 ID의 배열이 25로 제한 만 3000 LIMIT - 보내고 쿼리 내부에서 25로 대신 쿼리에 25 ID의를 추가 할 레디 스에 의해 반환 된 것 트림해야합니까?

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)

모든 제안 / 의견을 많이 감사합니다!

해결법

1. ==============================

   1.은 IN 목록 (100 이하의 지역에서 일반적으로 그 '너무 큰'일부 잘못 정의 된 값)이 너무 커질 경우 일반적으로 말해서, 그것은 임시 테이블 그렇게해야하는 경우를 작성, 조인을 사용하는 것이 더 효율적이된다 숫자를 개최합니다.

   은 IN 목록 (100 이하의 지역에서 일반적으로 그 '너무 큰'일부 잘못 정의 된 값)이 너무 커질 경우 일반적으로 말해서, 그것은 임시 테이블 그렇게해야하는 경우를 작성, 조인을 사용하는 것이 더 효율적이된다 숫자를 개최합니다.

   숫자가 조밀 한 세트 (빈틈이없는 - 샘플 데이터에서 알) 인 경우에, 당신은 더 나은과 함께 할 수있는 WHERE (300) 및 3000 ID입니다.

   그러나, 아마도 차이가있는가 차이가있는 경우에 당신이 사용할 수, 수가 상대적으로 적은 않는 한 결국 유효 값 목록 (함께 가야하는 것이 더있을 수 있습니다 포인트, 세트에있다 :

   WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836
   

   아니면 차이는 무엇이든.

2. ==============================

   2.나는 몇 가지 테스트를하고있다, 다윗 펠스는 그의 대답에 말한 것처럼, 그것은 아주 잘 최적화되어 있습니다. 참고로, 나는 1,000,000 레지스터와 함께 InnoDB의 테이블을 만들었으며 50 개 임의의 숫자와 함께 "IN"연산자를 선택하고 일을, 내 MAC에 2.5 초 정도 걸립니다; 단, 짝수 레지스터를 선택하는 0.5 초 걸린다.

   나는 몇 가지 테스트를하고있다, 다윗 펠스는 그의 대답에 말한 것처럼, 그것은 아주 잘 최적화되어 있습니다. 참고로, 나는 1,000,000 레지스터와 함께 InnoDB의 테이블을 만들었으며 50 개 임의의 숫자와 함께 "IN"연산자를 선택하고 일을, 내 MAC에 2.5 초 정도 걸립니다; 단, 짝수 레지스터를 선택하는 0.5 초 걸린다.

   내가 가진 것이 유일한 문제는 내가 my.cnf 파일에서의 max_allowed_packet 매개 변수를 증가했다 있다는 것입니다. , 신비한 "MYSQL 멀리 갔다"하지 않으면 오류가 발생합니다.

   여기에 내가 테스트를 만드는 데 사용하는 PHP 코드는 다음과 같습니다

   $NROWS =1000000;
   $SELECTED = 50;
   $NROWSINSERT =15000;
   
   $dsn="mysql:host=localhost;port=8889;dbname=testschema";
   $pdo = new PDO($dsn, "root", "root");
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   
   $pdo->exec("drop table if exists `uniclau`.`testtable`");
   $pdo->exec("CREATE  TABLE `testtable` (
           `id` INT NOT NULL ,
           `text` VARCHAR(45) NULL ,
           PRIMARY KEY (`id`) )");
   
   $before = microtime(true);
   
   $Values='';
   $SelValues='(';
   $c=0;
   for ($i=0; $i<$NROWS; $i++) {
       $r = rand(0,99);
       if ($c>0) $Values .= ",";
       $Values .= "( $i , 'This is value $i and r= $r')";
       if ($r<$SELECTED) {
           if ($SelValues!="(") $SelValues .= ",";
           $SelValues .= $i;
       }
       $c++;
   
       if (($c==100)||(($i==$NROWS-1)&&($c>0))) {
           $pdo->exec("INSERT INTO `testtable` VALUES $Values");
           $Values = "";
           $c=0;
       }
   }
   $SelValues .=')';
   echo "<br>";
   
   
   $after = microtime(true);
   echo "Insert execution time =" . ($after-$before) . "s<br>";
   
   $before = microtime(true);  
   $sql = "SELECT count(*) FROM `testtable` WHERE id IN $SelValues";
   $result = $pdo->prepare($sql);  
   $after = microtime(true);
   echo "Prepare execution time =" . ($after-$before) . "s<br>";
   
   $before = microtime(true);
   
   $result->execute();
   $c = $result->fetchColumn();
   
   $after = microtime(true);
   echo "Random selection = $c Time execution time =" . ($after-$before) . "s<br>";
   
   
   
   $before = microtime(true);
   
   $sql = "SELECT count(*) FROM `testtable` WHERE id %2 = 1";
   $result = $pdo->prepare($sql);
   $result->execute();
   $c = $result->fetchColumn();
   
   $after = microtime(true);
   echo "Pairs = $c Exdcution time=" . ($after-$before) . "s<br>";
   

   그리고 결과 :

   Insert execution time =35.2927210331s
   Prepare execution time =0.0161771774292s
   Random selection = 499102 Time execution time =2.40285992622s
   Pairs = 500000 Exdcution time=0.465420007706s
   

3. ==============================

   3.당신이 ID의 숫자를 넣어 중첩 된 쿼리를 실행할 수있는 당신은 임시 테이블을 만들 수 있습니다 예:

   당신이 ID의 숫자를 넣어 중첩 된 쿼리를 실행할 수있는 당신은 임시 테이블을 만들 수 있습니다 예:

   CREATE [TEMPORARY] TABLE tmp_IDs (`ID` INT NOT NULL,PRIMARY KEY (`ID`));
   

   선택 :

   SELECT id, name, price
   FROM products
   WHERE id IN (SELECT ID FROM tmp_IDs);
   

4. ==============================

   4.IN은 괜찮습니다, 잘 최적화. 당신이 인덱스 필드에서 사용할 확인하고있는 거 잘.

   IN은 괜찮습니다, 잘 최적화. 당신이 인덱스 필드에서 사용할 확인하고있는 거 잘.

   그것은 동일한 기능입니다 :

   (x = 1 OR x = 2 OR x = 3 ... OR x = 99)
   

   마찬가지로 지금까지 DB 엔진에 관한 한.

5. ==============================

   5.많은 레코드 목록에 큰 파라미터 세트에 사용하는 것은 사실 느려집니다.

   많은 레코드 목록에 큰 파라미터 세트에 사용하는 것은 사실 느려집니다.

   나는 최근에 해결하는 경우에는 내가이 있었다 곳 조항, 2,50 매개 변수를 사용하여 하나의 40 만 개 기록 테이블을 조회, 3,500 매개 변수를 사용하여 다른.

   내 쿼리는 표준 WHERE IN을 사용하여 5 분했다. 대신에 (자신의 인덱스 테이블의 매개 변수를 넣어)은 IN 문에 대한 하위 쿼리를 사용하여, 나는 2 초에 쿼리 다운을 얻었다.

   내 경험에 MySQL과 오라클 모두를 위해 일했다.

6. ==============================

   6.당신은 IN 연산자에 대한 많은 값을 제공 할 때 먼저 중복 제거를 정렬해야합니다. 적어도 나는 그렇게 생각한다. 이 분류는 N 로그 N 시간이 걸립니다, 너무 많은 가치를 제공 할 좋지 않은 것 그래서.

   당신은 IN 연산자에 대한 많은 값을 제공 할 때 먼저 중복 제거를 정렬해야합니다. 적어도 나는 그렇게 생각한다. 이 분류는 N 로그 N 시간이 걸립니다, 너무 많은 가치를 제공 할 좋지 않은 것 그래서.

   내 경험은 작은 부분 집합으로 값의 세트를 얇게하고 응용 프로그램의 모든 쿼리의 결과를 결합하여 최고의 성능을 제공하는 것이 입증했다. 나는 (퍼베이시브) 다른 데이터베이스에 경험을 수집 것을 인정하지만, 같은 모든 엔진에 적용 할 수있다. 세트 당 값의 내 수는 500-1000이다. 더 많은 이하는 상당히 느렸다.

from https://stackoverflow.com/questions/4514697/mysql-in-operator-performance-on-large-number-of-values by cc-by-sa and MIT license