數據庫查詢性能優化指南
數據庫查詢性能優化一直是程序員繞不開的話題，當我們遇到業務刷新報表緩慢或者查詢獲取結果延遲太大，可以采用提問法來思考如何進行優化。
1. 什么樣的環境
硬件環境
query執行的速度和我們的硬件息息相關，當前用的什么樣的CPU，有多少核多少線程， 內存有多大都直接影響了運算速度， 磁盤是SSD還是HDD，網卡什么速率都直接影響了我們數據讀取的時延
軟件環境
軟件環境雖然不像硬件一樣，各種參數看的見摸得著，但仍然影響著我們的查詢性能。沒一套系統實際上都在特定的場景有著各自的優勢。我們的查詢系統是什么樣的架構，適合什么樣的query，在線還是離線， 計算多還是數據讀取多，這些在我們做優化的時候都應該了然于心。
下面我們根據這種思路來看看如何做性能優化
2. 什么樣的query
首先我們優化查詢的時候，需要看看query 究竟是哪種類型。寫入還是查詢(這里鑒于篇幅只談查詢)， CPU密集還是IO密集。如果我們的系統是適合OLTP低延時點查的場景， 想要在這種系統上做OLAP大規模分析很顯然就不太適合， OLTP一般專注于數據一致性較高的點查，而OLAP由于數據量龐大，一般都需要采用向量并發查詢。OLAP不專注于毫秒級的低延遲， 而OLTP不專注于上億級的數據統計。
3. 如何尋找性能瓶頸
3.1 vmstat查看系統情況
整體系統不知道當前的瓶頸在哪里時， 我們可以先用vmstat工具來簡單的看一下系統的大致情況。如下圖所示，2表示每個兩秒采集一次服務器狀態。
procs : 查看進程狀態
r : 運行隊列，即當前可運行(正在運行或者等待運行)的進程數量。目前CPU比較空閑，這個數量很小，當這個值超過了CPU數目，就會出現CPU瓶頸了。
b : 阻塞的進程,即處在不可中斷sleep狀態下的進程數量。
memory : 查看內存狀態
swpd : 已使用的虛擬內存大小，如果大于0，表示機器開始使用虛擬內存了，虛擬內存運行會很慢。這里數值為0表示我們關閉了虛擬內存功能。
free : 空閑的物理內存的大小。
buff : 內存做為系統buffers的大小。
cache : 內存做為系統cache的大小。
swap : 磁盤和內存做數據交換的狀態
nesi : 每秒從磁盤讀入虛擬內存的大小，如果這個值大于0，表示物理內存不夠。
so : 每秒虛擬內存寫入磁盤的大小。
io：磁盤的io信息
bi : 每秒從塊設備接收的塊數量。
bo : 每秒發送給塊設備的塊數量。
如果這兩個值較大，表示IO比較頻繁，可以考慮IO優化。
system : 系統狀態信息
in : 每秒CPU的中斷次數(包括時鐘中斷)
cs : 每秒上下文切換次數，我們調用系統函數、線程的切換，就需要上下文切換，這個值要太大就可以考慮 減少系統的上下文切換，比如協程替代多線程等方式。
CPU : CPU信息
us : 包括用戶時間和nice時間，跑非內核的代碼(或者用戶代碼)的時間。
sy : 系統占用時間，跑內核代碼(比如系統調用)占用的時間。
id : 花費在idle上的 CPU時間。
wa : 等待IO CPU時間。如果這個值太大，表示IO系統瓶頸在IO上。
如果CPU占用高表示系統在CPU上， 如果系統的swap比較頻繁，很可能是系統內存泄露或者內存不夠用，需要擴展內存， 如果是IO等待較多則系統瓶頸出現在IO上，如果上下文切換，或者系統調用占比太大，則我們需要思考下我們程序的設計，減少系統調用或者上下文切換。
3.2 CPU占用過高
我們可以通過uptime、top、mpstat或者sar等一些工具來查看當前CPU占用過高的情況.
我們可以通過uptime看看當前系統的整體情況， 當前的系統時間和運行時間， 登陸的用戶數量，還有最近5、10和15分鐘的系統平均負載。
top則可以顯示較詳細的信息。head部分有CPU占用的詳細信息， 下面的列表也有記錄每個進程占用的CPU情況。
如果是多線程， 我們還可以通過top -H -p pid來查看進程的每個線程的CPU占用情況
我們找到哪個線程占用的比例多之后， 可以根據這個線程的線程名查看該線程是用來做什么處理的。大致了解下是什么樣的處理讓CPU比較高。
mpstat則可以查看系統每個核的運行狀態。
sar的功能比較全，這里不再做科普。
CPU用戶態的占用比較高，一般就是我們的程序編寫的效率太低，具體哪里低，我們可以通過perf工具或者Intel的vtunes來查看性能瓶頸。perf top的執行結果如下圖所示， 我們拿到對應的堆棧信息之后， 就可以針對性的消除CPU瓶頸了。(vtune的用法可以自行谷歌)。
鑒于上述工具檢查出來的情況， 如果CPU確實水位很高，則CPU基本就是性能瓶頸。如果不高則，需要進行下一步來判斷性能瓶頸。
3.3 IO占用過高的情況
IO定位的工具多種多樣， 一般查看IO問題我們可以使用iostat、pidstat和iotop工具。當然我們也可以使用其他的工具，大家可以自己搜索相關的工具使用， 這里主要介紹常用的幾種工具。
pidstat
pidstat是sysstat工具的一個命令，用于監控全部或指定進程的cpu、內存、線程、設備IO等系統資源的占用情況。用戶可以通過指定統計的次數和時間來獲得所需的統計信息。
我們通過這個命令可以知道哪個進程占用的IO比較多。然后我們可以通過指定進程號的方式查看更詳細的信息。
這樣我們就可以知道是哪個進程中的哪個線程占用了較多的IO資源，然后我們可以通過對應的TID，找到對應的執行代碼進行分析。
iostat
iostat是I/O statistics(輸入/輸出統計)的縮寫，它可以對系統的磁盤操作活動進行監控,匯報磁盤活動統計情況。但是iostat僅對系統的整體情況進行統計，不能對某個進程進行深入分析，單獨的進程分析我們可以用iotop工具，使用方法和top類似。
1 表示每秒打印一次當前磁盤的統計信息。我們需要注意的是后面幾個指標。
avgrq-sz直接反應了當前io的種類，比如大塊數據讀取還是小數據量的讀取。
avgqu-sz反應了當前IO的繁忙情況， 如果隊列長度太長，說明IO現在很忙很多任務處理不過來，換句話說 I，IO成為了瓶頸。
await 也是一樣， 如果等待比較高，說明IO成了累贅。
svctm則和avgrq-sz一樣，反應了IO操作的處理規模，如果是大塊數據讀寫， 這個時間就會拉長。
iotop
iotop 可以用于查看哪些進程執行占用了的 I/O,使用方式和top類似,這里不再做過多描述。
3.4 其他情況
如果TOP占用不高， IO也不是瓶頸，則可能處在程序架構上， 比如并發控制的不夠好有較多的線程在sleep狀態。這種情況可以通過pstack看看當前所有線程的堆棧。
4. 優化性能瓶頸
CPU瓶頸型
面對這種類型，一般我們需要通過perf配合對應的代碼去進行優化，核心思想就是減少計算的量。具體方法以下僅供參考：
- 多采用SIMD來代替老式的計算指令或者C++的操作運算符。可以引進類似Intel的MKL庫來輔助計算。
- 減少不必要的重復計算，減少for循環的次數。比如有些std庫的數據結構都有find函數都帶有起始坐標，善用起始坐標避免從0坐標重復查詢。
- 如果是系統調用過多，比如分配內存之類的，可以考慮預分配內存的方式，或者直接使用tcmalloc等類似的內存管理庫進行兜底，有條件的可以基于這類庫再開發適合自己的內存管理體系
IO瓶頸型
IO瓶頸一般都是和磁盤相關的，網絡上，因為網卡升級，速度上去比較快，相比來說，限制的io基本都是磁盤上的io.下面也只說說磁盤的IO優化方法。
- 如果是讀類型的請求造成了IO瓶頸， 可以考慮上層多開cache。比如全局的query cache， session級別的session cache， 塊設備的block cache等，從上層去減少磁盤的io請求。
- 如果是是小數據大并發的寫入類型的造成了IO瓶頸，我們可以考慮在內存做一次cache，對這多次寫入先在內存處理，然后通過時間或者大小閾值等策略控制，刷到磁盤上。
- 如果是大數據的寫入，我們可以考慮做下平滑寫入，每次限制寫入的數量。
- 如果是因為流量的關系，某一時間點出現峰值，之后回落，則可以考慮通過第三方來寫入。比如消息隊列，先寫到消息隊列i進行削峰，再平滑寫入系統。
- 除此之外我們還可以換更好的硬件，比如磁盤陣列等。
內存瓶頸型
內存瓶頸一般比較難出現，內存畢竟比較便宜，基本上都會滿足內存的需求。如果真的因為虛擬內存的問題造成了程序運行效率低下，我們一方面是考慮增加內存，關閉虛擬內存來解決，同時我們也應該思考自己的程序模型，比如減少中間數據的存在， 多用寫時復制技術，多用用系統的no copy接口替換老的接口等。
5. 后續
如果實在沒有方法優化了，我們真的就需要看看當前的query是否真的合適我們的系統了。還是那句話，每套系統都有適合自己的業務，一般公司的系統體系里都會有多種數據庫引擎，針對我們的query，去尋找合適的引擎也是一種方法。
