Comment accélérer l'exécution des requêtes MySQL lentes-Questions et réponses sur le réseau chinois PHP

Chaque fois que j'exécute cette requête, elle continue d'afficher "Code d'erreur 2013. Connexion perdue à la requête 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);

J'aimerais savoir ce qui fait que le temps d'exécution dépasse 30 secondes et comment améliorer la requête. Toute aide est appréciée, merci !

Sortie de 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'

Sortie de 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}

Sortie de 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', '', '', '', ''

Afficher la table créée 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'

Afficher la table créée 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'

Afficher la table créée 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\''

Expliquez la requête :

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

répondre à tous(1)

P粉8313104042024-04-01 00:03:07 1 plancher

Passez de MyISAM à InnoDB. (Cela n'a peut-être pas d'importance pour cetterequête.)

Ajouter provisoirement un index :

A:  INDEX(Serial_Number,  status)

GAUCHE non obligatoire :

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

-->

JOIN ( ... ) B  ON ...

À ce stade, il pourrait être raisonnable de se débarrasser du LEFT JOIN imbriqué et de simplement joindre les niveaux individuels A, X, Y ensemble .

L'explication suggère qu'au moins certaines de ces simplifications sont automatiquement comprises par l'optimiseur.

A.Serial_Number = B.`board_sn

Je vois "utf8" et "latin1". Si une requête JOINNE un VARCHAR entre différents jeux de caractères (ou classements), aucun autre index approprié ne sera utilisé. Si cela pose un problème, je vous recommande d'utiliser ALTER .. CONVERT TO .. pour changer latin1 en utf8.

Je vois des colonnes nommées "DURÉE" et "INTERVALLE" déclarées comme VARCHAR. Cela peut causer des problèmes si vous effectuez des calculs sur de telles colonnes. (Je sais que des choses comme "BOARD_SN" ne sont pas vraiment des chiffres.)