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

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

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

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

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

（2）降低 CPU 計(jì)算：

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

2.基本原則：

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

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

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

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

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

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

二、定位分析SQL語(yǔ)句的性能瓶頸：

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

show [session | global] status;

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

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

Com_select/Com_insert/Com_update/Com_delete

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

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

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

4、通過(guò)show profile 分析SQL的具體耗時(shí)瓶頸

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

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

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

最新評(píng)論