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

2、日志記錄系統(tǒng)

3、數(shù)據(jù)倉庫系統(tǒng)

4、嵌入式系統(tǒng)

二、MySQL架構(gòu)圖：

三、MySQL存儲引擎概述

1）MyISAM存儲引擎

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

MyISAM支持以下三種類型的索引：

1、B-Tree索引

B-Tree索引，顧名思義，就是所有的索引節(jié)點(diǎn)都按照balancetree的數(shù)據(jù)結(jié)構(gòu)來存儲，所有的索引數(shù)據(jù)節(jié)點(diǎn)都在葉節(jié)點(diǎn)。

2、R-Tree索引

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

3、Full-text索引

Full-text索引就是我們長說的全文索引，他的存儲結(jié)構(gòu)也是b-tree。主要是為了解決在我們需要用like查詢的低效問題。

2）Innodb 存儲引擎

1、支持事務(wù)安裝

2、數(shù)據(jù)多版本讀取

3、鎖定機(jī)制的改進(jìn)

4、實(shí)現(xiàn)外鍵

3）NDBCluster存儲引擎

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

4）Merge存儲引擎

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

5）Memory存儲引擎

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

6）BDB存儲引擎

BDB存儲引擎全稱為BerkeleyDB存儲引擎，和Innodb一樣，也不是MySQL自己開發(fā)實(shí)現(xiàn)的一個(gè)存儲引擎，而是由SleepycatSoftware所提供，當(dāng)然，也是開源存儲引擎，同樣支持事務(wù)安全。

7）FEDERATED存儲引擎

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

8）ARCHIVE存儲引擎

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

作，僅支持插入和查詢操作。鎖定機(jī)制為行級鎖定。

9）BLACKHOLE存儲引擎

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

10）CSV存儲引擎

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

四、影響MySQLServer性能的相關(guān)因素

1商業(yè)需求對性能的影響

典型需求：一個(gè)論壇帖子總量的統(tǒng)計(jì)，要求：實(shí)時(shí)更新。

2系統(tǒng)架構(gòu)及實(shí)現(xiàn)對性能的影響

以下幾類數(shù)據(jù)都是不適合在數(shù)據(jù)庫中存放的：

二進(jìn)制多媒體數(shù)據(jù)

流水隊(duì)列數(shù)據(jù)

超大文本數(shù)據(jù)

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

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

活躍用戶的基本信息數(shù)據(jù)；

活躍用戶的個(gè)性化定制信息數(shù)據(jù)；

準(zhǔn)實(shí)時(shí)的統(tǒng)計(jì)信息數(shù)據(jù)；

其他一些訪問頻繁但變更較少的數(shù)據(jù)；

3 Query語句對系統(tǒng)性能的影響

需求：取出某個(gè)group（假設(shè)id為1）下的用戶編號（id），用戶昵稱（nick_name），并按照加入組的時(shí)間（user_group.gmt_create）來進(jìn)行倒序排列，取出前20個(gè)。

解決方案一：

解決方案二：

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;

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

4 Schema設(shè)計(jì)對系統(tǒng)的性能影響

盡量減少對數(shù)據(jù)庫訪問的請求。

盡量減少無用數(shù)據(jù)的查詢請求。

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

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

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

針對上面的這些特點(diǎn)和分析，我們可以對OLTP的得出一個(gè)大致的方向。

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

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

并發(fā)量很高，CPU每秒所要處理的請求自然也就很多，所以CPU處理能力需要比較強(qiáng)勁；

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

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

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

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

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

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

雖然IO性能要求也比較高，但是并發(fā)請求較少，所以CPU處理能力較難成為性能瓶頸，所以CPU處理能力沒有太苛刻的要求；

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

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

3、除了以上兩個(gè)典型應(yīng)用之外，還有一類比較特殊的應(yīng)用系統(tǒng)，他們的數(shù)據(jù)量不是特別大，但是訪問請求及其頻繁，而且大部分是讀請求?？赡苊棵胄枰峁┥先f甚至幾萬次請求，每次請求都非常簡單，可能大部分都只有一條或者幾條比較小的記錄返回，就比如基于數(shù)據(jù)庫的DNS服務(wù)就是這樣類型的服務(wù)。

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

并發(fā)請求非常頻繁，比需要較強(qiáng)的CPU處理能力才能處理；

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

五、MySQL 鎖定機(jī)制簡介

行級鎖定（row-level）

表級鎖定（table-level）

頁級鎖定（page-level）

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

六、MySQL Query的優(yōu)化

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

1. 優(yōu)化更需要優(yōu)化的Query；

2. 定位優(yōu)化對象的性能瓶頸；

3. 明確的優(yōu)化目標(biāo)；

4. 從Explain入手；

5. 多使用profile

6. 永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集；

7. 盡可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 僅僅使用最有效的過濾條件；

10.盡可能避免復(fù)雜的Join和子查詢；

合理設(shè)計(jì)并利用索引

1）B-Tree索引

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

2）Hash索引

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

Hash索引僅僅只能滿足“=”,“IN”和“<=>”查詢，不能使用范圍查詢；

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

Hash索引不能利用部分索引鍵查詢；

Hash索引在任何時(shí)候都不能避免表掃面；

Hash索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高；

3）Full-text索引

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

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

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

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

Join語句的優(yōu)化

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

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

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

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

ORDER BY，GROUP BY和DISTINCT優(yōu)化

1）ORDER BY的實(shí)現(xiàn)與優(yōu)化

優(yōu)化Query語句中的ORDER BY的時(shí)候，盡可能利用已有的索引來避免實(shí)際的排序計(jì)算，可以很大幅度的提升ORDER BY操作的性能。

優(yōu)化排序：

1.加大max_length_for_sort_data參數(shù)的設(shè)置；

2.去掉不必要的返回字段；

3.增大sort_buffer_size參數(shù)設(shè)置；

2）GROUP BY的實(shí)現(xiàn)與優(yōu)化

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

3）DISTINCT的實(shí)現(xiàn)與優(yōu)化

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

下篇地址：http://m.fzitv.net/article/70530.htm

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助。

最新評論