新項(xiàng)目業(yè)務(wù)人員反饋說(shuō)最近訂單發(fā)放模塊經(jīng)常很卡，導(dǎo)致總是有鎖的情況發(fā)生，在用慢查詢和開(kāi)啟鎖監(jiān)控觀察后發(fā)現(xiàn)實(shí)際上只是單條查詢慢造成的阻塞鎖，這里考慮先對(duì)單條查詢做一下優(yōu)化。

一、優(yōu)化前的表結(jié)構(gòu)、數(shù)據(jù)量、SQL、執(zhí)行計(jì)劃、執(zhí)行時(shí)間

1. 表結(jié)構(gòu)

A表有90個(gè)字段，B表有140個(gè)字段。

2. 數(shù)據(jù)量

select count(*) from A; 
--166713 
select count(*) from B; 
--220810 

3. sql

開(kāi)啟慢查詢觀察到慢sql如下，單條執(zhí)行只取200條記錄是在23秒左右。

select ob.id, ob.customer, ob.order_no1, ob.accountingitems_code, ob.insert_date, ob.weight,  
ob.volume, ob.qty, ob.project_code,ob.order_no2,ob.order_type1  
 from A as ob  
where ifnull(ob.project_code,'')<>'' and ifnull(ob.accountingitems_code,'')<>''  
and ob.if_cost_proof='N'  
and EXISTS (select 1 from B ol where ob.id=ol.order_base) limit 200; 

4. 執(zhí)行計(jì)劃

思路

這兩張表都是訂單表，全國(guó)各地的每天大概會(huì)產(chǎn)生十萬(wàn)行左右，這里又是全掃，等后期達(dá)到上千萬(wàn)的數(shù)據(jù)就GG了。目前只是看到這個(gè)sql上的問(wèn)題，先考慮exists部分做一下改寫。

二、exists部分改寫

select ob.id, ob.customer, ob.order_no1, ob.accountingitems_code, ob.insert_date, ob.weight,  
ob.volume, ob.qty, ob.project_code,ob.order_no2,ob.order_type1  
 from fsl_order_base as ob,fsl_order_base_line ol 
where ob.id=ol.order_base and ob.if_cost_proof='N' and 
ifnull(ob.project_code,'')<>'' and ifnull(ob.accountingitems_code,'')<>'' limit 200; 

執(zhí)行時(shí)間：耗時(shí)1.8秒

對(duì)應(yīng)的執(zhí)行計(jì)劃：

可以看到ob表走了主鍵索引

業(yè)務(wù)確認(rèn)結(jié)果符合需求，那就在這基礎(chǔ)上建一下索引吧!

三、ol表建索引

create index idx_obl_id on fsl_order_base_line(order_base); 
create index idx_ob_id_cost on fsl_order_base(id,if_cost_proof); 

加上去但實(shí)際上用不到這個(gè)索引，選擇去掉

四、查看執(zhí)行時(shí)間和執(zhí)行計(jì)劃

耗時(shí)1.1秒，可惜執(zhí)行計(jì)劃還是走了全掃，在對(duì)ob表建了索引實(shí)際上也用不到，最終只在ol表建了索引。

五、考慮用join改寫

把ob結(jié)果集縮小，然后再做關(guān)聯(lián)查，并測(cè)試是否可以用上索引。

SELECT 
 obc.id, 
 obc.customer, 
 obc.order_no1, 
 obc.accountingitems_code, 
 obc.insert_date, 
 obc.weight, 
 obc.volume, 
 obc.qty, 
 obc.project_code, 
 obc.order_no2, 
 obc.order_type1  
FROM 
 (select * from fsl_order_base AS ob where ob.if_cost_proof = 'N' and ifnull( ob.project_code, '' ) <> '' and ifnull( ob.accountingitems_code, '' ) <> '' ) obc 
 join 
 fsl_order_base_line ol  
 on obc.id = ol.order_base limit 200; 

時(shí)間快了一點(diǎn)，但不是很明顯，先湊合吧

執(zhí)行計(jì)劃保持不變。

總結(jié)

建索引前因?yàn)樽吡酥麈I索引，所以時(shí)間在1.6秒這樣，建索引后不走主鍵索引了，走ol表的索引，所以在1.5秒，然后縮小結(jié)果集去查的話就在1s這樣。

更重要的是這兩個(gè)表一個(gè)90個(gè)字段，一個(gè)150個(gè)字段，所以這兩個(gè)表的關(guān)聯(lián)查后期結(jié)果集應(yīng)該還是會(huì)很大，建議是弄成分區(qū)表的形式，表能拆分的話是最好的。這些長(zhǎng)度不要直接給那么大，這么寬對(duì)性能都是有影響的。