概述

新項目業務人員反饋說最近訂單發放模塊經常很卡，導致總是有鎖的情況發生，在用慢查詢和開啟鎖監控觀察後發現實際上只是單條查詢慢造成的阻塞鎖，這裡考慮先對單條查詢做一下優化。

1、優化前的表結構、數據量、SQL、執行計劃、執行時間

1.1、表結構

A表有90個欄位，B表有140個欄位。

1.2、數據量

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

1.3、sql

開啟慢查詢觀察到慢sql如下，單條執行只取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;

1.4、執行計劃

思路

這兩張表都是訂單表，全國各地的每天大概會產生十萬行左右，這裡又是全掃，等後期達到上千萬的數據就GG了。目前只是看到這個sql上的問題，先考慮exists部分做一下改寫。

2、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;

執行時間：耗時1.8秒

對應的執行計劃：

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

業務確認結果符合需求，那就在這基礎上建一下索引吧！

3、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); --加上去但實際上用不到這個索引，選擇去掉

4、查看執行時間和執行計劃

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

5、考慮用join改寫

把ob結果集縮小，然後再做關聯查，並測試是否可以用上索引。

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;

時間快了一點，但不是很明顯，先湊合吧

執行計劃保持不變。

總結

建索引前因為走了主鍵索引，所以時間在1.6秒這樣，建索引後不走主鍵索引了，走ol表的索引，所以在1.5秒，然後縮小結果集去查的話就在1s這樣。

更重要的是這兩個表一個90個欄位，一個150個欄位，所以這兩個表的關聯查後期結果集應該還是會很大，建議是弄成分區表的形式，表能拆分的話是最好的。這些長度不要直接給那麼大，這麼寬對性能都是有影響的。

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~