Question

我正在使用MySQL,我有一个详细的表格,包括我的当地记录200万份,在问询之后,为200万份记录提取了约2个雷区,以产生产出,但在生产上,我当天就在该表中有8 000万份记录。

I already have index on column like StatusDateTime, ProductionFacility and ProductionStatusNo .

甚至在表格中,根据<代码>第对数值,如0,1,2,等等,在12年之前,我也有分数。

查询涉及两个参数:p_StatusDatetime,其他参数为p_UnassignproductionFaciltiy。我使用工会,因为有三种不同的景点,计算每个身份的计算和计算。地位0和状况1具有特殊性,如果我不需要在分询问中使用某些记录,但在我计算其他状况时不需要这样做。

SELECT  Unassigned ,0,p_StatusDateTime,
        COUNT(DISTINCT UniqueFormId )   ,    
        COUNT(DISTINCT UniqueFormId )       
        FROM detail
        WHERE ProductionFacility=p_UnassignedProductionFaciltiy and DATE(StatusDateTime)<=p_StatusDateTime
        and UniqueFormId not in ( select DISTINCT UniqueFormId from detail as exd where DATE(exd.StatusDateTime) <= p_StatusDateTime and exd.ProductionStatusNo!=1 )
        
        
      

union 
        SELECT ps.Status,ps.Id,p_StatusDateTime,
        CASE WHEN totalcount is null then 0 else totalcount end as count,
        (CASE WHEN totalcount is null then 0 else totalcount end) +
        COALESCE( (SELECT cumulative_count  FROM dailysummary WHERE ProductionStatusNo=ps.Id and DATE(StatusDateTime)=    DATE_SUB(p_StatusDateTime, INTERVAL 1 DAY) ),0)  as cumulative_count
         FROM productionstatus as ps left join (
        SELECT count(DISTINCT UniqueFormId) as totalcount ,
        DATE(p_StatusDateTime) as StatusDateTime,
        ProductionStatusNo 
        FROM detail as d
        WHERE DATE(StatusDateTime)=p_StatusDateTime 
         and UniqueFormId not in ( select DISTINCT UniqueFormId from detail as exd where DATE(exd.StatusDateTime) < p_StatusDateTime and exd.ProductionStatusNo=d.ProductionStatusNo )
        group by ProductionStatusNo ) as d ON ps.Id= d.ProductionStatusNo
        WHERE ps.Id =1 

UNION

        SELECT     ps.Status,     ps.Id,     p_StatusDateTime as StatusDateTime,     COALESCE(c, 0) as c,      COALESCE(c, 0) as c
        FROM  productionstatus as ps
        LEFT JOIN (    SELECT         COALESCE(COUNT(*), 0) as c,        ed.psn 
        FROM (
        SELECT
            UniqueFormId,MAX(productionStatusNo) as psn  FROM   detail
        WHERE   DATE(statusdatetime) <= p_StatusDateTime
        GROUP BY UniqueFormId
            ) as ed
        GROUP BY ed.psn
        ) as l ON ps.Id = l.psn
        WHERE
            ps.Id not in ( 0,1);

The table schema as following:

CREATE TABLE `detail` (
  `Id` int NOT NULL AUTO_INCREMENT,
  
  `EINNo` varchar(45) NOT NULL,
  `EmployeeNo` varchar(45) NOT NULL,
  `Form` varchar(45) NOT NULL,
  `ProductionStatus` varchar(45) NOT NULL,
  `ProductionStatusNo` int NOT NULL,
  
  `StatusDateTime` varchar(80) DEFAULT NULL,
  
  `UniqueFormId` varchar(450) NOT NULL,
  `ProductionFacility` varchar(450) NOT NULL,
  
  `IMB` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`,`ProductionStatusNo`),
  KEY `idx_detail_EINNo` (`EINNo`),
  KEY `idx_detail_EmployeeNo` (`EmployeeNo`),
  KEY `idx_detail_Form` (`Form`),
  KEY `idx_ProductionStatus` (`ProductionStatus`),
  KEY `IX_detail_Covering` (`ProductionStatus`,`StatusDateTime`,`Id`),
  KEY `idx_detail_UniqueFormId` (`UniqueFormId`),
  KEY `idx_detail_UniqueFormId1` (`UniqueFormId`),
  KEY `idx_detail_ProductionFacility` (`ProductionFacility`),
  KEY `idx_detail_StatusDateTime` (`StatusDateTime`),
  KEY `idx_detail_ProductionStatusNo` (`ProductionStatusNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`ProductionStatusNo`)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (13) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

here is the code for stored procedure in which this query runs

CREATE DEFINER=`root`@`localhost` PROCEDURE `Dashboard_Count`(
IN p_StatusDateTime Date, 
IN p_UnassignedProductionFaciltiy nvarchar(500)

)
BEGIN
    
         
    
        INSERT INTO dailysummary(ProductionStatus ,ProductionStatusNo,StatusDateTime ,count , cumulative_count ) 
        
         SELECT  Unassigned ,0,p_StatusDateTime,
        COUNT(DISTINCT UniqueFormId )   ,    
        COUNT(DISTINCT UniqueFormId )       
        FROM detail
        WHERE ProductionFacility=p_UnassignedProductionFaciltiy and DATE(StatusDateTime)<=p_StatusDateTime
        and UniqueFormId not in ( select DISTINCT UniqueFormId from detail as exd where DATE(exd.StatusDateTime) <= p_StatusDateTime and exd.ProductionStatusNo!=1 )
        
        
      

union 
SELECT ps.Status,ps.Id,p_StatusDateTime,
        CASE WHEN totalcount is null then 0 else totalcount end as count,
        (CASE WHEN totalcount is null then 0 else totalcount end) +
        COALESCE( (SELECT cumulative_count  FROM dailysummary WHERE ProductionStatusNo=ps.Id and DATE(StatusDateTime)=    DATE_SUB(p_StatusDateTime, INTERVAL 1 DAY) ),0)  as cumulative_count
         FROM productionstatus as ps left join (
        SELECT count(DISTINCT UniqueFormId) as totalcount ,
        DATE(p_StatusDateTime) as StatusDateTime,
        ProductionStatusNo 
        FROM detail as d
        WHERE DATE(StatusDateTime)=p_StatusDateTime 
         and UniqueFormId not in ( select DISTINCT UniqueFormId from detail as exd where DATE(exd.StatusDateTime) < p_StatusDateTime and exd.ProductionStatusNo=d.ProductionStatusNo )
        group by ProductionStatusNo ) as d ON ps.Id= d.ProductionStatusNo
        WHERE ps.Id =1 

        UNION

        SELECT     ps.Status,     ps.Id,     p_StatusDateTime as StatusDateTime,     COALESCE(c, 0) as c,      COALESCE(c, 0) as c
        FROM  productionstatus as ps
        LEFT JOIN (    SELECT         COALESCE(COUNT(*), 0) as c,        ed.psn 
        FROM (
        SELECT
            UniqueFormId,MAX(productionStatusNo) as psn  FROM   detail
        WHERE   DATE(statusdatetime) <= p_StatusDateTime
        GROUP BY UniqueFormId
            ) as ed
        GROUP BY ed.psn
        ) as l ON ps.Id = l.psn
        WHERE
            ps.Id not in ( 0,1);
END

Answer 1

Instead of slogging through the Fact table, build and maintain a Summary Table containing daily subtotals.

你们是否需要列入当今的计数? (有两种处理方式)。

Partitioning is probably useless unless you plan to delete "old" data. Then it only helps the big delete (by using DROP PARTITION instead of a big DELETE).

编辑:

KEY `idx_ProductionStatus` (`ProductionStatus`),  // DROP this
KEY `IX_detail_Covering`   (`ProductionStatus`,`StatusDateTime`,`Id`),

为什么你们两次选择:
```
COUNT(DISTINCT UniqueFormId )
```
请在每个栏目中填写表格。我在解释这一点时遇到麻烦:
```
WHERE  DATE(statusdatetime) <= p_StatusDateTime
```
此外,sargable。也许我们可以确定这一点。

而且,我可以不分析指数。
您正在使用<条码>LEFT JOIN,但不在<条码>上查询。也许更适当的是,一个简单的<代码>JOIN?
在此情况下不需要<编码>COALESCE:
```
COALESCE(COUNT(*), 0) as c
```

NOT IN (SlectT ...... >) 往往效率不高。提出

NOT EXISTS ( SELECT 1 ... )

or

LEFT JOIN ... WHERE ... IS NULL

友情链接