一、MySQL數據庫的優(yōu)化目標、基本原則：

1、優(yōu)化目標：

MySQL數據庫是常見的兩個瓶頸是CPU和I/O的瓶頸，無論是索引優(yōu)化、還是表結構優(yōu)化，參數優(yōu)化，最后都可以歸納到這這兩個分類中：

（1）減少 I/O 次數：

I/O是數據庫最容易瓶頸的地方，大部分數據庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當然，也是效果最明顯的優(yōu)化手段。

（2）降低 CPU 計算：

除了 IO 瓶頸之外，SQL優(yōu)化中需要考慮的就是 CPU 運算量的優(yōu)化了。order by，group by，distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理內存中的數據比較運算）。當我們的 IO 優(yōu)化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優(yōu)化的重要目標。

2.基本原則：

1）.優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL

對于破壞性來說，高并發(fā)的 SQL 總是會比低頻率的 SQL 來得大，因為高并發(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們任何喘息的機會就會將系統(tǒng)壓跨。而對于一些雖然需要消耗大量 IO 而且響應很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個系統(tǒng)響應慢一點，但至少可能撐一會兒，讓我們有緩沖的機會

2）.從全局出發(fā)優(yōu)化，而不是片面調整

SQL 優(yōu)化不能是單獨針對某一個進行，而應充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調整索引優(yōu)化 SQL 的執(zhí)行計劃的時候

3）.盡可能對每一條運行在數據庫中的SQL進行 explain

優(yōu)化 SQL，知道 SQL 的執(zhí)行計劃判斷是否有優(yōu)化余地，判斷是否存在執(zhí)行計劃問題。
進行大量的 explain 操作收集執(zhí)行計劃，并判斷是否需要進行優(yōu)化

二、定位分析SQL語句的性能瓶頸：

1、通過show status 命令了解各種SQL的執(zhí)行效率：

show [session | global] status;

可以根據需要加上參數來顯示session級（當前連接，默認）和global級（自數據庫上次啟動至今）的統(tǒng)計結果。

show status like ‘Com_%’; —顯示當前連接所有統(tǒng)計參數的值。
Com_xxx表示每個xxx語句執(zhí)行的次數，通常需要注意的是下面幾個參數：

Com_select/Com_insert/Com_update/Com_delete

2、定位執(zhí)行效率較低的SQL語句

（1）通過show processlist命令實時查看當前SQL的執(zhí)行情況；
（2）通過慢查詢日志定位出現(xiàn)的問題

3、通過explain分析慢SQL的執(zhí)行計劃

4、通過show profile 分析SQL的具體耗時瓶頸

通過secect @have_profiling命令能夠看到當前MySQL是否支持profile；
通過show profiles我們能夠更清楚了解SQL執(zhí)行的過程；
通過show profile for query我們能看到執(zhí)行過程中線程的每個狀態(tài)和消耗的時間。

三、數據庫的優(yōu)化方法：

1、設計符合范式的數據庫
2、選擇合適的存儲引擎
3、SQL表結構、字段優(yōu)化
4、SQL語句優(yōu)化與索引優(yōu)化
5、讀寫分離：主數據庫負責寫操作，從數據庫負責讀操作
6、分庫分表：垂直切分與水平切分
7、分區(qū)：將表的數據按照特定的規(guī)則放在不同的分區(qū)，提高磁盤的IO效率，提高數據庫的性能
8.數據庫參數優(yōu)化：IO參數、CPU參數
9.數據庫集群
10.硬件配置升級

最新評論