MySQL體系結(jié)構(gòu)：
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、成都網(wǎng)站設(shè)計、景泰網(wǎng)絡(luò)推廣、重慶小程序開發(fā)公司、景泰網(wǎng)絡(luò)營銷、景泰企業(yè)策劃、景泰品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供景泰建站搭建服務(wù)，24小時服務(wù)熱線：028-86922220，官方網(wǎng)址：www.cdcxhl.com
由：連接池組件、管理服務(wù)和工具組件、sql接口組件、查詢分析器組件、優(yōu)化器組件、
緩沖組件、插件式存儲引擎、物理文件組成。
mysql是獨有的插件式體系結(jié)構(gòu)，各個存儲引擎有自己的特點。
mysql各個存儲引擎概述：
（1） innodb存儲引擎：[/color][/b] 面向oltp(online transaction processing)、行鎖、支持外鍵、非鎖定讀、默認(rèn)采用repeaable級別（可重復(fù)讀）通過next-keylocking策略避免幻讀、插入緩沖、二次寫、自適應(yīng)哈希索引、預(yù)讀。
（2） myisam存儲引擎: 不支持事務(wù)、表鎖、全文索引、適合olap（在線分析處理），其中myd:放數(shù)據(jù)文件，myi:放索引文件。
（3）ndb存儲引擎： 集群存儲引擎，share nothing，可提高可用性。
（4）memory存儲引擎： 數(shù)據(jù)存放在內(nèi)存中，表鎖，并發(fā)性能差，默認(rèn)使用哈希索引。
（5） archive存儲引擎：只支持insert和select zlib算法壓縮1：10，適合存儲歸檔數(shù)據(jù)如日志等、行鎖。
（6）maria存儲引擎： 目的取代myisam、緩存數(shù)據(jù)和索引、行鎖、mvcc。
innodb特性：
（1）主體系結(jié)構(gòu)：默認(rèn)7個后臺線程，4個io thread(insert buffer、log、read、write),1個master thread(優(yōu)先級最高),1個鎖(lock)監(jiān)控線程，1個錯誤監(jiān)控線程?？梢酝ㄟ^show engine innodb status來查看。新版本已對默認(rèn)的read thread和write thread分別增大到4個，可通過show variables like 'innodb_io_thread%'查看。
（2） 存儲引擎組成：緩沖池(buffer pool)、重做日志緩沖池(redo log buffer)以及額外的內(nèi)存池(additional memory pool).具體配置可由show variables like 'innodb_buffer_pool_size'、show variables like
'innodb_log_buffer_size'、show variables like 'innodb_additional_mem_pool_size'來查看。
（3） 緩沖池：占最大塊內(nèi)存，用來存放各種數(shù)據(jù)的緩存包括有索引頁、數(shù)據(jù)頁、undo頁、插入緩沖、自適應(yīng)哈希索引、innodb存儲的鎖信息、數(shù)據(jù)字典信息等。工作方式總是將數(shù)據(jù)庫文件按頁(每頁16k)讀取到緩沖池，然后按最近最少使用(lru)的算法來保留在緩沖池中的緩存數(shù)據(jù)。如果數(shù)據(jù)庫文件需要修改，總是首先修改在緩存池中的頁(發(fā)生修改后即為臟頁)，然后再按照一定的頻率將緩沖池的臟頁刷新到文件。通過命令show engine innodb status;來查看。
（4） 日志緩沖：將重做日志信息先放入這個緩沖區(qū)，然后按一定頻率將其刷新到重做日志文件。
master thread（主線程）:
（1） loop主循環(huán)每秒一次的操作：
日志緩沖刷新到磁盤，即使這個事務(wù)還沒有提交。(總是執(zhí)行，所以再大的事務(wù)commit
的時間也是很快的)
合并插入緩沖(innodb當(dāng)前一秒發(fā)生的io次數(shù)小于5次則執(zhí)行)
至多刷新100個innodb的緩沖池中的臟頁到磁盤(超過配置的臟頁所占緩沖池比例則執(zhí)
行，在配置文件中由innodb_max_dirty_pages_pac決定，默認(rèn)是90，新版本是75，
google建議是80)
如果當(dāng)前沒用用戶活動，切換到backgroud loop（2） loop主循環(huán)每10秒一次的操作：
刷新100個臟頁到磁盤(過去10秒IO操作小于200次則執(zhí)行)
合并至多5個插入緩沖(總是)
將日志緩沖到磁盤(總是)
刪除無用的Undo頁(總是)
刷新100個或者10個臟頁到磁盤(有超過70%的臟頁，刷新100個臟頁;否則刷新10個臟頁)
產(chǎn)生一個檢查點
（3） backgroud loop，若當(dāng)前沒有用戶活動(數(shù)據(jù)庫空閑時)或者數(shù)據(jù)庫關(guān)閉時，就會切換到這個循環(huán)：
刪除無用的Undo頁(總是)
合并20個插入緩沖(總是)
跳回到主循環(huán)(總是)
不斷刷新100個頁，直到符合條件(可能在flush loop中完成)
如果flush loop中也沒有什么事情可以做了，InnoDB存儲引擎會切換到suspend_loop，將master thread掛起，等待事件的發(fā)生。若啟用了InnoDB存儲引擎，卻沒有使用任何InnoDB存儲引擎的表，那么master thread總是處于掛起狀態(tài)。插入緩沖：不是緩沖池的一部分，Insert Buffer是物理頁的一個組成部分,它帶來InnoDB性能的提高。根據(jù)B+算法(下文會提到)的特點，插入數(shù)據(jù)的時候會主鍵索引是順序的，不會造成數(shù)據(jù)庫的隨機(jī)讀取，而對于非聚集索引(即輔助索引)，葉子節(jié)點的插入不再是順序的了，這時需要離散地訪問非聚集索引，插入性能在這里變低了。InnoDB引入插入緩沖，判斷非聚集索引頁是否在緩沖池中，如果在則直接插入;不在，則先放在 插入緩沖區(qū)中。然后根據(jù)上述master thread中介紹的，會有一定的頻率將插入緩沖合并。此外，輔助索引不能是唯一的，因為插入到插入緩沖時，并不去查找索引頁的情況，否則仍然會造成隨機(jī)讀，失去插入緩沖的意義了。插入緩沖可能會占緩沖池中內(nèi)存，默認(rèn)也能會占到1/2，所以可以將這個值調(diào)小點，到1/3。通過IBUF_POOL_SIZE_PER_MAX_SIZE來設(shè)置，2表示1/2,3表示1/3。
兩次寫： 它帶來InnoDB數(shù)據(jù)的可靠性。如果寫失效，可以通過重做日志進(jìn)行恢復(fù)，但是重做日志中記錄的是對頁的物理操作，如果頁本身損壞，再對其進(jìn)行重做是沒有意義的。所以，在應(yīng)用重做日志前，需要一個頁的副本，當(dāng)寫入失效發(fā)生時，先通過頁的副本來還原該頁，再進(jìn)行重做，這就是doublewire。
恢復(fù)數(shù)據(jù)=頁副本+重做日志
自適應(yīng)哈希索引：InnoDB存儲引擎提出一種自適應(yīng)哈希索引，存儲引擎會監(jiān)控對表上索引的查找，如果觀察到建立建立哈希索引會帶來速度的提升，則建立哈希索引，所以稱之為自適應(yīng)的。自適應(yīng)哈希索引只能用來搜索等值的查詢，如select * from table where index_col='***', 此外自適應(yīng)哈希是由InnoDB存儲引擎控制的，我們只能通過innodb_adaptive_hash_index來禁用或啟用，默認(rèn)開啟。
參數(shù)文件：告訴Mysql實例啟動時在哪里可以找到數(shù)據(jù)庫文件，并且指定某些初始化參數(shù)，這些參數(shù)定義了某種內(nèi)存結(jié)構(gòu)的大小等設(shè)置。用文件存儲，可編輯，若啟動時加載不到則不能成功啟動（與其他數(shù)據(jù)庫不同）。參數(shù)有動態(tài)和靜態(tài)之分，靜態(tài)相當(dāng)于只讀，動態(tài)是可以set的。如我們通過show variable like '***'查出來的key、value值，是可以通過set key=value直接修改的。同是，修改時還有作用域之分，即這個seesion個有效和全局有效，在對應(yīng)的key前加上session或global即可，如select @@seesion.read_buffer_size、set @@global.read_buffer_size。
日志文件：用來記錄Mysql實例對某種條件做出響應(yīng)時寫入的文件。如錯誤日志文件、二進(jìn)制日志文件、慢查詢?nèi)罩疚募?、查詢?nèi)罩疚募取?br/>錯誤日志：通過show variables like 'log_error'來查看錯誤日志存放地址。
慢查詢?nèi)罩荆?/strong>通過show variables like '%long%' 查看慢查詢?nèi)罩居涗浀拈撝?，新版本設(shè)成了0.05；通過show variables like 'log_slow_queries'查看是否開啟了，默認(rèn)為關(guān)閉的；通過show variabes like 'log_queries_not_using_indexes'查看是將沒有使用索引的查詢記錄到慢日志中。mysql中可以直接通過mysqldumpslow命令來查看慢日志。
二進(jìn)制文件：不記錄查詢，只記錄對數(shù)據(jù)庫所有的修改操作。目的是為了恢復(fù)(point-in-time修復(fù))和復(fù)制。通過show variables like 'datadir'查看存放路徑。二進(jìn)制日志支持STATEMENT、ROW、MIX三種格式，通過binlog_format參數(shù)設(shè)定，通常設(shè)置為ROW，可以為數(shù)據(jù)庫的恢復(fù)和復(fù)制帶來更好的可靠性，但會帶來二進(jìn)制文件大小的增加，復(fù)制時會增加網(wǎng)絡(luò)開銷。mysql中通過mysqlbinlog查看二進(jìn)制日志文件內(nèi)容。
socket文件：當(dāng)用Unix域套接字方式進(jìn)行連接時需要的文件。
pid文件：Mysql實例的進(jìn)程ID文件。
Mysql表結(jié)構(gòu)文件：用來存放Mysql表結(jié)構(gòu)定義文件。因為Mysql插件式存儲引擎的體系結(jié)構(gòu)，每個表都有一個對應(yīng)的文件，以frm后綴結(jié)尾。
存儲引擎文件：存儲自己的文件來保存各種數(shù)據(jù)，真正存儲了數(shù)據(jù)和索引等數(shù)據(jù)。下面主要介紹InnoDB的存儲引擎下的表空間文件和重做日志文件。
表空間文件：InnoDB默認(rèn)的表空間文件為ibdata1,可通過show variables like 'innodb_file_per_table'查看每個表是否產(chǎn)生單獨的.idb表空間文件。但是，單獨的表空間文件僅存儲該表的數(shù)據(jù)、索引和插入緩沖等信息，其余信息還是存放在默認(rèn)的表空間中。
重做日志文件：實例和介質(zhì)失敗，重做日志文件就能派上用場，如數(shù)據(jù)庫掉電，InnoDB存儲引擎會使用重做日志恢復(fù)到掉電前的時刻，以此來保證數(shù)據(jù)的完整性。參數(shù)innodb_log_file_size指定了重做日志文件的大?。籭nnodb_log_file_in_group指定了日志文件組中重做日志文件的數(shù)量，默認(rèn)為2，innodb_mirrored_log_groups指定了日志鏡像文件組的數(shù)量，默認(rèn)為1，代表只有一個日志文件組，沒有鏡像；innodb_log_group_home_dir指定了日志文件組所在路徑，默認(rèn)在數(shù)據(jù)庫路徑下。
二進(jìn)制日志和重做日志的區(qū)別：首先，二進(jìn)制日志會記錄所有與Mysql有關(guān)的日志記錄，包括InnoDB、MyISAM、Heap等其他存儲引擎的日志。而InnoDB存儲引擎重做日志只存儲有關(guān)其本身的事務(wù)日志；其次內(nèi)容不同，不管將二進(jìn)制日志文件記錄的格式設(shè)為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關(guān)于一個事務(wù)的具體操作內(nèi)容。而InnoDB存儲引擎的重做日志文件記錄的關(guān)于每個頁的更改的物理情況 。此外，寫入時間不同，二進(jìn)制日志文件是在事務(wù)提交前進(jìn)行記錄的，而在事務(wù)進(jìn)行的過程中，不斷有重做日志條目被 寫入重做日志文件中。
表空間：表空間可看做是InnoDB存儲引擎邏輯結(jié)構(gòu)的最高層。
段：表空間由各個段組成，常見的段有數(shù)據(jù)段、索引段、回滾段等。
區(qū)：由64個連續(xù)的頁組成，每個頁大小為16kb，即每個區(qū)大小為1MB。
頁：每頁16kb，且不能更改。常見的頁類型有：數(shù)據(jù)頁、Undo頁、系統(tǒng)頁、事務(wù)數(shù)據(jù)頁、插入緩沖位圖頁、插入緩沖空閑列表頁、未壓縮的二進(jìn)制大對象頁、壓縮的二進(jìn)制大對象頁。
行：InnoDB存儲引擎是面向行的(row-oriented)，每頁最多允許存放7992行數(shù)據(jù)。
行記錄格式：常見兩種行記錄格式Compact和Redundant，mysql5.1版本后，主要是Compact行記錄格式。對于Compact，不管是char型還是varchar型，null型都是不占用存儲空間的；對于Redudant,varchar的null不占用空間，char的null型是占用存儲空間的。
varchar類型的長度限制是65535，其實達(dá)不到，會有別的開銷，一般是65530左右，這還跟選取的字符集有關(guān)。此外這個長度限制是一整行的，例如：create table test(a varchar(22000), b varchar(22000), cvarchar(22000)) charset=latin1 engine=innodb也會報錯。
對于blob類型的數(shù)據(jù)，在數(shù)據(jù)頁面中只保存了varchar(65535)的前768個字節(jié)前綴數(shù)據(jù)，之后跟的是偏移量，指向行溢出頁，也就是Uncompressed BLOB Page。新的InnoDB Plugin引入了新的文件格式稱為Barracuda，其有兩種新的行記錄格式Compressed和Dynamic，兩者對于存入Blog字段采用了完全溢出的方式，在數(shù)據(jù)庫頁中存放20個字節(jié)的指針，實際的數(shù)據(jù)都存入在BLOB Page中。
數(shù)據(jù)頁結(jié)構(gòu)：數(shù)據(jù)頁結(jié)構(gòu)由以下7個部分組成：
File Header(文件頭):記錄頁的一些頭信息，如頁偏移量、上一頁、下一頁、頁類型等，固定長度為38個字節(jié)。
Page Header(頁頭)：記錄頁的狀態(tài)信息，堆中記錄數(shù)、指向空閑列表的指針、已刪除記錄的字節(jié)數(shù)、最后插入的位置等，固定長度共56個字節(jié)。
Infimun+Supremum Records:在InnoDB存儲引擎中，每個數(shù)據(jù)頁中有兩個虛擬的行記錄，用來限定記錄的邊界。
Infimun記錄是比該頁中任何主鍵都要小的值，Supermum指比任何可能大的值還要大的值。這兩個值在頁創(chuàng)建時被建立，并且在任何情況下不會被刪除。在Compact行格式和Redundant行格式下，兩者占用的字節(jié)數(shù)各不相同。
User Records(用戶記錄，即行記錄):實現(xiàn)記錄的內(nèi)容。再次強(qiáng)調(diào)，InnoDB存儲引擎表總是B+村索引組織的。
Free Space(空閑空間)：指空閑空間，同樣也是個鏈表數(shù)據(jù)結(jié)構(gòu)。當(dāng)一條記錄被刪除后，該空間會被加入空閑鏈 表中。
Page Directory(頁目錄)：頁目錄存放了記錄的相對位置，并不是偏移量，有些時候這些記錄稱為Slots(槽)，InnoDB并不是每個記錄一個槽，槽是一個稀疏目錄，即一個槽中可能屬于多個記錄，最少屬于4條記錄，最多屬于8條記錄。需要牢記的是，B+樹索引本身并不能找到具體的一條記錄，B+樹索引能找到只是該記錄所在的頁。數(shù)據(jù)庫把頁載入內(nèi)存，然后通過Page Directory再進(jìn)行二叉查找。只不過二叉查找的時間復(fù)雜度低，同時內(nèi)存中的查找很快，因此通過忽略了這部分查找所用的時間。
File Trailer(文件結(jié)尾信息)：為了保證頁完整地寫入磁盤(如寫過程的磁盤損壞、機(jī)器宕機(jī)等)，固定長8個字節(jié)。視圖：Mysql中的視圖總是虛擬的表，本身不支持物化視圖。但是通過一些其他技巧(如觸發(fā)器)，同樣也可以實現(xiàn)一些簡單的物化視圖的功能。
分區(qū)：Mysql數(shù)據(jù)庫支持RANGE、LIST、HASH、KEY、COLUMNS分區(qū)，并且可以使用HASH或KEY來進(jìn)行子分區(qū)。
B+樹索引：B+樹的數(shù)據(jù)結(jié)構(gòu)相對較復(fù)雜，B代表的是balance最早是從平衡二叉樹演化而來，但B+樹并不是一個二叉樹，對其較詳細(xì)的介紹可以參見這篇文章：http://blog.csdn.net/v_JULY_v/article/details/6530142 由于B+樹索引的高扇出性，因此在數(shù)據(jù)庫中，B+樹的高度一般都在2~3層，也就對于查找某一鍵值的行記錄，最多只要2到3次IO,現(xiàn)在一般的磁盤每秒至少可以做100次IO，2~3次的IO意味著查詢時間只需0.02~0.03秒。
數(shù)據(jù)庫中的B+索引可以分為聚集索引(clustered index)和輔助聚集索引(secondary index),但其內(nèi)部都是B+樹的，即高度平衡的，葉子節(jié)點存放數(shù)據(jù)。
聚集索引：由于聚集索引是按照主鍵組織的，所以每一張表只能有一個聚集索引，每個數(shù)據(jù)頁都通過雙向鏈表進(jìn)行連接，葉子節(jié)點存放一整行的信息，所以查詢優(yōu)化器更傾向走聚集索引。此外，對于聚集索引的存儲是邏輯上連續(xù)的。所以，聚集索引對于主鍵的排序查找和范圍查找速度非?？?。
輔助索引：也叫非聚集索引，葉子節(jié)點不存全部數(shù)據(jù)，主要存鍵值及一個boomark(其實就是聚集索引的鍵)告訴InnoDB哪里可以找到與索引相對應(yīng)的行數(shù)據(jù)，如一個高度為3的輔助索引和一個高度為3的聚集索引，若根據(jù)輔助索引來查詢行記錄，一共需要6次IO。另外輔助索引可以有多個。
索引的使用原則：高選擇、取出表中的少部分?jǐn)?shù)據(jù)(也稱為唯一索引)。一般取出的數(shù)據(jù)量超過表中數(shù)據(jù)的20%，優(yōu)化器不會使用索引，而進(jìn)行全表掃描。如對于性別等字段是沒有意義的。
聯(lián)合索引：也稱復(fù)合索引，是在多列（>=2）上建立的索引。Innodb中的復(fù)合索引也是b+ tree結(jié)構(gòu)。索引的數(shù)據(jù)包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…使用復(fù)合索引要充分利用最左前綴原則，顧名思義，就是最左優(yōu)先。如創(chuàng)建索引ind_col1_col2(col1, col2)，那么在查詢where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引，但where col2=****是走不到索引的。在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁且過濾效果好的的一列放在最左邊。哈希索引：哈希算法也是比較常見的算法，mysql innoDB中使用了比較常見的鏈地址法進(jìn)行去重。此外上面已經(jīng)提及，innoDB中的hash是自適應(yīng)的，什么時候使用hash是系統(tǒng)決定的，無法進(jìn)行人工設(shè)置。
二分查找法：這個算法比較常見，這里就不多提及了。在InnoDB中，每頁Page Directory中的槽是按照主鍵的順序存放的，對于某一條具體記錄的查詢是通過對Page Directory進(jìn)行二分查找得到的。
InnoDB存儲引擎鎖的實現(xiàn)和Oracle非常類似，提供一致性的非鎖定讀、行級鎖支持、行級鎖沒有相關(guān)的開銷，可以同時得到并發(fā)性和一致性。
InnoDB存儲引擎實現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級鎖：共享鎖(S Lock)：允許事務(wù)讀一行數(shù)據(jù)；
排他鎖(X Lock)：允許事務(wù)刪除或者更新一行數(shù)據(jù)。當(dāng)一個事務(wù)已經(jīng)獲得了行r的共享鎖，那么另外的事務(wù)可以立即獲得行r的共享鎖，因為讀取沒有改變行r的數(shù)據(jù)，我們稱這種情況為鎖兼容。但如果有事務(wù)想獲得行r的排他鎖，則它必須等待事務(wù)釋放行r上的共享鎖————這種情況稱為鎖不兼容。
在InnoDB Plugin之前，只能通過SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令來查看當(dāng)前的數(shù)據(jù)庫請求，然后再判斷當(dāng)前事務(wù)中的鎖的情況。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架構(gòu)下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通過這三張表，可以更簡單地監(jiān)控當(dāng)前的事務(wù)并分析可能存在的鎖的問題。
INNODB_TRX由8個字段組成：
trx_id:InnoDB存儲引擎內(nèi)部唯一的事務(wù)ID
trx_state:當(dāng)前事務(wù)的狀態(tài)。
trx_started:事務(wù)的開始時間。
trx_requested_lock_id:等待事務(wù)的鎖ID。如trx_state的狀態(tài)為LOCK WAIT,那么該值代表當(dāng)前的等待之前事務(wù)占用鎖資源的ID.
若trx_state不是LOCK WAIT,則該值為NULL。
trx_wait_started:事務(wù)等待開始的時間。
trx_weight:事務(wù)的權(quán)重，反映了一個事務(wù)修改和鎖住的行數(shù)。在InnoDB存儲引擎中，當(dāng)發(fā)生死鎖需要回滾時，InnoDB存儲會選
擇該值最小的進(jìn)行回滾。
trx_mysql_thread_id:Mysql中的線程ID,SHOW PROCESSLIST顯示的結(jié)果。
trx_query:事務(wù)運行的sql語句。
通過select * from infomation_schema.INNODB_TRX;可查看INNODB_LOCKS表，該表由如下字段組成：
lock_id:鎖的ID。
lock_trx_id:事務(wù)ID。
lock_mode:鎖的模式。
lock_type:鎖的類型，表鎖還是行鎖。
lock_table:要加鎖的表。
lock_index:鎖的索引。
lock_space:InnoDB存儲引擎表空間的ID號。
lock_page:被鎖住的頁的數(shù)量。若是表鎖，則該值為NULL。
lock_rec:被鎖住的行的數(shù)量。若是表鎖，則該值為NULL。
lock_data:被鎖住的行的主鍵值。當(dāng)是表鎖時，該值為NULL。
通過select from information_schema.INNODB_LOCK;可查看
INNODBLOCKWAIT由4個字段組成：
requestingtrxid:申請鎖資源的事務(wù)ID。
requestinglockid:申請的鎖的ID。
blockingtrxid:阻塞的鎖的ID。
通過select from informationschema.INNODBLOCKWAITS;可查看。
一致性的非鎖定讀：InnoDB存儲引擎通過行多版本控制的方式來讀取當(dāng)前執(zhí)行時間數(shù)據(jù)庫中行的數(shù)據(jù)。如果讀取的行正在執(zhí)行Delete、update操作，這時讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB存儲引擎會去讀取行的一個快照數(shù)據(jù)。快照數(shù)據(jù)是指該行之前版本的數(shù)據(jù)，該實現(xiàn)是通過Undo段來實現(xiàn)。而Undo用來事務(wù)中回滾數(shù)據(jù)，因此快照本身是沒有額外開銷的。此外，快照數(shù)據(jù)是不需要上鎖的，因為沒有必要對歷史的數(shù)據(jù)進(jìn)行修改。一個行可能有不止一個快照數(shù)據(jù)，所以稱這種技術(shù)為行多版本技術(shù)。由此帶來并發(fā)控制，稱之為多版本并發(fā)控制(Multi VersionConcurrency Control, MVCC)。
事務(wù)的隔離級別：Read uncommitted、Read committed、Repeatable read、serializable。在Read Committed和Repeatable Read下，InnoDB存儲引擎使用非鎖定一致性讀。然而，對于快照的定義卻不同。在Read Committed事務(wù)隔離級別下，對于快照數(shù)據(jù)，非一致性讀總是讀取被鎖定行的最新一份快照數(shù)據(jù)。在Repeatable事務(wù)隔離級別下，對于快照數(shù)據(jù)，非一致性讀總是讀取事務(wù)開始時的行數(shù)據(jù)版本。
鎖的算法：
Record Lock:單行記錄上的鎖
Gap Lock:間隙鎖，鎖定一個范圍，但不包含記錄本身
Next-Key Lock:Gap Lock + Record Lock，鎖定一個范圍，并且鎖定記錄本身。更加詳細(xì)的介紹可以參見這篇blog,http://www.db110.com/?p=1848
鎖的問題：
丟失更新：經(jīng)典的數(shù)據(jù)庫問題，當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，會發(fā)生丟失更新問題。每個事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。
例:
事務(wù)A和事務(wù)B同時修改某行的值，
1.事務(wù)A將數(shù)值改為1并提交
2.事務(wù)B將數(shù)值改為2并提交。
這時數(shù)據(jù)的值為2，事務(wù)A所做的更新將會丟失。
解決辦法：事務(wù)并行變串行操作，對更新操作加排他鎖。
臟讀：一個事務(wù)讀到另一個事務(wù)未提交的更新數(shù)據(jù)，即讀到臟數(shù)據(jù)。
例:
1.Mary的原工資為1000, 財務(wù)人員將Mary的工資改為了8000(但未提交事務(wù))
2.Mary讀取自己的工資 ,發(fā)現(xiàn)自己的工資變?yōu)榱?000，歡天喜地！
3.而財務(wù)發(fā)現(xiàn)操作有誤，回滾了事務(wù),Mary的工資又變?yōu)榱?000, 像這樣,Mary記取的工資數(shù)8000是一個臟數(shù)據(jù)。
解決辦法：臟讀只有在事務(wù)隔離級別是Read Uncommitted的情況下才會出現(xiàn)，innoDB默認(rèn)隔離級別是Repeatable Read，所以生產(chǎn)環(huán)境下不會出現(xiàn)臟讀。
不可重復(fù)讀：在同一個事務(wù)中,多次讀取同一數(shù)據(jù),返回的結(jié)果有所不同。換句話說就是,后續(xù)讀取可以讀到另一個事務(wù)已提交的更新數(shù)據(jù)。相反"可重復(fù)讀"在同一事務(wù)多次讀取數(shù)據(jù)時,能夠保證所讀數(shù)據(jù)一樣,也就是后續(xù)讀取不能讀到另一事務(wù)已提交的更新數(shù)據(jù)。臟讀和不可重復(fù)讀的主要區(qū)別在于，臟讀是讀到未提交的數(shù)據(jù)，不可重復(fù)讀是讀到已提交的數(shù)據(jù)。
例:
1.在事務(wù)1中，Mary 讀取了自己的工資為1000,操作并沒有完成
2.在事務(wù)2中，這時財務(wù)人員修改了Mary的工資為2000,并提交了事務(wù).
3.在事務(wù)1中，Mary 再次讀取自己的工資時，工資變?yōu)榱?000
解決辦法：讀到已提交的數(shù)據(jù)，一般數(shù)據(jù)庫是可接受的，因此事務(wù)隔離級別一般設(shè)為Read Committed。Mysql InnoDB通過Next-Key Lock算法避免不可重復(fù)讀，默認(rèn)隔離級別為Repeatable Read。
事務(wù)的四個特性：原子性、一致性、隔離性、持久性
隔離性通過鎖實現(xiàn)，原子性、一致性、持久性通過數(shù)據(jù)庫的redo和undo來完成。
重做日志記錄了事務(wù)的行為，通過redo實現(xiàn)，保證了事務(wù)的完整性，但事務(wù)有時還需要撤銷，這時就需要產(chǎn)生undo。undo和redo正好相反，對于數(shù)據(jù)庫進(jìn)行修改時，數(shù)據(jù)庫不但會產(chǎn)生redo，而且還會產(chǎn)生一定的undo，即使執(zhí)行的事務(wù)或語句由于某種原因失敗了，或者如果用一條rollback語句請求回滾，就可以用這些undo信息將數(shù)據(jù)回滾到修改之前的樣子。與redo不同的是,redo存放在重做日志文件中，undo存放在數(shù)據(jù)庫內(nèi)部的一個特殊段(segment)中，這稱為undo段(undo segment)，undo段位于共享表空間內(nèi)。還有一點重要的是，undo記錄的是與事務(wù)操作相反的邏輯操作，如insert undo 記錄一個delete，所以undo只是邏輯地將數(shù)據(jù)庫恢復(fù)成事務(wù)開始前的樣子。如:insert 10萬行的數(shù)據(jù)，可能導(dǎo)致表空間增大，回滾后，表空間不會減小回去。
MyISAM MYSQL5.5以前支持的默認(rèn)存儲引擎
MyISAM的引擎特點.：
1.不支持事務(wù)(事務(wù)是指邏輯上的一組操作,組成這組操作的各個單元,要么全成功,要么全失敗)
2.表級鎖定(更新時鎖整個表):其鎖定機(jī)制是表級鎖定,這樣雖然可以讓鎖定的實現(xiàn)成本很小但是同時大大降低了其并發(fā)的性能.
3.讀寫相互阻塞:不僅會在寫入的時候阻塞讀取,MyISAM還會在讀取的時候阻塞寫入,但是讀本身不會阻塞另外的讀.
4.只會緩存索引:MyISAM可以通過Key_buffer_size緩存索引,以大大提高訪問性能,減少磁盤IO,但是這個緩存區(qū)只會緩存索引,而不會緩存數(shù)據(jù).
5.讀取速度快,占用資源相對少.
6.在MySQL5.5.5以前是默認(rèn)的存儲引擎.
MyISAM引擎的適用的生產(chǎn)場景：
1.不需要支持事務(wù)的業(yè)務(wù)(例如轉(zhuǎn)賬,付款就不行)。
2.一般為讀數(shù)據(jù)比較多的應(yīng)用,讀寫都頻繁則不合適,會堵塞,讀多或者寫多單一性都可以.
3.讀寫并發(fā)訪問較少的業(yè)務(wù)(單獨讀或者單獨寫高并發(fā)可以,同時讀寫并發(fā)不行)(主要是鎖定機(jī)制問題,其實鎖定整個表,會有堵塞問題)
4.數(shù)據(jù)修改相對較少的業(yè)務(wù).
5.對數(shù)據(jù)一致性要求不是非常高的業(yè)務(wù).
6.硬件資源較差的機(jī)器可以使用MyISAM,一般為中小型網(wǎng)站.
總結(jié)：單一對數(shù)據(jù)庫的操作都可以使用MyISAM,即是盡量純讀,或者純寫(insert,update,delete)等.
MyISAM引擎調(diào)優(yōu)精要:
1.設(shè)置合適的索引(緩存機(jī)制).
2.調(diào)整讀寫優(yōu)先級,根據(jù)實際需求確保重要操作更有限執(zhí)行.
3.啟用延時插入(盡量批量插入,降低寫的頻率)
4.盡量順序操作,讓insert數(shù)據(jù)都寫入到尾部,減少阻塞.
5.分解大的時間長的操作,降低單個操作的阻塞時間.
6.降低并發(fā)數(shù)(減少對MySQL的訪問),某些高并發(fā)的場景可以通過應(yīng)用進(jìn)行排隊隊列機(jī)制.
7.對于靜態(tài)(更改不頻繁)的數(shù)據(jù)庫數(shù)據(jù),充分利用Query Cache或者memcached緩存服務(wù)極大可能的提高訪問效率.
8.MyISAM的count只有在全表掃描的時候才是最高效的,帶有條件的count都需要進(jìn)行試機(jī)的數(shù)據(jù)訪問.
9.也可以在做主從同步的時候主庫使用InnoDB,從庫使用MyISAM,進(jìn)行讀寫分離(不推薦使用,數(shù)據(jù)遷移和升級比較麻煩)。
重慶分公司
重慶分公司
