[RUBY-ON-RAILS] WHERE IN 절을 사용하여 인덱스를 사용하지 않는 MySQL은?

WHERE IN 절을 사용하여 인덱스를 사용하지 않는 MySQL은?

내 레일 응용 프로그램에서 데이터베이스 쿼리의 일부를 최적화하기 위해 노력하고있어 나는 나를 난처한 상황에 빠진 가지고 그 몇 가지 있습니다. 그들은 모두 WHERE 절에서 IN을 사용하는 모든 적절한 인덱스 위치에있는 것으로 나타나더라도 전체 테이블 스캔을하고 있습니다.

예를 들면 :

SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

수행 전체 테이블 스캔을하고 설명 말합니다 :

select_type: simple
type: all
extra: using where
possible_keys: index_user_metrics_on_user_id  (which is an index on the user_id column)
key: (none)
key_length: (none)
ref: (none)
rows: 208

인덱스는 IN 문을 사용하는 경우에 사용하거나 할 나는 다른 무언가를 할 필요가 있습니까? 쿼리는 여기에 내가 나의 관계를 정의하는 방법을 다시 방문 할 수 있도록 레일에 의해 생성 된,하지만 내가 먼저 DB 수준에서 잠재적 인 수정과 함께 시작 거라고 생각되고있다.

해결법

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

   1.참조 어떻게 MySQL은 인덱스를 사용합니다.

   참조 어떻게 MySQL은 인덱스를 사용합니다.

   또한 당신이 당신의 user_metrics 테이블에 추가 2000 또는 이렇게 행을 추가 한 후 MySQL은 여전히 ​​전체 테이블 스캔을 수행하는지 여부를 확인합니다. 작은 테이블에 액세스 별 인덱스가 실제로는 더 비싼 (I / O를 현명한) 테이블 스캔보다와 MySQL의 최적화는이 점을 고려해야 할 수도 있습니다.

   내 이전 게시물과는 달리, 그것은 MySQL은 또한 아주 좋은 소식입니다 비용 기반 최적화, 사용하고 있음을 밝혀 -, 당신이 당신이 당신의 데이터베이스에있는 데이터의 양이 대표라고 생각 할 때 한 번, 적어도 ANALYZE 실행 제공 미래의 일상적인 사용에.

   비용 기반 옵티 마이저 (등 오라클, 포스트 그레스)를 다룰 때, 당신은 확실히 이상 10~15%에 의해 크기가 증가함에 따라 사용자의 다양한 테이블에 ANALYZE 실행 주기적으로 확인해야합니다. (기타의 RDBMS는 DBA에게이 책임을 떠날 것입니다 반면 포스트 그레스는 기본적으로 당신을 위해 자동으로 즉, 당신이 할 것입니다.) 통계 분석을 통해 최적화 관련된 얼마나 많은 I / O (및 기타의 더 나은 아이디어를 얻을 도움이 될 것입니다 ANALYZE 다양한 후보의 실행 계획 사이에 선택할 때 같은 CPU 등의 자원은 정렬이 필요 예를 들면) 관련됩니다. 실행에 실패 매우 가난, 때로는 비참한 계획 결정에서 발생할 수 ANALYZE (예를 들어, 밀리 초 - 쿼리 가끔 복용 때문에 조인에 나쁜 중첩 루프의 시간을.)

   성능 분석 실행 한 후 여전히 만족스럽지 않은 경우에, 당신은 일반적으로 사용 힌트, 예를 들면하여 문제를 해결할 수있을 것입니다 FORCE INDEX는 반면, 다른 경우에 당신이 (당신이 레일 'nested_set를 사용하도록했다 물린 수도 예를 들어,이 오래된 일) MySQL의 버그를 통해 발견했을 수 있습니다.

   당신은 레일 응용 프로그램에 있기 때문에 지금, 그것은 성가신 일 (및 액티브의 목적 패배) 대신 액티브 생성 된 것들을 계속 사용의 힌트와 사용자 정의 쿼리를 발행 할을 할 것이다.

   나는 액티브에 의해 생성 된 조인 단지의 일부는 가끔 15 초 또는 더 MySQL을 5.1으로 인해 내부 테이블 스캔과 중첩 루프와 많이 가지고있는 반면 우리의 레일 응용 프로그램의 모든 SELECT 쿼리도, 포스트 그레스로 전환 한 후 100ms로 아래로 떨어 언급했다 때 지수가 사용할 수있었습니다. 어떤 최적화가 완벽하지, 당신은 옵션을 알고 있어야합니다. 다른 잠재적 인 성능 문제는 쿼리 계획 최적화 외에 잠그는 알고 있어야합니다. 하지만 이것은 문제의 범위를 벗어난다.

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

   2.이 인덱스를 강제로 시도 :

   이 인덱스를 강제로 시도 :

   SELECT `user_metrics`.*
   FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id)
   WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))
   

   난 그냥 정확히 동일한 쿼리에 인덱스를 사용하지, 확인 :

   EXPLAIN EXTENDED
   SELECT * FROM tests WHERE (test IN ('test 1', 'test 2', 'test 3', 'test 4', 'test 5', 'test 6', 'test 7', 'test 8', 'test 9'))
   
   1, 'SIMPLE', 'tests', 'range', 'ix_test', 'ix_test', '602', '', 9, 100.00, 'Using where'
   

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

   3.때로는 MySQL은 하나를 사용할 수있는 경우에도 인덱스를 사용하지 않습니다. 최적화 추정 인덱스를 사용하여 테이블의 행의 매우 큰 비율을 액세스 할 수 MySQL의를 필요로한다고 할 때이 문제가 발생하는 아래 한 상황이다. (이 경우, 테이블 스캔은 적게는 노력이 필요하기 때문에 빨리 많이 될 가능성이 높습니다.)

   때로는 MySQL은 하나를 사용할 수있는 경우에도 인덱스를 사용하지 않습니다. 최적화 추정 인덱스를 사용하여 테이블의 행의 매우 큰 비율을 액세스 할 수 MySQL의를 필요로한다고 할 때이 문제가 발생하는 아래 한 상황이다. (이 경우, 테이블 스캔은 적게는 노력이 필요하기 때문에 빨리 많이 될 가능성이 높습니다.)

   행의 몇 퍼센트는 IN 절을 일치?

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

   4.나는 파티에 늦었 알고있다. 하지만 비슷한 문제를 가진 다른 사람을 도울 수 있기를 바랍니다.

   나는 파티에 늦었 알고있다. 하지만 비슷한 문제를 가진 다른 사람을 도울 수 있기를 바랍니다.

   최근에, 나는 같은 문제에 봉착했습니다. 그 때 나는 내 문제를 해결하기 위해 자체 조인-일을 사용하기로 결정. 문제는 MySQL을하지 않습니다. 문제는 우리입니다. 서브 쿼리에서 반환 유형은 우리의 테이블에서 차이입니다. 그래서 우리는 선택 컬럼의 종류에 하위 쿼리의 유형을 캐스팅해야합니다. 다음 예제 코드는 다음과 같습니다

   select `user_metrics`.* 
   from `user_metrics` um 
   join (select `user_metrics`.`user_id` in (N, N, N, N) ) as temp 
   on um.`user_id` = temp.`user_id`
   

   또는 내 자신의 코드 :

   올드 (사용하지 않습니다 지수 : ~ 정)

   SELECT 
       `jxm_character`.*
   FROM
       jxm_character
   WHERE
       information_date IN (SELECT DISTINCT
               (information_date)
           FROM
               jxm_character
           WHERE
               information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY))
           AND `jxm_character`.`ranking_type` = 1
           AND `jxm_character`.`character_id` = 3146089;
   

   새로운 기능 : (사용 지수 : ~ 0.02s)

   SELECT 
       *
   FROM
       jxm_character jc
           JOIN
       (SELECT DISTINCT
           (information_date)
       FROM
           jxm_character
       WHERE
           information_date >= DATE_SUB('2016-12-2', INTERVAL 7 DAY)) AS temp 
           ON jc.information_date = STR_TO_DATE(temp.information_date, '%Y-%m-%d')
           AND jc.ranking_type = 1
           AND jc.character_id = 3146089;
   

   jxm_character :

   SHOW 변수 '% 버전 %를'LIKE;

   'protocol_version', '10'
   'version', '5.1.69-log'
   'version_comment', 'Source distribution'
   

   마지막 주 : 당신이 MySQL의 인덱스가 가장 왼쪽 규칙을 이해합니다.

   P / S : 내 나쁜 영어 죄송합니다. D : 나는 내 솔루션을 취소 (물론 생산,) 내 코드를 게시 할 수 있습니다.

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

   5.당신이 where 절 주위에 여분의 브라켓을 제거하면 그것은 더 나은 얻을 수 있습니까?

   당신이 where 절 주위에 여분의 브라켓을 제거하면 그것은 더 나은 얻을 수 있습니까?

   그냥 당신이했습니다 때문에 200 개 정도 행을 가지고 있다고 할 수 있지만, 그것은 테이블 스캔이 더 빠른 것입니다 결정했다. 그것에 이상의 레코드가있는 테이블로 봅니다.

from https://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause by cc-by-sa and MIT license