1.?MySQL的內(nèi)部組件結(jié)構(gòu)

? ? ? 大體來(lái)說(shuō)候齿，MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分

Server層

????????主要包括連接器迄靠、查詢緩存、分析器卓缰、優(yōu)化器计呈、執(zhí)行器等砰诵，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)(如日期捌显、時(shí)間茁彭、數(shù)學(xué)和加密函數(shù)等)，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)苇瓣，比如存儲(chǔ)過(guò)程尉间、觸發(fā)器、視圖等击罪。

Store層

? ? ? ?存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取哲嘲。其架構(gòu)模式是插件式的，支持 InnoDB媳禁、MyISAM眠副、Memory 等多個(gè)存儲(chǔ)引擎。現(xiàn)最常用的存儲(chǔ)引擎是 InnoDB竣稽，它從 MySQL 5.5.5 版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎囱怕。也就是說(shuō)如果我們?cè)赾reate table時(shí)不指定

表的存儲(chǔ)引擎類(lèi)型,默認(rèn)會(huì)給你設(shè)置存儲(chǔ)引擎為InnoDB。

2. 什么是bin-log歸檔毫别？

? ? ? ? 刪庫(kù)是不需要跑路的娃弓，因?yàn)槲覀兊腟QL執(zhí)行時(shí)，會(huì)將sql語(yǔ)句的執(zhí)行邏輯記錄在我們的bin-log當(dāng)中岛宦，什么是bin-log呢?

binlog是Server層實(shí)現(xiàn)的二進(jìn)制日志,他會(huì)記錄我們的cud操作台丛。Binlog有以下幾個(gè)特點(diǎn):

? ? ? ?1、Binlog在MySQL的Server層實(shí)現(xiàn)(引擎共用)

? ? ? ?2砾肺、Binlog為邏輯日志,記錄的是一條語(yǔ)句的原始邏輯

? ? ? ?3挽霉、Binlog不限大小,追加寫(xiě)入,不會(huì)覆蓋以前的日志

? ? ? ? ?如果，我們誤刪了數(shù)據(jù)庫(kù),可以使用binlog進(jìn)行歸檔!要使用binlog歸檔变汪，首先我們得記錄binlog侠坎，因此需要先開(kāi)啟MySQL的binlog功能。

3.Explain工具介紹使用

? ? ? EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語(yǔ)句裙盾，分析你的查詢語(yǔ)句或是結(jié)構(gòu)的性能瓶頸

? ? ? 在 select 語(yǔ)句之前增加 explain 關(guān)鍵字实胸，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，執(zhí)行查詢會(huì)返回執(zhí)行計(jì)劃的信息闷煤，而不是執(zhí)行這條SQL童芹。

4. 索引最佳實(shí)踐

????1.全值匹配

? ? 2.最左前綴法則（如果索引了多列，要遵守最左前綴法則鲤拿。指的是查詢從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列）

????3.不在索引列上做任何操作(計(jì)算、函數(shù)署咽、(自動(dòng)or手動(dòng))類(lèi)型轉(zhuǎn)換)近顷，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

????4.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列

????5.盡量使用覆蓋索引(只訪問(wèn)索引的查詢(索引列包含查詢列))生音，減少 select * 語(yǔ)句

? ??6.mysql在使用不等于(!=或者<>)，not in 窒升，not exists 的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描< 小于缀遍、 > 大于、 <=饱须、>= 這些域醇，mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引

????7.is null,is not null 一般情況下也無(wú)法使用索引

? ??8.like以通配符開(kāi)頭('$abc...')mysql索引失效會(huì)變成全表掃描操作

? ??9.字符串不加單引號(hào)索引失效

? ??10.少用or或in蓉媳，用它查詢時(shí)譬挚，mysql不一定使用索引，mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例酪呻、表大小等多個(gè)因素整體評(píng)估是否使用索引减宣，詳見(jiàn)范圍查詢優(yōu)化

????11.范圍查詢優(yōu)化給年齡添加單值索引? ? ?沒(méi)走索引原因:mysql內(nèi)部?jī)?yōu)化器會(huì)根據(jù)檢索比例、表大小等多個(gè)因素整體評(píng)估是否使用索引玩荠。比如這個(gè)例子漆腌，可能是由于單次數(shù)據(jù)量查詢過(guò)大導(dǎo)致優(yōu)化器最終選擇不走索? ?優(yōu)化方法:可以將大的范圍拆分成多個(gè)小范圍

5.? 優(yōu)化小結(jié)

????1、MySQL支持兩種方式的排序filesort和index阶冈，Using index是指MySQL掃描索引本身完成排序闷尿。index效率高，filesort效率低女坑。

?????2填具、order by滿足兩種情況會(huì)使用Using index。

????????1) order by語(yǔ)句使用索引最左前列堂飞。

????????2) 使用where子句與order by子句條件列組合滿足索引最左前列灌旧。

????3、盡量在索引列上完成排序绰筛，遵循索引建立(索引創(chuàng)建的順序)時(shí)的最左前綴法則枢泰。

????4、如果order by的條件不在索引列上铝噩，就會(huì)產(chǎn)生Using filesort衡蚂。

????5、能用覆蓋索引盡量用覆蓋索引

????6骏庸、group by與order by很類(lèi)似毛甲，其實(shí)質(zhì)是先排序后分組，遵照索引創(chuàng)建順序的最左前綴法則具被。對(duì)于groupby的優(yōu)化如果不需要排序的可以加上order by null禁止排序玻募。注意，where高于having一姿，能寫(xiě)在where中的限定條件就不要去having限定了七咧。

7.索引設(shè)計(jì)原則

????1跃惫、代碼先行，索引后上

????????不知大家一般是怎么給數(shù)據(jù)表建立索引的艾栋，是建完表馬上就建立索引嗎?這其實(shí)是不對(duì)的爆存，一般應(yīng)該等到主體業(yè)務(wù)功能開(kāi)發(fā)完畢，把涉及到該表相關(guān)sql都要拿出來(lái)分析之后再建立索引蝗砾。

? ? 2先较、聯(lián)合索引盡量覆蓋條件比如可以設(shè)計(jì)一個(gè)或者兩三個(gè)聯(lián)合索引(盡量少建單值索引)，讓每一個(gè)聯(lián)合索引都盡量去包含sql語(yǔ)句里的where悼粮、order by闲勺、group by的字段，還要確保這些聯(lián)合索引的字段順序盡量滿足sql查詢的最左前綴原則矮锈。

????3霉翔、不要在小基數(shù)字段上建立索引索引基數(shù)是指這個(gè)字段在表里總共有多少個(gè)不同的值，比如一張表總共100萬(wàn)行記錄苞笨，其中有個(gè)性別字段债朵，其值不是男就是女，那么該字段的基數(shù)就是2瀑凝。如果對(duì)這種小基數(shù)字段建立索引的話序芦，還不如全表掃描了，因?yàn)槟愕乃饕龢?shù)里就包含男和女兩種值粤咪，根本沒(méi)法進(jìn)行快速的二分查找谚中，那用索引就沒(méi)有太大的意義了。一般建立索引寥枝，盡量使用那些基數(shù)比較大的字段宪塔，就是值比較多的字段，那么才能發(fā)揮出B+樹(shù)快速二分查找的優(yōu)勢(shì)來(lái)囊拜。

????4某筐、長(zhǎng)字符串我們可以采用前綴索引盡量對(duì)字段類(lèi)型較小的列設(shè)計(jì)索引，比如說(shuō)什么tinyint之類(lèi)的冠跷，因?yàn)樽侄晤?lèi)型較小的話南誊，占用磁盤(pán)空間也會(huì)比較小，此時(shí)你在搜索的時(shí)候性能也會(huì)比較好一點(diǎn)蜜托。當(dāng)然抄囚，這個(gè)所謂的字段類(lèi)型小一點(diǎn)的列，也不是絕對(duì)的橄务，很多時(shí)候你就是要針對(duì)varchar(255)這種字段建立索引幔托，哪怕多占用一些磁盤(pán)空間也是有必要的。對(duì)于這種varchar(255)的大字段可能會(huì)比較占用磁盤(pán)空間蜂挪，可以稍微優(yōu)化下柑司，比如針對(duì)這個(gè)字段的前20個(gè)字符建立索引迫肖，就是說(shuō)锅劝，對(duì)這個(gè)字段里的每個(gè)值的前20個(gè)字符放在索引樹(shù)里攒驰，類(lèi)似于 KEYindex(name(20),age,position)。此時(shí)你在where條件里搜索的時(shí)候故爵，如果是根據(jù)name字段來(lái)搜索玻粪，那么此時(shí)就會(huì)先到索引樹(shù)里根據(jù)name字段的前20個(gè)字符去搜索，定位到之后前20個(gè)字符的前綴匹配的部分?jǐn)?shù)據(jù)之后诬垂，再回到聚簇索引提取出來(lái)完整的name字段值進(jìn)行比對(duì)劲室。

????????但是假如你要是order by name，那么此時(shí)你的name因?yàn)樵谒饕龢?shù)里僅僅包含了前20個(gè)字符结窘，所以這個(gè)排序是沒(méi)法用上索引的很洋， group by也是同理。所以這里大家要對(duì)前綴索引有一個(gè)了解隧枫。

5喉磁、where與order by沖突時(shí)優(yōu)先where

????????在where和order by出現(xiàn)索引設(shè)計(jì)沖突時(shí)，到底是針對(duì)where去設(shè)計(jì)索引官脓，還是針對(duì)order by設(shè)計(jì)索引?到底是讓where去用上索引协怒，還是讓order by用上索引?

????????一般這種時(shí)候往往都是讓where條件去使用索引來(lái)快速篩選出來(lái)一部分指定的數(shù)據(jù)，接著再進(jìn)行排序卑笨。因?yàn)榇蠖鄶?shù)情況基于索引進(jìn)行where篩選往往可以最快速度篩選出你要的少部分?jǐn)?shù)據(jù)孕暇，然后做排序的成本可能會(huì)小很多。

6赤兴、基于慢sql查詢做優(yōu)化

????????可以根據(jù)監(jiān)控后臺(tái)的一些慢sql妖滔，針對(duì)這些慢sql查詢做特定的索引優(yōu)化。

8. mysql的表關(guān)聯(lián)常見(jiàn)有兩種算法

????????Nested-Loop Join 算法

????????Block Nested-Loop Join 算法

????????1桶良、 嵌套循環(huán)連接 Nested-Loop Join(NLJ) 算法

????????一次一行循環(huán)地從第一張表(稱為驅(qū)動(dòng)表)中讀取行座舍，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段，根據(jù)關(guān)聯(lián)字段在另一張表(被驅(qū)動(dòng)表)里取出滿足條件的行艺普，然后取出兩張表的結(jié)果合集簸州。

? ? ? ? 2、 基于塊的嵌套循環(huán)連接 Block Nested-Loop Join(BNL)算法

? ? ? 把驅(qū)動(dòng)表的數(shù)據(jù)讀入到 join_buffer 中歧譬，然后掃描被驅(qū)動(dòng)表岸浑，把被驅(qū)動(dòng)表每一行取出來(lái)跟 join_buffer 中的數(shù)據(jù)做對(duì)比。

9.?count(*)查詢優(yōu)化

????????四個(gè)sql的執(zhí)行計(jì)劃一樣瑰步，說(shuō)明這四個(gè)sql執(zhí)行效率應(yīng)該差不多

????????字段有索引:count(*)≈count(1)>count(字段)>count(主鍵 id) //字段有索引矢洲，count(字段)統(tǒng)計(jì)走二級(jí)索引，二級(jí)索引存儲(chǔ)數(shù)據(jù)比主鍵索引少缩焦，所以count(字段)>count(主鍵 id)

????????字段無(wú)索引:count(*)≈count(1)>count(主鍵 id)>count(字段) //字段沒(méi)有索引count(字段)統(tǒng)計(jì)走不了索引读虏，count(主鍵 id)還可以走主鍵索引责静，所以count(主鍵 id)>count(字段)

????????count(1)跟count(字段)執(zhí)行過(guò)程類(lèi)似，不過(guò)count(1)不需要取出字段統(tǒng)計(jì)盖桥，就用常量1做統(tǒng)計(jì)灾螃，count(字段)還需要取字段，所以理論上count(1)比count(字段)會(huì)快一點(diǎn)揩徊。

????????count(*) 是例外腰鬼，mysql并不會(huì)把全部字段取出來(lái)，而是專(zhuān)門(mén)做了優(yōu)化塑荒，不取值熄赡，按行累加，效率很高齿税，所以不需要用count(列名)或count(常量)來(lái)替代 count(*)彼硫。

????????為什么對(duì)于count(id)，mysql最終選擇輔助索引而不是主鍵聚集索引?因?yàn)槎?jí)索引相對(duì)主鍵索引存儲(chǔ)數(shù)據(jù)更少凌箕，檢索

性能應(yīng)該更高拧篮，mysql內(nèi)部做了點(diǎn)優(yōu)化(應(yīng)該是在5.7版本才優(yōu)化)。

10.?MVCC多版本并發(fā)控制機(jī)制

????????Mysql在可重復(fù)讀隔離級(jí)別下如何保證事務(wù)較高的隔離性陌知，我們上節(jié)課給大家演示過(guò)他托，同樣的sql查詢語(yǔ)句在一個(gè)事務(wù)里多次執(zhí)行查詢結(jié)果相同，就算其它事務(wù)對(duì)數(shù)據(jù)有修改也不會(huì)影響當(dāng)前事務(wù)sql語(yǔ)句的查詢結(jié)果仆葡。

????????這個(gè)隔離性就是靠MVCC(Multi-Version Concurrency Control)機(jī)制來(lái)保證的赏参，對(duì)一行數(shù)據(jù)的讀和寫(xiě)兩個(gè)操作默認(rèn)是不會(huì)通過(guò)加鎖互斥來(lái)保證隔離性，避免了頻繁加鎖互斥沿盅，而在串行化隔離級(jí)別為了保證較高的隔離性是通過(guò)將所有操作加鎖互斥來(lái)實(shí)現(xiàn)的把篓。

????????Mysql在讀已提交和可重復(fù)讀隔離級(jí)別下都實(shí)現(xiàn)了MVCC機(jī)制。

? ? ? ?總結(jié):MVCC機(jī)制的實(shí)現(xiàn)就是通過(guò)read-view機(jī)制與undo版本鏈比對(duì)機(jī)制腰涧，使得不同的事務(wù)會(huì)根據(jù)數(shù)據(jù)版本鏈對(duì)比規(guī)則讀同一條數(shù)據(jù)在版本鏈上的不同版本數(shù)據(jù)韧掩。

11.?Innodb引擎SQL執(zhí)行的BufferPool緩存機(jī)制? ??

????????為什么Mysql不能直接更新磁盤(pán)上的數(shù)據(jù)而且設(shè)置這么一套復(fù)雜的機(jī)制來(lái)執(zhí)行SQL了?

????????因?yàn)閬?lái)一個(gè)請(qǐng)求就直接對(duì)磁盤(pán)文件進(jìn)行隨機(jī)讀寫(xiě)，然后更新磁盤(pán)文件里的數(shù)據(jù)性能可能相當(dāng)差窖铡。

????????因?yàn)榇疟P(pán)隨機(jī)讀寫(xiě)的性能是非常差的疗锐，所以直接更新磁盤(pán)文件是不能讓數(shù)據(jù)庫(kù)抗住很高并發(fā)的。

????????Mysql這套機(jī)制看起來(lái)復(fù)雜费彼，但它可以保證每個(gè)更新請(qǐng)求都是更新內(nèi)存BufferPool滑臊，然后順序?qū)懭罩疚募瑫r(shí)還能保證各種異常情況下的數(shù)據(jù)一致性箍铲。

????????更新內(nèi)存的性能是極高的雇卷，然后順序?qū)懘疟P(pán)上的日志文件的性能也是非常高的，要遠(yuǎn)高于隨機(jī)讀寫(xiě)磁盤(pán)文件。

????????正是通過(guò)這套機(jī)制关划，才能讓我們的MySQL數(shù)據(jù)庫(kù)在較高配置的機(jī)器上每秒可以抗下幾干的讀寫(xiě)請(qǐng)求小染。