缓慢的MySQL查询，按限制分组-Java 学习之路

我目前加入5个表来选择20个对象来显示用户，不幸的是如果我使用 GROUP BY 和 ORDER BY 它变得非常慢 .

示例查询看起来像这样：

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 的密钥，Profiling说它使用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 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

laender，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 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 回答

您不需要在此处使用GROUP BY . 您还没有使用聚合函数 . 因此从查询中删除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

回复于 2024-05-06T13:17:20+08:00

2
首先，这是一个非标准组 . 因此，当您升级到mysql 5.7时它将停止工作 .

最大的问题来自于 objekte 表上没有使用索引的事实 . 更糟糕的是，您在该表的评级字段中进行排序，但索引仍未使用 . 一种可能的解决方案是创建一个这样的复合索引：
```
CREATE INDEX objekte_idx ON objekte(id,rating);
```
回复于 2024-05-06T13:17:20+08:00

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

2 回答

相关问题