缓慢的MySQL查询，按订单分组的限制

问题描述：

我目前加入5个表来选择20个对象来显示用户，不幸的是，如果我使用GROUP BY和ORDER BY它会变得非常慢。缓慢的MySQL查询，按订单分组的限制

示例查询看起来是这样的：

SELECT r.name, l.name, o.typ, o.id, persons, children, description, rating, totalratings, minprice, picture FROM angebote as a 
JOIN objekte as o ON a.fid_objekt = o.id 
JOIN regionen as r ON a.fid_region = r.id 
JOIN laender as l ON a.fid_land = l.id 
WHERE l.slug="aegypten" AND a.letztes_angebot >= 1 
GROUP BY a.fid_objekt ORDER BY rating DESC LIMIT 0,20

查询的EXPLAIN表明这一点：

+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+ 
| id | select_type | table | type | possible_keys    | key  | key_len | ref         | rows | Extra             | 
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+ 
| 1 | SIMPLE  | l  | ref | PRIMARY,slug    | slug  | 767  | const         |  1 | Using index condition; Using temporary; Using filesort | 
| 1 | SIMPLE  | o  | ALL | PRIMARY     | NULL  | NULL | NULL         | 186779 | Using join buffer (flat, BNL join)      | 
| 1 | SIMPLE  | a  | ref | unique_key,letztes_angebot | unique_key | 8  | ferienhaeuser.o.id,ferienhaeuser.l.id |  1 | Using where           | 
| 1 | SIMPLE  | r  | eq_ref | PRIMARY     | PRIMARY | 4  | ferienhaeuser.a.fid_region   |  1 |              | 
+------+-------------+-------+--------+----------------------------+------------+---------+---------------------------------------+--------+--------------------------------------------------------+

所以它看起来像它不使用密钥表objekte中，分析表明它使用2.7s来拷贝到tmp表。

而不是FROM angebote或JOIN objekte我试过用(SELECT * GROUP BY id)，但不幸的是这并没有改善。

用于WHERE,ORDER BY和GROUP BY的字段也被索引。

我想我错过了这里的一些基本概念，任何帮助将不胜感激。

因为它是最有可能我犯了一个错误与表，在这里他们的描述：

Objekte

 


+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| objekte | CREATE TABLE `objekte` (
    `id` int(11) NOT NULL, 
    `typ` varchar(50) NOT NULL, 
    `persons` int(11) NOT NULL, 
    `children` int(11) NOT NULL, 
    `description` text NOT NULL, 
    `rating` float NOT NULL, 
    `totalratings` int(11) NOT NULL, 
    `minprice` float NOT NULL, 
    `picture` varchar(255) NOT NULL, 
    `last_offer` int(11) NOT NULL, 
    PRIMARY KEY (`id`), 
    KEY `minprice` (`minprice`), 
    KEY `rating` (`rating`), 
    KEY `last_offer` (`last_offer`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Angebote

 

    +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| angebote | CREATE TABLE `angebote` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `fid_objekt` int(11) NOT NULL, 
    `fid_land` int(11) NOT NULL, 
    `fid_region` int(11) NOT NULL, 
    `fid_subregion` int(11) NOT NULL, 
    `letztes_angebot` int(11) NOT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `unique_key` (`fid_objekt`,`fid_land`,`fid_region`,`fid_subregion`), 
    KEY `letztes_angebot` (`letztes_angebot`), 
    KEY `fid_objekt` (`fid_objekt`), 
    KEY `fid_land` (`fid_land`), 
    KEY `fid_region` (`fid_region`), 
    KEY `fid_subregion` (`fid_subregion`) 
) ENGINE=InnoDB AUTO_INCREMENT=2433073 DEFAULT CHARSET=utf8 | 
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

联邦各州，regionen，subregionen（相同的结构）

 

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| laender | CREATE TABLE `laender` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `iso` varchar(2) NOT NULL, 
    `name` varchar(255) NOT NULL, 
    `slug` varchar(255) NOT NULL, 
    `letztes_angebot` int(11) NOT NULL, 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `iso` (`iso`), 
    KEY `slug` (`slug`), 
    KEY `letztes_angebot` (`letztes_angebot`) 
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8 | 
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

我不确定你是否有所有连接的索引，并且这意味着即使你有索引，分组仍然会很慢。 –

答

首先这是一个非标准组。因此，当你升级到MySQL 5.7时，它将停止工作。

最大的问题来自objekte表中没有使用索引的事实。更糟糕的是，您在该表上的评级字段上订购，但索引仍未被使用。一个可能的解决方案是创建一个像这样的复合索引：

CREATE INDEX objekte_idx ON objekte(id,rating);

非常感谢你，这是它，因为我还通过其他专栏排序，我曾试图将它们汇总在一起（编号，评级，最低价格），但我想我应该制作2个单独的专栏。我也应该能够通过另一种方式来组织团队。 – Som1

实际上，mysql每个表只能使用一个索引。因此，制作任何不同的索引都不会导致任何进一步的改进。使用mysql，多列索引是要走的路，但是您必须正确地获得列顺序。身份证，评级恰好在这里使用，但评级，ID可能不会！ – e4c5

答

您不需要在此使用GROUP BY。您没有使用聚合函数。所以从查询中删除GROUP BY。删除群组将提高查询性能。也不需要定义0作为限制。

SELECT r.name, l.name, o.typ, o.id, persons, children, description, rating, totalratings, minprice, picture FROM angebote as a 
JOIN objekte as o ON a.fid_objekt = o.id 
JOIN regionen as r ON a.fid_region = r.id 
JOIN laender as l ON a.fid_land = l.id 
WHERE l.slug="aegypten" AND a.letztes_angebot >= 1 
ORDER BY rating DESC LIMIT 20

我确实使用了GROUP BY，所以我没有多次获取同一个对象，一个对象可以在一个区域（或一个国家/地区）的不同子区域中。或者我想我为什么不需要GROUP BY呢？ 0也是更大值的占位符。 – Som1

使用不同的那 –

不幸的是，我必须犯另一个错误，因为只有删除GROUP BY不会有帮助。 – Som1

缓慢的MySQL查询，按订单分组的限制

相关推荐