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

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

---

최근에 Redis와 MongoDB를 실험 해 왔으며 MongoDB 또는 Redis 에 ID 배열을 저장하는 경우가 종종있는 것 같습니다 . MySQL IN 연산자 에 대해 묻고 있으므로이 질문에 대해서는 Redis를 계속 사용하겠습니다 .

IN 연산자 내부에 많은 수 (300-3000)의 ID 를 나열하는 것이 얼마나 성능이 좋은지 궁금합니다 .

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

특정 범주 의 제품 을 가져 오기 위해 일반적으로 함께 조인 할 수 있는 제품 및 범주 테이블 처럼 간단한 것을 상상해보십시오 . 위의 예에서 Redis ( ) 의 지정된 카테고리에서 ID가 4 인 카테고리의 모든 제품 ID를 반환 하고 연산자 내부의 위 쿼리에 배치하는 것을 볼 수 있습니다.category:4:product_idsSELECTIN

얼마나 성능이 좋은가요?

이것이 "의존적"상황입니까? 또는 구체적인 "허용되지 않음"또는 "빠름"또는 "느림"이 LIMIT 25있습니까? 아니면을 추가해야합니까 , 아니면 도움이되지 않습니까?

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

아니면 Redis에서 반환 한 제품 ID 배열을 잘라서 25 개로 제한하고 쿼리에 3000 개가 아닌 25 개 ID 만 추가하고 쿼리 LIMIT내부에서 25 개로 지정해야 합니까?

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

모든 제안 / 피드백은 대단히 감사합니다!

---

일반적으로 IN목록이 너무 커지면 (일반적으로 100 이하의 영역에있는 일부 잘못 정의 된 '너무 큰'값의 경우) 조인을 사용하는 것이 더 효율적이되어 필요한 경우 임시 테이블을 생성합니다. 숫자를 저장합니다.

숫자가 조밀 한 집합 (간격 없음-샘플 데이터가 제안하는 것) 인 경우 WHERE id BETWEEN 300 AND 3000.

그러나 아마도 세트에 간격이있을 수 있으므로 결국 유효한 값 목록을 사용하는 것이 더 나을 수 있습니다 (간격이 상대적으로 적은 경우가 아니면 다음을 사용할 수 있습니다.)

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

또는 그 간격이 무엇이든.

---

나는 몇 가지 테스트를 해왔고 David Fells가 그의 대답에서 말했듯 이 꽤 잘 최적화되어 있습니다. 참고로, 저는 1,000,000 개의 레지스터가있는 InnoDB 테이블을 만들고 500,000 개의 난수를 가진 "IN"연산자로 선택을 수행했습니다. MAC에서는 2.5 초 밖에 걸리지 않습니다. 짝수 레지스터 만 선택하는 데 0.5 초가 걸립니다.

내가 가진 유일한 문제 max_allowed_packet는 my.cnf파일 에서 매개 변수 를 늘려야한다는 것 입니다. 그렇지 않으면 신비한 "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

---

원하는 수의 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);

---

IN괜찮고 최적화되어 있습니다. 인덱싱 된 필드에서 사용하는지 확인하고 괜찮습니다.

It's functionally equivalent to:

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

As far as the DB engine is concerned.

---

Using IN with a large parameter set on a large list of records will in fact be slow.

In the case that I solved recently I had two where clauses, one with 2,50 parameters and the other with 3,500 parameters, querying a table of 40 Million records.

My query took 5 minutes using the standard WHERE IN. By instead using a subquery for the IN statement (putting the parameters in their own indexed table), I got the query down to TWO seconds.

Worked for both MySQL and Oracle in my experience.

---

When you provide many values for the IN operator it first must sort it to remove duplicates. At least I suspect that. So it would be not good to provide too many values, as sorting takes N log N time.

My experience proved that slicing the set of values into smaller subsets and combining the results of all the queries in the application gives best performance. I admit that I gathered experience on a different database (Pervasive), but the same may apply to all the engines. My count of values per set was 500-1000. More or less was significantly slower.

참고URL : https://stackoverflow.com/questions/4514697/mysql-in-operator-performance-on-large-number-of-values