IN 절의 MySQL 여러 열

IN 절의 MySQL 여러 열

저는 시작 위치와 끝 위치에 대한 지리적 좌표 x,y에 해당하는 4개의 열이 있는 데이터베이스를 가지고 있습니다.열은 다음과 같습니다.

• x0
• y0
• x1
• y1

저는 x0, y0, x1, y1 순서로 이 네 개의 열에 대한 인덱스를 가지고 있습니다.

저는 지리학적 쌍들의 조합이 100개 정도 되는 목록을 가지고 있습니다.이 데이터를 효율적으로 조회하려면 어떻게 해야 합니까?

이 SO 답변에서 제안하는 대로 이와 같은 작업을 수행하고 싶지만 MySQL이 아닌 Oracle 데이터베이스에서만 작동합니다.

SELECT * FROM my_table WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1));

지수로 뭔가 할 수 있을 것 같아서요?가장 좋은 접근법(즉, 가장 빠른 질의)은 무엇입니까?도와주셔서 감사합니다!

주의:

• 데이터베이스의 스키마를 변경할 수 없습니다.
• 100'000'000개 정도 줄을 가지고 있습니다.

편집: 코드 그대로가 실제로 작동했지만 매우 느렸고 인덱스를 활용하지 못했습니다(MySQL의 이전 버전이 있기 때문입니다).v5.6.27).

인덱스를 효과적으로 사용하기 위해, 우리는 다시 작성할 수 있습니다.IN술어의

예

(x0, y0, x1, y1) IN ((4, 3, 5, 6),(9, 3, 2, 1))

다음과 같은 경우:

(  ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 ) 
OR ( x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 )
)

편집

MySQL 옵티마이저의 최신 버전은 성능 문제를 해결하며, 사용 가능한 인덱스를 보다 효과적으로 사용할 수 있는 실행 계획을 생성합니다.

그(a,b) IN ((7,43),(7,44),(8,1))구문은 MySQL의 많은 버전에서 지원되었지만, Optimizer에 의해 생성된 차선의 실행 계획 때문에 (적어도 중요하지 않은 집합에서는) 성능 문제가 있었습니다.

그러나 MySQL의 새로운 버전에서는 옵티마이저가 개선되었습니다. 새로운 옵티마이저는 보다 효율적인 실행 계획을 생성할 수 있습니다.

에 관련된 유사한 문제를 기록합니다.OR건축물을 짓다.다음은 열로 정렬된 20개 행의 "다음 페이지"를 가져오기 위한 쿼리 예입니다.seq그리고.sub(unique 튜플).마지막으로 가져온 페이지(seq,sub)=(7,42)

이전 버전의 MySQL에서는 이 구문을 사용할 수 없습니다.

 WHERE (seq,sub) > (7,42)
 ORDER BY seq, sub
 LIMIT 20

그리고 MySQL이 구문을 지원했을 때, 우리는 우리가 작성했던 것과 같은 실행 계획을 얻을 것입니다.

WHERE ( seq > 7 ) 
   OR ( seq = 7 AND sub > 42 ) 
ORDER BY sub, seq
LIMIT 20

대신 미묘하게 다른 내용을 작성한다면 훨씬 효율적인 실행 계획을 얻을 수 있을 것입니다.

WHERE ( seq >= 7 ) 
  AND ( seq > 7 OR sub > 42 ) 
ORDER BY sub, seq
LIMIT 20

MySQL 옵티마이저를 통해 훨씬 더 나은 계획을 얻을 수 있습니다. 옵티마이저 계획은 (sub, seq)에서 사용 가능한 유니크 인덱스를 사용하고 범위 스캔 작업에서 인덱스 순서대로 행을 반환할 것으로 기대합니다.

저는 당신의 요점을 이해하지 못하겠습니다.다음 쿼리는 유효한 MySQL 구문입니다.

SELECT *
FROM my_table
WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1));

MySQL이 당신이 설명한 복합 인덱스를 사용할 것으로 기대합니다.하지만 그렇지 않다면 다음과 같은 일을 할 수 있을 수 있습니다.

SELECT *
FROM my_table
WHERE x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6
UNION ALL
. . .
SELECT *
FROM my_table
WHERE x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1

에서의 .WHERE절은 인덱스를 활용합니다.

MySQL을 사용하면 표시된 것처럼 행 생성기를 비교할 수 있지만, 최적화자는 MySQL 5.7까지 인덱스를 사용하여 성능을 향상시키는 방법을 알지 못했습니다.

• https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html

4개의 값을 문자열로 연결하여 다음과 같이 확인할 수 있습니다.

SELECT * 
FROM my_table 
WHERE CONCAT_WS(',', x0, y0, x1, y1) IN ('4,3,5,6', ..., '9,3,2,1');

당신이 하고 있는 방식은 내 기계의 mysql 버전으로 정확한 결과를 주는 것입니다.사용중입니다v5.5.55 것입니다 아마도 당신은 오래된 것을 사용하고 있을 것입니다.확인 부탁드립니다.

여전히 이 문제를 자신의 버전으로 해결하고 싶거나 위에 언급한 해결책이 작동하지 않으면 다음 해결책만 읽어 보십시오.

여기 있는 모든 칼럼의 데이터 종류와 범위에 대해서는 아직 잘 모르겠습니다.따라서 데이터 유형은 정수이고 범위는 0에서 9 사이라고 가정합니다.이 경우 아래와 같이 쉽게 수행할 수 있습니다.

select * from s1 where x0+10*x1+100*y1+1000*y2 in (4356,..., 9321);

언급URL : https://stackoverflow.com/questions/44706196/mysql-multiple-columns-in-in-clause