當前位置:網站首頁>mysql 常見面試題

mysql 常見面試題

2022-01-28 04:33:35 程序員社區

附錄:

https://mp.weixin.qq.com/s/pC0_Y7M7BkoUmlRwneZZdA

 

一、為什麼用自增列作為主鍵

1、如果我們定義了主鍵(PRIMARY KEY),那麼InnoDB會選擇主鍵作為聚集索引。

如果沒有顯式定義主鍵,則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引。

如果也沒有這樣的唯一索引,則InnoDB會選擇內置6字節長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增,這個ROWID不像ORACLE的ROWID那樣可引用,是隱含的)。

2、數據記錄本身被存於主索引(一顆B+Tree)的葉子節點上,這就要求同一個葉子節點內(大小為一個內存頁或磁盤頁)的各條數據記錄按主鍵順序存放

因此每當有一條新的記錄插入時,MySQL會根據其主鍵將其插入適當的節點和比特置,如果頁面達到裝載因子(InnoDB默認為15/16),則開辟一個新的頁(節點)

3、如果錶使用自增主鍵,那麼每次插入新的記錄,記錄就會順序添加到當前索引節點的後續比特置,當一頁寫滿,就會自動開辟一個新的頁

4、如果使用非自增主鍵(如果身份證號或學號等),由於每次插入主鍵的值近似於隨機,因此每次新紀錄都要被插到現有索引頁得中間某個比特置

此時MySQL不得不為了將新記錄插到合適比特置而移動數據,甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉,此時又要從磁盤上讀回來,這增加了很多開銷

同時頻繁的移動、分頁操作造成了大量的碎片,得到了不够緊凑的索引結構,後續不得不通過OPTIMIZE TABLE來重建錶並優化填充頁面。

二、為什麼使用數據索引能提高效率

  1. 數據索引的存儲是有序的

  2. 在有序的情况下,通過索引查詢一個數據是無需遍曆索引記錄的

  3. 極端情况下,數據索引的查詢效率為二分法查詢效率,趨近於 log2(N)

三、B+樹索引和哈希索引的區別

B+樹是一個平衡的多叉樹,從根節點到每個葉子節點的高度差值不超過1,而且同層級的節點間有指針相互鏈接,是有序的,如下圖:

mysql 常見面試題插圖

哈希索引就是采用一定的哈希算法,把鍵值換算成新的哈希值,檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找,只需一次哈希算法即可,是無序的,如下圖所示:

mysql 常見面試題插圖1

四、哈希索引的優勢:

等值查詢,哈希索引具有絕對優勢(前提是:沒有大量重複鍵值,如果大量重複鍵值時,哈希索引的效率很低,因為存在所謂的哈希碰撞問題。)

五、哈希索引不適用的場景:

  1. 不支持範圍查詢

  2. 不支持索引完成排序

  3. 不支持聯合索引的最左前綴匹配規則

通常,B+樹索引結構適用於絕大多數場景,像下面這種場景用哈希索引才更有優勢:

在HEAP錶中,如果存儲的數據重複度很低(也就是說基數很大),對該列數據以等值查詢為主,沒有範圍查詢、沒有排序的時候,特別適合采用哈希索引,例如這種SQL:

# 僅等值查詢

select id, name from table where name='李明'; 

而常用的 InnoDB 引擎中默認使用的是B+樹索引,它會實時監控錶上索引的使用情况。

如果認為建立哈希索引可以提高查詢效率,則自動在內存中的“自適應哈希索引緩沖區”建立哈希索引(在InnoDB中默認開啟自適應哈希索引)。

通過觀察搜索模式,MySQL會利用index key的前綴建立哈希索引,如果一個錶幾乎大部分都在緩沖池中,那麼建立一個哈希索引能够加快等值查詢。

注意:在某些工作負載下,通過哈希索引查找帶來的性能提昇遠大於額外的監控索引搜索情况和保持這個哈希錶結構所帶來的開銷。

但某些時候,在負載高的情况下,自適應哈希索引中添加的read/write鎖也會帶來競爭,比如高並發的join操作。like操作和%的通配符操作也不適用於自適應哈希索引,可能要關閉自適應哈希索引。

六、B樹和B+樹的區別

1、B樹,每個節點都存儲key和data,所有節點組成這棵樹,並且葉子節點指針為nul,葉子結點不包含任何關鍵字信息。

mysql 常見面試題插圖2

2、B+樹,所有的葉子結點中包含了全部關鍵字的信息,及指向含有這些關鍵字記錄的指針,且葉子結點本身依關鍵字的大小自小而大的順序鏈接

所有的非終端結點可以看成是索引部分,結點中僅含有其子樹根結點中最大(或最小)關鍵字。 (而B 樹的非終節點也包含需要查找的有效信息)

mysql 常見面試題插圖3

七、為什麼說B+比B樹更適合實際應用中操作系統的文件索引和數據庫索引?

1、B+的磁盤讀寫代價更低。

B+的內部結點並沒有指向關鍵字具體信息的指針,因此其內部結點相對B樹更小。

如果把所有同一內部結點的關鍵字存放在同一盤塊中,那麼盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。

2、B+-tree的查詢效率更加穩定。

由於非終結點並不是最終指向文件內容的結點,而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同,導致每一個數據的查詢效率相當。

八、MySQL聯合索引

1、聯合索引是兩個或更多個列上的索引。

對於聯合索引:Mysql從左到右的使用索引中的字段,一個查詢可以只使用索引中的一部份,但只能是最左側部分。

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3種組合進行查找,但不支持 b,c進行查找 .當最左側字段是常量引用時,索引就十分有效。

2、利用索引中的附加列,您可以縮小搜索的範圍,但使用一個具有兩列的索引不同於使用兩個單獨的索引。

複合索引的結構與電話簿類似,人名由姓和名構成,電話簿首先按姓氏對進行排序,然後按名字對有相同姓氏的人進行排序。

如果您知道姓,電話簿將非常有用;如果您知道姓和名,電話簿則更為有用,但如果您只知道名不知道姓,電話簿將沒有用處。

九、什麼情况下應不建或少建索引

1、錶記錄太少

2、經常插入、删除、修改的錶

3、數據重複且分布平均的錶字段,假如一個錶有10萬行記錄,有一個字段A只有T和F兩種值,且每個值的分布概率大約為50%,那麼對這種錶A字段建索引一般不會提高數據庫的查詢速度。

4、經常和主字段一塊查詢但主字段索引值比較多的錶字段

十、什麼是錶分區?

錶分區,是指根據一定規則,將數據庫中的一張錶分解成多個更小的,容易管理的部分。從邏輯上看,只有一張錶,但是底層卻是由多個物理分區組成。

十一、錶分區與分錶的區別

分錶:指的是通過一定規則,將一張錶分解成多張不同的錶。比如將用戶訂單記錄根據時間成多個錶。

分錶與分區的區別在於:分區從邏輯上來講只有一張錶,而分錶則是將一張錶分解成多張錶。

十二、錶分區有什麼好處?

1、存儲更多數據。分區錶的數據可以分布在不同的物理設備上,從而高效地利用多個硬件設備。和單個磁盤或者文件系統相比,可以存儲更多數據

2、優化查詢。在where語句中包含分區條件時,可以只掃描一個或多個分區錶來提高查詢效率;涉及sum和count語句時,也可以在多個分區上並行處理,最後匯總結果。

3、分區錶更容易維護。例如:想批量删除大量數據可以清除整個分區。

4、避免某些特殊的瓶頸,例如InnoDB的單個索引的互斥訪問,ext3問價你系統的inode鎖競爭等。

十三、分區錶的限制因素

  1. 一個錶最多只能有1024個分區

  2. MySQL5.1中,分區錶達式必須是整數,或者返回整數的錶達式。在MySQL5.5中提供了非整數錶達式分區的支持。

  3. 如果分區字段中有主鍵或者唯一索引的列,那麼多有主鍵列和唯一索引列都必須包含進來。即:分區字段要麼不包含主鍵或者索引列,要麼包含全部主鍵和索引列。

  4. 分區錶中無法使用外鍵約束

  5. MySQL的分區適用於一個錶的所有數據和索引,不能只對錶數據分區而不對索引分區,也不能只對索引分區而不對錶分區,也不能只對錶的一部分數據分區。

十四、如何判斷當前MySQL是否支持分區?

命令:show variables like '%partition%' 運行結果:

 

have_partintioning 的值為YES,錶示支持分區。

十五、MySQL支持的分區類型有哪些?

  1. RANGE分區: 這種模式允許將數據劃分不同範圍。例如可以將一個錶通過年份劃分成若幹個分區

  2. LIST分區: 這種模式允許系統通過預定義的列錶的值來對數據進行分割。按照List中的值分區,與RANGE的區別是,range分區的區間範圍值是連續的。

  3. HASH分區 :這中模式允許通過對錶的一個或多個列的Hash Key進行計算,最後通過這個Hash碼不同數值對應的數據區域進行分區。例如可以建立一個對錶主鍵進行分區的錶。

  4. KEY分區 :上面Hash模式的一種延伸,這裏的Hash Key是MySQL系統產生的。

十六、四種隔離級別

  1. Serializable (串行化):可避免髒讀、不可重複讀、幻讀的發生。

  2. Repeatable read (可重複讀):可避免髒讀、不可重複讀的發生。

  3. Read committed (讀已提交):可避免髒讀的發生。

  4. Read uncommitted (讀未提交):最低級別,任何情况都無法保證。

十七、關於MVVC

MySQL InnoDB存儲引擎,實現的是基於多版本的並發控制協議——MVCC (Multi-Version Concurrency Control) 

:與MVCC相對的,是基於鎖的並發控制,Lock-Based Concurrency Control

MVCC最大的好處:讀不加鎖,讀寫不沖突。在讀多寫少的OLTP應用中,讀寫不沖突是非常重要的,極大的增加了系統的並發性能,現階段幾乎所有的RDBMS,都支持了MVCC。

  1. LBCC:Lock-Based Concurrency Control,基於鎖的並發控制

  2. MVCC:Multi-Version Concurrency Control

    基於多版本的並發控制協議。純粹基於鎖的並發機制並發量低,MVCC是在基於鎖的並發控制上的改進,主要是在讀操作上提高了並發量。

十八、在MVCC並發控制中,讀操作可以分成兩類:

  1. 快照讀 (snapshot read):讀取的是記錄的可見版本 (有可能是曆史版本),不用加鎖(共享讀鎖s鎖也不加,所以不會阻塞其他事務的寫)

  2. 當前讀 (current read):讀取的是記錄的最新版本,並且,當前讀返回的記錄,都會加上鎖,保證其他事務不會再並發修改這條記錄

十九、行級鎖定的優點:

1、當在許多線程中訪問不同的行時只存在少量鎖定沖突。

2、回滾時只有少量的更改

3、可以長時間鎖定單一的行。

二十、行級鎖定的缺點:

  1. 比頁級或錶級鎖定占用更多的內存。

  2. 當在錶的大部分中使用時,比頁級或錶級鎖定速度慢,因為你必須獲取更多的鎖。

  3. 如果你在大部分數據上經常進行GROUP BY操作或者必須經常掃描整個錶,比其它鎖定明顯慢很多。

  4. 用高級別鎖定,通過支持不同的類型鎖定,你也可以很容易地調節應用程序,因為其鎖成本小於行級鎖定。

二十一、MySQL優化

  1. 開啟查詢緩存,優化查詢

  2. explain你的select查詢,這可以幫你分析你的查詢語句或是錶結構的性能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的,你的數據錶是如何被搜索和排序的

  3. 當只要一行數據時使用limit 1,MySQL數據庫引擎會在找到一條數據後停止搜索,而不是繼續往後查少下一條符合記錄的數據

  4. 為搜索字段建索引

  5. 使用 ENUM 而不是 VARCHAR。如果你有一個字段,比如“性別”,“國家”,“民族”,“狀態”或“部門”,你知道這些字段的取值是有限而且固定的,那麼,你應該使用 ENUM 而不是VARCHAR

  6. Prepared StatementsPrepared Statements很像存儲過程,是一種運行在後臺的SQL語句集合,我們可以從使用 prepared statements 獲得很多好處,無論是性能問題還是安全問題。

    Prepared Statements 可以檢查一些你綁定好的變量,這樣可以保護你的程序不會受到“SQL注入式”攻擊

  7. 垂直分錶

  8. 選擇正確的存儲引擎

二十二、key和index的區別

  1. key 是數據庫的物理結構,它包含兩層意義和作用,一是約束(偏重於約束和規範數據庫的結構完整性),二是索引(輔助查詢用的)。包括primary key, unique key, foreign key 等

  2. index是數據庫的物理結構,它只是輔助查詢的,它創建時會在另外的錶空間(mysql中的innodb錶空間)以一個類似目錄的結構存儲。索引要分類的話,分為前綴索引、全文本索引等;

二十三、Mysql 中 MyISAM 和 InnoDB 的區別有哪些?

區別:

  1. InnoDB支持事務,MyISAM不支持

    對於InnoDB每一條SQL語言都默認封裝成事務,自動提交,這樣會影響速度,所以最好把多條SQL語言放在begin和commit之間,組成一個事務;

  2. InnoDB支持外鍵,而MyISAM不支持。對一個包含外鍵的InnoDB錶轉為MYISAM會失敗;

  3. InnoDB是聚集索引,數據文件是和索引綁在一起的,必須要有主鍵,通過主鍵索引效率很高。

    但是輔助索引需要兩次查詢,先查詢到主鍵,然後再通過主鍵查詢到數據。因此主鍵不應該過大,因為主鍵太大,其他索引也都會很大。

    而MyISAM是非聚集索引,數據文件是分離的,索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。

  4. InnoDB不保存錶的具體行數,執行select count(*) from table時需要全錶掃描。而MyISAM用一個變量保存了整個錶的行數,執行上述語句時只需要讀出該變量即可,速度很快;

  5. Innodb不支持全文索引,而MyISAM支持全文索引,查詢效率上MyISAM要高;

如何選擇:

  1. 是否要支持事務,如果要請選擇innodb,如果不需要可以考慮MyISAM;

  2. 如果錶中絕大多數都只是讀查詢,可以考慮MyISAM,如果既有讀寫也挺頻繁,請使用InnoDB

  3. 系統奔潰後,MyISAM恢複起來更困難,能否接受;

  4. MySQL5.5版本開始Innodb已經成為Mysql的默認引擎(之前是MyISAM),說明其優勢是有目共睹的,如果你不知道用什麼,那就用InnoDB,至少不會差。

二十四、數據庫錶創建注意事項

1、字段名及字段配制合理性

  • 剔除關系不密切的字段;

  • 字段命名要有規則及相對應的含義(不要一部分英文,一部分拼音,還有類似a.b.c這樣不明含義的字段);

  • 字段命名盡量不要使用縮寫(大多數縮寫都不能明確字段含義);

  • 字段不要大小寫混用(想要具有可讀性,多個英文單詞可使用下劃線形式連接);

  • 字段名不要使用保留字或者關鍵字;

  • 保持字段名和類型的一致性;

  • 慎重選擇數字類型;

  • 給文本字段留足餘量;

2、系統特殊字段處理及建成後建議

  • 添加删除標記(例如操作人、删除時間);

  • 建立版本機制;

3、錶結構合理性配置

  • 多型字段的處理,就是錶中是否存在字段能够分解成更小獨立的幾部分(例如:人可以分為男人和女人);

  • 多值字段的處理,可以將錶分為三張錶,這樣使得檢索和排序更加有調理,且保證數據的完整性!

4、其它建議

  • 對於大數據字段,獨立錶進行存儲,以便影響性能(例如:簡介字段);

  • 使用varchar類型代替char,因為varchar會動態分配長度,char指定長度是固定的;

  • 給錶創建主鍵,對於沒有主鍵的錶,在查詢和索引定義上有一定的影響;

  • 避免錶字段運行為null,建議設置默認值(例如:int類型設置默認值為0)在索引查詢上,效率立顯;

  • 建立索引,最好建立在唯一和非空的字段上,建立太多的索引對後期插入、更新都存在一定的影響(考慮實際情况來創建);

 

版權聲明
本文為[程序員社區]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/01/202201280433351317.html

隨機推薦