如何改进此查询？
提问于 2013-05-14 11:29:11
大约一年前，我介绍了一个查询，它返回一种“客户也购买”数据集。然而，在它运行得相当快的时候，它变得非常慢，有时甚至需要5秒或更长的时间。
SELECT p.*, COUNT(*) AS total
FROM orders_products AS op
JOIN products AS p ON p.products_id = op.products_id
JOIN (
SELECT orders_id
FROM orders_products
WHERE products_id = 100
) AS opf ON opf.orders_id = op.orders_id
WHERE op.products_id <> 100
GROUP BY products_id
ORDER BY total DESC
LIMIT 5;+------+-------------+-----------------+--------+-----------------------+-------------+---------+------------------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-----------------+--------+-----------------------+-------------+---------+------------------------------------+------+----------+---------------------------------+
| 1 | SIMPLE | orders_products | ref | products_id,orders_id | products_id | 4 | const | 4511 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | op | ref | products_id,orders_id | orders_id | 4 | database.orders_products.orders_id | 2 | 100.00 | Using where |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | database.op.products_id | 1 | 100.00 | |
+------+-------------+-----------------+--------+-----------------------+-------------+---------+--------------------------------------+------+----------+-------------------------------+SHOW CREATE TABLE products：
+----------+----------------------------------------------------------------+
| Table | Create Table
+----------+----------------------------------------------------------------+
| products | CREATE TABLE `products` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`products_model` varchar(128) DEFAULT NULL,
`products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`products_date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`products_last_modified` datetime DEFAULT NULL,
`products_status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added` (`products_date_added`),
KEY `products_model` (`products_model`),
KEY `products_price` (`products_price`),
KEY `products_status` (`products_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------+SHOW INDEXES FROM products
+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products | 0 | PRIMARY | 1 | products_id | A | 4356 | NULL | NULL | | BTREE | | |
| products | 1 | idx_products_date_added | 1 | products_date_added | A | 4356 | NULL | NULL | | BTREE | | |
| products | 1 | products_model | 1 | products_model | A | 4356 | NULL | NULL | YES | BTREE | | |
| products | 1 | products_price | 1 | products_price | A | 1089 | NULL | NULL | | BTREE | | |
| products | 1 | products_status | 1 | products_status | A | 4 | NULL | NULL | | BTREE | | |
+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+SHOW CREATE TABLE orders_products：
+-----------------+--------------------------------------------------------+
| Table | Create Table
+-----------------+--------------------------------------------------------+
| orders_products | CREATE TABLE `orders_products` (
`orders_products_id` int(11) NOT NULL AUTO_INCREMENT,
`orders_id` int(11) NOT NULL DEFAULT '0',
`products_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`orders_products_id`),
KEY `products_id` (`products_id`),
KEY `orders_id` (`orders_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------------------------+SHOW INDEXES FROM orders_products
+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders_products | 0 | PRIMARY | 1 | orders_products_id | A | 3134198 | NULL | NULL | | BTREE | | |
| orders_products | 1 | products_id | 1 | products_id | A | 5014 | NULL | NULL | | BTREE | | |
| orders_products | 1 | orders_id | 1 | orders_id | A | 1567099 | NULL | NULL | | BTREE | | |
+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+有什么明显的东西我错过了吗？在大多数方面，数据库似乎经过了很好的调整。我们正在InnoDB/XtraDB存储引擎下运行MariaDB 5.5.30。
回答 3
Database Administration用户
回答已采纳
发布于 2013-05-14 12:25:02
这个表(orders_products)是一个多对多的表.我认为在这样的表上有2个复合索引是很常见的，因为它在许多常见的查询中都有帮助。
我肯定会在(orders_id, products_id)和(products_id, orders_id)上添加两个(唯一的)索引。
不确定是否将两者都定义为UNIQUE将是MariaDB优化器的进一步改进。
如果没有什么特殊的原因，可以删除自动递增的orders_products_id列。您可以通过订单和产品in标识表中的行。我认为该列只会在表和索引中增加更多的空间，而不会增加值。
Database Administration用户
发布于 2013-05-14 12:22:17
我不知道你的数据库，但希望这能帮上忙
我设置了一些测试数据，因此我有一个包含27,000行数据的orders_products表，因此，我可以从一个可能的10组中随机获得三个条目。
Create Table orders_products (Orders_Id Number, Products_Id Number);
create index orders_products_I1 on orders_products(orders_id);
create index orders_products_I2 on orders_products(products_id);我假设您没有像Oracle这样的复合键，所以只创建了两个索引，每个索引都有一个id。
Declare
P_Id Number(3);
o_id Number(4);
Begin
For o_id In 1000..9999
Loop
P_Id:=trunc(dbms_random.value(1,9))*100;
insert into orders_Products values (o_id, p_id);
P_Id:=trunc(dbms_random.value(1,9))*100;
Insert Into Orders_Products Values (O_Id, P_Id);
P_Id:=trunc(dbms_random.value(1,9))*100;
Insert Into Orders_Products Values (O_Id, P_Id);
End Loop;
Commit;
END;这就是我如何在Oracle中重写您的查询的方法，它只获取项和计数的列表
select p.* from products_id
where products_id in (
select products_id
From Orders_Products Op
where op.orders_id in (select orders_id
from orders_products opf
Where Exists (Select 1
from orders_products ope
Where Products_Id=100)
)
And Op.Products_Id <> 100
Group By Products_Id
order by count(*) desc)
and rownum <=5这在一秒钟内运行(运行时间: 00:00:00.234)，如果使用绑定变量而不是硬编码products_id，那么在Oracle中每次运行速度都会更快，因此如果数据库中有绑定变量，这可能会有所帮助。如果您已经存在，它只需要找到第一个事件，而不是所有可能匹配的事件。
我忽略的唯一一点是选择项目的所有产品详细信息--可以在此查询的外部放置一个包装器，以便只返回您想要的5个产品的所有产品详细信息，而不是根据排序限制所有行的详细信息。
这是包含总数的内部查询的结果。
PRODUCTS_ID TOTAL
----------- -----
700 3486
600 3406
400 3393
300 3389
200 3322
800 3320
500 3309
Elapsed: 00:00:00.234这是排除它的结果，所以我们可以在外部选择中使用products_id。
PRODUCTS_ID
700
600
400
300
200
800
500 经过: 00:00:00.405
Database Administration用户
发布于 2013-05-14 12:09:13
第一个操作看起来很昂贵，警告标志是Using temporary; Using filesort。考虑在orders_products上添加一个允许在products_id上搜索的索引：
create index IX_OrdersProcucts_ProductId
on orders_products(products_id, orders_id);通过在orders_id之外添加products_id，索引将覆盖：
SELECT orders_id
FROM orders_products
WHERE products_id = 100如果添加此索引，则explain extended输出的第一步应包括Using index。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/42188
复制
腾讯云开发者
