首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >如何改进此查询?

如何改进此查询?
EN

Database Administration用户
提问于 2013-05-14 19:29:11
回答 3查看 170关注 0票数 3

大约一年前,我介绍了一个查询,它返回一种“客户也购买”数据集。然而,在它运行得相当快的时候,它变得非常慢,有时甚至需要5秒或更长的时间。

代码语言:javascript
代码运行次数:0
运行
复制
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;

产自explain extended

代码语言:javascript
代码运行次数:0
运行
复制
+------+-------------+-----------------+--------+-----------------------+-------------+---------+------------------------------------+------+----------+---------------------------------+
| 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

代码语言:javascript
代码运行次数:0
运行
复制
+----------+----------------------------------------------------------------+
| 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

代码语言:javascript
代码运行次数:0
运行
复制
+----------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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

代码语言:javascript
代码运行次数:0
运行
复制
+-----------------+--------------------------------------------------------+
| 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

代码语言:javascript
代码运行次数:0
运行
复制
+-----------------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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。

EN

回答 3

Database Administration用户

回答已采纳

发布于 2013-05-14 20:25:02

这个表(orders_products)是一个多对多的表.我认为在这样的表上有2个复合索引是很常见的,因为它在许多常见的查询中都有帮助。

我肯定会在(orders_id, products_id)(products_id, orders_id)上添加两个(唯一的)索引。

不确定是否将两者都定义为UNIQUE将是MariaDB优化器的进一步改进。

如果没有什么特殊的原因,可以删除自动递增的orders_products_id列。您可以通过订单和产品in标识表中的行。我认为该列只会在表和索引中增加更多的空间,而不会增加值。

票数 5
EN

Database Administration用户

发布于 2013-05-14 20:22:17

我不知道你的数据库,但希望这能帮上忙

我设置了一些测试数据,因此我有一个包含27,000行数据的orders_products表,因此,我可以从一个可能的10组中随机获得三个条目。

代码语言:javascript
代码运行次数:0
运行
复制
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。

代码语言:javascript
代码运行次数:0
运行
复制
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中重写您的查询的方法,它只获取项和计数的列表

代码语言:javascript
代码运行次数:0
运行
复制
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个产品的所有产品详细信息,而不是根据排序限制所有行的详细信息。

这是包含总数的内部查询的结果。

代码语言:javascript
代码运行次数:0
运行
复制
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

代码语言:javascript
代码运行次数:0
运行
复制
    700 
    600 
    400 
    300 
    200 
    800 
    500 

经过: 00:00:00.405

票数 1
EN

Database Administration用户

发布于 2013-05-14 20:09:13

第一个操作看起来很昂贵,警告标志是Using temporary; Using filesort。考虑在orders_products上添加一个允许在products_id上搜索的索引:

代码语言:javascript
代码运行次数:0
运行
复制
create index IX_OrdersProcucts_ProductId
    on orders_products(products_id, orders_id);

通过在orders_id之外添加products_id,索引将覆盖:

代码语言:javascript
代码运行次数:0
运行
复制
SELECT orders_id
FROM orders_products
WHERE products_id = 100

如果添加此索引,则explain extended输出的第一步应包括Using index

票数 0
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/42188

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档