慢SQL優化實戰筆記

zhangdeTalk發表於2020-07-26
SQL優化筆記

經過sql慢查詢的優化，我們系統中發現了以下幾種型別的問題:

1.未建索引：整張表沒有建索引；

2.索引未命中：有索引,但是部分查詢條件下索引未命中；

3.搜尋了額外的非必要欄位，導致回表；

4.排序，聚合導致慢查詢；

5.相同內容多次查詢資料庫；

6.未消限制搜尋範圍或者限制的搜尋範圍在預期之外，導致全部掃描；
1.優化索引，增加或者修改當前的索引；         

2.重寫sql；

3.利用redis快取，減少查詢次數；

4.增加條件，避免非必要查詢；

5.增加條件，減少查詢範圍；

（一）藥材搜尋介面

完整sql語句在附錄，為方便閱讀和脫敏，部分常用欄位採用中文。

這兒主要講一下我們拿到Sql語句後的整個分析過程，思考邏輯，然後進行調整的過程和最後解決的辦法。

給大家提供一些借鑑，也希望大家能夠提出更好的建議。

這個sql語句要求是根據醫生搜尋的拼音或者中文，進行模糊查詢，找到藥材，然後根據醫生選擇的藥庫，查詢下面的供應商，然後根據供應商，進行藥材匹配，排除掉供應商沒有的藥材，然後根據真名在前，別名在後，完全匹配在前，部分匹配在後，附加醫生最近半年的使用習慣，把藥材排序出來。最後把不同名稱的同一味藥聚合起來，以真名(另名)的形式展現。

1．分析sql

  • （1）14-8

第14排,id為8的explain結果分析:

①Explain
8,DERIVED,ssof,range,"ix_district,ix_供應商id",ix_district,8,NULL,18,Using where; Using index; Using temporary
②Sql
SELECT DISTINCT (ssof.供應商id) AS 供應商id FROM  藥庫供應商關係表 AS ssof  WHERE ssof.藥庫id IN (  1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33)  AND ssof.藥方劑型id IN (1)
③索引
PRIMARY KEY (`id`),    UNIQUE KEY `ix_district` (        `藥庫id`, `藥方劑型id`, `供應商id`    ) USING BTREE,KEY `ix_供應商id` (`供應商id`) USING BTREE
④分析

使用了索引，建立了臨時表，這個地方索引已經完全覆蓋了，但是還有回表操作。

原因是用in，這個導致了回表。如果in可以被mysql 自動優化為等於，就不會回表。如果無法優化，就回表。

臨時表是因為有distinct，所以無法避免。

同時使用in需要注意，如果裡面的值數量比較多，有幾萬個。即使區分度高，就會導致索引失效，這種情況需要多次分批查詢。

2． 12-7

  • （1）Explain
7,DERIVED,<derived8>,ALL,NULL,NULL,NULL,NULL,18,Using temporary; Using filesort
  • （2）Sql
INNER JOIN (上面14-8臨時表) tp ON tp.供應商id= ms.供應商id
  • （3）索引

無

  • （4）分析

對臨時表操作，無索引，用了檔案排序。

這一部分是對臨時表和藥材表進行關聯操作的一部分，有檔案排序是因為需要對藥材表id進行group by 導致的。

1、預設情況下，mysql在使用group by之後，會產生臨時表，而後進行排序（此處排序預設是快排），這會消耗的效能。

2、group by本質是先分組後排序【而不是先排序後分組】。

3、group by column 預設會按照column分組, 然後根據column升序排列; group by column order by null 則預設按照column分組,然後根據標的主鍵ID升序排列。

3． 13-7

  • （1）Explain
7,DERIVED,ms,ref,"ix_title,idx_audit,idx_mutiy",idx_mutiy,5,"tp.供應商id,const",172,NULL
  • （2）Sql
SELECT ms.藥材表id, max(ms.audit) AS audit, max(ms.price) AS price, max(ms.market_price) AS market_price,max(ms.is_granule) AS is_granule,max(ms.is_decoct) AS is_decoct, max(ms.is_slice) AS is_slice,max(ms.is_cream) AS is_cream, max(ms.is_extract) AS is_extract,max(ms.is_cream_granule) AS is_cream_granule, max(ms.is_extract_granule) AS is_extract_granule,max(ms.is_drychip) AS is_drychip,            max(ms.is_pill) AS is_pill,max(ms.is_powder) AS is_powder, max(ms.is_bolus) AS is_bolus FROM 供應商藥材表 AS ms INNER JOIN (                SELECT                    DISTINCT (ssof.供應商id) AS 供應商id                FROM                    藥庫供應商關係表 AS ssof WHERE  ssof.藥庫id IN (  1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33 ) AND ssof.藥方劑型id IN (1) ) tp ON tp.供應商id= ms.供應商id WHERE  ms.audit = 1  GROUP BY  ms.藥材表id
  • （3）索引
   KEY `idx_mutiy` (`供應商id`, `audit`, `藥材表id`)
  • （4）分析

命中了索引，表間連線使用了供應商id，建立索引的順序是供應商id，where條件中audit，Group by 條件藥材表id。

這部分暫時不需要更改。

4．10-6

  • （1）Explain
6,DERIVED,r,range,"PRIMARY,id,idx_timeline,idx_did_timeline,idx_did_isdel_statuspay_timecreate_payorderid,idx_did_statuspay_ischecked_isdel",idx_did_timeline,8,NULL,546,Using where; Using index; Using temporary; Using filesort
  • （2）Sql
SELECT 
       count(*) AS total, 
       rc.i AS m藥材表id 
     FROM 
        處方藥材表 AS rc 
        INNER JOIN 藥方表AS r ON r.id = rc.藥方表_id 
     WHERE 
         r.did = 40 
         AND r.timeline > 1576115196 
         AND rc.type_id in (1, 3) 
         GROUP BY 
    rc.i
  • （3）索引
KEY `idx_did_timeline` (`did`, `timeline`),
  • （4）分析

驅動表與被驅動表，小表驅動大表。

先了解在join連線時哪個表是驅動表，哪個表是被驅動表：

1.當使用left join時，左表是驅動表，右表是被驅動表；

2.當使用right join時，右表時驅動表，左表是驅動表；

3.當使用join時，mysql會選擇資料量比較小的表作為驅動表，大表作為被驅動表；

4. in後面跟的是驅動表, exists前面的是驅動表；

5． 11-6

  • （1）Explain
6,DERIVED,rc,ref,"orderid_藥材表,藥方表_id",藥方表_id,5,r.id,3,Using where
  • （2）Sql

同上

  • （3）索引
  KEY `idx_藥方表_id` (`藥方表_id`, `type_id`) USING BTREE,
  • （4）分析

索引的順序沒有問題，仍舊是in 導致了回表。

6．8-5

  • （1）Explain
5,UNION,malias,ALL,id_tid,NULL,NULL,NULL,4978,Using where
  • （2）Sql
 SELECT 
      mb.id, 
      mb.sort_id, 
      mb.title, 
      mb.py, 
      mb.unit, 
      mb.weight, 
      mb.tid, 
      mb.amount_max, 
      mb.poisonous, 
      mb.is_auxiliary, 
      mb.is_auxiliary_free, 
      mb.is_difficult_powder, 
      mb.brief, 
      mb.is_fixed_recipe, 
      ASE WHEN malias.py = 'GC' THEN malias.title ELSE CASE WHEN malias.title = 'GC' THEN malias.title ELSE '' END END AS atitle, 
      alias.py AS apy, 
      CASE WHEN malias.py = 'GC' THEN 2 ELSE CASE WHEN malias.title = 'GC' THEN 2 ELSE 1 END END AS ttid 
 FROM 
      藥材表 AS mb 
      LEFT JOIN 藥材表 AS malias ON malias.tid = mb.id 
WHERE 
      alias.title LIKE '%GC%' 
      OR malias.py LIKE '%GC%'
  • （3）索引 
KEY `id_tid` (`tid`) USING BTREE,
  • （4）分析

因為like是左右like，無法建立索引，所以只能建tid。Type是all，遍歷全表以找到匹配的行，左右表大小一樣，估算的找到所需的記錄所需要讀取的行數有4978。這個因為是like的緣故，無法優化，這個語句並沒有走索引，藥材表 AS mb FORCE INDEX (id_tid) 改為強制索引，讀取的行數減少了700行。

7．9-5

  • （1）Explain
5,UNION,mb,eq_ref,"PRIMARY,ix_id",PRIMARY,4,malias.tid,1,NULL
  • （2）Sql

同上

  • （3）索引
PRIMARY KEY (`id`) USING BTREE,
  • （4）分析

走了主鍵索引，行數也少，通過。

8．7-4

  • （1）Explain
4,DERIVED,mb,ALL,id_tid,NULL,NULL,NULL,4978,Using where

  • （2）Sql 

    SELECT 
   mb.id, 
   mb.sort_id, 
   mb.title, 
   mb.py, 
   mb.unit, 
   mb.weight, 
   mb.tid, 
   mb.amount_max, 
   mb.poisonous, 
   mb.is_auxiliary, 
   mb.is_auxiliary_free, 
   mb.is_difficult_powder, 
   mb.brief, 
   mb.is_fixed_recipe, 
   '' AS atitle, 
   '' AS apy, 
   CASE WHEN mb.py = 'GC' THEN 3 ELSE CASE WHEN mb.title = 'GC' THEN 3 ELSE 1 END END AS ttid 
FROM 
   藥材表 AS mb 
  WHERE 
   mb.tid = 0 
   AND (
       mb.title LIKE '%GC%' 
       OR mb.py LIKE '%GC%'
                            )

  • （3）索引

KEY `id_tid` (`tid`) USING BTREE,

  • （4）分析

    tid int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘真名藥品的id’,

他也是like,這個沒法優化。

9．6-3

  • （1）Explain
3,DERIVED,<derived4>,ALL,NULL,NULL,NULL,NULL,9154,Using filesort

  • （2）Sql

    UNION ALL

  • （3）索引

無

  • （4）分析

就是把真名搜尋結果和別人搜尋結果合併。避免用or連線，加快速度 形成一個munion的表，初步完成藥材搜尋，接下去就是排序。

這一個進行了2次查詢，然後用union連線，可以考慮合併為一次查詢。用case when進行區分,計算出權重。

這邊是一個優化點。

10．4-2

  • （1）Explain
2,DERIVED,<derived3>,ALL,NULL,NULL,NULL,NULL,9154,NULL

  • （2）Sql

    
SELECT 
   munion.id, 
   munion.sort_id, 
   case when length(
     trim(
          group_concat(munion.atitle SEPARATOR ' ')
                    )
                )> 0 then concat(
                    munion.title, 
                    '(', 
                    trim(
                        group_concat(munion.atitle SEPARATOR ' ')
                    ), 
                    ')'
                ) else munion.title end as title, 
      munion.py, 
      munion.unit, 
      munion.weight, 
      munion.tid, 
      munion.amount_max, 
      munion.poisonous, 
      munion.is_auxiliary, 
      munion.is_auxiliary_free, 
      munion.is_difficult_powder, 
      munion.brief, 
      munion.is_fixed_recipe, 
      --  trim( group_concat( munion.atitle SEPARATOR ' ' ) ) AS atitle,
                ##  --  
       trim(
             group_concat(munion.apy SEPARATOR ' ')
             ) AS apy, 
         ##   
           max(ttid) * 100000 + id AS ttid 
     FROM 
            munion <derived4>
         GROUP BY 
            id -- 全部實名藥材 結束##

  • （3）索引

無

  • （4）分析

這裡全部在臨時表中搜尋了。

11．5-2

  • （1）Explain
2,DERIVED,<derived6>,ref,<auto_key0>,<auto_key0>,5,m.id,10,NULL
  • （2）Sql
Select fields from 全部實名藥材表 as m  LEFT JOIN ( 個人使用藥材統計表 ) p ON m.id = p.m藥材表id
  • （3）索引

無

  • （4）分析

2張虛擬表left join

使用了優化器為派生表生成的索引

這邊比較浪費效能，每次查詢，都要對醫生歷史開方記錄進行統計，並且統計還是幾張大表計算後的結果。但是如果只是sql優化，這邊暫時無法優化。

12．2-1

  • （1）Explain
1,PRIMARY,<derived7>,ALL,NULL,NULL,NULL,NULL,3096,Using where; Using temporary; Using filesort

  • （2）Sql

  • （3）索引

  • （4）分析

臨時表操作

13．3-1

  • （1）Explain
1,PRIMARY,<derived2>,ref,<auto_key0>,<auto_key0>,4,msu.藥材表id,29,NULL

  • （2）Sql

  • （3）索引

  • （4）分析

臨時表操作

14．null

  • （1）Explain
NULL,UNION RESULT,"<union4,5>",ALL,NULL,NULL,NULL,NULL,NULL,Using temporary

  • （2）Sql

  • （3）索引

  • （4）分析

臨時表

（二）優化sql

上面我們只做索引的優化,遵循的原則是:

1.最左字首匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式。

3.儘量選擇區分度高的列作為索引，區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄。

4.索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’)。

5.儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。

查詢優化神器 - explain命令

關於explain命令相信大家並不陌生，具體用法和欄位含義可以參考官網explain-output，這裡需要強調rows是核心指標，絕大部分rows小的語句執行一定很快（有例外，下面會講到）。所以優化語句基本上都是在優化rows。

化基本步驟：

0.先執行看看是否真的很慢，注意設定SQL_NO_CACHE

1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高；

2.explain檢視執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）；

3.order by limit 形式的sql語句讓排序的表優先查；

4.瞭解業務方使用場景；

5.加索引時參照建索引的幾大原則；

6.觀察結果，不符合預期繼續從0分析；

上面已經詳細的分析了每一個步驟，根據上面的sql，去除union操作， 增加索引。可以看出，優化後雖然有所改善。但是距離我們的希望還有很大距離，但是光做sql優化，感覺也沒有多少改進空間,所以決定從其他方面解決。

（三）拆分sql

由於速度還是不領人滿意,尤其是個人用藥情況統計,其實沒必要每次都全部統計一次,再要優化,只靠修改索引應該是不行的了,所以考慮使用快取。

接下來是修改php程式碼,把全部sql語句拆分,然後再組裝。

  • （1）搜尋真名,別名(快取)
SELECT  mb.id,  mb.sort_id,  mb.title,  mb.py,  mb.unit,  mb.weight,  mb.tid,  mb.amount_max,  mb.poisonous,  mb.is_auxiliary,  mb.is_auxiliary_free,  mb.is_difficult_powder,  mb.brief,  mb.is_fixed_recipe,  IFNULL(group_concat(malias.title),'') atitle,  IFNULL(group_concat(malias.py),'') apy  FROM  藥材表 AS mb  LEFT JOIN 藥材表 AS malias ON malias.tid = mb.id  WHERE  mb.tid = 0  AND (  malias.title LIKE '%GC%'  OR malias.py LIKE '%GC%'  or mb.title LIKE '%GC%'  OR mb.py LIKE '%GC%'  )  group by  mb.id

  • （2）如果命中有藥材
①排序

真名在前,別名在後,完全匹配在前,部分匹配在後

//對搜尋結果進行處理,增加權重
②對供應商藥材搜尋
SELECT ms.藥材表id, max( ms.audit ) AS audit, max( ms.price ) AS price, max( ms.market_price ) AS market_price, max( ms.is_granule ) AS is_granule, max( ms.is_decoct ) AS is_decoct, max( ms.is_slice ) AS is_slice, max( ms.is_cream ) AS is_cream, max( ms.is_extract ) AS is_extract, max( ms.is_cream_granule) AS is_cream_granule, max( ms.is_extract_granule) AS is_extract_granule, max( ms.is_drychip ) AS is_drychip, max( ms.is_pill ) AS is_pill, max( ms.is_powder ) AS is_powder, max( ms.is_bolus ) AS is_bolus  FROM 供應商藥材表 AS ms WHERE ms.audit = 1 AND ms.供應商idin (  SELECT DISTINCT  ( ssof.供應商id) AS 供應商id FROM  藥庫供應商關係表 AS ssof  WHERE  ssof.藥庫id IN ( 1,2,8,9,10,11,12,13,14,15,17,22,24,25,26,27,31,33 )  AND ssof.藥方劑型id IN (1) ) AND ms.藥材表id IN ( 78,205,206,207,208,209,334,356,397,416,584,652,988,3001,3200,3248,3521,3522,3599,3610,3624,4395,4396,4397,4398,4399,4400,4401,4402,4403,4404,4405,4406,4407,4408,5704,5705,5706,5739,5740,5741,5742,5743,6265,6266,6267,6268,6514,6515,6516,6517,6518,6742,6743 ) AND ms.is_slice = 1  GROUP BY ms.藥材表id

③拿醫生歷史開方藥材用量資料(快取)
SELECT  count( * ) AS total,  rc.i AS 藥材表id FROM  處方藥材表 AS rc  INNER JOIN 藥方表AS r ON r.id = rc.藥方表_id WHERE  r.did = 40  AND r.timeline > 1576116927  AND rc.type_id in (1,3) GROUP BY  rc.i

④ 裝配及排序微調

  • （3）小結

執行速度,對於開方量不是特別多的醫生來說,兩者速度都是0.1秒左右.但是如果碰到開方量大的醫生,優化後的sql速度比較穩定,能始終維持在0.1秒左右,優化前的sql速度會超過0.2秒.速度提升約一倍以上。

最後對搜尋結果和未優化前的搜尋結果進行比對,結果數量和順序完全一致.本次優化結束。

四、附錄：


SELECT sql_no_cache 
    *
FROM
    (
        -- mbu start##
        SELECT
            m.*,
            ifnull(p.total, 0) AS total
        FROM
            (
                --
全部實名藥材
開始
##
SELECT
       munion.id,
       munion.sort_id,
       case when length(
        trim(
              group_concat(munion.atitle SEPARATOR ' ')
                 )
             )> 0 then concat(
           munion.title,
          '(',
      trim(
             group_concat(munion.atitle SEPARATOR ' ')
              ),
                ')'
             ) else munion.title end as title,
        munion.py,
        munion.unit,
        munion.weight,
        munion.tid,
        munion.amount_max,
        munion.poisonous,
        munion.is_auxiliary,
        munion.is_auxiliary_free,
        munion.is_difficult_powder,
        munion.brief,
        munion.is_fixed_recipe,
        --  trim( group_concat( munion.atitle SEPARATOR ' ' ) ) AS atitle,##
        --  trim( group_concat( munion.apy SEPARATOR  ' ' ) ) AS apy,##
              max(ttid) * 100000 + id AS ttid
           FROM
              (
                -- #union start
聯合查詢
,
得到全部藥材
##
  (
       SELECT
              mb.id,
              mb.sort_id,
              mb.title,
              mb.py,
              mb.unit,
              mb.weight,
              mb.tid,
              mb.amount_max,
              mb.poisonous,
              mb.is_auxiliary,
              mb.is_auxiliary_free,
              mb.is_difficult_powder,
              mb.brief,
              mb.is_fixed_recipe,
              '' AS atitle,
              '' AS apy,
              CASE WHEN mb.py = 'GC' THEN 3 ELSE CASE WHEN mb.title = 'GC' THEN 3 ELSE 1 END END AS ttid
               FROM
                 藥材表 AS mb
                     WHERE
                         mb.tid = 0
                       AND (
                              mb.title LIKE '%GC%'
                              OR mb.py LIKE '%GC%'
                                )
                        ) --
真名藥材

結束
##
 UNION ALL
    (
      SELECT
            mb.id,
            mb.sort_id,
            mb.title,
            mb.py,
            mb.unit,
            mb.weight,
            mb.tid,
            mb.amount_max,
            mb.poisonous,
            mb.is_auxiliary,
            mb.is_auxiliary_free,
            mb.is_difficult_powder,
            mb.brief,
            mb.is_fixed_recipe,
            CASE WHEN malias.py = 'GC' THEN malias.title ELSE CASE WHEN malias.title = 'GC' THEN malias.title ELSE '' END END AS atitle,
            malias.py AS apy,
            CASE WHEN malias.py = 'GC' THEN 2 ELSE CASE WHEN malias.title = 'GC' THEN 2 ELSE 1 END END AS ttid
          FROM
                藥材表 AS mb
                LEFT JOIN 藥材表 AS malias ON malias.tid = mb.id
          WHERE
                malias.title LIKE '%GC%'
                OR malias.py LIKE '%GC%'
                      ) --
其他藥材結束
##
                 -- #union end##
                ) munion
                GROUP BY
                    id --
全部實名藥材

結束
##
                    ) m
            LEFT JOIN (
                --
個人使用藥材統計

開始
##
    SELECT
          count(*) AS total,
          rc.i AS m藥材表id
     FROM
           處方藥材表 AS rc
           INNER JOIN 藥方表AS r ON r.id = rc.藥方表_id
      WHERE
           r.did = 40
            AND r.timeline > 1576115196
            AND rc.type_id in (1, 3)
       GROUP BY
              rc.i --
個人使用藥材統計

結束
##
           ) p ON m.id = p.m藥材表id -- mbu end ##
            ) mbu
    INNER JOIN (
        -- msu start
供應商藥材篩選
##
        SELECT
            ms.藥材表id,
            max(ms.audit) AS audit,
            max(ms.price) AS price,
            max(ms.market_price) AS market_price,
            max(ms.is_granule) AS is_granule,
            max(ms.is_decoct) AS is_decoct,
            max(ms.is_slice) AS is_slice,
            max(ms.is_cream) AS is_cream,
            max(ms.is_extract) AS is_extract,
            max(ms.is_cream_granule) AS is_cream_granule,
            max(ms.is_extract_granule) AS is_extract_granule,
            max(ms.is_drychip) AS is_drychip,
            max(ms.is_pill) AS is_pill,
            max(ms.is_powder) AS is_powder,
            max(ms.is_bolus) AS is_bolus
        FROM
            供應商藥材表 AS ms
            INNER JOIN (
                SELECT
                    DISTINCT (ssof.供應商id) AS 供應商id
                FROM
                    藥庫供應商關係表 AS ssof
                WHERE
                    ssof.藥庫id IN (
                        1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22,
                        24, 25, 26, 27, 31, 33
                    )
                    AND ssof.藥方劑型id IN (1)
            ) tp ON tp.供應商id= ms.供應商id
        WHERE
            ms.audit = 1
        GROUP BY
            ms.藥材表id -- msu end ##
            ) msu ON mbu.id = msu.藥材表id
WHERE
    msu.藥材表id > 0
    AND msu.is_slice = 1
order by
    total desc,
    ttid desc
本作品採用《CC 協議》，轉載必須註明作者和本文連結

阿德

相關文章