MySQL十道面試題
本文公眾號(hào)來(lái)源：sowhat1412
作者：sowhat1412
本文已收錄至我的GitHub
1、SQL語(yǔ)句執(zhí)行流程
MySQL大體上可分為Server層和存儲(chǔ)引擎層兩部分。
Server層：
連接器：TCP握手后服務(wù)器來(lái)驗(yàn)證登陸用戶(hù)身份，A用戶(hù)創(chuàng)建連接后，管理員對(duì)A用戶(hù)權(quán)限修改了也不會(huì)影響到已經(jīng)創(chuàng)建的鏈接權(quán)限，必須重新登陸。查詢(xún)緩存：查詢(xún)后的結(jié)果存儲(chǔ)位置，MySQL8.0版本以后已經(jīng)取消，因?yàn)椴樵?xún)緩存失效太頻繁，得不償失。分析器：根據(jù)語(yǔ)法規(guī)則，判斷你輸入的這個(gè)SQL語(yǔ)句是否滿(mǎn)足MySQL語(yǔ)法。優(yōu)化器：多種執(zhí)行策略可實(shí)現(xiàn)目標(biāo)，系統(tǒng)自動(dòng)選擇最優(yōu)進(jìn)行執(zhí)行。執(zhí)行器：判斷是否有權(quán)限，將最終任務(wù)提交到存儲(chǔ)引擎。
存儲(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ǔ)引擎(經(jīng)常用的也是這個(gè))。
SQL執(zhí)行順序
2、BinLog、RedoLog、UndoLog
BinLog
BinLog是記錄所有數(shù)據(jù)庫(kù)表結(jié)構(gòu)變更（例如create、alter table）以及表數(shù)據(jù)修改(insert、update、delete)的二進(jìn)制日志，主從數(shù)據(jù)庫(kù)同步用到的都是BinLog文件。BinLog日志文件有三種模式。
STATEMENT 模式
內(nèi)容：binlog 只會(huì)記錄可能引起數(shù)據(jù)變更的 sql 語(yǔ)句
優(yōu)勢(shì)：該模式下，因?yàn)闆](méi)有記錄實(shí)際的數(shù)據(jù)，所以日志量和 IO 都消耗很低，性能是最優(yōu)的
劣勢(shì)：但有些操作并不是確定的，比如 uuid() 函數(shù)會(huì)隨機(jī)產(chǎn)生唯一標(biāo)識(shí)，當(dāng)依賴(lài) binlog 回放時(shí)，該操作生成的數(shù)據(jù)與原數(shù)據(jù)必然是不同的，此時(shí)可能造成無(wú)法預(yù)料的后果。
ROW 模式
內(nèi)容：在該模式下，binlog 會(huì)記錄每次操作的源數(shù)據(jù)與修改后的目標(biāo)數(shù)據(jù)，StreamSets就要求該模式。
優(yōu)勢(shì)：可以絕對(duì)精準(zhǔn)的還原，從而保證了數(shù)據(jù)的安全與可靠，并且復(fù)制和數(shù)據(jù)恢復(fù)過(guò)程可以是并發(fā)進(jìn)行的
劣勢(shì)：缺點(diǎn)在于 binlog 體積會(huì)非常大，同時(shí)，對(duì)于修改記錄多、字段長(zhǎng)度大的操作來(lái)說(shuō)，記錄時(shí)性能消耗會(huì)很?chē)?yán)重。閱讀的時(shí)候也需要特殊指令來(lái)進(jìn)行讀取數(shù)據(jù)。
MIXED 模式
內(nèi)容：是對(duì)上述STATEMENT 跟 ROW ?兩種模式的混合使用。
細(xì)節(jié)：對(duì)于絕大部分操作，都使用 STATEMENT 來(lái)進(jìn)行 binlog 的記錄，只有以下操作使用 ROW 來(lái)實(shí)現(xiàn)：表的存儲(chǔ)引擎為 NDB，使用了uuid() 等不確定函數(shù)，使用了 insert delay 語(yǔ)句，使用了臨時(shí)表
主從同步流程：
1、主節(jié)點(diǎn)必須啟用二進(jìn)制日志，記錄任何修改了數(shù)據(jù)庫(kù)數(shù)據(jù)的事件。
2、從節(jié)點(diǎn)開(kāi)啟一個(gè)線程（I/O Thread)把自己扮演成 mysql 的客戶(hù)端，通過(guò) mysql 協(xié)議，請(qǐng)求主節(jié)點(diǎn)的二進(jìn)制日志文件中的事件 。
3、主節(jié)點(diǎn)啟動(dòng)一個(gè)線程（dump Thread），檢查自己二進(jìn)制日志中的事件，跟對(duì)方請(qǐng)求的位置對(duì)比，如果不帶請(qǐng)求位置參數(shù)，則主節(jié)點(diǎn)就會(huì)從第一個(gè)日志文件中的第一個(gè)事件一個(gè)一個(gè)發(fā)送給從節(jié)點(diǎn)。
4、從節(jié)點(diǎn)接收到主節(jié)點(diǎn)發(fā)送過(guò)來(lái)的數(shù)據(jù)把它放置到中繼日志（Relay log）文件中。并記錄該次請(qǐng)求到主節(jié)點(diǎn)的具體哪一個(gè)二進(jìn)制日志文件內(nèi)部的哪一個(gè)位置（主節(jié)點(diǎn)中的二進(jìn)制文件會(huì)有多個(gè)）。
5、從節(jié)點(diǎn)啟動(dòng)另外一個(gè)線程（sql Thread ），把 Relay log 中的事件讀取出來(lái)，并在本地再執(zhí)行一次。
mysql默認(rèn)的復(fù)制方式是異步的，并且復(fù)制的時(shí)候是有并行復(fù)制能力的。主庫(kù)把日志發(fā)送給從庫(kù)后不管了，這樣會(huì)產(chǎn)生一個(gè)問(wèn)題就是假設(shè)主庫(kù)掛了，從庫(kù)處理失敗了，這時(shí)候從庫(kù)升為主庫(kù)后，日志就丟失了。由此產(chǎn)生兩個(gè)概念。
全同步復(fù)制
主庫(kù)寫(xiě)入binlog后強(qiáng)制同步日志到從庫(kù)，所有的從庫(kù)都執(zhí)行完成后才返回給客戶(hù)端，但是很顯然這個(gè)方式的話性能會(huì)受到嚴(yán)重影響。
半同步復(fù)制
半同步復(fù)制的邏輯是這樣，從庫(kù)寫(xiě)入日志成功后返回
ACK確認(rèn)給主庫(kù)，主庫(kù)收到至少一個(gè)從庫(kù)的確認(rèn)就認(rèn)為寫(xiě)操作完成。
還可以延伸到由于主從配置不一樣、主庫(kù)大事務(wù)、從庫(kù)壓力過(guò)大、網(wǎng)絡(luò)震蕩等造成主備延遲，如何避免這個(gè)問(wèn)題？主備切換的時(shí)候用可靠性?xún)?yōu)先原則還是可用性?xún)?yōu)先原則？如何判斷主庫(kù)Crash了？互為主備情況下如何避免主備循環(huán)復(fù)制？被刪庫(kù)跑路了如何正確恢復(fù)？(⊙o⊙)… 感覺(jué)越來(lái)越扯到DBA的活兒上去了。
RedoLog
可以先通過(guò)下面demo理解：
飯點(diǎn)記賬可以把賬單寫(xiě)在賬本上也可以寫(xiě)在粉板上。有人賒賬或者還賬的話，一般有兩種做法：
1、直接把賬本翻出來(lái)，把這次賒的賬加上去或者扣除掉。
2、先在粉板上記下這次的賬，等打烊以后再把賬本翻出來(lái)核算。
生意忙時(shí)選后者，因?yàn)榍罢咛闊┝恕５迷诿苊苈槁榈挠涗浿姓业竭@個(gè)人的賒賬總額信息，找到之后再拿出算盤(pán)計(jì)算，最后再將結(jié)果寫(xiě)回到賬本上。
同樣在MySQL中如果每一次的更新操作都需要寫(xiě)進(jìn)磁盤(pán)，然后磁盤(pán)也要找到對(duì)應(yīng)的那條記錄，然后再更新，整個(gè)過(guò)程IO成本、查找成本都很高。而粉板和賬本配合的整個(gè)過(guò)程就是MySQL用到的是Write-Ahead Logging 技術(shù)，它的關(guān)鍵點(diǎn)就是先寫(xiě)日志，再寫(xiě)磁盤(pán)。此時(shí)賬本 = BinLog，粉板 = RedoLog。
1、 記錄更新時(shí)，InnoDB引擎就會(huì)先把記錄寫(xiě)到RedoLog（粉板）里面，并更新內(nèi)存。同時(shí)，InnoDB引擎會(huì)在空閑時(shí)將這個(gè)操作記錄更新到磁盤(pán)里面。
2、 如果更新太多RedoLog處理不了的時(shí)候，需先將RedoLog部分?jǐn)?shù)據(jù)寫(xiě)到磁盤(pán)，然后擦除RedoLog部分?jǐn)?shù)據(jù)。RedoLog類(lèi)似轉(zhuǎn)盤(pán)。
RedoLog有write pos 跟checkpoint
write pos：是當(dāng)前記錄的位置，一邊寫(xiě)一邊后移，寫(xiě)到第3號(hào)文件末尾后就回到0號(hào)文件開(kāi)頭。
check point：是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。
write pos和check point之間的是粉板上還空著的部分，可以用來(lái)記錄新的操作。如果write pos追上checkpoint，表示粉板滿(mǎn)了，這時(shí)候不能再執(zhí)行新的更新，得停下來(lái)先擦掉一些記錄，把checkpoint推進(jìn)一下。
有了redo log，InnoDB就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱(chēng)為crash-safe。
redolog兩階段提交：為了讓binlog跟redolog兩份日志之間的邏輯一致。提交流程大致如下：
1 prepare階段 --> ?2 寫(xiě)binlog ?--> 3 commit
當(dāng)在2之前崩潰時(shí)，重啟恢復(fù)后發(fā)現(xiàn)沒(méi)有commit，回滾。備份恢復(fù)：沒(méi)有binlog 。一致 當(dāng)在3之前崩潰時(shí)，重啟恢復(fù)發(fā)現(xiàn)雖沒(méi)有commit，但滿(mǎn)足prepare和binlog完整，所以重啟后會(huì) 自動(dòng)commit。備份：有binlog. 一致
binlog跟redolog區(qū)別：
redo log是InnoDB引擎特有的；binlog是MySQL的Server層實(shí)現(xiàn)的，所有引擎都可以使用。 redo log是物理日志，記錄的是在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改；binlog是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如給ID=2這一行的c字段加1。 redo log是循環(huán)寫(xiě)的，空間固定會(huì)用完；binlog是可以追加寫(xiě)入的。追加寫(xiě)是指binlog文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。
UndoLog
UndoLog 一般是邏輯日志，主要分為兩種：
insert undo log
代表事務(wù)在insert新記錄時(shí)產(chǎn)生的undo log, 只在事務(wù)回滾時(shí)需要，并且在事務(wù)提交后可以被立即丟棄
update undo log
事務(wù)在進(jìn)行update或delete時(shí)產(chǎn)生的undo log; 不僅在事務(wù)回滾時(shí)需要，在快照讀時(shí)也需要；所以不能隨便刪除，只有在快速讀或事務(wù)回滾不涉及該日志時(shí)，對(duì)應(yīng)的日志才會(huì)被purge線程統(tǒng)一清除
3、MySQL中的索引
索引的常見(jiàn)模型有哈希表、有序數(shù)組和搜索樹(shù)。
哈希表：一種以KV存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，只適合等值查詢(xún)，不適合范圍查詢(xún)。
有序數(shù)組：只適用于靜態(tài)存儲(chǔ)引擎，涉及到插入的時(shí)候比較麻煩。可以參考Java中的ArrayList。
搜索樹(shù)：按照數(shù)據(jù)結(jié)構(gòu)中的二叉樹(shù)來(lái)存儲(chǔ)數(shù)據(jù)，不過(guò)此時(shí)是N叉樹(shù)(B+樹(shù))。廣泛應(yīng)用在存儲(chǔ)引擎層中。
B+樹(shù)比B樹(shù)優(yōu)勢(shì)在于：
B+ 樹(shù)非葉子節(jié)點(diǎn)存儲(chǔ)的只是索引，可以存儲(chǔ)的更多。B+樹(shù)比B樹(shù)更加矮胖，IO次數(shù)更少。 B+ 樹(shù)葉子節(jié)點(diǎn)前后管理，更加方便范圍查詢(xún)。同時(shí)結(jié)果都在葉子節(jié)點(diǎn)，查詢(xún)效率穩(wěn)定。 B+樹(shù)中更有利于對(duì)數(shù)據(jù)掃描，可以避免B樹(shù)的回溯掃描。
索引的優(yōu)點(diǎn)：
1、唯一索引可以保證每一行數(shù)據(jù)的唯一性?
2、提高查詢(xún)速度?
3、加速表與表的連接?
4、顯著的減少查詢(xún)中分組和排序的時(shí)間
5、通過(guò)使用索引，可以在查詢(xún)的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
索引的缺點(diǎn)：
1、創(chuàng)建跟維護(hù)都需要耗時(shí)?
2、創(chuàng)建索引時(shí)，需要對(duì)表加鎖，在鎖表的同時(shí)，可能會(huì)影響到其他的數(shù)據(jù)操作?
3、 索引需要磁盤(pán)的空間進(jìn)行存儲(chǔ)，磁盤(pán)占用也很快。
4、當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行CRUD的時(shí)，也會(huì)觸發(fā)索引的維護(hù)，而維護(hù)索引需要時(shí)間，可能會(huì)降低數(shù)據(jù)操作性能
索引設(shè)計(jì)的原則不應(yīng)該：
1、索引不是越多越好。索引太多，維護(hù)索引需要時(shí)間跟空間。
2、 頻繁更新的數(shù)據(jù)，不宜建索引。
3、數(shù)據(jù)量小的表沒(méi)必要建立索引。
應(yīng)該：
1、重復(fù)率小的列建議生成索引。因?yàn)橹貜?fù)數(shù)據(jù)少，索引樹(shù)查詢(xún)更有效率，等價(jià)基數(shù)越大越好。
2、數(shù)據(jù)具有唯一性，建議生成唯一性索引。在數(shù)據(jù)庫(kù)的層面，保證數(shù)據(jù)正確性?
3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率?
4、經(jīng)常用于查詢(xún)條件的字段建議生成索引。通過(guò)索引查詢(xún)，速度更快
索引失效的場(chǎng)景
1、
模糊搜索：左模糊或全模糊都會(huì)導(dǎo)致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。2、
隱式類(lèi)型轉(zhuǎn)換：比如select * from t where name = xxx , name是字符串類(lèi)型，但是沒(méi)有加引號(hào)，所以是由MySQL隱式轉(zhuǎn)換的，所以會(huì)讓索引失效 3、當(dāng)語(yǔ)句中帶有or的時(shí)候：比如select * from t where name=‘sw’ or age=144、
不符合聯(lián)合索引的最左前綴匹配：(A,B,C)的聯(lián)合索引，你只where了C或B或只有B,C
關(guān)于索引的知識(shí)點(diǎn)：
主鍵索引：主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)信息。在InnoDB里，主鍵索引也被稱(chēng)為聚簇索引（clustered index）。主鍵自增是無(wú)法保證完全自增的哦，遇到唯一鍵沖突、事務(wù)回滾等都可能導(dǎo)致不連續(xù)。
唯一索引：以唯一列生成的索引，該列不允許有重復(fù)值，但允許有空值(NULL)
普通索引跟唯一索引查詢(xún)性能：InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來(lái)讀寫(xiě)的，默認(rèn)每頁(yè)16KB，因此這兩種索引查詢(xún)數(shù)據(jù)性能差別微乎其微。
change buffer：普通索引用在更新過(guò)程的加速，更新的字段如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數(shù)據(jù)讀入內(nèi)存來(lái)確保不違背唯一性，所以盡量用普通索引。
非主鍵索引：非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在InnoDB里，非主鍵索引也被稱(chēng)為二級(jí)索引（secondary index）
回表：先通過(guò)數(shù)據(jù)庫(kù)索引掃描出數(shù)據(jù)所在的行，再通過(guò)行主鍵id取出索引中未提供的數(shù)據(jù)，即基于非主鍵索引的查詢(xún)需要多掃描一棵索引樹(shù)。
覆蓋索引：如果一個(gè)索引包含（或者說(shuō)覆蓋）所有需要查詢(xún)的字段的值，我們就稱(chēng)之為覆蓋索引。
聯(lián)合索引：相對(duì)單列索引，組合索引是用多個(gè)列組合構(gòu)建的索引，一次性最多聯(lián)合16個(gè)。
最左前綴原則：對(duì)多個(gè)字段同時(shí)建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯(lián)合索引) 以聯(lián)合索引(a,b,c)為例，建立這樣的索引相當(dāng)于建立了索引a、ab、abc三個(gè)索引。另外組合索引實(shí)際還是一個(gè)索引，并非真的創(chuàng)建了多個(gè)索引，只是產(chǎn)生的效果等價(jià)于產(chǎn)生多個(gè)索引。
索引下推：MySQL 5.6引入了索引下推優(yōu)化，可以在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，過(guò)濾掉不符合條件的記錄，減少回表字?jǐn)?shù)。
索引維護(hù)：B+樹(shù)為了維護(hù)索引有序性涉及到頁(yè)分裂跟頁(yè)合并。增刪數(shù)據(jù)時(shí)需考慮頁(yè)空間利用率。
自增主鍵：一般會(huì)建立與業(yè)務(wù)無(wú)關(guān)的自增主鍵，不會(huì)觸發(fā)葉子節(jié)點(diǎn)分裂。
延遲關(guān)聯(lián)：通過(guò)使用覆蓋索引查詢(xún)返回需要的主鍵，再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。
InnoDB存儲(chǔ): * .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫(kù)表是一張?jiān)趺礃拥谋怼?code style="font-size: 14px;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(40, 202, 113);">*.ibd文件則是該表的索引，數(shù)據(jù)存儲(chǔ)文件，既該表的所有索引樹(shù)，所有行記錄數(shù)據(jù)都存儲(chǔ)在該文件中。
MyISAM存儲(chǔ)：* .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫(kù)表是一張?jiān)趺礃拥谋怼?code style="font-size: 14px;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(40, 202, 113);">* .MYD文件是MyISAM存儲(chǔ)引擎表的所有行數(shù)據(jù)的文件。* .MYI文件存放的是MyISAM存儲(chǔ)引擎表的索引相關(guān)數(shù)據(jù)的文件。MyISAM引擎下，表數(shù)據(jù)和表索引數(shù)據(jù)是分開(kāi)存儲(chǔ)的。
MyISAM查詢(xún)：在MyISAM下，主鍵索引和輔助鍵索引都屬于非聚簇索引。查詢(xún)不管是走主鍵索引，還是非主鍵索引，在葉子結(jié)點(diǎn)得到的都是目的數(shù)據(jù)的地址，還需要通過(guò)該地址，才能在數(shù)據(jù)文件中找到目的數(shù)據(jù)。
PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引
4、SQL事務(wù)隔離級(jí)別
ACID的四個(gè)特性
原子性（Atomicity）：把多個(gè)操作放到一個(gè)事務(wù)中，保證這些操作要么都成功，要么都不成功一致性（Consistency）：理解成一串對(duì)數(shù)據(jù)進(jìn)行操作的程序執(zhí)行下來(lái)，不會(huì)對(duì)數(shù)據(jù)產(chǎn)生不好的影響，比如憑空產(chǎn)生，或消失隔離性（Isolation，又稱(chēng)獨(dú)立性）：隔離性的意思就是多個(gè)事務(wù)之間互相不干擾，即使是并發(fā)事務(wù)的情況下，他們只是兩個(gè)并發(fā)執(zhí)行沒(méi)有交集，互不影響的東西；當(dāng)然實(shí)現(xiàn)中，也不一定需要這么完整隔離性，即不一定需要這么的互不干擾，有時(shí)候還是允許有部分干擾的。所以MySQL可以支持4種事務(wù)隔離性持久性（Durability）：當(dāng)某個(gè)操作操作完畢了，那么結(jié)果就是這樣了，并且這個(gè)操作會(huì)持久化到日志記錄中
PS：ACID中C與CAP定理中C的區(qū)別
ACID的C著重強(qiáng)調(diào)單數(shù)據(jù)庫(kù)事務(wù)操作時(shí)，要保證數(shù)據(jù)的完整和正確性，數(shù)據(jù)不會(huì)憑空消失跟增加。CAP 理論中的C指的是對(duì)一個(gè)數(shù)據(jù)多個(gè)備份的讀寫(xiě)一致性
事務(wù)操作可能會(huì)出現(xiàn)的數(shù)據(jù)問(wèn)題
1、
臟讀(dirty read)：B事務(wù)更改數(shù)據(jù)還未提交，A事務(wù)已經(jīng)看到并且用了。B事務(wù)如果回滾，則A事務(wù)做錯(cuò)了?2、
不可重復(fù)讀(non-repeatable read)：不可重復(fù)讀的重點(diǎn)是修改: 同樣的條件, 你讀取過(guò)的數(shù)據(jù), 再次讀取出來(lái)發(fā)現(xiàn)值不一樣了，只需要鎖住滿(mǎn)足條件的記錄?3、
幻讀(phantom read)：事務(wù)A先修改了某個(gè)表的所有紀(jì)錄的狀態(tài)字段為已處理，未提交；事務(wù)B也在此時(shí)新增了一條未處理的記錄，并提交了；事務(wù)A隨后查詢(xún)記錄，卻發(fā)現(xiàn)有一條記錄是未處理的造成幻讀現(xiàn)象，幻讀僅專(zhuān)指新插入的行。幻讀會(huì)造成語(yǔ)義上的問(wèn)題跟數(shù)據(jù)一致性問(wèn)題。4、 在可重復(fù)讀RR隔離級(jí)別下，普通查詢(xún)是
快照讀，是不會(huì)看到別的事務(wù)插入的數(shù)據(jù)的。因此，幻讀在當(dāng)前讀下才會(huì)出現(xiàn)。要用間隙鎖解決此問(wèn)題。
在說(shuō)隔離級(jí)別之前，你首先要知道，你隔離得越嚴(yán)實(shí)，效率就會(huì)越低。因此很多時(shí)候，我們都要在二者之間尋找一個(gè)平衡點(diǎn)。SQL標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別由低到高如下：
上圖從上到下的模式會(huì)導(dǎo)致系統(tǒng)的并行性能依次降低，安全性依次提高。
讀未提交：別人改數(shù)據(jù)的事務(wù)尚未提交，我在我的事務(wù)中也能讀到。
讀已提交(Oracle默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中才能讀到。
可重復(fù)讀(MySQL默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中也不去讀，以此保證重復(fù)讀一致性。
串行：我的事務(wù)尚未提交，別人就別想改數(shù)據(jù)。
標(biāo)準(zhǔn)跟實(shí)現(xiàn)：上面都是關(guān)于事務(wù)的標(biāo)準(zhǔn)，但是每一種數(shù)據(jù)庫(kù)都有不同的實(shí)現(xiàn)，比如MySQL InnDB 默認(rèn)為RR級(jí)別，但是不會(huì)出現(xiàn)幻讀。因?yàn)楫?dāng)事務(wù)A更新了所有記錄的某個(gè)字段，此時(shí)事務(wù)A會(huì)獲得對(duì)這個(gè)表的表鎖，因?yàn)槭聞?wù)A還沒(méi)有提交，所以事務(wù)A獲得的鎖沒(méi)有釋放，此時(shí)事務(wù)B在該表插入新記錄，會(huì)因?yàn)闊o(wú)法獲得該表的鎖，則導(dǎo)致插入操作被阻塞。只有事務(wù)A提交了事務(wù)后，釋放了鎖，事務(wù)B才能進(jìn)行接下去的操作。所以可以說(shuō) ? MySQL的RR級(jí)別的隔離是已經(jīng)實(shí)現(xiàn)解決了臟讀，不可重復(fù)讀和幻讀的。
5、MySQL中的鎖
無(wú)論是Java的并發(fā)編程還是數(shù)據(jù)庫(kù)的并發(fā)操作都會(huì)涉及到鎖，研發(fā)人員引入了悲觀鎖跟樂(lè)觀鎖這樣一種鎖的設(shè)計(jì)思想。
悲觀鎖：
優(yōu)點(diǎn)：適合在寫(xiě)多讀少的并發(fā)環(huán)境中使用，雖然無(wú)法維持非常高的性能，但是在樂(lè)觀鎖無(wú)法提更好的性能前提下，可以做到數(shù)據(jù)的安全性
缺點(diǎn)：加鎖會(huì)增加系統(tǒng)開(kāi)銷(xiāo)，雖然能保證數(shù)據(jù)的安全，但數(shù)據(jù)處理吞吐量低，不適合在讀書(shū)寫(xiě)少的場(chǎng)合下使用
樂(lè)觀鎖：
優(yōu)點(diǎn)：在讀多寫(xiě)少的并發(fā)場(chǎng)景下，可以避免數(shù)據(jù)庫(kù)加鎖的開(kāi)銷(xiāo)，提高DAO層的響應(yīng)性能，很多情況下ORM工具都有帶有樂(lè)觀鎖的實(shí)現(xiàn)，所以這些方法不一定需要我們?nèi)藶榈娜?shí)現(xiàn)。
缺點(diǎn)：在寫(xiě)多讀少的并發(fā)場(chǎng)景下，即在寫(xiě)操作競(jìng)爭(zhēng)激烈的情況下，會(huì)導(dǎo)致CAS多次重試，沖突頻率過(guò)高，導(dǎo)致開(kāi)銷(xiāo)比悲觀鎖更高。
實(shí)現(xiàn)：數(shù)據(jù)庫(kù)層面的樂(lè)觀鎖其實(shí)跟CAS思想類(lèi)似， 通數(shù)據(jù)版本號(hào)或者時(shí)間戳也可以實(shí)現(xiàn)。
數(shù)據(jù)庫(kù)并發(fā)場(chǎng)景主要有三種：
讀-讀：不存在任何問(wèn)題，也不需要并發(fā)控制
讀-寫(xiě)：有隔離性問(wèn)題，可能遇到臟讀，幻讀，不可重復(fù)讀
寫(xiě)-寫(xiě)：可能存更新丟失問(wèn)題，比如第一類(lèi)更新丟失，第二類(lèi)更新丟失
兩類(lèi)更新丟失問(wèn)題：
第一類(lèi)更新丟失：事務(wù)A的事務(wù)回滾覆蓋了事務(wù)B已提交的結(jié)果 第二類(lèi)更新丟失：事務(wù)A的提交覆蓋了事務(wù)B已提交的結(jié)果
為了合理貫徹落實(shí)鎖的思想，MySQL中引入了雜七雜八的各種鎖：
鎖分類(lèi)
MySQL支持三種層級(jí)的鎖定，分別為
表級(jí)鎖定
MySQL中鎖定粒度
最大的一種鎖，最常使用的MYISAM與INNODB都支持表級(jí)鎖定。
頁(yè)級(jí)鎖定
是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間的一種鎖，表級(jí)鎖速度快，但沖突多，行級(jí)沖突少，但速度慢。所以取了
折衷的頁(yè)級(jí)，一次鎖定相鄰的一組記錄。
行級(jí)鎖定
Mysql中鎖定粒度
最細(xì)的一種鎖，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖。行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖突。其加鎖粒度最小，但加鎖的開(kāi)銷(xiāo)也最大行級(jí)鎖不一定比表級(jí)鎖要好：鎖的粒度越細(xì)，代價(jià)越高，相比表級(jí)鎖在表的頭部直接加鎖，行級(jí)鎖還要掃描找到對(duì)應(yīng)的行對(duì)其上鎖，這樣的代價(jià)其實(shí)是比較高的，所以表鎖和行鎖各有所長(zhǎng)。
MyISAM中的鎖
雖然MySQL支持表，頁(yè)，行三級(jí)鎖定，但MyISAM存儲(chǔ)引擎只支持表鎖。所以MyISAM的加鎖相對(duì)比較開(kāi)銷(xiāo)低，但數(shù)據(jù)操作的并發(fā)性能相對(duì)就不高。但如果寫(xiě)操作都是尾插入，那還是可以支持一定程度的讀寫(xiě)并發(fā)
從MyISAM所支持的鎖中也可以看出，MyISAM是一個(gè)支持讀讀并發(fā)，但不支持通用讀寫(xiě)并發(fā)，寫(xiě)寫(xiě)并發(fā)的數(shù)據(jù)庫(kù)引擎，所以它更適合用于讀多寫(xiě)少的應(yīng)用場(chǎng)合，一般工程中也用的較少。
InnoDB中的鎖
該模式下支持的鎖實(shí)在是太多了，具體如下：
共享鎖和排他鎖 (Shared and Exclusive Locks)
意向鎖（Intention Locks）?
記錄鎖（Record Locks）?
間隙鎖（Gap Locks）?
臨鍵鎖 （Next-Key Locks）?
插入意向鎖（Insert Intention Locks）?
主鍵自增鎖 (AUTO-INC Locks)?
空間索引斷言鎖（Predicate Locks for Spatial Indexes）
舉個(gè)栗子，比如行鎖里的共享鎖跟排它鎖：lock in share modle 共享讀鎖：
為了確保自己查到的數(shù)據(jù)沒(méi)有被其他的事務(wù)正在修改，也就是說(shuō)確保查到的數(shù)據(jù)是
最新的數(shù)據(jù)，并且不允許其他人來(lái)修改數(shù)據(jù)。但是自己不一定能夠修改數(shù)據(jù)，因?yàn)橛锌赡芷渌氖聞?wù)也對(duì)這些數(shù)據(jù)使用了in share mode的方式上了S鎖。如果不及時(shí)的commit 或者rollback 也可能會(huì)造成大量的事務(wù)等待。
for update排它寫(xiě)鎖:
為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù)，并且查到后的數(shù)據(jù)只允許自己來(lái)修改的時(shí)候，需要用到
for update。相當(dāng)于一個(gè) update 語(yǔ)句。在業(yè)務(wù)繁忙的情況下，如果事務(wù)沒(méi)有及時(shí)的commit或者rollback 可能會(huì)造成其他事務(wù)長(zhǎng)時(shí)間的等待，從而影響數(shù)據(jù)庫(kù)的并發(fā)使用效率。
Gap Lock間隙鎖：
1、行鎖只能鎖住行，如果在記錄之間的間隙插入數(shù)據(jù)就無(wú)法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是
左右開(kāi)區(qū)間。間隙鎖之間不會(huì)沖突。2、間隙鎖和行鎖合稱(chēng)
NextKeyLock，每個(gè)NextKeyLock是前開(kāi)后閉區(qū)間。
間隙鎖加鎖原則(學(xué)完忘那種)：
1、加鎖的基本單位是 NextKeyLock，是前開(kāi)后閉區(qū)間。
2、查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。
3、索引上的等值查詢(xún)，給
唯一索引加鎖的時(shí)候，NextKeyLock退化為行鎖。4、索引上的等值查詢(xún)，向右遍歷時(shí)且最后一個(gè)值不滿(mǎn)足等值條件的時(shí)候，NextKeyLock退化為間隙鎖。
5、唯一索引上的范圍查詢(xún)會(huì)訪問(wèn)到不滿(mǎn)足條件的第一個(gè)值為止。
6、MVCC
MVCC：
1、全稱(chēng)
Multi-Version Concurrency Control，即多版本并發(fā)控制。MVCC是一種并發(fā)控制的理念，維持一個(gè)數(shù)據(jù)的多個(gè)版本，使得讀寫(xiě)操作沒(méi)有沖突。2、MVCC在MySQL InnoDB中實(shí)現(xiàn)目的主要是為了提高數(shù)據(jù)庫(kù)并發(fā)性能，用更好的方式去處理讀-寫(xiě)沖突，做到即使有讀寫(xiě)沖突時(shí)，也能做到不加鎖，非阻塞并發(fā)讀。
MySQL InnoDB下的當(dāng)前讀和快照讀
當(dāng)前讀
1、像
select lock in share mode(共享鎖)、select for update 、update、insert、delete(排他鎖)這些操作都是一種當(dāng)前讀，就是它讀取的是記錄的最新版本，讀取時(shí)還要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄，會(huì)對(duì)讀取的記錄進(jìn)行加鎖。2、當(dāng)前讀可以認(rèn)為是
悲觀鎖的具體功能實(shí)現(xiàn)
快照讀
1、不加鎖的select就是快照讀，即不加鎖的非阻塞讀；快照讀的前提是隔離級(jí)別不是串行級(jí)別，串行級(jí)別下的快照讀會(huì)退化成當(dāng)前讀；之所以出現(xiàn)快照讀的情況，是基于提高并發(fā)性能的考慮，快照讀的實(shí)現(xiàn)是基于多版本并發(fā)控制，即
MVCC，可以認(rèn)為MVCC是行鎖的一個(gè)變種，但它在很多情況下，避免了加鎖操作，降低了開(kāi)銷(xiāo)；既然是基于多版本，即快照讀可能讀到的并不一定是數(shù)據(jù)的最新版本，而有可能是之前的歷史版本。2、快照讀就是MVCC思想在MySQL的具體非阻塞讀功能實(shí)現(xiàn)，MVCC的目的就是為了實(shí)現(xiàn)讀-寫(xiě)沖突不加鎖，提高并發(fā)讀寫(xiě)性能，而這個(gè)讀指的就是
快照讀。3、快照讀就是MySQL為我們實(shí)現(xiàn)MVCC理想模型的其中一個(gè)具體非阻塞讀功能。
因?yàn)榇罄胁粷M(mǎn)意只讓數(shù)據(jù)庫(kù)采用悲觀鎖這樣性能不佳的形式去解決讀-寫(xiě)沖突問(wèn)題，而提出了MVCC，所以我們可以形成兩個(gè)組合：
MVCC + 悲觀鎖：MVCC解決讀寫(xiě)沖突，悲觀鎖解決寫(xiě)寫(xiě)沖突
MVCC + 樂(lè)觀鎖：MVCC解決讀寫(xiě)沖突，樂(lè)觀鎖解決寫(xiě)寫(xiě)沖突
MVCC的實(shí)現(xiàn)原理
MVCC實(shí)現(xiàn)原理主要是依賴(lài)記錄中的 四個(gè)隱式字段、undo日志 、Consistent Read View來(lái)實(shí)現(xiàn)的。
四個(gè)隱式字段：
DB_TRX_ID：
6byte，最近修改(修改/插入)事務(wù)ID：記錄創(chuàng)建這條記錄/最后一次修改該記錄的
事務(wù)ID
DB_ROLL_PTR
7byte，回滾指針，指向這條記錄的
上一個(gè)版本（存儲(chǔ)于rollback segment里）
DB_ROW_ID
6byte，隱含的自增ID（
隱藏主鍵），如果數(shù)據(jù)表沒(méi)有主鍵，InnoDB會(huì)自動(dòng)以DB_ROW_ID產(chǎn)生一個(gè)聚簇索引
FLAG
一個(gè)刪除flag隱藏字段, 既記錄被更新或刪除并不代表真的刪除，而是刪除flag變了
事務(wù)對(duì)一條記錄的修改，會(huì)導(dǎo)致該記錄的undo log成為一條記錄版本線性表(鏈表)，undo log的鏈?zhǔn)拙褪亲钚碌呐f記錄，鏈尾就是最早的舊記錄。
undo日志：此知識(shí)點(diǎn)上文已經(jīng)說(shuō)過(guò)了，對(duì)MVCC有幫助的實(shí)質(zhì)是update undo log，undo log實(shí)際上就是存在rollback segment中舊記錄鏈。
一致讀視圖 Consistent Read View：Read View是事務(wù)進(jìn)行快照讀操作的時(shí)候生產(chǎn)的讀視圖(Read View)，在該事務(wù)執(zhí)行的快照讀的那一刻，會(huì)生成數(shù)據(jù)庫(kù)系統(tǒng)當(dāng)前的一個(gè)快照，記錄并維護(hù)系統(tǒng)當(dāng)前活躍事務(wù)的ID(InnoDB里面每個(gè)事務(wù)有一個(gè)唯一的事務(wù)ID，叫作transaction id。它是在事務(wù)開(kāi)始的時(shí)候向InnoDB的事務(wù)系統(tǒng)申請(qǐng)的，是按申請(qǐng)順序嚴(yán)格遞增的)。拿著這個(gè)ID跟記錄中ID對(duì)比進(jìn)行選擇性展示，這里說(shuō)下大致的思維。
你可以簡(jiǎn)單的理解為MVCC為每一行增加了兩個(gè)隱藏字段，兩個(gè)字段分別保存了這個(gè)行的當(dāng)前事務(wù)ID跟行的刪除事務(wù)ID。
insert時(shí)：
InnoDB為新插入的每一行保存當(dāng)前系統(tǒng)版本號(hào)作為版本號(hào)。
select時(shí)：
1、 InnoDB只會(huì)查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行(也就是行的系統(tǒng)版本號(hào)
<=事務(wù)的系統(tǒng)版本號(hào))，這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開(kāi)始前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過(guò)的。2、行的刪除版本要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)，這可以確保事務(wù)讀取到的行在事務(wù)開(kāi)始之前未被刪除。
3、只有1，2 同時(shí)滿(mǎn)足的記錄，才能返回作為查詢(xún)結(jié)果。
delete時(shí)：
InnoDB會(huì)為刪除的每一行保存當(dāng)前系統(tǒng)的版本號(hào)(事務(wù)的ID)作為刪除標(biāo)識(shí).
update時(shí)：
InnoDB執(zhí)行update，實(shí)際上是新插入了一行記錄，并保存其創(chuàng)建時(shí)間為當(dāng)前事務(wù)的ID，同時(shí)保存當(dāng)前事務(wù)ID到要update的行的刪除時(shí)間。
上面只是一個(gè)淺顯的講解MVCC選擇標(biāo)準(zhǔn)流程，源碼層面應(yīng)該是根據(jù)低水位跟高水位來(lái)截取的。具體實(shí)現(xiàn)可自行百度。
重點(diǎn)：
1、事務(wù)中快照讀的結(jié)果是
非常依賴(lài)該事務(wù)首次出現(xiàn)快照讀的地方，即某個(gè)事務(wù)中首次出現(xiàn)快照讀的地方非常關(guān)鍵，它有決定該事務(wù)后續(xù)快照讀結(jié)果的能力。2、在
RC隔離級(jí)別下，是每個(gè)快照讀都會(huì)生成并獲取最新的Read View；而在RR隔離級(jí)別下，則是同一個(gè)事務(wù)中的第一個(gè)快照讀才會(huì)創(chuàng)建Read View, 之后的快照讀獲取的都是同一個(gè)Read View。
7、緩沖池（buffer pool）
應(yīng)用系統(tǒng)分層架構(gòu)，為了加速數(shù)據(jù)訪問(wèn)，會(huì)把最常訪問(wèn)的數(shù)據(jù)，放在緩存(cache)里，避免每次都去訪問(wèn)數(shù)據(jù)庫(kù)。操作系統(tǒng)，會(huì)有緩沖池(buffer pool)機(jī)制，避免每次訪問(wèn)磁盤(pán)，以加速數(shù)據(jù)的訪問(wèn)。MySQL作為一個(gè)存儲(chǔ)系統(tǒng)，同樣具有緩沖池(buffer pool)機(jī)制，以避免每次查詢(xún)數(shù)據(jù)都進(jìn)行磁盤(pán)IO，主要作用：
1、存在的意義是加速查詢(xún)?
2、緩沖池(buffer pool) 是一種常見(jiàn)的降低磁盤(pán)訪問(wèn) 的機(jī)制；
3、緩沖池通常以頁(yè)(page 16K)為單位緩存數(shù)據(jù)；
4、緩沖池的常見(jiàn)管理算法是LRU，memcache，OS，InnoDB都使用了這種算法；
5、InnoDB對(duì)普通LRU進(jìn)行了優(yōu)化：將緩沖池分為
老生代和新生代，入緩沖池的頁(yè)，優(yōu)先進(jìn)入老生代，該頁(yè)被訪問(wèn)，才進(jìn)入新生代，以解決預(yù)讀失效的問(wèn)題頁(yè)被訪問(wèn)。且在老生代停留時(shí)間超過(guò)配置閾值的，才進(jìn)入新生代，以解決批量數(shù)據(jù)訪問(wèn)，大量熱數(shù)據(jù)淘汰的問(wèn)題
預(yù)讀失效：
由于預(yù)讀(Read-Ahead)，提前把頁(yè)放入了緩沖池，但最終MySQL并沒(méi)有從頁(yè)中讀取數(shù)據(jù)，稱(chēng)為預(yù)讀失效
緩沖池污染：
當(dāng)某一個(gè)SQL語(yǔ)句，要批量掃描大量數(shù)據(jù)時(shí)，可能導(dǎo)致把緩沖池的所有頁(yè)都替換出去，導(dǎo)致大量熱數(shù)據(jù)被換出，MySQL性能急劇下降，這種情況叫緩沖池污染。解決辦法：加入
老生代停留時(shí)間窗口策略后，短時(shí)間內(nèi)被大量加載的頁(yè)，并不會(huì)立刻插入新生代頭部，而是優(yōu)先淘汰那些，短期內(nèi)僅僅訪問(wèn)了一次的頁(yè)。
8、table瘦身
空洞：
MySQL執(zhí)行
delete命令其實(shí)只是把記錄的位置，或者數(shù)據(jù)頁(yè)標(biāo)記為了可復(fù)用，但磁盤(pán)文件的大小是不會(huì)變的。通過(guò)delete命令是不能回收表空間的。這些可以復(fù)用，而沒(méi)有被使用的空間，看起來(lái)就像是空洞。插入時(shí)候引發(fā)分裂同樣會(huì)產(chǎn)生空洞。
重建表思路：
1、新建一個(gè)跟A表結(jié)構(gòu)相同的表B?
2、按照主鍵ID將A數(shù)據(jù)一行行讀取同步到表B?
3、用表B替換表A實(shí)現(xiàn)效果上的瘦身。
重建表指令：
1、alter table A engine=InnoDB，慎重用，牛逼的DBA都用下面的開(kāi)源工具。
2、推薦Github：gh-ost
9、SQL Joins、統(tǒng)計(jì)、 隨機(jī)查詢(xún)
7種join具體如下：
統(tǒng)計(jì)：
1、MyISAM模式下把一個(gè)表的總行數(shù)存在了磁盤(pán)上，直接拿來(lái)用即可?
2、InnoDB引擎由于 MVCC的原因，需要把數(shù)據(jù)讀出來(lái)然后累計(jì)求和?
3、性能來(lái)說(shuō) 由好到壞：count(字段) < count(主鍵id) < count(1) ≈ count(*)，
盡量用count(*)。
隨機(jī)查詢(xún)：
mysql>?select?word?from?words?order?by?rand()?limit?3;
直接使用order by rand()，explain 這個(gè)語(yǔ)句發(fā)現(xiàn)需要 Using temporary和 Using filesort，查詢(xún)的執(zhí)行代價(jià)往往是比較大的。所以在設(shè)計(jì)的時(shí)要避開(kāi)這種寫(xiě)法。
mysql>?select?count(*)?into?@C?from?t;
set?@Y1?=?floor(@C?*?rand());
set?@Y2?=?floor(@C?*?rand());
set?@Y3?=?floor(@C?*?rand());
select?*?from?t?limit?@Y1,1;?
select?*?from?t?limit?@Y2,1;
select?*?from?t?limit?@Y3,1;
這樣可以避免臨時(shí)表跟排序的產(chǎn)生，最終查詢(xún)行數(shù) = C + (Y1+1) + (Y2+1) + (Y3+1)
exist 和 in 對(duì)比：
1、in查詢(xún)時(shí)首先查詢(xún)子查詢(xún)的表，然后將內(nèi)表和外表做一個(gè)
笛卡爾積，然后按照條件進(jìn)行篩選。2、子查詢(xún)使用 exists，會(huì)先進(jìn)行主查詢(xún)，將查詢(xún)到的每行數(shù)據(jù)
循環(huán)帶入子查詢(xún)校驗(yàn)是否存在，過(guò)濾出整體的返回?cái)?shù)據(jù)。3、兩表大小相當(dāng)，in 和 exists 差別不大。
內(nèi)表大，用 exists 效率較高；內(nèi)表小，用 in 效率較高。4、查詢(xún)用not in 那么內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引；而not extsts 的子查詢(xún)依然能用到表上的索引。
not exists都比not in要快。
10、MySQL優(yōu)化
SQL優(yōu)化主要分4個(gè)方向：SQL語(yǔ)句跟索引、表結(jié)構(gòu)、系統(tǒng)配置、硬件。
總優(yōu)化思路就是最大化利用索引、盡可能避免全表掃描、減少無(wú)效數(shù)據(jù)的查詢(xún)：
1、減少數(shù)據(jù)訪問(wèn)：設(shè)置
合理的字段類(lèi)型，啟用壓縮，通過(guò)索引訪問(wèn)等減少磁盤(pán) IO。2、返回更少的數(shù)據(jù)：只
返回需要的字段和數(shù)據(jù)分頁(yè)處理，減少磁盤(pán) IO 及網(wǎng)絡(luò) IO。3、減少交互次數(shù)：
批量DML 操作，函數(shù)存儲(chǔ)等減少數(shù)據(jù)連接次數(shù)。4、減少服務(wù)器 CPU 開(kāi)銷(xiāo)：盡量減少數(shù)據(jù)庫(kù)排序操作以及全表查詢(xún)，減少 CPU 內(nèi)存占用?。
5、分表分區(qū)：使用
表分區(qū)，可以增加并行操作，更大限度利用 CPU 資源。
SQL語(yǔ)句優(yōu)化大致舉例：
1、合理建立覆蓋索引：可以有效減少回表。
2、union，or，in都能命中索引，建議使用in?
3、負(fù)向條件(!=、<>、not in、not exists、not like 等) 索引不會(huì)使用索引，建議用in。
4、在列上進(jìn)行運(yùn)算或使用函數(shù)會(huì)使索引失效，從而進(jìn)行全表掃描?
5、小心隱式類(lèi)型轉(zhuǎn)換，原字符串用整型會(huì)觸發(fā)
CAST函數(shù)導(dǎo)致索引失效。原int用字符串則會(huì)走索引。6、不建議使用%前綴模糊查詢(xún)。
7、多表關(guān)聯(lián)查詢(xún)時(shí)，小表在前，大表在后。在 MySQL 中，執(zhí)行 from 后的表關(guān)聯(lián)查詢(xún)是從左往右執(zhí)行的(Oracle 相反)，第一張表會(huì)涉及到全表掃描。
8、調(diào)整 Where 字句中的連接順序，MySQL 采用從左往右，自上而下的順序解析 where 子句。根據(jù)這個(gè)原理，應(yīng)將過(guò)濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。
SQL調(diào)優(yōu)大致思路：
1、先用慢查詢(xún)?nèi)罩径ㄎ痪唧w需要優(yōu)化的sql?
2、使用 explain 執(zhí)行計(jì)劃查看索引使用情況?
3、重點(diǎn)關(guān)注(一般情況下根據(jù)這4列就能找到索引問(wèn)題)：
1、key（查看有沒(méi)有使用索引）?
2、key_len（查看索引使用是否充分）
3、type（查看索引類(lèi)型）?
4、Extra（查看附加信息：排序、臨時(shí)表、where條件為false等）
4、根據(jù)上1步找出的索引問(wèn)題優(yōu)化sql 5、再回到第2步
表結(jié)構(gòu)優(yōu)化：
1、盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類(lèi)型而非INT，如果非負(fù)則加上UNSIGNED 。
2、VARCHAR的長(zhǎng)度只分配真正需要的空間?。
3、盡量使用TIMESTAMP而非DATETIME?。
4、單表不要有太多字段，建議在20以?xún)?nèi)。
5、避免使用NULL字段，很難查詢(xún)優(yōu)化且占用額外索引空間。字符串默認(rèn)為''。
讀寫(xiě)分離：
只在主服務(wù)器上寫(xiě)，只在從服務(wù)器上讀。對(duì)應(yīng)到數(shù)據(jù)庫(kù)集群一般都是一主一從、一主多從。業(yè)務(wù)服務(wù)器把需要寫(xiě)的操作都寫(xiě)到主數(shù)據(jù)庫(kù)中，讀的操作都去從庫(kù)查詢(xún)。主庫(kù)會(huì)同步數(shù)據(jù)到從庫(kù)保證數(shù)據(jù)的一致性。一般 讀寫(xiě)分離 的實(shí)現(xiàn)方式有兩種：
代碼封裝跟數(shù)據(jù)庫(kù)中間件。
分庫(kù)分表：分庫(kù)分表分為垂直和水平兩個(gè)方式，一般是先垂直后水平。
1、
垂直分庫(kù)：將應(yīng)用分為若干模塊，比如訂單模塊、用戶(hù)模塊、商品模塊、支付模塊等等。其實(shí)就是微服務(wù)的理念。2、
垂直分表：一般將不常用字段跟數(shù)據(jù)較大的字段做拆分。3、
水平分表：根據(jù)場(chǎng)景選擇什么字段作分表字段，比如淘寶日訂單1000萬(wàn)，用userId作分表字段，數(shù)據(jù)查詢(xún)支持到最近6個(gè)月的訂單，超過(guò)6個(gè)月的做歸檔處理，那么6個(gè)月的數(shù)據(jù)量就是18億，分1024張表，每個(gè)表存200W數(shù)據(jù)，hash(userId)%100找到對(duì)應(yīng)表格。4、
ID生成器：分布式ID 需要跨庫(kù)全局唯一方便查詢(xún)存儲(chǔ)-檢索數(shù)據(jù)，確保唯一性跟數(shù)字遞增性。
目前主要流行的分庫(kù)分表工具 就是Mycat和sharding-sphere。
TiDB：開(kāi)源分布式數(shù)據(jù)庫(kù)，結(jié)合了傳統(tǒng)的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持無(wú)限的水平擴(kuò)展，具備強(qiáng)一致性和高可用性。TiDB 的目標(biāo)是為 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 場(chǎng)景提供一站式的解決方案。TiDB 具備如下核心特點(diǎn)
1、支持 MySQL 協(xié)議（開(kāi)發(fā)接入成本低）。
2、100% 支持事務(wù)（數(shù)據(jù)一致性實(shí)現(xiàn)簡(jiǎn)單、可靠）。
3、無(wú)限水平拓展（不必考慮分庫(kù)分表），不停服務(wù)。
4、TiDB 支持和 MySQL 的互備。
5、遵循jdbc原則，學(xué)習(xí)成本低，強(qiáng)關(guān)系型，強(qiáng)一致性，不用擔(dān)心主從配置，不用考慮分庫(kù)分表，還可以無(wú)縫動(dòng)態(tài)擴(kuò)展。
適合：
1、原業(yè)務(wù)的 MySQL 的業(yè)務(wù)遇到單機(jī)容量或者性能瓶頸時(shí)，可以考慮使用 TiDB 無(wú)縫替換 MySQL。
2、大數(shù)據(jù)量下，MySQL 復(fù)雜查詢(xún)很慢。
3、大數(shù)據(jù)量下，數(shù)據(jù)增長(zhǎng)很快，接近單機(jī)處理的極限，不想分庫(kù)分表或者使用數(shù)據(jù)庫(kù)中間件等對(duì)業(yè)務(wù)侵入性較大、對(duì)業(yè)務(wù)有約束的 Sharding 方案。
4、大數(shù)據(jù)量下，有高并發(fā)實(shí)時(shí)寫(xiě)入、實(shí)時(shí)查詢(xún)、實(shí)時(shí)統(tǒng)計(jì)分析的需求。5、有分布式事務(wù)、多數(shù)據(jù)中心的數(shù)據(jù) 100% 強(qiáng)一致性、auto-failover 的高可用的需求。
不適合：
1、單機(jī) MySQL 能滿(mǎn)足的場(chǎng)景也用不到 TiDB。
2、數(shù)據(jù)條數(shù)少于 5000w 的場(chǎng)景下通常用不到 TiDB，TiDB 是為大規(guī)模的數(shù)據(jù)場(chǎng)景設(shè)計(jì)的。
3、如果你的應(yīng)用數(shù)據(jù)量小（所有數(shù)據(jù)千萬(wàn)級(jí)別行以下），且沒(méi)有高可用、強(qiáng)一致性或者多數(shù)據(jù)中心復(fù)制等要求，那么就不適合使用 TiDB。
原創(chuàng)電子書(shū)
原創(chuàng)思維導(dǎo)圖
已經(jīng)有8756個(gè)初學(xué)者都下載了！
?三歪把【大廠面試知識(shí)點(diǎn)】、【簡(jiǎn)歷模板】、【原創(chuàng)文章】
全部整理成電子書(shū)，共有1263頁(yè)！掃碼或微信搜 Java3y
回復(fù)「888」領(lǐng)取
 |
|
47塊半年購(gòu)買(mǎi)服務(wù)器。最近如果要買(mǎi)服務(wù)器的同學(xué)可以重點(diǎn)關(guān)注，錯(cuò)過(guò)了就要等一年！別在活動(dòng)結(jié)束后再問(wèn)我能不能買(mǎi)了喲！