为什么多个连接在mysql中有很多行太长？ [英] Why multiple joins takes having many rows too long in mysql?

问题描述

我在这个数据库中编写了很多存储过程，几乎所有包含2个以上连接的查询运行速度都很慢，需要15-20秒才能执行。



请注意我无法编辑架构。

我在这里分享一个来自下面的程序，

帮助找出错误，如果有任何或建议方法来克服这个问题。



我尝试过：

I have written many stored procedures in this database and almost all queries which containing more than 2 joins are running very slows and takes 15-20 seconds to execute.

Please note I can not edit schema.
I am sharing here one from those procedure as below,
help to find out mistake if any or suggest method to overcome this problem.

What I have tried:

<pre>-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_BarcodeScanDetail`(IN scancode TEXT)
BEGIN


IF ((select Barcode from location_barcode where Barcode = scancode limit 1) = scancode)
THEN

SELECT distinct hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME,
hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',BARCODE 
FROM Master sm inner join stock_master hg
ON sm.code = hg.code 
inner join location_barcode lb 
on hg.SKU_LOC_STOCK_NO = lb.sku_Loc_Stock_No
WHERE lb.Barcode = scancode 
and SOH/COMPRESSION_FACTOR >0 limit 1;

ELSEIF ((select EAN_code from ean_sku_link where EAN_code = scancode limit 1)= scancode)
THEN

SELECT hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME,
hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',EAN_code 
FROM Master sm inner join stock_master hg
ON sm.code = hg.code 
inner join ean_sku ean 
on hg.SKU_CODE = ean.SKU_CODE
WHERE ean.EAN_code = scancode 
and SOH/COMPRESSION_FACTOR >0 limit 1;
end if;
END

在此程序中; IF和ELSE语句中的子查询发现扫描的条形码是全局的（公司生成的）或本地的（个人生成的）。此外，如果扫描的条形码是本地的，则执行第一主查询，如果扫描的条形码是全局的，则执行第二主查询。我使用内部联接为Master，stock_master，location_barcode/ean_sku。

执行子查询需要5-8秒，主查询需要10-12秒，因此整个过程需要15-20秒。



table_name行数

location_barcode 378150

master 76573

stock_master 280001

ean_sku 18233

In this procedure; the subqueries in IF and ELSE statement finds that scanned barcode is global(company generated) or local(personally generated). Also if scanned barcode is local then executed 1st main query and if scanned barcode is global then execute 2nd main query. I used inner join for "Master" , "stock_master" ,"location_barcode"/"ean_sku".
It takes 5-8 seconds for executing subqueries and 10-12 seconds for main queries, hence 15-20 seconds for whole procedure.

table_name rows count
location_barcode 378150
master 76573
stock_master 280001
ean_sku 18233

推荐答案

CREATE DEFINER =`root` @`localhost` PROCEDURE`Proc_BarcodeScanDetail`（IN scancode TEXT）
BEGIN


IF（（从location_barcode中选择条形码，其中Barcode = scancode limit 1）= scancode）
THEN

SELECT distinct hg.SKU_code，SKU_NAME，COMPRESSION_FACTOR，REF_BATCH_CODE，EXPIRY_DATETIME，
hg.MRP，SOH / COMPRESSION_FACTOR为'SOH'，BARCODE
来自主sm内连接stock_master hg
ON sm.code = hg.code
内连接location_barcode lb
on hg.SKU_LOC_STOCK_NO = lb. sku_Loc_Stock_No
WHERE lb.Barcode = scancode
和SOH / COMPRESSION_FACTOR> 0 limit 1;

ELSEIF（（从ean_sku_link选择EAN_code，其中EAN_code =扫描码限制1）=扫描码）
THEN

SELECT hg.SKU_code，SKU_NAME，COMPRESSION_FACTOR，REF_BATCH_CODE，EXPIRY_DATETIME ，
hg.MRP，SOH / COMPRESSION_FACTOR为'SOH'，EAN_code
FROM Master sm inner join stock_master hg
ON sm.code = hg.code
inner join ean_sku ean
on hg.SKU_CODE = ean.SKU_CODE
WHERE ean.EAN_code = scancode
和SOH / COMPRESSION_FACTOR> 0 limit 1;
结束如果;
结束

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_BarcodeScanDetail`(IN scancode TEXT) BEGIN IF ((select Barcode from location_barcode where Barcode = scancode limit 1) = scancode) THEN SELECT distinct hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME, hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',BARCODE FROM Master sm inner join stock_master hg ON sm.code = hg.code inner join location_barcode lb on hg.SKU_LOC_STOCK_NO = lb.sku_Loc_Stock_No WHERE lb.Barcode = scancode and SOH/COMPRESSION_FACTOR >0 limit 1; ELSEIF ((select EAN_code from ean_sku_link where EAN_code = scancode limit 1)= scancode) THEN SELECT hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME, hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',EAN_code FROM Master sm inner join stock_master hg ON sm.code = hg.code inner join ean_sku ean on hg.SKU_CODE = ean.SKU_CODE WHERE ean.EAN_code = scancode and SOH/COMPRESSION_FACTOR >0 limit 1; end if; END

在此程序中; IF和ELSE语句中的子查询发现扫描的条形码是全局的（公司生成的）或本地的（个人生成的）。此外，如果扫描的条形码是本地的，则执行第一主查询，如果扫描的条形码是全局的，则执行第二主查询。我使用内部联接为Master，stock_master，location_barcode/ean_sku。

执行子查询需要5-8秒，主查询需要10-12秒，因此整个过程需要15-20秒。



table_name行数

location_barcode 378150

master 76573

stock_master 280001

ean_sku 18233

In this procedure; the subqueries in IF and ELSE statement finds that scanned barcode is global(company generated) or local(personally generated). Also if scanned barcode is local then executed 1st main query and if scanned barcode is global then execute 2nd main query. I used inner join for "Master" , "stock_master" ,"location_barcode"/"ean_sku".
It takes 5-8 seconds for executing subqueries and 10-12 seconds for main queries, hence 15-20 seconds for whole procedure.

table_name rows count
location_barcode 378150
master 76573
stock_master 280001
ean_sku 18233

这篇关于为什么多个连接在mysql中有很多行太长？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！