我想总结一下订单。我喜欢以下产品和订购项目:
DROP TABLE IF EXISTS p;
CREATE TABLE p (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`combine` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `combine`(`combine`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS i;
CREATE TABLE i (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`p` int(10) unsigned DEFAULT NULL,
`quantity` decimal(15,2) NOT NULL,
PRIMARY KEY (`id`),
INDEX `p`(`p`)
) ENGINE=InnoDB;
INSERT INTO p SET id=1, combine=NULL;
INSERT INTO p SET id=2, combine=1;
INSERT INTO p SET id=3, combine=1;
INSERT INTO p SET id=4, combine=NULL;
INSERT INTO i SET id=1, p=1, quantity=5;
INSERT INTO i SET id=2, p=1, quantity=2;
INSERT INTO i SET id=3, p=2, quantity=1;
INSERT INTO i SET id=4, p=3, quantity=4;
INSERT INTO i SET id=5, p=4, quantity=2;
INSERT INTO i SET id=6, p=4, quantity=1;
这个想法是产品可以合并,这意味着所有的销售都是为这些产品合并的。例如,这意味着产品1、2和3应该有相同的结果:这些产品的所有销售额相加。所以我做了:
SELECT p.id, SUM(i.quantity)
FROM p
LEFT JOIN p AS p_all ON (p_all.id = p.id OR p_all.combine=p.combine OR p_all.id = p.combine OR p_all.combine = p.id)
LEFT JOIN i ON i.p = p_all.id
GROUP BY p.id;
得出所需结果:
p=1: 12 (i: 1, 2, 3, 4 added)
p=2: 12 (i: 1, 2, 3, 4 added)
p=3: 12 (i: 1, 2, 3, 4 added)
p=4: 3 (i: 5, 6 added)
我的问题是,在实际数据上,或在p\u combine的乘积的联接中,使得查询非常慢。只需不使用组合进行查询就需要0.2秒,而or会使查询持续30秒以上。
如何在mysql中提高查询效率?
补充:实际查询还有一些限制,如:
SELECT p.id, SUM(i.quantity)
FROM p
LEFT JOIN p AS p_all ON (p_all.id = p.id OR p_all.combine=p.combine OR p_all.id = p.combine OR p_all.combine = p.id)
LEFT JOIN i ON i.p = p_all.id
LEFT JOIN orders o ON o.id = i.order
WHERE o.ordered <= '2018-05-10'
AND i.flag=false
AND ...
GROUP BY p.id;
补充:解释真实数据:
+----+-------------+------------------+------------+-------+-----------------------------+---------+---------+--------------+------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+-----------------------------+---------+---------+--------------+------+----------+-------------------------------------------------+
| 1 | SIMPLE | p | NULL | index | PRIMARY,...combine... | PRIMARY | 4 | NULL | 6556 | 100.00 | NULL |
| 1 | SIMPLE | p_all | NULL | ALL | PRIMARY,combine | NULL | NULL | NULL | 6556 | 100.00 | Range checked for each record (index map: 0x41) |
| 1 | SIMPLE | p | NULL | ref | p | p | 5 | p_all.id | 43 | 100.00 | NULL |
+----+-------------+------------------+------------+-------+-----------------------------+---------+---------+--------------+------+----------+-------------------------------------------------+
2条答案
按热度按时间uinbv5nw1#
我不知道您是否有这样做的灵活性,但您可以通过更改p中的“合并”字段来加快速度:
然后您可以将on条件大大简化为:
进行查询(SQLFIDLE):
输出:
xkftehaa2#
使用子查询有时比连接更快。
例如。
您可以将所有约束添加到
i
在“orders”子查询中