时间戳

首页 > 解决方案 > mysql 查询需要大约一分钟使用 between

mysql - mysql 查询需要大约一分钟使用 between

问题描述

我有一个连接四个表的查询，但我不明白为什么它需要大约一分钟。我知道之间的日期超过3年。我真的不知道该怎么做以及如何优化此查询以获得更好的性能。有人可以给我建议做什么吗？将附上查询和查询的解释。

SELECT 
                `purchase_order`.`id`, 
                `customer`.`name` AS customer_name,
                `purchase_order`.`po_date`,
                `purchase_order`.`po_number`,
                `purchase_order`.`customer_id` AS customer_id ,
                `customer`.`name` AS customer_name, 
                `purchase_order`.`status` AS po_status, 
                `purchase_order_items`.`product_id`, 
                `purchase_order_items`.`po_item_name`, 
                `product`.`weight` as product_weight,
                `product`.`pending` as product_pending,
                `product`.`company_owner` as company_owner,
                `purchase_order_items`.`uom`, 
                `purchase_order_items`.`po_item_type`,
                `purchase_order_items`.`order_sequence`, 
                `purchase_order_items`.`pending_balance`, 
                `purchase_order_items`.`quantity`, 
                `purchase_order_items`.`notes`, 
                `purchase_order_items`.`status` AS po_item_status,
                `purchase_order_items`.`id` AS po_item_id 
              FROM  purchase_order
          INNER JOIN customer ON `customer`.`id` = `purchase_order`.`customer_id`
          INNER JOIN purchase_order_items ON `purchase_order_items`.`po_id` = `purchase_order`.`id` 
          INNER JOIN product ON `purchase_order_items`.`product_id` = `product`.`id`  WHERE  
                    `purchase_order_items`.`product_id` = '121' AND
                    `purchase_order`.`po_date`  
                    BETWEEN '2016-01-01' AND '2019-02-28' AND
                    `purchase_order_items`.`status` IN('Pending','Incomplete') 
               ORDER BY `purchase_order`.`po_date` DESC LIMIT 0, 20

我也不确定如何处理解释，我仍在尝试理解为什么会这样以及如何优化查询。我希望有人能在这方面帮助我。

在此处输入图像描述

标签： mysql

解决方案

看起来你的查询很好。使用表的别名重写为更短但仍然可读的“po”与“purchase_order”、“poi”与“purchase_order_items”

您的 purchase_order 表应至少在日期 THEN id 上有一个索引。因此，您的 WHERE 子句可以根据日期进行优化，还可以以 po.id 作为基础，通过 id 加入您的采购订单项目表。

接下来，采购订单项目将有助于（ po_id、product_id、status ）上的索引，以帮助优化该连接标准。

Purchase_Order index (po_date, id )
Purchase_Order_Items index ( po_id, product_id, status )

SELECT 
        po.id, 
        c.name AS customer_name,
        po.po_date,
        po.po_number,
        po.customer_id AS customer_id ,
        c.name AS customer_name, 
        po.status AS po_status, 
        poi.product_id, 
        poi.po_item_name, 
        p.weight as product_weight,
        p.pending as product_pending,
        p.company_owner as company_owner,
        poi.uom, 
        poi.po_item_type,
        poi.order_sequence, 
        poi.pending_balance, 
        poi.quantity, 
        poi.notes, 
        poi.status AS po_item_status,
        poi.id AS po_item_id 
    FROM 
        purchase_order po
            INNER JOIN customer c
                ON po.customer_id = c.id
            INNER JOIN purchase_order_items poi
                ON po.id = poi.po_id 
                AND poi.product_id = '121' 
                AND poi.status IN ('Pending','Incomplete') 
                INNER JOIN product p 
                    ON poi.product_id = p.id  
    WHERE 
        po.po_date BETWEEN '2016-01-01' AND '2019-02-28' 
    ORDER BY 
        po.po_date DESC 
    LIMIT 
        0, 20

如果这没有帮助，您的表格顺序似乎没问题，但引擎可能试图为您考虑太多。您可以尝试添加额外的关键字“STRAIGHT_JOIN”（特定于 mysql）。

select STRAIGHT_JOIN ( ... rest of query )

这告诉 mySQL 按照我给你的表顺序运行查询。

推荐阅读