主页 / dba / 问题 / 52043
Accepted
Abdul Manaf
Asked: 2013-10-24 03:45:37 +0800 CST

优化 MySQL Self JOIN 查询

  • 772

我有一个运行时间超过 15 秒的查询

SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

如何重写/优化查询以加快执行时间。

表结构

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ST_StockCode` int(11) NOT NULL,
  `SM_StockCode` int(11) NOT NULL,
  `ST_ItemSize` decimal(18,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `stockcode` (`ST_StockCode`),
  KEY `sm_stockcode` (`SM_StockCode`)
) ENGINE=InnoDB

解释计划

+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys      | key       | key_len | ref                | rows    | Extra                           |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
|  1 | SIMPLE      | t2    | ALL  | sm_stockcode       | NULL      | NULL    | NULL               | 1000545 | Using temporary; Using filesort |
|  1 | SIMPLE      | t1    | ref  | stockcode,idx_test | stockcode | 4       | lc.t2.SM_StockCode |       4 | Using index                     |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+

更新 一些行

SELECT * FROM stocks LIMIT 10;
+----+--------------+--------------+-------------+
| id | ST_StockCode | SM_StockCode | ST_ItemSize |
+----+--------------+--------------+-------------+
|  1 |       679783 |       678649 |        7.00 |
|  2 |       679789 |       688622 |        7.00 |
|  3 |       679792 |       679793 |        8.00 |
|  4 |       679792 |       686376 |        8.00 |
|  5 |       679793 |       679792 |        7.00 |
|  6 |       679793 |       686376 |        8.00 |
|  7 |       679795 |       679796 |        8.00 |
|  8 |       679796 |       679795 |        7.00 |
|  9 |       679797 |       617114 |        7.00 |
| 10 |       679797 |       627339 |        7.00 |
+----+--------------+--------------+-------------+

对于超立方体

 SELECT * FROM similar_stocks WHERE ST_StockCode = 679792 OR SM_StockCode = 679792 ;
+-------+--------------+--------------+-------------+
| id    | ST_StockCode | SM_StockCode | ST_ItemSize |
+-------+--------------+--------------+-------------+
|     3 |       679792 |       679793 |        8.00 |
|     4 |       679792 |       686376 |        8.00 |
|     5 |       679793 |       679792 |        7.00 |
|  4774 |       686376 |       679792 |        7.00 |
| 50028 |       679792 |       679793 |        8.00 |
| 50029 |       679792 |       686376 |        8.00 |
| 50030 |       679793 |       679792 |        7.00 |
| 52798 |       686376 |       679792 |        7.00 |
+-------+--------------+--------------+-------------+
mysql performance

1 个回答

  1. Best Answer

    让我们首先根据提供的示例数据剖析原始查询：

    SQL小提琴

    MySQL 5.5.32 架构设置：

    CREATE TABLE Stocks
    (`id` int, `ST_StockCode` int, `SM_StockCode` int, `ST_ItemSize` int)
;

INSERT INTO Stocks
    (`id`, `ST_StockCode`, `SM_StockCode`, `ST_ItemSize`)
VALUES
    (1, 679783, 678649, 7.00),
    (2, 679789, 688622, 7.00),
    (3, 679792, 679793, 8.00),
    (4, 679792, 686376, 8.00),
    (5, 679793, 679792, 7.00),
    (6, 679793, 686376, 8.00),
    (7, 679795, 679796, 8.00),
    (8, 679796, 679795, 7.00),
    (9, 679797, 617114, 7.00),
    (10, 679797, 627339, 7.00)
;

    查询 1：

    SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

    结果：

    | ST_STOCKCODE | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|--------------|-------------|
|       679792 |       679792 |           7 |
|       679793 |       679793 |           8 |
|       679795 |       679795 |           7 |
|       679796 |       679796 |           8 |

    首先要注意的是，这是一个内连接，两个值 ST_StockCode 和 SM_StockCode 总是相同的。因此，在下面的示例中，我只提到其中一个，因为很容易将缺少的添加回来。

    其次，该查询对 GROUP BY 子句使用了非 SQL 标准扩展。查询按 ST_StockCode 分组，然后引用其他两个没有聚合的列。MySQL 将为这些列返回它遇到的第一个值。它不检查值是否不同，每次执行您可能会得到不同的结果。如果我们假设原始查询编写器知道这种行为，我们可以这样做：

    查询 2：

    SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 GROUP BY SM_StockCode;

    结果：

    | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       617114 |           7 |
|       627339 |           7 |
|       678649 |           7 |
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |
|       686376 |           8 |
|       688622 |           7 |

    这将为每个 SM_StockCode 返回最小 ST_ItemSize（而不是随机的）。但是，它可能会返回比原始查询更多的行，因为可能存在没有匹配 ST_StockCode 的 SM_StockCode（如提供的示例数据中所示）。但是，这很容易解决：

    查询 3：

    SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 WHERE EXISTS(SELECT 1 FROM stocks AS t2 WHERE t2.ST_StockCode = t1.SM_StockCode)
 GROUP BY SM_StockCode;

    结果：

    | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |

    现在只返回具有匹配 ST_StockCodes 的 SM_StockCodes。（如果您确实需要两次该值，则必须复制 SM_StockCode 列。）

    以这种方式编写查询后，索引策略变得相当明显：

    在 ST_StockCode 上创建一个索引以支持 EXISTS 查找，并在 SM_StockCode、ST_ItemSize 上创建第二个索引以支持 GROUP BY。

    如果您确定每个 SM_StockCode 都有一个匹配的 ST_StockCode（例如，因为在两者之间声明了外键，或者因为两个分析师发誓始终如此），您可以进一步缩短查询：

    问题 4：

    SELECT DISTINCT SM_StockCode, ST_ItemSize
  FROM stocks;

    结果：

    | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       678649 |           7 |
|       688622 |           7 |
|       679793 |           8 |
|       686376 |           8 |
|       679792 |           7 |
|       679796 |           8 |
|       679795 |           7 |
|       617114 |           7 |
|       627339 |           7 |

    但是，由于在 10 个随机行的示例数据摘录中没有给出此条件，因此上述结果在这种情况下不匹配。

    • 3

相关问题