MySQL 8.4多表查询排序性能低下，寻求优化建议

我已花费数日优化MySQL 8.4数据库的查询性能，但以下两种查询仍存在性能问题（我均已尝试）：

SELECT sql_no_cache * FROM infracao.multas m
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
    AND m.Renavam IN (SELECT Renavam FROM veiculo.VeiculoCliente vc WHERE vc.Renavam = m.Renavam AND vc.Cliente = 'CLT00188')
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

-- or

SELECT sql_no_cache * FROM infracao.multas m
JOIN veiculo.VeiculoCliente vc
    ON vc.Renavam = m.Renavam
    AND vc.Cliente = 'CLT00188'
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

相关表的索引结构如下：

CREATE TABLE `VeiculoCliente` (
   `Renavam` varchar(11) NOT NULL DEFAULT '',
   `Cliente` varchar(8) NOT NULL DEFAULT '',
   `DtInsercao` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`Cliente`,`Renavam`),
   KEY `Idx_Renavam` (`Renavam`),
   KEY `Idx_Cliente` (`Cliente`) /*!80000 INVISIBLE */
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 190k rows

CREATE TABLE `multasDataBase` (
   `MultaId` char(36) NOT NULL DEFAULT '',
   `DtBase` date NOT NULL,
[...]
   PRIMARY KEY (`DtBase`,`MultaId`),
   KEY `DtBaseIdx` (`DtBase`) /*!80000 INVISIBLE */,
   KEY `Fk_infracao_infracaoDtBase_idx` (`MultaId`),
   KEY `multasDataBase_Id_DtBaseIdx` (`DtBase` DESC,`MultaId`),
   CONSTRAINT `Fk_infracao_infracaoDtBase` FOREIGN KEY (`MultaId`) REFERENCES `multas` (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 2.4 milion rows

CREATE TABLE `multas` (
   `Id` char(36) NOT NULL DEFAULT '',
[...]
   `CollectedAt` datetime NOT NULL,
[...]
   PRIMARY KEY (`Id`),
   UNIQUE KEY `AitDetranGuia` (`AitDetran`,`Guia`),
   UNIQUE KEY `unique_InfracaoKeySne` (`InfracaoKeySne`),
   KEY `Renainf` (`Renainf`),
   KEY `RenavamIdx` (`Renavam`),
   KEY `InsertAtIdx` (`InsertedAtUtc`),
   KEY `NormalizedAitIdx` (`NormalizedAit`),
   KEY `AitSne` (`AitSne`),
   KEY `CollectedAtIdxDesc` (`CollectedAt`),
   KEY `multasOrgaoIdx` (`CodigoOrgao`),
   KEY `multa_Id_CollectedAtIdxDesc` (`Id`,`CollectedAt`),
   KEY `query_clientMultas_collectedat_idx` (`Renavam`,`CollectedAt` DESC, `Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 708k rows

执行计划如下：

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,vc,NULL,ref,"PRIMARY,Idx_Renavam",PRIMARY,10,const,3126,100.00,"Using index; Using temporary; Using filesort"
1,SIMPLE,m,NULL,ref,"PRIMARY,RenavamIdx,multa_Id_CollectedAtIdxDesc,query_clientMultas_collectedat_idx",RenavamIdx,13,veiculo.vc.Renavam,5,100.00,NULL
1,SIMPLE,mdb,NULL,eq_ref,"PRIMARY,Fk_infracao_infracaoDtBase_idx,multasDataBase_Id_DtBaseIdx",PRIMARY,39,"const,infracao.m.Id",1,100.00,NULL

数据量最大的表为infracao.multas，且查询需按CollectedAt排序。从执行计划可见，查询出现Using temporary; Using filesort问题，导致性能大幅下降，恳请各位提供性能优化方案。

1. 强制利用现有索引消除排序开销 #

你已创建的query_clientMultas_collectedat_idx索引（Renavam,CollectedAt DESC, Id）完美匹配关联条件和排序条件，但优化器当前未选择它。通过FORCE INDEX强制使用该索引，可避免临时表和文件排序：

SELECT sql_no_cache m.*, mdb.*
FROM infracao.multas m FORCE INDEX(query_clientMultas_collectedat_idx)
JOIN veiculo.VeiculoCliente vc
    ON vc.Renavam = m.Renavam
    AND vc.Cliente = 'CLT00188'
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

该索引让MySQL直接按排序顺序读取multas表数据，无需后续排序，同时覆盖关联multasDataBase所需的Id字段，提升关联效率。

2. 调整查询顺序，优先扫描有序数据 #

将multas表作为驱动表，结合EXISTS子句过滤客户车辆，引导优化器优先按排序条件扫描数据：

SELECT sql_no_cache m.*, mdb.*
FROM infracao.multas m
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
WHERE EXISTS (
    SELECT 1 
    FROM veiculo.VeiculoCliente vc 
    WHERE vc.Renavam = m.Renavam 
      AND vc.Cliente = 'CLT00188'
)
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

这种写法减少了后续排序的数据集大小，降低临时表和文件排序的开销。

3. 替换偏移分页为游标分页 #

LIMIT 1000,1000需要扫描前2000条数据并丢弃前1000条，效率极低。改用基于前一页最后一条记录的CollectedAt和Id进行游标分页：

-- 假设上一页最后一条记录的CollectedAt为'2025-01-01 10:00:00'，Id为'xxxx-xxxx-xxxx'
SELECT sql_no_cache m.*, mdb.*
FROM infracao.multas m
JOIN veiculo.VeiculoCliente vc
    ON vc.Renavam = m.Renavam
    AND vc.Cliente = 'CLT00188'
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
WHERE (m.CollectedAt < '2025-01-01 10:00:00') 
   OR (m.CollectedAt = '2025-01-01 10:00:00' AND m.Id < 'xxxx-xxxx-xxxx')
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000;

这种方式直接利用索引定位起始位置，避免扫描无关数据，大幅提升分页效率。

4. 减少数据传输开销，避免SELECT * #

SELECT *会返回所有字段，增加内存占用和网络传输量。明确指定所需字段，提升查询速度的同时，让索引更容易实现覆盖扫描：

SELECT sql_no_cache m.Id, m.CollectedAt, m.Renavam, mdb.DtBase -- 替换为实际需要的字段
FROM infracao.multas m FORCE INDEX(query_clientMultas_collectedat_idx)
JOIN veiculo.VeiculoCliente vc
    ON vc.Renavam = m.Renavam
    AND vc.Cliente = 'CLT00188'
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

5. 更新表统计信息，帮助优化器选择最优计划 #

定期执行ANALYZE TABLE更新表的统计信息，确保MySQL优化器基于准确的数据分布选择最优执行计划：

ANALYZE TABLE infracao.multas;
ANALYZE TABLE infracao.multasDataBase;
ANALYZE TABLE veiculo.VeiculoCliente;

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