一、mysql的主要适用場景
1、web網站系統
2、日志記錄系統
3、資料倉庫系統
4、嵌入式系統
二、mysql架構圖：
三、mysql存儲引擎概述
1）myisam存儲引擎
myisam存儲引擎的表在資料庫中，每一個表都被存放為三個以表名命名的實體檔案。首先肯定會有任何存儲引擎都不可缺少的存放表結構定義資訊的.frm檔案，另外還有.myd和.myi檔案，分别存放了表的資料（.myd）和索引資料（.myi）。每個表都有且僅有這樣三個檔案做為myisam存儲類型的表的存儲，也就是說不管這個表有多少個索引，都是存放在同一個.myi檔案中。
myisam支援以下三種類型的索引：
1、b-tree索引
b-tree索引，顧名思義，就是所有的索引節點都按照balancetree的資料結構來存儲，所有的索引資料節點都在葉節點。
2、r-tree索引
r-tree索引的存儲方式和b-tree索引有一些差別，主要設計用于為存儲空間和多元資料的字段做索引，是以目前的mysql版本來說，也僅支援geometry類型的字段作索引。
3、full-text索引
full-text索引就是我們長說的全文索引，他的存儲結構也是b-tree。主要是為了解決在我們需要用like查詢的低效問題。
2）innodb 存儲引擎
1、支援事務安裝
2、資料多版本讀取
3、鎖定機制的改進
4、實作外鍵
3）ndbcluster存儲引擎
ndb存儲引擎也叫ndbcluster存儲引擎，主要用于mysqlcluster分布式叢集環境，cluster是mysql從5.0版本才開始提供的新功能。
4）merge存儲引擎
merge存儲引擎，在mysql使用者手冊中也提到了，也被大家認識為mrg_myisam引擎。why？因為merge存儲引擎可以簡單的了解為其功能就是實作了對結構相同的myisam表，通過一些特殊的包裝對外提供一個單一的通路入口，以達到減小應用的複雜度的目的。要建立merge表，不僅僅基表的結構要完全一緻，包括字段的順序，基表的索引也必須完全一緻。
5）memory存儲引擎
memory存儲引擎，通過名字就很容易讓人知道，他是一個将資料存儲在記憶體中的存儲引擎。memory存儲引擎不會将任何資料存放到磁盤上，僅僅存放了一個表結構相關資訊的.frm檔案在磁盤上面。是以一旦mysqlcrash或者主機crash之後，memory的表就隻剩下一個結構了。memory表支援索引，并且同時支援hash和b－tree兩種格式的索引。由于是存放在記憶體中，是以memory都是按照定長的空間來存儲資料的，而且不支援blob和text類型的字段。memory存儲引擎實作頁級鎖定。
6）bdb存儲引擎
bdb存儲引擎全稱為berkeleydb存儲引擎，和innodb一樣，也不是mysql自己開發實作的一個存儲引擎，而是由sleepycatsoftware所提供，當然，也是開源存儲引擎，同樣支援事務安全。
7）federated存儲引擎
federated存儲引擎所實作的功能，和oracle的dblink基本相似，主要用來提供對遠端mysql伺服器上面的資料的通路接口。如果我們使用源碼編譯來安裝mysql，那麼必須手工指定啟用federated存儲引擎才行，因為mysql預設是不起用該存儲引擎的。
8）archive存儲引擎
archive存儲引擎主要用于通過較小的存儲空間來存放過期的很少通路的曆史資料。archive表不支援索引，通過一個.frm的結構定義檔案，一個.arz的資料壓縮檔案還有一個.arm的meta資訊檔案。由于其所存放的資料的特殊性，archive表不支援删除，修改操
作，僅支援插入和查詢操作。鎖定機制為行級鎖定。
9）blackhole存儲引擎
blackhole存儲引擎是一個非常有意思的存儲引擎，功能恰如其名，就是一個“黑洞”。就像我們unix系統下面的“/dev/null”裝置一樣，不管我們寫入任何資訊，都是有去無回。
10）csv存儲引擎
csv存儲引擎實際上操作的就是一個标準的csv檔案，他不支援索引。起主要用途就是大家有些時候可能會需要通過資料庫中的資料導出成一份報表檔案，而csv檔案是很多軟體都支援的一種較為标準的格式，是以我們可以通過先在資料庫中建立一張cvs表，然後将生成的報表資訊插入到該表，即可得到一份csv報表檔案了。
四、影響mysqlserver性能的相關因素
1商業需求對性能的影響
典型需求：一個論壇文章總量的統計，要求：實時更新。
2系統架構及實作對性能的影響
以下幾類資料都是不适合在資料庫中存放的：
二進制多媒體資料
流水隊列資料
超大文本資料
通過cache技術來提高系統性能：
系統各種配置及規則資料；
活躍使用者的基本資訊資料；
活躍使用者的個性化定制資訊資料；
準實時的統計資訊資料；
其他一些通路頻繁但變更較少的資料；
3 query語句對系統性能的影響
需求：取出某個group（假設id為1）下的使用者編号（id），使用者昵稱（nick_name），并按照加入組的時間（user_group.gmt_create）來進行倒序排列，取出前20個。
解決方案一：
解決方案二：
通過比較兩個解決方案的執行計劃，我們可以看到第一中解決方案中需要和user表參與join的記錄數mysql通過統計資料估算出來是31156，也就是通過user_group表傳回的所有滿足group_id=1的記錄數（系統中的實際資料是20000）。而第二種解決方案的執行計劃中，user表參與join的資料就隻有20條，兩者相差很大，我們認為第二中解決方案應該明顯優于第一種解決方案。
4 schema設計對系統的性能影響
盡量減少對資料庫通路的請求。
盡量減少無用資料的查詢請求。
5硬體環境對系統性能的影響
1、典型oltp應用系統
對于各種資料庫系統環境中大家最常見的oltp系統，其特點是并發量大，整體資料量比較多，但每次通路的資料比較少，且通路的資料比較離散，活躍資料占總體資料的比例不是太大。對于這類系統的資料庫實際上是最難維護，最難以優化的，對主機整體性能要求也是最高的。因為不僅通路量很高，資料量也不小。
針對上面的這些特點和分析，我們可以對oltp的得出一個大緻的方向。
雖然系統總體資料量較大，但是系統活躍資料在資料總量中所占的比例不大，那麼我們可以通過擴大記憶體容量來盡可能多的将活躍資料cache到記憶體中；
雖然io通路非常頻繁，但是每次通路的資料量較少且很離散，那麼我們對磁盤存儲的要求是iops表現要很好，吞吐量是次要因素；
并發量很高，cpu每秒所要處理的請求自然也就很多，是以cpu處理能力需要比較強勁；
雖然與用戶端的每次互動的資料量并不是特别大，但是網絡互動非常頻繁，是以主機與用戶端互動的網絡裝置對流量能力也要求不能太弱。
2、典型olap應用系統
用于資料分析的olap系統的主要特點就是資料量非常大，并發通路不多，但每次通路所需要檢索的資料量都比較多，而且資料通路相對較為集中，沒有太明顯的活躍資料概念。
基于olap系統的各種特點和相應的分析，針對olap系統硬體優化的大緻政策如下：
資料量非常大，是以磁盤存儲系統的機關容量需要盡量大一些；
單次通路資料量較大，而且通路資料比較集中，那麼對io系統的性能要求是需要有盡可能大的每秒io吞吐量，是以應該選用每秒吞吐量盡可能大的磁盤；
雖然io性能要求也比較高，但是并發請求較少，是以cpu處理能力較難成為性能瓶頸，是以cpu處理能力沒有太苛刻的要求；
雖然每次請求的通路量很大，但是執行過程中的資料大都不會傳回給用戶端，最終傳回給用戶端的資料量都較小，是以和用戶端互動的網絡裝置要求并不是太高；
此外，由于olap系統由于其每次運算過程較長，可以很好的并行化，是以一般的olap系統都是由多台主機構成的一個叢集，而叢集中主機與主機之間的資料互動量一般來說都是非常大的，是以在叢集中主機之間的網絡裝置要求很高。
3、除了以上兩個典型應用之外，還有一類比較特殊的應用系統，他們的資料量不是特别大，但是通路請求及其頻繁，而且大部分是讀請求。可能每秒需要提供上萬甚至幾萬次請求，每次請求都非常簡單，可能大部分都隻有一條或者幾條比較小的記錄傳回，就比如基于資料庫的dns服務就是這樣類型的服務。
雖然資料量小，但是通路極其頻繁，是以可以通過較大的記憶體來cache住大部分的資料，這能夠保證非常高的命中率，磁盤io量比較小，是以磁盤也不需要特别高性能的；
并發請求非常頻繁，比需要較強的cpu處理能力才能處理；
雖然應用與資料庫互動量非常大，但是每次互動資料較少，總體流量雖然也會較大，但是一般來說普通的千兆網卡已經足夠了。
五、mysql 鎖定機制簡介
行級鎖定（row-level）
表級鎖定（table-level）
頁級鎖定（page-level）
在mysql資料庫中，使用表級鎖定的主要是myisam，memory，csv等一些非事務性存儲引擎，而使用行級鎖定的主要是innodb存儲引擎和ndbcluster存儲引擎，頁級鎖定主要是berkeleydb存儲引擎的鎖定方式。
六、mysql query的優化
query語句的優化思路和原則主要提現在以下幾個方面：
1. 優化更需要優化的query；
2. 定位優化對象的性能瓶頸；
3. 明确的優化目标；
4. 從explain入手；
5. 多使用profile
6. 永遠用小結果集驅動大的結果集；
7. 盡可能在索引中完成排序；
8. 隻取出自己需要的columns；
9. 僅僅使用最有效的過濾條件；
10.盡可能避免複雜的join和子查詢；
合理設計并利用索引
1）b-tree索引
一般來說，mysql中的b-tree索引的實體檔案大多都是以balancetree的結構來存儲的，也就是所有實際需要的資料都存放于tree的leafnode，而且到任何一個leafnode的最短路徑的長度都是完全相同的，是以我們大家都稱之為b-tree索引當然，可能各種資料庫（或mysql的各種存儲引擎）在存放自己的b-tree索引的時候會對存儲結構稍作改造。如innodb存儲引擎的b-tree索引實際使用的存儲結構實際上是b+tree，也就是在b-tree資料結構的基礎上做了很小的改造，在每一個leafnode上面出了存放索引鍵的相關資訊之外，還存儲了指向與該leafnode相鄰的後一個leafnode的指針資訊，這主要是為了加快檢索多個相鄰leafnode的效率考慮。
2）hash索引
hash索引在mysql中使用的并不是很多，目前主要是memory存儲引擎使用，而且在memory存儲引擎中将hash索引作為預設的索引類型。所謂hash索引，實際上就是通過一定的hash算法，将需要索引的鍵值進行hash運算，然後将得到的hash值存入一個hash表中。然後每次需要檢索的時候，都會将檢索條件進行相同算法的hash運算，然後再和hash表中的hash值進行比較并得出相應的資訊。
hash索引僅僅隻能滿足“=”,“in”和“<=>”查詢，不能使用範圍查詢；
hash索引無法被利用來避免資料的排序操作；
hash索引不能利用部分索引鍵查詢；
hash索引在任何時候都不能避免表掃面；
hash索引遇到大量hash值相等的情況後性能并不一定就會比b-tree索引高；
3）full-text索引
full-text索引也就是我們常說的全文索引，目前在mysql中僅有myisam存儲引擎支援，而且也并不是所有的資料類型都支援全文索引。目前來說，僅有char，varchar和text這三種資料類型的列可以建full-text索引。
索引能夠極大的提高資料檢索效率，也能夠改善排序分組操作的性能，但是我們不能忽略的一個問題就是索引是完全獨立于基礎資料之外的一部分資料，更新資料會帶來的io量和調整索引所緻的計算量的資源消耗。
是否需要建立索引，幾點原則：較頻繁的作為查詢條件的字段應該建立索引；唯一性太差的字段不适合單獨建立索引，即使頻繁作為查詢條件；更新非常頻繁的字段不适合建立索引；
不會出現在where子句中的字段不該建立索引；
join語句的優化
盡可能減少join語句中的nestedloop的循環總次數；“永遠用小結果集驅動大的結果集”。
優先優化nestedloop的内層循環；
保證join語句中被驅動表上join條件字段已經被索引；
當無法保證被驅動表的join條件字段被索引且記憶體資源充足的前提下，不要太吝惜joinbuffer的設定；
order by，group by和distinct優化
1）order by的實作與優化
優化query語句中的order by的時候，盡可能利用已有的索引來避免實際的排序計算，可以很大幅度的提升order by操作的性能。
優化排序：
1.加大max_length_for_sort_data參數的設定；
2.去掉不必要的傳回字段；
3.增大sort_buffer_size參數設定；
2）group by的實作與優化
由于group by實際上也同樣需要進行排序操作，而且與order by相比，group by主要隻是多了排序之後的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函數，那麼還需要一些聚合函數的計算。是以，在group by的實作過程中，與order by一樣也可以利用到索引。
3）distinct的實作與優化
distinct實際上和group by的操作非常相似，隻不過是在group by之後的每組中隻取出一條記錄而已。是以，distinct的實作和group by的實作也基本差不多，沒有太大的差別。同樣可以通過松散索引掃描或者是緊湊索引掃描來實作，當然，在無法僅僅使用索引即能完成distinct的時候，mysql隻能通過臨時表來完成。但是，和group by有一點差别的是，distinct并不需要進行排序。也就是說，在僅僅隻是distinct操作的query如果無法僅僅利用索引完成操作的時候，mysql會利用臨時表來做一次資料的“緩存”，但是不會對臨時表中的資料進行filesort操作。
七、mysql資料庫schema設計的性能優化
高效的模型設計
适度備援-讓query盡兩減少join
大字段垂直分拆-summary表優化
大表水準分拆-基于類型的分拆優化
統計表-準實時優化
合适的資料類型
時間存儲格式總類并不是太多，我們常用的主要就是datetime，date和timestamp這三種了。從存儲空間來看timestamp最少，四個位元組，而其他兩種資料類型都是八個位元組，多了一倍。而timestamp的缺點在于他隻能存儲從1970年之後的時間，而另外兩種時間類型可以存放最早從1001年開始的時間。如果有需要存放早于1970年之前的時間的需求，我們必須放棄timestamp類型，但是隻要我們不需要使用1970年之前的時間，最好盡量使用timestamp來減少存儲空間的占用。
字元存儲類型
char[(m)]類型屬于靜态長度類型，存放長度完全以字元數來計算，是以最終的存儲長度是基于字元集的，如latin1則最大存儲長度為255位元組，但是如果使用gbk則最大存儲長度為510位元組。char類型的存儲特點是不管我們實際存放多長資料，在資料庫中都會存放m個字元，不夠的通過空格補上，m預設為1。雖然char會通過空格補齊存放的空間，但是在通路資料的時候，mysql會忽略最後的所有空格，是以如果我們的實際資料中如果在最後确實需要空格，則不能使用char類型來存放。
varchar[(m)]屬于動态存儲長度類型，僅存占用實際存儲資料的長度。tinytext，text，mediumtext和longtext這四種類型同屬于一種存儲方式，都是動态存儲長度類型，不同的僅僅是最大長度的限制。
事務優化
1. 髒讀：髒讀就是指當一個事務正在通路資料，并且對資料進行了修改，而這種修改還沒有送出到資料庫中，這時，另外一個事務也通路這個資料，然後使用了這個資料。
2. 不可重複讀：是指在一個事務内，多次讀同一資料。在這個事務還沒有結束時，另外一個事務也通路該同一資料。那麼，在第一個事務中的兩次讀資料之間，由于第二個事務的修改，那麼第一個事務兩次讀到的的資料可能是不一樣的。這樣就發生了在一個事務内兩次讀到的資料是不一樣的，是以稱為是不可重複讀。
3. 幻讀：是指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好象發生了幻覺一樣。
innodb在事務隔離級别方面支援的資訊如下：
1.read uncommitted
常被成為dirty reads（髒讀），可以說是事務上的最低隔離級别：在普通的非鎖定模式下select的執行使我們看到的資料可能并不是查詢發起時間點的資料，因而在這個隔離度下是非consistent reads（一緻性讀）；
2.read committed
這一隔離級别下，不會出現dirtyread，但是可能出現non-repeatablereads(不可重複讀)和phantomreads（幻讀）。
3. repeatable read
repeatable read隔離級别是innodb預設的事務隔離級。在repeatable read隔離級别下，不會出現dirtyreads，也不會出現non-repeatable read，但是仍然存在phantomreads的可能性。
4.serializable
serializable隔離級别是标準事務隔離級别中的最進階别。設定為serializable隔離級别之後，在事務中的任何時候所看到的資料都是事務啟動時刻的狀态，不論在這期間有沒有其他事務已經修改了某些資料并送出。是以，serializable事務隔離級别下，phantomreads也不會出現。
八、可擴充性設計之資料切分
資料的垂直切分
資料的垂直切分，也可以稱之為縱向切分。将資料庫想象成為由很多個一大塊一大塊的“資料塊”（表）組成，我們垂直的将這些“資料塊”切開，然後将他們分散到多台資料庫主機上面。這樣的切分方法就是一個垂直（縱向）的資料切分。
垂直切分的優點
◆資料庫的拆分簡單明了，拆分規則明确；
◆應用程式子產品清晰明确，整合容易；
◆資料維護友善易行，容易定位；
垂直切分的缺點
◆部分表關聯無法在資料庫級别完成，需要在程式中完成；
◆對于通路極其頻繁且資料量超大的表仍然存在性能平靜，不一定能滿足要求；
◆事務處理相對更為複雜；
◆切分達到一定程度之後，擴充性會遇到限制；
◆過讀切分可能會帶來系統過渡複雜而難以維護。
資料的水準切分
資料的垂直切分基本上可以簡單的了解為按照表按照子產品來切分資料，而水準切分就不再是按照表或者是功能子產品來切分了。一般來說，簡單的水準切分主要是将某個通路極其平凡的表再按照某個字段的某種規則來分散到多個表之中，每個表中包含一部分資料。
水準切分的優點
◆表關聯基本能夠在資料庫端全部完成；
◆不會存在某些超大型資料量和高負載的表遇到瓶頸的問題；
◆應用程式端整體架構改動相對較少；
◆事務處理相對簡單；
◆隻要切分規則能夠定義好，基本上較難遇到擴充性限制；
水準切分的缺點
◆切分規則相對更為複雜，很難抽象出一個能夠滿足整個資料庫的切分規則；
◆後期資料的維護難度有所增加，人為手工定位資料更困難；
◆應用系統各子產品耦合度較高，可能會對後面資料的遷移拆分造成一定的困難。
資料切分與整合中可能存在的問題
1.引入分布式事務的問題
完全可以将一個跨多個資料庫的分布式事務分拆成多個僅處于單個資料庫上面的小事務，并通過應用程式來總控各個小事務。當然，這樣作的要求就是我們的俄應用程式必須要有足夠的健壯性，當然也會給應用程式帶來一些技術難度。
2.跨節點join的問題
推薦通過應用程式來進行處理，先在驅動表所在的mysqlserver中取出相應的驅動結果集，然後根據驅動結果集再到被驅動表所在的mysql server中取出相應的資料。
3.跨節點合并排序分頁問題
從多個資料源并行的取資料，然後應用程式彙總處理。
九、可擴充性設計之cache與search的利用
通過引入cache（redis、memcached），減少資料庫的通路，增加性能。
通過引入search（lucene、solr、elasticsearch），利用搜尋引擎高效的全文索引和分詞算法，以及高效的資料檢索實作，來解決資料庫和傳統的cache軟體完全無法解決的全文模糊搜尋、分類統計查詢等功能。
本文乃《mysql性能調優與架構設計》讀書筆記！
本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。
http://www.cnblogs.com/luxiaoxun/p/4694144.html