Mysql 慢查詢優化實踐

LIONIDO發表於2019-01-29
MySql優化

Mysql 慢查詢優化實踐

目標:

提高mysql執行效率,增加併發,提高響應速度

方案:

通過阿里雲給的慢查詢日誌excel,對耗時長,開銷大的sql語句進行優化,提升訪問速度伺服器執行效率

實踐:

分析 阿里雲給的資料庫單日報表有以下欄位

  • Create Time DBName
  • MySQL Total Execution Counts
  • MySQL Total Execution Times
  • Max Execution Time
  • Max Lock Time
  • Parse Total Row Counts
  • Parse Max Row Count
  • Return Total Row Counts
  • Return Max Row Count
  • SQL Text
  • hard ware- latencies

分別是

  • 建立時間
  • 資料庫名
  • mysql總執行數目
  • muysql總執行耗時
  • 最大執行耗時
  • 最大鎖耗時
  • 解析總行數統計
  • 解析最大行數
  • 返回總計行數
  • 返回最大行數
  • sql語句
  • 硬體延遲

根據阿里雲提供的慢查詢記錄,本次採用的優化策略如下:

查詢次數超過100次/日的高頻需求,按照最大查詢/總查詢用時最大,依次優化取得的優化收益最高.

第一條語句:

執行次數: 1114 最大耗時: 7 解析最大行數: 348325 返回最大行數 4 #執行次數: 1114 最大耗時: 7 解析最大行數: 348325 返回最大行數 4

select id from appname_m_members where yiku_id = :1
複製程式碼

可以看出,這個簡單的sql不應該有這麼大的解析行數,甚至最高要七秒鐘.
初步判斷沒有在yiku_id這個欄位加索引的可能性最大.現在我們需要尋求各種辦法來驗證下我們的猜測

分析
explain select id from appname_m_members where yiku_id = 1;
複製程式碼

Mysql 慢查詢優化實踐

可以看到的確是沒有給yiku_id增加索引.

索引的特點
對於查詢操作能迅速縮小查詢範圍,減少row的數量,指數級提高查詢速度點
對於寫操作,因為需要維護索引的變更,有一定開銷.如果遇到大量併發寫入,會有負面影響.
在這個表用來記錄我們微信使用者和應用id的關係,所以讀的操作較之寫操作更多,所以能夠增加索引.

#增加索引

ALTER TABLE `appname_m_members` 
ADD INDEX `yiku_id` (`yiku_id`) ;
複製程式碼

嘗試增加索引之後,再次分析語句的執行

Mysql 慢查詢優化實踐

結果:

匹配範圍 rows 從32w 降低到1

可以看到type從all的全表掃描變成ref的單個行的索引訪問,rows從全表32w降為1,說明新增索引對這條語句產生了巨大效果.

第二條語句:

執行次數: 482 最大耗時: 15 解析最大行數: 764383 返回最大行數: 482

#執行次數: 482 最大耗時: 15 解析最大行數: 764383 返回最大行數: 482
select fullname as username , linkphone as userphone , 
`userimage` , `nickname` , `hospitalname` , `partmentname` , 
`doctortitle` , `iscertification` , `fullname` 
from `users` 
where `useruuid` = '597_f66e1cb79341cedf6f24aaf01fde8611' limit 1;
複製程式碼
分析:

Mysql 慢查詢優化實踐

對其增加索引:
#增加索引
ALTER TABLE `users` 
ADD INDEX `useruuid` (`useruuid`);
複製程式碼

Mysql 慢查詢優化實踐

直接將掃描範圍(rows)從72w降到了1,提升明顯

結果:

匹配範圍 rows 從72w 降低到1

第三條語句:

執行次數: 820 最大耗時: 10 解析最大行數: 167214 返回最大行數 1

#執行次數: 820 最大耗時: 10 解析最大行數: 167214  返回最大行數 1
select count ( postingid ) as postnum from mediposting 
where isaudit != :1
and isgoodcase = :2
and postsection = :3
複製程式碼
分析:

Mysql 慢查詢優化實踐

改變sql語句的順序,按照最左原則修改如下

select count(postingid) as postnum from mediposting 
where postsection = 1
and isgoodcase = 1
and isaudit != 1
複製程式碼
結果:

主要使用的是 postsection 作為索引來統計總數,這部分無需優化.

##### 第四條語句:
執行次數: 482 最大耗時: 15 解析最大行數: 764383 返回最大行數: 482
##執行次數: 410 最大耗時: 10 解析最大行數:348325   返回最大行數 1
........
複製程式碼
結果: 語句過長(2017個字元),巢狀了邏輯,暫不優化
第五條語句:

執行次數: 659 最大耗時: 6 解析最大行數:215115 返回最大行數 659

## 執行次數: 659 最大耗時: 6 解析最大行數:215115   返回最大行數 659
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
  `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
   ( initalscore+effectevaladd ) as `effectval` 
   from `medigooddoc` 
   where ( ( initalscore+effectevaladd ) > 80 ) 
   order by rand ( ) limit 1 ;
複製程式碼
分析:

rand()函式放在order by後面會被執行多次,優化方式: 求出隨機id後,取得對應記錄

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
 `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` 
 where (initalscore+effectevaladd)  > 80
 and docid > ( 
     RAND() * (
         (SELECT MAX(docid) FROM `medigooddoc`) 
         -
         (SELECT MIN(docid) FROM `medigooddoc`)
    )
    +
    (SELECT MIN(docid) FROM `medigooddoc`) 
 )
 order by `docid` limit 1;
複製程式碼

優化前語句:

Mysql 慢查詢優化實踐

可以看到掃描範圍很大(rows) 120 770行.

Mysql 慢查詢優化實踐

可以看到

  • 查詢範圍最小(rows) 1
  • 獲取最大值/最小值是直接從mysql查詢優化器返回資料(extra).mysql文件中有以下解釋:

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

測試執行效率:

  • 執行10次 a: 2 941 ms b: 168 ms
  • 執行50次 a: 14 441 ms b: 828 ms
  • 執行100次a: 29 126 ms b: 1 645 ms

可以看到每百次執行時間已經從30s縮短到不到2秒,大大提高查詢mysql響應速度. 但是還有個問題,總共100 000的id,原來的語句查詢出的結果比較平衡,有過萬也有幾千,但是用這個語句後,總是出現小於一萬的id,結果在我們預期之外.

修正概率偏差

方案1:

增加一次對資料庫消耗不大的表查詢

# php
$round = select max(docid) as max,min(docid) as min from medigooddoc;
$rand = rand($round['min'],$round['max']);
複製程式碼
# sql
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
 `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` 
 where (initalscore+effectevaladd)  > 80
 and docid > $rand
 order by `docid` limit 1;
複製程式碼

這樣的問題是:會多產生一個sql互動,資料庫

方案2:

使用內連線 join 優化

#可用一
select `docid` ,`docname`,
 `doctitle` , `docimgurl` ,
 `docdep` , `dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` as t1 
 join (
     select rand() * (select max(docid) from `medigooddoc`) 
     as rand
 ) as t2 
 where (t1.initalscore+t1.effectevaladd)  > 80
 and `t1`.`docid` >= t2.rand
 order by `docid` limit 1;
複製程式碼

但是這樣有一個問題:並不是完全平均落到每條記錄上,因為記錄並不是連續的

修正概率 rand * 數量範圍,這樣概率平均到整張表存在的記錄中.

select `docid` ,`docname`,
 `doctitle` , `docimgurl` ,
 `docdep` , `dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` as t1 
 join (
     select rand() * 
     (
         (select max(docid) from `medigooddoc`)
         -
         (select min(docid) from `medigooddoc`)
     )
     +
     (select min(docid) from `medigooddoc`)  
     as rand
 ) as t2 
 where (t1.initalscore+t1.effectevaladd)  > 80
 and `t1`.`docid` >= t2.rand
 order by `docid` limit 1;
複製程式碼

綜合來說,因為方案1 產生了更多的資料庫互動,因為我們的資料庫是另一臺伺服器,網路連線開銷是比較大的,額外的查詢也會在高併發的時刻對資料庫產生更大壓力.

而方案2採用內連線的方式,僅需要一次資料庫互動就能完成,最大最小值也是直接由mysql查詢器返回,減少了種種資料庫效能開銷.故採用為最佳方案..

Mysql 慢查詢優化實踐

結果:

使用mysql儲存的表結構資訊替代了order rand()的低效率查詢.

深入理解:

Mysql 慢查詢優化實踐

第六條語句:

執行次數: 729 最大耗時: 4秒 解析最大行數:130898 返回最大行數 2

select `medigooddoc`.`docid` , `medigooddoc`.`yikuid` 
from `medigooddoc` 
where ( yikuid = 597725 or yikuid = -597725 );
複製程式碼
分析:

Mysql 慢查詢優化實踐

優化方案:

欄位yikuid加索引

ALTER TABLE `medigooddoc`
ADD INDEX `YiKuID` (`YiKuID`);
複製程式碼

再次執行explain分析

Mysql 慢查詢優化實踐

結果:

匹配範圍 rows 從8.3w 降低到1

第七條語句

執行次數: 474 最大耗時: 5秒 解析最大行數:261797 返回最大行數 1

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` 
 from `medigooddoc` order by rand ( ) limit 1;
複製程式碼
分析

Mysql 慢查詢優化實踐

方案

將獲取一條隨機記錄 由order by rand() limit 1 改為 內連線方式

select `docid`, `docname`,
  `doctitle` , `docimgurl` 
 from `medigooddoc` as  t1 
 inner join 
 (
     select rand()  *
     (
         (select MAX(docid) from `medigooddoc`)
         -
         (select MIN(docid) from `medigooddoc`)
     )
     +
     (select MIN(docid) from `medigooddoc`)
     as rand
 ) as t2 
 on t1.docid >= t2.rand
 order by docid limit 1;
複製程式碼

再次執行explain分析

Mysql 慢查詢優化實踐

結果

用mysql儲存的表資訊替代了效率低下的order by rand()

第八條語句

執行次數: 136 最大耗時: 7秒 解析最大行數:301880 返回最大行數 1

select `searchrecords`.`searchid` , `searchrecords`.`searchnum` 
from `searchrecords` 
where ( searchtype = 0 ) and ( userid = 14 ) 
and ( searchmsg = '碳酸鈣D3' );
複製程式碼
分析

Mysql 慢查詢優化實踐

方案

索引的目的是為了縮小查詢範圍,通過文字內容的前三個字區分,通過userid進行區分,可以得到範圍更精確的語句執行

ALTER TABLE searchrecords ADD INDEX searchmsg (searchmsg(5));
ALTER TABLE searchrecords ADD INDEX userid (userid);
複製程式碼

通過文字前5個字建立索引來區分範圍後,範圍縮小到28個記錄

Mysql 慢查詢優化實踐

再通過使用者ID建立索引,進一步縮小範圍,僅需要查詢1條記錄

Mysql 慢查詢優化實踐

分析索引對寫入的影響 表主要用來記錄使用者搜尋的高頻詞,主要的寫操作時更新統計欄位,這兩個新增索引的欄位並不會頻繁更新,故索引開銷不大.

結果

匹配範圍從 29w 縮小到 1

第九條語句
select `projects`.`id` , `projects`.`guid` , 
`projects`.`getittime` , `projects`.`keywords` ,
 `projects`.`barcode` as `num` , `projects`.`goodcasedep` ,
  `projects`.`bingshi` , `pictures`.* 
  from `projects` 
  inner join `pictures` on projects.guid = pictures.projectid 
  and pictures.filetype = :1 
  where ( islock != :2 ) and ( isgoodcase = :3 ) 
  and ( ( goodcasedep like :4 or goodcasedep like :5 
  or goodcasedep like :6 or goodcasedep like :7 
  or goodcasedep like :8 or goodcasedep like :9 
  or goodcasedep like :10 or goodcasedep like :11 
  or goodcasedep like :12 or goodcasedep like :13 
  or goodcasedep like :14 or goodcasedep like :15 
  or goodcasedep like :16 or goodcasedep like :17 
  or goodcasedep like :18 or goodcasedep like :19 
  or goodcasedep like :20 or goodcasedep like :21 
  or goodcasedep like :22 or goodcasedep like :23 
  or goodcasedep like :24 or goodcasedep like :25 
  or goodcasedep like :26 or goodcasedep like :27 
  or goodcasedep like :28 or goodcasedep like :29 
  or goodcasedep like :30 or goodcasedep like :31 
  or goodcasedep like :32 or goodcasedep like :33 
  or goodcasedep like :34 or goodcasedep like :35 
  or goodcasedep like :36 or goodcasedep like :37 
  or goodcasedep like :38 or goodcasedep like :39 
  or goodcasedep like :40 or goodcasedep like :41 ) ) 
  order by rand ( ) limit :42
複製程式碼
結果:

暫不修改:超過位元組限制

第十條語句

執行次數: 145 最大耗時: 2秒 解析最大行數:130898 返回最大行數 1

select `medigooddoc`.`isfollow` , `medigooddoc`.`isconsult` ,
 `medigooddoc`.`isphone` , `medigooddoc`.`isprivate` 
 from `medigooddoc` where ( yikuid =  694 );
複製程式碼
分析:

Mysql 慢查詢優化實踐

方案

增加索引

ALTER TABLE `medigooddoc` ADD INDEX YiKuID(`YiKuID`);
複製程式碼

再次執行explain分析

Mysql 慢查詢優化實踐

結果

匹配範圍從12w縮小到1

第十一條

執行次數: 148 最大耗時: 3秒 解析最大行數:74616 返回最大行數 30

select `magazinearticle`.`articleid` , 
`magazinearticle`.`articletitle` , 
`magazinearticle`.`article_publishtime` ,
 `magazinearticle`.`articlepicpath` ,
  `magazinearticle`.`articleurl` ,
   `magazinearticle`.`articlenum` ,
    `magazinearticle`.`perid` ,
     `magazinearticle`.`article_originallink` ,
      `magazinearticle`.`islink` from `magazinearticle` 
      where ( logicdel = 0 ) and ( perid != 60 ) 
      order by `article_publishtime` desc limit 1,30;
複製程式碼
分析:

Mysql 慢查詢優化實踐

方案:

由於是讀多寫少的文章表,增加索引適用這類場景,提高查詢響應速度.

ALTER TABLE `magazinearticle` ADD INDEX 
article_publishtime(`article_publishtime`);
複製程式碼

再次執行explain分析

Mysql 慢查詢優化實踐

結果:

匹配範圍 rows 從2w縮小到59

深入理解:

explain type的不同種類

型別 含義
型別 含義
system 表只有一行
const 表最多隻有一行匹配，通用用於主鍵或者唯一索引比較時
eq_ref 每次與之前的表合併行都只在該表讀取一行，這是除了system，const之外最好的一種，特點是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引
ref 如果每次只匹配少數行，那就是比較好的一種，使用=或<=>，可以是左覆蓋索引或非主鍵或非唯一鍵
fulltext 全文搜尋
ref_or_null 與ref類似，但包括NULL
index_merge 表示出現了索引合併優化(包括交集，並集以及交集之間的並集)，但不包括跨表和全文索引。
這個比較複雜，目前的理解是合併單表的範圍索引掃描（如果成本估算比普通的range要更優的話)
unique_subquery 在in子查詢中，就是value in (select...)把形如“select unique_key_column”的子查詢替換。
PS：所以不一定in子句中使用子查詢就是低效的！
index_subquery 同上，但把形如”select non_unique_key_column“的子查詢替換
range 常數值的範圍
index a.當查詢是索引覆蓋的，即所有資料均可從索引樹獲取的時候（Extra中有Using Index)
b.以索引順序從索引中查詢資料行的全表掃描（無 Using Index）；
c.如果Extra中Using Index與Using Where同時出現的話，則是利用索引查詢鍵值的意思；
d.如單獨出現，則是用讀索引來代替讀行，但不用於查詢
all 全表掃描
第十二條

執行次數: 135 最大耗時: 3秒 解析最大行數:78395 返回最大行數 0

select distinct userid from weekhosnominate 
where userid = 351211 and datatype = 4
複製程式碼
分析

Mysql 慢查詢優化實踐

方案
ALTER TABLE `weekhosnominate` ADD INDEX UserID(`UserID`);
複製程式碼

再次執行explain分析

Mysql 慢查詢優化實踐

結果

匹配範圍 rows 從1w縮小到288

第十三條

執行次數: 110 最大耗時: 2秒 解析最大行數:87693 返回最大行數 1

select `inspectioninfo`.`itemmsg` from `inspectioninfo` 
where ( itemid in ( 30 ,31 ) and itemtype = 0
and inspectionid = 109 ) limit 1 ;
複製程式碼
分析

Mysql 慢查詢優化實踐

方案:

增加索引

ALTER TABLE `inspectioninfo` ADD INDEX 
InspectionID(`InspectionID`);
複製程式碼

再次執行explain分析

Mysql 慢查詢優化實踐

結果

匹配範圍 rows 從 5w 縮小到 13

第十四條語句

執行次數: 103 最大耗時: 2秒 解析最大行數:78395 返回最大行數 0

select `weekhosnominate`.`id` from `weekhosnominate` 
where ( userid = 351211 );
複製程式碼
分析:

Mysql 慢查詢優化實踐

方案:

通過給欄位 userid 建立索引來區分,縮小範圍

ALTER TABLE `weekhosnominate` ADD INDEX UserID(UserID) ;
複製程式碼

再次執行explain分析可以發現, 通過索引 userid 將範圍由全表掃描的近萬到索引指向的數十條記錄.

Mysql 慢查詢優化實踐

結果:

匹配範圍 rows 從 9k 縮小到 288

深入理解:

Mysql 慢查詢優化實踐
mysql結構

mysql索引原理

索引目的
索引的目的在於提高查詢效率，可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然後從下往下找到y字母，再找到剩下的sql。如果沒有索引，那麼你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺得如果沒有索引，這個事情根本無法完成？

索引原理
除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得資料的範圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查詢方式來鎖定資料。

資料庫也是一樣，但顯然要複雜許多，因為不僅面臨著等值查詢，還有範圍查詢(>、<、between、in)、模糊查詢(like)、並集查詢(or)等等。資料庫應該選擇怎麼樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把資料分成段，然後分段查詢呢？最簡單的如果1000條資料，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條資料，只要找第三段就可以了，一下子去除了90%的無效資料。但如果是1千萬的記錄呢，分成幾段比較好？稍有演算法基礎的同學會想到搜尋樹，其平均複雜度是lgN，具有不錯的查詢效能。但這裡我們忽略了一個關鍵的問題，複雜度模型是基於每次相同的操作成本來考慮的，資料庫實現比較複雜，資料儲存在磁碟上，而為了提高效能，每次又可以把部分資料讀入記憶體來計算，因為我們知道訪問磁碟的成本大概是訪問記憶體的十萬倍左右，所以簡單的搜尋樹難以滿足複雜的應用場景。

磁碟IO與預讀
前面提到了訪問磁碟，那麼這裡先簡單介紹一下磁碟IO和預讀，磁碟讀取資料靠的是機械運動，每次讀取資料花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁軌所需要的時間，主流磁碟一般在5ms以下；旋轉延遲就是我們經常聽說的磁碟轉速，比如一個磁碟7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms；傳輸時間指的是從磁碟讀出或將資料寫入磁碟的時間，一般在零點幾毫秒，相對於前兩個時間可以忽略不計。那麼訪問一次磁碟的時間，即一次磁碟IO的時間約等於5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一臺500 -MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令，資料庫動輒十萬百萬乃至千萬級資料，每次9毫秒的時間，顯然是個災難。下圖是計算機硬體延遲的對比圖，供大家參考：

Mysql 慢查詢優化實踐
硬體處理延遲

REFER:

MySQL索引原理及慢查詢優化

相關文章