通过哪些指标提高联接和组的性能？

Question

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

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

CartData

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

Pro提示避免在性能敏感的查询中使用SELECT *或SELECT table.*。相反，请按名称选择实际需要使用的列

Pro tip MySQL有一个臭名昭著的非标准扩展GROUP BY，您正在使用，而且可能误用。看看这个。如果您遵循第一个专业技巧，那么跟随第二个建议就容易多了。

Pro提示避免“抛出”大量的单列索引，希望加快查询的速度。相反，创建索引，通常是复合索引，与实际查询的需求相匹配。读这个https://use-the-index-luke.com。

Pro提示 Using temporary; using filesort出现在解释输出中并不一定是坏的。这仅仅意味着查询引擎在返回结果集之前必须缓存部分结果集。temporary不是一个实际的表，而是一个RAM结构。如果内存太大，MySQL就会把它溅到磁盘上。但你的不是。

尽管如此，让我们重构您的查询。我想您希望为每个idCartDATA检索具有最大CartSplitData.SUPPLIERID值的行。

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

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

通过在CartSplitData：(SUPPLIERID, IDCartDATA)上添加一个复合索引，可以大大加快查询速度。

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

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上的索引将有助于此外部查询，复合索引将创建^。