왜 모든 서브쿼리는 키워드별로 주문하기 전에 select-statement로 실행됩니다.이러한 서브쿼리는 필요하지 않은 경우에도 실행됩니다.

왜 모든 서브쿼리는 키워드별로 주문하기 전에 select-statement로 실행됩니다.이러한 서브쿼리는 필요하지 않은 경우에도 실행됩니다.

1주일 전에 데이터베이스를 mySQL8에서 MariaDB10으로 변경했는데 성능 문제가 심각합니다.우리는 이유를 알아냈다: 우리는 선별된 문장에서 서브쿼리와 함께 작업하고 있다.ORDER BY들면 .하다

SELECT id, (SELECT id2 FROM table2 INNER JOIN [...] WHERE column.foreignkey = table.id) queryResult
FROM table
WHERE status = 5
ORDER BY column
LIMIT 10

table.status = 5.

mySQL8에서 일: ORDER BY ★★★★★★★★★★★★★★★★★」LIMIT실행 후 서브쿼리(10행 해당)

MariaDB10에서 일어나는 일: 서브쿼리가 실행됩니다(1.000.000 행이 영향을 받습니다).그 후ORDER BY ★★★★★★★★★★★★★★★★★」LIMIT

두 쿼리 모두 10개의 행을 반환하고 있지만 MariaDB10에서는 그 때문에 매우 느립니다.왜 이런 일이 생기는 거죠?그리고 MariaDB에서 이를 피하기 위해 활성화해야 할 옵션이 있습니까?ORDER BY에서 select subqueries가 언급되었을 때 실행된다는 것을 mySQL8에서 알고 있습니다.그러나 그렇지 않으면 결과 집합이 있을 때 실행됩니다.

정보: 이렇게 하면 모든 것이 정상입니다.

SELECT *, (SELECT id2 FROM table2 INNER JOIN [...] WHERE column.foreignkey = outerTable.id) 
FROM (
    SELECT id
    FROM table
    WHERE status = 5
    ORDER BY column
    LIMIT 10
) outerTable

도와주셔서 정말 감사합니다.

이것은 테이블 a가 본질적으로 정렬되지 않은 행 묶음이기 때문입니다.

SQL 표준에 따르면 "table"(및 FROM 절의 하위 쿼리)은 정렬되지 않은 행 집합입니다.테이블의 행(또는 FROM 절의 하위 쿼리)은 특정 순서로 정렬되지 않습니다.따라서 옵티마이저는 사용자가 지정한 ORDER BY 절을 무시할 수 있습니다.실제로 SQL 표준에서는 ORDER BY 절을 이 서브쿼리에 표시할 수 없습니다(ORDER BY가 ...이기 때문에 허용됩니다).LIMIT ... 결과, 행 집합, 순서뿐만 아니라 행 집합도 변경합니다.)

마리아답 매뉴얼

따라서 옵티마이저는 ORDER BY를 삭제하고 무시합니다.

하위 쿼리에서 LIMIT 및 ORDER By를 사용하여 우회하는 방법을 이미 찾았습니다.

검색과 검색 끝에 mySQL8에서 알고 있던 mariaDB10 데이터베이스를 사용할 수 있는 솔루션을 찾았습니다.

유사한 문제가 있는 경우: 서버에 연결할 때마다 이 설정을 하고 모든 것이 mySQL8에서처럼 작동합니다.

SET optimizer_use_condition_selectivity = 1

Long version : 위에서 설명한 문제가 갑자기 해결되어 mySQL8에서 예전처럼 서브쿼리가 실행되었습니다.난 아무 짓도 안 했어!

하지만 곧 새로운 문제가 생겼습니다. 통계 페이지가 생겼는데, 믿을 수 없을 만큼 느렸습니다.인덱스가 누락된 것을 발견하고 추가합니다.나는 쿼리를 실행했고 그것은 작동했다.38을 더한 후 결과를 찾는 데 영향을 받는 100.000행 인덱스를 사용하지 않습니다.잘 했어요.

그리고 이상한 일이 일어나기 시작했다.쿼리를 다시 실행했는데 데이터베이스가 인덱스를 사용하지 않았습니다.그래서 나는 그것을 몇 번이고 실행했다.결과는 다음과 같습니다.

첫 번째 쿼리 실행(이 작업을 수행했습니다)ANALYZE): 영향을 받는 100.000행

두 번째 쿼리 실행: 영향을 받는 행 38개

세 번째 쿼리 실행: 영향을 받는 행 38개

네 번째 쿼리 실행: 100.000 행의 영향

다섯 번째 쿼리 실행: 100.000 행의 영향

SaaS 솔루션에서조차 완전히 랜덤이었습니다.그래서 옵티마이저가 실행 계획을 결정하는 방법을 검색하기 시작합니다.이걸 찾았어요 optimizer_use_condition_selectivity

mariaDB10.4 서버의 기본값은 4입니다. 즉, 결과 세트를 계산하는 데 히스토그램이 사용됩니다.이것에 관한 비디오를 몇 개 보고, 우리의 경우(데이터베이스의 정규화를 고집하고 있지만)에는 효과가 없다는 것을 알았습니다.모드 1은 정상적으로 동작합니다.

전체 테이블 검사 또는 인덱스 검사를 통해 마지막으로 조인된 테이블에 액세스할 경우 인덱스 백업 범위 조건의 선택성을 사용하여 부분 조인 카디널리티를 계산합니다.

이게 나처럼 절망하는 다른 남자들에게 도움이 되었으면 좋겠어.

5.6에서 MariaDB와 MySQL은 Optimizer에 대해 서로 다른 방향으로 진행되었습니다.MariaDB는 서브쿼리에 많은 초점을 맞췄지만, 이 특정 쿼리에 피해를 입힐 수도 있습니다.

있습니까INDEX(status, column)? 이 쿼리의 대부분의 변형에 도움이 됩니다.

예, 서브쿼리는 각 행에 대해 평가해야 합니다.order by서브쿼리에는 필요한 것만 있습니다.id다음과 같이 표현할 수 있습니다.

SELECT id,
       (SELECT id2 FROM table2 INNER JOIN [...] WHERE column.foreignkey = t.id) as queryResult
FROM (SELECT t.*
      FROM table t
      WHERE status = 5
      ORDER BY column
      LIMIT 10
     ) t

그러면 테이블에서 행을 선택한 후에만 하위 쿼리가 평가됩니다.

언급URL : https://stackoverflow.com/questions/64504597/why-executes-mariadb-all-subqueries-in-select-statement-before-order-by-keyword