1、A,1,MySQL基礎(chǔ)技能與原理 基本原理,MySQL DBA Team,彭立勛（）,A,2,內(nèi)容概要,1. MySQL體系架構(gòu) 2. InnoDB特點(diǎn) 3. MySQL高級調(diào)優(yōu) 4. MySQL復(fù)制原理 5. MySQL高級備份 6. MySQL關(guān)鍵代碼實現(xiàn)分析,A,3,MySQL體系架構(gòu),MySQL結(jié)構(gòu)層次 MySQL處理流程 存儲引擎機(jī)制,A,4,MySQL結(jié)構(gòu)層次,A,5,MySQL處理流程,當(dāng)客戶端鏈接上mysql服務(wù)端時，系統(tǒng)為其分配一個鏈接描述符thd，用以描述客戶端的所有信息，將作為參數(shù)在各個模塊之間傳遞。一個典型的客戶端查詢在MySQL的主要模塊之間的調(diào)用關(guān)系如圖所示：,A,

2、6,存儲引擎機(jī)制,MySQL提供一個抽象層，允許不同的存儲應(yīng)請使用相同的API對表進(jìn)行訪問。該接口通過一個被稱為Handler的抽象類來實現(xiàn)，該處理器提供了一些可實現(xiàn)基本操作的方法，入打開和關(guān)閉表，連續(xù)掃描記錄，按照鍵值檢索記錄，存儲記錄以及刪除記錄。 每個存儲引擎都執(zhí)行處理器的一個子類以實現(xiàn)接口方法，以便將處理器操作轉(zhuǎn)化為特定存儲引擎的此層次存儲/檢索API調(diào)用 MySQL的主要存儲引擎有：MyISAM(非事務(wù)引擎)/InnoDB(事務(wù)引擎)/Archive(歸檔引擎)/Memory(內(nèi)存引擎)/NDB(集群引擎) 其他特殊引擎：Infobirght(數(shù)據(jù)倉庫引擎),A,7,InnoDB特點(diǎn)

3、,聚集索引 行級鎖 數(shù)據(jù)文件,A,8,聚集索引,InnoDB實現(xiàn)兩種B+Tree索引，一種是列值為Key，主鍵位置為Value即 (列值, 主鍵位置) 的非主鍵索引（Secondary Index），另一種是主鍵索引，兩種索引的每個葉子節(jié)點(diǎn)都有一個雙向指針分別指向前驅(qū)和后繼節(jié)點(diǎn)。 主鍵索引即聚集索引（Cluster Index），它不僅有主鍵，而且有主鍵所屬的全部數(shù)據(jù)，所以在InnoDB中，主鍵索引即數(shù)據(jù)。 在InnoDB中，即使用戶不指定主鍵，InnoDB也會生成一個隱含主鍵，這種情況下，InnoDB的性能比采用序列主鍵性能下降30%左右。 詳細(xì)參考：MySQL索引與存儲方式對性能的影響 數(shù)

4、據(jù)庫算法與數(shù)據(jù)結(jié)構(gòu)系列B樹相關(guān),A,9,聚集索引（圖）,A,10,行級鎖,InnoDB為了實現(xiàn)高并發(fā)，實現(xiàn)了一個行級鎖。 不同于Oracle的行級鎖，InnoDB的行級鎖是“間隙鎖”，即如果操作 1 col1 10，哪怕只有col1=3一條記錄，在可重復(fù)讀隔離級別下，也無法插入 col1=2 或 col1=4 等在110范圍內(nèi)的記錄。 InnoDB的行級鎖加在主鍵索引上，而不是加在數(shù)據(jù)塊上。 詳細(xì)參考：InnoDB行鎖的實現(xiàn)分析,A,11,數(shù)據(jù)文件,InnoDB的數(shù)據(jù)文件包括： （innodb_file_per_table） .frm 表結(jié)構(gòu)文件 .ibd 表數(shù)據(jù)文件（數(shù)據(jù)目錄）、系統(tǒng)元數(shù)據(jù)和

5、undo space（共享表空間） ib_logfile* 重做日志文件，相當(dāng)于Oracle的Redo Log,A,12,MySQL高級調(diào)優(yōu),索引調(diào)整 重要性能參數(shù)調(diào)整 編譯參數(shù)調(diào)優(yōu),A,13,索引調(diào)整,主鍵索引必須有，并且按要滿足序列化，遞增或者遞減 索引合并：索引順序不影響結(jié)果時，將可復(fù)用度高的字段放在索引的前面 主鍵排序無需索引：假設(shè)最后輸出結(jié)果按主鍵排序，無需考慮主鍵字段加入索引，InnoDB數(shù)據(jù)完全按主鍵順序組織，最終取出數(shù)據(jù)既是按主鍵排序。 關(guān)聯(lián)表存在關(guān)聯(lián)字段和條件字段，優(yōu)先將條件字段放在關(guān)聯(lián)字段前建立組合索引。 實在無法避免的列運(yùn)算但又需要很高的性能，可以通過新列和Trigger

6、（或程序?qū)崿F(xiàn)）實現(xiàn)函數(shù)索引：給MySQL做虛擬的“函數(shù)索引”,A,14,重要性能參數(shù)調(diào)整,Sync_binlog 同步刷新binlog的秒數(shù)，設(shè)為0則交由操作系統(tǒng)自己刷新，MySQL不強(qiáng)制刷新，除0外，數(shù)值越小性能越差 innodb_flush_log_at_trx_commit 0性能最好，但無法保證數(shù)據(jù)安全，斷電可能損失較多數(shù)據(jù)。2性能較好，但可能損失1s的數(shù)據(jù)。1性能最差，不丟失數(shù)據(jù)。 0只保證日志刷新到文件，不保證刷新到磁盤，2保證每秒刷新到磁盤，1保證每次提交刷新到磁盤。 關(guān)于Linux Cache參考：Linux Cache 機(jī)制探究 innodb_max_dirty_pages_

7、pct InnoDB Buffer Pool中臟頁的比例，建議設(shè)置為3%或者更小。如果臟頁比例超過這個參數(shù)，就會回寫到磁盤。如果這個值太大，每次關(guān)閉就會有很多臟頁需要回寫。,A,15,編譯參數(shù)調(diào)優(yōu),GCC參數(shù)（以Xeon 55XX系列64位系統(tǒng)為例） CXX=gcc CHOST=”x86_64-pc-linux-gnu” CFLAGS=”-O3 -fomit-frame-pointer -pipe -march=nocona -mfpmath=sse -m128bit-long-double -mmmx -msse -msse2 -maccumulate-outgoing-args -m64

8、-ftree-loop-linear -fprefetch-loop-arrays -freg-struct-return -fgcse-sm -fgcse-las -frename-registers -fforce-addr -fivopts -ftree-vectorize -ftracer -frename-registers -minline-all-stringops -fbranch-target-load-optimize2 CXXFLAGS=”$CFLAGS” MySQL參數(shù) ./configure prefix=/usr/alibaba/install/mysql-ent-

9、official-5.1.56 with-server-suffix=alibaba-mysql with-mysqld-user=mysql with-plugins=partition,blackhole,csv,heap,innobase,myisam,myisammrg with-charset=utf8 with-collation=utf8_general_ci with-extra-charsets=gbk,gb2312,utf8,ascii with-big-tables with-fast-mutexes with-zlib-dir=bundled enable-assemb

10、ler enable-profiling enable-local-infile enable-thread-safe-client with-readline with-pthread with-embedded-server with-client-ldflags=-all-static with-mysqld-ldflags=-all-static without-query-cache without-geometry without-debug without-ndb-debug 詳細(xì)參考：適合MySQL的Xeon 55XX系列CPU編譯參數(shù),A,16,MySQL復(fù)制原理,MySQL

11、 Replication的基本原理是通過binlog復(fù)制應(yīng)用的方式來還原數(shù)據(jù)。 MySQL通過Server_id來識別binlog由哪臺主機(jī)產(chǎn)生，因此即使雙Master復(fù)制，也不會出現(xiàn)binlog被重復(fù)應(yīng)用 復(fù)制線程分為Slave IO和Slave SQL兩個，Slave IO線程只負(fù)責(zé)注冊到Master上，讀取binlog，然后解析到本地，Slave SQL線程只負(fù)責(zé)把Slave IO線程產(chǎn)生的可執(zhí)行SQL應(yīng)用到本地。 避免主鍵沖突，MySQL提供了auto_increment_increment和auto_increment_offset來控制主鍵生成的序列，只要雙Master的兩臺主機(jī)沒

12、有相同的序列，就絕對不可能復(fù)制沖突。,A,17,MySQL高級備份,Xtrabckup物理熱備份InnoDB數(shù)據(jù)： “innobackupex /data/backups” 就可以創(chuàng)建一個備份到/data/backups目錄下，只要看到 “completed OK!” 就是備份成功。 Xtrabckup物理恢復(fù)InnoDB數(shù)據(jù)： “Innobackupex -apply-log /data/backups/2010-09-08_11-25-44/”，將數(shù)據(jù)文件處理為可以直接copy的內(nèi)容 “Innobackupex copy-back/data/backups/2010-09-08_11-25

13、-44/”，將數(shù)據(jù)文件拷貝到datadir，從而恢復(fù)數(shù)據(jù) 原理：Xtrabckup啟動時，會對數(shù)據(jù)庫加一個瞬時的鎖，此時開始，數(shù)據(jù)庫發(fā)生的所有redo log和undo space都會被Xtrabckup記錄，同時還在拷貝ibd文件。當(dāng)恢復(fù)的時候，Xtrabckup先利用InnoDB源碼中的恢復(fù)事務(wù)函數(shù)把當(dāng)時時間點(diǎn)之后的數(shù)據(jù)回滾，然后就可以得到一個完整備份時拷貝，然后覆蓋現(xiàn)有文件即可。 官方文檔：XtraBackup Howto,A,18,MySQL關(guān)鍵代碼實現(xiàn)分析,主要數(shù)據(jù)結(jié)構(gòu) 主要算法,A,19,主要數(shù)據(jù)結(jié)構(gòu),THD 線程描述符（sql/sql_class.h） 包含處理用戶請求時需要的相

14、關(guān)數(shù)據(jù)，每個連接會有一個線程來處理，在一些高層函數(shù)中，此數(shù)據(jù)結(jié)構(gòu)常被當(dāng)作第一個參數(shù)傳遞。 NET 網(wǎng)絡(luò)連接描述符（sql/mysql_com.h） 網(wǎng)絡(luò)連接描述符，對內(nèi)部數(shù)據(jù)包進(jìn)行了封裝，是client和server之間的通信協(xié)議。 TABLE 數(shù)據(jù)庫表描述符（sql/table.h） 數(shù)據(jù)庫表描述符，分成TABLE和TABLE_SHARE兩部分。 FIELD 字段描述符（sql/field.h） 域描述符，是各種字段的抽象基類。,A,20,主要算法,排序算法： MySQL排序采用sort_buffer_size設(shè)定的線程獨(dú)占內(nèi)存進(jìn)行排序，如果緩存里內(nèi)放下要排序的數(shù)據(jù)，那么MySQL會采取載入所有要返回的值進(jìn)入緩存排序；如果緩存放不下，則把要排序的列放入緩存排序，然后再回表查詢返回結(jié)果，如果依然放不下，就會采用歸并排序，排好序的部分放入磁盤，載入未排好序的部分繼續(xù)在內(nèi)存中排序，然后和磁盤上的部分二路歸并，直到排序全部完成。 詳細(xì)參考：數(shù)據(jù)庫算法與數(shù)據(jù)結(jié)構(gòu)系列排序相關(guān) 連接算