请找到下面的查询,它需要更多的时间,加载&/tmp/
目录上的空间
SELECT DISTINCT pil.pilsellersku,
pil.pilname,
pbi.pbicode,
'',
group_concat(DISTINCT ppt.pptcmtrfnum SEPARATOR ','),
pbi.pbibrand,
'',
'',
'',
'',
pil.pilicmdisplaycolor,
pbi.pbidesc,
pbi.pbiattrlabel1,
pil.pilattrvalue1,
pbi.pbiattrlabel2,
pil.pilattrvalue2,
pbi.pbiattrlabel3,
pil.pilattrvalue3,
'',
pod.PODISBESTSELLER,
pod.PODISNEWARRIVAL,
pod.PODISCHOSEN,
pit.pitzoomimage,
pit.pitimage,
pil.pilimage,
pit.pitthumbnailimage,
pit.pitthumbnailimage,
pil.pilmrp,
pod.PODMARGIN,
pod.PODRETCOST,
pbi.pbidiscvalue,
pbi.pbidiscinvalue,
pil.pilvat,
pil.pilstockqty,
pil.PILASRTGRP,
sot.sotsomrfnum,
group_concat(DISTINCT ppm.PPMPMTRFNUM SEPARATOR ','),
ccg.ccgrfnum,
pil.pilactive,
pbi.pbiisgiftwrap,
pbi.pbigiftwrapcharge,
pbi.pbiispreffdate,
pbi.pbissirfnum,
pod.PODSTSL,
'',
'',
pod.PODPVC,
pod.PODWSPRICE,
pod.PODODV,
pod.PODSEQ,
pod.PODSTCODE,
pod.PODTAXATTRI,
pod.PODPRODUNIT,
pod.PODSTDCOST,
pod.PODMATCODE,
pod.PODWEEK,
pod.PODYEAR,
pod.PODDYRWK,
pod.PODISBESTBUY,
pbi.isonline,
pbi.pbiseqnum,
scm.scmcode
FROM Ismpbi pbi
LEFT JOIN ISMPIL pil
ON pbi.pbirfnum = pil.pilpbirfnum
LEFT JOIN ISMPIT pit
ON pbi.pbirfnum = pit.pitpbirfnum
AND pit.pitviewseq = 1
LEFT JOIN ISMSOT sot
ON sot.sotpbirfnum = pbi.pbirfnum
LEFT JOIN OTHPOD pod
ON pod.podpbirfnum = pbi.pbirfnum
LEFT JOIN ISMPPM ppm
ON ppm.ppmpbirfnum = pbi.pbirfnum
LEFT JOIN ISMPPT ppt
ON ppt.pptpbirfnum = pbi.pbirfnum
LEFT JOIN ISMCCG ccg
ON pbi.pbiccgrfnum = ccg.ccgrfnum
LEFT JOIN OCTSCM scm
ON pbi.pbiscmrfnum = scm.scmrfnum
WHERE pbi.PBIBRAND = 35
GROUP BY pil.pilsellersku;
在上面的查询中,所有联接的列&其中对条件列(即pbi.PBIBRAND
和pit.pitviewseq
(进行索引。
并按如下说明陈述。
+----+-------------+-------+--------+-------------------------------------------+--------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------+--------------+---------+-------------------------+------+----------------------------------------------+
| 1 | SIMPLE | pbi | ref | IDX_PBIBRAND | IDX_PBIBRAND | 9 | const | 4420 | Usingwhere; Using temporary; Using filesort |
| 1 | SIMPLE | pil | ref | IDX_PILPBI | IDX_PILPBI | 8 | apparel.pbi.PBIRFNUM | 3 | |
| 1 | SIMPLE | pit | ref | IDX_PITPBI | IDX_PITPBI | 8 | apparel.pbi.PBIRFNUM | 9 | |
| 1 | SIMPLE | sot | ref | PRIMARY,SOTPBIRFNUM,IDX_SOTPBI | PRIMARY | 8 | apparel.pbi.PBIRFNUM | 1 | Using index |
| 1 | SIMPLE | pod | ref | PODPBIRFNUM,FK_POD_PBI,FK8B908042468F70B6 | PODPBIRFNUM | 9 | apparel.pbi.PBIRFNUM | 1 | |
| 1 | SIMPLE | ppm | ref | IDX_PPMPBI | IDX_PPMPBI | 8 | apparel.pbi.PBIRFNUM | 1 | |
| 1 | SIMPLE | ppt | ref | PRIMARY,PPTPBIRFNUM,IDX_PPTPBI | PRIMARY | 8 | apparel.pbi.PBIRFNUM | 1 | Using index |
| 1 | SIMPLE | ccg | eq_ref | PRIMARY,CCGRFNUM | PRIMARY | 8 | apparel.pbi.PBICCGRFNUM | 1 | Using index |
| 1 | SIMPLE | scm | eq_ref | PRIMARY,SCMRFNUM | PRIMARY | 8 | apparel.pbi.PBISCMRFNUM | 1 | |
+----+-------------+-------+--------+-------------------------------------------+--------------+---------+-------------------------+------+----------------------------------------------+
9 rows in set (0.00 sec)
上述查询耗时近17分钟。服务器上的负载正在跨越15,磁盘级别上的/tmp/
方向正在消耗21GB。
有谁能建议优化&修改查询以获得结果。
您的整体查询看起来简单而合理。。。ISMPBI的主表都与其他表相连,这对我来说意味着它们更多的是辅助表/查找表。
为了提供帮助,我愿意以下。。。将"STRIGHT_JOIN"关键字添加到您选择的中
选择direct_JOIN DISTINCT。。。。
对于ISMPBI表,请确保它在(PBIBRAND(上有索引
确保所有其他表在其各自的"JOIN"列名上都有索引。。。对于ISMPIT表,在(PITBIRFNUM,PITWIEWSEQ(上有索引——因为您有一个用于PITVIEWSEQ 的AND子句
EDIT--预查询测试选项。。
您是否可以将其作为预查询运行,以查看IT相对于每个表的联接的性能。。。
SELECT
pbi.pbirfnum,
pil.pilsellersku,
group_concat(DISTINCT ppt.pptcmtrfnum SEPARATOR ',') as PPT_Values,
group_concat(DISTINCT ppm.PPMPMTRFNUM SEPARATOR ',') as PPM_Values
FROM
Ismpbi pbi
LEFT JOIN ISMPIL pil
ON pbi.pbirfnum = pil.pilpbirfnum
LEFT JOIN ISMPPM ppm
ON pbi.pbirfnum = ppm.ppmpbirfnum
LEFT JOIN ISMPPT ppt
ON pbi.pbirfnum = ppt.pptpbirfnum
WHERE
pbi.PBIBRAND = 35
GROUP BY
pbi.pbirfnum,
pil.pilsellersku