社群 學習 工具庫 休閒
搜尋
繁体中文
如何讓緩慢的 MySQL 查詢運作得更快
P粉311089279
P粉311089279 2024-03-31 08:37:39
0
1
518

每當我執行這個查詢時，它經常顯示「錯誤代碼 2013。遺失與 mysql 查詢的連線」：

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);

我想知道是什麼導致運行時間超過 30 秒以及如何改進查詢。感謝任何幫助，謝謝！

hardware_tracking_msa.HAST_Detail 的輸出：

Index, Board_Number, SIG_Number, Board_ID, Serial_Number, Design_ID, Package, Sockets, Socket_Number, Status, Notes, Deleted_By, Inserted_Date, Inserted_By, Updated_Date, Updated_By, Deleted_Date
'1', '2759', '594-11269', '2759-001', '605637/001', 'J4Q2', 'WFBGA, PIN COUNT: 168/529,PACKAGE SIZE: 12x12', '40', '530-10670  SINGLE BIAS (ESPEC)CANNOT USE FOR J80C/J4Q2', '', '', '', '2022-05-17 14:00:00', 'NWANGA', '2022-05-18 10:15:41', 'tmingyao', '2022-05-17 14:57:33'
'2', '2759', '594-11269', '2759-002', '605637/008', 'J4Q2', 'WFBGA, PIN COUNT: 168/529,PACKAGE SIZE: 12x12', '40', '530-10670  SINGLE BIAS (ESPEC)CANNOT USE FOR J80C/J4Q2', NULL, '', '', '2022-05-17 14:00:00', 'NWANGA', '2022-05-17 14:57:33', '', '2022-05-17 14:57:33'
'3', '2759', '594-11269', '2759-003', '608061/001', 'J4Q2', 'WFBGA, PIN COUNT: 168/529,PACKAGE SIZE: 12x12', '40', '530-10670  SINGLE BIAS (ESPEC)CANNOT USE FOR J80C/J4Q2', NULL, '', '', '2022-05-17 14:00:00', 'NWANGA', '2022-05-17 14:57:33', '', '2022-05-17 14:57:33'

skynet_msa.lots_to_hast_boards 的輸出：

\begin{table}[]
\begin{tabular}{lll}
lotid,        & board\_sn,     & created\_date         \
'CVZ2JL2.11', & '1790247/003', & '2022-07-20 '14:26:04 \
'CV4YJL2.11', & '1317876/002', & '2022-07-20 14:26:04  \
'CVRMHL2.11', & '1790241/014', & '2022-07-20 14:26:04 
\end{tabular}
\end{table}

skynet_msa.labs_inventory 的輸出：

LOTID, LOCATION, ENV_TEST_INTERVAL, EST_DURATION_TIME, ENV_STRESS_VOLTAGE, ENV_STRESS_VOLT_2, ENV_STRESS_VOLT_3, PRODUCT_FAMILY, PRODUCT_TECHNOLOGY, DESIGN_ID, QA_WORK_REQUEST_NO, QA_PROCESS_TYPE, QA_PROCESS_NAME, QA_BURN_EXPERIMENT, QA_CONTACT, QA_PROCESS_LOT_NO, FABRICATION_FACILITY, ASSEMBLY_FACILITY, ELEC_TEST_FLOW, CONFIGURATION_WIDTH, NUMBER_OF_DIE_IN_PKG, CURRENT_QTY, LOT_LOCATION, LEAD_COUNT, PACKAGE_TYPE, PACKAGE_LENGTH, PACKAGE_WIDTH, PACKAGE_HEIGHT, SOAK_LEVEL, BAKE_TEMPERATURE, DRB_TEMPERATURE, ACTUAL_CURE_TIME, REFLOW_PROFILE, PINOUT_VERSION, DISPATCH_DUE_DATE, BINOUT_DUE_DATE, ROW_CREATED, ROW_MODIFIED, LOCATION_DATE, LOCATION_WW, MODULE_LOT, BURN_LOT, MONITOR_IGNORE, TICKER, PRIORITY, ASM_LOT_NUMBER, MARK_FORMAT, LOCATION_TAT, RPM_WW, QA_EVENT_ID, TC_WEIGHT, AUTOMOTIVE_LOT, CUSTOMER_OPTION, PKG_RECEIVE_DATE, CUSTOMER_GROUP, SAMPLE_PULLED_DATE, QA_SPECIAL_FLOW, QA_BLOCKS, PROBE_CUSTOM_TESTED, QA_PROGRAM_REV, NAND_FLOW_TYPE, NUM_FLASH_CE_PINS, RETICLE_WAVE_ID, MAJOR_PROBE_PROG_REV, MAJOR_TEST_PROG_REV, CYCLING_TYPE, QA_TARGET_CYCLE, LAST_TEST_INTERVAL, CYCLING_TEMPERATURE, ENV_STRESS_DURATION, FIRST_TEST_INTERVAL, DRB_TARGET_INTERVAL, LTDR_TEMPERATURE, RD_STRESS_TYPE
'1623941.001', 'TEMP CYCLE K INV', '25', '12.50', '0.000', '0.000', '0.000', 'MCP', 'ALL IN ONE MCP', 'UM181', '', 'PRODUCTION SCREEN', 'TEMP CYCLE K', '', '', '', 'MIXED', 'ASSEMBLY-MSA', '', '', '8', '2439', 'S01-AR-ASRSIN', '254/432', 'TFBGA', '13.000', '11.500', '1.100', '', '0', '0', '0', '', 'JEDEC', '1970-01-01 00:00:00', NULL, '2022-06-05 18:35:12', '2022-06-06 00:00:16', '2022-06-05 18:35:00', '202223', '0', '0', '0', '0', '4', '1623941.001', '', '1.57', '', 'QA 13', '0', '', '', '1970-01-01 00:00:00', '', '1970-01-01 00:00:00', '', '', 'MOBILE C', '', '', '4', '', '', '', '', '', '', '', '25', '', '', '', ''
'BC4WSXZ.31', 'THERMAL WARPAGE', '0', '0.00', '0.000', '0.000', '0.000', 'MCP', 'MASSFLASH/LPDDR4', 'J86L', '', 'PRODUCTION SCREEN', 'THERMAL WARPAGE', '', '', '', 'MIXED', 'PTI P3', '', '', '2', '26', '', '194/1026', 'UFBGA', '9.000', '12.500', '0.545', '', '0', '0', '0', '', 'AVALON', '1970-01-01 00:00:00', NULL, '2022-07-19 11:00:17', '2022-07-20 13:30:15', '2022-07-19 11:26:45', '202229', '0', '0', '0', '0', '4', 'PT22900.25', 'AVALON', '0.28', '', 'QA 32', '0', '', '', '1970-01-01 00:00:00', '', '2022-07-19 10:58:00', '', '', '', '', '', '1', '', '', '29', '', '', '', '', '', '', '', '', ''
'BC6VVLZ.31', 'TEMP CYCLE K INV', '25', '12.50', '0.000', '0.000', '0.000', 'MCP', 'MASSFLASH/CONTROLLER', 'J39E', '', 'PRODUCTION SCREEN', 'TEMP CYCLE K', '', '', '', 'FAB 10', 'ASSEMBLY-MSA', '', 'X4-X8', '4', '320', 'S01-REL-LAB-IN', '153/196', 'VFBGA', '13.000', '11.500', '1.000', '', '0', '0', '0', '', 'JEDEC', '1970-01-01 00:00:00', NULL, '2022-07-10 14:35:16', '2022-07-11 07:15:19', '2022-07-10 14:31:49', '202228', '0', '0', '0', '0', '4', 'BF3HFCQ.5X', '', '419.25', '', 'QA 45', '0', 'YES', 'AUTOMOTIVE', '1970-01-01 00:00:00', '', '1970-01-01 00:00:00', '', '', 'NAND AUTO', '', '', '4', 'WAVE007', '22', '', '', '', '', '', '25', '', '', '', ''

顯示建立表hardware_tracking_msa.HAST_Detail：

CREATE TABLE `HAST_Detail` (
    `Index` int NOT NULL AUTO_INCREMENT, 
    `Board_Number` varchar(250) DEFAULT \'\', 
    `SIG_Number` varchar(250) DEFAULT \'\', 
    `Board_ID` varchar(250) DEFAULT \'\', 
    `Serial_Number` varchar(250) DEFAULT \'\', 
    `Design_ID` varchar(150) DEFAULT \'\', 
    `Package` varchar(250) DEFAULT \'\', 
    `Sockets` int DEFAULT \'0\', 
    `Socket_Number` varchar(250) DEFAULT \'\', 
    `Status` varchar(45) DEFAULT NULL, 
    `Notes` varchar(1000) DEFAULT \'\', 
    `Deleted_By` varchar(20) DEFAULT \'\', 
    `Inserted_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `Inserted_By` varchar(20) NOT NULL DEFAULT \'\', 
    `Updated_Date` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
    `Updated_By` varchar(20) DEFAULT \'\', 
    `Deleted_Date` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`Index`)
) ENGINE=InnoDB AUTO_INCREMENT=1459 DEFAULT CHARSET=utf8'

顯示建立表格skynet_msa.lots_to_hast_boards：

CREATE TABLE `lots_to_hast_boards` (
    `lotid` varchar(45) NOT NULL DEFAULT \'\', 
    `board_sn` varchar(45) NOT NULL DEFAULT \'\', 
    `created_date` datetime DEFAULT CURRENT_TIMESTAMP, 
    PRIMARY KEY (`lotid`,`board_sn`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

顯示建立表格skynet_msa.labs_inventory：

CREATE TABLE `labs_inventory` (
    `LOTID` varchar(12) NOT NULL, 
    `LOCATION` varchar(48) NOT NULL, 
    `ENV_TEST_INTERVAL` int DEFAULT \'0\', 
    `EST_DURATION_TIME` decimal(8,2) DEFAULT \'0.00\', 
    `ENV_STRESS_VOLTAGE` decimal(6,3) DEFAULT NULL, 
    `ENV_STRESS_VOLT_2` decimal(6,3) DEFAULT NULL, 
    `ENV_STRESS_VOLT_3` decimal(6,3) DEFAULT NULL, 
    `PRODUCT_FAMILY` varchar(45) DEFAULT NULL, 
    `PRODUCT_TECHNOLOGY` varchar(45) DEFAULT NULL, 
    `DESIGN_ID` varchar(6) DEFAULT NULL, 
    `QA_WORK_REQUEST_NO` varchar(100) DEFAULT NULL, 
    `QA_PROCESS_TYPE` varchar(45) DEFAULT NULL, 
    `QA_PROCESS_NAME` varchar(64) DEFAULT NULL, 
    `QA_BURN_EXPERIMENT` varchar(45) DEFAULT NULL, 
    `QA_CONTACT` varchar(45) DEFAULT NULL, 
    `QA_PROCESS_LOT_NO` varchar(12) DEFAULT NULL, 
    `FABRICATION_FACILITY` varchar(45) DEFAULT NULL, 
    `ASSEMBLY_FACILITY` varchar(45) DEFAULT NULL, 
    `ELEC_TEST_FLOW` varchar(45) DEFAULT NULL, 
    `CONFIGURATION_WIDTH` varchar(8) DEFAULT NULL, 
    `NUMBER_OF_DIE_IN_PKG` int DEFAULT NULL, 
    `CURRENT_QTY` int DEFAULT NULL, 
    `LOT_LOCATION` varchar(45) DEFAULT NULL, 
    `LEAD_COUNT` varchar(45) DEFAULT \'\', 
    `PACKAGE_TYPE` varchar(45) DEFAULT \'\', 
    `PACKAGE_LENGTH` decimal(6,3) DEFAULT \'0.000\', 
    `PACKAGE_WIDTH` decimal(6,3) DEFAULT \'0.000\', 
    `PACKAGE_HEIGHT` decimal(6,3) DEFAULT \'0.000\', 
    `SOAK_LEVEL` varchar(45) DEFAULT NULL, 
    `BAKE_TEMPERATURE` varchar(30) DEFAULT NULL, 
    `DRB_TEMPERATURE` varchar(30) DEFAULT NULL, 
    `ACTUAL_CURE_TIME` int DEFAULT NULL, 
    `REFLOW_PROFILE` varchar(45) DEFAULT NULL, 
    `PINOUT_VERSION` varchar(45) DEFAULT NULL, 
    `DISPATCH_DUE_DATE` datetime DEFAULT NULL, 
    `BINOUT_DUE_DATE` datetime DEFAULT NULL, 
    `ROW_CREATED` datetime NOT NULL, 
    `ROW_MODIFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    `LOCATION_DATE` timestamp NOT NULL DEFAULT \'1970-01-01 12:00:00\', 
    `LOCATION_WW` varchar(10) DEFAULT NULL, 
    `MODULE_LOT` int NOT NULL DEFAULT \'0\', 
    `BURN_LOT` int DEFAULT \'0\', 
    `MONITOR_IGNORE` int NOT NULL DEFAULT \'0\', 
    `TICKER` int NOT NULL DEFAULT \'0\', 
    `PRIORITY` varchar(4) DEFAULT NULL, 
    `ASM_LOT_NUMBER` varchar(45) DEFAULT NULL, 
    `MARK_FORMAT` varchar(45) DEFAULT NULL, 
    `LOCATION_TAT` double DEFAULT \'0\', 
    `RPM_WW` varchar(10) DEFAULT NULL, 
    `QA_EVENT_ID` varchar(15) DEFAULT NULL, 
    `TC_WEIGHT` double DEFAULT \'0\', 
    `AUTOMOTIVE_LOT` varchar(45) DEFAULT NULL, 
    `CUSTOMER_OPTION` varchar(45) DEFAULT NULL, 
    `PKG_RECEIVE_DATE` datetime DEFAULT NULL, 
    `CUSTOMER_GROUP` varchar(45) DEFAULT NULL, 
    `SAMPLE_PULLED_DATE` datetime DEFAULT NULL, 
    `QA_SPECIAL_FLOW` varchar(45) DEFAULT NULL, 
    `QA_BLOCKS` varchar(45) DEFAULT NULL, 
    `PROBE_CUSTOM_TESTED` varchar(45) DEFAULT NULL, 
    `QA_PROGRAM_REV` varchar(45) DEFAULT NULL, 
    `NAND_FLOW_TYPE` varchar(45) DEFAULT NULL, 
    `NUM_FLASH_CE_PINS` varchar(45) DEFAULT NULL, 
    `RETICLE_WAVE_ID` varchar(45) DEFAULT NULL, 
    `MAJOR_PROBE_PROG_REV` varchar(45) DEFAULT NULL, 
    `MAJOR_TEST_PROG_REV` varchar(45) DEFAULT NULL, 
    `CYCLING_TYPE` varchar(45) DEFAULT NULL, 
    `QA_TARGET_CYCLE` varchar(45) DEFAULT NULL, 
    `LAST_TEST_INTERVAL` varchar(45) DEFAULT NULL, 
    `CYCLING_TEMPERATURE` varchar(45) DEFAULT NULL, 
    `ENV_STRESS_DURATION` varchar(45) DEFAULT NULL, 
    `FIRST_TEST_INTERVAL` varchar(45) DEFAULT NULL, 
    `DRB_TARGET_INTERVAL` varchar(45) DEFAULT NULL, 
    `LTDR_TEMPERATURE` varchar(45) DEFAULT NULL, 
    `RD_STRESS_TYPE` varchar(45) DEFAULT NULL, 
    PRIMARY KEY (`LOTID`), 
    KEY `design_id` (`DESIGN_ID`), 
    KEY `lot_location` (`LOT_LOCATION`), 
    KEY `burn` (`DESIGN_ID`,`QA_BURN_EXPERIMENT`), 
    KEY `locations` (`LOT_LOCATION`,`LOCATION`), 
    KEY `all_index` (`LOCATION`,`LOT_LOCATION`,`DISPATCH_DUE_DATE`,`PRODUCT_FAMILY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
       COMMENT=\'table to store MAM data for msa labs skynet\''

解釋查詢：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'X', NULL, 'ALL', 'PRIMARY', NULL, NULL, NULL, '55', '90.00', 'Using where'
'1', 'SIMPLE', 'Y', NULL, 'eq_ref', 'PRIMARY', 'PRIMARY', '14', 'skynet_msa.X.lotid', '1', '100.00', 'Using where'
'1', 'SIMPLE', 'A', NULL, 'ALL', NULL, NULL, NULL, NULL, '1458', '10.00', 'Using where; Using join buffer (hash join)'

P粉311089279
P粉311089279

全部回覆(1)
P粉831310404
P粉8313104042024-04-01 00:03:07 1樓

從 MyISAM 更改為 InnoDB。 （這對於這個查詢可能並不重要。）

暫定新增索引：

A:  INDEX(Serial_Number,  status)

不需要 LEFT：

LEFT JOIN ( ... ) B  ON ...  WHERE b.id IS NOT NULL

-->

JOIN ( ... ) B  ON ...

此時，擺脫嵌套的 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”之類的東西並不是真正的數字。）

點贊 +0
新增回覆
熱門專題
更多>
熱門文章
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板