一、MySQL數(shù)據(jù)庫的優(yōu)化目標(biāo)、基本原則：

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

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

（1）減少 I/O 次數(shù)：

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

（2）降低 CPU 計算：

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

2.基本原則：

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

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

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

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

3）.盡可能對每一條運行在數(shù)據(jù)庫中的SQL進(jìn)行 explain

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

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

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

show [session | global] status;

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

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

Com_select/Com_insert/Com_update/Com_delete

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

（1）通過show processlist命令實時查看當(dāng)前SQL的執(zhí)行情況；
（2）通過慢查詢?nèi)罩径ㄎ怀霈F(xiàn)的問題

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

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

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

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

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

最新評論