mysql - 优化大表上的 MySQL 查询

Question

我有一个 SQL 查询，在远程 MySQL 数据库上连接了 3 个表 - 其中两个表的大小约为 18GByte(STEP_RESULT 和 meas_numericlimit)，然后远程服务器创建一个 TMP 表，该表需要时间(大约 25 分钟)才能完成结尾我怎样才能优化这个查询？

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=latin;

和

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

感谢您的帮助

Answer 1

join_buffer_size 的值是多少？它不应超过 RAM 的 1% 左右。如果它大得多，您就会面临交换的风险，这对性能尤其不利。

EXPLAIN 中跳出了一件事:NULL | 48120004 说这是需要的:INDEX(STEP_ID);

但是，SELECT 和 EXPLAIN 似乎不匹配。请仔细检查。

uut_result 需要 INDEX(station_num, uut_name) -- 按此顺序；仅替换 (station_num)。

什么是 varchar(38)？ UUID 只有 36 个。IPv6 需要 39 个。

当数据太大而无法缓存时，UUID 的效率非常低。更多讨论:http://mysql.rjweb.org/doc.php/uuid

许多数据类型可以(应该)被缩小——这种缩小将减少 I/O，从而加快查询速度。如果您提供一些典型列的示例值，我可以提供更多建议。

例如，STATUS(通常)是少量不同的值。它可以表示为 1 字节的 ENUM 或 1 字节的 TINYINT；但也许您的应用程序有数百个不同的状态值？如果是这样，“正常化”可能是更好的答案。

DOUBLE 占用 8 个字节； FLOAT 仅占用 4 个字节，但将精度限制为仅约 7 个有效数字——也许这就足够了？

(大概您指的是latin1，而不是latin？)

还可以考虑切换到 InnoDB。

你有多少内存？表有多大(GB)？