見原文csdn
https://blog.csdn.net/weixin_47467550/article/details/142591006?fromshare=blogdetail&sharetype=blogdetail&sharerId=142591006&sharerefer=PC&sharesource=weixin_47467550&sharefrom=from_link
目錄
Mysql最佳化就四種：定位慢查詢/sql執行計劃/索引/Sql最佳化經驗 2
1Mysql如何定位慢查詢？ 2
2Sql語句執行很慢，如何分析呢？ 3
2.1那這個SQL語句執行很慢,如何分析呢? 3
3．瞭解過索引嗎?(什麼是索引) 3
3.1什麼是聚簇索引，什麼是非聚簇索引？什麼是回表查詢？ 3
3.1.1聚集索引選取規則 4
3.1.2回表查詢 4
4什麼是覆蓋索引 5
4.1超大分頁處理？ 5
5索引建立原則有哪些？ 6
6什麼情況下索引會失效？ 6
7談談你對sql最佳化的經驗 7
7.1表的設計最佳化、避免索引失效、sql語句最佳化等 7
8事務的特性 9
9併發事務的問題，隔離級別 9
9.1解決方案，對事務進行隔離 9
10undo log和redo log的區別？ 11
11解釋一下mvcc？ 12
12.mysql主從原理？ 13
13專案中用過分庫分表嗎? 14
13.1垂直分庫和分表 14
13.2水平分庫 14
Mysql最佳化就四種：定位慢查詢/sql執行計劃/索引/Sql最佳化經驗
1Mysql如何定位慢查詢？
基本就是頁面載入慢，介面的響應測試時間長。
怎麼確定是mysql的問題呢，如果是sql問題，怎麼找出慢的原因呢。
方案一：開源工具：監聽除錯。除錯工具arthas。運維工具：普羅米修斯，skywalking
方案一主要就是哪些介面慢，sql時間長給監控排序羅列出來。
方案二：慢日誌查詢。
慢查詢日誌開啟slow-query-log=1
慢日誌時間為2long-query-time=2
Sql超過兩秒則記錄/一般除錯的時候才開啟，生產的時候不開啟不然會損壞mysql效能
1.介紹一下當時產生問題的場景(我們當時的一個介面測試的時候非常的慢，壓測的結果大概5秒鐘)
2.我們系統中當時採用了運維工具(Skywalking)，可以監測出哪個介面，最終因為是sql的問題
3.在mysql中開啟了慢日誌查詢，我們設定的值就是2秒，一旦sql執行超過2秒就會記錄到日誌中(除錯階段)
2Sql語句執行很慢，如何分析呢？
Explain和desc就可以分析sql語句了
欄位名詞解釋：
possible key 當前sql可能會使用到的索引
key 當前sql實際命中的索引
key len 索引佔用的大小
extra額外的最佳化建議
type 這條sql的連線的型別，效能由好到差為NULL、system、const、eq ref、ref、range、index、all
2.1那這個SQL語句執行很慢,如何分析呢?
可以採用MySQL自帶的分析工具 EXPLAIN
透過key和key len檢查晟否命中了索引(索引本身存在是否有失效的情況)
透過type欄位檢視sql是否有進一步的最佳化空間，是否存在全索引掃描或全盤掃描
透過extra建議判斷，是否出現了回表的情況，如果出現了，可以嘗試新增索引或修改返回欄位來修復
3．瞭解過索引嗎?(什麼是索引)
索引(index)是幫助MySQI高效獲取資料的資料結構(有序)。在資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構(B+樹)，這些資料結構以某種方式引用(指向)資料，這樣就可以在這些資料結構上實現高階查詢演算法這種資料結構就是索引。
3.1什麼是聚簇索引，什麼是非聚簇索引？什麼是回表查詢？
個人理解，聚簇全是主鍵，然後拿行資料。只有一行
非聚簇就是透過某個資料欄位找這個主鍵，可能會有多個。
聚簇索引也就是聚集索引。
二級索引也就是非聚集索引。
什麼是聚集索引，什麼是二級索引(非聚集索引)什麼是回表?
聚集索引(Clustered Index)將資料儲存與索引放到了一塊，索引結構的葉子節點儲存了行資料必須有,而且只有一個，個人理解只對應一行資料
二級索引(Secondary Index)將資料與索引分開儲存，索引結構的葉子節點關聯的是對應的主鍵可以存在多個，葉子節點儲存對應的主鍵值。
3.1.1聚集索引選取規則
如果存在主鍵，主鍵索引就是聚集索引。
如果不存在主鍵，將使用第一個唯一(UNIQUE)索引作為聚集索引。
如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會自動生成一個rowid作為隱藏的聚集索引。
3.1.2回表查詢
大多數的資料都用的非聚簇索引。
回表查詢就是透過二級索引找到對應的主鍵值，到聚集索引中查詢整行資料，這個過程就是回表
4什麼是覆蓋索引
個人理解，不用回表查詢的查詢語句，比如侷促和非聚簇都可以的。
4.1超大分頁處理？
覆蓋索引+子查詢
比如分頁查詢需要九百萬limit僅僅需要十條怎麼才能快？
正常直接limit獲取*你這樣拿所有行資料很慢。
但你如果先拿到id，再透過id來查詢就非常快，省去了九百萬的時間代價。
5索引建立原則有哪些？
先陳述自己在工作中用到的，主鍵索引，唯一索引，根據業務建立的複合索引
單表超過十萬條資料，可以建立索引。
3儘量不要好幾條資料都是北京市，這種欄位不適合索引
4儘量欄位短一些，太長的可以擷取一部分，建立字首索引。
5儘量使用聯合索引或者叫做複合索引。
總結重點：資料量大；查詢頻繁；排序，分組，查詢條件的欄位；儘量聯合索引，避免回表；控制數量。
6什麼情況下索引會失效？
命中索引就是查詢的時候使用到了索引。
1.複合索引。不能跳過某一列去查詢，可能會失效。違反最左字首法則，索引失效。
2.範圍查詢，>1後面的索引是失效的。這叫範圍查詢最右邊的列，不能使用索引。
3.不要在索引列上運算，不然會失效
4.型別轉換，比如明明是字串，您不加單引號，那也索引失效。
5.%開頭的模糊查詢也會失效，放在末尾%不會影響索引
7談談你對sql最佳化的經驗
7.1表的設計最佳化、避免索引失效、sql語句最佳化等
參考阿里開發手冊嵩山版本。
- 型別選擇，tinyint或者char是固定型別但效能好。
- 索引建立原則，查詢的時候避免索引失效
8事務的特性
ACID分別就是原子性，隔離性，一致性，隔離性，永續性。
9併發事務的問題，隔離級別
- 髒讀就是，讀的是錯誤的修改後還沒提交的資料。
- 不能讀兩次，又是事務前，又是事務後。
9.1解決方案，對事務進行隔離
打叉就是解決的問題。
10undo log和redo log的區別？
緩衝池和資料頁。Sql操作肯定先去記憶體的緩衝池找資料操作，如果找不到就再去磁碟。然而操作緩衝池以後，要將資料同步到磁碟中，沒有同步過去的稱為髒頁。一旦伺服器當機，記憶體中的資料會丟失。於是就引進了redo log
關於mysql的事務提交，增刪改資料，為了提高效能。引入了兩塊區域，一個是記憶體結構，一個是磁碟結構。
磁碟的結構主要是儲存的資料頁，比如說某一個表的ibd檔案裡邊包含了很多資料頁。每個頁中儲存的就是sql一行行的資料。
增刪改首先會操作記憶體。記憶體的概念就是緩衝池。資料庫先操作記憶體，如果沒有資料才考慮磁碟，操作完記憶體會將資料同步到磁碟中。
主要是用來實現事務的永續性的。
Redo log 由兩部分組成，redo log buffer（記憶體）和redo log file （磁碟）
記憶體中有buffer pool和redo log buffer，當增刪改buffer pool的時候redo log buffer就記錄資料的變化。一旦發生變化，redo log buffer記錄資料頁的變化，就會把這些資料記錄到磁碟檔案中，也就是redo log file日誌檔案中。
所以一旦從記憶體同步資料到磁碟失敗的話，就會從redo log file日誌檔案中恢復資料
這個過程遵循WAL機制，write-ahead-logging就是先寫日誌redo log，所以當髒頁正常寫到磁碟中的時候，日誌就沒用了。這個日誌檔案在磁碟中是兩份，迴圈寫。
Undo log記錄相反日誌，可以實現事務的一致性和原子性。
11解釋一下mvcc？
個人理解大概實現了隔離性。
12.mysql主從原理？
主庫會把ddl（create-drop）和dml(增刪改)寫進binlog二進位制檔案，
從庫會有一個io thread執行緒來讀取這個二進位制日誌檔案，
然後寫進從庫的一箇中繼檔案relay log中，
再由中繼檔案透過sql thread執行緒同步到從庫的資料庫中。
13專案中用過分庫分表嗎?
單表資料量達到一千萬，或者20G以後。就要分庫分表了
13.1垂直分庫和分表
個人理解：垂直分庫，就是根據業務不同的表放在不同的庫中。
使用者微服務，商品微服務，訂單微服務。
比如基本資訊，詳細資訊，只有感興趣，點進去才會展示。
對比垂直分表，個人理解，不同的欄位拆成不同的表中。
熱資料冷資料。
特點:
1，冷熱資料分離2，減少IO過渡爭搶，兩表互不影響
13.2水平分庫
每個庫儲存的資料是不一樣的，但是型別是一樣的。所有庫的資料加起來才是這個業務的所有資料。
但是，你想要拿資料，該怎麼選擇庫呢，就對id進行一個分庫，可以路由規則
水平分庫用的更多一些，海量資料一般都水平分庫。
高併發提高磁碟io效能一般垂直分庫
垂直分表，冷熱資料分離/