最新Mysql大廠面試必會的34問題

1、mysql的隔離級別

四種隔離級別:

READ-UNCOMMITTED(讀取未提交)：最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重複讀。
READ-COMMITTED(讀取已提交)：允許讀取並發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重複讀仍有可能發生。
REPEATABLE-READ(可重複讀)：對同一欄位的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重複讀，但幻讀仍有可能發生。
SERIALIZABLE(可串列化)：最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重複讀以及幻讀

隔離級別臟讀不可重複讀幻讀READ UNCONMMITTED可能可能可能READ COMMITTED不可能可能可能REPRATABLE READ不可能不可能可能SERIALIZABLE不可能不可能不可能

1、臟讀：A，B兩事務，A事務會讀取到B事務未提交的數據，然後B因為某些原因回滾數據，所以A就讀取了B沒有提交的數據，也稱臟數據。

2、不可重複讀：在A事務中對同一數據兩次查詢不一致，可能原因是在A事務提交之前B事務對該數據進行了操作

3、幻讀：當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄，當之前的事務再次讀取該範圍的記錄時，會產生幻行，就像產生幻覺一樣，這就是發生了幻讀。

不可重複讀和臟讀的區別是，臟讀是某一事務讀取了另一個事務未提交的臟數據，而不可重複讀則是讀取了前一事務提交的數據。
幻讀和不可重複讀都是讀取了另一條已經提交的事務，不同的是不可重複讀的重點是修改，幻讀的重點在於新增或者刪除。
事務隔離就是為了解決上面提到的臟讀、不可重複讀、幻讀這幾個問題。

2、MYSQL性能優化

常用5種方式

最大連接數優化

**修改my.ini文件(永久修改)**

啟用查詢快取

特別注意：查詢快取從MySQL 5.7.20開始已被棄用，並在MySQL 8.0中被刪除。、

一種說法是不建議使用查詢快取，因為查詢快取往往弊大於利。查詢快取的失效非常頻繁，只要有對一個表的更新，這個表上的所有的查詢快取都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對於更新壓力大的資料庫來說，查詢快取的命中率會非常低。除非你的業務有一張靜態表，很長時間更新一次，比如系統配置表，那麼這張表的查詢才適合做查詢快取。
引擎優化

MyISAM存儲引擎：

場景：如果表主要是用於插入新記錄和讀出記錄，那麼選擇MyISAM能實現處理高效率。

優點：MyISAM引擎能提供較高的查詢效率，適用於對數據進行頻繁查詢操作的數據表（InnoDB提供提交、回滾、崩潰恢復能力及並發控制能力，適用於對數據更新操作頻率高的數據表）

索引優化

這裡是便於查詢可以設置索引，讓查詢效率變高.

聚合索引按照從左到右的匹配原則。也就是必須先匹配ID才能匹配name查詢。

全文檢索的查詢方式：

SELECT * FROM article WHERE MATCH(title, content) AGAINST(‘查詢字元串’)

全文索引只適合MyISAM引擎的數據表。並且只能對英文進行檢索

SQL語句優化

SQL優化的重心是查詢優化，查詢優化的重心是建立索引。所以查詢優化主要是避免出現導致索引失效的查詢。

①避免在索引列上出現null。

②不要在索引列上進行算術運算。：select age+1 from user

③避免實現!=或者<>、is null或者is not null、in等可能導致全表遍歷的操作。

④模糊查詢只能使用右邊%。

⑤where語句後儘可能少用小括弧、或者不要出現小括弧嵌套小括弧。

3、索引詳解

1、何為索引，有什麼用？

索引是存儲引擎用於提高資料庫表的訪問速度的一種數據結構。。常見的索引結構有：Hash、B數，B+樹。

索引的作用就是相當於目錄的作用。打個比方：我們在查字典的時候，如果沒有目錄，那我們就只能一頁一頁的去找我們需要查的那個字，速度很慢。如果有目錄了，我們只需要先去目錄里查找字的位置，然後直接翻到那一頁就行了。

數據是存儲在磁碟上的，查詢數據時，如果沒有索引，會載入所有的數據到記憶體，依次進行檢索，讀取磁碟次數較多。有了索引，就不需要載入所有數據，因為B+樹的高度一般在2-4層，最多只需要讀取2-4次磁碟，查詢速度大大提升。

2、索引的優缺點

優點：
- 加快數據查找的速度
為用來排序或者是分組的欄位添加索引，可以加快分組和排序的速度
加快表與表之間連接的速度
缺點：
- 建立索引需要佔用物理空間
會降低表的增刪改的效率，因為每次對錶記錄進行增刪改，需要進行動態維護索引，導致增刪改時間變長

注意：使用索引一定能提高查詢性能嗎?

大多數情況下，索引查詢都是比全表掃描要快的。但是如果資料庫的數據量不大，那麼使用索引也不一定能夠帶來很大提升。

4、什麼情況下需要建索引？

經常用於查詢的欄位
經常用於連接的欄位建立索引，可以加快連接的速度
經常需要排序的欄位建立索引，因為索引已經排好序，可以加快排序查詢速度

5、什麼情況下不建索引？

where條件中用不到的欄位不適合建立索引
表記錄較少
需要經常增刪改
參與列計算的列不適合建索引
區分度不高的欄位不適合建立索引，如性別等

6、索引的底層數據結構

1、hash表

哈希索引是基於哈希表實現的，對於每一行數據，存儲引擎會對索引列進行哈希計算得到哈希碼，並且哈希演算法要盡量保證不同的列值計算出的哈希碼值是不同的，將哈希碼的值作為哈希表的key值，將指向數據行的指針作為哈希表的value值。這樣查找一個數據的時間複雜度就是O(1)，一般多用於精確查找。

是鍵值對的集合，通過鍵(key)即可快速取出對應的值(value)，因此哈希表可以快速檢索數據（接近 O（1）

Hash 衝突問題

也就是說多個不同的 key 最後得到的 index 相同。通常情況下，我們常用的解決辦法是鏈地址法。鏈地址法就是將哈希衝突數據存放在鏈表中。就比如 JDK1.8 之前 HashMap就是通過鏈地址法來解決哈希衝突的。不過，JDK1.8 以後HashMap為了減少鏈表過長的時候搜索時間過長引入了紅黑樹。

既然哈希表這麼快，為什麼MySQL 沒有使用其作為索引的數據結構呢？

1.Hash 衝突問題：我們上面也提到過Hash 衝突了，不過對於資料庫來說這還不算最大的缺點。

2.Hash 索引不支援順序和範圍查詢(Hash 索引不支援順序和範圍查詢是它最大的缺點：假如我們要對表中的數據進行排序或者進行範圍查詢，那 Hash 索引可就不行了。

2、B 樹& B+樹

B 樹,全稱為多路平衡查找樹，B+ 樹是 B 樹的一種變體。B 樹和 B+樹中的 B 是

Balanced （平衡）的意思。

目前大部分資料庫系統及文件系統都採用 B-Tree 或其變種 B+Tree 作為索引結構。

進行查找操作時，首先在根節點進行二分查找，找到

key所在的指針，然後遞歸地在指針所指向的節點進行查找。直到查找到葉子節點，然後在葉子節點上進行二分查找，找出

key所對應的數據項。

B 樹& B+樹兩者有何異同呢？

B 樹的所有節點既存放鍵(key) 也存放數據(data)，而 B+樹只有葉子節點存放 key 和 data，其他內節點只存放 key。
B 樹的葉子節點都是獨立的;B+樹的葉子節點有一條引用鏈指向與它相鄰的葉子節點。
B 樹的檢索的過程相當於對範圍內的每個節點的關鍵字做二分查找，可能還沒有到達葉子節點，檢索就結束了。而 B+樹的檢索效率就很穩定了，任何查找都是從根節點到葉子節點的過程，葉子節點的順序檢索很明顯。
innoDB存儲引擎中的頁大小為16kb，一般主鍵類型int（佔4位元組）或者bigint（佔8位元組），指針類型也一般佔4或8位元組，也就是說一個頁（B+Tree中的一個節點）中大概可以存儲16KB/（8B+8B）=1K個鍵值。也就是說一個深度為3的B+Tree索引跨行業維護10^3 10^3 10^3=10億條記錄（8億）
實際情況中，每個節點並不能【填充滿，因此在資料庫中，B+Tree的高度一般都在2-4層，mysql的innoDB索引引擎在設計的時候是將根節點常駐放在記憶體中的，也就是說查找某一鍵值記錄最多只需要1-3次I/O操作。注意頂層頁常駐記憶體

7、Hash索引和B+樹索引的區別？

哈希索引不支援排序，因為哈希表是無序的。
哈希索引不支援範圍查找。
哈希索引不支援模糊查詢及多列索引的最左前綴匹配。
因為哈希表中會存在哈希衝突，所以哈希索引的性能是不穩定的，而B+樹索引的性能是相對穩定的，每次查詢都是從根節點到葉子節點。

8、為什麼B+樹比B樹更適合實現資料庫索引？

由於B+樹的數據都存儲在葉子結點中，葉子結點均為索引，方便掃庫，只需要掃一遍葉子結點即可，但是B樹因為其分支結點同樣存儲著數據，我們要找到具體的數據，需要進行一次中序遍歷按序來掃，所以B+樹更加適合在區間查詢的情況，而在資料庫中基於範圍的查詢是非常頻繁的，所以通常B+樹用於資料庫索引。
B+樹的節點只存儲索引key值，具體資訊的地址存在於葉子節點的地址中。這就使以頁為單位的索引中可以存放更多的節點。減少更多的I/O支出。
B+樹的查詢效率更加穩定，任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。

9、索引有什麼分類？

1、主鍵索引：名為primary的唯一非空索引，不允許有空值。

2、唯一索引：索引列中的值必須是唯一的，但是允許為空值。唯一索引和主鍵索引的區別是：唯一約束的列可以為null且可以存在多個null值。唯一索引的用途：唯一標識資料庫表中的每條記錄，主要是用來防止數據重複插入。創建唯一索引的SQL語句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、組合索引：在表中的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用，使用組合索引時需遵循最左前綴原則。

4、普通索引（單值索引）

5、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT類型欄位上使用全文索引。

10、什麼是最左匹配原則？

如果 SQL 語句中用到了組合索引中的最左邊的索引，那麼這條 SQL 語句就可以利用這個組合索引去進行匹配。當遇到範圍查詢(>、<、between、like)就會停止匹配，後面的欄位不會用到索引。

(a,b,c)建立索引，查詢條件使用 a/ab/abc 會走索引，使用 bc 不會走索引。如果查詢條件為

a = 1 and b > 2 and c = 3，那麼a、b個字兩段能用到索引，而c無法使用索引，因為b欄位是範圍查詢，導致後面的欄位無法使用索引。如下圖，對(a, b) 建立索引，a 在索引樹中是全局有序的，而 b 是全局無序，局部有序（當a相等時，會根據b進行排序）。

11、MySQL聚簇和非聚簇索引

都是B+樹的數據結構

1 、聚簇索引：葉子節點將數據存儲和索引存放在一起，並且是按照一定順序組織的，找到索引也就是找到數據，數據的物理存放順序和索引順序是一致的。即：只要索引相鄰，那麼對應的數據一定也是相鄰的存放在磁碟上的
2 、非聚簇索引：將數據存儲和索引分開存儲的，索引結構的葉子節點指向數據對應的位置

葉子節點不存儲數據、存儲的是數據的行地址（索引），也就是說根據索引查詢到的數據行的位置，再取磁碟查找數據，這個就類似一本樹的目錄。

在innoDB中，在聚簇索引之上創建的索引是非聚簇索引，非聚簇索引是輔佐索引，像複合索引、前綴索引、唯一索引。輔佐索引的葉子節點存儲的不再是行的物理位置，而是主鍵值，輔佐索引訪問數據總是需要二次查找

01、InnoDB中

InnoDB使用的是聚簇索引，將主鍵組織到一個棵樹中，而行數據就存儲在葉子節點上，若使用「where id = 4」這樣的條件查找主鍵，則按照B+樹的檢索演算法，即可查找到對應的葉子節點，之後獲得行數據
若對name列進行條件搜索，則需要兩步：
- 第一步：在輔佐索引B+樹中檢索name，到達其葉子節點獲取對應的主鍵
第二部：使用主鍵在主鍵索引B+樹再執行一次B+樹檢索操作，最終達到葉子節點可獲取整行數據（重點：在於通過其他鍵需要建立輔佐索引）
聚簇索引默認是主鍵，如果表中沒有定義主鍵InnoDB會選擇一個唯一且非空的索引代替。如果沒有這樣的索引，innoDB會隱式的定義一個主鍵（類似Oracle的Rowid）來作為聚簇索引。如果已經射設置了主鍵為聚簇索引，想希望單獨設置聚簇索引，必須先刪除主鍵，然後添加我們想要的聚簇索引，最終在恢復主鍵設置即可

02、MYISAM中

MYISAM使用的是非聚簇，非聚簇索引的兩棵B+樹看上去沒有太大的區別，節點的結構完全一致，至少2存儲的內瑞內容不一樣。主鍵索引B+樹的節點的存儲了主鍵，輔佐索引B+樹存儲了輔佐鍵，表的數據在獨立的地方，這兩課B+樹的葉子節點都使用地址指向真正的表數據，對錶數據來說，這兩個鍵沒有任何差別，由於索引樹是獨立的，通過輔佐鍵檢索無需訪問主鍵的索引樹

03、使用聚簇索引的優勢

問題：每次使用輔佐索引檢索都需要經過兩次的B+樹查找，看上去聚簇索引的效率明顯低於非聚簇索引，這不是多次一舉嗎，聚簇索引的優勢在哪呢？

1、由於行數據和聚簇索引的葉子節點存儲在一起，在同一頁會有多條行數據，訪問同一數據頁不同行記錄時，已經把頁的載入到Buffer（緩衝器），再次訪問的時，會再記憶體中完成訪問，不必再訪問磁碟，這樣主鍵和行數據是一起載入記憶體的，找到葉子節點就立刻將行數據返回了，如果按照ID來組織數據，獲取數據更快

2、輔佐索引的葉子節點，存儲主鍵值，而不是數據的存放地址。好處是當行數據發生變化時，索引樹的節點也需要分裂變化；或者是我們需要查找的數據，在上一次IO讀寫的快取中沒有，需要發送一次新的IO操作時，可以避免對輔佐索引的維護工作，只需要維護聚簇索引樹就好。另外好處就是，因為輔佐索引存放的時主鍵值，減少輔佐索引佔用的存儲空間大小

05、為什麼主鍵通常建議使用自增ID

12、什麼是覆蓋索引？

select的數據列只用從索引中就能夠取得，不需要回表進行二次查詢，也就是說查詢列要被所使用的索引覆蓋。對於

innodb表的二級索引，如果索引能覆蓋到查詢的列，那麼就可以避免對主鍵索引的二次查詢。

不是所有類型的索引都可以成為覆蓋索引。覆蓋索引要存儲索引列的值，而哈希索引、全文索引不存儲索引列的值，所以MySQL使用b+樹索引做覆蓋索引。

13、索引的設計原則？

索引列的區分度越高，索引的效果越好。比如使用性別這種區分度很低的列作為索引，效果就會很差。
盡量使用短索引，對於較長的字元串進行索引時應該指定一個較短的前綴長度，因為較小的索引涉及到的磁碟I/O較少，查詢速度更快。
索引不是越多越好，每個索引都需要額外的物理空間，維護也需要花費時間。
利用最左前綴原則。

14、什麼情況下無法利用索引（索引失效）

導致索引失效的情況：

1、對於組合索引，不是使用組合索引最左邊的欄位，則不會使用索引
2、查詢語句中使用like關鍵字

以%開頭的like查詢如%abc，無法使用索引；非%開頭的like查詢如abc%，相當於範圍查詢，會使用索引

3、查詢語句中使用OR關鍵字

查詢條件使用or連接

如果前後條件的列都是索引那麼可以利用索引
如果前後索引中華有一列不是索引，則無法利用索引
4、查詢條件中列類型是字元串，沒有使用引號，可能會因為類型不同發生隱式轉換，使索引失效
5、對索引列進行運算

15、什麼是前綴索引？

有時需要在很長的字元列上創建索引，這會造成索引特別大且慢。使用前綴索引可以避免這個問題。

前綴索引是指對文本或者字元串的前幾個字元建立索引，這樣索引的長度更短，查詢速度更快。

創建前綴索引的關鍵在於選擇足夠長的前綴以保證較高的索引選擇性。索引選擇性越高查詢效率就越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的數據行。

16、常見的存儲引擎有哪些？

MySQL中常用的四種存儲引擎分別是： MyISAM 、 InnoDB 、 MEMORY 、 ARCHIVE 。MySQL 5.5版本後默認的存儲引擎為InnoDB。

17、 Mysql鎖有哪些、如何理解

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-uVWXa7Hj-1650358046863)(//pizximfzuc.feishu.cn/space/api/box/stream/download/asynccode/?code=MjZhZDY3OTBhMzE1ODE4MGUxZTUyZTEyMjFhZjU0OTJfQVpRSDlaaERqU05HWVNrWlRwWjNzUDNobThvVzN1cUtfVG9rZW46Ym94Y25sVEtmcDFkdXYzZjVjbkJPbGkwUDRlXzE2NTAzNTc2NTY6MTY1MDM2MTI1Nl9WNA)]

18、 Mysql慢查詢如何優化

19、 Explain語句結果中查詢的各個欄位的意思

20、 Innodb如何實現事務的

Innodb通過Buffer Pool，Redo Log，Undo Log來實現事務，以一個update語句為例：

innodb在收到一個update語句後，會根據條件找到數據所在葉。並將該頁快取在buffer Pool中
執行update語句，修改Buffer Pool中的數據，也就是記憶體中的數據
針對update語句生成一個RedoLog對象，兵存入LogBuffer中
針對update語句生成的undoLog日誌，用於事務回滾
如果事務提交，則把RedoLog日對象進行持久化，後續還有其他機制將Buffer Pool中修改的數據頁持久化到磁碟中
如果事務回滾，則利用undoLog日誌進行回滾

21、 Redis和Mysql如何保證數據一致

1、先更新Mysql，再更新Redis，如果redis失敗，依舊可能數據不一致

2、先刪除redis中數據，再更新mysql，再次查詢的時候再更新數據添加到快取中。（這個方案解決了1的問題）。但是再高並發的情況下效率比較低下，並且還是可能有數據不一致的可能。比如執行緒1刪除後，再更新msql時，但是此時有一個執行緒2進來查詢時，就會將mysql中老數據又查詢到redis中。

3、``延遲雙刪。步驟：先刪除redis中的數據，再更新mysql，再隔幾百毫秒再刪除redis中快取數據。這樣就算在更新mysql時，有其他執行緒讀取mysql時，把老數據讀到redis中也會很快被刪掉

22、索引的基本原理

索引是用來快速查詢有特定值的記錄。如果沒有索引，一般來說執行查詢時候需要查詢全表

索引基本原理：把無序的數據變成有序的查詢

1、把創建索引的列的內容進行排序
2、對排序結果進行倒排列
3、在倒排表後面內容上拼接上數據地址鏈
4、在查詢的時候，先拿到倒排表內容，再取出數據地址鏈，從而就取出具體數據

23、 MyISAM和innoDb的區別

MyISAM:

不支援事務，但是每一次的查詢都是原子的
支援表級鎖，即每次操作都會對錶進行加鎖
存儲表的總行數
一個MyISAM表有三個文件：索引文件、表結構文件、數據文件
採用非聚簇索引，索引文件的數據有存儲指向數據文件的指針。輔助索引與主索引基本一致，但是輔助索引不用保證唯一性

InnoDb：

支援ACID的事務。支援事務的四種隔離級別
支援行級鎖與外鍵約束：因此可以支援寫並發
不存儲總行數
一個InnoDb引擎存儲在一個文件空間（共享表空間。表大小不受作業系統的控制，一個表可能分布在多個文化里），也可能為多個（設置為獨立表空，表大小受作業系統文件大小的控制，一般為2G）。受作業系統文件大小的限制
主鍵索引採用聚簇索引（索引的數據域存儲數據文件本身），輔助索引的數據域存儲主鍵的值；因此從輔助索引查找數據，需要先通過輔佐索引找到主鍵值，再訪問主索引；最好使用自增主鍵。防止插入數據時，為維護B+樹結構，文件的大整

24、ACID靠什麼保證的？

A原子性由undo log日誌保證，他記錄了需要回滾的日誌資訊，事務回滾時撤銷已近執行成功的sql
C 一致性由其他三大特性保證、數據上的一致需要程式程式碼要保證業務上的一致性
I 隔離性由MVCC來保證的
D 持久性由記憶體和redo log來保證，mysql修改數據同時在記憶體和redo log記錄這次操作。嘎機的時候可以從redo log恢復

redo log的刷盤會在系統空閑時候進行

25、什麼是MVCC

多版本並發控制：讀取數據時通過一種類似快照的方式將數據保存下來，這樣讀鎖和寫鎖就不衝突了，不同的事務session會看到自己的特定版本的數據，版本鏈

MVCC只在READ COOMMITED 和REPETABLE READ 兩個隔離級別下工作。其他兩個隔離級別和MVCC衝突不兼容，因為READ UNCOMMITED 總是讀取最新的數據行，而不是符合當前事務版本的數據行。而SESRIALIZABLE則會對所有的讀取行加鎖

聚簇索引記錄中有兩個必要的隱藏列

trx_id：用來存儲每次對某條聚簇索引記錄進行修改的時候事務id

roll_pointer：每次對哪條索引記錄進行修改的時候，都會把老版本寫入undo日誌中，這個roll_pointer就是保存了一個指針，它指向這條聚簇索引記錄的上一個版本的位置，通過它來獲取上一個版本的記錄資訊。（注意插入操作的undo日誌沒有這個屬性，因為它沒有老版本）

使用事務更新行記錄的時候，就會生成版本鏈，執行過程如下：

用排他鎖鎖住該行；
將該行原本的值拷貝到

undo log，作為舊版本用於回滾；

修改當前行的值，生成一個新版本，更新事務id，使回滾指針指向舊版本的記錄，這樣就形成一條版本鏈。

已提交讀和可重複讀的區別在於他們生成的ReadView的策瑜不同

無法複製載入中的內容

接下來了解下read view的概念。

read view可以理解成將數據在每個時刻的狀態拍成「照片」記錄下來。在獲取某時刻t的數據時，到t時間點拍的「照片」上取數據。

在read view內部維護一個活躍事務鏈表，表示生成read view的時候還在活躍的事務。這個鏈表包含在創建read view之前還未提交的事務，不包含創建read view之後提交的事務。

不同隔離級別創建read view的時機不同。

read committed：每次執行select都會創建新的read_view，保證能讀取到其他事務已經提交的修改。
repeatable read：在一個事務範圍內，第一次select時更新這個read_view，以後不會再更新，後續所有的select都是復用之前的read_view。這樣可以保證事務範圍內每次讀取的內容都一樣，即可重複讀。

總結：InnoDB 的MVCC是通過 read view 和版本鏈實現的，版本鏈保存有歷史版本記錄，通過read view 判斷當前版本的數據是否可見，如果不可見，再從版本鏈中找到上一個版本，繼續進行判斷，直到找到一個可見的版本。

26、關心過業務系統裡面的sql耗時嗎？統計過慢查詢嗎？對慢查詢都怎麼優化？

在業務系統中，除了使用主鍵進行查詢，還有其他的在測試庫上測試其耗時，慢查詢的統計一般主要由運維在做，會定期的將業務中的慢查詢回饋給我們、慢查詢的優化首先需要慢的原因是什麼？是查詢條件沒有命中索引？是load了需要的數據列，還是數據量過大？

所以優化也是針對這三個方向來的。

首先分析語句，看看是不是load了額外不需要的數據，可能是查詢了多餘的行並且拋棄掉了，可能是載入了許多結果中炳不需要的列，對語句進行分析以及重寫
分析語句的執行計劃，獲得其使用索引的情況，之後修改語句或者索引，使得語句可以儘可能的命中索引
如果語句的優化已經無法進行，可以考慮表中的數據量是否過大，如果是的話可以進行考慮分表

25、mysql中char與varchar的區別

都是用來存儲字元串的，只是他們的保存方式不一樣罷了

char有固定的長度，而varchar屬於可變長的字元類型。char 長度是固定的，不管你存儲的數據是多少他都會都固定的長度。而varchar則處可變長度但他要在總長度上加1字元，這個用來存儲位置

26、 mysql中的delete , drop 和truncate 區別

1、delete和truncate僅僅刪除表數據，trop連表數據和結構一起刪除，打個比方delete 是單殺，truncate 是團滅，drop 是把電腦摔了。
2、delete 是 DML 語句，操作完以後如果沒有不想提交事務還可以回滾，truncate 和 drop 是 DDL 語句，操作完馬上生效，不能回滾，打個比方，delete 是發微信說分手，後悔還可以撤回，truncate 和 drop 是直接扇耳光說滾，不能反悔。
3、執行的速度上， drop>truncate>delete ，打個比方，drop 是神舟火箭，truncate 是和諧號動車，delete 是自行車。

delete 是刪除一條數據，truncate是將這個表的所有數據都刪除，這兩種不刪除表的結構，可以用

27、關於sql和MySQL的語句執行順序

1、sql執行順序

from
join
on
where
group by(開始使用select中的別名，後面的語句中都可以使用)
avg,sum….
having
select
distinct
order by
limit

從這個順序中我們不難發現，所有的查詢語句都是從from開始執行的，在執行過程中，每個步驟都會為下一個步驟生成一個虛擬表，這個虛擬表將作為下一個執行步驟的輸入。

第一步：首先對from子句中的前兩個表執行一個笛卡爾乘積，此時生成虛擬表 vt1（選擇相對小的表做基礎表）。

第二步：接下來便是應用on篩選器，on 中的邏輯表達式將應用到 vt1 中的各個行，篩選出滿足on邏輯表達式的行，生成虛擬表 vt2 。

第三步：如果是outer join 那麼這一步就將添加外部行，left outer jion 就把左表在第二步中過濾的添加進來，如果是right outer join 那麼就將右表在第二步中過濾掉的行添加進來，這樣生成虛擬表 vt3 。

第四步：如果 from 子句中的表數目多餘兩個表，那麼就將vt3和第三個表連接從而計算笛卡爾乘積，生成虛擬表，該過程就是一個重複1-3的步驟，最終得到一個新的虛擬表 vt3。

第五步：應用where篩選器，對上一步生產的虛擬表引用where篩選器，生成虛擬表vt4，在這有個比較重要的細節不得不說一下，對於包含outer join子句的查詢，就有一個讓人感到困惑的問題，到底在on篩選器還是用where篩選器指定邏輯表達式呢？on和where的最大區別在於，如果在on應用邏輯表達式那麼在第三步outer join中還可以把移除的行再次添加回來，而where的移除的最終的。舉個簡單的例子，有一個學生表（班級,姓名）和一個成績表(姓名,成績)，我現在需要返回一個x班級的全體同學的成績，但是這個班級有幾個學生缺考，也就是說在成績表中沒有記錄。為了得到我們預期的結果我們就需要在on子句指定學生和成績表的關係（學生.姓名=成績.姓名）那麼我們是否發現在執行第二步的時候，對於沒有參加考試的學生記錄就不會出現在vt2中，因為他們被on的邏輯表達式過濾掉了,但是我們用left outer join就可以把左表（學生）中沒有參加考試的學生找回來，因為我們想返回的是x班級的所有學生，如果在on中應用學生.班級=’x’的話，left outer join會把x班級的所有學生記錄找回（感謝網友康欽謀__康欽苗的指正），所以只能在where篩選器中應用學生.班級=’x’ 因為它的過濾是最終的。

第六步：group by 子句將中的唯一的值組合成為一組，得到虛擬表vt5。如果應用了group by，那麼後面的所有步驟都只能得到的vt5的列或者是聚合函數（count、sum、avg等）。原因在於最終的結果集中只為每個組包含一行。這一點請牢記。

第七步：應用cube或者rollup選項，為vt5生成超組，生成vt6.

第八步：應用having篩選器，生成vt7。having篩選器是第一個也是為唯一一個應用到已分組數據的篩選器。

第九步：處理select子句。將vt7中的在select中出現的列篩選出來。生成vt8.

第十步：應用distinct子句，vt8中移除相同的行，生成vt9。事實上如果應用了group by子句那麼distinct是多餘的，原因同樣在於，分組的時候是將列中唯一的值分成一組，同時只為每一組返回一行記錄，那麼所以的記錄都將是不相同的。

第十一步：應用order by子句。按照order_by_condition排序vt9，此時返回的一個游標，而不是虛擬表。sql是基於集合的理論的，集合不會預先對他的行排序，它只是成員的邏輯集合，成員的順序是無關緊要的。對錶進行排序的查詢可以返回一個對象，這個對象包含特定的物理順序的邏輯組織。這個對象就叫游標。正因為返回值是游標，那麼使用order by 子句查詢不能應用於表表達式。排序是很需要成本的，除非你必須要排序，否則最好不要指定order by，最後，在這一步中是第一個也是唯一一個可以使用select列表中別名的步驟。

第十二步：應用top選項。此時才返回結果給請求者即用戶。

2、mysql的執行順序

1、SELECT語句定義

一個完成的SELECT語句包含可選的幾個子句。SELECT語句的定義如下：

SQL程式碼

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]

2、SELECT語句執行順序

SELECT語句中子句的執行順序與SELECT語句中子句的輸入順序是不一樣的，所以並不是從SELECT子句開始執行的，而是按照下面的順序執行：

開始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最終結果

28、事務的四大特性

事務特性ACID ：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。

原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾。
一致性是指一個事務執行之前和執行之後都必須處於一致性狀態。比如a與b賬戶共有1000塊，兩人之間轉賬之後無論成功還是失敗，它們的賬戶總和還是1000。
隔離性。跟隔離級別相關，如read committed，一個事務只能讀到已經提交的修改。
持久性是指一個事務一旦被提交了，那麼對資料庫中的數據的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

29、大表怎麼優化？

某個表有近千萬數據，查詢比較慢，如何優化？

當MySQL單表記錄數過大時，資料庫的性能會明顯下降，一些常見的優化措施如下：

限定數據的範圍。比如：用戶在查詢歷史資訊的時候，可以控制在一個月的時間範圍內；
讀寫分離：經典的資料庫拆分方案，主庫負責寫，從庫負責讀；
通過分庫分表的方式進行優化，主要有垂直拆分和水平拆分。

30、bin log/redo log/undo log

mysql日誌主要有查詢日誌、慢查詢日誌、事務日誌、錯誤日誌、二進位日誌。其中比較中亞的是bin log（二進位日誌）、redo log（重做日誌）、undo log（回滾日誌）

bin log

bin log是MySQL資料庫級別的文件，記錄對MySQL資料庫執行修改的所有操作，不會記錄select等語句，主要用於恢復資料庫和同步資料庫

redo log

redo log 是innodb引擎級別，用來記錄innodb存儲引擎的事務日誌，不管事務是否提交都會記錄下來，用於數據恢復。當資料庫發生故障，innodb存儲引擎會使用redo log恢復到發生故障前時刻，以此來保證數據的完整性。將參數innodb_flush_log_at_tx_commit設置為1，那麼在執行commit時會將redo log同步寫到磁碟。

undo log

除了記錄redo log外。當進行數據修改時還會記錄undo log, undo log用於數據的撤回操作，他保留了記錄修改前的內容。通過undo log可實現是事務的回滾，並且可以根據undo log回溯到某個特定的版本數據，實現 MVCC

31、bin log和redo log有什麼區別？

bin log會記錄所有日誌記錄，包括InnoDB、MyISAM等存儲引擎的日誌；redo log只記錄innoDB自身的事務日誌。
bin log只在事務提交前寫入到磁碟，一個事務只寫一次；而在事務進行過程，會有redo log不斷寫入磁碟。
bin log是邏輯日誌，記錄的是SQL語句的原始邏輯；redo log是物理日誌，記錄的是在某個數據頁上做了什麼修改。

32、分庫分表

當單表的數據量達到1000W或100G以後，優化索引、添加從庫等可能對資料庫性能提升效果不明顯，此時就要考慮對其進行切分了。切分的目的就在於減少資料庫的負擔，縮短查詢的時間。

數據切分可以分為兩種方式：垂直劃分和水平劃分。

垂直劃分

垂直劃分資料庫是根據業務進行劃分，例如購物場景，可以將庫中涉及商品、訂單、用戶的表分別劃分出成一個庫，通過降低單庫的大小來提高性能。同樣的，分表的情況就是將一個大表根據業務功能拆分成一個個子表，例如商品基本資訊和商品描述，商品基本資訊一般會展示在商品列表，商品描述在商品詳情頁，可以將商品基本資訊和商品描述拆分成兩張表。

優點：行記錄變小，數據頁可以存放更多記錄，在查詢時減少I/O次數。

缺點：

優點：行記錄變小，數據頁可以存放更多記錄，在查詢時減少I/O次數。

缺點：

主鍵出現冗餘，需要管理冗餘列；
會引起表連接JOIN操作，可以通過在業務伺服器上進行join來減少資料庫壓力；
依然存在單表數據量過大的問題。
水平劃分

水平劃分是根據一定規則，例如時間或id序列值等進行數據的拆分。比如根據年份來拆分不同的資料庫。每個資料庫結構一致，但是數據得以拆分，從而提升性能。

優點：單庫（表）的數據量得以減少，提高性能；切分出的表結構相同，程式改動較少。

缺點：

分片事務一致性難以解決
跨節點join性能差，邏輯複雜
數據分片在擴容時需要遷移

33、 having和where的區別？

二者作用的對象不同，where子句作用於表和視圖，having作用於組。
where在數據分組前進行過濾，having在數據分組後進行過濾。

34、樂觀鎖和悲觀鎖是什麼？

資料庫中的並發控制是確保在多個事務同時存取資料庫中同一數據時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀鎖和悲觀鎖是並發控制主要採用的技術手段。

悲觀鎖：假定會發生並發衝突，在查詢完數據的時候就把事務鎖起來，直到提交事務。實現方式：使用資料庫中的鎖機制。
樂觀鎖：假設不會發生並發衝突，只在提交操作時檢查是否數據是否被修改過。給表增加version欄位，在修改提交之前檢查version與原來取到的version值是否相等，若相等，表示數據沒有被修改，可以更新，否則，數據為臟數據，不能更新。實現方式：樂觀鎖一般使用版本號機制或CAS演算法實現。

Tags: JAVA 面試篇