이 SQL의 쿼리 성능 향상

이 SQL의 쿼리 성능 향상

결과를 반환하는 데 13초가 걸리는 이 쿼리의 성능을 개선하고 싶습니다.

메인 테이블에는 2백만 개 이상의 레코드가 있습니다.34번입니다.가 r.date_gps.

SELECT
    r.id,
    c.name,
    CONCAT(v.title, '', v.plaque) AS title,v.type_vehicle,
    r.sn,
    r.lat,
    r.lng,
    r.direction,
    r.date_db,
    r.date_gps,
    r.volt_main,
    r.speed,
    r.ing,
    t.sat_fix,
    r.sat
FROM registers r
JOIN trackers t ON t.cod = r.sn
JOIN installations i ON t.id = i.trackers_id
JOIN vehicles v ON v.id = i.vehicles_id
JOIN clients c ON c.id = v.clients_id
WHERE r.date_gps = (
    SELECT MAX(rr.date_gps) FROM registers rr WHERE r.sn = rr.sn
)
AND c.management_id = p_management
GROUP BY r.sn
ORDER BY r.date_gps DESC;

도 ★★★★★★★★★★★★★★★.Mysql에서는, ★★★★★★★★★★★를 서포트하고 않습니다.Row_Number 함수

.sub-query로로 합니다.INNER JOIN

SELECT r.id, 
       c.NAME, 
       Concat(v.title, '', v.plaque) AS title, 
       v.type_vehicle, 
       r, 
       sn, 
       .....
FROM   registers r 
       JOIN trackers t 
         ON t.cod = r.sn 
       JOIN installations i 
         ON t.id = i.trackers_id 
       JOIN vehicles v 
         ON v.id = i.vehicles_id 
       JOIN clients c 
         ON c.id = v.clients_id 
            AND c.management_id = p_management 
       INNER JOIN (SELECT Max(rr.date_gps) AS date_gps, 
                          sn 
                   FROM   registers rr
                   GROUP BY sn) a 
               ON r.sn = a.sn 
                  AND r.date_gps = a.date_gps 
GROUP  BY r.sn 
ORDER  BY r.date_gps DESC; 

할 때,은 「어느 쪽인가」입니다.FROM ★★★★★★★★★★★★★★★★★」WHERE§:

FROM registers r JOIN 
     trackers t
     ON t.cod = r.sn JOIN
     installations i
     ON t.id = i.trackers_id JOIN
     vehicles v
     ON v.id = i.vehicles_id JOIN
     clients c
     ON c.id = v.clients_id
WHERE r.date_gps = (SELECT MAX(rr.date_gps) FROM registers rr WHERE r.sn = rr.sn
                   ) AND
      c.management_id = p_management
GROUP BY r.sn
ORDER BY r.date_gps DESC;

이 시사하는 해 줍니다.clients(management_id, id),vehicles(clients_id, id),installations(vehicles_id, trackers_id),trackers(trackers_id, cod) , , , , 입니다.registers(sn, date_gps).

에 의해, 는, 「」, 「」, 「」, 「(」)로 clients " "에서의 필터링, " "management_id그러면 조인에서는 인덱스만 사용되며 쿼리의 이 부분은 매우 빠릅니다.수천, 매치가 , 론론수 of of .GROUP BY 쿼리가에는 "MySQL"의 시키기 위한 이 거의 ).GROUP BY를 참조해 주세요.

이러한 대량의 데이터를 사용하여 쿼리 속도를 높이려면 먼저 유지 보수 가능 데이터를 축소해야 합니다.때로는 2개의 쿼리가 1개보다 나을 수 있습니다.

WHERE 데이터를 먼저 가져오십시오.

SELECT MAX(rr.date_gps) FROM registers rr WHERE r.sn = rr.sn

그런 다음 결과를 사용하여 기본 쿼리에 추가합니다.

여기서 다른 테이블에 참여하기 전에 먼저 하위 쿼리를 사용할 수도 있습니다.

예를들면

SELECT registers.*
FROM (
    SELECT registers.id
    FROM registers
    WHERE <My WHERE Conditions>
) AS registers_sub

INNER JOIN registers 
ON registers_sub.id = registers.id

나머지 질문도 실행해 주세요.많은 참여와 그룹을 수행했을 때 작은 데이터 세트를 얻을 수 있습니다.

그리고 마지막 힌트는 모든 것이 필요하지 않은 경우 SELECT 세그먼트에서 *를 사용하지 마십시오;D

인사말

성능을 더 잘 문의할 수 있도록 도와주신 모든 분들께 감사드립니다.

VR46은 쿼리를 이 형식으로 다시 작성했지만 메인 뱅크는 계속 느려졌습니다.

ZFNerd도 2개의 쿼리로 분할되어 같은 저속 현상이 발생합니다.고든 리노프, 필드는 이미 색인화되어 있습니다.

최신 정보를 얻기 위해 다른 필드를 사용하는 문의가 있지만, 일부 기기는 가장 오래된 데이터 뒤에 현재 데이터를 전송하여 최신 기록을 찾는 데 어려움을 겪고 있습니다.다만, 가장 먼저 송신하도록 설정할 수 있는 것도 있습니다.일단 업데이트는 빠르고 문제는 최소화할 수 있도록 이 포맷으로 하겠습니다.

SELECT c.name,CONCAT(v.title,' ',v.plaque) AS title,v.type_vehicle,r.id,
r.sn,r.lat,r.lng, r.direction,r.date_db,r.date_gps,r.volt_main,r.speed,
r.ign,r.sat_fix,r.sat
FROM registers r
JOIN trackers t ON t.cod = r.sn
JOIN installations i ON t.id = i.trackers_id
JOIN vehicles v ON v.id = i.vehicles_id
JOIN clients c ON c.id = v.clients_id
JOIN (
    SELECT rr.sn, MAX(rr.id) AS id FROM registers rr GROUP BY sn
) AS L2 ON r.sn = L2.sn AND r.id = L2.id
AND c.management_id = p_management
GROUP BY r.sn;

다른 쿼리는 max() group by를 선택하면 도움이 되지만 다른 루트를 사용하는 것이 좋을 수 있습니다.특정 유형의 화물을 운송하는 차량을 추적하고 있으며 특정 시점의 최신 위치를 원하는 것 같습니다.

"Trackers" 테이블을 업데이트하고 LastGPSDate 열을 추가하는 것이 더 나을 수 있습니다.그런 다음 Date_GPS 테이블에 삽입할 때 트리거를 통해 해당 추적기에 업데이트를 발행합니다.LastGPSDate(COD = 삽입원 gps.sn)입니다(join 절에 기반).그러면 문의가 다음과 같이 간소화됩니다.

SELECT
      r.id,
      c.name,
      CONCAT(v.title, '', v.plaque) AS title,v.type_vehicle,
      r.sn,
      r.lat,
      r.lng,
      r.direction,
      r.date_db,
      r.date_gps,
      r.volt_main,
      r.speed,
      r.ing,
      t.sat_fix,
      r.sat
   FROM 
      clients c 
         JOIN vehicles v 
            ON c.id = v.clients_id
            JOIN installations i 
              ON v.id = i.vehicles_id
              join trackers t 
                 ON i.trackers_id = t.id
                 JOIN registers r 
                    ON t.cod = r.sn
                   AND t.LastGPSDate = r.date_gps
   WHERE
      c.management_id = p_management
   ORDER BY 
      t.LastGPSDate DESC;

당신이 찾고 있는 것과 더 잘 어울리도록 테이블 순서를 바꿨습니다.관리 ID를 통해 특정 위치를 가진 하나의 클라이언트부터 시작합니다.그리고 나서, 그들의 차량만 설비, 추적기, 그리고 마침내 등록한다.

다음으로 인덱스 테이블인덱스 클라이언트( management_id, id )차량(id, clients_id )설치(trackers_id, vehicles_id)트래커(LastGPSDate, cod, id, sat_fix)레지스터(sn, date_gps)를 권장합니다.

1개의 관리 ID에 대해 특별히 제한되는 join 조항으로 인해 저는 그것을 인덱스의 첫 번째 위치에 넣었습니다.

따라서 200만 개 이상의 레코드 중 데이터의 BULK는 20, 30, 100개의 다른 관리 ID가 될 수 있습니다.왜 데이터를 실행할 때마다 모든 레지스터에 적용되는 MAX를 생성해야 합니까?트리거를 통해 날짜/시간을 1회 스탬프로 표시함으로써 원하는 클라이언트에 대해서만 데이터를 빠르게 얻을 수 있습니다.

언급URL : https://stackoverflow.com/questions/34566465/improve-query-performance-of-this-sql