使用一些LEFT OUTER JOINS优化MySQL查询
使用一些LEFT OUTER JOINS优化MySQL查询
提问于 2018-08-27 05:14:11
我有一个分类广告网站，想要在我的服务器中优化最密集的查询。我对MySQL（或任何其他数据库）没有太多经验。
这是完整的列表广告：
SELECT
`category`.`id` AS `category_id`,
`category`.`subcat` AS `category_name`,
`category`.`desc` AS `category_desc`,
`category`.`name` AS `category_pretty_name`,
`ads`.`id`,
`ads`.`header`,
`ads`.`price`,
`ads`.`oldprice`,
`ads`.`sellfast`,
`ads`.`foto1`,
`ads`.`subcat_id`,
`ads`.`updated`,
`ads`.`created`,
`ads`.`name`,
`ads`.`phone`,
`ads`.`hits`,
`rating_ad`.`num_rates` AS `num_rates`,
`rating_ad`.`average_rating` AS `average_rating`,
SUBSTR(ads.body, 1, 160) AS body,
`ads`.`user_id`,
`ads`.`hash`,
`ads`.`hidden`,
`ads`.`email`,
`promo`.`promotype` AS `promo_type`,
`promo`.`end_promo`,
`supercategory`.`name` AS `supercategory_name`,
`supercategory`.`id` AS `supercategory_id`
FROM
`ads`
JOIN `category` ON `category`.`id` = `ads`.`subcat_id`
JOIN `supercategory` ON `supercategory`.`id` = `category`.`cat`
LEFT OUTER JOIN `promo` ON `promo`.`ads_id` = `ads`.`id`
LEFT OUTER JOIN (
SELECT
ad_id,
TRUNCATE (AVG(rating), 2) AS average_rating,
COUNT(user_id) AS num_rates
FROM
ads_rating
GROUP BY
ad_id
HAVING
COUNT(user_id) >= 1
) AS rating_ad ON `rating_ad`.`ad_id` = `ads`.`id`
WHERE
`ads`.`sellfast` = '1'
AND `ads`.`subcat_id` = '19'
AND `recycle_bin` = 0
AND `hidden` = 0
ORDER BY
`promo_type` DESC,
`updated` DESC
LIMIT 100;
当我执行EXPLAIN查询时，结果如下：
MySQL EXPLIN结果
因此，我在慢查询日志中看到这消耗大约0.350秒。这是可以优化的吗？
这是SHOW CRETE TABLE：
CREATE TABLE `ads` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subcat_id` mediumint(2) NOT NULL,
`province_id` mediumint(2) NOT NULL,
`price` float(12,2) NOT NULL,
`oldprice` float(12,2) NOT NULL,
`type` enum('1','2','3') NOT NULL,
`header` varchar(200) CHARACTER SET latin1 NOT NULL,
`body` text CHARACTER SET latin1 NOT NULL,
`email` varchar(150) CHARACTER SET latin1 NOT NULL,
`public_email` varchar(150) NOT NULL,
`name` varchar(50) CHARACTER SET latin1 NOT NULL,
`phone` varchar(30) CHARACTER SET latin1 NOT NULL,
`user_id` int(6) NOT NULL,
`created` datetime NOT NULL,
`foto1` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`foto2` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`foto3` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`foto4` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`foto5` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
`updated` datetime NOT NULL,
`hash` varchar(32) CHARACTER SET latin1 NOT NULL,
`hits` int(6) NOT NULL,
`new` tinyint(1) DEFAULT 0,
`sellfast` tinyint(1) DEFAULT 0,
`website` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
`tags` varchar(250) DEFAULT NULL,
`brand_id` smallint(2) NOT NULL,
`hidden` smallint(1) NOT NULL,
`recycle_bin` smallint(1) NOT NULL,
PRIMARY KEY (`id`,`hash`),
KEY `header` (`header`) USING BTREE,
KEY `tags` (`tags`) USING BTREE,
KEY `phone` (`phone`) USING BTREE,
KEY ` subcat_id` (`subcat_id`) USING BTREE,
KEY `province_id` (`province_id`) USING BTREE,
KEY `hidden` (`hidden`) USING BTREE,
KEY `recycle_bin` (`recycle_bin`) USING BTREE,
KEY `price` (`price`) USING BTREE,
KEY `email` (`email`) USING BTREE,
KEY `created` (`created`) USING BTREE,
KEY `updated` (`updated`) USING BTREE,
KEY `user_id` (`user_id`),
KEY `new` (`new`),
KEY `sellfast` (`sellfast`)
) ENGINE=MyISAM AUTO_INCREMENT=839759 DEFAULT CHARSET=utf8
最需要做的是哪个任务呢？
回答 1
Stack Overflow用户
发布于 2018-08-27 14:53:02
请提供SHOW CREATE TABLE; 例如，知道哪个表recycle_bin所在的优化非常重要。
甚至在查看连接之前，通过构建“复合”索引来摆脱“索引合并交叉”：
INDEX(sellfast, subcat_id, recycle_bin, hidden) -- in any order
A LEFT OUTER JOIN（aka LEFT JOIN）本身并不是恶棍。在其中有一个子查询可能是真正的恶棍。这样做可以做几件事，但我不确定任何治疗都适用于你的情况：
- 摆脱
LEFT。但如果失踪，这将失去一些行ads_rating。如果没有LEFT，子查询可能会进行第一次，而不是重复。（如果这是有效的，请先试试。） - 将子查询移动到
SELECT。但是提供了两列，因此不太可能实用。 - 使用
WITH。但这需要8.0版。你用的是什么版本？ CREATE TEMPORARY TABLE对于子查询，然后使用它来代替子查询。（这可能是最好的选择。）
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100002423
复制相关文章
相似问题