选择一条记录，即使该记录的值较低之前花费的时间太长且失败

小编典典

选择一条记录，即使该记录的值较低之前花费的时间太长且失败

sql

自从我实施了向我建议的代码以来，只要我的表一直在增长，sql查询所花费的时间就会越来越长。开始时，要花1000秒执行8秒钟。现在在具有超过25,000行的表上，它只是失败了。

**您可以在这里观看我的查询-http://sqlfiddle.com/#!2/5c480/1/0**

SELECT  a.ID, DATE_FORMAT(a.Time,'%d/%m/%y') AS T, a.SerialNumber, p.Model,
    b.Remain_Toner_Black BeforeCountBlack,
    a.Remain_Toner_Black AfterCountBlack,
    b.Remain_Toner_Cyan BeforeCountCyan,
    a.Remain_Toner_Cyan AfterCountCyan,
b.Remain_Toner_Magenta BeforeCountMagenta,
    a.Remain_Toner_Magenta AfterCountMagenta,
b.Remain_Toner_Yellow BeforeCountYellow,
    a.Remain_Toner_Yellow AfterCountYellow
FROM    
    (
        SELECT  a.ID, 
                a.Time, 
                a.SerialNumber, 
                a.Remain_Toner_Black,
                a.Remain_Toner_Cyan,
                a.Remain_Toner_Magenta,
                a.Remain_Toner_Yellow,
                (
                    SELECT  COUNT(*)
                    FROM    Reports c
                    WHERE   c.SerialNumber = a.SerialNumber AND
                            c.ID <= a.ID) AS RowNumber
        FROM    Reports a
    ) a
    LEFT JOIN
    (
        SELECT  a.ID, 
                a.Time, 
                a.SerialNumber, 
                a.Remain_Toner_Black,
                a.Remain_Toner_Cyan,
                a.Remain_Toner_Magenta,
                a.Remain_Toner_Yellow,
                (
                    SELECT  COUNT(*)
                    FROM    Reports c
                    WHERE   c.SerialNumber = a.SerialNumber AND
                            c.ID <= a.ID) AS RowNumber
        FROM    Reports a
    ) b ON a.SerialNumber = b.SerialNumber AND
            a.RowNumber = b.RowNumber + 1
INNER JOIN Printers p ON a.SerialNumber = p.SerialNumber
INNER JOIN Customers c ON p.IP = c.IP AND c.Company = 5
WHERE   (b.Remain_Toner_Black < a.Remain_Toner_Black AND b.Remain_Toner_Black >= 0) OR (b.Remain_Toner_Cyan < a.Remain_Toner_Cyan AND b.Remain_Toner_Cyan >= 0) OR (b.Remain_Toner_Magenta < a.Remain_Toner_Magenta AND b.Remain_Toner_Magenta >= 0) OR (b.Remain_Toner_Yellow < a.Remain_Toner_Yellow AND b.Remain_Toner_Yellow >= 0)

我需要处理以下3个表，以便仅选择属于具有ID的特定公司的打印机。

报告：

ID         SerialNumber         Remain_Toner_Black   
29881      Z30PBAHBB00034E      58   
30001      Z30PBAHBB00034E      98
30200      Z30PBAHBB00034E      70
30205      BVCfdgdfgdf329F      50
30207      BVCfdgdfgdf329F      40
30210      Z30PBAHBB00034E      50
30301      Z30PBAHBB00034E      100

打印机：

IP                 SerialNumber         Customer 
80.179.228.81      Z30PBAHBB00034E      52

顾客：

ID         IP                      Company        
52         80.179.228.81           5

我的查询工作完美，并返回：

ID         SerialNumber            BEFORECOUNTBLACK         AFTERCOUNTBLACK        
30001      Z30PBAHBB00034E         58                       98
30301      Z30PBAHBB00034E         50                       100

但同样，现在当我在表中有25,000行的Reports表上运行它时，它会失败。

阅读 150

2021-05-30

共1个答案

小编典典

这是您的问题1的解决方案，它将运行得更快，因为您有许多全表扫描和相关子查询。在这里，您最多只进行一次表扫描（可能还有一个临时表，这取决于您的数据量和拥有的内存量）。我认为您可以在此处轻松调整以适应您的问题。问题2（我还没有真正读过）可能也得到了回答，因为现在添加起来很容易where date_column = whatever

select * from (
    select
    t.*,
    if(@prev_toner < Remain_Toner_Black and @prev_sn = SerialNumber, 1, 0) as select_it,
    @prev_sn := SerialNumber,
    @prev_toner := Remain_Toner_Black
    from
    Table1 t
    , (select @prev_toner:=0, @prev_sn:=SerialNumber from Table1 order by SerialNumber limit 1) var_init
    order by SerialNumber, id
) sq  
where select_it = 1

看到它在一个 [sqlfiddle](http://sqlfiddle.com/#!2/8783c/1/0)

编辑：

解释：

用这条线

    , (select @prev_toner:=0, @prev_sn:=SerialNumber from Table1 order by SerialNumber

我们只是初始化变量@prev_toner，并@prev_sn在运行中。等同于根本不在查询中包含此行，而是在查询之前编写

SET @prev_toner = 0;
SET @prev_sn = (select serialnumber from your_table order by serialnumber limit 1);
SELECT ...

那么，为什么查询要为@prev_sn分配一个值，又为什么要按序列号排序？顺序非常重要。没有顺序，就不能保证返回行的顺序。同样，我们将使用变量访问前一行的值，因此将相同的序列号“分组”非常重要。

select子句中的列是一个接一个地求值的，因此首先选择此行很重要

if(@prev_toner < Remain_Toner_Black and @prev_sn = SerialNumber, 1, 0) as select_it,

在选择这两行之前

@prev_sn := SerialNumber,
@prev_toner := Remain_Toner_Black

这是为什么？最后两行仅将当前行的值分配给变量。为此，在这一行

if(@prev_toner < Remain_Toner_Black and @prev_sn = SerialNumber, 1, 0) as select_it,

变量仍保留前几行的值。我们在这里所做的无非就是说“如果Remain_Toner_Black列中的前一行值小于当前行中的值，
并且前几行的序列号与实际行的序列号相同，则返回1，否则返回0 。”

然后，我们可以简单地在外部查询中说“选择每一行，其中上面的返回1”。

根据您的查询，您不需要所有这些子查询。它们非常昂贵且不必要。其实这很疯狂。在查询的这一部分

    SELECT  a.ID, 
            a.Time, 
            a.SerialNumber, 
            a.Remain_Toner_Black,
            a.Remain_Toner_Cyan,
            a.Remain_Toner_Magenta,
            a.Remain_Toner_Yellow,
            (
                SELECT  COUNT(*)
                FROM    Reports c
                WHERE   c.SerialNumber = a.SerialNumber AND
                        c.ID <= a.ID) AS RowNumber
    FROM    Reports a

您选择 整个表格， 并 为每一行
计算该组中的行。那是一个依赖的子查询。所有这些只是为了拥有某种行号。然后再次进行此操作，以使您可以将这两个临时表连接起来以获得上一行。真的，难怪性能太差了。

那么，如何针对您的查询调整我的解决方案？我没有使用用于获取Remain_Toner_Black的上一行的一个变量，而是将四个用于黑色，青色，品红色和黄色。就像您一样，只需加入“打印机和客户”表即可。不要忘记订购的顺序，您已经完成了。

2021-05-30