我有这些桌子。
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结果,但当我要求在其上创建一个组时(我需要它用SUM()es进行统计:
SELECT * FROM movements_items_view GROUP BY itemId LIMIT 0,20时间跃升到0.2s或更多,它会导致在移动连接上“使用where;使用临时文件;使用文件”。
编辑:绕过视图。
我还通过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任何帮助都很感谢,谢谢。
发布于 2019-12-15 12:57:56
首先,如果您绕过视图并使用GROUP BY运行底层查询,查询会继续缓慢执行吗?在我的经验中,MySQL对除了SELECT语句以外的任何东西使用的视图都是非常糟糕的。
如果这解决了问题,那么只需使用要查询的实际信息创建第二个视图(即包含在视图中的GROUP BY )。
如果问题持续存在,那么有几件事情可以改进。
movement_items表中没有主键。再加一个。如果movementId和itemId的每个组合只能在条目上有,那么将PK作为这些列中的一个组合键。否则,可以添加代理项自动增量主键。
尝试以下索引:
movement_items: (itemId,movementId,qty)查询优化不是一个零碎的过程。您不能改进一个查询,然后期望这种改进一定要执行到相关查询中。
你说你想要做一个SUM,那么,向我们展示这个SUM查询。您到底是什么,SUM明将需要与SELECT *不同的优化。
同时,提供您的EXPLAIN计划。您提到您已经运行了它们,但不要向我们提供非常有用的信息。
https://dba.stackexchange.com/questions/255643
复制相似问题