cari

Rumah  >  Soal Jawab  >  teks badan

Bagaimana untuk membuat pertanyaan MySQL perlahan berjalan lebih cepat

Setiap kali saya menjalankan pertanyaan ini, ia terus menunjukkan "Kod ralat 2013. Sambungan terputus ke pertanyaan 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);

Saya ingin tahu apa yang menyebabkan masa berjalan melebihi 30 saat dan bagaimana untuk menambah baik pertanyaan. Sebarang bantuan dihargai, terima kasih!

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

Output 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}

Output 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', '', '', '', ''

Tunjukkan jadual hardware_tracking_msa yang dibuat.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'

Tunjukkan jadual yang dibuat 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'

Tunjukkan jadual yang dibuat 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\''

Jelaskan pertanyaan:

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粉311089279P粉311089279233 hari yang lalu464

membalas semua(1)saya akan balas

  • P粉831310404

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

    Tukar daripada MyISAM kepada InnoDB. (Ini mungkin tidak penting untuk pertanyaan ini.)

    Tambahkan indeks secara sementara:

    A:  INDEX(Serial_Number,  status)

    KIRI tidak diperlukan:

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

    -->

    JOIN ( ... ) B  ON ...

    Pada ketika ini, mungkin munasabah untuk menyingkirkan JOIN KIRI bersarang dan hanya menyertai peringkat individu A,X,Y bersama-sama .

    Penjelasan menunjukkan bahawa sekurang-kurangnya beberapa pemudahan ini dikira secara automatik oleh pengoptimum.

    A.Serial_Number = B.`board_sn

    Saya nampak "utf8" dan "latin1". Jika sebarang pertanyaan MENYERTAI VARCHAR antara set aksara yang berbeza (atau himpunan), tiada indeks lain yang sesuai akan digunakan. Jika ini adalah masalah, saya syorkan anda menggunakan ALTER .. CONVERT TO .. untuk menukar latin1 kepada utf8.

    Saya melihat lajur bernama "DURATION" dan "INTERVAL" diisytiharkan sebagai VARCHAR. Ini boleh menyebabkan masalah jika anda melakukan pemecahan nombor pada lajur tersebut. (Saya tahu perkara seperti "BOARD_SN" sebenarnya bukan nombor.)

    balas
    0
  • Batalbalas