記錄一次慢SQL優化：大表關聯小表-＞拆解為單表查詢

前言：

最近一段時間總是會收到資料庫CPU報警

一查發現有一個特别慢的SQL，調用的頻率很高，并且查一次SQL15s以上，十分影響性能

SQL分析

這個sql是一個分頁查詢的sql，每次查出10條主表是cfg_category_organization （大約50W+資料）

需要補充部分資訊：cfg_category （大約1000條資料）、cfg_org （大約2W資料）

按說這個資料量不大，但是就是非常慢

select
  b.*,
  co.organization_path,
  co.organization_full_name,
  a.name,
  a.status
from
  cfg_category_organization b
inner join cfg_category a on b.category_id = a.id
inner join cfg_org co on b.organization_code = co.organization_code
where
  b.is_delete = 0
  and co.is_delete = 0
  and a.is_delete = 0
  and co.organization_path like concat('/001/002/003/004', "%")
  and b.category_id = 7
order by
  b.status desc,
  b.update_time desc
limit 10;      

用explain看一下，發現表cfg_category 、cfg_org 的rows是很少的，隻有cfg_category_organization 幾乎進行了全表掃描，開銷較大。

SQL優化

是以我們可以采用，把小表查詢出來的結果作為條件，in到大表中，并且對應的大表字段都是有索引的

1.cfg_category 表單獨拎出來作為一個SQL，當有這張表的字段作為查詢條件時，就select出這張表的key：

select id from cfg_category where is_delete = 0 and id = 7      

如果說傳回參數為0，後面的内容均不需要查了，肯定聯查不出來任何資料，這樣就減少了一小部分無效查詢

2.同理cfg_org 表也單獨拎出來，如果有這張表的字段作為查詢條件的時候，就select出這張表的key：

select organization_code   from cfg_org where is_delete = 0 and organization_path like concat('/001/002/003/004', "%")      

同理，如果說傳回參數為0，後面的内容均不需要查了，肯定聯查不出來任何資料，這樣就減少了一小部分無效查詢

3.如果前兩步都能查出對應的資料，則對最大的表進行查詢：

select
  b.*
from
  cfg_category_organization b
where
  b.is_delete = 0
  and b.organization_code in('004', '005')
  and b.category_id in (7)
order by
  b.status desc,
  b.update_time desc
limit 10      

如果說沒有任何查詢條件，則SQL就是如下情況:

select
  b.*
from
  cfg_category_organization b
where
  b.is_delete = 0
order by
  b.status desc,
  b.update_time desc
limit 10      

分頁查詢，充其量單表查詢傳回的結果就是10條，那麼查出的10條，再組裝一下，category_id 、organization_code 字段，分别去小表查一次，再利用Java代碼拼接出來即可

select name,status from cfg_category where id in (7);
select organization_path,organization_full_name from cfg_org where organization_code in ('004','005');      

優化後效果不錯：平均15-20s查詢的慢SQL，優化到0.3-0.6s

總結與改進