我有一个 SQL 查询，其中 3 个表连接在一个远程 MySQL 数据库上

其中两个表的大小约为 15GB(STEP_RESULT 和 meas_numericlimit)

在发送数据之前，在服务器上创建一个TMP表，大约需要2.5小时才能结束

我不是服务器管理员，但我可以使用 MySql WorkBench 观察我的查询 此服务器是最新的，具有 64GB 的 RAM

如何优化这个查询？

谢谢

我的查询:

select 
t1.UUT_NAME, 
t1.STATION_NUM, 
t1.START_DATE_TIME, 
t3.LOW_LIMIT, 
t3.DATA,
t3.HIGH_LIMIT,
t3.UNITS,
t2b.STEP_NAME 
from 
meas_numericlimit t3 
    inner join STEP_RESULT t2a on t3.ID = t2a.STEP_ID
    inner join STEP_RESULT t2b on t2a.STEP_PARENT = t2b.STEP_ID
    inner join uut_result t1 on t2b.UUT_RESULT = t1.ID
where 
t1.UUT_NAME like 'Variable1-1%' and 
t1.STATION_NUM = 'variable2' and 
t2b.STEP_NAME = 'variable3' and 
t2b.STEP_TYPE = 'constant'

这里是 SHOW TABLES 和 EXPLAIN 输出查询:

+--------------------+
| Tables_in_spectrum |
+--------------------+
| cal_dates          |
| calibrage          |
| execution_time     |
| meas_numericlimit  |
| station_feature    |
| step_callexe       |
| step_graph         |
| step_msgjnl        |
| step_msgpopup      |
| step_passfail      |
| step_result        |
| step_seqcall       |
| step_stringvalue   |
| syst_event         |
| uptime             |
| users              |
| uut_result         |
+--------------------+

和

+----+-------------+-------+--------+-------------------------+--------

| id | select_type | table | type   | possible_keys           | key     
| 
+----+-------------+-------+--------+-------------------------+--------

|  1 | SIMPLE      | t2a   | ALL    | NULL                    | NULL    
|
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY                 | PRIMARY 
|
|  1 | SIMPLE      | t2b   | ALL    | NULL                    | NULL    
|
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,FK_uut_result_1 | PRIMARY 
|
+----+-------------+-------+--------+-------------------------+--------


---------+----------------------+----------- +-------------------------

key_len  | ref                  |    rows    | Extra                     
|
---------+----------------------+----------- +-------------------------
NULL    | NULL                 | 48120004   |                           
|
40      | spectrum.t2a.STEP_ID |        1   |                           
|
 NULL    | NULL                 | 48120004   | Using where; Using join
                                                             buffer  |
40      | spectrum.t2b.UUT_RESULT |       1 | Using where               
|
-------+----------------------+------------+---------------------------

这里显示创建表:

CREATE TABLE `uut_result` (
`ID` varchar(38) NOT NULL DEFAULT '', 
`STATION_NUM` varchar(255) DEFAULT NULL, 
`SOFTVER_ODTGEN` varchar(10) DEFAULT NULL, 
`HARDVER_ODTGEN` varchar(10) DEFAULT NULL, 
`NEXT_CAL_DATE` date DEFAULT NULL, 
`UUT_NAME` varchar(255) DEFAULT NULL, 
`UUT_SERIAL_NUMBER` varchar(255) DEFAULT NULL, 
`UUT_VERSION` varchar(255) DEFAULT NULL, 
`USER_LOGIN_NAME` varchar(255) DEFAULT NULL, 
`USER_LOGIN_LOGIN` varchar(255) NOT NULL DEFAULT '', 
`START_DATE_TIME` datetime DEFAULT NULL, 
`EXECUTION_TIME` float DEFAULT NULL, 
`UUT_STATUS` varchar(255) DEFAULT NULL, 
`UUT_ERROR_CODE` int(11) DEFAULT NULL, 
`UUT_ERROR_MESSAGE` varchar(1023) DEFAULT NULL, 
`PAT_NAME` varchar(255) NOT NULL DEFAULT '', 
`PAT_VERSION` varchar(10) NOT NULL DEFAULT '', 
`TEST_LEVEL` varchar(50) DEFAULT NULL, 
`INTERFACE_ID` int(10) unsigned NOT NULL DEFAULT '0', 
`EXECUTION_MODE` varchar(45) DEFAULT NULL, 
`LOOP_MODE` varchar(45) DEFAULT NULL, 
`STOP_ON_FAIL` tinyint(4) unsigned NOT NULL DEFAULT '0', 
`EXECUTION_COMMENT` text, 
PRIMARY KEY (`ID`), 
KEY `FK_uut_result_1` (`STATION_NUM`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

和

CREATE TABLE `meas_numericlimit` (
`ID` varchar(38) NOT NULL DEFAULT '', 
`STEP_RESULT` varchar(38) NOT NULL DEFAULT '', 
`NAME` varchar(255) DEFAULT NULL, 
`COMP_OPERATOR` varchar(30) DEFAULT NULL, 
`HIGH_LIMIT` double DEFAULT NULL, 
`LOW_LIMIT` double DEFAULT NULL, 
`UNITS` varchar(255) DEFAULT NULL, 
`DATA` double DEFAULT NULL, 
`STATUS` varchar(255) DEFAULT NULL, 
`FORMAT` varchar(15) DEFAULT NULL, 
`NANDATA` int(11) DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY `FK_meas_numericlimit_1` (`STEP_RESULT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

和

CREATE TABLE `step_result` (
`ID` varchar(38) NOT NULL DEFAULT '', 
`UUT_RESULT` varchar(38) NOT NULL DEFAULT '', 
`STEP_PARENT` varchar(38) DEFAULT NULL, 
`STEP_NAME` varchar(255) DEFAULT NULL, 
`STEP_ID` varchar(38) NOT NULL DEFAULT '', 
`STEP_TYPE` varchar(255) DEFAULT NULL, 
`STATUS` varchar(255) DEFAULT NULL, 
`REPORT_TEXT` text, 
`DIAG` text, 
`ERROR_OCCURRED` tinyint(1) NOT NULL DEFAULT '0', 
`ERROR_CODE` int(11) DEFAULT NULL, 
`ERROR_MESSAGE` varchar(1023) DEFAULT NULL, 
`MODULE_TIME` float DEFAULT NULL, 
`TOTAL_TIME` float DEFAULT NULL, 
`NUM_LOOPS` int(11) DEFAULT NULL, 
`NUM_PASSED` int(11) DEFAULT NULL, 
`NUM_FAILED` int(11) DEFAULT NULL, 
`ENDING_LOOP_INDEX` int(11) DEFAULT NULL, 
`LOOP_INDEX` int(11) DEFAULT NULL, 
`INTERACTIVE_EXENUM` int(11) DEFAULT NULL, 
`STEP_GROUP` varchar(30) DEFAULT NULL, 
`STEP_INDEX` int(11) DEFAULT NULL, 
`ORDER_NUMBER` int(11) DEFAULT NULL, 
PRIMARY KEY (`ID`), 
KEY `FK_step_result_1` (`UUT_RESULT`), 
KEY `IDX_step_parent` (`STEP_PARENT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin

最佳答案

首先要注意的是，仅仅因为您按一个顺序编写联接并不意味着它们实际上会按该顺序执行。 (查找声明性语言。)

出于这个原因，我会首先创建满足每个 where 子句的索引，然后是每个连接谓词，但在两个方向上......

从 WHERE 子句开始，所有复合/覆盖索引都应以...开头

• STEP_RESULT:(STEP_NAME，STEP_TYPE)
• uut_result : (STATION_NUM, UUT_NAME)

同时考虑到 JOIN 谓词，所有复合/覆盖索引都应该是...

• meas_numericlimit : (ID)
• STEP_RESULT:(STEP_NAME、STEP_TYPE、STEP_ID)
• STEP_RESULT:(STEP_NAME、STEP_TYPE、STEP_PARENT)
• STEP_RESULT:(STEP_NAME、STEP_TYPE、UUT_RESULT)
• uut_result : (STATION_NUM, UUT_NAME, ID)

在这 5 个索引中，您可能只会看到四个在使用中，因此您可能希望删除未使用的那个，或者保留它以防统计数据的变化改变解释计划。

这在一定程度上取决于您的数据的性质。例如，您可能只想将 uut_result 上的索引“反转”为 (ID, STATION_NUM, UUT_NAME)。在对数据行为一无所知的情况下，两种方法都值得一试。 (这同样适用于其他索引建议。)

关于mysql - 在 3 个大表上使用内部连接优化 SQL 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47421928/