MySql讀寫(xiě)分離

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16、)則在鎖定時(shí)刻仍然允許讀取該表相關(guān)的 Query Cache。檢查查詢緩存使用情況此時(shí)的查詢緩存命中率：175/（175+4）=97%；由于個(gè)人的測(cè)試數(shù)據(jù)庫(kù)，查詢較少，更行更少，命中率頗高。嘿嘿M(fèi)ySQL 提供了一系列的 Global Status來(lái)記錄 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ù)，也就是沒(méi)有命中的次數(shù)Qcache_lowmem_prunes：當(dāng) Query Cache內(nèi)存容量不夠，需要從中刪除老的Query Cache以給新的 Cache 對(duì)象使用的次數(shù)Qcache_not_cached：沒(méi)有被 Cache的SQL數(shù)，包括無(wú)法被 Cache的 SQL 以及由于query_cache_type設(shè)置的不會(huì)被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注入是攻擊者通過(guò)把惡意SQL命令插入到Web表單的輸入域或頁(yè)面請(qǐng)求的查詢字符串中，來(lái)達(dá)到欺騙服務(wù)器執(zhí)行惡意的SQL命令的一種攻擊方式。利用SQL注入漏洞，攻擊者可以操縱數(shù)據(jù)庫(kù)的數(shù)據(jù)（如得到數(shù)據(jù)庫(kù)中的機(jī)密數(shù)據(jù)、隨意更改數(shù)據(jù)庫(kù)中的數(shù)據(jù)、刪除數(shù)據(jù)庫(kù)等等），在得到一定權(quán)限后還可以掛馬，甚至得到整臺(tái)服務(wù)器的管理員權(quán)限。由于SQL注入是通過(guò)網(wǎng)站正常端口（通常為80端口）來(lái)提交惡意SQL語(yǔ)句，表面上看起來(lái)和正常訪問(wèn)網(wǎng)站沒(méi)有區(qū)別，如果不仔細(xì)查看WEB日志很難發(fā)現(xiàn)此類攻擊，隱蔽性非常高。一旦程序出現(xiàn)SQL注入漏洞，危害相當(dāng)大，所以

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

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

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

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

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