《优化订货宝日志优化相关.docx》由会员分享，可在线阅读，更多相关《优化订货宝日志优化相关.docx（6页珍藏版）》请在三一文库上搜索。
1、优化 SQL ：SELECTc.Coding,c.Units,c.ID,c.OrderID,c.ClientID,c.ContentID,c.ContentName,c.ContentColor,c.ContentSpecification,c.ContentPrice,c.ContentNumber,c.ContentPercent,c.ContentSend,o.OrderSN,o.OrderStatusFROMrsung_view_index_cart cINNER JOIN(SELECTOrderID, OrderSN, OrderStatusFROMrsung_order_order
2、infoWHEREOrderCompany = 28 AND OrderStatus != 8AND OrderStatus != 9) AS o ON c.OrderID = o.OrderIDWHEREc.CompanyID = 28ORDER BY c.ID DESC limit 10;修改为：SELECTi.Coding,i.Units,c.ID,c.OrderID,c.ClientID,c.ContentID,c.ContentName,c.ContentColor,c.ContentSpecification,c.ContentPrice,c.ContentNumber,c.Con
3、tentPercent,c.ContentSend,o.OrderSN,o.OrderStatusFROMrsung_order_cart c LEFT JOIN rsung_order_content_index i ON c.ContentID=i.ID andi.coding = 107894LEFT JOIN rsung_order_orderinfo o ONAND o.OrderStatus != 9WHEREc.CompanyID = 226ORDER BY c.ID DESCLIMIT 0,50;c.OrderID = o.OrderID AND o.OrderStatus !
4、= 8- SQL 取得行数select aa:=aa+1 from rsung_order_cart c,(select aa:=0) s limit 20;2015-10-14:Replace into与insertignore与insert.On duplicatekey update.REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARYKEY 或一个 UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。注意，除非表有一个PRIMARYKEY 或 UNIQUE索引，否则，使用一个REPLACE 语句没有意义。该语句会与I
5、NSERT相同，因为没有索引被用于确定是否新行复制了其它的行。db_dhb_hk和db_dhb_hk_1 和db_dhb_hk_2=原SQL ：select s.*,o.OrderID,o.OrderSN,c.ClientID,c.ClientCompanyName FROM rsung_order_ordersubmit sinner join rsung_order_orderinfo o on s.OrderID=o.OrderIDinner join rsung_order_client c ON c.ClientID=o.OrderUserIDwhere c.ClientCompan
6、y=210 and s.CompanyID=210 and s.Status=客户留言 and o.OrderCompany=210 and s.Date 1444060800 ORDER BY s.ID DESC limit 0,20;s . * , o.OrderID, o.OrderSN, c.ClientID, c.ClientCompanyName FROM rsung_order_ordersubmit sFORCE INDEX ( idx_CompanyID_date )INNER JOIN rsung_order_orderinfo o ON s.OrderID= o.Orde
7、rIDAND o.OrderCompany =28INNER JOIN rsung_order_client c ON c.ClientID= o.OrderUserIDAND c.ClientCompany=28WHERE s.CompanyID=28AND s.Status = 客户留言 AND s.Date 1444060800ORDER BY s.ID DESC索引：ALTER TABLE rsung_order_ordersubmitADD INDEXidx_CompanyID_date (CompanyID,DATE);-=db_dhb_hk_- 原 SQLselectfrom r
8、sung_order_orderinfo a left join rsung_order_returninfo b on a.ordersn= b.returnorder where a.OrderCompany=154 and a.OrderUserID=35194 ORDER BY OrderID DESC limit0,12;优化后：SELECTa.OrderID,OrderSN,OrderSendType,OrderSendStatus,OrderPayType,OrderPayStatus,DeliveryDate,OrderRemark,OrderTotal,OrderStatus
9、,OrderDate,OrderType,OrderCollect,OrderSpecial,ReturnIDFROM rsung_order_orderinfo a FORCE INDEX (CLIENT)LEFT JOIN rsung_order_returninfo b ON a.ordersn= b.returnorderWHERE a.OrderCompany=154 AND a.OrderUserID=35194ORDER BY a.OrderID DESCLIMIT 0,12;添加索引： ALTER TABLE rsung_order_returninfo ADD INDEX i
10、dx_returnorder(returnorder);=原 SQL：SELECT s.ConsignmentDate,s.ConsignmentCompany,COUNT( DISTINCT c.OrderID) AS OrderCnt , COUNT( DISTINCT s.ConsignmentID) AS ConsignmentCnt, SUM(c.ContentSend * c.ContentPrice) AS AmountMFROM rsung_order_consignment AS sMLEFT JOIN rsung_order_orderinfo AS oMON o.Orde
11、rSN = s.ConsignmentOrderMINNER JOIN rsung_order_out_library AS lMON l.ConsignmentID = s.ConsignmentIDMLEFT JOIN rsung_order_cart AS cMON c.ID = l.CartIDMWHERE s.ConsignmentCompany = 538 AND ConsignmentDate 0000-00-00 AND ConsignmentDate = 2015-09-14 00:00:00 AND ConsignmentDate = 2015-10-14 23:59:59
12、MGROUP BY s.ConsignmentDate ASC;优化后：alter table rsung_order_consignment add index idx_CompanyDate(ConsignmentCompany,ConsignmentDate);alter table rsung_order_orderinfo add index idx_OrderSN(OrderSN);SELECT s.ConsignmentDate,s.ConsignmentCompany,COUNT( DISTINCT c.OrderID) AS OrderCnt , COUNT( DISTINC
13、T s.ConsignmentID) AS ConsignmentCnt, SUM(c.ContentSend * c.ContentPrice) AS AmountFROM rsung_order_consignment AS s force index (idx_CompanyDate)LEFT JOIN rsung_order_orderinfo AS o ON o.OrderSN = s.ConsignmentOrderINNERJOINrsung_order_out_libraryASlONl.ConsignmentID=s.ConsignmentIDLEFT JOIN rsung_
14、order_cart AS cON c.ID = l.CartIDWHERE s.ConsignmentCompany = 538AND s.ConsignmentDate 0000-00-00AND s.ConsignmentDate = 2015-09-14 00:00:00 AND s.ConsignmentDate= 2015-10-14 23:59:59GROUP BY s.ConsignmentDate ASC;资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借
15、鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。资料精选，适合职场人士使用借鉴参考。