• <acronym id="eyrpt"></acronym>
    <track id="eyrpt"></track>
    <p id="eyrpt"></p>

      <table id="eyrpt"><ruby id="eyrpt"></ruby></table>
      <table id="eyrpt"></table>

    1. 當前位置:首頁 > 短網址資訊 > 正文內容

      MySQL升級5.6 SQL變慢調優案例


      背景:

      某業務DB從5.5升級5.6后,同一sql執行時間由毫秒級飆升到了20秒,sql文本如下

      select * from big_table as t
      where ( t.plan_TYPE = 1 or t.plan_TYPE=3 )
      and t.limit_TYPE in (10)
      and t.xx_ID = 25773208367
      and t.USER_ID in (133174222100)
      plan by t.gmt_create desc , t.ID desc limit 1,10

      以下是解決過程:

      查看短網址當前執行計劃

      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t
               type: range
      possible_keys: idx_xx_id,idx_gmt_create
                key: idx_gmt_create
            key_len: 17
                ref: NULL
               rows: 6816016
              Extra: Using index condition; Using where
      1 row in set (0.00 sec)

      表上短網址數據庫的索引分布

        PRIMARY KEY (`id`),
        KEY `idx_xx_id` (`xx_id`,`plan_type`,`user_id`),
        ……
        KEY `idx_gmt_create` (`limit_type`,`xx_id`,`gmt_create`)

      該sql選擇索引idx_gmt_create,因其符合最左前綴策略,故排序沒有使用到filesort,其訪問路徑大致如下:


      • 反向掃描idx_gmt_create葉子結點,搜尋(limit_type,xx_id)=(10,25773208367)的元組;

      • 回表,驗證該元組對應的主鍵記錄是否滿足約束(plan_type,user_id)=(1 or 3, 133174222100),滿足則計數加1否則丟棄,繼續掃描下一個元組;

      • 當計數達到10時,停止掃描,將對應的10條記錄返回給客戶端; 


      root@ 03:20:56>select limit_type,count(*) from big_table group by limit_type;
      +-------------+----------+
      | limit_type | count(*) |
      +-------------+----------+
      | NULL | 226865 |
      | 9 | 463346 |
      | 10 | 13353116 |
      +-------------+----------+
      3 rows in set (3.13 sec)

      作為復合索引的引導列,limit_type字段的選擇性驚人的低,這是查詢變慢的主要原因之一。之所以要強調”之一”,是因為針對本例,只要其他字段足夠給力,即便limit_type=10也能很快執行完畢,查看xx_id的分布情況,也是比較畸形,該sql又很不幸的選擇了候選行最多的那個。

      root@ 04:01:12>select xx_id,count(*) from big_table where limit_type =10 group by xx_id order by xx_id desc;
      +-------------+----------+
      | xx_id | count(*) |
      +-------------+----------+
      | 25773208367 | 13352433 |
      | 25770261347 | 2 |
      | 258809681 | 148 |
      | 1 | 2100 |
      +-------------+----------+
      4 rows in set (5.79 sec)

      如果xx_id=1,該sql最多只需要比較2100條記錄即可返回,會很快執行完畢,然后返回短網址背后對應的原始長地址;

      即便xx= 25773208367,如果能快速找出滿足非索引字段約束的主鍵記錄,sql也會很快執行完畢,mysql是在驗證了海量的(limit_type,xx_id)=(10,25773208367)元組后,才湊齊10條同時滿足(plan_type,user_id)約束的主鍵記錄,據此我們可以反推出最早滿足所有約束條件的user_id,其查詢邏輯如下:

      select user_id,count(*) from big_table t where limit_type =10 and xx_id =25773208367 and ( t.plan_TYPE = 1 or t.plan_TYPE=3 ) group by user_id having count(*)>=10 order by gmt_create desc limit 1,5; 5 rows in set (1 min 12.42 sec)

      執行結果:

      +------------+----------+
      | user_id | count(*) |
      +------------+----------+
      | 1851362558 | 15 |
      | 2118141658 | 11 |
      | 2641244918 | 14 |
      | 2448823838 | 17 |
      | 16375410 | 32 |
      +------------+----------+
      5 rows in set (1 min 12.42 sec)

      隨便挑一個替換25773208367,比如1851362558,執行計劃沒有變,原本需要運行20多秒的sql卻在200毫秒內執行完畢。

       

      而5.5版本的執行計劃為:

      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t
               type: range
      possible_keys: idx_xx_id
                key: idx_xx_id
            key_len: 18
                ref: NULL
               rows: 116
              Extra: Using where; Using filesort

      雖然采用了filesort,但是經索引idx_xx_id過濾后的候選行非常少,故執行時間很短。


       

      解決方案

      修改sql,添加force index (idx_xx_id),此方案不夠靈活;

      修改sql,將排序字段從gmt_create改為gmt_modified,因無法采用索引排序5.6會選擇idx_xx_id,此方案可能造成返回數據有誤;

      修改sql,將t.limit_TYPE in (10)改為t.limit_TYPE >9 and t.limit_TYPE <11,優化器會認為sql沒有滿足索引最左前綴便不再使用idx_gmt_create,這招似乎有點賤,同時說明MySQL優化器還不夠智能。

       

      結束語

      5.6優化器做了大量改進,以本sql為例,讓其選擇了idx_gmt_create從而省去了filesort,之所以運行變慢了是因為表字段數據分布太不均勻,而本sql又湊巧滿足了各種坑,這算是一個意外吧。




      掃描二維碼推送至手機訪問。

      版權聲明:本文由短鏈接發布,如需轉載請注明出處。

      本文鏈接:http://www.virginiabusinesslawupdate.com/article_313.html

      分享給朋友:

      相關文章

      FT12短網址:繪制隨機不規則三角彩條——小談FT12短網址主頁的實現

      FT12短網址:繪制隨機不規則三角彩條——小談FT12短網址主頁的實現

      引言8月6號早讀文章由@FT12短網址分享。正文從這開始~緣起最近感覺自己搭的一個項目資料匯總的項目,主頁真的是一點美感都沒有,非常簡單粗暴。就想來點改版,讓主頁顯得高大上一點。改版前的效果是這樣的:尋思著怎么改版,剛開始想就簡單加個背景圖...

      群雄逐鹿的云計算時代,誰能獨領風騷

      群雄逐鹿的云計算時代,誰能獨領風騷

      [ FT12短網址 ] 由德意志銀行數據顯示,中國國內公有云市場2016年增長率為150-200%。國內的云服務器市場是一超多強的局面,阿里云領跑,騰訊緊隨,其次亞馬遜和微軟,之后是UCloud,以及是其他國內玩家,如華為云和中國...

      如何進行網站分析?

      如何進行網站分析?

      網站分析的首要目標是提升線上客戶的用戶體驗。短網址分析不是提供報表的一種技術,而是優化網站的一個有效的流程。下述的框架有助于在公司建立數據驅動的文化,去監測客戶與網站的交互,細分客戶群體,了解每個不同群體的行為,分析不同營銷活動的回報率,以...

      食品與健康之間有著千絲萬縷的關系

      特朗普總統在別的方案中表明,星期二減少了核算藍圖,彌補營養援助方案(曾經稱為食物券方案)將在十年內喪失其資金的29%。傳統基金會聯邦核算事務所的Romina Boccia在一份聲明中表明,保守派集體贊揚核算提案聯系添加的國防開銷和減少“國內...

      你才喜歡郭敬明呢,你全家都喜歡郭敬明

      在我們哪所十八線鄉村野雞高中,喜歡郭敬明是一件特別酷的事情呢吃過沒文化的虧,高中讀郭敬明的書,喜歡到想哭,尤其是《小時代》,唐宛如那句“讓我受到了驚嚇”是高中生活特別好的調味劑,適合跟朋友各種逗樂的語境。后來也試著再翻翻看,畢竟當初覺得幽默...

      FT12短網址教你如何記錄閱讀進度

      大家是否會遇到這些需求:如果有三個tab頁簽,從某個tab頁簽下跳出去打開新頁后,點擊瀏覽器后退鍵,能回到跳出去的相應tab頁簽下希望像原生app那樣在wap端的列表頁跳到詳情頁,點擊瀏覽器后退鍵,能回到跳出去的列表處如果有上拉加載更多,希...

      發表評論

      訪客

      ◎歡迎參與討論,請在這里發表您的看法和觀點。
      一本色综合网久久
    2. <acronym id="eyrpt"></acronym>
      <track id="eyrpt"></track>
      <p id="eyrpt"></p>

        <table id="eyrpt"><ruby id="eyrpt"></ruby></table>
        <table id="eyrpt"></table>