如何让慢速MySQL查询运行得更快

Question

我有这个查询，经常显示我"错误代码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', '', '', '', ''

show create table 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'

show create table 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'

show create table 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)'

Answer 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连接在一起。

EXPLAIN显示，至少其中一些简化是由优化器自动计算出来的。

。Serial_Number= b ' board_sn

我看到"utf8"one_answers";latin1"。如果任何查询在不同字符集(或排序)之间的VARCHAR上连接，则不会使用其他合适的索引。如果这是一个问题，我建议您使用ALTER .. CONVERT TO ..将latin1更改为utf8。

我看到名为"DURATION"one_answers";INTERVAL"被宣布为VARCHAR。如果对这样的列进行数值操作，这可能会造成麻烦。(我理解"board_sn"之类的东西;不是真正的数字)