MariaDB 10.9.3: MySQL查询占用大量时间。如何重构这个？

Question

我有一个数据库，其中包含两个表:带有核心数据的文章和带有交付物品的articleHistory，以及相应的价格和数量。要根据交货而不是基础信息中的平均价格计算实际价格，我需要查询所有商品并添加历史记录，只包含足够的结果，至少与库存中的数量相匹配。

目前，我正在使用两个单独的查询。首先收集所有文章，然后在for循环中添加历史信息以计算php中的价格：

-- First get all articles:
SELECT Artikel.Artikel,
       Artikel.Hersteller,
       Artikel.Bezeichnung1,
       Artikel.Bezeichnung2,
       Artikel.Matchcode,
       Artikel.Stellplatzname,
       Artikel.BestandAktuell,
       Artikel.Nachkommastellen
FROM artikel AS Artikel;

-- Use for loop to get history into nested array:
SELECT t.Menge,
       t.Preiseinheit,
       t.Einheitspreis,
       t.Datum,
       @total := @total + t.Menge AS Summe
FROM (SELECT *, @total := 0 FROM artikelhistorie WHERE Vorgang = 'EL' AND Header_Artikel = '$Artikel' AND Header_Hersteller = '$Hersteller' ORDER BY Datum DESC) t
WHERE t.Vorgang = 'EL'
AND t.Header_Artikel = '$Artikel'
AND t.Header_Hersteller = '$Hersteller'
AND @total < $Bestand;

但是这个查询需要超过2秒的时间，而且数据库中有5000多篇文章，需要花费大量的时间(>2.5小时)。如何重构此查询或使用完全不同的查询来加快任务？是否有一种方法只使用一个查询并立即计算金额？我不能胜任MySQL本身的任务，所以我目前正在使用PHP来完成这个任务。

这两个表的结构如下：

CREATE TABLE `artikel` (
  `Artikel` varchar(20) COLLATE latin1_bin NOT NULL,
  `Hersteller` char(10) COLLATE latin1_bin NOT NULL,
  `Bezeichnung1` varchar(50) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Bezeichnung2` varchar(50) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Gewicht` decimal(9,3) NOT NULL DEFAULT '0.000',
  `ArtikelhistorieAnker` int(11) NOT NULL DEFAULT '0',
  `RabattierungsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `USt_Satz_Alt` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `Artikelgruppe` char(3) COLLATE latin1_bin NOT NULL DEFAULT '999',
  `Erloescode` char(2) COLLATE latin1_bin NOT NULL DEFAULT '01',
  `Mengeneinheit` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Preiseinheit` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Preiserrechnungsformel` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Eingabefolge` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Nachkommastellen` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Provisionssatz` decimal(9,2) NOT NULL DEFAULT '0.00',
  `Bewertungssatz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `RabattstaffelVerkauf` char(2) COLLATE latin1_bin NOT NULL DEFAULT '00',
  `RabattgruppeVerkauf` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Aktionscode` char(2) COLLATE latin1_bin NOT NULL DEFAULT '00',
  `VerbrauchPeriode` decimal(15,0) NOT NULL DEFAULT '0',
  `VerbrauchKumuliert` decimal(15,0) NOT NULL DEFAULT '0',
  `UmsatzPeriode` decimal(15,2) NOT NULL DEFAULT '0.00',
  `UmsatzKumuliert` decimal(15,2) NOT NULL DEFAULT '0.00',
  `RoherloesPeriode` decimal(15,2) NOT NULL DEFAULT '0.00',
  `RoherloesKumuliert` decimal(15,2) NOT NULL DEFAULT '0.00',
  `BestandAktuell` decimal(15,0) NOT NULL DEFAULT '0',
  `FertigungsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Sonderpreis` decimal(15,2) NOT NULL DEFAULT '0.00',
  `SonderpreisGueltigBis` date NOT NULL DEFAULT '0100-01-01',
  `Frei` decimal(5,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis1` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis2` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis3` decimal(15,2) NOT NULL DEFAULT '0.00',
  `ArtikelzusatztextAnker` int(11) NOT NULL DEFAULT '0',
  `LizenzkennzeichenAlt` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Frei1` varbinary(2) DEFAULT NULL,
  `Stellplatzname` varchar(6) COLLATE latin1_bin NOT NULL DEFAULT '',
  `MittlererEinkaufspreis` decimal(15,2) NOT NULL DEFAULT '0.00',
  `LizenzkennzeichenNeu` varchar(8) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Frei2` varbinary(5) DEFAULT NULL,
  `Bestellkennzeichen` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Gebindefaktor` decimal(9,3) NOT NULL DEFAULT '0.000',
  `Mindest_Meldebestand` decimal(15,0) NOT NULL DEFAULT '0',
  `ProvisionierungsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `Matchcode` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
  `ArtikeltextSprache1Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache2Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache3Anker` int(11) NOT NULL DEFAULT '0',
  `FesterLieferant` char(10) COLLATE latin1_bin NOT NULL DEFAULT '0000000000',
  `TextergaenzungsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `LagerfuehrungsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `HistorieneintragsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Lizenzkennzeichen` char(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `Anker_Dokumente` int(11) NOT NULL DEFAULT '0',
  `Ende_Dokumente` int(11) NOT NULL DEFAULT '0',
  `Frei3` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Prueflizenzgruppe` varchar(8) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Gewichtseinheit` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `KalkulatorischerEinkaufspreis` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Bezugskostenzuschlag_Alt` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Gemeinkostenzuschlag_Alt` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Gewinnzuschlag_Alt` decimal(10,2) NOT NULL DEFAULT '0.00',
  `KalkulatorischerEK_PflegeKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `Kostentraegernummer` char(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Umrechnungsfaktor` decimal(15,6) NOT NULL DEFAULT '0.000000',
  `UmrechnungsfaktorMengeneinheit` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '',
  `UmrechnungsfaktorMengenformat` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `UmrechnungsfaktorEingabefolge` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `SteuerklassenzugriffsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `ArtikeltextSprache4Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache5Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache6Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache7Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache8Anker` int(11) NOT NULL DEFAULT '0',
  `ArtikeltextSprache9Anker` int(11) NOT NULL DEFAULT '0',
  `Steuerklasse` char(3) COLLATE latin1_bin NOT NULL DEFAULT '001',
  `SonderpreisGueltigAb` date NOT NULL DEFAULT '0100-01-01',
  `Standardlager` char(4) COLLATE latin1_bin NOT NULL DEFAULT '0001',
  `Verkaufspreis4` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis5` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis6` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis7` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis8` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Verkaufspreis9` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Dispokennzeichen` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `MaterialgemeinkostenNr` char(3) COLLATE latin1_bin NOT NULL DEFAULT '000',
  `VerwaltungsgemeinkostenNr` char(3) COLLATE latin1_bin NOT NULL DEFAULT '000',
  `VertriebsgemeinkostenNr` char(3) COLLATE latin1_bin NOT NULL DEFAULT '000',
  `GewinnzuschlagNr` char(3) COLLATE latin1_bin NOT NULL DEFAULT '000',
  `EntwicklungsgemeinkostenNr` char(3) COLLATE latin1_bin NOT NULL DEFAULT '000',
  `VerkaufspreiskalkulationsKz` char(1) COLLATE latin1_bin NOT NULL DEFAULT '0',
  `Frei4` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Frei31` varchar(3) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Statuskennzeichen` varchar(2) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Verkaufskennzeichen` char(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `Chargenpflichtkennzeichen` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT 'N',
  `Chargenentnahmekennzeichen` varchar(2) COLLATE latin1_bin NOT NULL DEFAULT 'E1',
  `ChargenVorlauftage` int(11) NOT NULL DEFAULT '0',
  `ChargenAblaufzeit` int(11) NOT NULL DEFAULT '0',
  `ChargenSperrkennzeichen` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT 'F',
  `ChargenNummernkreistyp` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT 'S',
  `ChargenNummernkreis` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT '00000000000000000000',
  `ChargennummerVergabeKz` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT 'N',
  `ChargennummerZeichenvorrat` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT 'G',
  `ChargenBelegdruckkennzeichen` char(1) COLLATE latin1_bin NOT NULL DEFAULT '4',
  `Lieferantenchargekenzeichen` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT 'J',
  `Chargenposition_Initial_Anker` int(11) NOT NULL DEFAULT '0',
  `StandardlagerEinkauf` char(4) COLLATE latin1_bin NOT NULL DEFAULT '0001',
  `Frei41` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Frei5` smallint(6) NOT NULL DEFAULT '0',
  `Timestamp_Datum` date NOT NULL DEFAULT '0100-01-01',
  `Timestamp_Zeit` time NOT NULL DEFAULT '00:00:00',
  `Timestamp_Benutzer` varchar(3) COLLATE latin1_bin NOT NULL DEFAULT '',
  `LetzteLieferung` date NOT NULL DEFAULT '0100-01-01',
  `Sperrvermerk1` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Sperrvermerk2` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Sperrvermerk3` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Sperrvermerk4` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Sperrvermerk5` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Sperrvermerk6` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Anker_Memo` int(11) NOT NULL DEFAULT '0',
  `MaterialGKPreset` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '1',
  `VerwaltungsGKPreset` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '2',
  `VertriebsGKPreset` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '3',
  `GewinnzuschlagPreset` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '4',
  `EntwicklungsGKPreset` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '5',
  `MengeneinheitPreset` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '6',
  `USER_ECCN` varchar(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `USER_EUECCN` varchar(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `USER_Herkunftsland` varchar(3) COLLATE latin1_bin NOT NULL DEFAULT '',
  `USER_Zolltarifnummer` varchar(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `USER_SVHC` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`Artikel`,`Hersteller`),
  KEY `ZZ_ArtikelIndexTimestamp` (`Timestamp_Datum`,`Timestamp_Zeit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ROW_FORMAT=COMPRESSED;

CREATE TABLE `artikelhistorie` (
  `ID` int(11) NOT NULL,
  `ChainGuid` char(36) COLLATE latin1_bin NOT NULL,
  `Sequence` decimal(38,0) NOT NULL,
  `Guid` char(36) COLLATE latin1_bin NOT NULL,
  `Header_Artikel` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Header_Hersteller` char(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Positionstyp` smallint(6) NOT NULL DEFAULT '1',
  `Datum` date NOT NULL DEFAULT '0100-01-01',
  `Vorgang` varchar(2) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Nummer` varchar(11) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Kunde` varchar(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Menge` decimal(15,0) NOT NULL DEFAULT '0',
  `Einheitspreis` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Rabatte` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Roherloes` decimal(15,2) NOT NULL DEFAULT '0.00',
  `Kundenkurzbezeichnung` varchar(28) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Positionsart` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Wareneingangsnr_GemaessBW` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT '',
  `KzAnzeigeWareneingangsNr` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Preiseinheit` char(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Auftrags_Bestellnummer` varchar(11) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Auftrags_Bestelldatum` date NOT NULL DEFAULT '0100-01-01',
  `CheckIntern` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Kostenstelle` char(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Reserviert2` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Kostentraeger` char(10) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Stuecklisten_Pos_KZ` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Art` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Seriennummer` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT '',
  `Text` varchar(100) COLLATE latin1_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `ZZ_ChainIndex` (`ChainGuid`,`Sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ROW_FORMAT=COMPRESSED;

PHP代码很简单：

$sage = new TBSageConnector();

// executes the first query
$artikel = $sage->holeAlleArtikel();

// executes the second query in a loop
for($i=0;$i<count($artikel);$i++) {
    $art = $artikel[$i]['Artikel'];
    $her = $artikel[$i]['Hersteller'];
    $bes = $artikel[$i]['Bestand'];
    $artikel[$i]['Historie'] = $sage->holeArtikelHistorieBisBestand($art, $her, $bes);
}

我使用的是MariaDB 10.9.3和PHP8。

第一个查询的EXPLAIN输出：

+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | Artikel | ALL  | NULL          | NULL | NULL    | NULL | 6318 |       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

第二个查询的解释输出：

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | extra                       |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | PRIMARY     | <derived2>      | ALL  | NULL          | NULL | NULL    | NULL | 315324 | Using where                 |
|  2 | DERIVED     | artikelhistorie | ALL  | NULL          | NULL | NULL    | NULL | 315324 | Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

Answer 1

尝试使用联接谓词通过左侧连接您的文章和历史记录表。在子查询中查找和。然后应用你的条件来过滤满足库存数量的记录。我想出了下面的查询，请尝试(没有测试)。

SELECT * FROM (
    SELECT Artikel.Artikel,
           Artikel.Hersteller,
           Artikel.Bezeichnung1,
           Artikel.Bezeichnung2,
           Artikel.Matchcode,
           Artikel.Stellplatzname,
           Artikel.BestandAktuell,
           Artikel.Nachkommastellen,
           History.Menge,
           History.Preiseinheit,
           History.Einheitspreis,
           History.Datum,
           (SELECT SUM(H.Menge) FROM artikelhistorie AS H WHERE H.Header_Artikel = History.Header_Artikel AND H.Header_Hersteller = History.Header_Hersteller AND H.Vorgang = 'EL') AS Summe       
    FROM artikel AS Artikel
    LEFT JOIN artikelhistorie AS History ON Artikel.Artikel = History.Header_Artikel AND 
        Artikel.Hersteller = History.Header_Hersteller AND History.Vorgang = 'EL'
) Overall
WHERE Summe < AmountInInventory;