我突然在MariaDB (mysql)中遇到了一个错误
错误代码:5. 内存不足(需要 3758098168 字节)
这是我的配置文件:
[mysqld]
max_connection=60
datadir=/data/mysql
innodb_buffer_pool_size=6G
skip-external-locking
query_cache_size= 256M
key_buffer_size=20M
table_open_cache=400
sort_buffer_size=4M
read_buffer_size=20M
join_buffer_size=30M
查询包含大量左外联接...所以我认为一定是必须增加的join_buffer_size。我把它从3M改成了30M,但它仍然崩溃了。
这是查询:
SELECT Periods.Periods, Result.count as `Result`, Studies.count as `Studies`,
MouthStudies.Mouthcount as `MOU`,
LiverStudies.LiverCount as `LIV`,
BreastStudies.BreastCount as `BRE`,
BladderStudies.BladderCount as `BLA`,
CervixStudies.CervixCount as `CER`,
ProstateStudies.ProstateCount as `PRO`,
ColorectalStudies.ColorectalCount + ColonStudies.ColonCount as COL,
LungStudies.LungCount as LUN,
KidneyStudies.KidneyCount as KID,
EndometrianStudies.EndometrianCount as `END`,
GallbladderStudies.GallbladderCount as `GAL`,
NasopharynxStudies.NasopharynxCount as `NAS`,
ObesityStudies.ObesityCount as `OBE`,
OesophagealStudies.OesophagealCount as `OES`,
OvarianStudies.OvarianCount as OVA,
PancreasStudies.PancreasCount as PAN,
SkinStudies.SkinCount as SKI,
StomachStudies. StomachCount as STM,
PolypesStudies.PolypesCount as POL
from
(select DISTINCT(DATE_FORMAT(allDates.`DATETIME`,'%M - %Y')) as Periods
from
(select `DATETIME`
from tblresults
union all
select `DATETIME`
from tblstudydetailsmain) as allDates
order by `DATETIME` DESC LIMIT 3) as Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods, count(ID) as `count` FROM central_log.tblresults
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as Result
ON Periods.periods=Result.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods, count(ID) as `count` FROM central_log.tblstudydetailsmain
where USER LIKE concat('','%') and USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as Studies
ON Periods.periods=Studies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'mou%' then 1 else 0 END) as `Mouthcount`
FROM central_log.tblstudydetailsmain
where USER LIKE concat('','%') and USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as MouthStudies
ON Periods.periods=MouthStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'LIV%' then 1 else 0 END) as `Livercount`
FROM central_log.tblstudydetailsmain
where USER LIKE concat('','%') and USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as LiverStudies
ON Periods.periods=LiverStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'BRE%' then 1 else 0 END) as `Breastcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as BreastStudies
ON Periods.periods=BreastStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'BLA%' then 1 else 0 END) as `Bladdercount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as BladderStudies
ON Periods.periods=BladderStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'CER%' then 1 else 0 END) as `Cervixcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as CervixStudies
ON Periods.periods=CervixStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'PRO%' then 1 else 0 END) as `Prostatecount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ProstateStudies
ON Periods.periods=ProstateStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'COL%' then 1 else 0 END) as `Coloncount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ColonStudies
ON Periods.periods=ColonStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'LUN%' then 1 else 0 END) as `Lungcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as LungStudies
ON Periods.periods=LungStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'KID%' then 1 else 0 END) as `Kidneycount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as KidneyStudies
ON Periods.periods=KidneyStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'END%' then 1 else 0 END) as `Endometriancount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as EndometrianStudies
ON Periods.periods=EndometrianStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'CRC%' then 1 else 0 END) as `Colorectalcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ColorectalStudies
ON Periods.periods=ColorectalStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'GAL%' then 1 else 0 END) as `Gallbladdercount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as GallbladderStudies
ON Periods.periods=GallbladderStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'NAS%' then 1 else 0 END) as `Nasopharynxcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as NasopharynxStudies
ON Periods.periods=NasopharynxStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'OBE%' then 1 else 0 END) as `Obesitycount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ObesityStudies
ON Periods.periods=ObesityStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'oes%' then 1 else 0 END) as `Oesophagealcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as OesophagealStudies
ON Periods.periods=OesophagealStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'OVA%' then 1 else 0 END) as `Ovariancount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as OvarianStudies
ON Periods.periods=OvarianStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'PAN%' then 1 else 0 END) as `Pancreascount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as PancreasStudies
ON Periods.periods=PancreasStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'SKI%' then 1 else 0 END) as `Skincount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as SkinStudies
ON Periods.periods=SkinStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'STM%' then 1 else 0 END) as `Stomachcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as StomachStudies
ON Periods.periods= StomachStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'POL%' then 1 else 0 END) as `Polypescount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as PolypesStudies
ON Periods.periods= PolypesStudies.Periods;
基本上,它是主数据库中按癌症类型添加/删除/更新的所有研究的日志数据库。它在这里用于关注团队的生产。表中总共只有 12,457 行,直到昨天查询都没有返回任何错误。
如果周围有一些MySql-MariaDB性能大师,请帮助我解决这个问题!
克里斯托夫
考虑将所有 JOIN 替换为...
SELECT
DATE_FORMAT(Periods`DATETIME`, '%M - %Y') AS Periods, -- see note below
Result.count as `Result`,
Studies.count as `Studies`,
( SELECT sum(WCRF_CODE LIKE 'BLA%')
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION = 'added'
AND `DATETIME` >= Periods.periods
AND `DATETIME` < Periods.periods + INTERVAL 1 MONTH -- note
) AS 'BLA', -- a "correlated subquery"
( SELECT ...
) AS 'MOU',
...
FROM ( SELECT DATE(`DATETIME`) -- See note below
... ) AS Periods
注意。。。 我想使用月份开始值,例如"2015-06-01"而不是您的DATETIME
或Jun - 2015
。 相反,在最后一刻构建它。 DATETIME < Periods.periods + INTERVAL 1 MONTH
也需要月份开始。 因此,如果DATETIME
并不总是一个月的开始,则需要进行一些进一步的编辑。
我的查询是废品,重写它可以解决这个问题,而不是使用许多连接,我用(联合所有)制作了一个平面数据集并使用 CASE WHEN 来计算每个类别中的研究......