MySQL View 已创建 - 对它运行查询需要几分钟

Question

我已将以下查询转换为 View 表名称 salesboard:

select 
distinct(p.paymentID) AS paymentID,
j.jobID AS jobID,j.jobNumber AS jobNumber,j.jobType AS jobType, j.countType AS countType, j.countID AS countID,
ui.userID AS salesRep,
(
    case 
        when (j.idType = 'dealership') then d.dealershipName 
        when (j.idType = 'Group') then g.groupName 
        when (j.idType = 'Agency') then a.agencyName 
    end
) AS dealershipName,
(
    case 
        when (p.manualTimestamp <> '0000-00-00 00:00:00') then p.manualTimestamp 
        when (p.manualTimestamp = '0000-00-00 00:00:00') then from_unixtime(p.timestamp) 
    end
) AS checkTS,
p.paymentAmount AS paymentAmount,
po.estimatedMailArrival AS estimatedMailArrival 
from jobs j 
left join smdealershipjoins smdj on j.dealershipID = smdj.dealership 
left join smgroupjoins smgj on j.dealershipID = smgj.groupID 
left join smagencyjoins smaj on j.dealershipID = smaj.agencyID 
join userinfo ui on 
(
    case 
        when (j.idType = 'dealership') then (smdj.sm = ui.userID) 
        when (j.idType = 'Group') then (smgj.sm = ui.userID) 
        when (j.idType = 'Agency') then (smaj.sm = ui.userID) 
    end
) 
left join dealerships d on smdj.dealership = d.dealershipID 
left join dealershipgroups g on smgj.groupID = g.groupID 
left join agencies a on smaj.agencyID = a.agencyID 
join payments p on j.jobID = p.jobID 
left join purchaseorders po on j.jobID = po.jobID 
order by 
(
    case 
        when (p.manualTimestamp <> '0000-00-00 00:00:00') then p.manualTimestamp 
        when (p.manualTimestamp = '0000-00-00 00:00:00') then from_unixtime(p.timestamp) 
    end
) desc

View 已保存并返回预期结果。但是，每当我在新 View 表上运行即使是最简单的查询时，查询也需要几分钟才能运行。我四处搜寻，看看是否有什么办法可以解决这个问题 - 但我却空手而归:(

有什么可以做的吗？我绞尽脑汁，想寻求一些帮助。

Answer 1

如果任何结果集的大小超过了 innodb_buffer_pool_size 的大小，则由于 I/O 分页，您的查询将花费大量时间。在您的情况下，由于 ORDER BY 子句，from 语句中包含的每个表都必须加载到内存中。

通过使用分区减少提取的数据量或增加缓冲池的大小。