哪些索引可以提高join和group by的性能

q9rjltbz 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(379)

我已经设置了一些表并运行了一个查询。不过，在我的解释中，sql结果会出现在正在生成的临时表中（我假设这是因为groupby）
我添加了一些索引来加快查询速度，但是想知道是否有方法停止使用临时表，以及是否有其他方法可以使用索引加快查询速度？
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

查询结果说明

mysql query-performance group-by performance indexing

来源：https://stackoverflow.com/questions/52988287/what-indexes-to-improve-performance-of-join-and-group-by

1条答案

按热度按时间

wz8daaqr1#

专业提示避免 SELECT * 或者 SELECT table.* 在性能敏感的查询中。而是按名称选择实际需要使用的列
pro-tip mysql有一个臭名昭著的非标准扩展 GROUP BY 你正在使用的，可能是误用的。读这个。https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html 如果您遵循第一个专业提示，那么遵循第二个提示会容易得多。
专业提示：避免“抛出”大量的单列索引，以期加快查询速度。相反，创建索引，通常是复合索引，以匹配实际查询的需要。读这个https://use-the-index-luke.com .
专业提示 Using temporary; using filesort 在解释输出中出现并不一定是坏的。这仅仅意味着查询引擎必须在返回部分结果集之前缓存它。这个 temporary 它不是一个实际的表，而是一个ram结构。如果它太大以至于淹没了ram，mysql会将它溢出到磁盘。但你的不是。
尽管如此，让我们重构您的查询。我猜你想检索最大 idCartDATA 每个的值 CartSplitData.SUPPLIERID .
我们把它写成子查询。

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

赞(0）回复(0）举报 2021-06-18

我来回答

哪些索引可以提高join和group by的性能

1条答案

相关问题

热门标签

最新问答