背景說明
基礎(chǔ)環(huán)境介紹
- 應(yīng)用環(huán)境: 基于springcloud的java應(yīng)用
- 數(shù)據(jù)庫：mysql
- DAO: mybatis+pagehelper
主要庫表結(jié)構(gòu)
- leads_info 銷售線索主表拣帽，數(shù)據(jù)量約40萬條，本次涉及的關(guān)鍵字段:
- id 線索編號(hào)
- phone_no 客戶電話號(hào)碼
- cust_name 客戶姓名
- first_duplicate_id 本線索第一條重復(fù)線索的id
- last_duplicate_id 本線索的最后一條重復(fù)線索
- enquire_date 線索留資時(shí)間
- cust_base_info 若客戶姓名有修改記錄嚼锄，數(shù)據(jù)量約1300條
- id 修改記錄id
- leads_id 線索id
- name 客戶姓名
主要業(yè)務(wù)邏輯：
- 重復(fù)線索：若有同樣電話的銷售線索减拭，則新插入一條數(shù)據(jù)，將新插入的數(shù)據(jù)的first_group_id設(shè)置為第一條同樣號(hào)碼的線索区丑；第一條線索的last_group_id設(shè)置為最新的一條線索id
- 修改客戶姓名：
- 在cust_base_info中查找對(duì)應(yīng)線索id的數(shù)據(jù)拧粪，若有則更新數(shù)據(jù)，若無則插入一條數(shù)據(jù)沧侥；
- 若此線索為重復(fù)線索可霎，則所有修改只針對(duì)其first_group_id來做
PS：再次吐槽這樣的設(shè)計(jì)
問題發(fā)現(xiàn)
問題癥狀
原來系統(tǒng)在執(zhí)行的過程中，就發(fā)現(xiàn)系統(tǒng)在列表與分頁的過程中宴杀，發(fā)現(xiàn)系統(tǒng)比較慢癣朗，但是還可以接受，后來發(fā)現(xiàn)系統(tǒng)越來越慢旺罢，直至導(dǎo)致springcloud超時(shí)（我們?cè)O(shè)置了15秒的超長(zhǎng)超時(shí)時(shí)間）
關(guān)鍵sql
經(jīng)過查詢?nèi)罩究跤啵l(fā)現(xiàn)出現(xiàn)問題的列表sql如下
select
a.id as "id",
a.site_id as "siteId",
a.name as "name",
……
c.name as "custBaseInfo.name"
from
leads_info a
left join sys_user b on
a.next_track_user = b.id
left join cust_base_info c on
(a.id = c.leads_id
or a.first_duplicate_id = c.leads_id)
and c.del_flag = 0
WHERE
a.del_flag = 0
and a.site_id = ?
and (a.last_duplicate_id is null
or a.last_duplicate_id is not null
and a.id = a.last_duplicate_id)
order by
a.enquire_date desc
LIMIT 20
- where條件主要是為了只取最新的一條數(shù)據(jù)
- join的條件是為了保證在新線索來以前修改的客戶姓名
問題分析與解決
第一步，分析leads_info
發(fā)現(xiàn)leads_info表除了id是primary key以外扁达，無任何索引正卧，先給他加上索引
- 索引一
- site_id
- enquire_date
- 索引二
- site_id
- last_duplicate_id
- enquire_date
添加索引后，發(fā)現(xiàn)速度有所提升罩驻，但是并沒有明顯的提升
第二部穗酥，分析join表
首先去掉left join cust_base_info，執(zhí)行下面的sql
select
a.id as "id",
a.site_id as "siteId",
a.name as "name",
……
c.name as "custBaseInfo.name"
from
leads_info a
left join sys_user b on
a.next_track_user = b.id
WHERE
a.del_flag = 0
and a.site_id = ?
and (a.last_duplicate_id is null
or a.last_duplicate_id is not null
and a.id = a.last_duplicate_id)
order by
a.enquire_date desc
LIMIT 20
發(fā)現(xiàn)速度飛快惠遏，可以確定是這個(gè)join的問題
查詢cust_base_info表砾跃，發(fā)現(xiàn)此表的id是主鍵，leads_id無索引节吮，left join可能存在一對(duì)多抽高，對(duì)此表的leads_id設(shè)置為unique索引
建立索引后，發(fā)現(xiàn)上述sql性能有了飛速的提升透绩，在dbeaver中查詢時(shí)間降低到了ms級(jí)別翘骂，但是在生產(chǎn)系統(tǒng)修改了庫表以后壁熄，發(fā)現(xiàn)前端展示仍未能解決，相應(yīng)時(shí)間有所降低碳竟，但是仍然無法忍受草丧，需要等待將近10s
最終解決
繼續(xù)查詢系統(tǒng)日志，發(fā)現(xiàn)因?yàn)榉猪摰男枰ㄎΓ趫?zhí)行列表查詢以前昌执，系統(tǒng)需要首先做一個(gè)count的操作，具體sql如下：
select
count(1)
from
leads_info a
left join sys_user b on
a.next_track_user = b.id
left join cust_base_info c on
(a.id = c.leads_id
or a.first_duplicate_id = c.leads_id)
and c.del_flag = 0
此sql的響應(yīng)時(shí)間為大概9s诈泼，找到原因懂拾，發(fā)現(xiàn)就出在了left join cust_base_info的條件上，因?yàn)槔锩嬗衞r條件铐达，故此mysql在執(zhí)行的時(shí)候因?yàn)闊o法保證left join不增加數(shù)據(jù)條數(shù)岖赋，所以需要把所有的數(shù)據(jù)全部完成join以后才能進(jìn)行count統(tǒng)計(jì)，果斷將這個(gè)條件作了修改：
select
a.id as "id",
a.site_id as "siteId",
a.name as "name",
……
c.name as "custBaseInfo.name"
from
leads_info a
left join sys_user b on
a.next_track_user = b.id
left join cust_base_info c on
ifnull(a.fist_duplicate_id, a.id) = c.leads_id
and c.del_flag = 0
WHERE
a.del_flag = 0
and a.site_id = ?
and (a.last_duplicate_id is null
or a.last_duplicate_id is not null
and a.id = a.last_duplicate_id)
order by
a.enquire_date desc
修改后瓮孙，系統(tǒng)性能得到飛速提升唐断，列表頁面響應(yīng)時(shí)間可以控制在1s以內(nèi)了。
總結(jié)
總結(jié)本次問題衷畦，得出要想在分頁時(shí)得到最佳的性能栗涂，總原則如下：
- 通過主表（本例中的leads_info)與從表（本例中的cust_base_info）關(guān)鍵，要盡可能的使用left join操作祈争，少用inner join斤程，避免使用outer join
- 主表與其他表left join的條件最好是主表的某一個(gè)字段去關(guān)聯(lián)主表的主鍵或unique的屬性值
- 關(guān)聯(lián)條件中不要有or條件
- 查詢條件集中在主表上，盡量不要涉及從表的字段菩混；唯一可以有的是判斷是否關(guān)聯(lián)上忿墅，本例中可以用 where b.leads_is is null，對(duì)于必須關(guān)鍵上的條件沮峡，可以寫成 inner join
