資料庫面試

1、 mysql分頁有什麼優化

第一種簡單粗暴，就是不允許檢視這麼靠後的資料，比如百度就是這樣的，最多翻到76頁就不讓你翻了，這種方式就是從業務上解決；

第二種方法，在查詢下一頁時把上一頁的行id作為參數傳遞給用戶端程式，然後sql就改成了

第三種：延遲關聯

2、悲觀鎖、樂觀鎖

悲觀鎖(Pessimistic Lock), 顧名思義，就是很悲觀，每次去拿資料的時候都認為别人會修改，是以每次在拿資料的時候都會上鎖，這樣别人想拿這個資料就會block直到它拿到鎖。傳統的關系型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。它指的是對資料被外界（包括本系統目前的其他事務，以及來自外部系統的事務處理）修改持保守态度，是以，在整個資料處理過程中，将資料處于鎖定狀态。悲觀鎖的實作，往往依靠資料庫提供的鎖機制（也隻有資料庫層提供的鎖機制才能真正保證資料通路的排他性，否則，即使在本系統中實作了加鎖機制，也無法保證外部系統不會修改資料）。

樂觀鎖(Optimistic Lock), 顧名思義，就是很樂觀，每次去拿資料的時候都認為别人不會修改，是以不會上鎖，但是在更新的時候會判斷一下在此期間别人有沒有去更新這個資料，可以使用版本号等機制。樂觀鎖适用于多讀的應用類型，這樣可以提高吞吐量，像資料庫如果提供類似于write_condition機制的其實都是提供的樂觀鎖。

兩種鎖各有優缺點，不可認為一種好于另一種，像樂觀鎖适用于寫比較少的情況下，即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。但如果經常産生沖突，上層應用會不斷的進行retry，這樣反倒是降低了性能，是以這種情況下用悲觀鎖就比較合适。

3、mysql 的表鎖、行鎖

1） 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。

2） 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。

3） 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

三種鎖各有各的特點，若僅從鎖的角度來說，表級鎖更适合于以查詢為主，隻有少量按索引條件更新資料的應用，如WEB應用；行級鎖更适合于有大量按索引條件并發更新少量不同資料，同時又有并發查詢的應用，如一些線上事務處理（OLTP）系統。

4、mysql 性能優化

1. 為查詢緩存優化你的查詢 2. EXPLAIN 你的 SELECT 查詢 3. 當隻要一行資料時使用 LIMIT 1 4. 為搜尋字段建索引 7. 避免 SELECT * 8. 永遠為每張表設定一個ID

2. 固定長度的表會更快

   5、mysql的索引分類：B+，hash；什麼情況用什麼索引

   B+樹是一個平衡的多叉樹，從根節點到每個葉子節點的高度內插補點不超過1，而且同層級的節點間有指針互相連結。在B+樹上的正常檢索，從根節點到葉子節點的搜尋效率基本相當，不會出現大幅波動，而且基于索引的順序掃描時，也可以利用雙向指針快速左右移動，效率非常高。是以，B+樹索引被廣泛應用于資料庫、檔案系統等場景。順便說一下，xfs檔案系統比ext3/ext4效率高很多的原因之一就是，它的檔案及目錄索引結構全部采用B+樹索引，而ext3/ext4的檔案目錄結構則采用Linked list, hashed B-tree、Extents/Bitmap等索引資料結構，是以在高I/O壓力下，其IOPS能力不如xfs。

   簡單地說，哈希索引就是采用一定的雜湊演算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，隻需一次雜湊演算法即可立刻定位到相應的位置，速度非常快。

   從上面的圖來看，B+樹索引和哈希索引的明顯差別是：

   如果是等值查詢，那麼哈希索引明顯有絕對優勢，因為隻需要經過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據連結清單往後掃描，直到找到相應的資料；

   從示意圖中也能看到，如果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；

   同理，哈希索引也沒辦法利用索引完成排序，以及like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；

   哈希索引也不支援多列聯合索引的最左比對規則；

   B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。

   6、事務的特性和隔離級别

   事務的四大特性：1. 原子性（全部成功，全部失敗） 2.一緻性（事務執行前後都必須處于一緻性狀态）3.隔離性（隔離性是當多個使用者并發通路資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所幹擾，多個并發事務之間要互相隔離。）4.持久性（持久性是指一個事務一旦被送出了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丢失送出事務的操作。）

3. 髒讀（髒讀是指在一個事務處理過程裡讀取了另一個未送出的事務中的資料。）
4. 不可重複讀

5. 虛讀（幻讀和不可重複讀都是讀取了另一條已經送出的事務（這點就髒讀不同），所不同的是不可重複讀查詢的都是同一個資料項，而幻讀針對的是一批資料整體（比如資料的個數））

   現在來看看MySQL資料庫為我們提供的四種隔離級别：

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

   ② Repeatable read (可重複讀)：可避免髒讀、不可重複讀的發生。

   ③ Read committed (讀已送出)：可避免髒讀的發生。

   ④ Read uncommitted (讀未送出)：最低級别，任何情況都無法保證。

7.資料庫索引有哪些類型？

普通索引、唯一索引、主鍵索引、組合索引；

普通索引：沒有任何限制；

唯一索引：索引列的值必須唯一，但允許有空值；

主鍵索引：特殊的唯一索引，不允許有空值；一個表隻能有一個主鍵；

組合索引：多個字段組合作為索引；

8.資料庫索引原理？

索引：資料庫索引，是資料庫管理系統中一個排序的資料結構，以協助快速查詢、更新資料庫表中資料。索引的實作通常使用B樹及其變種B+樹。

在資料之外，資料庫系統還維護着滿足特定查找算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實作進階查找算法。這種資料結構，就是索引。

9.B樹和B+樹原理？

為什麼使用B樹？

二叉查找樹結構由于樹的深度過大而造成磁盤I/O讀寫過于頻繁，進而導緻查詢效率低下。采用多叉樹結構減少樹的深度，進而達到有效避免磁盤過于頻繁的查找存取操作，進而有效提高查找效率。

動态查找樹主要有：二叉查找樹（Binary Search Tree），平衡二叉查找樹（Balanced Binary Search Tree），紅黑樹(Red-Black Tree )，B-tree/B±tree/ B*-tree (B~Tree)。前三者是典型的二叉查找樹結構，其查找的時間複雜度O(log2N)與樹的深度相關，那麼降低樹的深度自然會提高查找效率。

但是咱們有面對這樣一個實際問題：就是大規模資料存儲中，實作索引查詢這樣一個實際背景下，樹節點存儲的元素數量是有限的（如果元素數量非常多的話，查找就退化成節點内部的線性查找了），這樣導緻二叉查找樹結構由于樹的深度過大而造成磁盤I/O讀寫過于頻繁，進而導緻查詢效率低下（為什麼會出現這種情況，待會在外部存儲器-磁盤中有所解釋），那麼如何減少樹的深度（當然是不能減少查詢的資料量），一個基本的想法就是：采用多叉樹結構（由于樹節點元素數量是有限的，自然該節點的子樹數量也就是有限的）。

也就是說，因為磁盤的操作費時費資源，如果過于頻繁的多次查找勢必效率低下。那麼如何提高效率，即如何避免磁盤過于頻繁的多次查找呢？根據磁盤查找存取的次數往往由樹的高度所決定，是以，隻要我們通過某種較好的樹結構減少樹的結構盡量減少樹的高度，那麼是不是便能有效減少磁盤查找存取的次數呢？那這種有效的樹結構是一種怎樣的樹呢？

這樣我們就提出了一個新的查找樹結構——多路查找樹。根據平衡二叉樹的啟發，自然就想到平衡多路查找樹結構，也就是這篇文章所要闡述的第一個主題B~tree，即B樹結構(後面，我們将看到，B樹的各種操作能使B樹保持較低的高度，進而達到有效避免磁盤過于頻繁的查找存取操作，進而有效提高查找效率)。

10. 為什麼用資料庫連接配接池？

1）資源重用。連接配接池裡的連接配接可以重複使用，避免了頻繁建立、釋放連接配接引起的大量性能開銷。

2）更快的系統響應速度 。資料庫連接配接池在初始化過程中，往往已經建立了若幹資料庫連接配接置于池中備用。此時連接配接的初始化工作均已完成。對于業務請求處理而言，直接利用現有可用連接配接，避免了資料庫連接配接初始化和釋放過程的時間開銷，進而縮減了系統整體響應時間。

11. 資料庫查詢優化？

查詢優化？

（1.資料庫：索引、分區；2.I/O：緩沖區；3.SQL語句，條目）

1）資料庫方面：1.建立有效的索引；2.對資料庫分區（如按時間分區）；

2）I/O方面：增加緩沖區；

3）SQL語句方面：1.優化SQL語句，減少比較次數；如避免select * from而使用具體的字段清單代替，避免在where中使用!=或<>操作符，否則将放棄索引而進行全表掃描； 2.限制傳回條目limit；

12. 死鎖的避免？如何解決死鎖？

避免死鎖：資源有序配置設定（避免循環等待）、避免事務互動（減少持有資源的時間）、保持事務簡短并處于一個批進行中（減少持有資源時間）

解決死鎖？