前言

最近在回顧之前學(xué)的知識點，mysql部分涉及的東西很多痘煤，所以想寫寫文章記錄一些重要的知識點输瓜，方便以后回顧瓦胎，同時也分享給大家，如果文章中有描述的不對或不足的地方尤揣，歡迎指出和交流搔啊。

一、架構(gòu)原理

1北戏、基礎(chǔ)架構(gòu)

1.1坯癣、架構(gòu)概覽

image

1.2、架構(gòu)說明

1.2.1最欠、server層

• 連接器： 負(fù)責(zé)跟客戶端建立連接示罗、獲取權(quán)限惩猫、維持和管理連接，一個用戶成功建立連接后蚜点，即使用管理員的賬號對這個用戶的權(quán)限進(jìn)行修改也不會影響已存在的連接的權(quán)限轧房。客戶端如果太長時間沒動靜绍绘，連接器會自動斷開連接奶镶，由參數(shù)wait_timeout控制（默認(rèn)為8小時）
• 查詢緩存： 查詢出的結(jié)果會暫時緩存在這里，不過建議盡量不用查詢緩存陪拘，因為更新表記錄后厂镇，緩存會失效，對更新比較頻繁的表來說命中率很低左刽，除非是很長時間才更新一次的表（mysql8.0之后已將查詢緩存功能刪除）
• 分析器： 先做詞法分析捺信，識別出關(guān)鍵字。再做語法分析欠痴，判斷是否滿足mysql的語法迄靠。
• 優(yōu)化器： 選擇索引、確定執(zhí)行方案喇辽。
• 執(zhí)行器： 操作引擎掌挚，返回執(zhí)行結(jié)果。

1.2.2菩咨、存儲引擎層

存儲引擎層有MyISAM吠式、Innodb等。

1.2.3抽米、系統(tǒng)文件層

• 日志文件： 包括：錯誤日志（Error log）特占、通用查詢?nèi)罩荆℅eneral query log）、通用查詢?nèi)罩荆℅eneral query log）缨硝、通用查詢?nèi)罩荆℅eneral query log）摩钙。
• 配置文件： 用于存放MySQL所有的配置信息文件，比如my.cnf查辩、my.ini等胖笛。
• 數(shù)據(jù)文件:
  • db.opt 文件: 記錄這個庫的默認(rèn)使用的字符集和校驗規(guī)則。
  • frm 文件： 存儲與表相關(guān)的元數(shù)據(jù)（meta）信息宜岛，包括表結(jié)構(gòu)的定義信息等长踊，每一張表都會有一個frm 文件。
  • MYD 文件： MyISAM 存儲引擎專用萍倡，存放 MyISAM 表的數(shù)據(jù)（data)身弊，每一張表都會有一個.MYD 文件。
  • MYI 文件： MyISAM 存儲引擎專用，存放 MyISAM 表的索引相關(guān)信息阱佛，每一張 MyISAM 表對應(yīng)一個 .MYI 文件帖汞。
  • ibd文件和 ibdata文件： 存放 InnoDB 的數(shù)據(jù)文件（包括索引）。InnoDB 存儲引擎有兩種
    表空間方式：獨享表空間和共享表空間凑术。獨享表空間使用 .ibd 文件來存放數(shù)據(jù)翩蘸，且每一張InnoDB 表對應(yīng)一個 .ibd 文件。共享表空間使用 .ibdata 文件淮逊，所有表共同使用一個（或多個催首，自行配置）.ibdata 文件。
  • ibdata1 文件: 系統(tǒng)表空間數(shù)據(jù)文件泄鹏，存儲表元數(shù)據(jù)郎任、Undo日志等。
  • ib_logfile0备籽、ib_logfile1 文件： Redo log 日志文件舶治。

1.3、運(yùn)行機(jī)制

1.3.1胶台、一次sql查詢經(jīng)過的過程

• 執(zhí)行過程圖：
  執(zhí)行過程
• 過程詳解
  • <font size=2>①建立連接： 通過客戶端/服務(wù)器通信協(xié)議與MySQL建立連接歼疮。MySQL 客戶端與服務(wù)端的通信方式是 “ 半雙工 ”杂抽。</font>
  • <font size=2>②查詢緩存： 這是MySQL的一個可優(yōu)化查詢的地方诈唬，如果開啟了查詢緩存且在查詢緩存過程中查詢到完全相同的SQL語句（包括參數(shù)值），則將查詢結(jié)果直接返回給客戶端缩麸。不過有些情況下即使開啟查詢緩存铸磅，以下SQL也不能緩存（<font size=1 color=red>查詢語句使用SQL_NO_CACHE、查詢的結(jié)果大于query_cache_limit設(shè)置杭朱、查詢中有一些不確定的參數(shù)阅仔，比如now()</font>）</font>
  • <font size=2>③解析器： 將客戶端發(fā)送的SQL進(jìn)行語法解析，生成"解析樹"弧械。預(yù)處理器根據(jù)一些MySQL規(guī)則進(jìn)一步檢查“解析樹”是否合法八酒，例如這里將檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，還會解析名字和別名刃唐，看看它們是否有歧義羞迷，最后生成新的“解析樹”。</font>
  • <font size=2>④查詢優(yōu)化器： 根據(jù)“解析樹”生成最優(yōu)的執(zhí)行計劃画饥。MySQL使用很多優(yōu)化策略生成最優(yōu)的執(zhí)行計劃衔瓮，可以分為兩類：靜態(tài)優(yōu)化（編譯時優(yōu)化）、動態(tài)優(yōu)化（運(yùn)行時優(yōu)化）抖甘。</font>
    • <font size=2>等價變換策略： 例如：5=5 and a>5 改成 a > 5热鞍，a < b and a=5 改成b>5 and a=5；基于聯(lián)合索引，調(diào)整條件位置等）薇宠。</font>
    • <font size=2>優(yōu)化count偷办、min、max等函數(shù)： InnoDB引擎min函數(shù)只需要找索引最左邊澄港、InnoDB引擎max函數(shù)只需要找索引最右邊爽篷、MyISAM引擎count(*)，不需要計算慢睡，直接返回逐工。</font>
    • <font size=2>提前終止查詢： 使用了limit查詢，獲取limit所需的數(shù)據(jù)漂辐，就不在繼續(xù)遍歷后面數(shù)據(jù)泪喊。</font>
    • <font size=2>in的優(yōu)化： MySQL對in查詢，會先進(jìn)行排序髓涯，再采用二分法查找數(shù)據(jù)袒啼。比如where id in (2,1,3)，變成 in (1,2,3)纬纪。</font>
  • <font size=2>⑤查詢執(zhí)行引擎： 負(fù)責(zé)執(zhí)行 SQL 語句蚓再，此時查詢執(zhí)行引擎會根據(jù) SQL 語句中表的存儲引擎類型，以及對應(yīng)的API接口與底層存儲引擎緩存或者物理文件的交互包各，得到查詢結(jié)果并返回給客戶端摘仅。若開啟用查詢緩存，這時會將SQL 語句和結(jié)果完整地保存到查詢緩存（Cache&Buffer）中问畅，以后若有相同的 SQL 語句執(zhí)行則直接返回結(jié)果娃属。<font color=red size=1>注：如果開啟了查詢緩存，先將查詢結(jié)果做緩存操作护姆，如果返回結(jié)果過多矾端，采用增量模式返回</font></font>

二、存儲引擎

關(guān)于存儲引擎這邊只接受InnoDB引擎卵皂，其他引擎用的不多秩铆，所以了解的也不是很深。

1灯变、存儲結(jié)構(gòu)

1.1殴玛、總體存儲結(jié)構(gòu)

innodb總體結(jié)構(gòu)

1.2、內(nèi)存結(jié)構(gòu)

innodb內(nèi)存結(jié)構(gòu)

1.2.1柒凉、Buffer Pool

緩沖池族阅，簡稱BP。BP以Page頁為單位膝捞，默認(rèn)大小16K坦刀，BP的底層采用鏈表數(shù)據(jù)結(jié)構(gòu)管理Page愧沟。在InnoDB訪問表記錄和索引時會在Page頁中緩存，以后使用可以減少磁盤IO操作鲤遥，提升效率沐寺，其中page有三種狀態(tài)類型：

1、free page： 空閑page盖奈，未被使用
2混坞、clean page： 被使用page，數(shù)據(jù)沒有被修改過
3钢坦、dirty page： 臟頁究孕，被使用page，數(shù)據(jù)被修改過爹凹，頁中數(shù)據(jù)和磁盤的數(shù)據(jù)產(chǎn)生了不一致

三種鏈表結(jié)構(gòu)：

1厨诸、free list： 表示空閑緩沖區(qū)，管理free page
2禾酱、lru list： 表示正在使用的緩沖區(qū)微酬，管理clean page和dirty page，緩沖區(qū)以midpoint為基點颤陶，前面鏈表稱為new列表區(qū)颗管，存放經(jīng)常訪問的數(shù)據(jù)，占63%滓走；后面的鏈表稱為old列表區(qū)垦江，存放使用較少據(jù)據(jù)，占37%闲坎。
3疫粥、flush list： 表示需要刷新到磁盤的緩沖區(qū)茬斧，管理dirty page腰懂，內(nèi)部page按修改時間排序。臟頁即存在于flush鏈表项秉，也在LRU鏈表中绣溜，但是兩種互不影響，LRU鏈表負(fù)責(zé)管理page的可用性和釋放娄蔼，而flush鏈表負(fù)責(zé)管理臟頁的刷盤操作

1.2.2怖喻、Change Buffer

寫緩沖區(qū)，簡稱CB岁诉。在進(jìn)行DML操作時锚沸，如果BP沒有其相應(yīng)的Page數(shù)據(jù)，并不會立刻將磁盤頁加載到緩沖池涕癣，而是在CB記錄緩沖變更哗蜈，等未來數(shù)據(jù)被讀取時，會先進(jìn)行磁盤讀取，然后再從ChangeBuffer中讀取信息合并距潘，最終載入BufferPool中炼列。不過，僅適用于非唯一普通索引頁音比，如果在索引設(shè)置唯一性俭尖，在進(jìn)行修改時，InnoDB必須要做唯一性校驗洞翩，因此必須查詢磁盤稽犁，做一次IO操作。會直接將記錄查詢到BufferPool中骚亿，然后在緩沖池修改缭付，不會在ChangeBuffer操作。

• change buffer 讀過程：

  
  
  change buffer 讀過程
• change buffer 更新過程：

change buffer 更新過程

1.2.3循未、Adaptive Hash Index（自適應(yīng)哈希索引）

用于優(yōu)化對BP數(shù)據(jù)的查詢陷猫。InnoDB存儲引擎會監(jiān)控對表索引的查找，如果觀察到建立哈希索引可以帶來速度的提升的妖，則建立哈希索引绣檬，所以稱之為自適應(yīng)。InnoDB存儲引擎會自動根據(jù)訪問的頻率和模式來為某些頁建立哈希索引

1.2.4嫂粟、Log Buffer

日志緩沖區(qū)娇未，用來保存要寫入磁盤上log文件（Redo/Undo）的數(shù)據(jù)，日志緩沖區(qū)的內(nèi)容定期刷新到磁盤log文件中星虹。日志緩沖區(qū)滿時會自動將其刷新到磁盤零抬。

其中可以優(yōu)化的參數(shù)：

• innodb_log_buffer_size： 將這個參數(shù)調(diào)大，可以減少磁盤IO頻率
• <font color=red>innodb_flush_log_at_trx_commit：</font> 控制日志刷盤行為宽涌，默認(rèn)為1平夜。
  • 0 ： 每隔1秒寫日志文件和刷盤操作（寫日志文件LogBuffer-->OS cache，刷盤OS cache-->磁盤文件）卸亮，最多丟失1秒數(shù)據(jù)忽妒；
  • 1：事務(wù)提交，立刻寫日志文件和刷盤兼贸，數(shù)據(jù)不丟失段直，但是會頻繁IO操作；
  • 2：事務(wù)提交溶诞，立刻寫日志文件鸯檬，每隔1秒鐘進(jìn)行刷盤操作

1.3、磁盤結(jié)構(gòu)

1.3.1螺垢、表空間（Tablespaces）

• 系統(tǒng)表空間（The System Tablespace）
  包含InnoDB數(shù)據(jù)字典喧务，Doublewrite Buffer颜及，Change Buffer，Undo Logs的存儲區(qū)域蹂楣。系統(tǒng)表空間也默認(rèn)包含任何用戶在系統(tǒng)表空間創(chuàng)建的表數(shù)據(jù)和索引數(shù)據(jù)俏站。系統(tǒng)表空間是一個共享的表空間因為它是被多個表共享的。該空間的數(shù)據(jù)文件通過參數(shù)
  innodb_data_file_path控制痊土，默認(rèn)值是ibdata1:12M:autoextend(文件名為ibdata1肄扎、12MB、自動擴(kuò)展)
• 獨立表空間（File-Per-Table Tablespaces）
  默認(rèn)開啟赁酝，每個表文件表空間由一個.ibd數(shù)據(jù)文件代表犯祠，該文件默認(rèn)被創(chuàng)建于數(shù)據(jù)庫目錄中。表空間的表文件支持動態(tài)（dynamic）和壓縮（commpressed）行格式酌呆。
• 通用表空間（General Tablespaces）
  通過create tablespace語法創(chuàng)建的共享表空間衡载。通用表空間可以創(chuàng)建于mysql數(shù)據(jù)目錄外的其他表空間
• 撤銷表空間（Undo Tablespaces）
  InnoDB使用的undo表空間由innodb_undo_tablespaces配置選項控制，默認(rèn)為0（0表示使用系統(tǒng)表空間ibdata1隙袁，大于0表示使用undo表空間）
• 臨時表空間（Temporary Tablespaces）

1.3.2痰娱、數(shù)據(jù)字典（InnoDB Data Dictionary）

由內(nèi)部系統(tǒng)表組成，這些表包含用于查找表菩收、索引和表字段等對象的元數(shù)據(jù)

1.3.3梨睁、雙寫緩沖區(qū)（Doublewrite Buffer）

在BufferPage的page頁刷新到磁盤真正的位置前，會先將數(shù)據(jù)存在Doublewrite 緩沖區(qū)娜饵，使用Doublewrite 緩沖區(qū)時建議將innodb_flush_method設(shè)置為O_DIRECT

innodb_flush_method有三個值可以配置：

• fdatasync(默認(rèn))：fdatasync意思是先寫入操作系統(tǒng)緩存坡贺，然后再調(diào)用fsync()函數(shù)去異步刷數(shù)據(jù)文件與redo log的緩存信息
• O_DIRECT：O_DIRECT表示數(shù)據(jù)文件寫入操作會通知操作系統(tǒng)不要緩存數(shù)據(jù)，也不要用預(yù)讀箱舞，直接從Innodb Buffer寫到磁盤文件
• O_DSYNC：表示寫日志時遍坟，數(shù)據(jù)都要寫到磁盤，并且元數(shù)據(jù)也需要更新晴股，才返回成功愿伴。

1.3.4、重做日志（Redo Log）

重做日志是一種基于磁盤的數(shù)據(jù)結(jié)構(gòu)队魏，用于在崩潰恢復(fù)期間更正不完整事務(wù)寫入的數(shù)據(jù)公般，默認(rèn)情況下，重做日志在磁盤上由兩個名為ib_logfile0和ib_logfile1的文件物理表示

1.3.5胡桨、撤銷日志（Undo Logs）

用于例外情況時回滾事務(wù)。撤消日志屬于邏輯日志瞬雹，根據(jù)每行記錄進(jìn)行記錄昧谊。撤消日志存在于系統(tǒng)表空間、撤消表空間和臨時表空間中

1.4酗捌、線程模型

innodb線程模型

1.4.1呢诬、IO Thread

• read thread：負(fù)責(zé)讀取操作涌哲，將數(shù)據(jù)從磁盤加載到緩存page頁（4個）
• write thread：負(fù)責(zé)寫操作，將緩存臟頁刷新到磁盤（4個）
• log thread：負(fù)責(zé)將日志緩沖區(qū)內(nèi)容刷新到磁盤（1個）
• insert buffer thread：負(fù)責(zé)將寫緩沖內(nèi)容刷新到磁盤（1個）

1.4.2宦焦、purge Thread

事務(wù)提交之后醋闭，其使用的undo日志將不再需要澜薄，因此需要Purge Thread回收已經(jīng)分配的undo頁

1.4.3、page Cleaner Thread

作用是將臟數(shù)據(jù)刷新到磁盤初烘，臟數(shù)據(jù)刷盤后相應(yīng)的redo log也就可以覆蓋，即可以同步數(shù)據(jù)分俯，又能達(dá)到redo log循環(huán)使用的目的肾筐。會調(diào)用write thread線程處理

<font color=red >刷臟頁的機(jī)制：</font>

• 用到innodb_io_capacity這個參數(shù)，它會告訴InnoDB你的磁盤能力缸剪。這個值建議設(shè)置成磁盤的IOPS吗铐。磁盤的IOPS可以通過fio這個工具來測試
• InnoDB的刷盤速度就是要參考這兩個因素：一個是臟頁比例，一個是redo log寫盤速度杏节，InnoDB會根據(jù)這兩個因素先單獨算出兩個數(shù)字
  • 參數(shù)innodb_max_dirty_pages_pct是臟頁比例上限唬渗，默認(rèn)值是75%。InnoDB會根據(jù)當(dāng)前的臟頁比例（假設(shè)為M）奋渔，算出一個范圍在0到100之間的數(shù)字

  • InnoDB每次寫入的日志都有一個序號谣妻，當(dāng)前寫入的序號跟checkpoint對應(yīng)的序號之間的差值，
    我們假設(shè)為N卒稳。InnoDB會根據(jù)這個N算出一個范圍在0到100之間的數(shù)字

    
    
    刷臟頁的速度策略
• innodb_flush_neighbors 參數(shù)：是用來控制將鄰居臟頁也刷盤的蹋半，值為1的時候會有上述的“連坐”機(jī)制，值為0時表示不找鄰居充坑，自己刷自己的减江，在MySQL 8.0中，innodb_flush_neighbors參數(shù)的默認(rèn)值已經(jīng)是0了

1.4.5捻爷、Master Thread

主線程辈灼，負(fù)責(zé)調(diào)度其他各線程，優(yōu)先級最高也榄，它會在每隔一段時間執(zhí)行一些操作巡莹，比如：

• 每1秒的操作：
  • 1、刷新日志緩沖區(qū)甜紫，刷到磁盤
  • 2降宅、合并寫緩沖區(qū)數(shù)據(jù)，根據(jù)IO讀寫壓力來決定是否操作
  • 3囚霸、刷新臟頁數(shù)據(jù)到磁盤腰根，根據(jù)臟頁比例達(dá)到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）
• 每10秒的操作：
  • 1.刷新臟頁數(shù)據(jù)到磁盤
  • 2.合并寫緩沖區(qū)數(shù)據(jù)
  • 3.刷新日志緩沖區(qū)
  • 4.刪除無用的undo頁

1.5拓型、數(shù)據(jù)文件

1.5.1额嘿、文件存儲結(jié)構(gòu)

innodb文件存儲結(jié)構(gòu)

• Tablesapce： 表空間
• segment： 段瘸恼，用于管理多個Extent，分為數(shù)據(jù)段（Leaf node segment）册养、索引段（Non-leaf node segment）东帅、回滾段（Rollback segment）。一個表至少會有兩個segment球拦，一個管理數(shù)據(jù)靠闭，一個管理索引。每多創(chuàng)建一個索引刘莹，會多兩個segment
• extent： 區(qū)阎毅，一個區(qū)固定包含64個連續(xù)的頁，大小為1M点弯。當(dāng)表空間不足扇调，需要分配新的頁資源，不會一頁一頁分抢肛，直接分配一個區(qū)
• page： 頁狼钮，用于存儲多個Row行記錄，大小為16K捡絮。包含很多種頁類型熬芜，比如數(shù)據(jù)頁，undo頁福稳，系統(tǒng)頁涎拉，事務(wù)數(shù)據(jù)頁，大的BLOB對象頁的圆，page header鼓拧，page trailer和page body組成
• row： 行，包含了記錄的字段值越妈，事務(wù)ID（Trx id）季俩、滾動指針（Roll pointer）、字段指針（Field pointers）等信息

1.5.2梅掠、File文件格式

• Antelope： 最原始的InnoDB文件格式酌住，它支持兩種行格式：COMPACT和REDUNDANT
• Barracuda： 新的文件格式。它支持InnoDB的所有行格式

1.5.3阎抒、Row行格式（Row_format）

• REDUNDANT： Redundant行格式是MySQL 5.0之前使用的一種行格式酪我，這里不做討論
• COMPACT： Compact行記錄是在MySQL5.0中引入的，為了高效的存儲數(shù)據(jù)挠蛉，簡單的說祭示，就是為了讓一個頁（Page）存放的行數(shù)據(jù)越多，這樣性能就越高
• DYNAMIC： 使用DYNAMIC行格式谴古，InnoDB會將表中長可變長度的列值完全存儲在頁外质涛，而索引記錄只包含指向溢出頁的20字節(jié)指針。大于或等于768字節(jié)的固定長度字段編碼為可變長度字段掰担。DYNAMIC行格式支持大索引前綴汇陆，最多可以為3072字節(jié)，可通過innodb_large_prefix參數(shù)控制
• COMPRESSED： COMPRESSED行格式提供與DYNAMIC行格式相同的存儲特性和功能带饱，但增加了對表和索引數(shù)據(jù)壓縮的支持毡代。

三、日志系統(tǒng)

1勺疼、undo log

數(shù)據(jù)庫事務(wù)開始之前教寂，會將要修改的記錄存放到 Undo 日志里，當(dāng)事務(wù)回滾時或者數(shù)據(jù)庫崩潰時执庐，可以利用 Undo 日志酪耕，撤銷未提交事務(wù)對數(shù)據(jù)庫產(chǎn)生的影響

1.1、產(chǎn)生與銷毀

Undo Log在事務(wù)開始前產(chǎn)生轨淌，事務(wù)在提交時迂烁，并不會立刻刪除undo log，innodb會將該事務(wù)對應(yīng)的undo log放入到刪除列表中递鹉，后面會通過后臺線程purge thread進(jìn)行回收處理

1.2盟步、存放的內(nèi)容

Undo Log屬于邏輯日志，記錄一個變化過程躏结。例如執(zhí)行一個delete却盘，undolog會記錄一個insert；執(zhí)行一個update媳拴，undolog會記錄一個相反的update

1.3黄橘、存儲方式

undo log采用段的方式管理和記錄。在innodb數(shù)據(jù)文件中包含一種rollback segment回滾段禀挫，內(nèi)部包含1024個undo log segment旬陡，由參數(shù)innodb_undo控制，在以前老版本语婴，只支持1個rollback segment描孟，這樣就只能記錄1024個undo log segment。后來MySQL5.5可以支持128個rollback segment砰左，即支持128*1024個undo操作匿醒，還可以通過變量 innodb_undo_logs (5.6版本以前該變量是 innodb_rollback_segments )自定義多少個rollback segment，默認(rèn)值為128

1.4缠导、作用

• 實現(xiàn)事務(wù)的原子性: 事務(wù)處理過程中廉羔，如果出現(xiàn)了錯誤或者用戶執(zhí)行了 ROLLBACK 語句，MySQL 可以利用 Undo Log 中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)
• 實現(xiàn)多版本并發(fā)控制（MVCC）: 事務(wù)未提交之前僻造，Undo Log保存了未提交之前的版本數(shù)據(jù)憋他，Undo Log 中的數(shù)據(jù)可作為數(shù)據(jù)舊版本快照供其他并發(fā)事務(wù)進(jìn)行快照讀
  
  undo log實現(xiàn)mvcc

2孩饼、redo log(InnoDB引擎特有的）

redo log通常是物理日志，記錄的是數(shù)據(jù)頁的物理修改竹挡，而不是某一行或某幾行修改成怎樣怎樣镀娶，它用來恢復(fù)提交后的物理數(shù)據(jù)頁(恢復(fù)數(shù)據(jù)頁，且只能恢復(fù)到最后一次提交的位置)

2.1揪罕、工作原理

redo log工作原理

2.1.1梯码、寫入機(jī)制

• 新增或更新記錄時先記錄到redo log中，并更新內(nèi)存好啰，空閑時再寫入磁盤轩娶，如果內(nèi)存頁中的數(shù)據(jù)與磁盤的數(shù)據(jù)不一致則稱這個內(nèi)存頁為臟頁，mysql偶爾會抖一下框往，是在刷臟頁（flush）鳄抒，什么情況下會進(jìn)行刷臟頁？

  • 1搅窿、redo log滿了嘁酿，此時會將checkpoint指針往前推，將臟頁刷入磁盤男应，這種情況會導(dǎo)致這個系統(tǒng)不再接受更新闹司，影響性能
  • 2、系統(tǒng)內(nèi)存不足時沐飘，當(dāng)需要新的內(nèi)存頁而內(nèi)存不夠用時游桩，會進(jìn)行刷入磁盤，其中耐朴，mysql使用buffer pool緩存池來管理內(nèi)存借卧，緩存池中的內(nèi)存頁有三種狀態(tài)：

  1、還沒使用的頁
  2筛峭、使用了并且是干凈頁
  3铐刘、使用了并且是臟頁

  • 3、系統(tǒng)空閑時
  • 4影晓、mysql正常關(guān)閉時

• 寫入時是采用兩階段提交

  
  
  redo log寫入機(jī)制

• 寫入策略

  • 先寫入redo log buffer再寫入page cache 最后刷到磁盤

  • 由參數(shù)innodb_flush_log_at_trx_commit控制

    1镰吵、設(shè)置為0的時候，表示每次事務(wù)提交時都只是把redo log留在redo log buffer中
    2挂签、設(shè)置為1的時候疤祭，表示每次事務(wù)提交時都將redo log直接持久化到磁盤；與sync_binlog組成“雙一”配置饵婆，一個事務(wù)提交會執(zhí)行兩次刷盤
    3勺馆、 設(shè)置為2的時候，表示每次事務(wù)提交時都只是把redo log寫到page cache

  • 其他策略

    • 后臺有個線程每隔1秒會把redo log buffer中的日志寫到page cache中然后在持久化到磁盤中

2.2、存儲結(jié)構(gòu)

是環(huán)形結(jié)構(gòu)草穆，固定大小

redo log 結(jié)構(gòu)

3灌灾、binlog

是mysql server層自己的日志，是邏輯日志续挟，記錄的是這個語句的原始邏輯紧卒，比如“給ID=2這一行的c字段加1 “

3.1侥衬、使用場景

• 1诗祸、主從復(fù)制
  在主庫中開啟Binlog功能，這樣主庫就可以把Binlog傳遞給從庫轴总，從庫拿到Binlog后實現(xiàn)數(shù)據(jù)恢復(fù)達(dá)到主從數(shù)據(jù)一致性
• 2直颅、數(shù)據(jù)恢復(fù)：
  通過mysqlbinlog工具來恢復(fù)數(shù)據(jù)

3.2、寫入機(jī)制

• 根據(jù)記錄模式和操作觸發(fā)event事件生成log event（事件觸發(fā)執(zhí)行機(jī)制）
• 系統(tǒng)binlog cache分配一片內(nèi)存怀樟，每個線程一個功偿，由參數(shù)binlog_cache_size來控制單個線程binlog cache的大小，如果超過這個大小往堡，就要暫存到磁盤中
• 將事務(wù)執(zhí)行過程中產(chǎn)生log event寫入緩沖區(qū)械荷，每個事務(wù)線程都有一個緩沖區(qū)Log Event保存在一個binlog_cache_mngr數(shù)據(jù)結(jié)構(gòu)中，在該結(jié)構(gòu)中有兩個緩沖區(qū)虑灰，一個是stmt_cache吨瞎，用于存放不支持事務(wù)的信息；另一個是trx_cache穆咐，用于存放支持事務(wù)的信息
• 將事務(wù)執(zhí)行過程中產(chǎn)生log event寫入緩沖區(qū)颤诀，每個事務(wù)線程都有一個緩沖區(qū)Log Event保存在一個binlog_cache_mngr數(shù)據(jù)結(jié)構(gòu)中，在該結(jié)構(gòu)中有兩個緩沖區(qū)对湃，一個是stmt_cache崖叫，用于存放不支持事務(wù)的信息；另一個是trx_cache拍柒，用于存放支持事務(wù)的信息
• 事務(wù)在提交階段會將產(chǎn)生的log event寫入到外部binlog文件中心傀。不同事務(wù)以串行方式將log event寫入binlog文件中，所以一個事務(wù)包含的log event信息在binlog文件中是連續(xù)的拆讯，中間不會插入其他事務(wù)的log event

• 事務(wù)執(zhí)行時脂男，把日志寫入binlog cache中，事務(wù)提交時把binlog cache中完整的事務(wù)寫入到binlog中往果，并清空binlog cache

  
  
  bin log寫盤過程
  • wirte是指把日志寫入到文件系統(tǒng)的page cache疆液，fsync才會將數(shù)據(jù)寫入磁盤
  • wirte和fsync的時機(jī)是由參數(shù)sync_binlog控制的

1. sync_binlog=0的時候，表示每次提交事務(wù)都只write陕贮，不fsync堕油；
2. sync_binlog=1的時候，表示每次提交事務(wù)都會執(zhí)行fsync；
3. sync_binlog=N(N>1)的時候掉缺，表示每次提交事務(wù)都write卜录，但累積N個事務(wù)后才fsync。

3.4眶明、三種模式

3.4.1艰毒、Row Level 行模式

日志中會記錄每一行數(shù)據(jù)被修改的形式，然后在slave端再對相同的數(shù)據(jù)進(jìn)行修改搜囱，由參數(shù)binlog_format='row'控制
優(yōu)點： 在row level模式下丑瞧，bin-log中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅僅只需要記錄那一條被修改蜀肘。所以rowlevel的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)绊汹。不會出現(xiàn)某些特定的情況下的存儲過程或function，以及trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題
缺點： row level扮宠，所有的執(zhí)行的語句當(dāng)記錄到日志中的時候西乖，都將以每行記錄的修改來記錄，會產(chǎn)生大量的日志內(nèi)容坛增。

3.4.2获雕、Statement Level（默認(rèn)）

每一條會修改數(shù)據(jù)的sql都會記錄到master的bin-log中。slave在復(fù)制的時候sql進(jìn)程會解析成和原來master端執(zhí)行過的相同的sql來再次執(zhí)行收捣，由參數(shù)binlog_format='statement'控制
優(yōu)點：statement level下的優(yōu)點首先就是解決了row level下的缺點届案，不需要記錄每一行數(shù)據(jù)的變化，減少bin-log日志量坏晦，節(jié)約IO萝玷，提高性能，因為它只需要在Master上所執(zhí)行的語句的細(xì)節(jié)昆婿，以及執(zhí)行語句的上下文的信息球碉。
缺點：由于只記錄語句，所以仓蛆，在statement level下 已經(jīng)發(fā)現(xiàn)了有不少情況會造成MySQL的復(fù)制出現(xiàn)問題睁冬，主要是修改數(shù)據(jù)的時候使用了某些定的函數(shù)或者功能的時候會出現(xiàn)，比如now()看疙。

3.4.3豆拨、 Mixed 混合模式

在Mixed模式下，MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志格式能庆，也就是在Statement和Row之間選擇一種施禾。如果sql語句確實就是update或者delete等修改數(shù)據(jù)的語句，那么還是會記錄所有行的變更搁胆。

3.5弥搞、清除

通過設(shè)置expire_logs_days參數(shù)來啟動自動清理功能邮绿。默認(rèn)值為0表示沒啟用。設(shè)置為1表示超出1天binlog文件會自動刪除掉

二攀例、索引

1船逮、索引類型

索引類型可以按照不同的劃分方式進(jìn)行劃分，主要的劃分方式有以下幾點

1.1粤铭、按索引存儲結(jié)構(gòu)劃分

可分為：B Tree索引挖胃、Hash索引、FULLTEXT全文索引梆惯、R Tree索引

1.2酱鸭、按應(yīng)用層次劃分

1.2.1、普通索引

最基本的索引類型加袋，基于普通字段建立的索引凛辣，沒有任何限制，創(chuàng)建方式：

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

1.2.2职烧、唯一索引

索引字段的值必須唯一，但允許有空值 防泵。在創(chuàng)建或修改表時追加唯一約束蚀之，就會自動創(chuàng)建對應(yīng)的唯一索引，創(chuàng)建方式：

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

1.2.3捷泞、主鍵索引

一種特殊的唯一索引足删，不允許有空值。在創(chuàng)建或修改表時追加主鍵約束即可锁右，每個表只能有一個主鍵失受，創(chuàng)建方式：

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

1.2.4、復(fù)合索引

單一索引是指索引列為一列的情況咏瑟，即新建索引的語句只實施在一列上拂到；用戶可以在多個列上建立索引，這種索引叫做復(fù)合索引（組合索引）码泞。復(fù)合索引可以代替多個單一索引兄旬，相比多個單一索引復(fù)合索引所需的開銷更小。

索引同時有兩個概念叫做窄索引和寬索引余寥，窄索引是指索引列為1-2列的索引领铐，寬索引也就是索引列超過2列的索引，設(shè)計索引的一個重要原則就是能用窄索引不用寬索引宋舷，因為窄索引往往比組合索引更有效

創(chuàng)建方式：

CREATE INDEX <索引的名字> ON tablename (字段名1绪撵，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1祝蝠，字段名2...) );

1.2.5音诈、前綴索引

有時候需要索引很長的字符列汹来，這會讓索引變得大且慢。通掣耐В可以索引開始的部分字符收班，這樣可以大大節(jié)約索引空間，從而提高索引效率谒兄，對于BLOB摔桦，TEXT，或者很長的VARCHAR類型的列承疲，必須使用前綴索引邻耕，因為MySQL不允許索引這些列的完整長度。

• 通過計算不同的區(qū)分度來決定使用多長的前綴

select count(distinct email)as l from user;
select count(distinct left(email,5))as l1 from user;

注意事項： 使用前綴索引就用不上覆蓋索引的優(yōu)化了

1.2.6燕鸽、全文索引

查詢操作在數(shù)據(jù)量比較少時兄世，可以使用like模糊查詢，但是對于大量的文本數(shù)據(jù)檢索啊研，效率很低御滩。如果使用全文索引，查詢速度會比like快很多倍党远。在MySQL 5.6 以前的版本削解，只有MyISAM存儲引擎支持全文索引，從MySQL 5.6開始MyISAM和InnoDB存儲引擎均支持沟娱，創(chuàng)建方式：

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名)

使用方法：全文索引有自己的語法格式氛驮，使用 match 和 against 關(guān)鍵字

select * from user where match(name) against('aaa');
select * from user where match(name) against('a*' in boolean mode);

注意事項：

全文索引必須在字符串、文本字段上建立济似。
全文索引字段值必須在最小字符和最大字符之間的才會有效矫废。（innodb：3-84；myisam：4-84）
全文索引字段值要進(jìn)行切詞處理砰蠢，按syntax字符進(jìn)行切割蓖扑，例如b+aaa，切分成b和aaa
全文索引匹配查詢娩脾，默認(rèn)使用的是等值匹配赵誓，例如a匹配a，不會匹配ab,ac柿赊。如果想匹配可以在布爾模式下搜索a*

1.3俩功、按索引鍵值類型劃分

主鍵索引、輔助索引（二級索引）

1.4碰声、按數(shù)據(jù)存儲和索引鍵值邏輯關(guān)系劃分

1.4.1诡蜓、聚集索引（聚簇索引）

InnoDB的聚簇索引就是按照主鍵順序構(gòu)建 B+Tree結(jié)構(gòu)。B+Tree的葉子節(jié)點就是行記錄胰挑，行記錄和主鍵值緊湊地存儲在一起蔓罚。 這也意味著 InnoDB 的主鍵索引就是數(shù)據(jù)表本身椿肩，它按主鍵順序存放了整張表的數(shù)據(jù)，占用的空間就是整個表數(shù)據(jù)量的大小豺谈。通常說的主鍵索引就是聚集索引

1.4.2郑象、非聚集索引（非聚簇索引）

與InnoDB表存儲不同，MyISAM數(shù)據(jù)表的索引文件和數(shù)據(jù)文件是分開的茬末，被稱為非聚簇索引結(jié)構(gòu)

2厂榛、索引原理

2.1、定義

是存儲引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)丽惭。需要額外開辟空間和數(shù)據(jù)維護(hù)工作击奶，索引是物理數(shù)據(jù)頁存儲，在數(shù)據(jù)文件中（InnoDB责掏，ibd文件）柜砾，利用數(shù)據(jù)頁(page)存儲。索引可以加快檢索速度换衬，但是同時也會降低增刪改操作速度痰驱，索引維護(hù)需要代價

2.2、B+Tree結(jié)構(gòu)

• 結(jié)構(gòu)： 非葉子節(jié)點不存儲data數(shù)據(jù)冗疮，只存儲索引值萄唇，這樣便于存儲更多的索引值。葉子節(jié)點包含了所有的索引值和data數(shù)據(jù)术幔。葉子節(jié)點用指針連接，提高區(qū)間的訪問性能
• 自適應(yīng)哈希索引： InnoDB注意到某些索引值訪問非常頻繁時湃密，會在內(nèi)存中基于B+Tree索引再創(chuàng)建一個哈希索引诅挑，使得內(nèi)存中的 B+Tree 索引具備哈希索引的功能，即能夠快速定值訪問頻繁訪問的索引頁

2.3泛源、索引分析與優(yōu)化

2.3.1拔妥、EXPLAIN命令重要參數(shù)解析

• select_type（查詢的類型）

SIMPLE ： 表示查詢語句不包含子查詢或union
PRIMARY：表示此查詢是最外層的查詢
UNION：表示此查詢是UNION的第二個或后續(xù)的查詢

• type（表示存儲引擎查詢數(shù)據(jù)時采用的方式）

ALL：表示全表掃描，性能最差达箍。
index：表示基于索引的全表掃描没龙，先掃描索引再掃描全表數(shù)據(jù)。
range：表示使用索引范圍查詢缎玫。使用>硬纤、>=、<赃磨、<=筝家、in等。
ref：表示使用非唯一索引進(jìn)行單值查詢邻辉。
eq_ref：一般情況下出現(xiàn)在多表join查詢溪王，表示前面表的每一個記錄腮鞍，都只能匹配后面表的一行結(jié)果。
const：表示使用主鍵或唯一索引做等值查詢莹菱，常量查詢移国。
NULL：表示不用訪問表，速度最快

• possible_keys: 表示查詢時能夠使用到的索引道伟。注意并不一定會真正使用迹缀，顯示的是索引名稱
• key: 表示查詢時真正使用到的索引，顯示的是索引名稱
• rows: 估算SQL要查詢到結(jié)果需要掃描多少行記錄
• key_len: 表示查詢使用了索引的字節(jié)數(shù)量皱卓」ィ可以判斷是否全部使用了組合索引
• Extra:

Using where
表示查詢需要通過索引回表查詢數(shù)據(jù)。
Using index
表示查詢需要通過索引娜汁，索引就可以滿足所需數(shù)據(jù)嫂易。
Using filesort
表示查詢出來的結(jié)果需要額外排序，數(shù)據(jù)量小在內(nèi)存掐禁，大的話在磁盤怜械，因此有Using filesort建議優(yōu)化。
Using temprorary
查詢使用到了臨時表傅事，一般出現(xiàn)于去重缕允、分組等操作

2.3.2婆誓、覆蓋索引

只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)囤官，無需回表昏兆，速度更快猎唁，這就叫做覆蓋索引也叫索引覆蓋骑篙。（回表查詢：通過二級索引查詢主鍵值苇侵，然后再去聚簇索引查詢記錄信息）

2.3.3耕餐、最左匹配原則

在MySQL建立聯(lián)合索引時會遵守最左前綴匹配原則店枣，即最左優(yōu)先买置，在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配粪糙。索引的底層是一顆B+樹，那么聯(lián)合索引的底層也就是一顆B+樹忿项，只不過聯(lián)合索引的B+樹節(jié)點中存儲的是鍵值蓉冈。由于構(gòu)建一棵B+樹只能根據(jù)一個值來確定索引關(guān)系，所以數(shù)據(jù)庫依賴聯(lián)合索引最左的字段來構(gòu)建轩触。

2.3.4寞酿、 LIKE查詢優(yōu)化

MySQL在使用Like模糊查詢時，索引是可以被使用的怕膛，只有把%字符寫在后面才會使用到索引

2.3.5熟嫩、NULL查詢優(yōu)化

NULL是一個特殊的值，從概念上講褐捻，NULL意味著“一個未知值”掸茅，它的處理方式與其他值有些不同椅邓。比如：不能使用=，<昧狮，>這樣的運(yùn)算符景馁，對NULL做算術(shù)運(yùn)算的結(jié)果都是NULL，count時不會包括NULL行等逗鸣，NULL比空字符串需要更多的存儲空間等

2.3.6合住、索引與排序優(yōu)化

• filesort排序
  • 原理： 先把結(jié)果查出，然后在緩存或磁盤進(jìn)行排序操作撒璧，效率較低
  • 算法：
    • 雙路排序： 需要兩次磁盤掃描讀取透葛，最終得到用戶數(shù)據(jù)。第一次將排序字段讀取出來卿樱，然后排序僚害；第二次去讀取其他字段數(shù)據(jù)
    • 單路排序： 從磁盤查詢所需的所有列數(shù)據(jù)，然后在內(nèi)存排序?qū)⒔Y(jié)果返回繁调。如果查詢數(shù)據(jù)超出緩存sort_buffer萨蚕，會導(dǎo)致多次磁盤讀取操作，并創(chuàng)建臨時表蹄胰，最后產(chǎn)生了多次IO岳遥，反而會增加負(fù)擔(dān)。解決方案：少使用select *裕寨；增加sort_buffer_size容量和max_length_for_sort_data容量
  • 會走filesort排序的場景

1浩蓉、WHERE子句和ORDER BY子句滿足最左前綴，但where子句使用了范圍查詢（例如>宾袜、<妻往、in等），explain select id from user where age>10 order by name; //對應(yīng)(age,name)索引
2试和、對索引列同時使用了ASC和DESC，explain select id from user order by age asc,name desc; //對應(yīng)(age,name)索引
3纫普、使用了不同的索引阅悍，MySQL每次只采用一個索引，ORDER BY涉及了兩個索引昨稼，explain select id from user order by name,age; //對應(yīng)(name)节视、(age)兩個索引
4、WHERE子句與ORDER BY子句假栓，使用了不同的索引寻行。explain select id from user where name='tom' order by age; //對應(yīng)（name)、(age)索引
5匾荆、WHERE子句或者ORDER BY子句中索引列使用了表達(dá)式拌蜘，包括函數(shù)表達(dá)式杆烁。explain select id from user order by abs(age); //對應(yīng)(age)索引

• index排序
  • 原理： 是指利用索引自動實現(xiàn)排序，不需另做排序操作简卧，效率會比較高
  • 會走index排序的場景：

1兔魂、 ORDER BY 子句索引列組合滿足索引最左前列。explain select id from user order by id; //對應(yīng)(id)举娩、(id,name)索引有效
2析校、WHERE子句+ORDER BY子句索引列組合滿足索引最左前列。explain select id from user where age=18 order by name; //對應(yīng)(age,name)索引

2.4铜涉、查詢優(yōu)化

2.4.1智玻、慢查詢優(yōu)化

• 全表掃描：explain分析type屬性all
• 全索引掃描：explain分析type屬性index
• 索引過濾性不好：靠索引字段選型、數(shù)據(jù)量和狀態(tài)芙代、表設(shè)計
• 頻繁的回表查詢開銷：盡量少用select *吊奢，使用覆蓋索引

2.4.2、 分頁查詢優(yōu)化

• 利用覆蓋索引優(yōu)化

select * from user limit 10000,100;
select id from user limit 10000,100;

• 利用子查詢優(yōu)化

select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100;
使用了id做主鍵比較(id>=)链蕊，并且子查詢使用了覆蓋索引進(jìn)行優(yōu)化事甜。

三、事務(wù)和鎖

1滔韵、事務(wù)控制

1.1逻谦、隔離級別

事務(wù)隔離級別

• read uncommit (RU 讀未提交) ：一個事務(wù)還沒提交時，它的變更就能被其他事務(wù)看到（會產(chǎn)生臟讀）
• read commit（RC 讀已提交） ：一個事務(wù)提交后陪蜻，它的變更才能被其他事務(wù)看到（產(chǎn)出不可重復(fù)讀問題邦马，在事務(wù)內(nèi)兩次讀取數(shù)據(jù)，第二次有其他事務(wù)提交了數(shù)據(jù)宴卖，則兩次數(shù)據(jù)不一致）
• repeatable read （RR 可重復(fù)讀)：一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù)滋将，總是跟這個事務(wù)啟動時看到的數(shù)據(jù)一致，在該隔離級別下症昏，未提交的變更對其他事務(wù)不可見
• serializable (串行化）：對于同一行記錄會加鎖随闽，串行化操作

1.2、事務(wù)隔離的實現(xiàn)(MVCC)

1.2.1肝谭、概念

MVCC（Multi-Version Concurrency Control）多版本并發(fā)控制掘宪，是用來在數(shù)據(jù)庫中控制并發(fā)的方法，實現(xiàn)對數(shù)據(jù)庫的并發(fā)訪問用的攘烛。在MySQL中魏滚，MVCC只在讀取已提交（Read Committed）和可重復(fù)讀（Repeatable Read）兩個事務(wù)級別下有效。其是通過Undo日志中的版本鏈和ReadView一致性視圖來實現(xiàn)的坟漱。MVCC就是在多個事務(wù)同時存在時鼠次，SELECT語句找尋到具體是版本鏈上的哪個版本，然后在找到的版本上返回其中所記錄的數(shù)據(jù)的過程。利用了Copy on Write的思想

1.2.2腥寇、讀取數(shù)據(jù)的方式

• 當(dāng)前讀： 讀取的都是最新版本成翩，讀取時加行鎖不允許其他事物修改當(dāng)前記錄，像select lock in share mode(共享鎖), select for update ; update, insert ,delete(排他鎖)這些操作都是一種當(dāng)前讀花颗；
• 快照讀： 通過mvcc實現(xiàn)捕传，通過不同版本來解決讀寫并發(fā)的問題

1.2.3、實現(xiàn)原理

• 三個隱式字段

DB_TRX_ID：6byte,最近修改（修改扩劝、插入）事務(wù)ID
DB_ROLL_PTR：7byte,回滾指針庸论，指向這條記錄的上一個版本（存儲在rollback segment中）
DB_ROW_ID：隱藏主鍵

• undo log

insert undo log：代表事務(wù)在insert新記錄時產(chǎn)生的undo log，只有在事務(wù)回滾時需要棒呛，事務(wù)提交后刪除
update undo log：事務(wù)在進(jìn)行update或delete時產(chǎn)生的undo log; 不僅在事務(wù)回滾時需要聂示，在快照讀時也需要；所以不能隨便刪除簇秒，只有在快速讀或事務(wù)回滾不涉及該日志時鱼喉，對應(yīng)的日志才會被purge線程統(tǒng)一清除

• read view

Read View就是事務(wù)進(jìn)行快照讀操作的時候生產(chǎn)的讀視圖(Read View)，在該事務(wù)執(zhí)行的快照讀的那一刻趋观，會生成數(shù)據(jù)庫系統(tǒng)當(dāng)前的一個快照扛禽，記錄并維護(hù)系統(tǒng)當(dāng)前活躍事務(wù)的ID(當(dāng)每個事務(wù)開啟時，都會被分配一個ID, 這個ID是遞增的皱坛，所以最新的事務(wù)编曼，ID值越大)

• RC和RR read view 的差別：
  • RC：在每個語句執(zhí)行前都會重新算出一個read view
  • RR：在事務(wù)開始是創(chuàng)建read view

1.2.3、執(zhí)行原理

mvcc執(zhí)行原理

2剩辟、數(shù)據(jù)庫鎖

2.1掐场、鎖分類

2.1.1、全局鎖

顧名思義贩猎，全局鎖就是對整個數(shù)據(jù)庫實例加鎖熊户。MySQL 提供了一個加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)吭服。當(dāng)你需要讓整個庫處于只讀狀態(tài)的時候嚷堡，可以使用這個命令，之后其他線程的以下語句會被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）艇棕、數(shù)據(jù)定義語句（包括建表麦到、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句；
應(yīng)用場景是全庫邏輯備份欠肾，全局鎖不太好，可以使用官方的mysqldump拟赊，當(dāng)使用-single-transaction時刺桃，導(dǎo)出數(shù)據(jù)之前會啟動一個事物，通過mvcc來保證一致性視圖

2.1.2吸祟、表級鎖

MySQL里面表級別的鎖有兩種：一種是表鎖瑟慈，一種是元數(shù)據(jù)鎖（meta data lock桃移，MDL)

• 表鎖： 表鎖的語法是 lock tables … read/write。與FTWRL類似葛碧，可以用unlock tables主動釋放鎖借杰， 也可以在客戶端斷開的時候自動釋放。
• 元數(shù)據(jù)鎖（meta data lock,MDL)： 當(dāng)要對表做結(jié)構(gòu)變更操作的時候进泼，加MDL寫鎖蔗衡，事務(wù)中的MDL鎖，在語句執(zhí)行開始時申請乳绕，但是語句結(jié)束后并不會馬上釋放绞惦，而會等到整個事務(wù)提交后再釋放。

2.1.3洋措、行鎖

MySQL的行鎖是在引擎層由各個引擎自己實現(xiàn)的济蝉。但并不是所有的引擎都支持行鎖，比如MyISAM引擎就不支持行鎖菠发。不支持行鎖意味著并發(fā)控制只能使用表鎖王滤，對于這種引擎的表，同一張表上任何時刻只能有一個更新在執(zhí)行滓鸠，這就會影響到業(yè)務(wù)并發(fā)度雁乡。在在InnoDBInnoDB事務(wù)中，行鎖是在需要的時候才加上的哥力，但并不是不需要了就立刻釋事務(wù)中蔗怠，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放吩跋，而是要等到事務(wù)結(jié)束時才釋放寞射。這個就是兩階段鎖協(xié)議。

• 死鎖：

  • 產(chǎn)生的原因：兩個事物互相等待對方釋放鎖

    • 死鎖

  • 死鎖排查

    • 查看死鎖日志：通過show engine innodb status\G命令查看近期死鎖日志信息锌钮。使用方法：1桥温、查看近期死鎖日志信息；2梁丘、使用explain查看下SQL執(zhí)行計劃
    • 查看鎖狀態(tài)變量：通過show status like'innodb_row_lock%‘命令檢查狀態(tài)變量侵浸，分析系統(tǒng)中的行鎖的爭奪情況

    Innodb_row_lock_current_waits：當(dāng)前正在等待鎖的數(shù)量
    Innodb_row_lock_time：從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度
    Innodb_row_lock_time_avg： 每次等待鎖的平均時間
    Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最長的一次鎖的時間
    Innodb_row_lock_waits：系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)

  • 如何解決死鎖

    • 1、設(shè)置超時時間innodb_lock_wait_timeout（默認(rèn)50S)
    • 2氛谜、發(fā)起死鎖檢測掏觉，發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈條中的某一個事務(wù)值漫，讓其他事務(wù)得以繼續(xù)執(zhí)行澳腹。將參數(shù)innodb_deadlock_detect設(shè)置為on，表示開啟這個邏輯

2.1.4、間隙鎖（Gap Lock)

隙鎖（Gap Lock）是Innodb在<font color=red>可重復(fù)讀（RR）</font>提交下為了解決幻讀問題時引入的鎖機(jī)制酱塔，幻讀在“當(dāng)前讀”才會出現(xiàn)沥邻，幻讀產(chǎn)生的原因是行鎖只能鎖住行，對于新插入的記錄這個動作羊娃，要更新的是記錄之間的間隙唐全。不使用間隙鎖的話，可以使用RC級別加上binlog_format=row蕊玷。優(yōu)化：在刪除時可以加上limit邮利，來減少間隙鎖的范圍。

2.1.5集畅、next-key lock

每個next-key lock是前開后閉區(qū)間

• 加鎖規(guī)則
  • 兩個原則

    1近弟、加鎖的基本單位是next-key-lock(前開后閉區(qū)間）
    2、查找過程中訪問到的對象才會加鎖
    - 兩個優(yōu)化
    > 1挺智、索引上的等值查詢祷愉，給唯一索引加鎖的時候，next-key lock退化為行鎖
    > 2赦颇、索引上等值查詢二鳄，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock退化為間隙鎖(開區(qū)間）

四媒怯、sql語句優(yōu)化

1订讼、order by 優(yōu)化

1.1、執(zhí)行過程

• 全字段排序

  
  
  全字段排序過程

• rowId排序

  
  
  rowId

• 由max_length_for_sort_data的值決定扇苞，如果字段總長度大于這個值則執(zhí)行rowid排序

1.2欺殿、排序機(jī)制

排序默認(rèn)會在sort_buffer內(nèi)存中進(jìn)行，如果大小超過sort_buffer_size值鳖敷，就會借助外部排序生成多個臨時文件（歸并排序）

1.3脖苏、優(yōu)化

通過覆蓋索引可以不用進(jìn)行排序，索引本身就是有序的

2定踱、join查詢優(yōu)化

2.1棍潘、判斷要不要使用join

看explain看 extra字段有沒有出現(xiàn)“Block Nested Loop”，如果沒有出現(xiàn)則可以使用join崖媚，但是要以小表作為驅(qū)動表

2.2亦歉、優(yōu)化

盡量在被驅(qū)動的表上加索引，使其使用BKA（batched key acess)算法畅哑，BKA是基于MRR（Multi-RangeRead)優(yōu)化實現(xiàn)肴楷，在t1中取出索引a的數(shù)據(jù)，在join buffer中對數(shù)據(jù)進(jìn)行id的排序荠呐，在t2進(jìn)行順序查找阶祭，使用set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';開啟BKA绷杜，如果被驅(qū)動表是大表的話，可以使用臨時表來優(yōu)化濒募，創(chuàng)建要查詢范圍數(shù)據(jù)的臨時表，再設(shè)置關(guān)聯(lián)字段的索引即可圾结。

五瑰剃、集群架構(gòu)

1、主從模式

1.1筝野、主從同步

mysql主從同步

主庫和備庫之間維持了一個長連接晌姚，主庫An內(nèi)部有個線程，專門服務(wù)備庫b的這個長連接歇竟，完整過程：

1挥唠、在備庫B上通過change master命令，設(shè)置主庫A的ip焕议、端口宝磨、用戶名、密碼盅安，以及要從哪個位置開始請求binlog唤锉，這個位置包含文件名和日志的偏移量
2、在備庫B上執(zhí)行start slave命令别瞭，這時候備庫會啟動兩個線程窿祥，io_thread和sql_thread，其中io_tread負(fù)責(zé)與主庫建立連接
3蝙寨、主庫A校驗完用戶名晒衩、密碼后，開始按照備庫B傳過來的位置墙歪，從本地讀取binlog听系，發(fā)給B
4、備庫接收到binlog后箱亿，寫到本地文件跛锌，稱為中轉(zhuǎn)日志（relay log)
5、sql_thread讀取中轉(zhuǎn)日志届惋，解析出日志里的命令髓帽，并執(zhí)行

1.2、存在的問題以及解決辦法

• 1脑豹、主庫宕機(jī)后郑藏，數(shù)據(jù)可能丟失

  • 解決辦法：半同步復(fù)制,，即主庫等待從庫寫入 relay log 并返回 ACK 后才進(jìn)行Engine Commit
  • 半同步復(fù)制

• 2瘩欺、從庫只有一個SQL Thread必盖，主庫寫壓力大拌牲，復(fù)制很可能延時

  • 解決辦法：并行復(fù)制，sql_thread變成多線程來加快備庫的執(zhí)行歌粥，減少延遲塌忽，遵循兩個規(guī)則：

  1、同一個事務(wù)必須在同一個work線程中執(zhí)行
  2失驶、操作同一行數(shù)據(jù)的多個事務(wù)必須在同一個work線程中執(zhí)行

2土居、讀寫分離

讀寫分離首先需要將數(shù)據(jù)庫分為主從庫，一個主庫用于寫數(shù)據(jù)嬉探，多個從庫完成讀數(shù)據(jù)的操作擦耀，主從庫之間通過主從復(fù)制機(jī)制進(jìn)行數(shù)據(jù)的同步

2.1、讀寫分配機(jī)制

• 基于編程和配置實現(xiàn)（應(yīng)用端）：優(yōu)點是實現(xiàn)簡單涩堤，因為程序在代碼中實現(xiàn)眷蜓，不需要增加額外的硬件開支，缺點是需要開發(fā)人員來實現(xiàn)胎围，運(yùn)維人員無從下手吁系，如果其中一個數(shù)據(jù)庫宕機(jī)了，就需要修改配置重啟項目
• 基于服務(wù)器端代理實現(xiàn)（服務(wù)器端）：常用的有MySQL Proxy痊远、MyCat以及Shardingsphere等

2.2垮抗、存在的問題及解決辦法

• 主從同步延遲
• 解決辦法：
  • 寫后立刻讀：在寫入數(shù)據(jù)庫后，某個時間段內(nèi)讀操作就去主庫碧聪，之后讀操作訪問從庫
  • 二次查詢：先去從庫讀取數(shù)據(jù)冒版，找不到時就去主庫進(jìn)行數(shù)據(jù)讀取。該操作容易將讀壓力返還給主庫逞姿，為了避免惡意攻擊辞嗡，建議對數(shù)據(jù)庫訪問API操作進(jìn)行封裝，有利于安全和低耦合
  • 根據(jù)業(yè)務(wù)特殊處理：根據(jù)業(yè)務(wù)特點和重要程度進(jìn)行調(diào)整滞造，比如重要的续室，實時性要求高的業(yè)務(wù)數(shù)據(jù)讀寫可以放在主庫。對于次要的業(yè)務(wù)谒养，實時性要求不高可以進(jìn)行讀寫分離挺狰，查詢時去從庫查詢

3、雙主模式

3.1买窟、雙主單寫

其中一個Master提供線上服務(wù)丰泊，另一個Master作為備胎供高可用切換，Master下游掛載Slave承擔(dān)讀請求

3.2始绍、問題及解決辦法

• 雙“M"互為主備瞳购，怎么避免循環(huán)復(fù)制？
• 解決辦法：會在binlog中記錄第一次執(zhí)行時所在的實例serverId,備庫在收到binlog并重放過程中生成與原binlog相同serverid的binlog亏推，A在收到時判斷serverid如果是自己的就不做處理

4学赛、主備切換

4.1年堆、可靠性優(yōu)先策略

主從同步是都是只讀，存在一段時間不可用

可靠性優(yōu)先策略

4.2盏浇、可用性優(yōu)先策略

會存在數(shù)據(jù)不一致的情況

可用性優(yōu)先策略

5变丧、MHA架構(gòu)

是一套比較成熟的 MySQL 高可用方案，也是一款優(yōu)秀的故障切換和主從提升的高可用軟件绢掰。在MySQL故障切換過程中锄贷，MHA能做到在30秒之內(nèi)自動完成數(shù)據(jù)庫的故障切換操作，并且在進(jìn)行故障切換的過程中曼月，MHA能在最大程度上保證數(shù)據(jù)的一致性，以達(dá)到真正意義上的高可用柔昼。MHA還支持在線快速將Master切換到其他主機(jī)哑芹，通常只需0.5－2秒

MHA集群架構(gòu)

5.1、故障處理機(jī)制

1捕透、把宕機(jī)master的binlog保存下來
2聪姿、根據(jù)binlog位置點找到最新的slave
3、用最新slave的relay log修復(fù)其它slave
4乙嘀、將保存下來的binlog在最新的slave上恢復(fù)
5末购、將最新的slave提升為master
6、將其它slave重新指向新提升的master虎谢，并開啟主從復(fù)制

5.2盟榴、優(yōu)點

1、自動故障轉(zhuǎn)移快
2婴噩、主庫崩潰不存在數(shù)據(jù)一致性問題
3擎场、性能優(yōu)秀，支持半同步復(fù)制和異步復(fù)制
4几莽、一個Manager監(jiān)控節(jié)點可以監(jiān)控多個集群

6迅办、分庫分表

6.1、拆分方式

日常工作中章蚣，我們通常會同時使用兩種拆分方式站欺，垂直拆分更偏向于產(chǎn)品/業(yè)務(wù)/功能拆分的過程，在技術(shù)上我們更關(guān)注水平拆分的方案

6.1.1纤垂、垂直拆分（解決表過多或者是表字段過多問題）

垂直拆分是將表按庫進(jìn)行分離矾策，或者修改表結(jié)構(gòu)按照訪問的差異將某些列拆分出去。應(yīng)用時有垂直分庫和垂直分表兩種方式洒忧，一般談到的垂直拆分主要指的是垂直分庫蝴韭；

• 垂直分庫：采用垂直分庫，比如將用戶表和訂單表拆分到不同的數(shù)據(jù)庫中
• 垂直分表：垂直分表就是將一張表中不常用的字段拆分到另一張表中熙侍，從而保證第一張表中的字段較少榄鉴，避免出現(xiàn)數(shù)據(jù)庫跨頁存儲的問題履磨，從而提升查詢效率
• 優(yōu)點：

1、拆分后業(yè)務(wù)清晰庆尘，拆分規(guī)則明確剃诅；
2、易于數(shù)據(jù)的維護(hù)和擴(kuò)展驶忌；
3矛辕、可以使得行數(shù)據(jù)變小，一個數(shù)據(jù)塊 (Block) 就能存放更多的數(shù)據(jù)付魔，在查詢時就會減少 I/O 次數(shù)聊品；
4、可以達(dá)到最大化利用 Cache 的目的几苍，具體在垂直拆分的時候可以將不常變的字段放一起翻屈，將經(jīng)常改變的放一起；
5妻坝、便于實現(xiàn)冷熱分離的數(shù)據(jù)表設(shè)計模式

• 缺點：

1伸眶、主鍵出現(xiàn)冗余，需要管理冗余列刽宪；
2厘贼、會引起表連接 JOIN 操作，可以通過在業(yè)務(wù)服務(wù)器上進(jìn)行 join 來減少數(shù)據(jù)庫壓力圣拄，提高了系統(tǒng)的復(fù)雜度嘴秸；
3、依然存在單表數(shù)據(jù)量過大的問題售担；
4赁遗、事務(wù)處理復(fù)雜

6.2、水平拆分（解決表中記錄過多問題）

根據(jù)某種規(guī)則將數(shù)據(jù)分散至多個庫或表中族铆，每個表僅包含數(shù)據(jù)的一部分

• 優(yōu)點：

1岩四、拆分規(guī)則設(shè)計好，join 操作基本可以數(shù)據(jù)庫做哥攘；
2剖煌、不存在單庫大數(shù)據(jù)，高并發(fā)的性能瓶頸逝淹；
3耕姊、切分的表的結(jié)構(gòu)相同，應(yīng)用層改造較少栅葡，只需要增加路由規(guī)則即可茉兰；
4、提高了系統(tǒng)的穩(wěn)定性和負(fù)載能力

• 缺點：

1欣簇、拆分規(guī)則難以抽象规脸；
2坯约、跨庫Join性能較差；
3莫鸭、分片事務(wù)的一致性難以解決闹丐；
4、數(shù)據(jù)擴(kuò)容的難度和維護(hù)量極大被因；

6.2卿拴、主鍵策略

6.2.1、UUID

• 優(yōu)點： 可以在本地生成梨与，沒有網(wǎng)絡(luò)消耗堕花，所以生成性能高；
• 缺點： UUID比較長粥鞋，沒有規(guī)律性航徙，耗費存儲空間， 如果UUID作為數(shù)據(jù)庫主鍵陷虎，在InnoDB引擎下，UUID的無序性可能會引起數(shù)據(jù)位置頻繁變動杠袱，影響性能

6.2.2尚猿、COMB（UUID變種）

保留UUID的前10個字節(jié)，用后6個字節(jié)表示GUID生成的時間（DateTime）楣富，這樣我們將時間信息與UUID組合起來凿掂，在保留UUID的唯一性的同時增加了有序性，以此來提高索引效率纹蝴。解決UUID無序的問題庄萎，性能優(yōu)于UUID

6.2.3、SNOWFLAKE

SnowFlake生成的ID整體上按照時間自增排序塘安，并且整個分布式系統(tǒng)內(nèi)不會產(chǎn)生ID重復(fù)糠涛，并且效率較高。經(jīng)測試SnowFlake每秒能夠產(chǎn)生26萬個ID兼犯。缺點是強(qiáng)依賴機(jī)器時鐘忍捡，如果多臺機(jī)器環(huán)境時鐘沒同步，或時鐘回?fù)芮星瑫?dǎo)致發(fā)號重復(fù)或者服務(wù)會處于不可用狀態(tài)

在這里插入圖片描述

6.2.4砸脊、Redis生成ID

假如一個集群中有5臺Redis∥诚迹可以初始化每臺Redis的值分別是1,2,3,4,5凌埂，然后步長都是5。各個Redis生成的ID為：

A：1,6,11,16,21
B：2,7,12,17,22
C：3,8,13,18,23
D：4,9,14,19,24
E：5,10,15,20,25

6.3诗芜、分片策略

6.3.1瞳抓、基于范圍分片

根據(jù)特定字段的范圍進(jìn)行拆分埃疫，比如用戶ID、訂單時間挨下、產(chǎn)品價格等

• 優(yōu)點： 新的數(shù)據(jù)可以落在新的存儲節(jié)點上熔恢，如果集群擴(kuò)容，數(shù)據(jù)無需遷移臭笆。
• 缺點： 數(shù)據(jù)熱點分布不均叙淌，數(shù)據(jù)冷熱不均勻，導(dǎo)致節(jié)點負(fù)荷不均

6.3.2愁铺、哈希取模分片

通過Hash(Key) % n就可以確定數(shù)據(jù)所在的設(shè)備編號

• 優(yōu)點： 實現(xiàn)簡單鹰霍，數(shù)據(jù)分配比較均勻灸拍，不容易出現(xiàn)冷熱不均迈窟，負(fù)荷不均的情況；
• 缺點： 擴(kuò)容時會產(chǎn)生大量的數(shù)據(jù)遷移采缚，比如從n臺設(shè)備擴(kuò)容到n+1瓶竭，絕大部分?jǐn)?shù)據(jù)需要重新分配和遷移

6.3.3督勺、一致性哈希分片

一致性Hash是將數(shù)據(jù)按照特征值映射到一個首尾相接的Hash環(huán)上，同時也將節(jié)點（按照IP地址或者機(jī)器名Hash）映射到這個環(huán)上斤贰。對于數(shù)據(jù)智哀，從數(shù)據(jù)在環(huán)上的位置開始，順時針找到的第一個節(jié)點即為數(shù)據(jù)的存儲節(jié)點

一致性哈希分片

一致性Hash在增加或者刪除節(jié)點的時候荧恍，受到影響的數(shù)據(jù)是比較有限的瓷叫，只會影響到Hash環(huán)相鄰的節(jié)點，不會發(fā)生大規(guī)模的數(shù)據(jù)遷移送巡，為了讓節(jié)點數(shù)據(jù)分配均勻摹菠，可以使用虛擬節(jié)點；