[骚年，求教SQL优化]

2012-07-03 03:41:30 242 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

那些年我们一起优化的SQL 数据量较大的时候，如果没有索引，那么数据库只能全表一行一行的遍历判断数据，因此优化SQL的时候，第一步要做的就是确定有没有合适的可用的索引。using index condition表示使用了ICP（Index Condition Pushdown索引下推），ICP是MySQL 5.6之后新增的特性，部分字段因为某些情况无法走索引查询，则会展示using where（在server层进行数据过滤），ICP是在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据过滤调一部分数据。阅读详情

其实想根据订单，自动结算库存,然后判断哪些单可以出库，标记 #TMP_TRADE.Flag 字段
可是我这样写出来，3千订单，要跑2分钟才能结算出来，再次请高手指点下，



----下面我先贴上我的SQL 语句

-------- 定义订单列表 -------

CREATE TABLE #TMP_TRADE

(

  ID INT,

  Oid VARCHAR(50),

  UserId VARCHAR(100),

  Country VARCHAR(100),

  StateOrProvince VARCHAR(100),

  City VARCHAR(100),

  PostalCode VARCHAR(20),

  ShopID INT,

  ShippingCost DECIMAL(16,2),

  TotalPrice DECIMAL(16,2),

  SaleDate VARCHAR(20),

  PaidDate VARCHAR(20),

  ShippedDate VARCHAR(20),

  Flag INT 	 --是否可出库(有库存)   0 无库存，1有库存



)



-- 构造订单数据(实际要处理的有3千条数据)

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0

INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0





------ 库存表(动态结算，暂无数据) ------

CREATE TABLE #TMP_SkuNum

(

  SKU_ID INT,

  STOCK_ID INT,

  NUM INT

)



--- SKU 产品关联表 ----

CREATE TABLE #SKU_PRODUCT_MTM

(

 ID INT,

 PRODUCT_CODE VARCHAR(20),

 SKU_CODE VARCHAR(20),

 SKU_QTY INT

)



INSERT INTO #SKU_PRODUCT_MTM SELECT 1 'SP001','SKU001',2





CREATE TABLE #Calc_Sku

(

  Id INT,	

  Num INT

)



DECLARE @T_ID INT

DECLARE @Oid VARCHAR(20)

DECLARE @Calc_Num INT

SET @T_ID=0

SET @Oid=''

DECLARE MY_CURSOR CURSOR FOR SELECT Id,Oid FROM #TMP_TRADE  

OPEN MY_CURSOR

FETCH FROM MY_CURSOR INTO @T_ID,@Oid

WHILE (@@FETCH_STATUS=0)

BEGIN

	IF EXISTS(SELECT 1 FROM tEBay_TradeProduct tetp JOIN #SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE	WHERE tetp.Oid=@Oid )   --判断产品有无关联的SKU

	BEGIN			

		INSERT #Calc_Sku

		SELECT tssn.Num-tetp.Num*spm.SKU_QTY 

		FROM tEBay_TradeProduct tetp   --tEBay_TradeProduct 订单产品表，此表实际中只有20W数据 

		JOIN #SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE		

		JOIN #TMP_SkuNum tssn ON tss.Id=tssn.SKU_ID AND tssn.STOCK_ID=1

		WHERE tetp.Oid=@Oid

		

		IF EXISTS(SELECT 1 FROM #Calc_Sku WHERE Num<0)

		BEGIN		

			PRINT '无库存Oid:'+@Oid

		END

		ELSE

		BEGIN

			UPDATE #TMP_TRADE  SET Flag = 1 WHERE ID=@T_ID

			UPDATE #TMP_SkuNum SET #TMP_SkuNum.NUM = #Calc_Sku.Num FROM #TMP_SkuNum,#Calc_Sku WHERE #TMP_SkuNum.SKU_ID=#Calc_Sku.Id AND #TMP_SkuNum.STOCK_ID=1		

		END

		

		DELETE FROM #Calc_Sku

	END

	

	FETCH FROM MY_CURSOR INTO @T_ID,@Oid

END

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

 



SELECT * FROM #TMP_TRADE ORDER BY Flag DESC,PaidDate



DROP TABLE #TMP_TRADE

DROP TABLE #TMP_SkuNum

DROP TABLE #SKU_PRODUCT_MTM

DROP TABLE #Calc_Sku

...全文

SQL如何进行优化，看这篇就好了在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多 SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的 SQL 就是整个系统性能的瓶颈。... 阅读详情

雷肿么了

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

雷肿么了 2012-07-03

打赏
举报

最后没用游标写了，用游标确实慢了很多，

我直接查出来，递减库存就可以了。 2秒搞定。感觉还可以优化的。

Rotel-刘志东 2012-07-03

打赏
举报

游标的效率是很差的，不到万不得已的时候不要用游标。
可以用存储过程实现代替游标的功能。

筱筱澄 2012-07-03

打赏
举报

没看的懂为什么要用游标，直接更新会是情况？
另外没有特殊需要的话，尽量使用FORWARD_ONLY READ_ONLY STATIC游标；

雷肿么了 2012-07-03

打赏
举报



--之前写的时候漏了，红色部分，没敲上来

		INSERT #Calc_Sku

		SELECT tss.id,tssn.Num-tetp.Num*spm.SKU_QTY 

		FROM tEBay_TradeProduct tetp

		JOIN SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE

		JOIN tStock_Sku tss ON tss.Code=spm.SKU_CODE

		JOIN #TMP_SkuNum tssn ON tss.Id=tssn.SKU_ID AND tssn.STOCK_ID=@StockID

		WHERE tetp.Oid=@Oid

雷肿么了 2012-07-03

打赏
举报

PS: 一个订单商品是由多个SKU 组合而成的



CREATE TABLE tEBay_TradeProduct

(

  ID INT,                    --订单商品ID

  Oid VARCHAR(20),           --订单编号

  PRODUCT_CODE VARCHAR(20)   --	产品编码

  Num  INT                   --	数量

)

CREATE TABLE #tStock_Sku

(

  ID INT,           --SKU ID

  CODE VARCHAR(20), --sku编号

)

其实我首先是根据查询条件将订单插入订单表 #TMP_TRADE （有3千条数据）
然后关联tEBay_TradeProduct （订单产品表，有20W数据）根据各自的Oid关联，
然后再根据tEBay_TradeProduct的PRODUCT_CODE（产品编号）与#SKU_PRODUCT_MTM（订单SKU关联表）的PRODUCT_CODE关联
然后#SKU_PRODUCT_MTM（订单SKU关联表）的SKU_CODE 与#tStock_Sku（SKU 表）表的SKU_CODE关联，
最后#tStock_Sku（SKU 表）表与#TMP_SkuNum（库存表）的SKU_ID
关联

就这样，每个订单找到关联的SKU，并结算库存，最后得出能否出库

雷肿么了 2012-07-03

打赏
举报



CREATE TABLE #TMP_TRADE

(

  ID INT,   --订单ID

  Oid VARCHAR(50), --订单编号

  UserId VARCHAR(100),

  Country VARCHAR(100),

  StateOrProvince VARCHAR(100),

  City VARCHAR(100),

  PostalCode VARCHAR(20),

  ShopID INT,

  ShippingCost DECIMAL(16,2),

  TotalPrice DECIMAL(16,2),

  SaleDate VARCHAR(20),

  PaidDate VARCHAR(20),

  ShippedDate VARCHAR(20),

  Flag INT      --是否可出库(有库存)   0 无库存，1有库存



)



------ 库存表(动态结算，暂无数据) ------

CREATE TABLE #TMP_SkuNum

(

  SKU_ID INT,   --SKU ID

  STOCK_ID INT, --仓库ID

  NUM INT       --数量

)



--- SKU 产品关联表 ----

CREATE TABLE #SKU_PRODUCT_MTM

(

 ID INT,

 PRODUCT_CODE VARCHAR(20),  --产品编码

 SKU_CODE VARCHAR(20),      --SKU编码

 SKU_QTY INT                --关联数量

)



CREATE TABLE #Calc_Sku

(

  Id INT,     --SKU ID

  Num INT     --结算库存数量

)

连星入剑端 2012-07-03

打赏
举报

呃，能把每个字段的作用解释的再清楚点不，包括你的思路，通过命名又猜不出来。又没主外键的，哪些表关联都弄不清，看起来比较头大呢，呵呵。
慢的主要原因是游标吧，能不用游标么？

开着拖拉机泡妞 2012-07-03

打赏
举报

为什么不用递归试试？
游标没什么效率，不到万不得已干嘛用游标

[骚年 ，求教SQL优化]

相关推荐

[骚年，求教SQL优化]