昨天（2022-7-22）上線了我的一個功能，測試環(huán)境數(shù)據(jù)量較小具温，問題不大，但是上生產(chǎn)之后筐赔，直接卡死了铣猩，然后就開始了這么一次SQL優(yōu)化，這里記錄一下茴丰。
不太方便透露公司的表結(jié)構(gòu)达皿，這里我自己建了幾張表，模擬一下就可以了贿肩。
肯定有杠精要說表可以不這樣設(shè)計了峦椰，但是事實現(xiàn)在系統(tǒng)就是這樣設(shè)計的，如果想改動表設(shè)計汰规，影響面就太大了（我們急著上線哦）汤功。當(dāng)然，本文的后面也會給出修改設(shè)計的方案溜哮，以達(dá)到更優(yōu)解滔金。
1. 創(chuàng)建表
進(jìn)貨單表：
CREATE TABLE `purchase_order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵自增id', `purchase_time` varchar(255) DEFAULT NULL COMMENT '進(jìn)貨時間', `purchase_pre_unit_price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '進(jìn)貨預(yù)訂單價(元/kg)', `purchase_weight` decimal(10,2) unsigned zerofill NOT NULL COMMENT '進(jìn)貨重量(kg)', `purchase_bill_no` varchar(255) NOT NULL COMMENT '進(jìn)貨單號', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=62181 DEFAULT CHARSET=utf8 COMMENT='進(jìn)貨單';
進(jìn)貨結(jié)算單表：
CREATE TABLE `settlement_voucher` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID', `purchase_bill_no` varchar(512) DEFAULT NULL COMMENT '進(jìn)貨單號', `settlement_bill_no` varchar(64) NOT NULL COMMENT '結(jié)算單號', `unit_price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '實際結(jié)算單價（元/kg）', `settlement_weight` decimal(10,2) unsigned zerofill NOT NULL COMMENT '實際結(jié)算重量（kg）', `cut_off_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '結(jié)算時間', PRIMARY KEY (`id`), KEY `idx_settlement_bill_no` (`settlement_bill_no`)) ENGINE=InnoDB AUTO_INCREMENT=63288 DEFAULT CHARSET=utf8 COMMENT='進(jìn)貨結(jié)算單';
發(fā)票表：
CREATE TABLE `invoice` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID', `invoice_code` varchar(255) NOT NULL COMMENT '發(fā)票代碼', `invoice_number` varchar(255) NOT NULL COMMENT '發(fā)票號碼', `pay_amount` decimal(10,2) DEFAULT NULL COMMENT '發(fā)票金額', PRIMARY KEY (`id`), KEY `idx_invoice_number` (`invoice_number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='發(fā)票表';
發(fā)票-結(jié)算單關(guān)聯(lián)表：
CREATE TABLE `settlement_invoice_relation` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID', `invoice_code` varchar(255) DEFAULT NULL COMMENT '發(fā)票代碼', `invoice_number` varchar(255) DEFAULT NULL COMMENT '發(fā)票號碼', `settlement_bill_no` varchar(64) DEFAULT NULL COMMENT '結(jié)算單號', PRIMARY KEY (`id`), KEY `idx_settlement_bill_no` (`settlement_bill_no`), KEY `idx_invoice_number` (`invoice_number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='發(fā)票-結(jié)算單關(guān)聯(lián)表';
以上是我自己創(chuàng)建的幾張表，先介紹一下這幾張表的關(guān)系：
- 進(jìn)貨單表(purchase_order)和進(jìn)貨結(jié)算單表(settlement_voucher)通過進(jìn)貨單號(purchase_bill_no)關(guān)聯(lián)茂嗓。這里值得注意的是：一個進(jìn)貨單可以對應(yīng)多個進(jìn)貨結(jié)算單餐茵，通過purchase_bill_no關(guān)聯(lián)，如以下數(shù)據(jù)：一個進(jìn)貨結(jié)算單可以對應(yīng)多個進(jìn)貨單述吸，通過purchase_bill_no關(guān)聯(lián)忿族，settlement_voucher表中的purchase_bill_no字段存放多個進(jìn)貨單號，使用英文逗號隔開蝌矛。如以下數(shù)據(jù)：
- 發(fā)票表(invoice)和結(jié)算單表(settlement_voucher)有一個關(guān)聯(lián)關(guān)系表(settlement_invoice_relation)發(fā)票表和關(guān)聯(lián)關(guān)系表使用invoice_code和invoice_number關(guān)聯(lián)結(jié)算單表和關(guān)聯(lián)關(guān)系表使用settlement_bill_no關(guān)聯(lián)發(fā)票和結(jié)算單是多對多的關(guān)系
2. 需求
現(xiàn)在需要以進(jìn)貨結(jié)算單表(settlement_voucher)查詢出一個列表：
- 列表字段有【進(jìn)貨時間(多個使用英文逗號隔開)道批、進(jìn)貨平均單價、進(jìn)貨預(yù)定總金額朴读，結(jié)算單號屹徘，結(jié)算平均單價，結(jié)算金額衅金，結(jié)算時間噪伊，發(fā)票號碼(多個使用英文逗號隔開)，發(fā)票代碼(多個使用英文逗號隔開)】
- 查詢條件有：進(jìn)貨時間(一個進(jìn)貨結(jié)算單對應(yīng)多個進(jìn)貨單時氮唯，只要有一個進(jìn)貨單的時間在范圍內(nèi)鉴吹，就查詢到)，結(jié)算時間惩琉，發(fā)票號碼(一個結(jié)算單對應(yīng)多個發(fā)票時豆励，只要有一個發(fā)票能關(guān)聯(lián)上，就查詢到)
- 根據(jù)結(jié)算時間排序
當(dāng)然，實際當(dāng)時的那個需求良蒸，列表字段比這多技扼，查詢條件也比這多......
3. 給表插入數(shù)據(jù)
先給貨單表(purchase_order)和進(jìn)貨結(jié)算單表(settlement_voucher)各自插入10萬條數(shù)據(jù)，我這里使用了存儲過程：
begindeclare 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; -- 插入進(jìn)貨單表嫩痰，進(jìn)貨時間隨機(jī)生成 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; -- 插入結(jié)算單表剿吻，結(jié)算時間隨機(jī)生成 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
調(diào)用存儲過程生成數(shù)據(jù)：
call pre();
生成之后需要隨機(jī)改幾條數(shù)據(jù)，模擬一個進(jìn)貨單可以對應(yīng)多個進(jìn)貨結(jié)算單串纺，以及一個進(jìn)貨結(jié)算單可以對應(yīng)多個進(jìn)貨單兩種情況（這樣數(shù)據(jù)更真實一點）丽旅。
一個進(jìn)貨單可以對應(yīng)多個進(jìn)貨結(jié)算單的情況就不模擬了，這種情況其實對這次查詢的影響并不大纺棺。
一個進(jìn)貨結(jié)算單可以對應(yīng)多個進(jìn)貨單的情況：
再創(chuàng)建一些發(fā)票數(shù)據(jù)和結(jié)算單-發(fā)票關(guān)聯(lián)數(shù)據(jù)榄笙，需要體現(xiàn)多對多的關(guān)系：
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. 開始根據(jù)需求寫SQL
優(yōu)化第一步，當(dāng)然是想讓產(chǎn)品經(jīng)理去掉一些查詢條件祷蝌，避免進(jìn)貨單表和進(jìn)貨結(jié)算表關(guān)聯(lián)了茅撞，但是你懂的。杆逗。乡翅。。罪郊。蠕蚜。
這里就以進(jìn)貨時間為條件查詢?yōu)槔ㄒ驗橹饕褪沁M(jìn)貨單和進(jìn)貨結(jié)算單關(guān)聯(lián)導(dǎo)致慢查詢），記得需求哦悔橄，就是一個進(jìn)貨結(jié)算單可能對應(yīng)多個進(jìn)貨單靶累，只要有其中一個進(jìn)貨單在時間范圍內(nèi)，就需要查詢出這條進(jìn)貨結(jié)算單
還有：我上面創(chuàng)建的表中索引也模擬了當(dāng)時優(yōu)化之前的索引......
4.1 第一版
select GROUP_CONCAT(po.purchase_time) as 進(jìn)貨時間, AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進(jìn)貨均價, t.settlement_bill_no as 結(jié)算單號, AVG(IFNULL(t.unit_price,0)) as 結(jié)算均價, any_value(t.cut_off_time) as 結(jié)算時間, any_value(invoice_tmp.invoice_code) as 發(fā)票代碼, any_value(invoice_tmp.invoice_number) as 發(fā)票號碼from settlement_voucher tleft join purchase_order po on FIND_IN_SET(po.purchase_bill_no,t.purchase_bill_no)>0left 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_nowhere 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當(dāng)時在本地環(huán)境執(zhí)行是用了5秒左右癣疟，此時就已經(jīng)意識到問題了挣柬，這別說上生產(chǎn)了，就是在測試環(huán)境都得掛掉睛挚。
但是看看我在自己的垃圾服務(wù)器（雙核4G）上跑這條SQL吧邪蛔，是根本執(zhí)行不出來的（雖然公司服務(wù)器好一些，但是生產(chǎn)環(huán)境確實卡死了）：
當(dāng)時就還沒沒看執(zhí)行計劃扎狱，一眼看去侧到，這個SQL中用到了FIND_IN_SET，肯定是不會走索引的淤击，建了索引也沒用匠抗，也就是主要是進(jìn)貨單表(purchase_order)和進(jìn)貨結(jié)算單表settlement_voucher關(guān)聯(lián)會很慢，畢竟他們是多對多的關(guān)系污抬，再加上這惡心的需求汞贸。所以現(xiàn)在想想該怎么才能不用 FIND_IN_SET。
對，吃飯期間矢腻，突發(fā)奇想：我應(yīng)該可以把進(jìn)貨結(jié)算單表拆成一個臨時表门驾，如果進(jìn)貨結(jié)算單表對應(yīng)了5個進(jìn)貨單，我就把進(jìn)貨結(jié)算單拆成5條數(shù)據(jù)踏堡，這五條數(shù)據(jù)除了進(jìn)貨單號不一樣猎唁，其他字段都 一樣，這樣就可以不用FIND_IN_SET了顷蟆。
說干就干，于是有了下面第二版SQL腐魂。
4.2 第二版
向把進(jìn)貨結(jié)算單表拆分成上面說的臨時表帐偎，需要添加一個表：
CREATE TABLE `incre_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用于分割進(jìn)貨結(jié)算單表';-- 注意：這里一個進(jìn)貨結(jié)算單對應(yīng)多少個進(jìn)貨單，這里就要依次插入多少條數(shù)據(jù)蛔屹，我這里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);
先來看看怎么把一條進(jìn)貨結(jié)算單數(shù)據(jù)拆分成多條：
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_nofrom settlement_voucher svRIGHT 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';
來解釋一下這個騷操作：
- 首先我創(chuàng)建了一個只有id的表incre_table削樊，插入了十條數(shù)據(jù)，并且這十條數(shù)據(jù)必須是1-10兔毒。
- 然后我使用settlement_voucher 右連接了 incre_table漫贞，并且只取incre_table中id小于或等于進(jìn)貨單數(shù)量的數(shù)據(jù)。這樣就控制了這條SQL應(yīng)該查詢多少條數(shù)據(jù)（就剛好是一個進(jìn)貨結(jié)算單對應(yīng)的進(jìn)貨單條數(shù)）育叁。
- 然后使用SUBSTRING_INDEX去一個一個拆分settlement_voucher表中的進(jìn)貨單號
這套SQL執(zhí)行的結(jié)果就是：
綜合起來迅脐，就寫好了第二版SQL：
select GROUP_CONCAT(po.purchase_time) as 進(jìn)貨時間, AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進(jìn)貨均價, t.settlement_bill_no as 結(jié)算單號, AVG(IFNULL(t.unit_price,0)) as 結(jié)算均價, any_value(t.cut_off_time) as 結(jié)算時間, any_value(invoice_tmp.invoice_code) as 發(fā)票代碼, any_value(invoice_tmp.invoice_number) as 發(fā)票號碼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)) tleft join purchase_order po on po.purchase_bill_no = t.purchase_bill_noleft 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_nowhere 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;折疊
測試查詢數(shù)據(jù)結(jié)果肯定是沒有問題的哦！：浪浴谴蔑！
好的，到這里終于把所有用到FIND_IN_SET的地方去掉了龟梦，這時看索引就有意義了隐锭！
看看執(zhí)行計劃吧：
阿西巴，一堆的全表掃描计贰，看看上面第二版SQL钦睡，發(fā)現(xiàn)進(jìn)貨表(purchase_order)的purchase_bill_no字段是應(yīng)該走索引的，按道理這個字段一般設(shè)計表的時候就應(yīng)該已經(jīng)加索引了躁倒，但是我以為只是我以為荞怒，它確實沒加索引，好的樱溉，那就給它加上索引吧：
create index idx_purchase_bill_no on purchase_order(purchase_bill_no);
加完是這個索引后挣输，再看看執(zhí)行計劃：
purchase_order表的purchase_bill_no已經(jīng)走了索引，但是
settlement_invoice_relation咋不走索引福贞，它是有兩個索引的撩嚼。。。完丽。恋技。。
再看看在我的垃圾服務(wù)器上執(zhí)行逻族，看能不能執(zhí)行出來：
好了蜻底，為了讓
settlement_invoice_relation表的查詢也走索引，開始下一輪的SQL優(yōu)化
4.3 第三版
就不在下面去聚合獲取invoice_code和invoice_number了聘鳞，在上面來聚合薄辅，至于要以這兩個字段作為查詢條件，那可以把下面這條SQL再包一層抠璃，作為一個臨時表再查詢一遍站楚，這里就不演示了
select GROUP_CONCAT(po.purchase_time) as 進(jìn)貨時間, AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進(jìn)貨均價, t.settlement_bill_no as 結(jié)算單號, AVG(IFNULL(t.unit_price,0)) as 結(jié)算均價, any_value(t.cut_off_time) as 結(jié)算時間, GROUP_CONCAT(DISTINCT invoice_tmp.invoice_code) as 發(fā)票代碼, GROUP_CONCAT(DISTINCT invoice_tmp.invoice_number) as 發(fā)票號碼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)) tleft join purchase_order po on po.purchase_bill_no = t.purchase_bill_noleft 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_nowhere 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;折疊
再看看執(zhí)行計劃：
這時，基本優(yōu)化結(jié)束搏嗡，再看看在我的垃圾服務(wù)器上跑出的結(jié)果：
到這里窿春，基本上生產(chǎn)上是可以在三秒以內(nèi)查詢出來了，本次SQL優(yōu)化就到此結(jié)束了２珊小＞善颉！
但是磅氨，其實還是可以繼續(xù)優(yōu)化的尺栖，但是設(shè)計到系統(tǒng)改的地方比較多了，影響面比較大悍赢，這里就說一下思路决瞳，暫時不能實踐：
可以把進(jìn)貨單表purchase_order和進(jìn)貨結(jié)算單表settlement_voucher之間，建立一個中間表左权，實現(xiàn)多對多的關(guān)系皮胡，再加以索引，應(yīng)該會更快赏迟，而且可以一勞永逸屡贺，以后這種關(guān)聯(lián)都會比較方便了！
