SQL 쿼리가 너무 오래 걸리는데 다른 방법이 있나요?

SQL 쿼리가 너무 오래 걸리는데 다른 방법이 있나요?

MariaDB에 3D 벡터 이미지를 저장하고 싶지만 데이터를 검색하는 데 시간이 너무 많이 걸려 실용적이지 못합니다.

테이블이 몇 개 있습니다.

x, y, z 좌표에 도면요소 ID를 더한 점 표

고유 ID, 엔터티 유형(텍스트, 선, 폴리선 등)을 포함하는 엔터티 테이블 색상 및 선 유형과 같은 기타 공통 속성

텍스트, 텍스트 높이, 선 두께 및 플래그와 같은 추가 값을 포함하는 일부 보조 테이블은 필드 유형(varchar, int 또는 float)을 기준으로 별도의 테이블로 분할됩니다.

저는 다음과 같이 PHP를 통해 자료에 접근하고 있습니다.

if($result = mysqli_query($conn, "SELECT entityID,X,Y,Z FROM dwgpoints WHERE drawing=".$DrawingID." AND blockID=".$blockID.";"))
{
  $previous_eID=0;
  while($row = mysqli_fetch_array($result))
  {
    $eID=$row['entityID'];
    if($previous_eID!=$eID)
    {
      if($previous_eID)// confirm it's not zero
        renderEntity($image_handle,$DrawingID,$previous_eID,$etype,$colour,$ltype,$points, $transformation, $clip);
      $previous_eID=$eID;
      if($eResult=mysqli_query($conn,"SELECT colour,ltype,etype FROM entity WHERE drawing=".$ID." AND eID=".$eID.";")){
        $erow=mysqli_fetch_assoc($eResult);
        $colour=$erow['colour'];
        $ltype=$erow['ltype'];
        $etype=$erow['etype'];
        $points=[[$row['X'],$row['Y'],$row['Z']]];
      }
    }else{
      $points[]=[$row['X'],$row['Y'],$row['Z']];
    }
  }
}

이 프로세스에는 최대 10분이 소요되지만, 예를 들어 Openstreet maps는 유사한 양의 데이터를 사용하여 타일을 렌더링합니다.

EXPLE 지침의 결과는 다음과 같습니다.

MariaDB [wptest_11]> EXPLAIN SELECT entityID,X,Y,Z FROM dwgpoints WHERE drawing=2 AND blockID=-1;
+------+-------------+-----------+------+---------------+--------+---------+-------------+-------+-----------------------+
| id   | select_type | table     | type | possible_keys | key    | key_len | ref         | rows  | Extra                 |
+------+-------------+-----------+------+---------------+--------+---------+-------------+-------+-----------------------+
|    1 | SIMPLE      | dwgpoints | ref  | idx_id        | idx_id | 9       | const,const | 24939 | Using index condition |
+------+-------------+-----------+------+---------------+--------+---------+-------------+-------+-----------------------+

데이터 검색을 간소화하여 프로세스 시간을 관리할 수 있습니까?저는 저렴한 VPS를 사용하고 있기 때문에 하드웨어 성능 문제가 있을 수 있습니다. 어떤 경우에 업그레이드를 하면 큰 차이가 있습니까?아니면 접근 방식을 재고해야 합니까?

어떤 조언이든 환영합니다.

참고로, 저는 더 나은 성능을 가진 라즈베리 파이3에서 수백만 개의 레코드를 실행했습니다.하드웨어가 도움이 되지만, 먼저 다음을 살펴보겠습니다.

• 애플리케이션에 적합한 경우 통화 최소화
• 성능 향상을 위해 WHERE 절의 필드에 일부 인덱스 추가

단일 쿼리

다음 쿼리를 필요에 맞게 조정합니다.그럼 시간이 어떻게 되는지 보세요.26k 결과의 경우 1초 미만이어야 합니다.

SELECT 
    a.entityID
  , a.X
  , a.Y
  , a.Z 
  , b.colour
  , b.ltype
  , b.etype 
  , b.drawing
 FROM dwgpoints   a
 LEFT JOIN entity b 
 ON     b.eID     = a.entityID 
    AND b.drawing = a.drawing
 WHERE 
     a.drawing = ".$ID." 
 AND a.blockID = ".$blockID.
 ;

인덱스

일부 인덱스 만들기:

1. 의 조합drawing그리고.blockID를 위해dwgpoints테이블.
2. 의 조합drawing그리고.eID를 위해entity테이블.

위 쿼리를 다시 실행하십시오. 개선 사항이 있을 것입니다.

또한 테이블 사이에 외부 키를 추가하여 데이터베이스의 데이터 무결성을 개선하는 데 도움이 될 것입니다.

언급URL : https://stackoverflow.com/questions/58517380/my-sql-query-is-taking-too-long-is-there-another-approach