MySQL多查询合并及LEFT JOIN性能优化问题咨询

API 探路者

2026-8-10

问题背景

原本用左连接（LEFT JOIN）编写的MySQL查询可返回所有数据，但处理1万+行数据时速度极慢；改为内连接（INNER JOIN）后查询速度提升，但丢失了存在空值或关联缺失的行。

需求

通过合并两个查询找回内连接丢失的行
寻求该查询的整体性能优化方案

相关代码与表结构

原LEFT JOIN查询

SELECT 
 DISTINCT a.RequestId, 
  a.*, str_to_date(RequestedTestDate, '%d-%b-%Y') AS 
  cRequestedTestDate, 
  str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
  name as Engineer, Cancelled 
 FROM ( 
  cert_request_cute a 
 LEFT JOIN tech_schedule b on a.RequestId = b.cute_id 
  ) 
 LEFT JOIN techs ts on b.tech_id = ts.id 
 LEFT JOIN sts c on ( 
  c.id = a.stsCustomer OR c.code = a.stsCustomer 
  ) 
 LEFT JOIN status_cute stat on (
  stat.RequestId = a.RequestId
  )" 
 . $swhere . $orderByQuery . $limitQuery;

修改后的INNER JOIN查询

SELECT 
 DISTINCT a.RequestId, 
  a.*, str_to_date(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate, 
  str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
  name as Engineer, Cancelled 
 FROM ( 
  cert_request_cute a 
  JOIN tech_schedule b on a.RequestId = b.cute_id 
  ) 
 JOIN techs ts on b.tech_id = ts.id 
 JOIN sts c on ( 
  c.id = a.stsCustomer OR c.code = a.stsCustomer 
  ) 
 JOIN status_cute stat on (
  stat.RequestId = a.RequestId
  )"
 . $swhere . $orderByQuery . $limitQuery;

尝试的错误合并查询

SELECT 
 DISTINCT a.RequestId,
 a.*,
 str_to_date(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate,
 str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate,
 name as Engineer,
 Cancelled  

FROM 
 (
  cert_request_cute a
  JOIN tech_schedule b on a.RequestId = b.cute_id
 )

WHERE b.cute_id, ts.id, a.stsCustomer, a.RequestId NOT IN (
 DISTINCT a.RequestId, 
  a.*, str_to_date(RequestedTestDate, '%d-%b-%Y') AS 
  cRequestedTestDate, 
  str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
  name as Engineer, Cancelled 
 FROM ( 
  cert_request_cute a 
  JOIN tech_schedule.b.cute_id b on a.RequestId.b.cute_id = 
  b.cute_id 
 ) 
 JOIN techs.ts.id ts on b.tech_id.ts.id = ts.id 
 JOIN sts c on ( 
  c.id = a.stsCustomer.astsCustomer OR c.code.c.code = 
  a.stsCustomer 
 ) 
 JOIN status_cute stat on (
  stat.RequestId.a.RequestId = a.RequestId
 ) 
)" 
 . $swhere . $orderByQuery . $limitQuery;

cert_request_cute表结构

CREATE TABLE `cert_request_cute` (
  `RequestId` int(10) NOT NULL COMMENT '请求ID',
  `stsCustomer` char(8) DEFAULT NULL COMMENT 'STS客户编码',
  `stsCustomerOtherCode` char(3) DEFAULT NULL COMMENT '其他STS客户编码',
  `stsCustomerOtherDescription` mediumtext DEFAULT NULL COMMENT '其他STS客户描述',
  `FirstName` mediumtext NOT NULL DEFAULT '' COMMENT '名字',
  `LastName` mediumtext NOT NULL DEFAULT '' COMMENT '姓氏',
  `Email` mediumtext NOT NULL DEFAULT '' COMMENT '邮箱',
  `Phone` mediumtext NOT NULL DEFAULT '' COMMENT '电话',
  `stsHandle` mediumtext NOT NULL COMMENT 'STS处理人',
  `CertificationRequest` mediumtext DEFAULT NULL COMMENT '认证请求内容',
  `CertificationRequestDetails` mediumtext NOT NULL COMMENT '认证请求详情',
  `RequestDescription` mediumtext DEFAULT NULL COMMENT '请求描述',
  `RequestedTestDate` mediumtext DEFAULT NULL COMMENT '请求测试日期',
  `RequestedBetaDate` mediumtext DEFAULT NULL COMMENT '请求Beta测试日期',
  `RequestedGlobalReleaseDate` mediumtext DEFAULT NULL COMMENT '请求全球发布日期',
  `BetaSiteXP` mediumtext NOT NULL COMMENT 'Beta站点XP',
  `BetaSite7` mediumtext NOT NULL COMMENT 'Beta站点7',
  `BetaSiteXP-2` mediumtext NOT NULL COMMENT 'Beta站点XP-2',
  `BetaSite7-2` mediumtext NOT NULL COMMENT 'Beta站点7-2',
  `FirstBetaSiteChoice` char(7) DEFAULT NULL COMMENT '第一Beta站点选择',
  `SecondBetaSiteChoice` char(7) DEFAULT NULL COMMENT '第二Beta站点选择',
  `ThirdBetaSiteChoice` char(7) DEFAULT NULL COMMENT '第三Beta站点选择',
  `ApplicationName` mediumtext DEFAULT NULL COMMENT '应用名称',
  `ApplicationVersion` mediumtext DEFAULT NULL COMMENT '应用版本',
  `SCutePlatform` mediumtext DEFAULT NULL COMMENT 'SCute平台',
  `ApplicationLocalServer` char(25) DEFAULT NULL COMMENT '应用本地服务器',
  `ApplicationLocalServerOther` mediumtext DEFAULT NULL COMMENT '其他应用本地服务器',
  `OSAPI` mediumtext DEFAULT NULL COMMENT '操作系统API',
  `OSAPIOther` mediumtext DEFAULT NULL COMMENT '其他操作系统API',
  `NewOSAPI` mediumtext DEFAULT NULL COMMENT '新操作系统API',
  `WANProtocol` mediumtext DEFAULT NULL COMMENT '广域网协议',
  `WANProtocolOther` mediumtext DEFAULT NULL COMMENT '其他广域网协议',
  `NewWANProtocol` mediumtext DEFAULT NULL COMMENT '新广域网协议',
  `Gateway` mediumtext DEFAULT NULL COMMENT '网关',
  `GatewayOther` mediumtext DEFAULT NULL COMMENT '其他网关',
  `SCuteLAN` mediumtext DEFAULT NULL COMMENT 'SCute局域网',
  `LANProtocol` mediumtext DEFAULT NULL COMMENT '局域网协议',
  `CommunicationCard` mediumtext DEFAULT NULL COMMENT '通信卡',
  `GatewayOS` mediumtext DEFAULT NULL COMMENT '网关操作系统',
  `RoutingProtocol` mediumtext DEFAULT NULL COMMENT '路由协议',
  `RegisteredAddressing` char(5) DEFAULT NULL COMMENT '注册寻址',
  `AdditionalInformation` mediumtext DEFAULT NULL COMMENT '附加信息',
  `MainFirstName` mediumtext DEFAULT NULL COMMENT '主要联系人名字',
  `MainLastName` mediumtext DEFAULT NULL COMMENT '主要联系人姓氏',
  `NetworkConfiguratorFirstName` mediumtext NOT NULL COMMENT '网络配置员名字',
  `NetworkConfiguratorLastName` mediumtext NOT NULL COMMENT '网络配置员姓氏',
  `NetworkConfiguratorEmail` mediumtext NOT NULL COMMENT '网络配置员邮箱',
  `NetworkConfiguratorPhone` mediumtext NOT NULL COMMENT '网络配置员电话',
  `OperationsManagerFirstName` mediumtext NOT NULL COMMENT '运营经理名字',
  `OperationsManagerLastName` mediumtext NOT NULL COMMENT '运营经理姓氏',
  `OperationsManagerEmail` mediumtext NOT NULL COMMENT '运营经理邮箱',
  `OperationsManagerPhone` mediumtext NOT NULL COMMENT '运营经理电话',
  `TechSupportFirstName` mediumtext NOT NULL COMMENT '技术支持名字',
  `TechSupportlastName` mediumtext NOT NULL COMMENT '技术支持姓氏',
  `TechSupportEmail` mediumtext NOT NULL COMMENT '技术支持邮箱',
  `TechSupportPhone` mediumtext NOT NULL COMMENT '技术支持电话',
  `stsManagerFirstName` mediumtext NOT NULL COMMENT 'STS经理名字',
  `stsManagerLastName` mediumtext NOT NULL COMMENT 'STS经理姓氏',
  `stsManagerEmail` mediumtext NOT NULL COMMENT 'STS经理邮箱',
  `stsManagerPhone` mediumtext NOT NULL COMMENT 'STS经理电话',
  `SAccountManagerFirstName` mediumtext NOT NULL DEFAULT '' COMMENT '客户经理名字',
  `SAccountManagerLastName` mediumtext NOT NULL DEFAULT '' COMMENT '客户经理姓氏',
  `SAccountManagerEmail` mediumtext NOT NULL DEFAULT '' COMMENT '客户经理邮箱',
  `SAccountManagerPhone` mediumtext NOT NULL DEFAULT '' COMMENT '客户经理电话',
  `PrimaryContactFirstName` mediumtext NOT NULL COMMENT '主要联系人名字',
  `PrimaryContactLastName` mediumtext NOT NULL COMMENT '主要联系人姓氏',
  `PrimaryContactEmail` mediumtext NOT NULL COMMENT '主要联系人邮箱',
  `PrimaryContactPhone` mediumtext NOT NULL COMMENT '主要联系人电话',
  `CompanyAddress` mediumtext NOT NULL COMMENT '公司地址',
  `CompanyWebsite` mediumtext NOT NULL COMMENT '公司网站',
  `RequestedDate` mediumtext NOT NULL COMMENT '请求提交日期',
  `ActualTestDate` mediumtext DEFAULT NULL COMMENT '实际测试日期',
  `TestDays` char(2) DEFAULT NULL COMMENT '测试天数',
  `PPMNumber` mediumtext DEFAULT NULL COMMENT 'PPM编号',
  `Comments` mediumtext DEFAULT NULL COMMENT '备注',
  `stsUsers` mediumtext NOT NULL COMMENT 'STS用户ID',
  `Cancelled` set('yes','no') NOT NULL DEFAULT 'no' COMMENT '是否取消',
  `TestingType` mediumtext NOT NULL COMMENT '测试类型',
  `has_url` set('Yes','No') NOT NULL COMMENT '是否有URL',
  `RequestType` mediumtext NOT NULL COMMENT '请求类型',
  `OperatingSystem` mediumtext NOT NULL COMMENT '操作系统',
  `complete` int(1) NOT NULL COMMENT '是否完成',
  `Price` mediumtext NOT NULL COMMENT '价格',
  `UpdatePrice` mediumtext NOT NULL DEFAULT '-' COMMENT '更新后价格',
  `BillingCompanyName` mediumtext NOT NULL COMMENT '账单公司名称',
  `BillingName` mediumtext NOT NULL COMMENT '账单联系人姓名',
  `BillingEmail` mediumtext NOT NULL COMMENT '账单联系人邮箱',
  `BillingPhone` mediumtext NOT NULL COMMENT '账单联系人电话',
  `ProductOwner` mediumtext NOT NULL COMMENT '产品负责人（仅XS）',
  `CostCenter` mediumtext NOT NULL COMMENT '成本中心（仅XS）',
  `BudgetCode` mediumtext NOT NULL COMMENT '预算编码（仅XS）',
  `Reminded` set('yes','no') NOT NULL COMMENT '是否已提醒',
  `has_ssl` set('Yes','No') DEFAULT NULL COMMENT '是否有SSL'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=0;

tech_schedule表结构

CREATE TABLE `tech_schedule` (
  `tech_id` int(10) NOT NULL COMMENT '技术人员ID',
  `b_date` mediumtext NOT NULL COMMENT '预约日期',
  `cute_id` int(10) NOT NULL COMMENT '关联cert_request_cute的RequestId',
  `cuss_id` int(10) NOT NULL COMMENT '关联CUSS表ID',
  `cuss_sbd_id` int(11) NOT NULL COMMENT '关联CUSS_SBD表ID',
  `book` set('yes','no') NOT NULL COMMENT '是否已预约',
  `cupps_id` int(6) NOT NULL COMMENT '关联CUPS表ID',
  `hardware_id` int(6) NOT NULL COMMENT '硬件ID',
  `pos_id` int(3) NOT NULL COMMENT 'POS ID',
  `realtime_id` int(10) NOT NULL COMMENT '实时数据ID',
  `sec_tech_id` int(10) NOT NULL COMMENT '备用技术人员ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

techs表结构

CREATE TABLE `techs` (
  `id` int(11) NOT NULL COMMENT '技术人员ID',
  `Type` text DEFAULT NULL COMMENT '人员类型',
  `name` text NOT NULL COMMENT '姓名',
  `email` text NOT NULL COMMENT '邮箱',
  `Phone` text DEFAULT NULL COMMENT '电话',
  `Title` text DEFAULT NULL COMMENT '职位',
  `active` enum('yes','no') NOT NULL DEFAULT 'yes' COMMENT '是否活跃'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

重构后查询（页面加载200ms）

SELECT
    a.*,
    STR_TO_DATE(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate,
    STR_TO_DATE(ActualTestDate, '%d-%b-%Y') AS cActualTestDate,
    ts.NAME AS Engineer

FROM ( SELECT *
        FROM cert_request_cute 
    )a
LEFT JOIN tech_schedule b ON a.RequestId = b.cute_id
LEFT JOIN techs ts ON b.tech_id = ts.id
LEFT JOIN airlines c on c.code = a.AirlineCustomer
LEFT JOIN cert_status_cute stat on stat.RequestId = a.RequestId
$swhere
GROUP BY a.RequestId
$orderByQuery
$limitQuery";;

解决方案

1. 合并查询找回内连接丢失的行

使用UNION ALL合并内连接结果与左连接中未匹配到关联数据的行，既保留内连接的性能优势，又补全缺失数据：

-- 内连接结果：匹配到所有关联数据的行
SELECT 
 DISTINCT a.RequestId, 
 a.*, 
 STR_TO_DATE(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate, 
 STR_TO_DATE(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
 ts.name as Engineer, 
 stat.Cancelled 
FROM cert_request_cute a 
JOIN tech_schedule b ON a.RequestId = b.cute_id 
JOIN techs ts ON b.tech_id = ts.id 
JOIN sts c ON (c.id = a.stsCustomer OR c.code = a.stsCustomer) 
JOIN status_cute stat ON stat.RequestId = a.RequestId
$swhere

UNION ALL

-- 左连接中未匹配到关联数据的行：找回缺失行
SELECT 
 a.RequestId, 
 a.*, 
 STR_TO_DATE(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate, 
 STR_TO_DATE(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
 NULL AS Engineer, 
 NULL AS Cancelled 
FROM cert_request_cute a 
LEFT JOIN tech_schedule b ON a.RequestId = b.cute_id 
LEFT JOIN techs ts ON b.tech_id = ts.id 
LEFT JOIN sts c ON (c.id = a.stsCustomer OR c.code = a.stsCustomer) 
LEFT JOIN status_cute stat ON stat.RequestId = a.RequestId
WHERE b.cute_id IS NULL 
   OR ts.id IS NULL 
   OR c.id IS NULL 
   OR stat.RequestId IS NULL
$swhere

$orderByQuery
$limitQuery;

说明：UNION ALL无需去重，性能优于UNION；第二个查询专门筛选左连接中未匹配到关联表的行，补充到结果集。

2. 整体性能优化方案

（1）添加必要索引

cert_request_cute：将RequestId设为主键，给stsCustomer加普通索引
tech_schedule：给cute_id、tech_id加普通索引，或创建联合索引(cute_id, tech_id)
techs：将id设为主键
sts：给id、code加普通索引
status_cute：给RequestId加普通索引

示例索引创建语句：

ALTER TABLE cert_request_cute ADD PRIMARY KEY (RequestId);
CREATE INDEX idx_cert_stsCustomer ON cert_request_cute(stsCustomer);

CREATE INDEX idx_techschedule_cuteid ON tech_schedule(cute_id);
CREATE INDEX idx_techschedule_techid ON tech_schedule(tech_id);

ALTER TABLE techs ADD PRIMARY KEY (id);

CREATE INDEX idx_sts_id ON sts(id);
CREATE INDEX idx_sts_code ON sts(code);

CREATE INDEX idx_statuscute_requestid ON status_cute(RequestId);

（2）优化查询逻辑

去掉不必要的子查询：重构后的查询中(SELECT * FROM cert_request_cute)a完全多余，直接写cert_request_cute a即可
用GROUP BY替代DISTINCT：GROUP BY a.RequestId比DISTINCT去重更高效
拆分OR连接条件：将sts c ON (c.id = a.stsCustomer OR c.code = a.stsCustomer)拆分为两个左连接，用COALESCE获取结果，避免索引失效：
```
LEFT JOIN sts c1 ON c1.id = a.stsCustomer
LEFT JOIN sts c2 ON c2.code = a.stsCustomer
SELECT COALESCE(c1.column, c2.column) AS target_column
```

（3）优化字段类型

将RequestedTestDate和ActualTestDate字段类型改为DATE，避免每次查询都执行STR_TO_DATE转换：

ALTER TABLE cert_request_cute MODIFY COLUMN RequestedTestDate DATE NULL;
ALTER TABLE cert_request_cute MODIFY COLUMN ActualTestDate DATE NULL;

（4）优化分页逻辑

确保分页时先排序再分页，结合索引使用，避免大数量下的全表扫描。

内容的提问来源于stack exchange，提问作者demo7up

MySQL多查询合并及LEFT JOIN性能优化问题咨询

需求 #

相关代码与表结构 #

原LEFT JOIN查询 #

修改后的INNER JOIN查询 #

尝试的错误合并查询 #

cert_request_cute表结构 #

tech_schedule表结构 #

techs表结构 #

重构后查询（页面加载200ms） #

解决方案 #

1. 合并查询找回内连接丢失的行 #

2. 整体性能优化方案 #