查询非常慢--如何优化
查询非常慢--如何优化
提问于 2017-10-16 14:21:06
我有一个查询，需要7分钟才能执行。我的问题是，我如何能够优化这个查询？
EXPLAIN语句显示了以下两个瓶颈：
- 表
fmonatsstatistik有1721051条记录 - 表
filialartikel有268460条记录
Extra是说使用sort_union，使用where，使用临时的，使用文件，我认为这是非常糟糕的。
索引filalartikel：
+---------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| filialartikel | 0 | PRIMARY | 1 | FilialNr | A | 18 | NULL | NULL | | BTREE | | |
| filialartikel | 0 | PRIMARY | 2 | ArtikelNr | A | 256176 | NULL | NULL | | BTREE | | |
| filialartikel | 1 | ArtikelName | 1 | ArtikelName | A | 15029 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | ArtikelZusatz1 | 1 | ArtikelZusatz1 | A | 392 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | ArtikelZusatz2 | 1 | ArtikelZusatz2 | A | 6 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | ArtikelZusatz3 | 1 | ArtikelZusatz3 | A | 44 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | Matchcode | 1 | Matchcode | A | 12443 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | RegalStandort | 1 | RegalStandort | A | 1 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | EinkaugMenge | 1 | EinkaufMenge | A | 232 | NULL | NULL | YES | BTREE | | |
| filialartikel | 1 | EinkaufSumme | 1 | EinkaufSumme | A | 1012 | NULL | NULL | YES | BTREE | | |
+---------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+索引fmonatsstatistik：
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fmonatsstatistik | 0 | PRIMARY | 1 | Jahr | A | 432 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 0 | PRIMARY | 2 | Monat | A | 3462 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 0 | PRIMARY | 3 | ArtikelNr | A | 138994 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 0 | PRIMARY | 4 | FilialNr | A | 1257526 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 1 | ArtikelNr | 1 | ArtikelNr | A | 14366 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 1 | Woche | 1 | Monat | A | 11 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 1 | Umsatz | 1 | Umsatz | A | 851 | NULL | NULL | YES | BTREE | | |
| fmonatsstatistik | 1 | Betrag | 1 | Betrag | A | 4757 | NULL | NULL | YES | BTREE | | |
| fmonatsstatistik | 1 | MWStBetrag | 1 | MWStBetrag | A | 7669 | NULL | NULL | YES | BTREE | | |
| fmonatsstatistik | 1 | GMEKPreis | 1 | GMEKPreis | A | 2321 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 1 | Monat | 1 | Monat | A | 11 | NULL | NULL | | BTREE | | |
| fmonatsstatistik | 1 | GMVKPreis | 1 | GMVKPreis | A | 2640 | NULL | NULL | | BTREE | | |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+查询：
SELECT fmonatsstatistik.artikelnr,
filialartikel.artikelname,
artikel.artikelnummer,
filialartikel.artikelzusatz1,
filialartikel.artikelzusatz2,
filialartikel.artikelzusatz3,
filialen.filialname,
(SELECT Concat_ws(' ', name1, name2)
FROM adressen
WHERE adressnr = (SELECT adressnr
FROM artikellieferanten
WHERE artikellieferanten.artikelnr =
filialartikel.artikelnr
AND artikellieferanten.filialnr =
filialartikel.filialnr
ORDER BY bevorzugt
LIMIT 1)) AS
Lieferant,
(SELECT Sum(filialartikel2.istbestandregal
+ filialartikel2.istbestandlager)
FROM filialartikel AS filialartikel2
WHERE filialartikel.artikelnr = filialartikel2.artikelnr
AND ( filialartikel2.filialnr = 0
OR '0' = '0' )) AS
Bestand,
Sum(fmonatsstatistik.umsatz) AS
MengeSumme,
Sum(fmonatsstatistik.betrag) AS
BetragSumme,
Sum(fmonatsstatistik.betrag - fmonatsstatistik.mwstbetrag) AS
MWStExclSumme,
(SELECT warengruppenname
FROM warengruppen
WHERE warengruppen.warengruppennr = artikel.warengruppennr) AS
WgName,
(SELECT warenobergruppenname
FROM warenobergruppen
WHERE warenobergruppen.warenobergruppennr = (SELECT warenobergruppennr
FROM warengruppen
WHERE
warengruppen.warengruppennr = artikel.warengruppennr)) AS
WogName,
filialartikel.regalstandort AS
RegalPlatz,
Sum(fmonatsstatistik.betrag - fmonatsstatistik.mwstbetrag) -
Sum(fmonatsstatistik.umsatz) * ( fmonatsstatistik.gmekpreis + 0 ) * (
(SELECT
waehrungkurs
FROM
waehrungen
WHERE
waehrungnr = (SELECT landw�hrung
FROM land
WHERE landnr = (SELECT landnr
FROM filialen
WHERE
filialen.filial_id =
fmonatsstatistik.filialnr))) / (SELECT waehrungkurs
FROM waehrungen
WHERE waehrungnr = (SELECT waehrungnr
FROM adressen
WHERE adressnr = (SELECT adressnr
FROM artikellieferanten
WHERE artikellieferanten.artikelnr =
filialartikel.artikelnr
AND
artikellieferanten.filialnr =
filialartikel.filialnr
LIMIT 1))) + 0 ) AS
Rohertrag,
Sum(fmonatsstatistik.betrag - fmonatsstatistik.mwstbetrag) -
Sum(fmonatsstatistik.umsatz) * (
IF(filialartikel.einkaufmenge<>0, filialartikel.einkaufsumme/filialartikel.einkaufmenge, fmonatsstatistik.gmekpreis)
+ 0 ) * ( (SELECT waehrungkurs
FROM waehrungen
WHERE waehrungnr = (SELECT landw�hrung
FROM land
WHERE landnr = (SELECT landnr
FROM filialen
WHERE
filialen.filial_id =
fmonatsstatistik.filialnr))) / (SELECT waehrungkurs
FROM waehrungen
WHERE waehrungnr = (SELECT waehrungnr
FROM adressen
WHERE
adressnr = (SELECT adressnr
FROM artikellieferanten
WHERE
artikellieferanten.artikelnr =
filialartikel.artikelnr
AND
artikellieferanten.filialnr =
filialartikel.filialnr
LIMIT 1))) + 0 ) AS
Rohertrag3
FROM fmonatsstatistik
INNER JOIN filialen
ON fmonatsstatistik.filialnr = filialen.filial_id
INNER JOIN filialartikel
ON filialartikel.artikelnr = fmonatsstatistik.artikelnr
AND filialartikel.filialnr = fmonatsstatistik.filialnr
INNER JOIN artikel
ON artikel.artikelnr = fmonatsstatistik.artikelnr
WHERE ( fmonatsstatistik.filialnr = 0
OR '0' = '0' )
AND ( artikel.warengruppennr = (SELECT fvalue
FROM query_params
WHERE fname = 'WarengruppenNr'
LIMIT 1)
OR (SELECT fvalue
FROM query_params
WHERE fname = 'WarengruppenNr'
LIMIT 1) = 0 )
AND ( artikel.sortimentnr = (SELECT fvalue
FROM query_params
WHERE fname = 'SortimentNr'
LIMIT 1)
OR (SELECT fvalue
FROM query_params
WHERE fname = 'SortimentNr'
LIMIT 1) = 0 )
AND ( CONVERT(artikel.artikelnummer, CHAR) =
(SELECT fvalue
FROM query_params
WHERE fname = 'Artikelnummer'
LIMIT 1)
OR (SELECT fvalue
FROM query_params
WHERE fname = 'Artikelnummer'
LIMIT 1) = '' )
AND ( (SELECT warenobergruppennr
FROM warengruppen
WHERE warengruppen.warengruppennr = artikel.warengruppennr) =
(SELECT fvalue
FROM
query_params
WHERE
fname
=
'WarenObergruppenNr'
LIMIT 1)
OR (SELECT fvalue
FROM query_params
WHERE fname = 'WarenObergruppenNr'
LIMIT 1) = 0 )
AND fmonatsstatistik.jahr * 12 + fmonatsstatistik.monat >= 24193
AND fmonatsstatistik.jahr * 12 + fmonatsstatistik.monat <= 24204
AND ( CONVERT(filialartikel.regalstandort, CHAR) =
(SELECT fvalue
FROM query_params
WHERE fname = 'RegalStandort'
LIMIT 1)
OR (SELECT fvalue
FROM query_params
WHERE fname = 'RegalStandort'
LIMIT 1) = '' )
AND ( CONVERT(filialartikel.artikelname, CHAR) LIKE Concat('%',
(SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelName'
LIMIT 1), '%')
OR (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelName'
LIMIT 1) = '' )
AND ( CONVERT(filialartikel.artikelzusatz1, CHAR) LIKE
Concat('%', (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelZusatz1'
LIMIT 1), '%')
OR (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelZusatz1'
LIMIT 1) = '' )
AND ( CONVERT(filialartikel.artikelzusatz2, CHAR) LIKE
Concat('%', (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelZusatz2'
LIMIT 1), '%')
OR (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelZusatz2'
LIMIT 1) = '' )
AND ( CONVERT(filialartikel.artikelzusatz3, CHAR) LIKE
Concat('%', (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelZusatz3'
LIMIT 1), '%')
OR (SELECT fvalue
FROM query_params
WHERE fname = 'ArtikelZusatz3'
LIMIT 1) = '' )
AND ( NOT Isnull((SELECT artikelnr
FROM artikellieferanten
WHERE adressnr = (SELECT fvalue
FROM query_params
WHERE fname = 'Lieferant'
LIMIT 1)
AND
artikellieferanten.artikelnr = artikel.artikelnr
AND artikellieferanten.filialnr =
fmonatsstatistik.filialnr
ORDER BY bevorzugt
LIMIT 1))
OR (SELECT fvalue
FROM query_params
WHERE fname = 'Lieferant'
LIMIT 1) = '' )
AND ( umsatz != 0
OR betrag != 0 )
GROUP BY fmonatsstatistik.artikelnr,
filialartikel.artikelname,
Coalesce(filialartikel.artikelzusatz1, ''),
Coalesce(filialartikel.artikelzusatz2, ''),
Coalesce(filialartikel.artikelzusatz3, '')
ORDER BY wogname ASC,
wgname ASC,
artikelname 回答 1
Database Administration用户
发布于 2017-10-16 16:21:06
甚至不要考虑这种构建查询的方式。是的，通过查找一个配置值来禁用各种ANDs是明智的；但是它非常慢。
相反，获取配置值，然后构建查询。在此过程中，省略任何您可以看到的将计算为AND的TRUE：
AND ( ( ... ) OR ( SELECT fvalue ... ) = '' )也就是说，获取query_params以决定该SELECT的值，然后将其包含在WHERE (如果!='')中，或者包含它：
AND ( ... )在修正了其中的大部分内容之后，再深入研究SELECT子句。例如，这很昂贵；尝试找到一种不涉及嵌套SELECTs的方法：
( SELECT waehrungkurs
FROM waehrungen
WHERE waehrungnr =
( SELECT landw�hrung
FROM land
WHERE landnr =
( SELECT landnr
FROM filialen
WHERE filialen.filial_id = fmonatsstatistik.filialnr))
)首先，把它变成一个3表的JOIN。但是你应该试着让它变得更简单，因为有这么多这样的。
"filesort“并不是”非常糟糕“。实际上，您必须至少有两个--一个用于GROUP BY，另一个用于ORDER BY。(EXPLAIN没有说明需要多少。)
请提供EXPLAIN；Rows的数量通常是信息丰富的。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/188565
复制相关文章
相似问题
腾讯云开发者
