一区二区三区四区无码免费 ,天天躁日日躁狠狠躁喷水,青娱乐极品黄色视频,特黄AAAAAAAA免费观看视频,伊人网大香蕉视频,欧.美干逼逼,亚洲一卡二卡精品,2022狠狠干

一前言

不管是開發(fā)同學(xué)還是DBA,想必大家都遇到慢查詢(select,update,insert,delete 語句慢)，影響業(yè)務(wù)穩(wěn)定性。這里說的慢，有兩個含義一是比正常的慢，有可能正常執(zhí)行時間是10ms，異常的是100ms 。二是sql執(zhí)行時間超過設(shè)置的慢查詢標(biāo)準(zhǔn)比如500ms。

本文從IT架構(gòu)以及數(shù)據(jù)庫緯度來分析導(dǎo)致sql執(zhí)行慢的原因/場景，拋磚引玉，有不足之處還請大家多多提建議。

二基礎(chǔ)知識

分析慢查詢之前，我們先看看sql執(zhí)行的路徑,理清楚可能會影響sql執(zhí)行速度的相關(guān)因素。

執(zhí)行路徑

app ---[proxy]---db

app --- db

目前大部分的數(shù)據(jù)庫架構(gòu)基本都是上面的路徑，sql從app的應(yīng)用服務(wù)器發(fā)起經(jīng)過proxy然后到db，db執(zhí)行sql進過proxy或者直接返回給app應(yīng)用服務(wù)器。分析這個過程我們可以得到幾個會影響sql執(zhí)行速度的因素。

1 網(wǎng)絡(luò)，各個節(jié)點之間的網(wǎng)絡(luò)

2 OS系統(tǒng) ，即數(shù)據(jù)庫服務(wù)器

3 MySQL數(shù)據(jù)庫本身

三基礎(chǔ)系統(tǒng)層面

3.1 網(wǎng)絡(luò)層面

1 網(wǎng)絡(luò)丟包，重傳

其實這個比較容易理解。當(dāng)sql 從app端發(fā)送到數(shù)據(jù)庫，執(zhí)行完畢，數(shù)據(jù)庫將結(jié)果返回給app端，這個將數(shù)據(jù)返回給app端的過程本質(zhì)是網(wǎng)絡(luò)包傳輸。因為鏈路的不穩(wěn)定性，如果在傳輸過程中發(fā)送丟包會導(dǎo)致數(shù)據(jù)包重傳，進而增加數(shù)據(jù)傳輸時間。從app端來看，就會覺得sql執(zhí)行慢。

2 網(wǎng)卡滿比如大字段

這個場景可能不容易遇到，如果公司業(yè)務(wù)體量很大，比如平時每天300w訂單的電商平臺，平臺大促(雙十一，618)的時候極有可能出現(xiàn)網(wǎng)卡被打滿。網(wǎng)卡帶寬被占滿類似各種節(jié)假日高速公路收費站(網(wǎng)卡)擁堵導(dǎo)致車流(數(shù)據(jù)包傳輸?shù)乃俣?行動緩慢。

3 網(wǎng)絡(luò)鏈路變長

該場景會影響應(yīng)用緯度的一個事務(wù)比如交易下單整體耗時。

我們知道每個節(jié)點之間的數(shù)據(jù)傳輸是需要時間的，比如同城跨機房(15KM)之間的訪問一般網(wǎng)絡(luò)耗時1.5ms左右。

鏈路1 [app1]--調(diào)用--[app2]---[proxy]---[db]相比鏈路2[app1] -- [proxy] --[db]

執(zhí)行一條sql請求會增加 [app1]--[app2]之間的網(wǎng)絡(luò)傳輸耗時大約3ms。如果一個業(yè)務(wù)事件包含30個sql ，那么鏈路1要比鏈路2 多花至少90ms的時間成本。導(dǎo)致業(yè)務(wù)整體變慢。

3.2 受到影響IO的場景

1 磁盤io被其他任務(wù)占用

有些備份策略為了減少備份空間的使用，基于xtrabckup備份的時候使用了compress選項將備份集壓縮。當(dāng)我們需要在數(shù)據(jù)庫服務(wù)器上恢復(fù)一個比較大的實例，而解壓縮的過程需要耗費cpu和占用大量io導(dǎo)致數(shù)據(jù)庫實例所在的磁盤io使用率100%，會影響MySQL 從磁盤獲取數(shù)據(jù)的速度，導(dǎo)致大量慢查詢。

2 raid卡充放電，raid 卡重置

RAID卡都有寫cache(Battery Backed Write Cache),寫cache對IO性能的提升非常明顯,因為掉電會丟失數(shù)據(jù),所以必須由電池提供支持。電池會定期充放電,一般為90天左右,當(dāng)發(fā)現(xiàn)電量低于某個閥值時,會將寫cache策略從writeback置為writethrough,相當(dāng)于寫cache會失效,這時如果系統(tǒng)有大量的IO操作,可能會明顯感覺到IO響應(yīng)速度變慢,cpu 隊列堆積系統(tǒng)load飆高。下面是一個raid充放電導(dǎo)致sql慢查的案例。

root@rac1#megacli  -FwTermLog dsply -aALL
11/08/143:36:58: prCallback: PR completed for pd=0a
11/08/143:36:58: PR cycle complete
11/08/143:36:58: EVT#14842-11/03/12  3:36:58:  35=Patrol Read complete
11/08/143:36:58: Next PR scheduled to start at 11/10/123:01:59 
11/08/140:48:04: EVT#14843-11/04/12  0:48:04:  44=Time established as 11/04/12  0:48:04; (25714971 seconds since power on)
11/08/1415:30:13: EVT#14844-11/05/12 15:30:13: 195=BBU disabled; changing WB virtual disks to WT  ---問題的原因
11/08/1415:30:13: Changein current cache property detected for LD : 0!
11/08/1415:30:13: EVT#14845-11/05/12 15:30:13:  54=Policy change on VD 00/0 to [ID=00,dcp=0d,ccp=0c,ap=0,dc=0,dbgi=0,S=0|0] from [ID=00,dcp=0d,ccp=0d,ap=0,dc=0,dbgi=0,S=0|0]

raid 卡充電將磁盤的寫策略有write back 修改為write through ，io性能急劇下降導(dǎo)致sql慢查，進而影響應(yīng)用層的邏輯處理。

raid 卡重置 當(dāng)raid卡遇到異常時，會進行重置，相當(dāng)于程序重啟，導(dǎo)致系統(tǒng)io hang。此時也會導(dǎo)致sql慢。下圖是生產(chǎn)中遇到的 RAID卡重置案例。

3 io調(diào)度算法

noop（電梯式調(diào)度策略）：

NOOP實現(xiàn)了一個FIFO隊列，它像電梯的工作方式一樣對I/O請求進行組織，當(dāng)有一個新的請求到來時，它將請求合并到最近的請求之后，以此來保證請求同一個介質(zhì)。NOOP傾向于餓死讀而利于寫，因此NOOP對于閃存設(shè)備，RAM以及嵌入式是最好的選擇。

deadline（介質(zhì)時間調(diào)度策略）：

Deadline確保了在一個截至?xí)r間內(nèi)服務(wù)請求，這個截至?xí)r間是可調(diào)整的，而默認(rèn)讀期限短于寫期限。這樣就防止了寫操作因為不能被讀取而餓死的現(xiàn)象。Deadline對數(shù)據(jù)庫類應(yīng)用是最好的選擇。

anticipatory（預(yù)料I/O調(diào)度策略）：

本質(zhì)上與Deadline一樣，但在最后一次讀操作后，要等待6ms，才能繼續(xù)進行對其他I/O請求進行調(diào)度。它會在每個6ms中插入新的I/O操作，而會將一些小寫入流合并成一個大寫入流，用寫入延時換取最大的寫入吞吐量。AS適合于寫入較多的環(huán)境，比如文件服務(wù)器，AS對數(shù)據(jù)庫環(huán)境表現(xiàn)很差。

3.3 cpu 類型

cpu 電源策略是控制cpu運行在哪種模式下的耗電策略的，對于數(shù)據(jù)庫服務(wù)器推薦最大性能模式 以下內(nèi)容摘自《Red Hat Enterprise Linux7 電源管理指南》

 https://access.redhat.com/documentation/zh-cn/red_hat_enterprise_linux/7/pdf/power_management_guide/Red_Hat_Enterprise_Linux-7-Power_Management_Guide-zh-CN.pdf

2 指令集最近遇到的一個性能案例是hw的機器,因為指令集合默認(rèn)關(guān)閉導(dǎo)致性能下降15%。

https://support.huawei.com/enterprise/zh/doc/EDOC1000039566/c2662e35

自己對CPU并不精通，所以這里的2個點并非CPU優(yōu)化配置的全部，自建機房的運維朋友依賴官方技術(shù)支持的建議或者技術(shù)資料的指導(dǎo)來設(shè)置cpu相關(guān)參數(shù)。

四數(shù)據(jù)庫層面

4.1?沒有索引，或者索引不正確

這個場景其實比較容易理解。相信每個DBA工作過程中都會或多或少遇到性能案例都和索引設(shè)計有關(guān):創(chuàng)建表，沒有索引，sql隨著數(shù)據(jù)量增大全表掃描而變慢。這個就不額外舉例子了。

4.2?隱式轉(zhuǎn)換

發(fā)生隱式轉(zhuǎn)換時，MySQL選擇執(zhí)行計劃并不能利用到合適的索引而是選擇全表掃描導(dǎo)致慢查詢。常見的引發(fā)隱式轉(zhuǎn)換的場景如下:

in 參數(shù)包含多個類型,簡單說,就是在IN的入口有一個判斷, 如果in中的字段類型不兼容, 則認(rèn)為不可使用索引.例如 --圖
判斷符號左邊是字符串，右邊是數(shù)字，比如 where a=1;其中a是字符串
多表join時，where 左右兩邊的字段的字符集類型不一致。

4.3?執(zhí)行計劃錯誤

由于MySQL優(yōu)化器本身的不足，選擇執(zhí)行計劃時會導(dǎo)致錯誤的執(zhí)行計劃使sql走了錯誤的索引或者沒有做索引。比如

在檢查某業(yè)務(wù)數(shù)據(jù)庫的slowlog 時發(fā)現(xiàn)一個慢查詢,查詢時間 1.57s ,檢查表結(jié)構(gòu) where條件字段存在正確的組合索引,正確的情況下優(yōu)化器應(yīng)該選擇組合索引,而非為啥會導(dǎo)致慢查詢呢？

  root@rac1 10:48:11>explain select id,gmt_create, gmt_modified,order_id,service_id, seller_id,seller_nick, sale_type from lol where seller_id= 1501204and service_id= 1and sale_type in(3, 4) and use_status in(3, 4, 5, 6) and process_node_id= 6 order by id desc limit 0,20 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lol
         type: index
possible_keys:idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype
          key: PRIMARY  --- 應(yīng)該選擇 idx_sidustsvidtype
      key_len: 8
ref: NULL
         rows: 3076
Extra: Usingwhere
1 row inset (0.00 sec)

4.4?數(shù)據(jù)巨大

比如select count(*) from t1 where a='xxxx'; 盡管字段a有索引，但是如果符合條件的記錄數(shù)超高10w，查詢速度還是會比較慢。

select  count(*) from t1 where app = 'marketing';
+----------+
| count(*) |
+----------+
|  2671690 |
+----------+
1 row inset (0.92 sec)

4.5?MetaData Lock鎖等待

MDL鎖這個場景其實蠻多案例的，比如ddl開始時，針對同一個表的長查詢還沒結(jié)束，后續(xù)的寫操作都會被堵住導(dǎo)致 thread running 飆高。實例整體的sql執(zhí)行慢。

案例一長查詢/mysqldump 阻塞DDL?

未提交事務(wù)阻塞ddl 阻塞查詢

4.6?并發(fā)更新同一行

常見的秒殺場景:數(shù)據(jù)庫并發(fā)執(zhí)行update，更新同一行的動作會被其他已經(jīng)持有鎖的會話堵住，并且需要要進行判斷會不會由于自己的加入導(dǎo)致死鎖，這個時間復(fù)雜度O(n)，如果有1000個請求，每個線程都要檢測自己和其他999個線程是否死鎖。如果其他線程都沒有持有其他鎖，約比較50w次(計算方式 999+998+...+1)。這個種鎖等待和檢查死鎖沖突帶來巨大的時間成本。對于OLTP 業(yè)務(wù)高并發(fā)大流量訪問的情況下，鎖等待會直接導(dǎo)致thread running飆高，所有的請求會被阻塞并等待innodb引擎層處理，于是sql 會變慢。

4.7?數(shù)據(jù)分布不均

其實和數(shù)據(jù)分布相關(guān)，常見的比如字段a 是標(biāo)記狀態(tài)0,1，總行數(shù)1000w，a=0的值大概幾千條，a=1的有999w多。顯然執(zhí)行

select count(*) from tab where a=1 ;

的查詢效率肯定比查詢a=0的要慢很多。

select count(*) from tab where a=0 ;

4.8?sql 姿勢不合理

常見的分頁查詢，使用大分頁深度查詢。

SELECT * FROM tablewhere kid=1342 and type=1 order id desc limit 149420 ,20;

該SQL是一個非常典型的排序+分頁查詢：order by col desc limit N,MMySQL 執(zhí)行此類SQL時需要先掃描到N行，然后再去取 M行。對于此類操作，取前面少數(shù)幾行數(shù)據(jù)會很快，但是掃描的記錄數(shù)越多，SQL的性能就會越差，因為N越大，MySQL需要掃描越多的數(shù)據(jù)來定位到具體的N行，這樣耗費大量的IO 成本和時間成本。

針對limit 優(yōu)化有很多種方式:

1 前端加緩存、搜索，減少落到庫的查詢操作。比如海量商品可以放到搜索里面，使用瀑布流的方式展現(xiàn)數(shù)據(jù)，很多電商網(wǎng)站采用了這種方式。
2 優(yōu)化SQL 訪問數(shù)據(jù)的方式，直接快速定位到要訪問的數(shù)據(jù)行。
3 使用書簽方式，記錄上次查詢最新/大的id值，向后追溯 M行記錄。對于第二種方式我們推薦使用"延遲關(guān)聯(lián)"的方法來優(yōu)化排序操作，何謂"延遲關(guān)聯(lián)" ：通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。

4.9?表結(jié)構(gòu)設(shè)計

表結(jié)構(gòu)設(shè)計是否合理也是影響sql性能的重要因素之一。以下表格展示了字段類型不同帶來的rt性能差異。其中字段c1 為int類型的字段，字段c2則是表名對應(yīng)的字符串長度類型varchar(200)到varchar(5000) ,還有text字段。

對于讀請求，單獨查詢c1 int類型的性能并無差異。查詢字段c2時，隨著字段占用的實際字節(jié)大小增大，耗費的時間增加，也即rt增大。帶寬逐步增大，text的帶寬147MB 對于千兆網(wǎng)卡已經(jīng)滿了。

對于寫請求，因為binlog為row模式，字段長度越大，binlog也越大，網(wǎng)絡(luò)傳輸帶寬增加。整體rt也增加。

4.10?innodb 刷臟頁

對數(shù)據(jù)庫運行機制有一定了解的朋友都會知道InnoDB引擎采用Write Ahead Log(WAL)策略，即事務(wù)提交時，先寫日志(redo log)，再寫磁盤。為了提高IO效率,在寫日志的時候會先寫buffer，然后集中flush buffer pool 到磁盤。 這個過程我們稱之為刷臟頁。官方文檔中描述

With heavy DML activity, flushing can fall behind if it is not aggressive enough, resulting in excessive memory use in the buffer pool; or, disk writes due to flushing can saturate your I/O capacity if that mechanism is too aggressive.這個過程中就有可能導(dǎo)致平時執(zhí)行很快的SQL突然變慢。

4.11?undo 沒有被purge/回收

UNDO 日志是 MVCC 的重要組成部分，當(dāng)一條數(shù)據(jù)被修改時，UNDO 日志里面保存了記錄的歷史版本。當(dāng)事務(wù)需要查詢記錄的歷史版本時，可以通過 UNDO 日志構(gòu)建特定版本的數(shù)據(jù)。

每條行記錄上面都有一個指針 DATA_ROLL_PTR，指向最近的 UNDO 記錄。同時每條 UNDO 記錄包含一個指向前一個 UNDO 記錄的指針，這樣就構(gòu)成了一條記錄的所有 UNDO 歷史的鏈表。當(dāng) UNDO 的記錄還存在，那么對應(yīng)的記錄的歷史版本就能被構(gòu)建出來。
當(dāng)記錄對應(yīng)的版本通過 DATA_TRX_ID 比對發(fā)現(xiàn)不可見時，通過系統(tǒng)列 DATAROLLPTR，找到對應(yīng)的回滾段記錄，繼續(xù)通過上述判斷記錄可見的規(guī)則，進行判斷，如果記錄依舊不可見，繼續(xù)通過回滾段查找之前的版本，直到找到對應(yīng)可見的版本。

所以當(dāng)有長事務(wù)/異常未提交的情況就會因為其他查詢需要構(gòu)建快照導(dǎo)致undo 不能被及時回收。查詢遍歷的undo越多sql執(zhí)行的越慢。

-END-


“養(yǎng)碼場”
現(xiàn)有技術(shù)人80000+
覆蓋JAVA/PHP/IOS/測試等領(lǐng)域
80%級別在P6及以上，含P9技術(shù)大咖30人
技術(shù)總監(jiān)和CTO?500余人

還在寫慢SQL？

二 基礎(chǔ)知識