數(shù)據(jù)庫查詢性能優(yōu)化一直是程序員繞不開的話題，當(dāng)我們遇到業(yè)務(wù)刷新報表緩慢或者查詢獲取結(jié)果延遲太大，可以采用提問法來思考如何進行優(yōu)化。

[[379574]]

1. 什么樣的環(huán)境

硬件環(huán)境

query執(zhí)行的速度和我們的硬件息息相關(guān)，當(dāng)前用的什么樣的CPU，有多少核多少線程， 內(nèi)存有多大都直接影響了運算速度， 磁盤是SSD還是HDD，網(wǎng)卡什么速率都直接影響了我們數(shù)據(jù)讀取的時延

軟件環(huán)境

軟件環(huán)境雖然不像硬件一樣，各種參數(shù)看的見摸得著，但仍然影響著我們的查詢性能。沒一套系統(tǒng)實際上都在特定的場景有著各自的優(yōu)勢。我們的查詢系統(tǒng)是什么樣的架構(gòu)，適合什么樣的query，在線還是離線， 計算多還是數(shù)據(jù)讀取多，這些在我們做優(yōu)化的時候都應(yīng)該了然于心。

下面我們根據(jù)這種思路來看看如何做性能優(yōu)化

2. 什么樣的query

首先我們優(yōu)化查詢的時候，需要看看query 究竟是哪種類型。寫入還是查詢(這里鑒于篇幅只談查詢)， CPU密集還是IO密集。如果我們的系統(tǒng)是適合OLTP低延時點查的場景， 想要在這種系統(tǒng)上做OLAP大規(guī)模分析很顯然就不太適合， OLTP一般專注于數(shù)據(jù)一致性較高的點查，而OLAP由于數(shù)據(jù)量龐大，一般都需要采用向量并發(fā)查詢。OLAP不專注于毫秒級的低延遲， 而OLTP不專注于上億級的數(shù)據(jù)統(tǒng)計。

3. 如何尋找性能瓶頸

3.1 vmstat查看系統(tǒng)情況

整體系統(tǒng)不知道當(dāng)前的瓶頸在哪里時， 我們可以先用vmstat工具來簡單的看一下系統(tǒng)的大致情況。如下圖所示，2表示每個兩秒采集一次服務(wù)器狀態(tài)。

 

 

 

 

procs : 查看進程狀態(tài)

r : 運行隊列，即當(dāng)前可運行(正在運行或者等待運行)的進程數(shù)量。目前CPU比較空閑，這個數(shù)量很小，當(dāng)這個值超過了CPU數(shù)目，就會出現(xiàn)CPU瓶頸了。

b : 阻塞的進程,即處在不可中斷sleep狀態(tài)下的進程數(shù)量。

memory : 查看內(nèi)存狀態(tài)

swpd : 已使用的虛擬內(nèi)存大小，如果大于0，表示機器開始使用虛擬內(nèi)存了，虛擬內(nèi)存運行會很慢。這里數(shù)值為0表示我們關(guān)閉了虛擬內(nèi)存功能。

free : 空閑的物理內(nèi)存的大小。

buff : 內(nèi)存做為系統(tǒng)buffers的大小。

cache : 內(nèi)存做為系統(tǒng)cache的大小。

swap : 磁盤和內(nèi)存做數(shù)據(jù)交換的狀態(tài)

nesi : 每秒從磁盤讀入虛擬內(nèi)存的大小，如果這個值大于0，表示物理內(nèi)存不夠。

so : 每秒虛擬內(nèi)存寫入磁盤的大小。

io：磁盤的io信息

bi : 每秒從塊設(shè)備接收的塊數(shù)量。

bo : 每秒發(fā)送給塊設(shè)備的塊數(shù)量。

如果這兩個值較大，表示IO比較頻繁，可以考慮IO優(yōu)化。

system : 系統(tǒng)狀態(tài)信息

in : 每秒CPU的中斷次數(shù)(包括時鐘中斷)

cs : 每秒上下文切換次數(shù)，我們調(diào)用系統(tǒng)函數(shù)、線程的切換，就需要上下文切換，這個值要太大就可以考慮 減少系統(tǒng)的上下文切換，比如協(xié)程替代多線程等方式。

CPU : CPU信息

us : 包括用戶時間和nice時間，跑非內(nèi)核的代碼(或者用戶代碼)的時間。

sy : 系統(tǒng)占用時間，跑內(nèi)核代碼(比如系統(tǒng)調(diào)用)占用的時間。

id : 花費在idle上的 CPU時間。

wa : 等待IO CPU時間。如果這個值太大，表示IO系統(tǒng)瓶頸在IO上。

如果CPU占用高表示系統(tǒng)在CPU上， 如果系統(tǒng)的swap比較頻繁，很可能是系統(tǒng)內(nèi)存泄露或者內(nèi)存不夠用，需要擴展內(nèi)存， 如果是IO等待較多則系統(tǒng)瓶頸出現(xiàn)在IO上，如果上下文切換，或者系統(tǒng)調(diào)用占比太大，則我們需要思考下我們程序的設(shè)計，減少系統(tǒng)調(diào)用或者上下文切換。

3.2 CPU占用過高

我們可以通過uptime、top、mpstat或者sar等一些工具來查看當(dāng)前CPU占用過高的情況.

我們可以通過uptime看看當(dāng)前系統(tǒng)的整體情況， 當(dāng)前的系統(tǒng)時間和運行時間， 登陸的用戶數(shù)量，還有最近5、10和15分鐘的系統(tǒng)平均負載。

 

top則可以顯示較詳細的信息。head部分有CPU占用的詳細信息， 下面的列表也有記錄每個進程占用的CPU情況。

 

如果是多線程， 我們還可以通過top -H -p pid來查看進程的每個線程的CPU占用情況

 

我們找到哪個線程占用的比例多之后， 可以根據(jù)這個線程的線程名查看該線程是用來做什么處理的。大致了解下是什么樣的處理讓CPU比較高。

 

mpstat則可以查看系統(tǒng)每個核的運行狀態(tài)。

sar的功能比較全，這里不再做科普。

CPU用戶態(tài)的占用比較高，一般就是我們的程序編寫的效率太低，具體哪里低，我們可以通過perf工具或者Intel的vtunes來查看性能瓶頸。perf top的執(zhí)行結(jié)果如下圖所示， 我們拿到對應(yīng)的堆棧信息之后， 就可以針對性的消除CPU瓶頸了。(vtune的用法可以自行谷歌)。

 

鑒于上述工具檢查出來的情況， 如果CPU確實水位很高，則CPU基本就是性能瓶頸。如果不高則，需要進行下一步來判斷性能瓶頸。

3.3 IO占用過高的情況

IO定位的工具多種多樣， 一般查看IO問題我們可以使用iostat、pidstat和iotop工具。當(dāng)然我們也可以使用其他的工具，大家可以自己搜索相關(guān)的工具使用， 這里主要介紹常用的幾種工具。

pidstat

pidstat是sysstat工具的一個命令，用于監(jiān)控全部或指定進程的cpu、內(nèi)存、線程、設(shè)備IO等系統(tǒng)資源的占用情況。用戶可以通過指定統(tǒng)計的次數(shù)和時間來獲得所需的統(tǒng)計信息。

 

 

 

 

我們通過這個命令可以知道哪個進程占用的IO比較多。然后我們可以通過指定進程號的方式查看更詳細的信息。

 

這樣我們就可以知道是哪個進程中的哪個線程占用了較多的IO資源，然后我們可以通過對應(yīng)的TID，找到對應(yīng)的執(zhí)行代碼進行分析。

iostat

iostat是I/O statistics(輸入/輸出統(tǒng)計)的縮寫，它可以對系統(tǒng)的磁盤操作活動進行監(jiān)控,匯報磁盤活動統(tǒng)計情況。但是iostat僅對系統(tǒng)的整體情況進行統(tǒng)計，不能對某個進程進行深入分析，單獨的進程分析我們可以用iotop工具，使用方法和top類似。

 

 

 

 

1 表示每秒打印一次當(dāng)前磁盤的統(tǒng)計信息。我們需要注意的是后面幾個指標(biāo)。

 

 

avgrq-sz直接反應(yīng)了當(dāng)前io的種類，比如大塊數(shù)據(jù)讀取還是小數(shù)據(jù)量的讀取。

avgqu-sz反應(yīng)了當(dāng)前IO的繁忙情況， 如果隊列長度太長，說明IO現(xiàn)在很忙很多任務(wù)處理不過來，換句話說 I，IO成為了瓶頸。

await 也是一樣， 如果等待比較高，說明IO成了累贅。

svctm則和avgrq-sz一樣，反應(yīng)了IO操作的處理規(guī)模，如果是大塊數(shù)據(jù)讀寫， 這個時間就會拉長。

iotop

iotop 可以用于查看哪些進程執(zhí)行占用了的 I/O,使用方式和top類似,這里不再做過多描述。

 

 

 

 

3.4 其他情況

如果TOP占用不高， IO也不是瓶頸，則可能處在程序架構(gòu)上， 比如并發(fā)控制的不夠好有較多的線程在sleep狀態(tài)。這種情況可以通過pstack看看當(dāng)前所有線程的堆棧。

4. 優(yōu)化性能瓶頸

CPU瓶頸型

面對這種類型，一般我們需要通過perf配合對應(yīng)的代碼去進行優(yōu)化，核心思想就是減少計算的量。具體方法以下僅供參考：

1. 多采用SIMD來代替老式的計算指令或者C++的操作運算符?？梢砸M類似Intel的MKL庫來輔助計算。
2. 減少不必要的重復(fù)計算，減少for循環(huán)的次數(shù)。比如有些std庫的數(shù)據(jù)結(jié)構(gòu)都有find函數(shù)都帶有起始坐標(biāo)，善用起始坐標(biāo)避免從0坐標(biāo)重復(fù)查詢。
3. 如果是系統(tǒng)調(diào)用過多，比如分配內(nèi)存之類的，可以考慮預(yù)分配內(nèi)存的方式，或者直接使用tcmalloc等類似的內(nèi)存管理庫進行兜底，有條件的可以基于這類庫再開發(fā)適合自己的內(nèi)存管理體系

IO瓶頸型

IO瓶頸一般都是和磁盤相關(guān)的，網(wǎng)絡(luò)上，因為網(wǎng)卡升級，速度上去比較快，相比來說，限制的io基本都是磁盤上的io.下面也只說說磁盤的IO優(yōu)化方法。

1. 如果是讀類型的請求造成了IO瓶頸， 可以考慮上層多開cache。比如全局的query cache， session級別的session cache， 塊設(shè)備的block cache等，從上層去減少磁盤的io請求。
2. 如果是是小數(shù)據(jù)大并發(fā)的寫入類型的造成了IO瓶頸，我們可以考慮在內(nèi)存做一次cache，對這多次寫入先在內(nèi)存處理，然后通過時間或者大小閾值等策略控制，刷到磁盤上。
3. 如果是大數(shù)據(jù)的寫入，我們可以考慮做下平滑寫入，每次限制寫入的數(shù)量。
4. 如果是因為流量的關(guān)系，某一時間點出現(xiàn)峰值，之后回落，則可以考慮通過第三方來寫入。比如消息隊列，先寫到消息隊列i進行削峰，再平滑寫入系統(tǒng)。
5. 除此之外我們還可以換更好的硬件，比如磁盤陣列等。

內(nèi)存瓶頸型

內(nèi)存瓶頸一般比較難出現(xiàn)，內(nèi)存畢竟比較便宜，基本上都會滿足內(nèi)存的需求。如果真的因為虛擬內(nèi)存的問題造成了程序運行效率低下，我們一方面是考慮增加內(nèi)存，關(guān)閉虛擬內(nèi)存來解決，同時我們也應(yīng)該思考自己的程序模型，比如減少中間數(shù)據(jù)的存在， 多用寫時復(fù)制技術(shù)，多用用系統(tǒng)的no copy接口替換老的接口等。

5. 后續(xù)

如果實在沒有方法優(yōu)化了，我們真的就需要看看當(dāng)前的query是否真的合適我們的系統(tǒng)了。還是那句話，每套系統(tǒng)都有適合自己的業(yè)務(wù)，一般公司的系統(tǒng)體系里都會有多種數(shù)據(jù)庫引擎，針對我們的query，去尋找合適的引擎也是一種方法。