記一次SQL優(yōu)化的實(shí)戰(zhàn)記錄

更新時(shí)間：2022年07月24日 08:27:43 作者：下半夜的風(fēng)

作為開發(fā)人員,我們免不了與sql打交道,有些sql可能在業(yè)務(wù)的最開始,執(zhí)行是毫無(wú)問題的,但是隨著業(yè)務(wù)量的提升以及業(yè)務(wù)復(fù)雜度的加深,可能之前的sql就會(huì)需要優(yōu)化了,下面這篇文章主要給大家介紹了關(guān)于一次SQL優(yōu)化的實(shí)戰(zhàn)記錄,需要的朋友可以參考下

前言

昨天（2022-7-22）上線了我的一個(gè)功能，測(cè)試環(huán)境數(shù)據(jù)量較小，問題不大，但是上生產(chǎn)之后，直接卡死了，然后就開始了這么一次SQL優(yōu)化，這里記錄一下。

不太方便透露公司的表結(jié)構(gòu)，這里我自己建了幾張表，模擬一下就可以了。

肯定有杠精要說(shuō)表可以不這樣設(shè)計(jì)了，但是事實(shí)現(xiàn)在系統(tǒng)就是這樣設(shè)計(jì)的，如果想改動(dòng)表設(shè)計(jì)，影響面就太大了（我們急著上線哦）。當(dāng)然，本文的后面也會(huì)給出修改設(shè)計(jì)的方案，以達(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)貨時(shí)間',
  `purchase_pre_unit_price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '進(jìn)貨預(yù)訂單價(jià)(元/kg)',
  `purchase_weight` decimal(10,2) unsigned zerofill NOT NULL COMMENT '進(jìn)貨重量(kg)',
  `purchase_bill_no` varchar(255) NOT NULL COMMENT '進(jìn)貨單號(hào)',
  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)貨單號(hào)',
  `settlement_bill_no` varchar(64) NOT NULL COMMENT '結(jié)算單號(hào)',
  `unit_price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '實(shí)際結(jié)算單價(jià)（元/kg）',
  `settlement_weight` decimal(10,2) unsigned zerofill NOT NULL COMMENT '實(shí)際結(jié)算重量（kg）',
  `cut_off_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '結(jié)算時(shí)間',
  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ā)票號(hào)碼',
  `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ā)票號(hào)碼',
  `settlement_bill_no` varchar(64) DEFAULT NULL COMMENT '結(jié)算單號(hào)',
  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)貨單號(hào)(purchase_bill_no)關(guān)聯(lián)。這里值得注意的是：
- 一個(gè)進(jìn)貨單可以對(duì)應(yīng)多個(gè)進(jìn)貨結(jié)算單，通過purchase_bill_no關(guān)聯(lián)，如以下數(shù)據(jù)：
- 一個(gè)進(jìn)貨結(jié)算單可以對(duì)應(yīng)多個(gè)進(jìn)貨單，通過purchase_bill_no關(guān)聯(lián)，settlement_voucher表中的purchase_bill_no字段存放多個(gè)進(jìn)貨單號(hào)，使用英文逗號(hào)隔開。如以下數(shù)據(jù)：
發(fā)票表(invoice)和結(jié)算單表(settlement_voucher)有一個(gè)關(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é)算單是多對(duì)多的關(guān)系

2. 需求

現(xiàn)在需要以進(jìn)貨結(jié)算單表(settlement_voucher)查詢出一個(gè)列表：

列表字段有【進(jìn)貨時(shí)間(多個(gè)使用英文逗號(hào)隔開)、進(jìn)貨平均單價(jià)、進(jìn)貨預(yù)定總金額，結(jié)算單號(hào)，結(jié)算平均單價(jià)，結(jié)算金額，結(jié)算時(shí)間，發(fā)票號(hào)碼(多個(gè)使用英文逗號(hào)隔開)，發(fā)票代碼(多個(gè)使用英文逗號(hào)隔開)】
查詢條件有：進(jìn)貨時(shí)間(一個(gè)進(jìn)貨結(jié)算單對(duì)應(yīng)多個(gè)進(jìn)貨單時(shí)，只要有一個(gè)進(jìn)貨單的時(shí)間在范圍內(nèi)，就查詢到)，結(jié)算時(shí)間，發(fā)票號(hào)碼(一個(gè)結(jié)算單對(duì)應(yīng)多個(gè)發(fā)票時(shí)，只要有一個(gè)發(fā)票能關(guān)聯(lián)上，就查詢到)
根據(jù)結(jié)算時(shí)間排序

當(dāng)然，實(shí)際當(dāng)時(shí)的那個(gè)需求，列表字段比這多，查詢條件也比這多......

3. 給表插入數(shù)據(jù)

先給貨單表(purchase_order)和進(jìn)貨結(jié)算單表(settlement_voucher)各自插入10萬(wàn)條數(shù)據(jù)，我這里使用了存儲(chǔ)過程：

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;
 
	-- 插入進(jìn)貨單表，進(jìn)貨時(shí)間隨機(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é)算時(shí)間隨機(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)用存儲(chǔ)過程生成數(shù)據(jù)：

call pre();

生成之后需要隨機(jī)改幾條數(shù)據(jù)，模擬一個(gè)進(jìn)貨單可以對(duì)應(yīng)多個(gè)進(jìn)貨結(jié)算單，以及一個(gè)進(jìn)貨結(jié)算單可以對(duì)應(yīng)多個(gè)進(jìn)貨單兩種情況（這樣數(shù)據(jù)更真實(shí)一點(diǎn)）。

一個(gè)進(jìn)貨單可以對(duì)應(yīng)多個(gè)進(jìn)貨結(jié)算單的情況就不模擬了，這種情況其實(shí)對(duì)這次查詢的影響并不大。

一個(gè)進(jìn)貨結(jié)算單可以對(duì)應(yīng)多個(gè)進(jìn)貨單的情況：

再創(chuàng)建一些發(fā)票數(shù)據(jù)和結(jié)算單-發(fā)票關(guān)聯(lián)數(shù)據(jù)，需要體現(xiàn)多對(duì)多的關(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)貨時(shí)間為條件查詢?yōu)槔ㄒ驗(yàn)橹饕褪沁M(jìn)貨單和進(jìn)貨結(jié)算單關(guān)聯(lián)導(dǎo)致慢查詢），記得需求哦，就是一個(gè)進(jìn)貨結(jié)算單可能對(duì)應(yīng)多個(gè)進(jìn)貨單，只要有其中一個(gè)進(jìn)貨單在時(shí)間范圍內(nèi)，就需要查詢出這條進(jìn)貨結(jié)算單

還有：我上面創(chuàng)建的表中索引也模擬了當(dāng)時(shí)優(yōu)化之前的索引......

4.1 第一版

select 
	GROUP_CONCAT(po.purchase_time) as 進(jìn)貨時(shí)間,
	AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進(jìn)貨均價(jià),
	t.settlement_bill_no as 結(jié)算單號(hào),
	AVG(IFNULL(t.unit_price,0)) as 結(jié)算均價(jià),
	any_value(t.cut_off_time) as 結(jié)算時(shí)間,
	any_value(invoice_tmp.invoice_code) as 發(fā)票代碼,
	any_value(invoice_tmp.invoice_number) as 發(fā)票號(hào)碼
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當(dāng)時(shí)在本地環(huán)境執(zhí)行是用了5秒左右，此時(shí)就已經(jīng)意識(shí)到問題了，這別說(shuō)上生產(chǎn)了，就是在測(cè)試環(huán)境都得掛掉。

但是看看我在自己的垃圾服務(wù)器（雙核4G）上跑這條SQL吧，是根本執(zhí)行不出來(lái)的（雖然公司服務(wù)器好一些，但是生產(chǎn)環(huán)境確實(shí)卡死了）：

當(dāng)時(shí)就還沒沒看執(zhí)行計(jì)劃，一眼看去，這個(gè)SQL中用到了FIND_IN_SET，肯定是不會(huì)走索引的，建了索引也沒用，也就是主要是進(jìn)貨單表(purchase_order)和進(jìn)貨結(jié)算單表settlement_voucher關(guān)聯(lián)會(huì)很慢，畢竟他們是多對(duì)多的關(guān)系，再加上這惡心的需求。所以現(xiàn)在想想該怎么才能不用 FIND_IN_SET。

對(duì)，吃飯期間，突發(fā)奇想：我應(yīng)該可以把進(jìn)貨結(jié)算單表拆成一個(gè)臨時(shí)表，如果進(jìn)貨結(jié)算單表對(duì)應(yīng)了5個(gè)進(jìn)貨單，我就把進(jìn)貨結(jié)算單拆成5條數(shù)據(jù)，這五條數(shù)據(jù)除了進(jìn)貨單號(hào)不一樣，其他字段都一樣，這樣就可以不用FIND_IN_SET了。

說(shuō)干就干，于是有了下面第二版SQL。

4.2 第二版

向把進(jìn)貨結(jié)算單表拆分成上面說(shuō)的臨時(shí)表，需要添加一個(gè)表：

CREATE TABLE `incre_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用于分割進(jìn)貨結(jié)算單表';
-- 注意：這里一個(gè)進(jìn)貨結(jié)算單對(duì)應(yīng)多少個(gè)進(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);

先來(lái)看看怎么把一條進(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_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';

來(lái)解釋一下這個(gè)騷操作：

首先我創(chuàng)建了一個(gè)只有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ù)（就剛好是一個(gè)進(jìn)貨結(jié)算單對(duì)應(yīng)的進(jìn)貨單條數(shù)）。
然后使用SUBSTRING_INDEX去一個(gè)一個(gè)拆分settlement_voucher表中的進(jìn)貨單號(hào)

這套SQL執(zhí)行的結(jié)果就是：

綜合起來(lái)，就寫好了第二版SQL：

select 
	GROUP_CONCAT(po.purchase_time) as 進(jìn)貨時(shí)間,
	AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進(jìn)貨均價(jià),
	t.settlement_bill_no as 結(jié)算單號(hào),
	AVG(IFNULL(t.unit_price,0)) as 結(jié)算均價(jià),
	any_value(t.cut_off_time) as 結(jié)算時(shí)間,
	any_value(invoice_tmp.invoice_code) as 發(fā)票代碼,
	any_value(invoice_tmp.invoice_number) as 發(fā)票號(hào)碼
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;

測(cè)試查詢數(shù)據(jù)結(jié)果肯定是沒有問題的哦?。。?/p>

好的，到這里終于把所有用到FIND_IN_SET的地方去掉了，這時(shí)看索引就有意義了！

看看執(zhí)行計(jì)劃吧：

阿西巴，一堆的全表掃描，看看上面第二版SQL，發(fā)現(xiàn)進(jìn)貨表(purchase_order)的purchase_bill_no字段是應(yīng)該走索引的，按道理這個(gè)字段一般設(shè)計(jì)表的時(shí)候就應(yīng)該已經(jīng)加索引了，但是我以為只是我以為，它確實(shí)沒加索引，好的，那就給它加上索引吧：

create index idx_purchase_bill_no on purchase_order(purchase_bill_no);

加完是這個(gè)索引后，再看看執(zhí)行計(jì)劃：

purchase_order表的purchase_bill_no已經(jīng)走了索引，但是settlement_invoice_relation咋不走索引，它是有兩個(gè)索引的。。。。。。

再看看在我的垃圾服務(wù)器上執(zhí)行，看能不能執(zhí)行出來(lái)：

好了，為了讓settlement_invoice_relation表的查詢也走索引，開始下一輪的SQL優(yōu)化

4.3 第三版

就不在下面去聚合獲取invoice_code和invoice_number了，在上面來(lái)聚合，至于要以這兩個(gè)字段作為查詢條件，那可以把下面這條SQL再包一層，作為一個(gè)臨時(shí)表再查詢一遍，這里就不演示了

select 
	GROUP_CONCAT(po.purchase_time) as 進(jìn)貨時(shí)間,
	AVG(IFNULL(po.purchase_pre_unit_price,0)) as 進(jìn)貨均價(jià),
	t.settlement_bill_no as 結(jié)算單號(hào),
	AVG(IFNULL(t.unit_price,0)) as 結(jié)算均價(jià),
	any_value(t.cut_off_time) as 結(jié)算時(shí)間,
	GROUP_CONCAT(DISTINCT invoice_tmp.invoice_code) as 發(fā)票代碼,
	GROUP_CONCAT(DISTINCT invoice_tmp.invoice_number) as 發(fā)票號(hào)碼
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;

再看看執(zhí)行計(jì)劃：

這時(shí)，基本優(yōu)化結(jié)束，再看看在我的垃圾服務(wù)器上跑出的結(jié)果：

到這里，基本上生產(chǎn)上是可以在三秒以內(nèi)查詢出來(lái)了，本次SQL優(yōu)化就到此結(jié)束了！??！

但是，其實(shí)還是可以繼續(xù)優(yōu)化的，但是設(shè)計(jì)到系統(tǒng)改的地方比較多了，影響面比較大，這里就說(shuō)一下思路，暫時(shí)不能實(shí)踐：

可以把進(jìn)貨單表purchase_order和進(jìn)貨結(jié)算單表settlement_voucher之間，建立一個(gè)中間表，實(shí)現(xiàn)多對(duì)多的關(guān)系，再加以索引，應(yīng)該會(huì)更快，而且可以一勞永逸，以后這種關(guān)聯(lián)都會(huì)比較方便了！