mysql - 提高JOIN和GROUP BY性能的指标有哪些

Question

我已经设置了一些表并运行了一个查询。然而，在我的解释中，SQL 结果会生成一个临时表(我假设这是因为 GROUP BY)

我已经添加了一些索引来加快查询速度，但想知道是否有办法停止使用临时表，是否有任何其他方法可以使用索引加快查询速度？

购物车数据

CREATE TABLE `cartdata` (
    `IDCartData` INT(11) NOT NULL AUTO_INCREMENT,
    `CartOrderref` VARCHAR(25) NOT NULL DEFAULT '',
    `UserID` INT(11) NOT NULL DEFAULT '0',
    `LastUpdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
    CURRENT_TIMESTAMP,
    `ManualContactName` VARCHAR(100) NOT NULL DEFAULT '',
    `ManualOrderConfirmationEmail` VARCHAR(100) NOT NULL DEFAULT '',
    PRIMARY KEY (`IDCartData`),
    INDEX `CartOrderref` (`CartOrderref`)
)

CartSplitData

    CREATE TABLE `cartsplitdata` (
        `IDCartSupplierData` INT(11) NOT NULL AUTO_INCREMENT,
        `IDCartData` INT(11) NOT NULL DEFAULT '0',
        `supplierid` INT(11) NOT NULL DEFAULT '0',
        `DeliveryDate` DATE NOT NULL DEFAULT '2000-01-01',
        `AccountNumber` VARCHAR(50) NOT NULL DEFAULT '',
        `ManualOrderref` VARCHAR(50) NOT NULL DEFAULT '',
        `lastupdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`IDCartSupplierData`),
        INDEX `cartdatasupplierid` (`IDCartData`, `supplierid`)
    )

我的示例查询

        EXPLAIN SELECT max(CartData.idCartDATA) AS idCartDATA , CartData.*, CartSplitData.*
        FROM CartData
        JOIN CartSplitData ON CartSplitData.IDCartDATA = CartDATA.IDCartData
        WHERE  CartData.CartOrderref = 'XXXXXXXXX'
        group by CartSplitData.SUPPLIERID

查询结果说明

Answer 1

专业提示 避免在对性能敏感的查询中使用SELECT * 或SELECT table.*。而是按名称选择您实际需要使用的列

专业提示 MySQL 对 GROUP BY 有一个臭名昭著的非标准扩展，您正在使用它，并且可能会误用它。读这个。 https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html如果您遵循了第一个专业提示，那么遵循第二个会容易得多。

专业提示 避免为了加速查询而“投入”大量单列索引。相反，创建索引，通常是复合索引，以满足您实际查询的需要。阅读此 https://use-the-index-luke.com .

专业提示 使用临时；使用出现在 EXPLAIN 输出中的 filesort 不一定是坏事。它只是意味着查询引擎必须在返回之前缓存部分结果集。 temporary 不是一个实际的表，它是一个 RAM 结构。如果它太大以至于淹没了 RAM，MySQL 会将其溢出到磁盘。但你的不是。

综上所述，让我们重构您的查询。我猜您想为每个 CartSplitData.SUPPLIERID 检索具有最大 idCartDATA 值的行。

所以让我们把它写成一个子查询。

                  SELECT max(IDCartDATA) AS IDCartDATA, SUPPLIERID
                    FROM CartSplitData
                   GROUP BY SUPPLIERID

通过在 CartSplitData 上放置复合索引，可以显着加快此查询:(SUPPLIERID, IDCartDATA)。

接下来，让我们重写您的主查询以查找与该子查询中的 ID 匹配的行。

SELECT CartData.*             /* * hammers performance */
       CartSplitData.*        /* * hammers performance */
  FROM CartData
  JOIN CartSplitData ON CartSplitData.IDCartDATA = CartDATA.IDCartData
  JOIN (
                  SELECT max(IDCartDATA) AS IDCartDATA, SUPPLIERID
                    FROM CartSplitData
                   GROUP BY SUPPLIERID
       )x ON x.SUPPLIERID = CartSplitData.SUPPLIERID
         AND x.IDCartData = CartSplitData.IDCartData
 WHERE CartData.CartOrderref = 'XXXXXXXXX'

您在 CartData.CartOrderref 上的索引将有助于此外部查询，创建的复合索引也将如此 ^^^。