mysql - Mysql GROUP BY在 View 上真的很慢

我有这些桌子。

CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementType` tinyint(3) UNSIGNED NOT NULL,
  `deleted` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementType` (`movementType`) USING BTREE,
  ADD KEY `deleted` (`deleted`),
  ADD KEY `movementId` (`movementId`,`deleted`);

CREATE TABLE `movements_items` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `itemId` mediumint(8) UNSIGNED NOT NULL,
  `qty` decimal(10,3) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements_items`
  ADD KEY `movementId` (`movementId`),
  ADD KEY `itemId` (`itemId`),
  ADD KEY `movementId_2` (`movementId`,`itemId`);

该视图称为“ movements_items_view”。

SELECT
movements_items.itemId, movements_items.qty,
movements.movementId, movements.movementType
FROM movements_items
JOIN movements ON (movements.movementId=movements_items.movementId
                    AND movements.deleted=0)

第一个表有5913行，第二个表有144992。

该视图非常快，它在0.0011s内将20个结果加载到PhpMyAdmin中，但是一旦我要求对它进行GROUP BY（我需要它使用SUM（）进行统计），就可以：

SELECT * FROM movements_items_view GROUP BY itemId LIMIT 0,20

时间跳到0.2s或更多，它会导致运动连接上出现“在哪里使用；使用临时文件；使用文件排序”。
任何帮助表示赞赏，谢谢。

编辑：

我也通过phpMyAdmin运行此查询以尝试不使用该视图：

SELECT movements.movementId, movements.movementType, movements_items.qty
    FROM movements_items
    JOIN movements ON movements.movementId=movements_items.movementId
    GROUP BY itemId LIMIT 0,20

和性能是一样的。

编辑。这是说明

id  select_type     table               type    possible_keys                    key            key_len     ref                        rows     Extra
1   SIMPLE          movements           index   PRIMARY,movementid               movement_type  1           NULL                       5913     Using index; Using temporary; Using filesort
1   SIMPLE          movements_items     ref     movementId,itemId,movementId_2   movementId_2   3           movements.movementId       12       Using index

最佳答案

把它里面翻出来。查看是否可行：

SELECT  movementId, m.movementType, mi.qty
    FROM
        ( SELECT  movementId, qty
            FROM  movements_items
            GROUP BY  itemId
            ORDER BY  itemId
            LIMIT  20
        ) AS mi
    JOIN  movements AS m  USING(movementId)

诀窍是尽早执行LIMIT。原始方式包含所有数据，而不仅仅是20行。

在movements_items中，没有列或列的组合是“唯一的”吗？如果是这样，将其设置为PRIMARY KEY。

在movement中，KEY movementId (movementId,deleted)是多余的，应将其删除。

在movement_items中，KEY movementId (movementId)是多余的，应将其删除。

关于mysql - Mysql GROUP BY在 View 上真的很慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59340374/