mysql - 如何避免使用子查询在 MySQL 中多次运行一个大表？

Question

我在继承的站点上的 MySQL 5..0.88/Coldfusion8 中有一个产品表。

我需要多次查询表以在搜索表单中填写默认值并进行实际搜索。

我所有的查询基本都是一样的，吃了不少时间。唯一改变的是被查询的参数，实际的查询设置没有改变，所以我希望这可以以某种方式捆绑在单个查询中以避免运行查询 x 来获取值 A，然后运行查询 x 来获取值 B 等等....

我的转储查询如下所示:

 // 1/6 queries - get all company names based on active products
 SELECT art.firma
      FROM artikelstammdaten AS art USE INDEX (i_firma)
      // join pricelists to exclude seller if pricelists applicable but user not authorized 
      LEFT JOIN preislisten p ON 
           p.iln = art.iln
           AND p.ean = art.ean 
           // dynamic list of seller IDs and pricelist name
           AND ( (  p.preisliste = "name1" AND p.iln= "12345" ) OR (  p.preisliste = "name2" AND p.iln= "98765" ) OR (1=0) )

            WHERE art.aktiv = "ja" 
            // if pricelists apply, only take products with a pice not zero
            AND ( IF( art.iln IN ( 12345,98765), p.preisliste IS NOT NULL,1 ) )
            // exclude demo
            AND art.iln != "111" AND art.iln != "222" AND art.iln != "777"
            // sellers free to order or authorization ok
            AND (art.modus = "OPEN" OR art.iln IN ( 12345,98765) )
            // inventory
            AND ( art.bestand != "0" ) AND art.vororder = "0"
            // order mode
            AND ( art.vororder = 'nein' OR art.vororder = 'no' )
            GROUP BY art.firma

运行后，我必须再次运行它才能获得:

 art.marke= brandnames
 art.preis_aktuell = minimum buying price
 art.preis_vk = maximum buying price
 ... 4 more

我一直在尝试使用像这样的子查询来做到这一点:

 SELECT DISTINCT
      ( SELECT art.firma  ) AS firma
     ,( SELECT art.marke  ) AS marke
     ,( SELECT MIN(art.preis_aktuell) ) AS ak_min
     ,( SELECT MAX(art.preis_aktuell) ) AS ak_max
     ,( SELECT MIN(art.preis_vk) ) AS vk_min
     ,( SELECT MAX(art.preis_vk) ) AS vk_max
     ,( SELECT ROUND(MIN( 100*( ( art.preis_ek - art.preis_aktuell ) / art.preis_ek ) ))) AS reb_min
     ,( SELECT ROUND(MAX( 100*( ( art.preis_ek - art.preis_aktuell ) / art.preis_ek ) ))) AS reb_max
 FROM artikelstammdaten AS art USE INDEX (i_iln,)
    ....
    GROUP BY art.marke
    ORDER BY COUNT(art.marke) DESC;

但我无法让它工作，因为我只得到一条记录(最小/最大值可以，但不适用于 x-sellers、y-brands 和 20 种尺寸。我认为这是由于我不知道如何对子查询进行分组，所以:

问题:
是否可以使用子查询/其他方式将上述查询捆绑到一个查询中？我将如何GROUP BY 才不会以单个结果记录结束？

感谢您的帮助!

编辑:也许拥有它。更改为 SELECT DISTINCT 和 GROUPB BY(见上文)。现在的问题是如何对特定列进行排序以及结果是否有效(现在检查)

编辑 2:
这是为 "Articles" 和 "Pricelists" 这两个表创建的。文章包括包含基本价格的基本产品，价格表包含可供选择的价格，可以分配给用户。

文章创建(字段是德语，我将上面的查询转换回德语)

CREATE TABLE `articles` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `iln` VARCHAR(13) NULL DEFAULT NULL,
    `ean` VARCHAR(35) NULL DEFAULT NULL,
    `artikelnummer` VARCHAR(35) NULL DEFAULT NULL,
    `artikelbezeichnung` VARCHAR(70) NULL DEFAULT NULL,
    `artikelbezeichnung_lang` VARCHAR(255) NULL DEFAULT NULL,
    `groesse` VARCHAR(10) NULL DEFAULT NULL,
    `farbe` VARCHAR(35) NULL DEFAULT NULL,
    `farbnummer` VARCHAR(10) NULL DEFAULT NULL,
    `material` VARCHAR(70) NULL DEFAULT NULL,
    `preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
    `preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
    `preis_aktuell` DECIMAL(12,2) NULL DEFAULT NULL,
    `einheit` VARCHAR(3) NULL DEFAULT NULL,
    `firma` VARCHAR(35) NULL DEFAULT NULL,
    `marke` VARCHAR(35) NULL DEFAULT NULL,
    `warengruppe1` VARCHAR(35) NULL DEFAULT NULL,
    `warengruppe2` VARCHAR(35) NULL DEFAULT NULL,
    `warengruppe3` VARCHAR(35) NULL DEFAULT NULL,
    `groessenlauf` VARCHAR(35) NULL DEFAULT NULL,
    `bildpfad` VARCHAR(255) NULL DEFAULT NULL,
    `bilddateiname` VARCHAR(255) NULL DEFAULT NULL,
    `nos` VARCHAR(4) NULL DEFAULT NULL,
    `nos_anzeige` VARCHAR(4) NULL DEFAULT NULL,
    `aktiv` VARCHAR(4) NULL DEFAULT NULL,
    `vororder` VARCHAR(4) NULL DEFAULT 'Nein',
    `highlight` VARCHAR(4) NULL DEFAULT NULL,
    `modus` VARCHAR(4) NULL DEFAULT NULL,
    `bestand` DECIMAL(10,0) NULL DEFAULT '0',
    `last_import` DATETIME NULL DEFAULT NULL,
    `last_update` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `i_iln` (`iln`),
    INDEX `i_iln_id` (`id`, `iln`),
    INDEX `i_aktiv` (`aktiv`),
    INDEX `i_bestand` (`bestand`),
    INDEX `i_artikelnummer` (`artikelnummer`),
    INDEX `i_artikelbezeichnung` (`artikelbezeichnung`),
    INDEX `i_artikelbezeichnung_lang` (`artikelbezeichnung_lang`),
    INDEX `i_farbe` (`farbe`),
    INDEX `i_groesse` (`groesse`),
    INDEX `i_preis_aktuell` (`preis_aktuell`),
    INDEX `i_preis_vk` (`preis_vk`),
    INDEX `i_warengruppe1` (`warengruppe1`),
    INDEX `i_warengruppe2` (`warengruppe2`),
    INDEX `i_nos` (`nos`),
    INDEX `i_modus` (`modus`),
    INDEX `i_firma` (`firma`),
    INDEX `i_marke` (`marke`),
    INDEX `i_highlight` (`highlight`),
    INDEX `i_preis_ek` (`preis_ek`),
    INDEX `i_warengruppe3` (`warengruppe3`),
    INDEX `i_farbnummer` (`farbnummer`),
    INDEX `i_ean` (`ean`),
    INDEX `i_vororder` (`vororder`)        
    )
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

价目表创建

CREATE TABLE `preislisten` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `iln` VARCHAR(13) NULL DEFAULT NULL,
    `preisliste` VARCHAR(35) NULL DEFAULT NULL,
    `bezeichnung` VARCHAR(35) NULL DEFAULT NULL,
    `EAN` VARCHAR(14) NULL DEFAULT NULL,
    `waehrung` VARCHAR(3) NULL DEFAULT NULL,
    `ek` DECIMAL(18,3) NULL DEFAULT NULL,
    `vk` DECIMAL(18,3) NULL DEFAULT NULL,
    `onlinepreis` DECIMAL(18,3) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `i_iln` (`iln`),
    INDEX `ind_onlinepreis` (`onlinepreis`)
    )
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

选择

SELECT DISTINCT
     ( SELECT art.firma  ) AS comp
    ,( SELECT art.marke  ) AS marke
    ,( SELECT MIN(art.preis_aktuell) ) AS ak_min
    ,( SELECT MAX(art.preis_aktuell) ) AS ak_max
    ,( SELECT MIN(art.preis_vk) ) AS vk_min
    ,( SELECT MAX(art.preis_vk) ) AS vk_max
    ,( SELECT ROUND(MIN( 100*( ( art.preis_ek - art.preis_aktuell ) / art.preis_ek ) ))) AS reb_min
    ,( SELECT ROUND(MAX( 100*( ( art.preis_ek - art.preis_aktuell ) / art.preis_ek ) ))) AS reb_max     

    FROM artikelstammdaten AS art USE INDEX (i_
    LEFT JOIN preislisten p ON 
                p.iln = art.iln 
            AND p.ean = art.ean 
            AND ( (  p.preisliste = "-Standard-" AND p.iln = "9900000003005" ) OR (  p.preisliste = "-Standard-" AND p.iln = "2222222222222" ) OR (1=0) )

            WHERE art.aktiv = "ja" 
            AND ( IF( art.iln IN ( 9900000003005,2222222222222 ), p.onlinepreis IS NOT NULL,1 ) )
            AND art.iln != "1111111111111" AND art.iln != "2222222222222" AND art.iln != "7777777777777"
            AND (art.modus = "OPEN" 
                    OR art.iln IN ( 9900000003005,2222222222222 ) 
            )
            AND ( art.bestand != "0" OR ( ( art.nos = "JA" OR art.nos = "YES" ) AND art.nos_anzeige = "JA" ) )  AND art.vororder = "nein"
            AND ( art.vororder = 'nein' OR art.vororder = 'no' )

解释选择

"id "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "art"   "ref"   "i_iln,i_aktiv,i_bestand,i_nos,i_modus,i_vororder"  "i_aktiv"   "7" "const" "16844" "Using where; Using temporary; Using filesort"
"1" "SIMPLE"    "p" "ref"   "i_iln" "i_iln" "16"    "db.art.iln"    "1075"  "Using where"

好的。很多数据...如果您有任何问题，请告诉我。 articles 上的索引看起来有点多。时间 killer 是带有价目表的 LEFT JOIN。如果没有这个，查询会非常快，但这种情况很少见。

感谢您的关注!如果有人能给我一个关于如何加快速度的好答案，我会赏金!

编辑 3:
我添加了 Unique index。现在重新运行查询 EXPLAIN 是:

"id" "select_type"  "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1"  "SIMPLE"        "art"  "ref"   "i_iln,i_aktiv,i_bestand,i_nos,i_modus,i_vororder"  "i_aktiv"   "7" "const" "15409" "Using where; Using temporary; Using filesort"
"1"  "SIMPLE"        "p"    "ref"   "i_iln_ean,i_iln"   "i_iln_ean" "33"    "db.art.iln,db.art.ean" "1" "Using where"

似乎快了一点。执行时间降为 0，所以我想这是一个好兆头 :-)。问题:我是否也应该将此索引添加到文章表中。它在那里也将是独一无二的。

Answer 1

当您执行 GROUP BY art.comp 时，我假设您真正想做的是 SELECT DISTINCT？

无论如何，如果您考虑一下，您得到的各个列都会返回不同数量的行，因为您不知道每个列将有多少个不同的值。

这会自动告诉您您不能一次将它们全部作为列返回:您会得到多少行？

如果查询速度很慢，那么您应该解决这个问题——我没有看到任何会使查询速度变慢的东西。您的表可能需要一些复合索引，以便 MySQL 可以更好地过滤内容。