MySQL基本原理

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

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

3、irght(數(shù)據(jù)倉(cāng)庫(kù)引擎)2021/8/27InnoDB特點(diǎn)聚集索引行級(jí)鎖數(shù)據(jù)文件2021/8/28聚集索引InnoDB實(shí)現(xiàn)兩種B+Tree索引，一種是列值為Key，主鍵位置為Value即 (列值, 主鍵位置) 的非主鍵索引（Secondary Index），另一種是主鍵索引，兩種索引的每個(gè)葉子節(jié)點(diǎn)都有一個(gè)雙向指針?lè)謩e指向前驅(qū)和后繼節(jié)點(diǎn)。主鍵索引即聚集索引（Cluster Index），它不僅有主鍵，而且有主鍵所屬的全部數(shù)據(jù)，所以在InnoDB中，主鍵索引即數(shù)據(jù)主鍵索引即數(shù)據(jù)。在InnoDB中，即使用戶不指定主鍵，InnoDB也會(huì)生成一個(gè)隱含主鍵，這種情況下，InnoDB的性能比采用序列主鍵性

4、能下降30%左右。詳細(xì)參考：MySQL索引與存儲(chǔ)方式對(duì)性能的影響 數(shù)據(jù)庫(kù)算法與數(shù)據(jù)結(jié)構(gòu)系列B樹(shù)相關(guān)2021/8/29聚集索引（圖）2021/8/210行級(jí)鎖InnoDB為了實(shí)現(xiàn)高并發(fā)，實(shí)現(xiàn)了一個(gè)行級(jí)鎖。不同于Oracle的行級(jí)鎖，InnoDB的行級(jí)鎖是“間隙鎖”，即如果操作 1 col1 10，哪怕只有col1=3一條記錄，在可重復(fù)讀隔離級(jí)別下，也無(wú)法插入 col1=2 或 col1=4 等在110范圍內(nèi)的記錄。InnoDB的行級(jí)鎖加在主鍵索引上，而不是加在數(shù)據(jù)塊上。詳細(xì)參考：InnoDB行鎖的實(shí)現(xiàn)分析2021/8/211數(shù)據(jù)文件InnoDB的數(shù)據(jù)文件包括： （innodb_file_per_

5、table）.frm 表結(jié)構(gòu)文件.ibd 表數(shù)據(jù)文件（數(shù)據(jù)目錄）、系統(tǒng)元數(shù)據(jù)和undo space（共享表空間）ib_logfile* 重做日志文件，相當(dāng)于Oracle的Redo Log2021/8/212MySQL高級(jí)調(diào)優(yōu)索引調(diào)整重要性能參數(shù)調(diào)整編譯參數(shù)調(diào)優(yōu)2021/8/213索引調(diào)整主鍵索引必須有，并且按要滿足序列化，遞增或者遞減索引合并：索引順序不影響結(jié)果時(shí)，將可復(fù)用度高的字段放在索引的前面主鍵排序無(wú)需索引：假設(shè)最后輸出結(jié)果按主鍵排序，無(wú)需考慮主鍵字段加入索引，InnoDB數(shù)據(jù)完全按主鍵順序組織，最終取出數(shù)據(jù)既是按主鍵排序。關(guān)聯(lián)表存在關(guān)聯(lián)字段和條件字段，優(yōu)先將條件字段放在關(guān)聯(lián)字段前建立組

6、合索引。實(shí)在無(wú)法避免的列運(yùn)算但又需要很高的性能，可以通過(guò)新列和Trigger（或程序?qū)崿F(xiàn)）實(shí)現(xiàn)函數(shù)索引：給MySQL做虛擬的“函數(shù)索引”2021/8/214重要性能參數(shù)調(diào)整Sync_binlog同步刷新binlog的秒數(shù)，設(shè)為0則交由操作系統(tǒng)自己刷新，MySQL不強(qiáng)制刷新，除0外，數(shù)值越小性能越差innodb_flush_log_at_trx_commit0性能最好，但無(wú)法保證數(shù)據(jù)安全，斷電可能損失較多數(shù)據(jù)。2性能較好，但可能損失1s的數(shù)據(jù)。1性能最差，不丟失數(shù)據(jù)。0只保證日志刷新到文件，不保證刷新到磁盤(pán)，2保證每秒刷新到磁盤(pán)，1保證每次提交刷新到磁盤(pán)。關(guān)于Linux Cache參考：Linu

7、x Cache 機(jī)制探究innodb_max_dirty_pages_pctInnoDB Buffer Pool中臟頁(yè)的比例，建議設(shè)置為3%或者更小。如果臟頁(yè)比例超過(guò)這個(gè)參數(shù)，就會(huì)回寫(xiě)到磁盤(pán)。如果這個(gè)值太大，每次關(guān)閉就會(huì)有很多臟頁(yè)需要回寫(xiě)。2021/8/215編譯參數(shù)調(diào)優(yōu)GCC參數(shù)（以參數(shù)（以Xeon 55XX系列系列64位系統(tǒng)為例）位系統(tǒng)為例）CXX=gcc CHOST=”x86_64-pc-linux-gnu” CFLAGS=”-O3 -fomit-frame-pointer -pipe -march=nocona -mfpmath=sse -m128bit-long-double -mm

8、mx -msse -msse2 -maccumulate-outgoing-args -m64 -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-optimize2CXXFLAGS=”$CFLAGS” MySQL參數(shù)參數(shù)./

9、configure prefix=/usr/alibaba/install/mysql-ent-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-

10、fast-mutexes with-zlib-dir=bundled enable-assembler 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ì)參考：

11、適合MySQL的Xeon 55XX系列CPU編譯參數(shù)2021/8/216MySQL復(fù)制原理MySQL Replication的基本原理是通過(guò)binlog復(fù)制應(yīng)用的方式來(lái)還原數(shù)據(jù)。MySQL通過(guò)Server_id來(lái)識(shí)別binlog由哪臺(tái)主機(jī)產(chǎn)生，因此即使雙Master復(fù)制，也不會(huì)出現(xiàn)binlog被重復(fù)應(yīng)用復(fù)制線程分為Slave IO和Slave SQL兩個(gè)，Slave IO線程只負(fù)責(zé)注冊(cè)到Master上，讀取binlog，然后解析到本地，Slave SQL線程只負(fù)責(zé)把Slave IO線程產(chǎn)生的可執(zhí)行SQL應(yīng)用到本地。避免主鍵沖突，MySQL提供了auto_increment_increment和

12、auto_increment_offset來(lái)控制主鍵生成的序列，只要雙Master的兩臺(tái)主機(jī)沒(méi)有相同的序列，就絕對(duì)不可能復(fù)制沖突。2021/8/217MySQL高級(jí)備份Xtrabckup物理熱備份InnoDB數(shù)據(jù)：“innobackupex /data/backups” 就可以創(chuàng)建一個(gè)備份到/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)容“Innoba

13、ckupex copy-back/data/backups/2010-09-08_11-25-44/”，將數(shù)據(jù)文件拷貝到datadir，從而恢復(fù)數(shù)據(jù)原理：Xtrabckup啟動(dòng)時(shí)，會(huì)對(duì)數(shù)據(jù)庫(kù)加一個(gè)瞬時(shí)的鎖，此時(shí)開(kāi)始，數(shù)據(jù)庫(kù)發(fā)生的所有redo log和undo space都會(huì)被Xtrabckup記錄，同時(shí)還在拷貝ibd文件。當(dāng)恢復(fù)的時(shí)候，Xtrabckup先利用InnoDB源碼中的恢復(fù)事務(wù)函數(shù)把當(dāng)時(shí)時(shí)間點(diǎn)之后的數(shù)據(jù)回滾，然后就可以得到一個(gè)完整備份時(shí)拷貝，然后覆蓋現(xiàn)有文件即可。官方文檔：XtraBackup Howto2021/8/218MySQL關(guān)鍵代碼實(shí)現(xiàn)分析主要數(shù)據(jù)結(jié)構(gòu)主要算法2021/8/

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