느린 MySQL 쿼리를 더 빠르게 실행하는 방법

Question

이 쿼리를 실행할 때마다 "Error code 2013. Lost Connection to mysql query"가 계속 표시됩니다.

으아악

실행시간이 30초를 초과하는 원인과 쿼리 개선 방법을 알고 싶습니다. 도움을 주시면 감사하겠습니다!

hardware_tracking_msa.HAST_Detail의 출력:

으아악

skynet_msa.lots_to_hast_boards의 출력:

으아악

skynet_msa.labs_inventory의 출력:

으아악

생성된 테이블 hardware_tracking_msa.HAST_Detail 표시:

으아악

생성된 테이블 skynet_msa.lots_to_hast_boards 표시:

으아악

생성된 테이블 skynet_msa.labs_inventory 표시:

으아악

쿼리 설명:

DROP TABLE IF EXISTS elogbook_get_boardid;

CREATE TABLE elogbook_get_boardid AS
  (SELECT DISTINCT `LOTID`,
                   `Board_ID`,
                   `Serial_Number`,
                   coalesce(CASE
                                WHEN A.`Serial_Number` = B.`board_sn` THEN 'In Use'
                                ELSE A.`status`
                            END, '') AS `Status`,
                   coalesce(B.`LOT_LOCATION`, '') AS `chamber`,
                   coalesce(B.`created_date`, '') AS `Start Date`,
                   coalesce(B.`BINOUT_DUE_DATE`, '') AS `Est End`
   FROM hardware_tracking_msa.HAST_Detail A
   LEFT JOIN
     (SELECT X.*,
             Y.`BINOUT_DUE_DATE`,
             Y.`LOT_LOCATION`
      FROM skynet_msa.lots_to_hast_boards X
      LEFT JOIN skynet_msa.labs_inventory Y ON X.`lotid` = Y.`LOTID`) B ON A.`Serial_Number` = B.`board_sn`
   WHERE `LOTID` IS NOT NULL);

Answer 1

MyISAM에서 InnoDB로 변경하세요. (이것은 이 쿼리에는 중요하지 않을 수 있습니다.)

잠정적으로 색인 추가:

으아악

LEFT 필요하지 않음:

으아악

-->

으아악

이 시점에서는 중첩된 LEFT JOIN을 제거하고 단순히 개별 레벨 A,X,Y를 함께 결합하는 것이 합리적일 수 있습니다.

설명에 따르면 이러한 단순화 중 적어도 일부는 최적화 프로그램에 의해 자동으로 파악됩니다.

A.Serial_Number = B.`board_sn

"utf8"과 "latin1"이 보입니다. 쿼리가 서로 다른 문자 집합(또는 데이터 정렬) 사이에 VARCHAR을 JOIN하는 경우 다른 적합한 인덱스는 사용되지 않습니다. 이것이 문제라면 ALTER .. CONVERT TO ..를 사용하여 latin1을 utf8로 변경하는 것이 좋습니다.

'DURATION' 및 'INTERVAL'이라는 열이 VARCHAR로 선언된 것을 볼 수 있습니다. 이러한 열에 대해 숫자 처리를 수행하면 문제가 발생할 수 있습니다. ("BOARD_SN"과 같은 것은 실제로 숫자가 아니라는 것을 알고 있습니다.)