1.mysql一條查詢語句是如何執(zhí)行的?
mysql內(nèi)部分為：連接器，分析器，優(yōu)化器，執(zhí)行器和緩存查詢。
連接器：用于客戶端與server端進行賬密連接，及權(quán)限校驗。
緩存查詢：一條執(zhí)行的查詢sql先查緩存是否存在這樣的數(shù)據(jù)如果有直接返回結(jié)果（不建議使用緩存查詢，命中率太低，對于更新頻繁的表更傾向于操作引擎返回結(jié)果，設(shè)置query_cache_type = DEMAND 默認不使用查詢緩存，我們可以在sql顯示先查詢緩存例如：select SQL_CACHE * from t）
分析器：對sql進行分析，sql語句是否合法，是否規(guī)范，還會校驗表，字段等是否存在等
優(yōu)化器：分析器執(zhí)行完成之后，優(yōu)化器會對sql語句進行優(yōu)化如對索引的選用
執(zhí)行器：執(zhí)行sql語句，對于表無索引的執(zhí)行流程(從表第一行開始全表掃描取到當前行做對比如果符合條件加入到結(jié)果集，直到最后一行將結(jié)果集返回)，對于有索引（通過索引數(shù)進行查找符合的加入結(jié)果集，直到不滿足的行將結(jié)果集返回）
2.mysql一條更新語句是如何執(zhí)行的?
更新語句與查詢語句大致相同，不一樣的是更新語句涉及到兩個日志模塊：redo log(重做日志)，binlog(歸檔日志)
redo log日志：更新語句記錄在redo log上，當redo log日志寫滿之后會將buffer pool臟頁更新到磁盤中，向前推進checkpoint。redolog是InnoDB引擎特有的日志
binlog日志：binlog是歸檔日志，可以追加寫入，當binlog日志文件到達一定大小時候會切換到下一個文件進行追加寫入。
看下這條更新語句如何執(zhí)行
//首先創(chuàng)建一張表
create table test(id int primary key, column int);
//執(zhí)行更新操作
update test set column = column +1 where id = 2；
1.執(zhí)行器根據(jù)id=2的主鍵進行掃描，如果緩存中（buffer pool）中如果有直接返回給執(zhí)行器，如果沒有從磁盤上掃描將數(shù)據(jù)加入到緩存中（buffer pool）返回給執(zhí)行器。
2.執(zhí)行器拿到這行數(shù)據(jù)讀取column值改成column+1，調(diào)用引擎將數(shù)據(jù)更新，
3.引擎將更新的數(shù)據(jù)再寫入buffer pool，同時更新操作記錄到redo log中，此時redo log 處于prepare狀態(tài)，待執(zhí)行器執(zhí)行完畢隨時可以提交事務(wù)。
4.執(zhí)行器又將這個更新操作記錄成binglog模式，將binlog寫入到磁盤
5.執(zhí)行器調(diào)用引擎事務(wù)提交接口，引擎將剛剛記錄的redo log改成commit，執(zhí)行操作完成。
為什么redo log 有prepare 和 commit (兩階段提交) 兩個步驟呢？
兩階提交是為了保證兩個日志文件的邏輯一致。例如更新這條語句假設(shè)column原值為0
update test set column = 1 where id = 2；
不妨我們使用反證法進行驗證：1.先寫redo log 后寫 binlog 2.先binlog 后 redo log
1種情況：先寫redo log 后寫 binlog
假設(shè)寫完redo log之后 發(fā)生crash binlog未寫入，此時redo log 的column值為1，binlog未記錄，mysql重啟之后將id為2的記錄更新成1，當需要binlog來恢復臨時庫時候binlog并未記錄更新的操作，導致數(shù)據(jù)與主庫的數(shù)據(jù)不一致。
2中情況：先binlog 后 redo log
假設(shè)寫完binlog之后發(fā)生crash redolog未寫入事務(wù)是無效的會回滾，binlog的日志記錄column為1，使用binlog來恢復數(shù)據(jù)后發(fā)現(xiàn)column值為1了與原來邏輯不一致。
兩階段提交的作用
兩階段提交過程中可以出現(xiàn)的crashredo log 寫入成功 binlog 未寫入 與 redo log 寫入成功 binlog 寫入但未commit
這種情況由于redolog寫入成功處于prepare狀態(tài)，當mysql重啟會讀取redolog進行復盤，發(fā)現(xiàn)有個日志未commit，會拿這個日志的xid去binlog日志中找是否有這個日志，如果有事務(wù)提交，否則事務(wù)回滾（binlog未寫入，該事務(wù)回滾）都保證了兩分日志的邏輯一致性
3.ACID和mysql事務(wù)隔離級別?
A（原子性）：一個事務(wù)要么成功，要么失敗。
C（一致性）：一個
I（隔離性）：
D（持久性）：
Read Uncommited（讀未提交）: 一個事務(wù)還沒提交，做的變更其他事務(wù)是可以訪問到。
Read commited（讀已提交）：一個事務(wù)提交后，做的變更其他事務(wù)才可以訪問到。
Repeatable read（可重復讀）：一個事務(wù)執(zhí)行過程看到的數(shù)據(jù)，總是和這個事務(wù)在啟動時候看到的數(shù)據(jù)是一致的。
serializable（串行化）：對于一條記錄，寫會加寫鎖，讀會加讀鎖，當讀寫鎖發(fā)生沖突后訪問到的事務(wù)必須等待前一個事務(wù)提交之后才可以執(zhí)行
其實對于RC，RR兩種情況數(shù)據(jù)庫會創(chuàng)建視圖，RC情況下每次SQL執(zhí)行前創(chuàng)建視圖，RR情況是每次事務(wù)開始時候創(chuàng)建視圖。
mysql記錄每條更新語句都會記錄一條回滾操作，記錄的新值通過回滾可以得到上一個狀態(tài)值，如圖所示：
假設(shè)一個字段唄A，B，C事務(wù)進行一次更新2,3,4對應(yīng)的回滾記錄就是3,2,1。同一條記錄在系統(tǒng)中有多個版本，這就是數(shù)據(jù)庫的多版本控制（MVCC），對于A事務(wù)需要得到1，就要依次回滾視圖中所有的操作。
？？什么時候會刪掉記錄版本呢----答：不需要的時候 ？？？？什么時候不需要--答：當版本不處于任何事務(wù)之內(nèi)的就可以刪除了，所以我們建議盡量不使用長事務(wù)。如果長事務(wù)時間過長在這個事務(wù)內(nèi)的視圖都需要保留，導致系統(tǒng)內(nèi)有過多的視圖，占用更多的存儲空間。
4.mysql索引
索引作用：提高數(shù)據(jù)查詢效率，
索引模型：數(shù)組，hash表，B+樹
通過索引如何查找數(shù)據(jù)
//創(chuàng)建T表，索引k，主鍵索引ID
create table T (ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))engine=InnoDB;
//插入數(shù)據(jù)
insert into T values(100,1, 'aa'),
(200,2,'bb'),
(300,3,'cc'),
(500,5,'ee'),
(600,6,'ff'),
(700,7,'gg');
//查詢語句 需要執(zhí)行幾次樹搜索操作
select * from T where k between 3 and 5
sql執(zhí)行流程
1.在k索引樹上進行搜索k=3記錄取得ID=300；
2.ID=300在主鍵索引搜索取得R3放入結(jié)果集
3.繼續(xù)遍歷k索引樹k=3的下個值為k=5取得ID=500；
4.ID=500在主鍵索引搜索取得R4放入結(jié)果集。
5.繼續(xù)遍歷k索引樹k=5的下個值為k=6不滿足調(diào)節(jié)循環(huán)結(jié)束返回結(jié)果集
在這個執(zhí)行過程中在k索引樹上執(zhí)行時1,3,5。在主鍵索引樹上執(zhí)行2,4（回表操作）搜索了4次
索引覆蓋
上面的查詢語句是查詢所有字段，如果我們將sql改成
select ID,k from T where k between 3 and 5
sql執(zhí)行流程
1.在k索引樹上進行搜索k=3記錄取得將ID=300，k=3放入結(jié)果集；
2.繼續(xù)遍歷k索引樹k=3的下個值為k=5取得ID=500，k=5放入結(jié)果集；
3.繼續(xù)遍歷k索引樹k=5的下個值為k=6 條件不滿足 循環(huán)退出 返回結(jié)果集；
使用覆蓋索引避免回表掃描行數(shù)就是2次
聯(lián)合索引優(yōu)勢
1.覆蓋索引
2.最左前綴index（a,b,c,d）包含a的索引都不是使用
3.索引下推 如 a=10，b>5 會判斷b是否大于5，避免了小于=5的數(shù)據(jù)進行回表掃描
普通索引與唯一索引如何選擇
待補充。。。
5.mysql全局鎖和表鎖及行鎖
全局鎖：
flash table with read lock（FTWRL）：將表設(shè)置只讀狀態(tài)，當遇到 DDL或者DML操作會阻塞，影響性能 客戶端與數(shù)據(jù)庫斷開連接鎖也就釋放了，不建議使用。
mysqldump --single-transaction ：需要結(jié)合事務(wù)隔離級別為RR，使用數(shù)據(jù)備份場景，不影響其他事務(wù)的讀寫操作，推薦使用
set global read_only=1：這種場景是設(shè)置是判斷是否是從庫，對于更高權(quán)限的系統(tǒng)用戶是無效的，客戶端與數(shù)據(jù)庫異常斷開連接不會釋放鎖。不推薦使用
表鎖：
table lock XXX read/write：例如table lock A read B write；設(shè)置A表只讀，B表 讀寫操作，別的事務(wù)對于這A表寫和B表讀寫處于阻塞狀態(tài)。不推薦使用
隱式的加鎖Metadata Lock（MDL）：對表做增刪改查（DML）時候?qū)Ρ磉M行加讀鎖，對表進行DDL時候加的是寫鎖，讀寫鎖互斥也就是說DDL語句后的DML語句都會被阻塞
舉個例子也是可能會被問到的知識點
SessionA: begin; select * from t limit 1; 啟動sessionA
SessionB: begin; select * from t limit 1; 緊接著啟動sessionB
SessionC: alter table t add f int；接著啟動sessionC
SessionD: begin; select * from t limit 1; 然后啟動sessionD
SessionE: begin; select * from t limit 1; 最后啟動sessionE
※SessionA，B可以正常啟動拿到MDL讀鎖進行查詢，
SessionC獲取MDL寫鎖等待A，B執(zhí)行commit之后才可以執(zhí)行，
SessionD，E獲取到MDL讀鎖由于C寫鎖互斥處于阻塞狀態(tài)。
當SessionA，B事務(wù)進行Commit之后，開始執(zhí)行SessionC，C執(zhí)行完釋放寫鎖，D，E繼續(xù)執(zhí)行。
我們都能想到A->B->C->D->E正確的流程？？？
實際上順序是A->B->D->E->C才是正確的流程，我來解釋下為什么是這個樣子的。
※我們需要知道Online DDL真實的流程
1.獲取MDL寫鎖
2.將MDL寫鎖降級為讀鎖
3.執(zhí)行DDL操作（比較耗時，期間可以執(zhí)行DML操作）
4.升級MDL讀鎖為寫鎖
5.釋放MDL寫鎖
當?shù)诙紺將MDL寫鎖降級成讀鎖時候，此時D，E兩個session是可以執(zhí)行的，當C執(zhí)行完DDL操作需要升級鎖時候發(fā)現(xiàn)有MDL讀鎖未提交，
等待DE事務(wù)commit之后才可以，所以我們看到的流程是ABDEC
InnoDB引擎行鎖
行鎖就是對數(shù)據(jù)表中的行記錄鎖定，如事務(wù)A更新一行數(shù)據(jù)，而事務(wù)B也更新事務(wù)A的同一行，那么事務(wù)B就要等事務(wù)A提交之后才可以更新這行數(shù)據(jù)。
在InnoDB事務(wù)中行鎖是需要加的時候才會加，但并不是不需要的時候就立即釋放的，而是要等到事務(wù)提交之后才釋放，兩階段鎖協(xié)議。這樣的話，很容易出現(xiàn)死鎖
如圖：事務(wù)A中有兩條更新語句，B也如此，A先更新id=1再更新id=2，B先更新id=2再更新id=1，在事務(wù)A執(zhí)行更新id=1時候?qū)@行記錄加上鎖，此時id=2這行數(shù)據(jù)是沒有鎖的，事務(wù)B可以更新id=2這行記錄，當A，B都執(zhí)行完第一更新語句時候，（兩階段提交id=1和id=2已經(jīng)都被鎖住了）導致A更新id=2時候發(fā)現(xiàn)id=2這行記錄有鎖，等待，事務(wù)B更新id=1時候發(fā)現(xiàn)id=1這行記錄有鎖，等待，這就導致了A，B事務(wù)都帶等待互相持有的鎖，導致了死鎖
死鎖兩個策略
1.設(shè)置等待時間通過innodb_lock_wait_timeout參數(shù)設(shè)置鎖等待時間
弊端：默認50s但是如果等待50s后退出，這個時間又不可接受，如果設(shè)置太小1s又會誤傷很多事務(wù)。
2.死鎖檢測，發(fā)現(xiàn)死鎖，主動回滾某一個事務(wù)，讓其他事物繼續(xù)執(zhí)行，將參數(shù)innodb_deadlock_detect=no標識開啟這個邏輯
6.mysql鎖與隔離
RR隔離級別啟動事務(wù)時候會拍一個快照，這個快照是基于整個庫的，這個快照并不是復制整個庫，而是創(chuàng)建一個trx_id做為當前快照的版本。而對于表的row會有多版本控制（MVCC）對于哪個版本可見那就要看版本的trx_id與當前快照trx_id值對比了，當然對于低版本的trx_id也可能是不可見的，（低版本的trx_id處于未提交的事務(wù) 不可見）。
當前讀概念
select * from t lock in share mode/ for update（共享鎖/排他鎖）當前讀是讀取到最新值，如果當前讀有未執(zhí)行完的事務(wù)需要等到事務(wù)提交釋放行鎖，當前讀才可以執(zhí)行（DML寫鎖與讀鎖互斥）
7.mysql ChangeBuffer,BufferPool,redolog之間關(guān)系
什么是ChangeBuffer
changeBuffer是從BufferPool內(nèi)存中申請的一塊內(nèi)存，用于存放更新數(shù)據(jù)的記錄。在更新一條記錄時：
1.如果該數(shù)據(jù)所在的數(shù)據(jù)頁在內(nèi)存中（bufferpool中）直接更新bufferpool即可
2.如果該數(shù)據(jù)所在的數(shù)據(jù)頁不在內(nèi)存中（不在bufferpool中）直接將更新的操作記錄在changebuffer中，當下次訪問這個數(shù)據(jù)頁時候?qū)?shù)據(jù)頁從磁盤讀到內(nèi)存bufferpool中，然后將changebuffer和數(shù)據(jù)頁進行merger合并，此時數(shù)據(jù)頁成了臟頁，等待刷入磁盤。
使用changebuffer好處
減少在更新數(shù)據(jù)時候需要磁盤隨機訪問IO，有種場景不適合使用changebuffer
1.如果更新的數(shù)據(jù)有唯一索引（更新唯一索引會磁盤隨機訪問到數(shù)據(jù)判斷更新的值是否會唯一鍵沖突，這樣數(shù)據(jù)頁會加載到內(nèi)存，內(nèi)存操作和changebuffer一樣，這樣沒有避免這次磁盤的隨機訪問）
2.對于剛更新的數(shù)據(jù)立馬就查詢的場景也不適合使用changebuffer，剛更新邏輯數(shù)據(jù)加入到changebuffer上，立馬進行查詢會進行磁盤隨機訪問將數(shù)據(jù)頁加入到內(nèi)存中，然后內(nèi)存數(shù)據(jù)頁與changebuffer進行merge合并，這種情況changebuffer數(shù)據(jù)還很少，磁盤的io訪問并未減少。
針對上面兩種不適合使用的場景反之就是合適的場景（1.普通索引，2.更新多讀少的場景如賬單，日志類等使用changebuffer性能明顯）
這三者之間如何聯(lián)系呢
//表t k字段普通索引
insert into t(id,k) values(id1,k1),(id2,k2);
假設(shè)k1索引查詢的要插入所在頁在內(nèi)存page1，k2位置所在頁不在內(nèi)存中
1. Page 1 在內(nèi)存中（buffer pool中），直接更新內(nèi)存，直接把數(shù)據(jù)插入到表t中的位置
2.Page 2 沒有在內(nèi)存中，就在內(nèi)存的 change buffer 區(qū)域，記錄下 “ 我要往 Page 2 插入一行 ” 這個信息
3.將上述兩個動作記入 redo log 中（圖中 3 和 4 ）（包含了數(shù)據(jù)的變更和 change buffer 的變更）
這樣的一個事務(wù)，寫了兩處內(nèi)存（直接在內(nèi)存更新和在內(nèi)存中的change buffer記錄），然后寫了一處磁盤（兩次操作合在一起寫了一次磁盤中redolog）
如果查詢
select * from t where k in (k1, k2)
讀 Page 1 的時候，直接從內(nèi)存返回更新后的內(nèi)容，要讀 Page 2 的時候，需要把 Page 2 從磁盤讀入內(nèi)存中，然后應(yīng)用 change buffer 里面的操作日志(add 某個數(shù)據(jù) to page2).最后生成正確的版本并返回結(jié)果
8.mysql選錯索引
場景1：不斷地刪除歷史數(shù)據(jù)和新增歷史數(shù)據(jù)，mysql會選錯索引
重新統(tǒng)計索引信息：analyze table t；
場景2.表T有10萬條數(shù)據(jù)。表結(jié)構(gòu)為id主鍵索引，a，b兩個字段int類型都是普通索引，執(zhí)行sql為select * from T where (a between 1and 1000) and (b between 50000 and 100000 ) order by b limit 10;這個sql執(zhí)行explain計劃可以看到，使用索引的是b掃描行為5萬多行，為什么沒有選擇a索引呢，使用a索引只需要掃描1000行即可。分析下：由于order by 根據(jù)b排序，優(yōu)化器覺得掃描b索引天然排序效率更快。如何使用a索引呢？兩個方案：方案一，在order by b，a即可，讓優(yōu)化器知道不僅要根據(jù)b排序還要根據(jù)a排序，就會重新分析走a索引。方案二使用force index(a)讓sql指定使用a索引。
圖片.png
我們可以總結(jié)一下：索引選錯情況如何優(yōu)化
1.使用analyze table XXX；（這種適合不斷刪除和新增的表）
2.使用force index(xxx) 如 select * from t force index(xxx) where xxx = aaa;
3.修改sql語句
4.刪除多余的索引重建索引等
9.如何給字符串字段加索引
1.直接給該字段創(chuàng)建索引，占用的空間可能比較多。
2.使用前綴索引，占得空間相對較小，增加了查詢次數(shù)，而且不能使用覆蓋索引（因為使用前綴索引掃描到的值不能準確知道是否完整的字段需要回表查詢）
3.使用倒敘存儲，在使用前綴索引降低了索引的重復度，但是增加了字符串反轉(zhuǎn)的計算的開銷，而且不能使用覆蓋索引及范圍查詢。
4.創(chuàng)建hash值字段創(chuàng)建索引，有額外的存儲和計算開銷，而且不能使用范圍查詢
10.Mysql為什么隔一段時間查詢會抖動一次？
數(shù)據(jù)查詢突然很慢，而且這個場景很難復現(xiàn)，他不僅是隨機的，而且持續(xù)時間很短。針對這個可能發(fā)生了再刷臟頁。發(fā)生刷臟頁可能有一下四個場景。
場景一：redolog日志快寫滿了，需要將redolog日志進行擦除一部分來騰出空間。而擦除的這部分空間數(shù)據(jù)對應(yīng)的bufferpool數(shù)據(jù)頁屬于臟頁需要進行flush更新到磁盤。
場景二：當bufferpool內(nèi)存不足情況，有新的查詢數(shù)據(jù)需要從磁盤數(shù)據(jù)頁寫入到內(nèi)存中，就需要淘汰bufferpool中原有的數(shù)據(jù)頁。要淘汰的數(shù)據(jù)頁如果是臟頁，就需要寫入到磁盤。
場景三：mysql認為系統(tǒng)不是很忙時候，主動進行將內(nèi)存部分臟頁進行寫入磁盤。
場景四：當mysql正常關(guān)閉時需要將bufferpool中的臟頁數(shù)據(jù)寫入到磁盤中。
針對以上四個場景分析下影響性能的條件:
對于場景三屬于mysql空閑干的事，無需考慮，場景四是mysql處于正常關(guān)閉也和性能無關(guān)。對于場景一redolog日志寫滿了需要騰出空間繼續(xù)寫入，對于這種刷臟頁是需要盡量避免的，因為這種情況刷臟頁由于redolog空間不足，寫入也就停滯了，影響寫的性能。對于場景二 bufferpool內(nèi)存不足刷新臟頁這種是常態(tài)，但是出現(xiàn)這兩種場景，都會影響性能。1.redolog日志滿了，需要刷臟頁，此時寫入線程被堵塞，對于敏感業(yè)務(wù)來書是不能接受的。2.如果一個查詢需要淘汰的內(nèi)存臟數(shù)據(jù)頁過多，寫入磁盤數(shù)量也會過多，這必定會影響查詢的性能。
InnoDB刷臟頁的控制策略
了解系統(tǒng)IO性能，配置mysql可執(zhí)行的IO能力，這樣可用提升mysql刷臟頁的性能，配置innodb_io_capacity=1000。盡量避免刷臟頁的IO速度小于產(chǎn)生臟頁的速度。mysql有個刷臟頁的機制，如果準備刷臟頁的下一個數(shù)據(jù)頁也是臟頁，也會被一起刷掉，“連坐機制”，通過配置innodb_flush_neighbors=1連坐 0 只刷自己
11.刪除表數(shù)據(jù)，表文件大小不變
刪除某行記錄，Innodb引擎只會把這行記錄標記為刪除，在別的數(shù)據(jù)插入可以復用這個位置。但是磁盤大小不會縮小，如果是刪除整個頁，那么整個數(shù)據(jù)頁就可以被復用了。
這些被標記的可復用的位置我們俗稱“空洞”；
產(chǎn)生空洞的原因有:
1.數(shù)據(jù)刪除，被標記可復用的位置為空洞。
2.插入數(shù)據(jù)導致頁分裂，分裂的頁末尾會空出一個位置產(chǎn)生空洞。
如果把這些由于增刪產(chǎn)生的空洞數(shù)據(jù)去掉可以達到收縮表空間的目的---重建表。
方式1：alter table A engine=InnoDB
方式2：optimize table t ==recreate+analyze
知識點
一個表t文件大小問1TB，執(zhí)行 alter table t engine=InnoDB，執(zhí)行完之后空間沒有變小反而變成了1.01TB？這種是什么原因？？？
alter table t engine=InnoDB是重建表，對于有很多的空洞情況效果明顯，但是如果原本每個數(shù)據(jù)頁存儲的數(shù)據(jù)都是緊湊的，當我們執(zhí)行重建表語句后，mysql會將每個頁預留10%空間，可能發(fā)生某些數(shù)據(jù)頁發(fā)生了也分裂情況，導致我們的文件大小不降反增加了
12.查詢表行數(shù)方式對比優(yōu)缺點（count(*),count(1),count(id),count(column)）
Count()函數(shù)的含義：count()是一個聚合函數(shù)，對于要返回的結(jié)果集進行一行行的判斷，如果count函數(shù)的參數(shù)不為NULL，累計值就加一，否則不加，最后返回累計值。
所以count(),count(id),count(1)都表示返回滿足條件的總行數(shù)，對于count(column)則表示返回滿足條件的column列不為NULL的個數(shù)。
count(ID)：InnoDB引擎會遍歷整張表，把每行的id都取出來，返回給server層，server層拿到id判斷不可能為空，按行累加。
count(1)：InnoDB引擎遍歷整個表，不會取數(shù)據(jù)，server層對于返回一行就放一個1進去判斷不可能為空，按行累加。
count(column)：1.如果這個字段定義not null InnoDB遍歷整個表一行行返回數(shù)據(jù)讀出該字段，判斷不能為null，按行累加。
2.如果這個字段定義允許為null InnoDB引擎遍歷表一行行讀取數(shù)據(jù)返回給server層，server層判斷該字段值是否為空，不為空才累加。
count(*):不會把所以字段取出來，count（）肯定不為null，按行累加。
按照上述效率排序 count(字段) < count(ID) < Count(1) <= count(*)盡量使用count（*）
13.order by 語句工作流程
//表結(jié)構(gòu)
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
//查詢語句
select city,name,age from t where city='杭州' order by name limit 1000 ;
explain命令看看語句的執(zhí)行情況。
圖片.png
使用了索引查詢（using index condition）和排序（using filesort mysql會為每個線程分配一塊內(nèi)存用于排序）
排序的過程可能在內(nèi)存中，也可能需要使用外部排序，這取決于排序所需要的內(nèi)存參數(shù)sort_buffer_size（全字段排序）;
max_length_for_sort_data是 MySQL 中專門控制用于排序的行數(shù)據(jù)的長度的一個參數(shù)。它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個算法（rowId排序）
全字段排序執(zhí)行流程如下：
1.初始化sort_buffer，存放，city，name，age這三個字段
2.根據(jù)city索引找到索引為 city='杭州' 第一個值id為ID_X
3.通過主鍵索引找到id=ID_X的行信息，取出city，name，age這三個字段放入sort_buffer中。
4.通過city索引找到ID_X的下個值滿足條件重復3.步驟放入到sort_buffer中直到city不滿足條件為止。
5.將sort_buffer中的數(shù)據(jù)按照name字段進行排序。
6.取出前1000行的數(shù)據(jù)返回給客戶端。
rowId排序執(zhí)行流程：與全字段執(zhí)行流程相似，多的步驟是需要回表查詢字段，因為在使用rowid排序max_length_for_sort_data對于行字段大小有限制，只能放下要排序的字段和主鍵Id
14.索引失效
1.條件語句使用函數(shù)如：select * from t where f1（index） = “kjjjk”；
2.隱式類型轉(zhuǎn)換 select * from t where str = 9；str 是字符隱式轉(zhuǎn)換成select * from t where CAST(str USING int) = 9
3.隱式字符集轉(zhuǎn)換
4.條件中使用or，存在無索引的條件，則索引失效，要想使用索引or中條件必須都要有索引。
5.多列索引（聯(lián)合索引），需要使用最左前綴才能使用索引
6.like查詢需要'xxx%'在查詢字段結(jié)尾才可使用
7.mysql認為全表掃描比索引更快，則不使用索引。
15.加鎖過程
RR隔離級別執(zhí)行：select * from t where column=10 for update / select * from t where column=10 lock in share mode。全表掃描給每個行加上行鎖，將行之間的間隙加上間隙鎖，統(tǒng)稱為next-key lock。加上鎖別的事務(wù)不可以修改該行，間隙鎖不允許在各條記錄之間插入新的行數(shù)據(jù)。
RC隔離級別執(zhí)行：select * from t where column=10 for update / select * from t where column=10 lock in share mode。全表掃描給每行加上上鎖，然后將column != 10的行鎖釋放。不會加間隙鎖。
對于RR隔離級別間隙鎖規(guī)則：
1.查詢過程中訪問到的對象才會加鎖。
2.加鎖的基本單位是next-key lock（前開后閉）。
3.等值查詢上MySQL的優(yōu)化： 索引上的等值查詢.
如果是唯一索引，next-key lock會退化為行鎖，
如果不是唯一索引，需要訪問到第一個不滿足條件的值，此時next-key lock會退化為間隙鎖。
4.范圍查詢：無論是否是唯一索引，范圍查詢都需要訪問到不滿足條件的第一個值為止
//表結(jié)構(gòu)
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
//插入的數(shù)據(jù)
insert into t values(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);
案例一:等值查詢間隙鎖
![]()
圖片.png
由于表t上沒有id=7的值。
1.加鎖最小單位是next_key lock SessionA加的鎖范圍(5,10];
2.等值查詢，唯一索引，next_key lock會退化成行鎖，由于（id=7）10不等上鎖范圍為(5,10)；
所以SessionB在插入Id=8由于間隙鎖原因被阻塞，而SessionC id=10不在間隙鎖內(nèi)執(zhí)行成功
案例二：非唯一索引等值鎖（覆蓋索引上上鎖）
1.使用非唯一索引等值查詢查詢，鎖最小單位是next_key lock 上(0,5]，
2.由于非唯一索引等值查詢需要繼續(xù)掃描知道條件不滿足，下個值是（10,10,10）加的next_key lock 為(0,5],(5,10]。
3.由于非唯一索引等值查詢會將next_key lock 退化成間隙鎖（5,10) 由于覆蓋索引未回表無法在主鍵索引加鎖。
SessionB 可以執(zhí)行成功， Session由于間隙鎖阻塞。
注：例子中使用的是lock in share mode 只鎖覆蓋索引，但是如果使用for update 就不一樣了，執(zhí)行for update 系統(tǒng)會認為你要更新數(shù)據(jù)，也會給主鍵索引上滿足條件的加鎖。
案例三：主鍵索引范圍鎖
mysql> select * from t where id>=10 and id<11 for update;
1.加鎖最小單位next_key lock 主鍵索引(5,10]等值查詢唯一索引退化成行鎖 10。
2.id<11，范圍查詢查到第一個不滿足條件的值加的鎖為(10,15],加鎖范圍是行鎖10和next_key lock (10,15]。
所以SessionB插入（8，8，8）成功，插入（13,13,13）阻塞。SessionC更新id15阻塞。
案例四：費唯一索引范圍鎖
1.在普通索引上加第一個鎖為(5,10]
2.范圍查詢需要查詢到第一個不滿足條件的加上鎖（10,15],加的鎖為next_key lock 為(5,10],(10,15]。
所以SessionB插入的（8,8,8）和Session更新的c=15鎖住
案例五：唯一索引范圍鎖 bug
1.id>10 id<=15加鎖next_key lock 為10,15].
2.無論是否是唯一索引范圍查詢都需要查詢不滿足條件的第一個記錄id=20不滿足加的next_key lock為(15,20]加鎖next_key lock為(10,15],(15,20]。
所以SessionB更新id=20和id=16都會被阻塞。
16.Redo log,binlog寫入機制
redolog寫入機制：
事務(wù)在執(zhí)行過程中生成的redolog日志是需要寫入到redolog buffer中，接著寫入到page cache文件中，最后才是fsync持久化到磁盤上。
redolog寫入策略，InnoDB提供了innodb_flush_log_at_trx_commint參數(shù)，取值含義分別為：
1.innodb_flush_log_at_trx_commint=0,表示每次事務(wù)提交時只把redolog留在redolog buffer中，弊端（當mysql重啟會丟失redolog buffer中數(shù)據(jù)導致，這些數(shù)據(jù)未持久化到磁盤上）；
2.innodb_flush_log_at_trx_commint=1,表示每次事務(wù)提交都將redolog持久化到磁盤上，弊端（每次事務(wù)提交都持久化到磁盤，iops高）。
3.innodb_flush_log_at_trx_commint=2,表示每次事務(wù)提交都將redolog寫到page cache中。弊端（當主機重啟會丟失page cache中的數(shù)據(jù)）；
InnoDB后臺有個線程每隔一秒，會將redolog buffer中的日志，寫入到page cache中，然后調(diào)用fsync持久化到磁盤。
未提交事務(wù)的redolog也可能持久化到磁盤上:
1.InnoDB后臺線程在持久化磁盤時候也會將為提交事務(wù)的redolog持久化到磁盤上
2.redolog buffer占用的空間到達redolog_buffer_size配置的空間一半時候，后臺線程會主動寫盤。
3.當innodb_flush_log_at_trx_commit=1時候當有事務(wù)提交需要持久化到磁盤，會攜帶未提交事務(wù)的redolog一起寫盤。
binlog寫入機制：
事務(wù)在執(zhí)行過程中先寫入到binlog cache中，接著寫入到binlog files（page cache）中，隨后fsync持久化到磁盤上。
binlog cache:它適用于緩存binlog event的內(nèi)存，大小有binlog_cache_size指定大小。
binlog cache臨時文件：是一個臨時磁盤文件，存儲由于binlog cache不足溢出的binlog cache的event，由max_binlog_size配置大小。
binlog寫入時策略，由參數(shù)sync_binlog控制：
1.sync_binlog=0時，表示每次提交事務(wù)都只將binlog_cache日志寫入到page cache中，不執(zhí)行fsync寫入磁盤。（主機發(fā)生重啟 page cache中的事務(wù)會丟失）
2.sync_binlog=1時，表示每次事務(wù)提交會將binlog_cache日志寫入到page_cache中，然后執(zhí)行fsync寫入磁盤。
3.sync_binlog=N(N>1)時表示每次事務(wù)提交都寫入到page cache中，累計到N個事務(wù)提交時候，一起執(zhí)行fsync寫入磁盤，提高IO性能。（主機發(fā)生重啟 page cache中的小于N個事務(wù)會丟失）
組提交機制寫入磁盤
redolog在寫入磁盤時候攜帶別的已提交的事務(wù)一起寫入的磁盤。提高IO性能。由參數(shù)1.binlog_group_commit_sync_delay表示延遲多少微妙才調(diào)用fsync寫入磁盤。
2.binlog_group_commit_sync_no_delay_count表示當累計多少個組員后調(diào)用fsync寫入磁盤。
數(shù)據(jù)丟失情況
1.innodb_flush_log_at_trx_commint=0(只寫入redolog buffer中) sync_binlog=1（每次事務(wù)提交持久化到磁盤）當mysql異常重啟redolog buffer中的數(shù)據(jù)還未來得及持久化就會丟失，binlog日志完整導致數(shù)據(jù)不一致。
2.innodb_flush_log_at_trx_commint=0(只寫入redolog buffer中) sync_binlog=N(N>1)（累計到達多少個事務(wù)才會持久化磁盤）如果主機重啟redolog buffer中的數(shù)據(jù)還未來得及持久化就會丟失，存儲binlog的page cache日志 會丟失<N的數(shù)據(jù)。
3..innodb_flush_log_at_trx_commint=0(只寫入redolog buffer中) sync_binlog=0異常重啟或主機重啟數(shù)據(jù)丟失
4..innodb_flush_log_at_trx_commint=1(每次事務(wù)提交持久化磁盤) sync_binlog=0 mysql異常重啟或主機重啟數(shù)據(jù)丟失。
5.innodb_flush_log_at_trx_commint=1； sync_binlog=1（不會發(fā)生數(shù)據(jù)丟失）
6.innodb_flush_log_at_trx_commint=1； sync_binlog=N（N>1）主機重啟存儲binlog的page cache丟失數(shù)據(jù)。
7.innodb_flush_log_at_trx_commint=2（每次事務(wù)提交都只寫入到page cache中）； sync_binlog=0主機斷電兩邊數(shù)據(jù)都丟失，mysql重啟存存儲binlog的page cache丟失數(shù)據(jù)。
8.innodb_flush_log_at_trx_commint=2； sync_binlog=1 主機斷電redolog日志丟失數(shù)據(jù)。
9，innodb_flush_log_at_trx_commint=2； sync_binlog=N 主機斷電兩邊日志都丟失
17.Mysql主備
主從結(jié)構(gòu)有：一主一從，雙M結(jié)構(gòu)兩個節(jié)點都可以當主庫來使用。
主備延遲的來源
1.主備機器性能不一致導致的。（備庫執(zhí)行同步速度低于主庫產(chǎn)生binlog速度）
2.備庫壓力大，覺得備庫提供讀能力，不克制隨意的執(zhí)行讀操作，可能耗費大量的cpu資源影響同步速度，造成主備延遲。
3.大事物導致的，在做一個更新操作耗時很多，導致同步給備庫執(zhí)行，延遲的時間就是這個大事務(wù)的耗時時間（一次性刪除過多的數(shù)據(jù)，耗時太久?；蛘咭粡埓蟊韴?zhí)行DDL操作）
4.備庫在執(zhí)行備份操作
5.設(shè)置了是延遲備庫
6.備庫空間不足
備庫并行同步數(shù)據(jù)
mysql5.6之前只支持單線程同步數(shù)據(jù)（sql_thread），如果主庫高并發(fā)，就會出現(xiàn)主備延遲問題。
mysql5.6之后對其進行優(yōu)化使用多個線程進行數(shù)據(jù)復制。
圖片.png
coordinator線程也就是之前的sql_thread線程coordinator只負責讀取中轉(zhuǎn)日志和分發(fā)事務(wù)，不在同步數(shù)據(jù)了，同步數(shù)據(jù)的任務(wù)交給worker線程執(zhí)行。在coordinator分發(fā)事務(wù)時候遵循兩個條件：
1.不能造成更新覆蓋，這也就要求更新同一行的兩個事務(wù)，必須發(fā)給同一個worker線程執(zhí)行（避免了如果分發(fā)給兩個worker執(zhí)行，由于cup調(diào)度可能會發(fā)生執(zhí)行順序不一樣，覆蓋更新）
2.同一個事務(wù)不能被拆分，必須放到同一個worker中。
mysql5.7提夠了并行復制策略由 參數(shù)binlog-transaction-dependency-tracking控制
1.COMMIT_ORDER:表示同時進入prepare和commit的事務(wù)可以并行復制
2.WRITESET表示對于事務(wù)涉及更新的每一行，計算出這行的hash值，組成集合writeset，如果兩個事務(wù)沒有更新同一行，也就是兩個writeset沒有交集，就可以并行。
3.WRITESET_SESSION表示在WRITESET的基礎(chǔ)上多了一個約束，即在主庫上同一個線程先后執(zhí)行多個事務(wù)，在備庫執(zhí)行的時候也要保證相同的執(zhí)行順序。
18.主從切換復制數(shù)據(jù)出現(xiàn)錯誤如何解決
1.使用sql_slave_skip_counter命令主動跳過出現(xiàn)錯誤的事務(wù)。
2.使用slave_skip_errors參數(shù)設(shè)置跳過指定的錯誤如slave_skip_errors=1032,1062。其中1032是刪除數(shù)據(jù)找不到行，1062錯誤是插入數(shù)據(jù)唯一鍵沖突。
3.上面兩種方法操作都比較復雜，我們還可以使用GTID來解決；GTID=source_id:transaction_id，啟動mysql配置參數(shù)gtid_mode=on和enforce_gtid_consistency=on
19.讀寫分離有哪些坑
解決主從數(shù)據(jù)不一致，過期讀的方案
1.對于要求實時性比較高的數(shù)據(jù)，主從延遲導致主從數(shù)據(jù)不一致，可以將這部分查詢數(shù)據(jù)放到主庫上讀。
2.在讀從庫時候可以Sleep休眠一下，可以解決但是是不靠譜，不好控制休眠時間。
3.使用判斷主備是否有延遲在進行查詢從庫，show slave status 可以查到second_behind_master值如果為0，說明無延遲。單位是s級別的，也不是很準確
3.semi-sync方式，事務(wù)提交主庫生成的binlog發(fā)送給從庫，從庫收到binlog以后，會發(fā)給主庫一個ack表示我已收到。主庫收到這個ack時候響應(yīng)給客戶端事務(wù)完成。弊端：一主一從方案滿足，但是一主多從方案可能也會產(chǎn)生過期度。
20.查詢數(shù)據(jù)那么多不會OOM嗎？
InnoDB保存數(shù)據(jù)在主鍵索引上的，全表掃描實際上掃描的是主鍵索引，查到的每一行都直接放入到結(jié)果集返回給客戶端，獲取到數(shù)據(jù)放入到net_buffer中，大小可通過參數(shù)net_buffer_length控制，默認16k，當net_buffer寫滿調(diào)用發(fā)送接口發(fā)送給客戶端，發(fā)送成功情況net_buffer繼續(xù)讀取數(shù)據(jù)。
buffer pool淘汰數(shù)據(jù)頁策略：使用的LRU算法，淘汰最久未使用的數(shù)據(jù)。將Buffer pool的鏈表分成yong區(qū)和old區(qū)比例5:3，如何淘汰一個數(shù)據(jù)頁，有新的數(shù)據(jù)頁需要加入到buffer pool中需要淘汰處于old區(qū)鏈表的尾部的數(shù)據(jù)頁，
yong區(qū)數(shù)據(jù)被訪問直接將該數(shù)據(jù)頁加入到y(tǒng)ong鏈表頭部，
old區(qū)數(shù)據(jù)頁被訪問需要做判斷，如果這個數(shù)據(jù)頁在old區(qū)超過1s就需要將該數(shù)據(jù)頁移動到y(tǒng)ong區(qū)的頭部，如果短于1s位置不變。
21.join到底該如何使用
使用join有這幾種方式
1.simple Nested-Loop Join算法：對比數(shù)據(jù)是在數(shù)據(jù)庫層面做對比，
Blocked Nested-Loop join算法：數(shù)據(jù)讀到內(nèi)存（join_buffer_size 控制內(nèi)存大小）進行判斷比較，如果數(shù)據(jù)過多可以將數(shù)據(jù)分塊讀取。(注意項：如果驅(qū)動表是大的冷數(shù)據(jù)表，如果join_buffer不夠大會全表掃描多次，不僅會導致IO壓力大還有一個致命的問題，多次掃描會導致buffer pool中的yong區(qū)數(shù)據(jù)頁會被淘汰，導致查詢頻繁的數(shù)據(jù)頁需要重新從磁盤上掃描出來放入buffer pool中)
INdex Nested-Loop join：使用索引進行查詢比較效率高，掃描的行數(shù)較少。
22.為什么自增id不是連續(xù)的？
產(chǎn)生這種不連續(xù)的原因有，1.一個事務(wù)執(zhí)行發(fā)生異常回滾了，在這個事務(wù)執(zhí)行過程中有其他事物對自增主鍵incr了，為什么發(fā)生回滾不回滾id，如果也會滾id導致下次增加插入數(shù)據(jù)主鍵沖突
2.當執(zhí)行批量插入的時候，為了避免其他事物需要等待，mysql在批量插入引入事先申請自增的id，如：第一次插入申請2個，第二次插入申請4個每次申請都是上次申請的2倍，這樣就會導致最后一次申請的id過多，導致出現(xiàn)較大的id不連續(xù)。
3.隨意修改主鍵的值
23.InnoDB引擎四大特性
1.插入緩沖；2.doubleWrite；3自適性hash；4預讀
