MySql的巨大的循环优化

• Z时代
• 2024-01-10
• 分类：问答

我其实对两个主要的查询componed脚本工作。 第一个将返回类似100.000行。MySql的巨大的循环优化

而第二个将要求在每一行结果的第一个（返回1至20行最大）。

我想优化第二，因为它实际上需要1-2秒的事。全球治疗变得很长。

第一请求：

SELECT distinct             
substring_index(aoi.reference,'-',1) as ref1,          
substring_index(aoi.reference,'-',-1) as ref2, 
DATE_FORMAT(ao.date_contractuelle,'%Y-%m-%d') as date  
FROM xxx ao                 
JOIN xxx as t2                
ON ao.package_id = t2.package OR (t2.package IS NULL AND ao.package_id IS NULL) 
AND ao.ordertype_id = t2.`type`             
JOIN xxxx aoi ON ao.id = aoi.order_id          
JOIN xxxx stk ON aoi.reference = stk.id_stock_sct        
JOIN xxxx usr ON usr.id = ao.recipient_id           
WHERE                    
t2.annee = DATE_FORMAT(ao.date_contractuelle,'%Y')        
AND ao.status_id = 5                
AND ao.ordertype_id not in (4,11,13)            
AND ao.validationDate >= DATE_SUB(NOW(),INTERVAL 2 YEAR)       
AND stk.position_orli IS NOT NULL             
AND aoi.`type` in ('classique','tu')            
AND aoi.exceptionalSize_id IS NULL 

第二请求：

Select distinct pos, qte from(            
SELECT stk.position_orli as pos, sum(aoi.quantity) as qte ,stk.id_nature_stock 
FROM xxx aoi               
JOIN xxx ao ON ao.id = aoi.order_id          
JOIN xxx stk ON stk.id_stock_sct = aoi.reference        
-- HERE ARE THE VARS FROM THE FIRST REQUEST           
AND aoi.reference like 'ref1-%-ref2'  
AND ao.date_contractuelle LIKE 'date%' 
-- -----------------------------------------      
AND stk.position_orli IS NOT NULL                     
AND aoi.`type` in ('classique','tu')           
AND aoi.exceptionalSize_id IS NULL            
AND ao.status_id = 5               
AND ao.ordertype_id not in (4,11,13)           
GROUP BY stk.position_orli, stk.id_nature_stock          
) as tbl                  
) 

的表（只与重要的数据）：

`aoi` 8 500 000 lines 
(
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `order_id` INT(11) NOT NULL, 
    `quantity` INT(11) NOT NULL, 
    `reference` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', 
    `type` ENUM('classique','te','surmesure','tu') NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', 
    `exceptionalSize_id` INT(11) NULL DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    CONSTRAINT `FK_x` FOREIGN KEY (`exceptionalSize_id`) REFERENCES `aoe` (`id`), 
    CONSTRAINT `FK_x` FOREIGN KEY (`order_id`) REFERENCES `ao` (`id`) 
) 
`ao` 1 300 000 lines 
(
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `date_contractuelle` DATETIME NULL DEFAULT NULL, 
    `recipient_id` INT(11) NULL DEFAULT NULL, 
    `status_id` INT(11) NULL DEFAULT NULL, 
    `ordertype_id` INT(11) NULL DEFAULT NULL, 
    `validationDate` DATETIME NULL DEFAULT NULL, 
    `package_id` INT(11) NULL DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    CONSTRAINT `FK_x` FOREIGN KEY (`recipient_id`) REFERENCES `usr` (`id`), 
    CONSTRAINT `FK_x` FOREIGN KEY (`package_id`) REFERENCES `pc` (`id`), 
    CONSTRAINT `FK_x` FOREIGN KEY (`ordertype_id`) REFERENCES `ot` (`id`) 
) 
`stk` 180 000 lines 
(
    `id_stock_sct` VARCHAR(40) NOT NULL COLLATE 'utf8_unicode_ci', 
    `id_nature_stock` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci', 
    `position_orli` ENUM('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20') NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', 
    PRIMARY KEY (`id_stock_sct`, `id_nature_stock`) 
) 
`t2` 2600 lines 
(
    `id` INT(40) NOT NULL AUTO_INCREMENT, 
    `package` INT(11) NULL DEFAULT NULL, 
    `type` INT(11) NOT NULL, 
    `annee` YEAR NULL DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    CONSTRAINT `FK__ot` FOREIGN KEY (`type`) REFERENCES `ot` (`id`), 
    CONSTRAINT `FK__pc` FOREIGN KEY (`package`) REFERENCES `pc` (`id`) 
) 
`usr` 208 000 lines 
(
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (`id`) 
) 

由于

回答：

INDEX(exceptionalSize, type) -- aoi 
INDEX(status_id, ordertype_id) -- ao 

GROUP BY position_orli, id_nature_stock 

AND ao.date_contractuelle LIKE 'date%' 

以上是 MySql的巨大的循环优化 的全部内容， 来源链接： utcz.com/qa/259669.html