MySQL技術(shù)原理介紹

1、MySQL技術(shù)原理介紹MySQL的疑問1、mysam innodb引擎比較，什么是引擎 原理.詳細(xì)說明.2、復(fù)制問題：日志，復(fù)制過程和機制。3、備份問題：動手做下數(shù)據(jù)庫備份、腳本、有哪些備份方法.4、性能監(jiān)控： a、打開慢日志。做分析 b、主從復(fù)制狀態(tài)，監(jiān)控文檔 c、ITSM接入監(jiān)控(深入研究。原理 機制)內(nèi)容概要1. MySQL體系架構(gòu)2. MySQL處理流程3. MySQL引擎及引擎機制4. Myisam、Innodb引擎對比5. MySQL日志系統(tǒng)6. MySQL Innodb日志機制深入分析7. MySQL主從復(fù)制原理8. MySQL高級備份9. MySQL關(guān)鍵代碼實現(xiàn)分析MySQL體

2、系架構(gòu)MySQL結(jié)構(gòu)層次MySQL的架構(gòu)是“數(shù)據(jù)庫管理”和“數(shù)據(jù)管理”分離，即MySQL Server+Storage Engine的模式。MySQL Server是直接與Client交互的一層，它負(fù)責(zé)管理連接線程，解析SQL生成執(zhí)行計劃，管理和實現(xiàn)視圖、觸發(fā)器、存儲過程等這些與具體數(shù)據(jù)操作管理無關(guān)的事情，通過調(diào)用Handler API讓存儲引擎去操作具體的數(shù)據(jù)。Storage Engine通過繼承實現(xiàn)Handler API的函數(shù)，負(fù)責(zé)直接與數(shù)據(jù)交互，數(shù)據(jù)存取實現(xiàn)（必須實現(xiàn)），事務(wù)實現(xiàn)（可選），索引實現(xiàn)（可選），數(shù)據(jù)緩存實現(xiàn)（可選）。Mysql是由SQL接口，解析器，優(yōu)化器，緩存，和存儲引擎組

3、成的。1 Connectors：指的是不同語言中與SQL的交互。2 Management Serveices & Utilities： 系統(tǒng)管理和控制工具3 Connection Pool: 連接池。管理緩沖用戶連接，線程處理等需要緩存的需求4 SQL Interface: SQL接口。接受用戶的SQL命令，并且返回用戶需要查詢的結(jié)果。比如select from就是調(diào)用SQL Interface5 Parser: 解析器。SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現(xiàn)的，是一個很長的腳本。主要功能：a.將SQL語句分解成數(shù)據(jù)結(jié)構(gòu)，并將這個結(jié)構(gòu)傳遞到后續(xù)步驟，以

4、后SQL語句的傳遞和處理就是基于這個結(jié)構(gòu)的 ;b.如果在分解構(gòu)成中遇到錯誤，那么就說明這個sql語句是不合理的6 Optimizer: 查詢優(yōu)化器。SQL語句在查詢之前會使用查詢優(yōu)化器對查詢進行優(yōu)化。他使用的是“選取-投影-聯(lián)接”策略進行查詢。 用一個例子就可以理解： select uid,name from user where gender = 1; 這個select 查詢先根據(jù)where 語句進行選取，而不是先將表全部查詢出來以后再進行g(shù)ender過濾 這個select查詢先根據(jù)uid和name進行屬性投影，而不是將屬性全部取出以后再進行過濾 將這兩個查詢條件聯(lián)接起來生成最終查詢結(jié)果7

5、Cache和Buffer： 查詢緩存。如果查詢緩存有命中的查詢結(jié)果，查詢語句就可以直接去查詢緩存中取數(shù)據(jù)。這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權(quán)限緩存等MySQL體系架構(gòu)MySQL通過HandlerSocket插件提供了API訪問接口.HandlerSocket是在MySQL的內(nèi)部組件，以MySQL Daemon Plugin的形式提供類似NoSQL的網(wǎng)絡(luò)服務(wù)，它并不直接處理數(shù)據(jù)，只是偵聽配置好的某個端口方式，接收采用NoSQL/API的通訊協(xié)議，然后通過MySQL內(nèi)部的Handler API來調(diào)用存儲引擎（例如InnoDB）處理數(shù)據(jù)MySQL體系架構(gòu)MySQ

6、L處理流程當(dāng)客戶端鏈接上mysql服務(wù)端時，系統(tǒng)為其分配一個鏈接描述符thd，用以描述客戶端的所有信息，將作為參數(shù)在各個模塊之間傳遞。一個典型的客戶端查詢在MySQL的主要模塊之間的調(diào)用關(guān)系如圖所示：MySQL處理流程Mysql存儲引擎機制MySQL提供一個抽象層，允許不同的存儲應(yīng)使用相同的API對表進行訪問。該接口通過一個被稱為Handler的抽象類來實現(xiàn)，該處理器提供了一些可實現(xiàn)基本操作的方法，入打開和關(guān)閉表，連續(xù)掃描記錄，按照鍵值檢索記錄，存儲記錄以及刪除記錄。每個存儲引擎都執(zhí)行處理器的一個子類以實現(xiàn)接口方法，以便將處理器操作轉(zhuǎn)化為特定存儲引擎的此層次存儲/檢索API調(diào)用MySQL的主要

7、存儲引擎有：MyISAM(非事務(wù)引擎)/InnoDB(事務(wù)引擎)/Archive(歸檔引擎)/Memory(內(nèi)存引擎)/NDB(集群引擎)、MyISAM： MyISAM是MySQL的ISAM擴展格式和缺省的數(shù)據(jù)庫引擎。除了提供ISAM里所沒有的索引和字段管理的大量功能，MyISAM還使用一種表格鎖定的機制，來優(yōu)化多個并發(fā)的讀寫操作，其代價是你需要經(jīng)常運行OPTIMIZE TABLE命令，來恢復(fù)被更新機制所浪費的空間。MyISAM還有一些有用的擴展，例如用來修復(fù)數(shù)據(jù)庫文件的MyISAMCHK工具和用來恢復(fù)浪費空間的 MyISAMPACK工具。MYISAM強調(diào)了快速讀取操作，這可能就是為什么MyS

8、QL受到了WEB開發(fā)如此青睞的主要原因：在WEB開發(fā)中你所進行的大量數(shù)據(jù)操作都是讀取操作。所以，大多數(shù)虛擬主機提供商和INTERNET平臺提供商只允許使用MYISAM格式。MyISAM格式的一個重要缺陷就是不能在表損壞后恢復(fù)數(shù)據(jù)。InnoDB：MySQL 官方對InnoDB是這樣解釋的：InnoDB給MySQL提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）存儲引擎。InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風(fēng)格一致的非鎖定讀，這些特色增加了多用戶部署和性能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支持

9、FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。Myisam、innodb引擎對比MYISAMinnodb構(gòu)成上的區(qū)別：每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。 .frm文件存儲表定義。 數(shù)據(jù)文件的擴展名為.MYD (MYData)。 索引文件的擴展名是.MYI (MYIndex)。基于磁盤的資源是InnoDB表空間數(shù)據(jù)文件和它的日志文件，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB事務(wù)處理上方面:MyISAM類型的表強調(diào)的是性

10、能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持;如果執(zhí)行大量的SELECT，MyISAM是更好的選擇 InnoDB提供事務(wù)支持事務(wù)，外部鍵等高級數(shù)據(jù)庫功能;如果數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應(yīng)該使用InnoDB表表的具體行數(shù)select count(*) from table,MyISAM只要簡單的讀出保存好的行數(shù)，注意的是，當(dāng)count(*)語句包含 where條件時，兩種表的操作是一樣的InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行鎖表鎖提供行級鎖

11、定(locking on row level)，提供與 Oracle 類型一致的不加鎖讀取(non-locking read in SELECTs)，另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%”Myisam、innodb引擎對比基本上我們可以考慮使用InnoDB來替代我們的MyISAM引擎了，因為InnoDB自身很多良好的特點，比如事務(wù)支持、存儲過程、視圖、行級鎖定等等，在并發(fā)很多的情況下，InnoDB的表現(xiàn)要比MyI

12、SAM強很多。當(dāng)然如果不是很復(fù)雜的Web應(yīng)用，非關(guān)鍵應(yīng)用，還是可以繼續(xù)考慮MyISAM的，這個兩個引擎的選擇可以考慮具體的應(yīng)用場景。總結(jié)Myisam、innodb引擎對比mysql 的日志：1.error log 錯誤日志 2.general query log 普通日志3.binary log 二進制日志4.slow query log 慢查詢?nèi)罩?.innodb log innodb日志MySQL 日志系統(tǒng)error logerror log記錄mysqld啟動、關(guān)閉以及運行過程中的錯誤信息. error log對于錯誤診斷，故障修復(fù)以及發(fā)現(xiàn)安全隱患有非常重要的作用。mysqld啟動失敗時

13、，絕大部分時間可以通過error log確定出錯的原因，如參數(shù)的配置錯誤：格式，內(nèi)存配置過大，數(shù)據(jù)目錄路徑錯誤或權(quán)限問題；innodb在異常退出后的回滾，系統(tǒng)庫的丟失等；運行過程出現(xiàn)的錯誤和警告信息同樣可以用與分析數(shù)據(jù)庫的性能，數(shù)據(jù)表的損壞，復(fù)制過程中的錯誤，域名的解析等問題。同樣errorlog中記錄了數(shù)據(jù)庫關(guān)閉的過程之后正常。在解決故障過程中，第一件事情就是檢查error log，從中找到用于后續(xù)分析的信息。general query loggeneral log從客戶端連接開始，記錄所有的sql執(zhí)行記錄，直到連接關(guān)閉。較少開啟使用，對所有的操作記錄日志，必然帶來io負(fù)載，而且這個日志的增

14、長會非?？欤矔加么疟P空間。general log可以動態(tài)的開啟和關(guān)閉，一般會在需要的時候開啟一段時間用于分析。正常運行關(guān)閉general log。setglobalgenral_log=1/0;binary logbin log歸檔日志文件，記錄所有修改數(shù)據(jù)的sql語句。主要用于復(fù)制、數(shù)據(jù)備份恢復(fù)。slow query記錄慢查詢語句。慢查詢的檢查是非常有必要的。經(jīng)常由于sql語句導(dǎo)致負(fù)載高，響應(yīng)慢，長時間資源占用甚至是拖垮數(shù)據(jù)庫。而對慢查詢的優(yōu)化處理可以提升性能，降低系統(tǒng)負(fù)載以及避免故障的產(chǎn)生。參數(shù)： log-slow-queries開啟/關(guān)閉慢查詢?nèi)罩?long_query_time慢查

15、詢記錄時間log-queries-not-using-indexs對未使用索引的查詢記錄到slow log慢查詢的分析工具有自帶的mysqldumpslow，mysqlsla,pt-query-diges等，各有所長，主要作用都是對慢查詢?nèi)罩具M行統(tǒng)計分析和排序，提供更好的分析結(jié)果。innodb的日志分為redo log（日志文件組ib_logfile*）和undo log（共享表空間中ibdata*）。redo log重做日志用與數(shù)據(jù)的恢復(fù)以及提高io能力。undo log撤銷日志用于事務(wù)的回滾。slow query loginnodb logUndo Log 是為了實現(xiàn)事務(wù)的原子性，在MyS

16、QL數(shù)據(jù)庫InnoDB 存儲引擎中，還用Undo Log來實現(xiàn)多版本并發(fā)控制.MySQL Innodb日志機制深入分析原理為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個地方（這個存儲數(shù)據(jù)備份的地方稱為Undo Log）。然后進行數(shù)據(jù)的修改。如果出現(xiàn)了錯誤或者用戶執(zhí)行了ROLLBACK語句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)。除了可以保證事務(wù)的原子性，Undo Log也可以用來輔助完成事務(wù)的持久化。為了保證持久性，數(shù)據(jù)庫系統(tǒng)會將修改后的數(shù)據(jù)完全的記錄到持久的存儲上。- - - 用Undo Log實現(xiàn)原子性和持久化的事務(wù)的簡化過程- - - -假設(shè)有

17、A、B兩個數(shù)據(jù)，值分別為1,2。A.事務(wù)開始.B.記錄A=1到undo log.C.修改A=3.D.記錄B=2到undo log.E.修改B=4.F.將undo log寫到磁盤。G.將數(shù)據(jù)寫到磁盤。H.事務(wù)提交這里有一個隱含的前提條件：數(shù)據(jù)都是先讀到內(nèi)存中，然后修改內(nèi)存中的數(shù)據(jù)，最后將數(shù)據(jù)寫回磁盤。之所以能同時保證原子性和持久化，是因為以下特點：A. 更新數(shù)據(jù)前記錄Undo log。日志先行原則.B. 為了保證持久性，必須將數(shù)據(jù)在事務(wù)提交前寫到磁盤。只要事務(wù)成功提交，數(shù)據(jù)必然已經(jīng)持久化。C. Undo log必須先于數(shù)據(jù)持久化到磁盤。如果在G,H之間系統(tǒng)崩潰，undo log是完整的，可以用來

18、回滾事務(wù)。D. 如果在A-F之間系統(tǒng)崩潰,因為數(shù)據(jù)沒有持久化到磁盤。所以磁盤上的數(shù)據(jù)還是保持在事務(wù)開始前的狀態(tài)。缺陷：每個事務(wù)提交前將數(shù)據(jù)和Undo Log寫入磁盤，這樣會導(dǎo)致大量的磁盤IO，因此性能很低。如果能夠?qū)?shù)據(jù)緩存一段時間，就能減少IO提高性能。但是這樣就會喪失事務(wù)的持久性。因此引入了另外一種機制來實現(xiàn)持久化，即RedoLog, ,在默認(rèn)情況下，在InnoDB存儲引擎的數(shù)據(jù)目錄下會有兩個名為ib_logfile0和ib_logfile1的文件,在MySQL官方手冊中將其稱為InnoDB存儲引擎的日志文件RedoLog-原理和UndoLog相反，RedoLog記錄的是新數(shù)據(jù)的備份。在事

19、務(wù)提交前，只要將RedoLog持久化即可，不需要將數(shù)據(jù)持久化。當(dāng)系統(tǒng)崩潰時，雖然數(shù)據(jù)沒有持久化，但是RedoLog已經(jīng)持久化。系統(tǒng)可以根據(jù)RedoLog的內(nèi)容，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)。Undo+Redo事務(wù)的簡化過程假設(shè)有A、B兩個數(shù)據(jù)，值分別為1,2.A.事務(wù)開始.B.記錄A=1到undolog.C.修改A=3.D.記錄A=3到redolog.E.記錄B=2到undolog.F.修改B=4.G.記錄B=4到redolog.H.將redolog寫入磁盤。I.事務(wù)提交MySQL Innodb日志機制深入分析Undo+Redo事務(wù)的特點A.為了保證持久性，必須在事務(wù)提交前將RedoLog持久化

20、。B.數(shù)據(jù)不需要在事務(wù)提交前寫入磁盤，而是緩存在內(nèi)存中。C.RedoLog保證事務(wù)的持久性。D.UndoLog保證事務(wù)的原子性。E.有一個隱含的特點，數(shù)據(jù)必須要晚于redolog寫入持久存儲。IO性能Undo+Redo的設(shè)計主要考慮的是提升IO性能。雖說通過緩存數(shù)據(jù)，減少了寫數(shù)據(jù)的IO.但是卻引入了新的IO，即寫RedoLog的IO。如果RedoLog的IO性能不好，就不能起到提高性能的目的。為了保證RedoLog能夠有比較好的IO性能，InnoDB的RedoLog的設(shè)計有以下幾個特點：A.盡量保持RedoLog存儲在一段連續(xù)的空間上。因此在系統(tǒng)第一次啟動時就會將日志文件的空間完全分配。以順序

21、追加的方式記錄RedoLog,通過順序IO來改善性能。B.批量寫入日志。日志并不是直接寫入文件，而是先寫入redologbuffer.當(dāng)需要將日志刷新到磁盤時(如事務(wù)提交),將許多日志一起寫入磁盤.C.并發(fā)的事務(wù)共享RedoLog的存儲空間，它們的RedoLog按語句的執(zhí)行順序，依次交替的記錄在一起，以減少日志占用的空間。例如,RedoLog中的記錄內(nèi)容可能是這樣的：記錄1:記錄2:記錄3:記錄4:記錄5:D.因為C的原因,當(dāng)一個事務(wù)將RedoLog寫入磁盤時，也會將其他未提交的事務(wù)的日志寫入磁盤。E.RedoLog上只進行順序追加的操作，當(dāng)一個事務(wù)需要回滾時，它的RedoLog記錄也不會從R

22、edoLog中刪除掉。MySQL Innodb日志機制深入分析每個InnoDB存儲引擎至少有1個重做日志文件組（group），每個文件組下至少有2個重做日志文件，如默認(rèn)的ib_logfile0和ib_logfile1。為了得到更高的可靠性，用戶可以設(shè)置多個的鏡像日志組（mirrored log groups），將不同的文件組放在不同的磁盤上，以此提高重做日志的高可用性。在日志組中每個重做日志文件的大小一致，并以循環(huán)寫入的方式運行。InnoDB存儲引擎先寫重做日志文件1，當(dāng)達(dá)到文件的最后時，會切換至重做日志文件2，再當(dāng)重做日志文件2也被寫滿時，會再切換到重做日志文件1中。下圖顯示了一個擁有3個重

23、做日志文件的重做日志文件組。下列參數(shù)影響著重做日志文件的屬性：innodb_log_file_size /指定每個重做日志文件的大小最多512GBinnodb_log_files_in_group /重做日志文件的數(shù)量，默認(rèn)為2innodb_mirrored_log_groups /日志鏡像文件組的數(shù)量，默認(rèn)為1表示只有一個日志文件組，沒有鏡像。若磁盤本身已經(jīng)做了高可用的方案，如磁盤陣列，那么可以不開啟重做日志鏡像的功能innodb_log_group_home_dir /日志文件組所在路徑，默認(rèn)為./，表示在MySQL的數(shù)據(jù)目錄下MySQL Innodb日志機制深入分析MySQL Innod

24、b日志機制深入分析innodb_flush_log_at_trx_commit /redolog日志刷盤策略，參數(shù)值 0、1、2Innodb的數(shù)據(jù)并不是實時寫盤的，為了避免宕機時數(shù)據(jù)丟失，保證數(shù)據(jù)的ACID屬性，Innodb至少要保證數(shù)據(jù)對應(yīng)的日志不能丟失。對于不同的情況，Innodb采取不同的對策：Innodb有日志刷盤機制，可以通過innodb_flush_log_at_trx_commit參數(shù)進行控制；innodb_flush_log_at_trx_commit=0 #每秒寫一次traxlog,并執(zhí)行fsync。在事務(wù)提交的時候，不會有任何動作，即mysqld進程的崩潰會導(dǎo)致上一秒鐘所有

25、事務(wù)數(shù)據(jù)的丟失.innodb_flush_log_at_trx_commit=1 #每次trax提交的時候?qū)懸淮蝨raxlog,并執(zhí)行fsync，默認(rèn)值1是為了ACID (atomicity, consistency, isolation, durability)原子性，一致性，隔離性和持久化的考慮。在系統(tǒng)崩潰的情況，可能會丟失最多一秒鐘的事務(wù)數(shù)據(jù).innodb_flush_log_at_trx_commit=2 #每次trax提交的時候?qū)懸淮蝨raxlog,不會執(zhí)行fsync，不寫入硬盤而是寫入系統(tǒng)緩存。日志仍然會每秒flush到硬盤。只有在操作系統(tǒng)崩潰或者系統(tǒng)掉電的情況下，上一秒鐘所有事務(wù)

26、數(shù)據(jù)才可能丟失MySQL Innodb日志機制深入分析MySQL Slow Log慢日志分析show variables 來查看變量設(shè)置用 show variables like long% 查看 long_query_time 值 默認(rèn)為10 秒我們oss統(tǒng)一規(guī)則上配置的是2秒，這樣 MySQL自動會把 slowlog 超過2秒的 查詢語句存放到 mysql庫 slow_log表中。這里我們的slow log文件很小，使用more less等命令就足夠了。如果slow log很大怎么辦？MySQL自帶的工具mysqldumpslow來可以解析慢日志MySQL Innodb日志機制深入分析My

27、SQL Slow Log慢日志分析對于 SQL 文本完全一致，只是變量不同的語句， mysqldumpslow 將會自動視為同一個語句進行統(tǒng)計，變量值用 N 來代替。這個統(tǒng)計結(jié)果將大大增加用戶閱讀慢查詢?nèi)罩镜男?，并迅速定位系統(tǒng)的 SQL 瓶頸。出現(xiàn)次數(shù)(Count),執(zhí)行最長時間(Time),累計總耗費時間(Time),等待鎖的時間(Lock),發(fā)送給客戶端的行總數(shù)(Rows),掃描的行總數(shù)(Rows),用戶以及sql語句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).MySQL Innodb日志機制深入分析MySQL Slow Log慢日志分析Coun

28、t: 1 Time=35.85s (35s) Lock=0.00s (0s) Rows=50.0 (50), urpurp6 select * from urp.urp_call_log order by N desc limit N, N分析：1表示這條sql語句執(zhí)行了1次,平均的執(zhí)行時間是35.8s,共計50行,總共的時間是35s.夠慢的.我們來看看grep -color -i desc limit tygzptdb1-slow.logselect * from urp.urp_call_log order by 1 desc limit 0, 50;接下來就開始用

29、explain優(yōu)化它:Explain select * from urp.urp_call_log order by 1 desc limit 0, 50;roottygzptdb1 mysql# mysqldumpslow tygzptdb1-slow.log explain顯示了mysql如何使用索引來處理select語句以及連接表?？梢詭椭x擇更好的索引和寫出更優(yōu)化的查詢語句。使用方法，在select語句前加上explain就可以了：select_typesimple: 簡單的 select （沒有使用 union或子查詢）primary: 最外層的 select。union: 第二層，

30、在select 之后使用了 union。table顯示這一行的數(shù)據(jù)是關(guān)于哪張表的type這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALLpossible_keys顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引?？梢詾橄嚓P(guān)的域從WHERE語句中選擇一個合適的語句key實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優(yōu)化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）

31、來強制MYSQL忽略索引key_len使用的索引的長度。在不損失精確性的情況下，長度越短越好ref顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)rowsMYSQL認(rèn)為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)Extra關(guān)于MYSQL如何解析查詢的額外信息。這里可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結(jié)果是檢索會很慢MySQL Innodb日志機制深入分析MySQL Slow Log慢日志分析MySQL Innodb日志機制深入分析MySQL Slow Log慢日志分析很顯然,type 是 ALL(從頭到尾全表掃描),即最壞的情

32、況。Extra 里還出現(xiàn)了 Using filesort(無法利用索引完成的排序操作稱為“文件排序”),也是最壞的情況。優(yōu)化是必須的。mysql select * from urp_call_log order by 1 desc limit 0, 50;50 rows in set (1 min 28.10 sec)開始創(chuàng)建第一個字段的索引mysql alter table urp_call_log add index callLog_calldesc(REQUEST_MSGID);再次執(zhí)行mysql select * from urp_call_log order by 1 desc li

33、mit 0, 50;可以看到通過慢日志再用explain分析語句再進行優(yōu)化，提高sql語句的執(zhí)行效率Explain select * from urp.urp_call_log order by 1 desc limit 0, 50;對這個表第一個字段降序查詢前50條記錄MySQL主從復(fù)制原理mysql支持的復(fù)制類型： （1）：基于語句的復(fù)制（statement-based replication，簡稱：SBR） ： 在主服務(wù)器上執(zhí)行的SQL語句，在從服務(wù)器上執(zhí)行同樣的語句。MySQL默認(rèn)采用基于語句的復(fù)制，效率比較高。 一旦發(fā)現(xiàn)沒法精確復(fù)制時， 會自動選著基于行的復(fù)制。MySQL 5.0及之

34、前的版本僅支持基于語句的復(fù)制. （2）：基于行的復(fù)制（row-based replication， 簡稱：RBR） ：把改變的內(nèi)容復(fù)制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0開始支持 （3）：混合類型的復(fù)制（mixed-based replication， 簡稱：MBR） : 默認(rèn)采用基于語句的復(fù)制，一旦發(fā)現(xiàn)基于語句的無法精確的復(fù)制時，就會采用基于行的復(fù)制。三個進程：Mysql的復(fù)制（replication）是一個異步的復(fù)制，從一個Master復(fù)制到另一個Slave。實現(xiàn)整個復(fù)制操作主要由三個進程完成的，其中兩個進程在Slave（Sql進程和IO進程），另外一個進程在 Ma

35、ster（IO進程）上，可以分別在master如果replication在進行的話可以通過運行show processlist查看，slave上可以直線show slave status進行查看三個log文件和兩個info文件保存master的相關(guān)信息。不要刪除它，否則，slave重啟后不能連接包含slave中當(dāng)前二進制日志和中繼日志的信息。二進制日志(binary log)，中繼日志(relay log)，error.logMySQL主從復(fù)制原理MySQL主從復(fù)制原理復(fù)制如何工作 復(fù)制有5個步驟： 1)master將改變記錄到二

36、進制日志(binary log)中(這些記錄叫做二進制日志事件，binary log events)； 2) Slave上面的IO進程連接上Master，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內(nèi)容拷貝到它的中繼日志(relay log)； 3) Master接收到來自Slave的IO進程的請求后，通過負(fù)責(zé)復(fù)制的IO進程根據(jù)請求信息讀取制定日志指定位置之后的日志信息，返回給Slave 的IO進程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經(jīng)到Master端的bin-log文件的名稱以及bin-log的位置4)、Slave的IO進程接收到信息后，將接收到的日

37、志內(nèi)容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪 個位置開始往后的日志內(nèi)容，請發(fā)給我”； 5)、Slave的Sql進程檢測到relay-log中新增加了內(nèi)容后，會馬上解析relay-log的內(nèi)容成為在Master端真實執(zhí)行時候的那些可執(zhí)行的內(nèi)容，并在自身執(zhí)行.同時slave也會通過sql進程去對比master和slave的變化，如果對比發(fā)現(xiàn)不一致，復(fù)制進程會停止并把錯誤信息計入slave的error.log,如果結(jié)果能對的上，一個新的日子的位置和數(shù)字會記錄到slave會等另外一個變化到relaylog文件。MySQL主從復(fù)制原理MySQL主從復(fù)制原理監(jiān)控MYSQL主從復(fù)制中監(jiān)控從庫運行狀態(tài)的腳本。通過監(jiān)控show slave statusG命令輸出的Seconds_Behind_Master參數(shù)的