記一次SQL優化
2022-07-23 12:00:32
昨天（2022-7-22）上線了我的一個功能，測試環境資料量較小，問題不大，但是上生產之後，直接卡死了，然後就開始了這麼一次SQL優化，這裡記錄一下。
不太方便透露公司的表結構，這裡我自己建了幾張表，模擬一下就可以了。
肯定有槓精要說表可以不這樣設計了，但是事實現在系統就是這樣設計的，如果想改動表設計，影響面就太大了（我們急著上線哦）。當然，本文的後面也會給出修改設計的方案，以達到更優解。
1. 建立表
進貨單表：
CREATE TABLE `purchase_order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵自增id',
`purchase_time` varchar(255) DEFAULT NULL COMMENT '進貨時間',
`purchase_pre_unit_price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '進貨預訂單價(元/kg)',
`purchase_weight` decimal(10,2) unsigned zerofill NOT NULL COMMENT '進貨重量(kg)',
`purchase_bill_no` varchar(255) NOT NULL COMMENT '進貨單號',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=62181 DEFAULT CHARSET=utf8 COMMENT='進貨單';
進貨結算單表：
CREATE TABLE `settlement_voucher` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`purchase_bill_no` varchar(512) DEFAULT NULL COMMENT '進貨單號',
`settlement_bill_no` varchar(64) NOT NULL COMMENT '結算單號',
`unit_price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '實際結算單價（元/kg）',
`settlement_weight` decimal(10,2) unsigned zerofill NOT NULL COMMENT '實際結算重量（kg）',
`cut_off_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '結算時間',
PRIMARY KEY (`id`),
KEY `idx_settlement_bill_no` (`settlement_bill_no`)
) ENGINE=InnoDB AUTO_INCREMENT=63288 DEFAULT CHARSET=utf8 COMMENT='進貨結算單';
發票表：
CREATE TABLE `invoice` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`invoice_code` varchar(255) NOT NULL COMMENT '發票程式碼',
`invoice_number` varchar(255) NOT NULL COMMENT '發票號碼',
`pay_amount` decimal(10,2) DEFAULT NULL COMMENT '發票金額',
PRIMARY KEY (`id`),
KEY `idx_invoice_number` (`invoice_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='發票表';
發票-結算單關聯表：
CREATE TABLE `settlement_invoice_relation` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`invoice_code` varchar(255) DEFAULT NULL COMMENT '發票程式碼',
`invoice_number` varchar(255) DEFAULT NULL COMMENT '發票號碼',
`settlement_bill_no` varchar(64) DEFAULT NULL COMMENT '結算單號',
PRIMARY KEY (`id`),
KEY `idx_settlement_bill_no` (`settlement_bill_no`),
KEY `idx_invoice_number` (`invoice_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='發票-結算單關聯表';
以上是我自己建立的幾張表，先介紹一下這幾張表的關係：
- 進貨單表(
purchase_order)和進貨結算單表(settlement_voucher)通過進貨單號(purchase_bill_no)關聯。這裡值得注意的是：- 一個進貨單可以對應多個進貨結算單，通過
purchase_bill_no關聯，如以下資料： - 一個進貨結算單可以對應多個進貨單，通過
purchase_bill_no關聯，settlement_voucher表中的purchase_bill_no欄位存放多個進貨單號，使用英文逗號隔開。如以下資料：
- 一個進貨單可以對應多個進貨結算單，通過
- 發票表(
invoice)和結算單表(settlement_voucher)有一個關聯關係表(settlement_invoice_relation)- 發票表和關聯關係表使用
invoice_code和invoice_number關聯 - 結算單表和關聯關係表使用
settlement_bill_no關聯 - 發票和結算單是多對多的關係
- 發票表和關聯關係表使用
2. 需求
現在需要以進貨結算單表(settlement_voucher)查詢出一個列表：
- 列表欄位有【進貨時間(多個使用英文逗號隔開)、進貨平均單價、進貨預定總金額，結算單號，結算平均單價，結算金額，結算時間，發票號碼(多個使用英文逗號隔開)，發票程式碼(多個使用英文逗號隔開)】
- 查詢條件有：進貨時間(一個進貨結算單對應多個進貨單時，只要有一個進貨單的時間在範圍內，就查詢到)，結算時間，發票號碼(一個結算單對應多個發票時，只要有一個發票能關聯上，就查詢到)
- 根據結算時間排序
當然，實際當時的那個需求，列表欄位比這多，查詢條件也比這多......
3. 給表插入資料
先給貨單表(purchase_order)和進貨結算單表(settlement_voucher)各自插入10萬條資料，我這裡使用了儲存過程：
begin
declare i int;
declare purchase_weight decimal(10,2);
declare unit_price decimal(10,2);
declare purchase_bill_no varchar(255);
declare settlement_bill_no varchar(255);
set i=0;
while i<100000 do
select ROUND(RAND()*100,2) into purchase_weight from dual;
select ROUND(RAND()*10,2) into unit_price from dual;
select CONCAT('purchase-',LPAD(i,8,'0')) into purchase_bill_no from dual;
select CONCAT('settlement-',LPAD(i,8,'0')) into settlement_bill_no from dual;
-- 插入進貨單表，進貨時間隨機生成
insert into purchase_order(purchase_time,purchase_pre_unit_price,purchase_weight,purchase_bill_no)
select (DATE_ADD(NOW(), INTERVAL FLOOR(1 - (RAND() * 864000)) SECOND )),
unit_price,purchase_weight,purchase_bill_no from dual;
-- 插入結算單表，結算時間隨機生成
insert into settlement_voucher(purchase_bill_no,settlement_bill_no,unit_price,settlement_weight,cut_off_time)
select purchase_bill_no,settlement_bill_no,unit_price,purchase_weight,
(DATE_ADD(NOW(), INTERVAL FLOOR(1 - (RAND() * 864000)) SECOND )) from dual;
set i=i+1;
end while;
end
呼叫儲存過程生成資料：
call pre();
生成之後需要隨機改幾條資料，模擬一個進貨單可以對應多個進貨結算單，以及一個進貨結算單可以對應多個進貨單兩種情況（這樣資料更真實一點）。
一個進貨單可以對應多個進貨結算單的情況就不模擬了，這種情況其實對這次查詢的影響並不大。
一個進貨結算單可以對應多個進貨單的情況：
再建立一些發票資料和結算單-發票關聯資料，需要體現多對多的關係：
insert into invoice(invoice_code,invoice_number,pay_amount)
VALUES
('111111','1111100','1000'),
('111112','1111101','1001'),
('111113','1111102','1002'),
('111114','1111103','1003'),
('111115','1111104','1004'),
('111116','1111105','1005'),
('111117','1111106','1006'),
('111118','1111107','1007'),
('111119','1111108','1008'),
('111110','1111109','1009');
INSERT into settlement_invoice_relation(invoice_code,invoice_number,settlement_bill_no)
VALUES
('111111','1111100','settlement-00000000'),
('111112','1111101','settlement-00000000'),
('111113','1111102','settlement-00000000'),
('111114','1111103','settlement-00000004'),
('111114','1111103','settlement-00000006'),
('111114','1111103','settlement-00000030'),
('111116','1111105','settlement-00000041'),
('111117','1111106','settlement-00000041'),
('111118','1111107','settlement-00000043');
4. 開始根據需求寫SQL
優化第一步，當然是想讓產品經理去掉一些查詢條件，避免進貨單表和進貨結算表關聯了，但是你懂的。。。。。。
這裡就以進貨時間為條件查詢為例（因為主要就是進貨單和進貨結算單關聯導致慢查詢），記得需求哦，就是一個進貨結算單可能對應多個進貨單，只要有其中一個進貨單在時間範圍內，就需要查詢出這條進貨結算單
還有：我上面建立的表中索引也模擬了當時優化之前的索引......
4.1 第一版
select
GROUP_CONCAT(po.purchase_time) as 進貨時間,
AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進貨均價,
t.settlement_bill_no as 結算單號,
AVG(IFNULL(t.unit_price,0)) as 結算均價,
any_value(t.cut_off_time) as 結算時間,
any_value(invoice_tmp.invoice_code) as 發票程式碼,
any_value(invoice_tmp.invoice_number) as 發票號碼
from settlement_voucher t
left join purchase_order po on FIND_IN_SET(po.purchase_bill_no,t.purchase_bill_no)>0
left join (
select sir.settlement_bill_no,
GROUP_CONCAT(i.invoice_number) invoice_number,
GROUP_CONCAT(i.invoice_code) invoice_code
from settlement_invoice_relation sir, invoice i
where sir.invoice_code = i.invoice_code and sir.invoice_number = i.invoice_number
group by sir.settlement_bill_no
) invoice_tmp on invoice_tmp.settlement_bill_no = t.settlement_bill_no
where 1=1
-- and t.settlement_bill_no='settlement-00000000'
and EXISTS(select 1 from purchase_order po1 where FIND_IN_SET(po1.purchase_bill_no,t.purchase_bill_no)>0
and po1.purchase_time >='2022-07-01 00:00:00'
)
and EXISTS(select 1 from purchase_order po1 where FIND_IN_SET(po1.purchase_bill_no,t.purchase_bill_no)>0
and po1.purchase_time <='2022-07-23 23:59:59'
)
group by t.settlement_bill_no;
第一版SQL當時在本地環境執行是用了5秒左右，此時就已經意識到問題了，這別說上生產了，就是在測試環境都得掛掉。
但是看看我在自己的垃圾伺服器（雙核4G）上跑這條SQL吧，是根本執行不出來的（雖然公司伺服器好一些，但是生產環境確實卡死了）：
當時就還沒沒看執行計劃，一眼看去，這個SQL中用到了FIND_IN_SET，肯定是不會走索引的，建了索引也沒用，也就是主要是進貨單表(purchase_order)和進貨結算單表settlement_voucher關聯會很慢，畢竟他們是多對多的關係，再加上這噁心的需求。所以現在想想該怎麼才能不用 FIND_IN_SET。
對，吃飯期間，突發奇想：我應該可以把進貨結算單表拆成一個臨時表，如果進貨結算單表對應了5個進貨單，我就把進貨結算單拆成5條資料，這五條資料除了進貨單號不一樣，其他欄位都 一樣，這樣就可以不用FIND_IN_SET了。
說幹就幹，於是有了下面第二版SQL。
4.2 第二版
向把進貨結算單表拆分成上面說的臨時表，需要新增一個表：
CREATE TABLE `incre_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用於分割進貨結算單表';
-- 注意：這裡一個進貨結算單對應多少個進貨單，這裡就要依次插入多少條資料，我這裡10條 就夠用了
insert into incre_table(id) VALUES(1);
insert into incre_table(id) VALUES(2);
insert into incre_table(id) VALUES(3);
insert into incre_table(id) VALUES(4);
insert into incre_table(id) VALUES(5);
insert into incre_table(id) VALUES(6);
insert into incre_table(id) VALUES(7);
insert into incre_table(id) VALUES(8);
insert into incre_table(id) VALUES(9);
insert into incre_table(id) VALUES(10);
先來看看怎麼把一條進貨結算單資料拆分成多條：
select
sv.cut_off_time,
sv.settlement_bill_no,
sv.unit_price,
sv.settlement_weight,
SUBSTRING_INDEX(SUBSTRING_INDEX(sv.purchase_bill_no,',',it.id),',',-1) purchase_bill_no
from settlement_voucher sv
RIGHT JOIN incre_table it on it.id<=(LENGTH(sv.purchase_bill_no) - LENGTH(REPLACE(sv.purchase_bill_no,',','')) + 1)
where sv.settlement_bill_no='settlement-00000000';
來解釋一下這個騷操作：
- 首先我建立了一個只有id的表
incre_table，插入了十條資料，並且這十條資料必須是1-10。 - 然後我使用
settlement_voucher右連線了incre_table，並且只取incre_table中id小於或等於進貨單數量的資料。這樣就控制了這條SQL應該查詢多少條資料（就剛好是一個進貨結算單對應的進貨單條數）。 - 然後使用
SUBSTRING_INDEX去一個一個拆分settlement_voucher表中的進貨單號
這套SQL執行的結果就是：
綜合起來，就寫好了第二版SQL：
select
GROUP_CONCAT(po.purchase_time) as 進貨時間,
AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進貨均價,
t.settlement_bill_no as 結算單號,
AVG(IFNULL(t.unit_price,0)) as 結算均價,
any_value(t.cut_off_time) as 結算時間,
any_value(invoice_tmp.invoice_code) as 發票程式碼,
any_value(invoice_tmp.invoice_number) as 發票號碼
from (
select
sv.cut_off_time,
sv.settlement_bill_no,
sv.unit_price,
sv.settlement_weight,
SUBSTRING_INDEX(SUBSTRING_INDEX(sv.purchase_bill_no,',',it.id),',',-1) purchase_bill_no
from settlement_voucher sv
RIGHT JOIN incre_table it on it.id<=(LENGTH(sv.purchase_bill_no) - LENGTH(REPLACE(sv.purchase_bill_no,',','')) + 1)
) t
left join purchase_order po on po.purchase_bill_no = t.purchase_bill_no
left join (
select sir.settlement_bill_no,
GROUP_CONCAT(i.invoice_number) invoice_number,
GROUP_CONCAT(i.invoice_code) invoice_code
from settlement_invoice_relation sir, invoice i
where sir.invoice_code = i.invoice_code and sir.invoice_number = i.invoice_number
group by sir.settlement_bill_no
) invoice_tmp on invoice_tmp.settlement_bill_no = t.settlement_bill_no
where 1=1
-- and t.settlement_bill_no='settlement-00000000'
and po.purchase_time >='2022-07-01 00:00:00'
and po.purchase_time <='2022-07-23 23:59:59'
group by t.settlement_bill_no;
測試查詢資料結果肯定是沒有問題的哦！！！
好的，到這裡終於把所有用到FIND_IN_SET的地方去掉了，這時看索引就有意義了！
看看執行計劃吧：
阿西巴，一堆的全表掃描，看看上面第二版SQL，發現進貨表(purchase_order)的purchase_bill_no欄位是應該走索引的，按道理這個欄位一般設計表的時候就應該已經加索引了，但是我以為只是我以為，它確實沒加索引，好的，那就給它加上索引吧：
create index idx_purchase_bill_no on purchase_order(purchase_bill_no);
加完是這個索引後，再看看執行計劃：
purchase_order表的purchase_bill_no已經走了索引，但是settlement_invoice_relation咋不走索引，它是有兩個索引的。。。。。。
再看看在我的垃圾伺服器上執行，看能不能執行出來：
好了，為了讓settlement_invoice_relation表的查詢也走索引，開始下一輪的SQL優化
4.3 第三版
就不在下面去聚合獲取invoice_code和invoice_number了，在上面來聚合，至於要以這兩個欄位作為查詢條件，那可以把下面這條SQL再包一層，作為一個臨時表再查詢一遍，這裡就不演示了
select
GROUP_CONCAT(po.purchase_time) as 進貨時間,
AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進貨均價,
t.settlement_bill_no as 結算單號,
AVG(IFNULL(t.unit_price,0)) as 結算均價,
any_value(t.cut_off_time) as 結算時間,
GROUP_CONCAT(DISTINCT invoice_tmp.invoice_code) as 發票程式碼,
GROUP_CONCAT(DISTINCT invoice_tmp.invoice_number) as 發票號碼
from (
select
sv.cut_off_time,
sv.settlement_bill_no,
sv.unit_price,
sv.settlement_weight,
SUBSTRING_INDEX(SUBSTRING_INDEX(sv.purchase_bill_no,',',it.id),',',-1) purchase_bill_no
from settlement_voucher sv
RIGHT JOIN incre_table it on it.id<=(LENGTH(sv.purchase_bill_no) - LENGTH(REPLACE(sv.purchase_bill_no,',','')) + 1)
) t
left join purchase_order po on po.purchase_bill_no = t.purchase_bill_no
left join (
select sir.settlement_bill_no,
i.invoice_number,
i.invoice_code
from settlement_invoice_relation sir, invoice i
where sir.invoice_code = i.invoice_code and sir.invoice_number = i.invoice_number
) invoice_tmp on invoice_tmp.settlement_bill_no = t.settlement_bill_no
where 1=1
-- and t.settlement_bill_no='settlement-00000000'
and po.purchase_time >='2022-07-01 00:00:00'
and po.purchase_time <='2022-07-23 23:59:59'
group by t.settlement_bill_no;
再看看執行計劃：
這時，基本優化結束，再看看在我的垃圾伺服器上跑出的結果：
到這裡，基本上生產上是可以在三秒以內查詢出來了，本次SQL優化就到此結束了！！！
但是，其實還是可以繼續優化的，但是設計到系統改的地方比較多了，影響面比較大，這裡就說一下思路，暫時不能實踐：
可以把進貨單表purchase_order和進貨結算單表settlement_voucher之間，建立一箇中間表，實現多對多的關係，再加以索引，應該會更快，而且可以一勞永逸，以後這種關聯都會比較方便了！