《Mysql實例詳解MySQL性能優化（一）》要點：
本文介紹了Mysql實例詳解MySQL性能優化（一），希望對您有用。如果有疑問，可以聯系我們。
一、MySQL的主要適用場景
1、Web網站系統MYSQL應用
2、日志記錄系統MYSQL應用
3、數據倉庫系統MYSQL應用
4、嵌入式系統MYSQL應用
二、MySQL架構圖：MYSQL應用
?MYSQL應用
三、MySQL存儲引擎概述MYSQL應用
1)MyISAM存儲引擎MYSQL應用
MyISAM存儲引擎的表在數據庫中,每一個表都被存放為三個以表名命名的物理文件.首先肯定會有任何存儲引擎都不可缺少的存放表結構定義信息的.frm文件,另外還有.MYD和.MYI文件,分別存放了表的數據(.MYD)和索引數據(.MYI).每個表都有且僅有這樣三個文件做為MyISAM存儲類型的表的存儲,也就是說不管這個表有多少個索引,都是存放在同一個.MYI文件中.MYSQL應用
MyISAM支持以下三種類型的索引：MYSQL應用
1、B-Tree索引MYSQL應用
B-Tree索引,顧名思義,就是所有的索引節點都依照balancetree的數據結構來存儲,所有的索引數據節點都在葉節點.MYSQL應用
2、R-Tree索引MYSQL應用
R-Tree索引的存儲方式和b-tree索引有一些區別,主要設計用于為存儲空間和多維數據的字段做索引,所以目前的MySQL版本來說,也僅支持geometry類型的字段作索引.MYSQL應用
3、Full-text索引MYSQL應用
Full-text索引就是我們長說的全文索引,他的存儲結構也是b-tree.主要是為了辦理在我們需要用like查詢的低效問題.MYSQL應用
2)Innodb 存儲引擎MYSQL應用
1、支持事務安裝MYSQL應用
2、數據多版本讀取MYSQL應用
3、鎖定機制的改進MYSQL應用
4、實現外鍵MYSQL應用
3)NDBCluster存儲引擎MYSQL應用
NDB存儲引擎也叫NDBCluster存儲引擎,主要用于MySQLCluster分布式集群環境,Cluster是MySQL從5.0版本才開始提供的新功能.MYSQL應用
4)Merge存儲引擎MYSQL應用
MERGE存儲引擎,在MySQL用戶手冊中也提到了,也被大家認識為MRG_MyISAM引擎.Why?因為MERGE存儲引擎可以簡單的理解為其功能就是實現了對結構相同的MyISAM表,通過一些特殊的包裝對外提供一個單一的拜訪入口,以達到減小應用的復雜度的目的.要創建MERGE表,不僅僅基表的結構要完全一致,包括字段的順序,基表的索引也必須完全一致.MYSQL應用
5)Memory存儲引擎MYSQL應用
Memory存儲引擎,通過名字就很容易讓人知道,他是一個將數據存儲在內存中的存儲引擎.Memory存儲引擎不會將任何數據存放到磁盤上,僅僅存放了一個表結構相關信息的.frm文件在磁盤上面.所以一旦MySQLCrash或者主機Crash之后,Memory的表就只剩下一個結構了.Memory表支持索引,并且同時支持Hash和B－Tree兩種格式的索引.由于是存放在內存中,所以Memory都是依照定長的空間來存儲數據的,而且不支持BLOB和TEXT類型的字段.Memory存儲引擎實現頁級鎖定.MYSQL應用
6)BDB存儲引擎MYSQL應用
BDB存儲引擎全稱為BerkeleyDB存儲引擎,和Innodb一樣,也不是MySQL自己開發實現的一個存儲引擎,而是由SleepycatSoftware所提供,當然,也是開源存儲引擎,同樣支持事務平安.MYSQL應用
7)FEDERATED存儲引擎MYSQL應用
FEDERATED存儲引擎所實現的功能,和Oracle的DBLINK基本相似,主要用來提供對遠程MySQL服務器上面的數據的拜訪接口.如果我們使用源碼編譯來安裝MySQL,那么必須手工指定啟用FEDERATED存儲引擎才行,因為MySQL默認是不起用該存儲引擎的.MYSQL應用
8)ARCHIVE存儲引擎MYSQL應用
ARCHIVE存儲引擎主要用于通過較小的存儲空間來存放過期的很少拜訪的歷史數據.ARCHIVE表不支持索引,通過一個.frm的結構定義文件,一個.ARZ的數據壓縮文件還有一個.ARM的meta信息文件.由于其所存放的數據的特殊性,ARCHIVE表不支持刪除,修改操MYSQL應用
作,僅支持插入和查詢操作.鎖定機制為行級鎖定.MYSQL應用
9)BLACKHOLE存儲引擎MYSQL應用
BLACKHOLE存儲引擎是一個非常有意思的存儲引擎,功能恰如其名,就是一個“黑洞”.就像我們unix系統下面的“/dev/null”設備一樣,不管我們寫入任何信息,都是有去無回.MYSQL應用
10)CSV存儲引擎MYSQL應用
CSV存儲引擎實際上操作的就是一個標準的CSV文件,他不支持索引.起主要用途就是大家有些時候可能會需要通過數據庫中的數據導出成一份報表文件,而CSV文件是很多軟件都支持的一種較為標準的格式,所以我們可以通過先在數據庫中建立一張CVS表,然后將生成的報表信息插入到該表,即可得到一份CSV報表文件了.MYSQL應用
四、影響MySQLServer性能的相關因素MYSQL應用
1商業需求對性能的影響MYSQL應用
典型需求：一個論壇帖子總量的統計,要求：實時更新.MYSQL應用
2系統架構及實現對性能的影響MYSQL應用
以下幾類數據都是不適合在數據庫中存放的：MYSQL應用
二進制多媒體數據MYSQL應用
流水隊列數據MYSQL應用
超大文本數據MYSQL應用
通過Cache技術來提高系統性能：MYSQL應用
系統各種配置及規則數據；MYSQL應用
活躍用戶的基本信息數據；MYSQL應用
活躍用戶的個性化定制信息數據；MYSQL應用
準實時的統計信息數據；MYSQL應用
其他一些拜訪頻繁但變更較少的數據；MYSQL應用
3 Query語句對系統性能的影響MYSQL應用
需求：取出某個group(假設id為1)下的用戶編號(id),用戶昵稱(nick_name),并依照加入組的時間(user_group.gmt_create)來進行倒序排列,取出前20個.MYSQL應用
辦理方案一：MYSQL應用
辦理方案二：
MYSQL應用
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;
通過比較兩個辦理方案的執行計劃,我們可以看到第一中辦理方案中需要和user表參與Join的記錄數MySQL通過統計數據估算出來是31156,也就是通過user_group表返回的所有滿足group_id=1的記錄數(系統中的實際數據是20000).而第二種辦理方案的執行計劃中,user表參與Join的數據就只有20條,兩者相差很大,我們認為第二中辦理方案應該明顯優于第一種辦理方案.MYSQL應用
4 Schema設計對系統的性能影響MYSQL應用
盡量減少對數據庫訪問的哀求.MYSQL應用
盡量減少無用數據的查詢哀求.MYSQL應用
5硬件環境對系統性能的影響MYSQL應用
1、典型OLTP應用系統MYSQL應用
對于各種數據庫系統環境中大家最常見的OLTP系統,其特點是并發量大,整體數據量比較多,但每次拜訪的數據比較少,且拜訪的數據比較離散,活躍數據占總體數據的比例不是太大.對于這類系統的數據庫實際上是最難維護,最難以優化的,對主機整體性能要求也是最高的.因為不僅拜訪量很高,數據量也不小.MYSQL應用
針對上面的這些特點和分析,我們可以對OLTP的得出一個大致的方向.MYSQL應用
雖然系統總體數據量較大,但是系統活躍數據在數據總量中所占的比例不大,那么我們可以通過擴大內存容量來盡可能多的將活躍數據cache到內存中；MYSQL應用
雖然IO拜訪非常頻繁,但是每次拜訪的數據量較少且很離散,那么我們對磁盤存儲的要求是IOPS表現要很好,吞吐量是次要因素；MYSQL應用
并發量很高,CPU每秒所要處理的哀求自然也就很多,所以CPU處理能力需要比較強勁；MYSQL應用
雖然與客戶端的每次交互的數據量并不是特別大,但是網絡交互非常頻繁,所以主機與客戶端交互的網絡設備對流量能力也要求不能太弱.MYSQL應用
2、典型OLAP應用系統MYSQL應用
用于數據分析的OLAP系統的主要特點就是數據量非常大,并發拜訪不多,但每次拜訪所需要檢索的數據量都比較多,而且數據拜訪相對較為集中,沒有太明顯的活躍數據概念.MYSQL應用
基于OLAP系統的各種特點和相應的分析,針對OLAP系統硬件優化的大致策略如下：MYSQL應用
數據量非常大,所以磁盤存儲系統的單位容量需要盡量大一些；MYSQL應用
單次拜訪數據量較大,而且拜訪數據比較集中,那么對IO系統的性能要求是需要有盡可能大的每秒IO吞吐量,所以應該選用每秒吞吐量盡可能大的磁盤；MYSQL應用
雖然IO性能要求也比較高,但是并發哀求較少,所以CPU處理能力較難成為性能瓶頸,所以CPU處理能力沒有太苛刻的要求；MYSQL應用
雖然每次哀求的訪問量很大,但是執行過程中的數據大都不會返回給客戶端,最終返回給客戶端的數據量都較小,所以和客戶端交互的網絡設備要求并不是太高；MYSQL應用
此外,由于OLAP系統由于其每次運算過程較長,可以很好的并行化,所以一般的OLAP系統都是由多臺主機構成的一個集群,而集群中主機與主機之間的數據交互量一般來說都是非常大的,所以在集群中主機之間的網絡設備要求很高.MYSQL應用
3、除了以上兩個典型應用之外,還有一類比較特殊的應用系統,他們的數據量不是特別大,但是訪問哀求及其頻繁,而且大部分是讀哀求.可能每秒需要提供上萬甚至幾萬次哀求,每次哀求都非常簡單,可能大部分都只有一條或者幾條比較小的記錄返回,就比如基于數據庫的DNS服務就是這樣類型的服務.MYSQL應用
雖然數據量小,但是拜訪極其頻繁,所以可以通過較大的內存來cache住大部分的數據,這能夠保證非常高的命中率,磁盤IO量比較小,所以磁盤也不需要特別高性能的；MYSQL應用
并發哀求非常頻繁,比需要較強的CPU處理能力才能處理；MYSQL應用
雖然應用與數據庫交互量非常大,但是每次交互數據較少,總體流量雖然也會較大,但是一般來說普通的千兆網卡已經足夠了.MYSQL應用
五、MySQL 鎖定機制簡介MYSQL應用
行級鎖定(row-level)MYSQL應用
表級鎖定(table-level)MYSQL應用
頁級鎖定(page-level)MYSQL應用
在MySQL數據庫中,使用表級鎖定的主要是MyISAM,Memory,CSV等一些非事務性存儲引擎,而使用行級鎖定的主要是Innodb存儲引擎和NDBCluster存儲引擎,頁級鎖定主要是BerkeleyDB存儲引擎的鎖定方式.MYSQL應用
六、MySQL Query的優化MYSQL應用
Query語句的優化思路和原則主要提現在以下幾個方面：MYSQL應用
1. 優化更需要優化的Query；MYSQL應用
2. 定位優化對象的性能瓶頸；MYSQL應用
3. 明確的優化目標；MYSQL應用
4. 從Explain入手；MYSQL應用
5. 多使用profileMYSQL應用
6. 永遠用小結果集驅動大的結果集；MYSQL應用
7. 盡可能在索引中完成排序；MYSQL應用
8. 只取出自己需要的Columns；MYSQL應用
9. 僅僅使用最有效的過濾條件；MYSQL應用
10.盡可能避免復雜的Join和子查詢；MYSQL應用
合理設計并利用索引MYSQL應用
1)B-Tree索引MYSQL應用
一般來說,MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的結構來存儲的,也就是所有實際需要的數據都存放于Tree的LeafNode,而且到任何一個LeafNode的最短路徑的長度都是完全相同的,所以我們大家都稱之為B-Tree索引當然,可能各種數據庫(或MySQL的各種存儲引擎)在存放自己的B-Tree索引的時候會對存儲結構稍作改造.如Innodb存儲引擎的B-Tree索引實際使用的存儲結構實際上是B+Tree,也就是在B-Tree數據結構的基礎上做了很小的改造,在每一個LeafNode上面出了存放索引鍵的相關信息之外,還存儲了指向與該LeafNode相鄰的后一個LeafNode的指針信息,這主要是為了加快檢索多個相鄰LeafNode的效率考慮.MYSQL應用
2)Hash索引MYSQL應用
Hash索引在MySQL中使用的并不是很多,目前主要是Memory存儲引擎使用,而且在Memory存儲引擎中將Hash索引作為默認的索引類型.所謂Hash索引,實際上就是通過一定的Hash算法,將需要索引的鍵值進行Hash運算,然后將得到的Hash值存入一個Hash表中.然后每次需要檢索的時候,都會將檢索條件進行相同算法的Hash運算,然后再和Hash表中的Hash值進行比較并得出相應的信息.MYSQL應用
Hash索引僅僅只能滿足“=”,“IN”和“<=>”查詢,不能使用范圍查詢；MYSQL應用
Hash索引無法被利用來避免數據的排序操作；MYSQL應用
Hash索引不能利用部分索引鍵查詢；MYSQL應用
Hash索引在任何時候都不能避免表掃面；MYSQL應用
Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高；MYSQL應用
3)Full-text索引MYSQL應用
Full-text索引也就是我們常說的全文索引,目前在MySQL中僅有MyISAM存儲引擎支持,而且也并不是所有的數據類型都支持全文索引.目前來說,僅有CHAR,VARCHAR和TEXT這三種數據類型的列可以建Full-text索引.MYSQL應用
索引能夠極大的提高數據檢索效率,也能夠改善排序分組操作的性能,但是我們不能忽略的一個問題就是索引是完全獨立于基礎數據之外的一部分數據,更新數據會帶來的IO量和調整索引所致的計算量的資源消耗.MYSQL應用
是否需要創建索引,幾點原則：較頻繁的作為查詢條件的字段應該創建索引；唯一性太差的字段不適合單獨創建索引,即使頻繁作為查詢條件；更新非常頻繁的字段不適合創建索引；MYSQL應用
不會出現在WHERE子句中的字段不該創建索引；MYSQL應用
Join語句的優化MYSQL應用
盡可能減少Join語句中的NestedLoop的循環總次數；“永遠用小結果集驅動大的結果集”.MYSQL應用
優先優化NestedLoop的內層循環；MYSQL應用
保證Join語句中被驅動表上Join條件字段已經被索引；MYSQL應用
當無法保證被驅動表的Join條件字段被索引且內存資源充足的前提下,不要太吝惜JoinBuffer的設置；MYSQL應用
ORDER BY,GROUP BY和DISTINCT優化MYSQL應用
1)ORDER BY的實現與優化MYSQL應用
優化Query語句中的ORDER BY的時候,盡可能利用已有的索引來避免實際的排序計算,可以很大幅度的提升ORDER BY操作的性能.MYSQL應用
優化排序：MYSQL應用
1.加大max_length_for_sort_data參數的設置；MYSQL應用
2.去掉不必要的返回字段；MYSQL應用
3.增大sort_buffer_size參數設置；MYSQL應用
2)GROUP BY的實現與優化MYSQL應用
由于GROUP BY實際上也同樣需要進行排序操作,而且與ORDER BY相比,GROUP BY主要只是多了排序之后的分組操作.當然,如果在分組的時候還使用了其他的一些聚合函數,那么還需要一些聚合函數的計算.所以,在GROUP BY的實現過程中,與ORDER BY一樣也可以利用到索引.MYSQL應用
3)DISTINCT的實現與優化MYSQL應用
DISTINCT實際上和GROUP BY的操作非常相似,只不過是在GROUP BY之后的每組中只取出一條記錄罷了.所以,DISTINCT的實現和GROUP BY的實現也基本差不多,沒有太大的區別.同樣可以通過松散索引掃描或者是緊湊索引掃描來實現,當然,在無法僅僅使用索引即能完成DISTINCT的時候,MySQL只能通過臨時表來完成.但是,和GROUP BY有一點差別的是,DISTINCT并不需要進行排序.也就是說,在僅僅只是DISTINCT操作的Query如果無法僅僅利用索引完成操作的時候,MySQL會利用臨時表來做一次數據的“緩存”,但是不會對臨時表中的數據進行filesort操作.MYSQL應用
下篇地址：/article/70530.htmMYSQL應用
以上就是本文的全部內容,希望對大家的學習有所贊助.MYSQL應用
《Mysql實例詳解MySQL性能優化（一）》是否對您有啟發，歡迎查看更多與《Mysql實例詳解MySQL性能優化（一）》相關教程，學精學透。維易PHP學院為您提供精彩教程。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/8087.html
