변수를 사용하는 인덱스가 있는 경우 MySQL 순위 지정

변수를 사용하는 인덱스가 있는 경우 MySQL 순위 지정

@N=@N + 1을 사용하여 정렬된 열의 항목 순위를 가져옵니다.주문하기 전에 다른 테이블과 내부 결합함으로써 기본 테이블에서 일부 값을 필터링해야 합니다.따라서 쿼리는 다음과 같습니다.

SET @N=0;
SELECT 
  @N := @N + 1 AS rank,
  fa.id,
  fa.val 
FROM
    table1 AS fa 
    INNER JOIN table2 AS em 
      ON em.id = fa.id 
      AND em.type = "A" 
ORDER BY fa.val ;

문제는 em.type에 인덱스가 없으면 모든 것이 정상적으로 동작하지만 em.type에 인덱스를 붙이면 hell이 해제되고 val column에 따라 순서가 매겨지지 않고 랭크 값이 em 테이블에 저장되는 순서로 표시된다는 것입니다.

출력 예를 다음에 나타냅니다.

인덱스 없음-:

rank    id         val
1   05F8C7  55050.000000
2   05HJDG  51404.733458
3   05TK1Z  46972.008208
4   05F2TR  46900.000000
5   05F349  44433.412847
6   06C2BT  43750.000000
7   0012X3  42000.000000
8   05MMPK  39430.399658
9   05MLW5  39054.046383
10  062D20  35550.000000

인덱스 포함:

rank      id        val
480     05F8C7  55050.000000
629     05HJDG  51404.733458
1603    05TK1Z  46972.008208
466     05F2TR  46900.000000
467     05F349  44433.412847
3534    06C2BT  43750.000000
15      0012X3  42000.000000
1109    05MMPK  39430.399658
1087    05MLW5  39054.046383
2544    062D20  35550.000000

나는 지표를 사용하는 것이 완전히 투명해야 하고 그것에 의해 산출물이 영향을 받아서는 안 된다고 생각한다.이것은 MySQL의 버그입니까?

이 "꼼수"는 폭발을 기다리는 폭탄이었다.현명한 최적기는 쿼리가 적합하다고 판단되는 대로 평가하여 속도에 맞게 최적화합니다. 이것이 바로 최적기라고 불리는 이유입니다.MySQL 변수 사용이 예상대로 작동하지 않는 것으로 문서화되어 있다고 생각합니다만, 정상적으로 동작하고 있었습니다.

MariaDB 옵티마이저가 최근 개선될 때까지 작동했습니다.메인스트림 MySQL에서도 깨질 수 있습니다.아직 출시되지 않은 베타 버전 5.6에서는 옵티마이저가 몇 가지 개선되었습니다.

MySQL이 창 기능을 구현할 때까지 수행할 수 있는 작업은 셀프 조인 및 그룹화를 사용하는 것입니다.최적화 도구에서 향후 어떤 개선이 이루어지더라도 결과는 일관됩니다.단점은 효율적이지 않을 수 있다는 것입니다.

SELECT 
  COUNT(*) AS rank,
  fa.id,
  fa.val 
FROM
    table1 AS fa 
    INNER JOIN table2 AS em 
      ON em.id = fa.id 
      AND em.type = 'A'

    INNER JOIN

    table1 AS fa2 
    INNER JOIN table2 AS em2 
      ON em2.id = fa2.id 
      AND em2.type = 'A' 

      ON fa2.id <= fa.id 
                          --- assuming that `id` is the Primary Key of the table
GROUP BY fa.id
ORDER BY fa.val ;

언급URL : https://stackoverflow.com/questions/10856546/mysql-ranking-in-presence-of-indexes-using-variables