《Mysql實例詳解MySQL性能優(yōu)化（一）》要點：
本文介紹了Mysql實例詳解MySQL性能優(yōu)化（一），希望對您有用。如果有疑問，可以聯(lián)系我們。

一、MySQL的主要適用場景
1、Web網(wǎng)站系統(tǒng)MYSQL應(yīng)用

2、日志記錄系統(tǒng)MYSQL應(yīng)用

3、數(shù)據(jù)倉庫系統(tǒng)MYSQL應(yīng)用

4、嵌入式系統(tǒng)MYSQL應(yīng)用

二、MySQL架構(gòu)圖：MYSQL應(yīng)用

?MYSQL應(yīng)用

三、MySQL存儲引擎概述MYSQL應(yīng)用

1)MyISAM存儲引擎MYSQL應(yīng)用

MyISAM存儲引擎的表在數(shù)據(jù)庫中,每一個表都被存放為三個以表名命名的物理文件.首先肯定會有任何存儲引擎都不可缺少的存放表結(jié)構(gòu)定義信息的.frm文件,另外還有.MYD和.MYI文件,分別存放了表的數(shù)據(jù)(.MYD)和索引數(shù)據(jù)(.MYI).每個表都有且僅有這樣三個文件做為MyISAM存儲類型的表的存儲,也就是說不管這個表有多少個索引,都是存放在同一個.MYI文件中.MYSQL應(yīng)用

MyISAM支持以下三種類型的索引：MYSQL應(yīng)用

1、B-Tree索引MYSQL應(yīng)用

B-Tree索引,顧名思義,就是所有的索引節(jié)點都依照balancetree的數(shù)據(jù)結(jié)構(gòu)來存儲,所有的索引數(shù)據(jù)節(jié)點都在葉節(jié)點.MYSQL應(yīng)用

2、R-Tree索引MYSQL應(yīng)用

R-Tree索引的存儲方式和b-tree索引有一些區(qū)別,主要設(shè)計用于為存儲空間和多維數(shù)據(jù)的字段做索引,所以目前的MySQL版本來說,也僅支持geometry類型的字段作索引.MYSQL應(yīng)用

3、Full-text索引MYSQL應(yīng)用

Full-text索引就是我們長說的全文索引,他的存儲結(jié)構(gòu)也是b-tree.主要是為了辦理在我們需要用like查詢的低效問題.MYSQL應(yīng)用

2)Innodb 存儲引擎MYSQL應(yīng)用

1、支持事務(wù)安裝MYSQL應(yīng)用

2、數(shù)據(jù)多版本讀取MYSQL應(yīng)用

3、鎖定機制的改進MYSQL應(yīng)用

4、實現(xiàn)外鍵MYSQL應(yīng)用

3)NDBCluster存儲引擎MYSQL應(yīng)用

NDB存儲引擎也叫NDBCluster存儲引擎,主要用于MySQLCluster分布式集群環(huán)境,Cluster是MySQL從5.0版本才開始提供的新功能.MYSQL應(yīng)用

4)Merge存儲引擎MYSQL應(yīng)用

MERGE存儲引擎,在MySQL用戶手冊中也提到了,也被大家認識為MRG_MyISAM引擎.Why?因為MERGE存儲引擎可以簡單的理解為其功能就是實現(xiàn)了對結(jié)構(gòu)相同的MyISAM表,通過一些特殊的包裝對外提供一個單一的拜訪入口,以達到減小應(yīng)用的復(fù)雜度的目的.要創(chuàng)建MERGE表,不僅僅基表的結(jié)構(gòu)要完全一致,包括字段的順序,基表的索引也必須完全一致.MYSQL應(yīng)用

5)Memory存儲引擎MYSQL應(yīng)用

Memory存儲引擎,通過名字就很容易讓人知道,他是一個將數(shù)據(jù)存儲在內(nèi)存中的存儲引擎.Memory存儲引擎不會將任何數(shù)據(jù)存放到磁盤上,僅僅存放了一個表結(jié)構(gòu)相關(guān)信息的.frm文件在磁盤上面.所以一旦MySQLCrash或者主機Crash之后,Memory的表就只剩下一個結(jié)構(gòu)了.Memory表支持索引,并且同時支持Hash和B－Tree兩種格式的索引.由于是存放在內(nèi)存中,所以Memory都是依照定長的空間來存儲數(shù)據(jù)的,而且不支持BLOB和TEXT類型的字段.Memory存儲引擎實現(xiàn)頁級鎖定.MYSQL應(yīng)用

6)BDB存儲引擎MYSQL應(yīng)用

BDB存儲引擎全稱為BerkeleyDB存儲引擎,和Innodb一樣,也不是MySQL自己開發(fā)實現(xiàn)的一個存儲引擎,而是由SleepycatSoftware所提供,當然,也是開源存儲引擎,同樣支持事務(wù)平安.MYSQL應(yīng)用

7)FEDERATED存儲引擎MYSQL應(yīng)用

FEDERATED存儲引擎所實現(xiàn)的功能,和Oracle的DBLINK基本相似,主要用來提供對遠程MySQL服務(wù)器上面的數(shù)據(jù)的拜訪接口.如果我們使用源碼編譯來安裝MySQL,那么必須手工指定啟用FEDERATED存儲引擎才行,因為MySQL默認是不起用該存儲引擎的.MYSQL應(yīng)用

8)ARCHIVE存儲引擎MYSQL應(yīng)用

ARCHIVE存儲引擎主要用于通過較小的存儲空間來存放過期的很少拜訪的歷史數(shù)據(jù).ARCHIVE表不支持索引,通過一個.frm的結(jié)構(gòu)定義文件,一個.ARZ的數(shù)據(jù)壓縮文件還有一個.ARM的meta信息文件.由于其所存放的數(shù)據(jù)的特殊性,ARCHIVE表不支持刪除,修改操MYSQL應(yīng)用

作,僅支持插入和查詢操作.鎖定機制為行級鎖定.MYSQL應(yīng)用

9)BLACKHOLE存儲引擎MYSQL應(yīng)用

BLACKHOLE存儲引擎是一個非常有意思的存儲引擎,功能恰如其名,就是一個“黑洞”.就像我們unix系統(tǒng)下面的“/dev/null”設(shè)備一樣,不管我們寫入任何信息,都是有去無回.MYSQL應(yīng)用

10)CSV存儲引擎MYSQL應(yīng)用

CSV存儲引擎實際上操作的就是一個標準的CSV文件,他不支持索引.起主要用途就是大家有些時候可能會需要通過數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)出成一份報表文件,而CSV文件是很多軟件都支持的一種較為標準的格式,所以我們可以通過先在數(shù)據(jù)庫中建立一張CVS表,然后將生成的報表信息插入到該表,即可得到一份CSV報表文件了.MYSQL應(yīng)用

四、影響MySQLServer性能的相關(guān)因素MYSQL應(yīng)用

1商業(yè)需求對性能的影響MYSQL應(yīng)用

典型需求：一個論壇帖子總量的統(tǒng)計,要求：實時更新.MYSQL應(yīng)用

2系統(tǒng)架構(gòu)及實現(xiàn)對性能的影響MYSQL應(yīng)用

以下幾類數(shù)據(jù)都是不適合在數(shù)據(jù)庫中存放的：MYSQL應(yīng)用

二進制多媒體數(shù)據(jù)MYSQL應(yīng)用

流水隊列數(shù)據(jù)MYSQL應(yīng)用

超大文本數(shù)據(jù)MYSQL應(yīng)用

通過Cache技術(shù)來提高系統(tǒng)性能：MYSQL應(yīng)用

系統(tǒng)各種配置及規(guī)則數(shù)據(jù)；MYSQL應(yīng)用

活躍用戶的基本信息數(shù)據(jù)；MYSQL應(yīng)用

活躍用戶的個性化定制信息數(shù)據(jù)；MYSQL應(yīng)用

準實時的統(tǒng)計信息數(shù)據(jù)；MYSQL應(yīng)用

其他一些拜訪頻繁但變更較少的數(shù)據(jù)；MYSQL應(yīng)用

3 Query語句對系統(tǒng)性能的影響MYSQL應(yīng)用

需求：取出某個group(假設(shè)id為1)下的用戶編號(id),用戶昵稱(nick_name),并依照加入組的時間(user_group.gmt_create)來進行倒序排列,取出前20個.MYSQL應(yīng)用

辦理方案一：MYSQL應(yīng)用

辦理方案二：
MYSQL應(yīng)用

SELECT user.id,user.nick_name FROM(
SELECT user_id
FROM user_group
WHERE user_group.group_id=1
ORDER BY gmt_create desc
limit 100,20)t,user
WHERE t.user_id=user.id;

通過比較兩個辦理方案的執(zhí)行計劃,我們可以看到第一中辦理方案中需要和user表參與Join的記錄數(shù)MySQL通過統(tǒng)計數(shù)據(jù)估算出來是31156,也就是通過user_group表返回的所有滿足group_id=1的記錄數(shù)(系統(tǒng)中的實際數(shù)據(jù)是20000).而第二種辦理方案的執(zhí)行計劃中,user表參與Join的數(shù)據(jù)就只有20條,兩者相差很大,我們認為第二中辦理方案應(yīng)該明顯優(yōu)于第一種辦理方案.MYSQL應(yīng)用

4 Schema設(shè)計對系統(tǒng)的性能影響MYSQL應(yīng)用

盡量減少對數(shù)據(jù)庫訪問的哀求.MYSQL應(yīng)用

盡量減少無用數(shù)據(jù)的查詢哀求.MYSQL應(yīng)用

5硬件環(huán)境對系統(tǒng)性能的影響MYSQL應(yīng)用

1、典型OLTP應(yīng)用系統(tǒng)MYSQL應(yīng)用

對于各種數(shù)據(jù)庫系統(tǒng)環(huán)境中大家最常見的OLTP系統(tǒng),其特點是并發(fā)量大,整體數(shù)據(jù)量比較多,但每次拜訪的數(shù)據(jù)比較少,且拜訪的數(shù)據(jù)比較離散,活躍數(shù)據(jù)占總體數(shù)據(jù)的比例不是太大.對于這類系統(tǒng)的數(shù)據(jù)庫實際上是最難維護,最難以優(yōu)化的,對主機整體性能要求也是最高的.因為不僅拜訪量很高,數(shù)據(jù)量也不小.MYSQL應(yīng)用

針對上面的這些特點和分析,我們可以對OLTP的得出一個大致的方向.MYSQL應(yīng)用

雖然系統(tǒng)總體數(shù)據(jù)量較大,但是系統(tǒng)活躍數(shù)據(jù)在數(shù)據(jù)總量中所占的比例不大,那么我們可以通過擴大內(nèi)存容量來盡可能多的將活躍數(shù)據(jù)cache到內(nèi)存中；MYSQL應(yīng)用

雖然IO拜訪非常頻繁,但是每次拜訪的數(shù)據(jù)量較少且很離散,那么我們對磁盤存儲的要求是IOPS表現(xiàn)要很好,吞吐量是次要因素；MYSQL應(yīng)用

并發(fā)量很高,CPU每秒所要處理的哀求自然也就很多,所以CPU處理能力需要比較強勁；MYSQL應(yīng)用

雖然與客戶端的每次交互的數(shù)據(jù)量并不是特別大,但是網(wǎng)絡(luò)交互非常頻繁,所以主機與客戶端交互的網(wǎng)絡(luò)設(shè)備對流量能力也要求不能太弱.MYSQL應(yīng)用

2、典型OLAP應(yīng)用系統(tǒng)MYSQL應(yīng)用

用于數(shù)據(jù)分析的OLAP系統(tǒng)的主要特點就是數(shù)據(jù)量非常大,并發(fā)拜訪不多,但每次拜訪所需要檢索的數(shù)據(jù)量都比較多,而且數(shù)據(jù)拜訪相對較為集中,沒有太明顯的活躍數(shù)據(jù)概念.MYSQL應(yīng)用

基于OLAP系統(tǒng)的各種特點和相應(yīng)的分析,針對OLAP系統(tǒng)硬件優(yōu)化的大致策略如下：MYSQL應(yīng)用

數(shù)據(jù)量非常大,所以磁盤存儲系統(tǒng)的單位容量需要盡量大一些；MYSQL應(yīng)用

單次拜訪數(shù)據(jù)量較大,而且拜訪數(shù)據(jù)比較集中,那么對IO系統(tǒng)的性能要求是需要有盡可能大的每秒IO吞吐量,所以應(yīng)該選用每秒吞吐量盡可能大的磁盤；MYSQL應(yīng)用

雖然IO性能要求也比較高,但是并發(fā)哀求較少,所以CPU處理能力較難成為性能瓶頸,所以CPU處理能力沒有太苛刻的要求；MYSQL應(yīng)用

雖然每次哀求的訪問量很大,但是執(zhí)行過程中的數(shù)據(jù)大都不會返回給客戶端,最終返回給客戶端的數(shù)據(jù)量都較小,所以和客戶端交互的網(wǎng)絡(luò)設(shè)備要求并不是太高；MYSQL應(yīng)用

此外,由于OLAP系統(tǒng)由于其每次運算過程較長,可以很好的并行化,所以一般的OLAP系統(tǒng)都是由多臺主機構(gòu)成的一個集群,而集群中主機與主機之間的數(shù)據(jù)交互量一般來說都是非常大的,所以在集群中主機之間的網(wǎng)絡(luò)設(shè)備要求很高.MYSQL應(yīng)用

3、除了以上兩個典型應(yīng)用之外,還有一類比較特殊的應(yīng)用系統(tǒng),他們的數(shù)據(jù)量不是特別大,但是訪問哀求及其頻繁,而且大部分是讀哀求.可能每秒需要提供上萬甚至幾萬次哀求,每次哀求都非常簡單,可能大部分都只有一條或者幾條比較小的記錄返回,就比如基于數(shù)據(jù)庫的DNS服務(wù)就是這樣類型的服務(wù).MYSQL應(yīng)用

雖然數(shù)據(jù)量小,但是拜訪極其頻繁,所以可以通過較大的內(nèi)存來cache住大部分的數(shù)據(jù),這能夠保證非常高的命中率,磁盤IO量比較小,所以磁盤也不需要特別高性能的；MYSQL應(yīng)用

并發(fā)哀求非常頻繁,比需要較強的CPU處理能力才能處理；MYSQL應(yīng)用

雖然應(yīng)用與數(shù)據(jù)庫交互量非常大,但是每次交互數(shù)據(jù)較少,總體流量雖然也會較大,但是一般來說普通的千兆網(wǎng)卡已經(jīng)足夠了.MYSQL應(yīng)用

五、MySQL 鎖定機制簡介MYSQL應(yīng)用

行級鎖定(row-level)MYSQL應(yīng)用

表級鎖定(table-level)MYSQL應(yīng)用

頁級鎖定(page-level)MYSQL應(yīng)用

在MySQL數(shù)據(jù)庫中,使用表級鎖定的主要是MyISAM,Memory,CSV等一些非事務(wù)性存儲引擎,而使用行級鎖定的主要是Innodb存儲引擎和NDBCluster存儲引擎,頁級鎖定主要是BerkeleyDB存儲引擎的鎖定方式.MYSQL應(yīng)用

六、MySQL Query的優(yōu)化MYSQL應(yīng)用

Query語句的優(yōu)化思路和原則主要提現(xiàn)在以下幾個方面：MYSQL應(yīng)用

1. 優(yōu)化更需要優(yōu)化的Query；MYSQL應(yīng)用

2. 定位優(yōu)化對象的性能瓶頸；MYSQL應(yīng)用

3. 明確的優(yōu)化目標；MYSQL應(yīng)用

4. 從Explain入手；MYSQL應(yīng)用

5. 多使用profileMYSQL應(yīng)用

6. 永遠用小結(jié)果集驅(qū)動大的結(jié)果集；MYSQL應(yīng)用

7. 盡可能在索引中完成排序；MYSQL應(yīng)用

8. 只取出自己需要的Columns；MYSQL應(yīng)用

9. 僅僅使用最有效的過濾條件；MYSQL應(yīng)用

10.盡可能避免復(fù)雜的Join和子查詢；MYSQL應(yīng)用

合理設(shè)計并利用索引MYSQL應(yīng)用

1)B-Tree索引MYSQL應(yīng)用

一般來說,MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的結(jié)構(gòu)來存儲的,也就是所有實際需要的數(shù)據(jù)都存放于Tree的LeafNode,而且到任何一個LeafNode的最短路徑的長度都是完全相同的,所以我們大家都稱之為B-Tree索引當然,可能各種數(shù)據(jù)庫(或MySQL的各種存儲引擎)在存放自己的B-Tree索引的時候會對存儲結(jié)構(gòu)稍作改造.如Innodb存儲引擎的B-Tree索引實際使用的存儲結(jié)構(gòu)實際上是B+Tree,也就是在B-Tree數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上做了很小的改造,在每一個LeafNode上面出了存放索引鍵的相關(guān)信息之外,還存儲了指向與該LeafNode相鄰的后一個LeafNode的指針信息,這主要是為了加快檢索多個相鄰LeafNode的效率考慮.MYSQL應(yīng)用

2)Hash索引MYSQL應(yīng)用

Hash索引在MySQL中使用的并不是很多,目前主要是Memory存儲引擎使用,而且在Memory存儲引擎中將Hash索引作為默認的索引類型.所謂Hash索引,實際上就是通過一定的Hash算法,將需要索引的鍵值進行Hash運算,然后將得到的Hash值存入一個Hash表中.然后每次需要檢索的時候,都會將檢索條件進行相同算法的Hash運算,然后再和Hash表中的Hash值進行比較并得出相應(yīng)的信息.MYSQL應(yīng)用

Hash索引僅僅只能滿足“=”,“IN”和“<=>”查詢,不能使用范圍查詢；MYSQL應(yīng)用

Hash索引無法被利用來避免數(shù)據(jù)的排序操作；MYSQL應(yīng)用

Hash索引不能利用部分索引鍵查詢；MYSQL應(yīng)用

Hash索引在任何時候都不能避免表掃面；MYSQL應(yīng)用

Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高；MYSQL應(yīng)用

3)Full-text索引MYSQL應(yīng)用

Full-text索引也就是我們常說的全文索引,目前在MySQL中僅有MyISAM存儲引擎支持,而且也并不是所有的數(shù)據(jù)類型都支持全文索引.目前來說,僅有CHAR,VARCHAR和TEXT這三種數(shù)據(jù)類型的列可以建Full-text索引.MYSQL應(yīng)用

索引能夠極大的提高數(shù)據(jù)檢索效率,也能夠改善排序分組操作的性能,但是我們不能忽略的一個問題就是索引是完全獨立于基礎(chǔ)數(shù)據(jù)之外的一部分數(shù)據(jù),更新數(shù)據(jù)會帶來的IO量和調(diào)整索引所致的計算量的資源消耗.MYSQL應(yīng)用

是否需要創(chuàng)建索引,幾點原則：較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引；唯一性太差的字段不適合單獨創(chuàng)建索引,即使頻繁作為查詢條件；更新非常頻繁的字段不適合創(chuàng)建索引；MYSQL應(yīng)用

不會出現(xiàn)在WHERE子句中的字段不該創(chuàng)建索引；MYSQL應(yīng)用

Join語句的優(yōu)化MYSQL應(yīng)用

盡可能減少Join語句中的NestedLoop的循環(huán)總次數(shù)；“永遠用小結(jié)果集驅(qū)動大的結(jié)果集”.MYSQL應(yīng)用

優(yōu)先優(yōu)化NestedLoop的內(nèi)層循環(huán)；MYSQL應(yīng)用

保證Join語句中被驅(qū)動表上Join條件字段已經(jīng)被索引；MYSQL應(yīng)用

當無法保證被驅(qū)動表的Join條件字段被索引且內(nèi)存資源充足的前提下,不要太吝惜JoinBuffer的設(shè)置；MYSQL應(yīng)用

ORDER BY,GROUP BY和DISTINCT優(yōu)化MYSQL應(yīng)用

1)ORDER BY的實現(xiàn)與優(yōu)化MYSQL應(yīng)用

優(yōu)化Query語句中的ORDER BY的時候,盡可能利用已有的索引來避免實際的排序計算,可以很大幅度的提升ORDER BY操作的性能.MYSQL應(yīng)用

優(yōu)化排序：MYSQL應(yīng)用

1.加大max_length_for_sort_data參數(shù)的設(shè)置；MYSQL應(yīng)用

2.去掉不必要的返回字段；MYSQL應(yīng)用

3.增大sort_buffer_size參數(shù)設(shè)置；MYSQL應(yīng)用

2)GROUP BY的實現(xiàn)與優(yōu)化MYSQL應(yīng)用

由于GROUP BY實際上也同樣需要進行排序操作,而且與ORDER BY相比,GROUP BY主要只是多了排序之后的分組操作.當然,如果在分組的時候還使用了其他的一些聚合函數(shù),那么還需要一些聚合函數(shù)的計算.所以,在GROUP BY的實現(xiàn)過程中,與ORDER BY一樣也可以利用到索引.MYSQL應(yīng)用

3)DISTINCT的實現(xiàn)與優(yōu)化MYSQL應(yīng)用

DISTINCT實際上和GROUP BY的操作非常相似,只不過是在GROUP BY之后的每組中只取出一條記錄罷了.所以,DISTINCT的實現(xiàn)和GROUP BY的實現(xiàn)也基本差不多,沒有太大的區(qū)別.同樣可以通過松散索引掃描或者是緊湊索引掃描來實現(xiàn),當然,在無法僅僅使用索引即能完成DISTINCT的時候,MySQL只能通過臨時表來完成.但是,和GROUP BY有一點差別的是,DISTINCT并不需要進行排序.也就是說,在僅僅只是DISTINCT操作的Query如果無法僅僅利用索引完成操作的時候,MySQL會利用臨時表來做一次數(shù)據(jù)的“緩存”,但是不會對臨時表中的數(shù)據(jù)進行filesort操作.MYSQL應(yīng)用

下篇地址：/article/70530.htmMYSQL應(yīng)用

以上就是本文的全部內(nèi)容,希望對大家的學(xué)習(xí)有所贊助.MYSQL應(yīng)用

《Mysql實例詳解MySQL性能優(yōu)化（一）》是否對您有啟發(fā)，歡迎查看更多與《Mysql實例詳解MySQL性能優(yōu)化（一）》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/8087.html