用左连接查询大表真的很慢
The following query takes approximately 12 seconds to execute. I have tried optimizing but was not able to. The table to be joined is pretty large (> 8.000.000 records).
执行以下查询大约需要12秒。我试过优化,但没能做到。要连接的表相当大(> 8.000.000记录)。
SELECT
p0_.id AS id_0,
p0_.ean AS ean_1,
p0_.brand AS brand_2,
p0_.type AS type_3,
p0_.retail_price AS retail_price_4,
p0_.target_price AS target_price_5,
min(NULLIF(c1_.delivery_price, 0)) AS sclr_6,
COALESCE(((p0_.target_price - min(NULLIF(c1_.delivery_price, 0))) / p0_.target_price * -100), 0) AS sclr_7
FROM product p0_
LEFT JOIN crawl c1_ ON (
c1_.product_ean = p0_.ean AND (
c1_.crawl_date = p0_.last_crawl_date OR
p0_.last_crawl_date IS NULL
)
AND c1_.source_id IN (
SELECT o2_.source_id AS sclr_8
FROM organisation_source o2_
WHERE o2_.organisation_id = 5
)
)
WHERE p0_.organisation_id = 5 GROUP BY p0_.ean
I already tried writing the query in a lot of different ways, but unfortunately did not give me any performance win. If I remove the subquery in the last AND it does not help either.
我已经尝试过用许多不同的方式编写查询,但不幸的是,我没有获得任何性能优势。如果我在最后一个查询中删除子查询,它也没有帮助。
See below the output of the EXPLAIN statement:
请参阅解释语句的输出:
+------+--------------+-------+------+---------------------------------------------------+------------------+---------+------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------+------+---------------------------------------------------+------------------+---------+------------------------+--------+-------------+
| 1 | PRIMARY | p0_ | ref | uniqueConstraint,IDX_D34A04AD9E6B1585 | uniqueConstraint | 5 | const | 69 | Using where |
| 1 | PRIMARY | c1_ | ref | IDX_product_ean,IDX_crawl_date | IDX_product_ean | 62 | admin_pricev-p.p0_.ean | 468459 | Using where |
| 2 | MATERIALIZED | o2_ | ref | PRIMARY,IDX_DD91A56E9E6B1585,IDX_DD91A56E953C1C61 | PRIMARY | 4 | const | 1 | Using index |
+------+--------------+-------+------+---------------------------------------------------+------------------+---------+------------------------+--------+-------------+
See below the CREATE TABLE statements of the product and crawl tabel:
请参见产品的CREATE TABLE语句和爬行表:
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`organisation_id` int(11) DEFAULT NULL,
`ean` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`brand` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`retail_price` decimal(10,2) NOT NULL,
`target_price` decimal(10,2) NOT NULL,
`last_crawl_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniqueConstraint` (`organisation_id`,`ean`),
KEY `IDX_D34A04AD9E6B1585` (`organisation_id`),
KEY `IDX_target_price` (`target_price`),
KEY `IDX_ean` (`ean`),
KEY `IDX_type` (`type`),
KEY `IDX_last_crawl_date` (`last_crawl_date`),
CONSTRAINT `FK_D34A04AD9E6B1585` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=927 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `crawl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source_id` int(11) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`product_ean` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`crawl_date` datetime NOT NULL,
`takeaway_price` decimal(10,2) DEFAULT NULL,
`delivery_price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `IDX_B4E9F1C2953C1C61` (`source_id`),
KEY `IDX_B4E9F1C2B092A811` (`store_id`),
KEY `IDX_product_ean` (`product_ean`),
KEY `IDX_takeaway_price` (`takeaway_price`),
KEY `IDX_crawl_date` (`crawl_date`),
CONSTRAINT `FK_B4E9F1C2953C1C61` FOREIGN KEY (`source_id`) REFERENCES `source` (`id`),
CONSTRAINT `FK_B4E9F1C2B092A811` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8606874 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Anyone has any idea how to improve the performance of this query? Many thanks! If more information is needed please let me know!
有人知道如何改进这个查询的性能吗?很多谢谢!如果需要更多的信息,请告诉我!
2 个解决方案
#1
2
You can probably simplify the query to:
您可以将查询简化为:
SELECT . . .
FROM product p0_ LEFT JOIN
crawl c1_
ON c1_.product_ean = p0_.ean AND
c1_.crawl_date = p0_.last_crawl_date AND
EXISTS (SELECT 1
FROM organisation_source o2_
WHERE o2_.organisation_id = 5 AND c1_.source_id = o2_.source_id
)
WHERE p0_.organisation_id = 5
GROUP BY p0_.ean;
The p0_.last_crawl_date IS NULL
is presumably unnecessary. A LEFT JOIN
will keep all rows in the first table even when there is a NULL
in a comparison. Your logic matches all rows in the second table (that meet the other conditions). That may be what you want, but I am guessing not.
p0_。假定last_crawl_date为NULL是不必要的。左连接将保留第一个表中的所有行,即使比较中有NULL。逻辑匹配第二个表中的所有行(满足其他条件)。这也许是你想要的,但我猜不是。
In MySQL, exists
is sometimes faster than in
, which is why I've rewritten that portion.
在MySQL中,exist的速度有时比In快,这就是我重写这一部分的原因。
For this query, you can speed it up using indexes: product(organisation_id, ean, last_crawl_date)
, crawl(product_ean, crawl_date, source_id)
and organisation_source(source_id, organisation_id)
.
对于这个查询,您可以使用索引来加速它:product(organization_id、ean、last_crawl_date)、抓取(product_ean、crawl_date、source_id)和organization_source (source_id、organization_id)。
更多相关文章
- Sql查询性能限制行数之间的差异
- 高性能Mysql第三版(第六章查询性能优化)
- 腾讯云数据库团队:GreenPlum简单性能测试与分析--续
- MyBatis排序时使用order by 动态参数时需要注意,用$而不是# 用$传
- mysql索引性能测试
- MySql 优化之like语句
- c语言把mysql数据库语句和变量封装为一个语句
- sql语句之union与join的区别
- MySQL中一些查看事务和锁情况的常用语句