17. 如何正確顯示隨機(jī)數(shù)

直接使用order byrand()，語句需要Using temporary和 Using filesort， 查詢的執(zhí)行代
價往往是比較大的。
MySQL對臨時表排序的執(zhí)行過程

1. 先全表掃描復(fù)制到臨時表（掃描全部的行）
2. 對臨時表排序，全表掃描（掃描全部的行），取出要排序的字段值，和位置信息（臨時表在內(nèi)存中沒有索引）
3. 根據(jù)位置信息，取需要的數(shù)據(jù)（隨機(jī)IO,只需要掃描n行）

limit關(guān)鍵字掃描多少行？
MySQL處理limit Y,1 的做法就是按順序一個一個地讀出來， 丟掉前Y個， 然后把下一個記錄作為返回結(jié)果， 因此這一步需要掃描Y+1行

18. 為什么SQL語句邏輯相同，性能卻差異巨大？

對索引字段做函數(shù)操作或者計算， 可能會破壞索引值的有序性， 因此優(yōu)化器就決定放棄走樹搜索功能。
此外隱式的類型轉(zhuǎn)換或者字符集轉(zhuǎn)換，實際也是加了"函數(shù)操作"，因此也會放棄走索引。
解決方法：試著修改sql的查詢條件，避免直接做函數(shù)操作，比如將函數(shù)操作放在查詢條件的右邊，比如where id=1000-1不要寫成id+1=1000

19.為什么只查詢一行語句也執(zhí)行地這么慢？

如果MySQL數(shù)據(jù)庫本身就有很大的壓力， 導(dǎo)致數(shù)據(jù)庫服務(wù)器CPU占用率很高或ioutil（IO利用率） 很高， 這種情況下所有語句的執(zhí)行都有可能變慢，不在討論范圍內(nèi)。

• 查詢長時間不返回
  大概率是表t被鎖住了，分析原因的時候， 一般首先執(zhí)行show processlist命令， 查看當(dāng)前語句處于什么狀態(tài)
  1.等MDL鎖
  Waiting for table metadata lock，有一個線程正在表t上請求或者持有MDL寫鎖， 把select語句
  堵住了，處理方式： 找到誰持有MDL寫鎖， 然后把它kill掉
  2.等flush
  Waiting for table flush 可能情況是： 有一個flush tables命令被別的語句堵住了， 然后它又堵住了select語句。(flush table(s) 的作用關(guān)閉所有已打開的表對象（同一個庫中，沒有sql運(yùn)行的表是不計算在內(nèi)），同時將查詢緩存中的結(jié)果清空。就是說Flush tables的一個效果就是會等待所有正在運(yùn)行的SQL請求結(jié)束)
  3.等行鎖
  解決方式：kill掉占用鎖線程的語句（不一定有用），或者直接關(guān)閉占有線程的鏈接，此時會回滾，釋放行鎖
• 查詢慢

1. 沒有索引
2. 有索引，但是為一致性讀，都之前其他事務(wù)修改了多次，由于mvcc導(dǎo)致需要回放很多次undo log才能計算到一致性試圖

20.幻讀是什么？

幻讀指的是一個事務(wù)在前后兩次查詢同一個范圍的時候， 后一次查詢看到了前一次查詢沒有看到的行。普通的查詢是快照讀， 是不會看到別的事務(wù)插入的數(shù)據(jù)的。 因此，幻讀在“當(dāng)前讀”下才會出現(xiàn)。

幻讀有什么問題？

1. 加鎖的語義失效
2. 數(shù)據(jù)不一致問題，即使把所有的記錄都加上鎖， 還是阻止不了新插入的記錄

產(chǎn)生原因：行鎖只能鎖住行， 但是新插入記錄這個動作， 要更新的是記錄之間的“間隙”

解決辦法：InnoDB只好引入了是間隙鎖(GapLock)

• 跟間隙鎖存在沖突關(guān)系的， 是“往這個間隙中插入一個記錄”這個操作， 間隙鎖之間都不存在沖突關(guān)系
• 間隙鎖和行鎖合稱next-keylock， 每個next-keylock是前開后閉區(qū)間。 也就是說 如果用select * from t for update要把整個表所有記錄鎖起來， 就形成了7個next-keylock， 分別是 (-∞,0]、 (0,5]、 (5,10]、 (10,15]、 (15,20]、 (20, 25]、 (25, +supremum
• 間隙鎖和next-key lock的引入， 幫我們解決了幻讀的問題， 但同時也帶來了一些“困擾”。 可能會導(dǎo)致同樣的語句鎖住更大的范圍， 這其實是影響了并發(fā)度的，甚至?xí)?dǎo)致死鎖
  -間隙鎖是在可重復(fù)讀隔離級別下才會生效的。

所以如果把隔離級別設(shè)置為讀提交的話，就沒有間隙鎖了。 但同時需要解決可能出現(xiàn)的數(shù)據(jù)和日志不一致問題(RC模式下采用的是statment，binlog的記錄順序是以commit方式提交的,并發(fā)事務(wù)會導(dǎo)致日志不一致)， 需要把binlog格式設(shè)置為row。(如果讀提交隔離級別夠用，即業(yè)務(wù)不需要可重復(fù)讀的保
證，讀提交+binlog_format = row 這個選擇是合理)

21. 為什么我只改一行的語句， 鎖這么多？（MySQL的加鎖規(guī)則）

5.x系列<=5.7.24， 8.0系列 <=8.0.13，默認(rèn)可重復(fù)讀級別的加鎖規(guī)則：
鎖都是加在索引上的，總結(jié)為兩個“原則”、 兩個“優(yōu)化”和一個“bug”

1. 原則1： 加鎖的基本單位是next-keylock, next-keylock是前開后閉區(qū)間，(m,n]。
2. 原則2： 查找過程中訪問到的對象才會加鎖。
3. 優(yōu)化1： 索引上的等值查詢， 給唯一索引加鎖的時候， next-keylock退化為行鎖。
4. 優(yōu)化2： 索引上的等值查詢， 向右遍歷時且最后一個值不滿足等值條件的時候， next-keylock退化為間隙鎖。(非唯一索引一般是向后遍歷，如果desc排序則會向前遍歷保證順序，但是找到第一個滿足值后依然為向后再遍歷一個間隙加鎖，然后再從后往前倒敘遍歷，具體參考21講課后思考題)
5. 一個bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止

索引加鎖
如果是覆蓋索引，則鎖不會加到主鍵索引上，符合規(guī)則2。 但如果select 選了其他字段，則會鎖住主鍵索引

limit語句加鎖
在循環(huán)掃描到滿足limit的行數(shù)時候，就停止了，并不會繼續(xù)向右掃描，按上述規(guī)則分析的鎖范圍很可能會變小。因此建議刪除數(shù)據(jù)的時候盡量加limit， 這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)讓操作更安全， 還可以減小加鎖的范圍

next-key lock 兩步加鎖
分析加鎖規(guī)則的時候可以用next-keylock來分析。 但是具體執(zhí)行的時候， 是要分成間隙鎖和行鎖兩段來執(zhí)行的

例如：

image.png

1. session A 啟動事務(wù)后執(zhí)行查詢語句加lock in share mode， 在索引c上加了next-key
   lock(5,10] 和間隙鎖(10,15)；
2. session B 的update語句也要在索引c上加next-keylock(5,10] ， 進(jìn)入鎖等待；
3. 然后session A要再插入(8,8,8)這一行， 被session B的間隙鎖鎖住。 由于出現(xiàn)了死
   鎖， InnoDB讓session B回滾。

你可能會問， session B的next-keylock不是還沒申請成功嗎？為什么會死鎖？
其實是這樣的， session B的“加next-keylock(5,10] ”操作， 實際上分成了兩步， 先是加(5,10)的間隙鎖， 加鎖成功； 然后加c=10的行鎖， 這時候才被鎖住的。（申請到了間隙鎖，因此session A的插入語句和sessionB的更新語句形成了死鎖）

關(guān)于讀提交隔離級別：
間隙鎖的部分，只剩下行鎖的部分。
但其實讀提交隔離級別在外鍵場景下還是有間隙鎖， 相對比較復(fù)雜先不展開。
另外， 在讀提交隔離級別下還有一個優(yōu)化， 即： 語句執(zhí)行過程中加上的行鎖， 在語句執(zhí)行完成
后， 就要把“不滿足條件的行”上的行鎖直接釋放了， 不需要等到事務(wù)提交。
也就是說， 讀提交隔離級別下， 鎖的范圍更小， 鎖的時間更短， 這也是不少業(yè)務(wù)都默認(rèn)使用讀提
交隔離級別的原因

22.MySQL有哪些“飲鴆止渴”提高性能的方法？

• 短連接風(fēng)暴
  正常的短連接模式就是連接到數(shù)據(jù)庫后， 執(zhí)行很少的SQL語句就斷開， 下次需要的時候再重連。如果使用的是短連接， 在業(yè)務(wù)高峰期的時候， 就可能出現(xiàn)連接數(shù)突然暴漲的情況。
  解決方法：
  1） 先處理掉那些占著連接但是不工作的線程
  比如優(yōu)先斷開事務(wù)外空閑太久的連接，從服務(wù)端斷開連接使用的是kill connection + id的命令， 一個客戶端處于sleep狀態(tài)時， 它的連接被服務(wù)端主動斷開后， 這個客戶端并不會馬上知道。 直到客戶端在發(fā)起下一個請求的時候， 才會收到報錯
  2）減少連接過程的消耗
  比如讓數(shù)據(jù)庫跳過權(quán)限驗證階段，跳過權(quán)限驗證的方法是： 重啟數(shù)據(jù)庫， 并使用–skip-grant-tables參數(shù)啟動。 這樣， 整個MySQL會跳過所有的權(quán)限驗證階段， 包括連接過程和語句執(zhí)行過程在內(nèi)。但是， 這種方法風(fēng)險極高。

在實際開發(fā)中， 我們也要盡量避免一些低效的方法， 比如避免大量地使用短連接。 同時， 連接異常斷開是常有的事， 代碼里要有正確地重連并重試的機(jī)制。

• 慢查詢性能問題
  在MySQL中， 會引發(fā)性能問題的慢查詢， 大體有以下三種可能：
  1.） 索引沒有設(shè)計好；->緊急創(chuàng)建索引
  2.） SQL語句沒寫好；->query_rewrite功能, 查詢重寫，但是會存在誤傷
  3.）MySQL選錯了索引； ->使用force index
• QPS突增問題
  1.） 一種是由全新業(yè)務(wù)的bug導(dǎo)致的。 假設(shè)你的DB運(yùn)維是比較規(guī)范的， 也就是說白名單是一個個加的。 這種情況下， 如果你能夠確定業(yè)務(wù)方會下掉這個功能， 只是時間上沒那么快， 那么就可以從數(shù)據(jù)庫端直接把白名單去掉。
  2.） 如果這個新功能使用的是單獨(dú)的數(shù)據(jù)庫用戶， 可以用管理員賬號把這個用戶刪掉， 然后斷開現(xiàn)有連接。 這樣這個新功能的連接不成功， 由它引發(fā)的QPS就會變成0。
  3.）如果這個新增的功能跟主體功能是部署在一起的， 那么我們只能通過處理語句來限制，可以使用上面提到的查詢重寫功能， 把壓力最大的SQL語句直接重寫成"select 1"返回