MySql讀寫分離

1、MySql讀寫分離1.MySQL主從復(fù)制入門首先，看一個圖：影響MySQL-A數(shù)據(jù)庫的操作，在數(shù)據(jù)庫執(zhí)行后，都會寫入本地的日志系統(tǒng)A中。 假設(shè)，實時的將變化了的日志系統(tǒng)中的數(shù)據(jù)庫事件操作，在MYSQL-A的3306端口，通過網(wǎng)絡(luò)發(fā)給MYSQL-B。MYSQL-B收到后，寫入本地日志系統(tǒng)B，然后一條條的將數(shù)據(jù)庫事件在數(shù)據(jù)庫中完成。 那么，MYSQL-A的變化，MYSQL-B也會變化，這樣就是所謂的MYSQL的復(fù)制，即MYSQL replication。在上面的模型中，MYSQL-A就是主服務(wù)器，即master，MYSQL-B就是從服務(wù)器，即slave。日志系統(tǒng)A，其實它是MYSQL的日志類型中的

2、二進(jìn)制日志，也就是專門用來保存修改數(shù)據(jù)庫表的所有動作，即bin log?！咀⒁釳YSQL會在執(zhí)行語句之后，釋放鎖之前，寫入二進(jìn)制日志，確保事務(wù)安全】日志系統(tǒng)B，并不是二進(jìn)制日志，由于它是從MYSQL-A的二進(jìn)制日志復(fù)制過來的，并不是自己的數(shù)據(jù)庫變化產(chǎn)生的，有點接力的感覺，稱為中繼日志，即relay log?？梢园l(fā)現(xiàn)，通過上面的機(jī)制，可以保證MYSQL-A和MYSQL-B的數(shù)據(jù)庫數(shù)據(jù)一致，但是時間上肯定有延遲，即MYSQL-B的數(shù)據(jù)是滯后的?！炯幢悴豢紤]什么網(wǎng)絡(luò)的因素，MYSQL-A的數(shù)據(jù)庫操作是可以并發(fā)的執(zhí)行的，但是MYSQL-B只能從relay log中讀一條，執(zhí)行下。因此MYSQL-A的寫

3、操作很頻繁，MYSQL-B很可能跟不上。】2.主從復(fù)制的幾種方式同步復(fù)制所謂的同步復(fù)制，意思是master的變化，必須等待slave-1,slave-2,.,slave-n完成后才能返回。這樣，顯然不可取，也不是MYSQL復(fù)制的默認(rèn)設(shè)置。比如，在WEB前端頁面上，用戶增加了條記錄，需要等待很長時間。異步復(fù)制如同AJAX請求一樣。master只需要完成自己的數(shù)據(jù)庫操作即可。至于slaves是否收到二進(jìn)制日志，是否完成操作，不用關(guān)心。MYSQL的默認(rèn)設(shè)置。半同步復(fù)制master只保證slaves中的一個操作成功，就返回，其他slave不管。這個功能，是由google為MYSQL引入的。3.主從復(fù)制

4、分析問題1：master的寫操作，slaves被動的進(jìn)行一樣的操作，保持?jǐn)?shù)據(jù)一致性，那么slave是否可以主動的進(jìn)行寫操作？假設(shè)slave可以主動的進(jìn)行寫操作，slave又無法通知master，這樣就導(dǎo)致了master和slave數(shù)據(jù)不一致了。因此slave不應(yīng)該進(jìn)行寫操作，至少是slave上涉及到復(fù)制的數(shù)據(jù)庫不可以寫。實際上，這里已經(jīng)揭示了讀寫分離的概念。問題2：主從復(fù)制中，可以有N個slave,可是這些slave又不能進(jìn)行寫操作，要他們干嘛？可以實現(xiàn)數(shù)據(jù)備份。類似于高可用的功能，一旦master掛了，可以讓slave頂上去，同時slave提升為master。 異地容災(zāi)，比如master在北

5、京，地震掛了，那么在上海的slave還可以繼續(xù)。主要用于實現(xiàn)scale out,分擔(dān)負(fù)載,可以將讀的任務(wù)分散到slaves上。【很可能的情況是，一個系統(tǒng)的讀操作遠(yuǎn)遠(yuǎn)多于寫操作，因此寫操作發(fā)向master，讀操作發(fā)向slaves進(jìn)行操作】問題3：主從復(fù)制中有master,slave1,slave2,.等等這么多MYSQL數(shù)據(jù)庫，那比如一個JAVA WEB應(yīng)用到底應(yīng)該連接哪個數(shù)據(jù)庫?當(dāng)然，我們在應(yīng)用程序中可以這樣，insert/delete/update這些更新數(shù)據(jù)庫的操作，用connection(for master)進(jìn)行操作，select用connection(for slaves)進(jìn)行操作。

6、那我們的應(yīng)用程序還要完成怎么從slaves選擇一個來執(zhí)行select，例如簡單的輪循算法。這樣的話，相當(dāng)于應(yīng)用程序完成了SQL語句的路由，而且與MYSQL的主從復(fù)制架構(gòu)非常關(guān)聯(lián)，一旦master掛了，某些slave掛了，那么應(yīng)用程序就要修改了。能不能讓應(yīng)用程序與MYSQL的主從復(fù)制架構(gòu)沒有什么太多關(guān)系呢？可以看下面的圖：找一個組件，application program只需要與它打交道，用它來完成MYSQL的代理，實現(xiàn)SQL語句的路由。mysql proxy并不負(fù)責(zé)，怎么從眾多的slaves挑一個？可以交給另一個組件(比如haproxy)來完成。這就是所謂的MYSQL READ WRITE S

7、PLITE，MYSQL的讀寫分離。問題4：如果mysql proxy , direct , master他們中的某些掛了怎么辦？總統(tǒng)一般都會弄個副總統(tǒng)，以防不測。同樣的，可以給這些關(guān)鍵的節(jié)點來個備份。問題5：當(dāng)master的二進(jìn)制日志每產(chǎn)生一個事件，都需要發(fā)往slave，如果我們有N個slave,那是發(fā)N次，還是只發(fā)一次？如果只發(fā)一次，發(fā)給了slave-1，那slave-2,slave-3,.它們怎么辦？顯然，應(yīng)該發(fā)N次。實際上，在MYSQL master內(nèi)部，維護(hù)N個線程，每一個線程負(fù)責(zé)將二進(jìn)制日志文件發(fā)往對應(yīng)的slave。master既要負(fù)責(zé)寫操作，還的維護(hù)N個線程，負(fù)擔(dān)會很重。可以這樣，

8、slave-1是master的從，slave-1又是slave-2,slave-3,.的主，同時slave-1不再負(fù)責(zé)select。slave-1將master的復(fù)制線程的負(fù)擔(dān)，轉(zhuǎn)移到自己的身上。這就是所謂的多級復(fù)制的概念。問題6：當(dāng)一個select發(fā)往mysql proxy，可能這次由slave-2響應(yīng)，下次由slave-3響應(yīng)，這樣的話，就無法利用查詢緩存了。應(yīng)該找一個共享式的緩存，比如memcache來解決。將slave-2,slave-3,.這些查詢的結(jié)果都緩存至mamcache中。問題7：隨著應(yīng)用的日益增長，讀操作很多，我們可以擴(kuò)展slave，但是如果master滿足不了寫操作了，怎

9、么辦呢？scale on ?更好的服務(wù)器？ 沒有最好的，只有更好的，太貴了。scale out ? 主從復(fù)制架構(gòu)已經(jīng)滿足不了。可以分庫【垂直拆分】，分表【水平拆分】。MySql查詢緩存1.緩存機(jī)制MySQL緩存機(jī)制簡單的說就是緩存sql文本及查詢結(jié)果，如果運行相同的sql，服務(wù)器直接從緩存中取到結(jié)果，而不需要再去解析和執(zhí)行sql。如果表更改 了，那么使用這個表的所有緩沖查詢將不再有效，查詢緩存值的相關(guān)條目被清空。更改指的是表中任何數(shù)據(jù)或是結(jié)構(gòu)的改變，包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包

10、括那些映射到改變了的表的使用MERGE表的查詢。顯然，這對于頻繁更新的表，查詢緩存是不適合的，而對于一些不常改變數(shù)據(jù)且有 大量相同sql查詢的表，查詢緩存會節(jié)約很大的性能。查詢必須是完全相同的(逐字節(jié)相同)才能夠被認(rèn)為是相同的。另外，同樣的查詢字符串由于其它原因可能認(rèn)為是不同的。使用不同的數(shù)據(jù)庫、不同的協(xié)議版本或者不同 默認(rèn)字符集的查詢被認(rèn)為是不同的查詢并且分別進(jìn)行緩存。下面sql查詢緩存認(rèn)為是不同的：1. SELECT * FROM tbl_name2. Select * from tbl_name2.MySQL 檢查緩存命中的規(guī)則1. 在檢查緩存的時候，MySQL 不會對語句進(jìn)行解析、正則

11、化或者參數(shù)化，它精確地使用客戶端傳來的查詢語句和其他數(shù)據(jù)。只要字符大小寫、空格或者注釋有一點點不同，查詢緩存就認(rèn)為這是一個不同的查詢2. 查詢緩存不會存儲有不確定結(jié)果的查詢。因此，任何一個包含不確定函數(shù)（比如NOW()或CURRENT_DATE()）的查詢不會被緩存。同樣地，CURRENT_USER()或CONNECTION_ID()這些由不同用戶執(zhí)行，將會產(chǎn)生不同的結(jié)果的查詢也不會被緩存。事實上，查詢緩存不會緩存引用了用戶自定義函數(shù)、存儲函數(shù)、用戶自定義變量、臨時表、mysql 數(shù)據(jù)庫中的表或者任何一個有列級權(quán)限的表的查詢3. 查詢必須是完全相同的(逐字節(jié)相同)才能夠被認(rèn)為是相同的。另外，同

12、樣的查詢字符串由于其它原因可能認(rèn)為是不同的。使用不同的數(shù)據(jù)庫、不同的協(xié)議版本或者不同 默認(rèn)字符集的查詢被認(rèn)為是不同的查詢并且分別進(jìn)行緩存。3.開啟查詢緩存的開銷1. 讀取查詢在開始之前必須要檢查緩存。2. 如果查詢是可以被緩存的，但是不在緩存中，那么在產(chǎn)生結(jié)果之后進(jìn)行保存會帶來一些額外的開銷。寫入數(shù)據(jù)的查詢也會有額外的開銷，因為它必須使緩存中相關(guān)的數(shù)據(jù)表失效。這些開銷相對來說較小，所以查詢緩存還是很有好處的。但是，稍后你會看到，額外的開銷有可能也會增加。從緩存中受益最多的查詢可能是需要很多資源來產(chǎn)生結(jié)果，但是不需要很多空間來保存的類型。所以用于存儲、返回和失效的代價都較小。聚集查詢，比如從大表

13、中利用COUNT()產(chǎn)生較小的結(jié)果，就符合這個范疇。4.Msyql緩存狀態(tài)的查看have_query_cache 的值如果是yes表示開啟緩存 Binlog_cache_size:默認(rèn)大小是32768即32KMax_binlog_cache_size: 默認(rèn)值是18446744073709547520，這個值很大，夠我們使用的了。此參數(shù)和binlog_cache_size相對應(yīng)，代表binlog所能使用的cache最大使用大小。如果系統(tǒng)中事務(wù)過多，而此參數(shù)值設(shè)置有小，則會報錯。query_cache_limit：允許 Cache 的單條 Query 結(jié)果集的最大容量，默認(rèn)是1MB，超過此參數(shù)設(shè)

14、置的 Query 結(jié)果集將不會被 Cachequery_cache_min_res_unit：設(shè)置 Query Cache 中每次分配內(nèi)存的最小空間大小，也就是每個 Query 的 Cache 最小占用的內(nèi)存空間大小query_cache_size：設(shè)置 Query Cache 所使用的內(nèi)存大小，默認(rèn)值為0，大小必須是1024的整數(shù)倍，如果不是整數(shù)倍，MySQL 會自動調(diào)整降低最小量以達(dá)到1024的倍數(shù)query_cache_type：控制 Query Cache 功能的開關(guān)，可以設(shè)置為0(OFF),1(ON)和2(DEMAND)三種，意義分別如下：0(OFF)：關(guān)閉 Query Cache

15、功能，任何情況下都不會使用 Query Cache1(ON)：開啟 Query Cache 功能，但是當(dāng) SELECT 語句中使用的 SQL_NO_CACHE 提示后，將不使用Query Cache2(DEMAND)：開啟 Query Cache 功能，但是只有當(dāng) SELECT 語句中使用了 SQL_CACHE 提示后，才使用 Query Cachequery_cache_wlock_invalidate：控制當(dāng)有寫鎖定發(fā)生在表上的時刻是否先失效該表相關(guān)的 Query Cache，如果設(shè)置為 1(TRUE)，則在寫鎖定的同時將失效該表相關(guān)的所有 Query Cache，如果設(shè)置為0(FALSE

16、)則在鎖定時刻仍然允許讀取該表相關(guān)的 Query Cache。檢查查詢緩存使用情況此時的查詢緩存命中率：175/（175+4）=97%；由于個人的測試數(shù)據(jù)庫，查詢較少，更行更少，命中率頗高。嘿嘿MySQL 提供了一系列的 Global Status來記錄 Query Cache的當(dāng)前狀態(tài)，具體如下： Qcache_free_blocks：目前還處于空閑狀態(tài)的 Query Cache中內(nèi)存 Block數(shù)目 Qcache_free_memory：目前還處于空閑狀態(tài)的 Query Cache內(nèi)存總量Qcache_hits：Query Cache命中次數(shù)Qcache_inserts：向 Query C

17、ache中插入新的Query Cache的次數(shù)，也就是沒有命中的次數(shù)Qcache_lowmem_prunes：當(dāng) Query Cache內(nèi)存容量不夠，需要從中刪除老的Query Cache以給新的 Cache 對象使用的次數(shù)Qcache_not_cached：沒有被 Cache的SQL數(shù)，包括無法被 Cache的 SQL 以及由于query_cache_type設(shè)置的不會被Cache的 SQLQcache_queries_in_cache：目前在 Query Cache中的SQL數(shù)量Qcache_total_blocks：Query Cache中總的Block數(shù)量 清空緩存之前的Qcache_

18、hits Qcache_inserts均被清空MySql安全性1.Sql注入SQL注入是攻擊者通過把惡意SQL命令插入到Web表單的輸入域或頁面請求的查詢字符串中，來達(dá)到欺騙服務(wù)器執(zhí)行惡意的SQL命令的一種攻擊方式。利用SQL注入漏洞，攻擊者可以操縱數(shù)據(jù)庫的數(shù)據(jù)（如得到數(shù)據(jù)庫中的機(jī)密數(shù)據(jù)、隨意更改數(shù)據(jù)庫中的數(shù)據(jù)、刪除數(shù)據(jù)庫等等），在得到一定權(quán)限后還可以掛馬，甚至得到整臺服務(wù)器的管理員權(quán)限。由于SQL注入是通過網(wǎng)站正常端口（通常為80端口）來提交惡意SQL語句，表面上看起來和正常訪問網(wǎng)站沒有區(qū)別，如果不仔細(xì)查看WEB日志很難發(fā)現(xiàn)此類攻擊，隱蔽性非常高。一旦程序出現(xiàn)SQL注入漏洞，危害相當(dāng)大，所以

19、我們對此應(yīng)該給予足夠的重視。2.Sql注入原理SQL注入的本質(zhì)是惡意攻擊者將SQL代碼插入或添加到程序的參數(shù)中，而程序并沒有對傳入的參數(shù)進(jìn)行正確處理，導(dǎo)致參數(shù)中的數(shù)據(jù)會被當(dāng)做代碼來執(zhí)行，并最終將執(zhí)行結(jié)果返回給攻擊者。3防御 使用預(yù)編譯語句，綁定變量。對傳入的參數(shù)進(jìn)行驗證，確保符合應(yīng)用中定義的標(biāo)準(zhǔn)。MySql優(yōu)化 最常見的系統(tǒng)瓶頸有以下幾種：1. 磁盤搜索。它慢慢地在磁盤中搜索數(shù)據(jù)塊。對現(xiàn)代磁盤來說，平時的搜索時間基本上小于10毫秒，因此理論上每秒鐘可以做100次磁盤搜索。這個時間對于全新的新磁盤來說提高的不多，并且對于只有一個表的情況也是如此。加快搜索時間的方法是將數(shù)據(jù)分開存放到多個磁盤中。2

20、. 磁盤讀/寫。當(dāng)磁盤在正確的位置上時，就需要讀取數(shù)據(jù)。對現(xiàn)代磁盤來說，磁盤吞吐量至少是10-20MB/秒。這比磁盤搜索的優(yōu)化更容易，因為可以從多個媒介中并行地讀取數(shù)據(jù)。3. CPU周期。數(shù)據(jù)存儲在主內(nèi)存中（或者它已經(jīng)在主內(nèi)存中了），這就需要處理這些數(shù)據(jù)以得到想要的結(jié)果。最常見的瓶頸是調(diào)度內(nèi)存資源對數(shù)據(jù)集進(jìn)行對比，如果結(jié)果集不大則還好。4. 內(nèi)存帶寬。當(dāng)CPU要將更多的數(shù)據(jù)存放在CPU緩存中時，主內(nèi)存的帶寬就是瓶頸了。在大多數(shù)系統(tǒng)中，這不是常見的瓶頸，不過也是要注意的一個因素。三個方面：標(biāo)準(zhǔn)化，數(shù)據(jù)類型，索引標(biāo)準(zhǔn)化標(biāo)準(zhǔn)化是在數(shù)據(jù)庫中組織數(shù)據(jù)的過程。其中包括，根據(jù)設(shè)計規(guī)則創(chuàng)建表并在這些表間建立關(guān)

21、系；通過取消冗余度與不一致相關(guān)性，該設(shè)計規(guī)則可以同時保護(hù)數(shù)據(jù)并提高數(shù)據(jù)的靈活性。通常數(shù)據(jù)庫標(biāo)準(zhǔn)化是讓數(shù)據(jù)庫設(shè)計符合某一級別的范式，通常滿足第三范式即可。也有第四范式（也稱為 Boyce Codd范式，BCNF)）與第五范式存在，但是在實際設(shè)計中很少考慮。忽視這些規(guī)則可能使得數(shù)據(jù)庫的設(shè)計不太完美，但這不應(yīng)影響功能。 標(biāo)準(zhǔn)化的特點：1) 所有的“對象”都在它自己的table中，沒有冗余。2) 數(shù)據(jù)庫通常由E-R圖生成。3) 簡潔，更新屬性通常只需要更新很少的記錄。4) Join操作比較耗時。5) Select，sort優(yōu)化措施比較少。6) 適用于OLTP應(yīng)用。非標(biāo)準(zhǔn)化的特點：1) 在一張表中存儲很

22、多數(shù)據(jù)，數(shù)據(jù)冗余。2) 更新數(shù)據(jù)開銷很大，更新一個屬性可能會更新很多表，很多記錄。3) 在刪除數(shù)據(jù)是有可能丟失數(shù)據(jù)。4) Select，order有很多優(yōu)化的選擇。5) 適用于DSS應(yīng)用。標(biāo)準(zhǔn)化和非標(biāo)準(zhǔn)化都有各自的優(yōu)缺點，通常在一個數(shù)據(jù)庫設(shè)計中可以混合使用，一部分表格標(biāo)準(zhǔn)化，一部分表格保留一些冗余數(shù)據(jù)：1) 對OLTP使用標(biāo)準(zhǔn)化，對DSS使用非標(biāo)準(zhǔn)化2) 使用物化視圖。MySQL不直接支持該數(shù)據(jù)庫特性，但是可以用MyISAM表代替。3) 冗余一些數(shù)據(jù)在表格中，例如將ref_id和name存在同一張表中。但是要注意更新問題。4) 對于一些簡單的對象，直接使用value作為建。例如IP addre

23、ss等5) Reference by PRIMARY/UNIQUE KEY。MySQL可以優(yōu)化這種操作.數(shù)據(jù)類型 最基本的優(yōu)化之一就是使表在磁盤上占據(jù)的空間盡可能小。這能帶來性能非常大的提升，因為數(shù)據(jù)小，磁盤讀入較快，并且在查詢過程中表內(nèi)容被處理所占用的內(nèi)存更少。同時，在更小的列上建索引，索引也會占用更少的資源?？梢允褂孟旅娴募夹g(shù)可以使表的性能更好并且使存儲空間最?。?) 使用正確合適的類型，不要將數(shù)字存儲為字符串。2) 盡可能地使用最有效(最小)的數(shù)據(jù)類型。MySQL有很多節(jié)省磁盤空間和內(nèi)存的專業(yè)化類型。3) 盡可能使用較小的整數(shù)類型使表更小。例如，MEDIUMINT經(jīng)常比INT好一些，因為MEDIUMINT列使用的空間要少25%。4) 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節(jié)省一位。注意如果在應(yīng)用