Java面試題之數據庫的示例分析

這篇文章給大家分享的是有關Java面試題之數據庫的示例分析的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

面試題1：MySQL數據庫cpu飆升到500%的話你會怎么處理？

當 cpu 飆升到 500%時，先用操作系統命令 top 命令觀察是不是 mysqld 占用導致的，如果不是，找出占用高的進程，進行相關處理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情況，是不是有消耗資源的 sql 在運行。找出消耗高的 sql，看看是沒用上索引還是IO過大造成的。

mysql> show processlist;
+--------+-----------------+--------------------+---------+---------+------+-----------------------------+------------------+
| Id     | User            | Host               | db      | Command | Time | State                       | Info             |
+--------+-----------------+--------------------+---------+---------+------+-----------------------------+------------------+
|      1 | event_scheduler | localhost          | NULL    | Daemon  |  313 | Waiting for next activation | NULL             |
| 239896 | root            | 192.168.1.21:55050 | finance | Sleep   | 1160 |                             | NULL             |
| 239898 | root            | 192.168.1.21:58118 | NULL    | Sleep   |  397 |                             | NULL             |
| 239899 | root            | 192.168.1.21:58127 | csjdemo | Sleep   |  393 |                             | NULL             |
| 239901 | root            | 192.168.1.21:58135 | csjdemo | Sleep   |  387 |                             | NULL             |
| 239901 | root            | 192.168.1.21:58135 | csjdemo | Query   | 1044 |                             | select * from T like `name` like '%陳哈哈%'            |
| 239904 | root            | localhost          | NULL    | Query   |    0 | starting                    | show processlist |
+--------+-----------------+--------------------+---------+---------+------+-----------------------------+------------------+
6 rows in set (0.00 sec)

show full processlist 可以看到所有鏈接的情況，但是大多鏈接的 state 其實是 Sleep 的，這種的其實是空閑狀態，沒有太多查看價值；我們要觀察的是有問題的，所以可以進行過濾：

-- 查詢非 Sleep 狀態的鏈接，按消耗時間倒序展示，自己加條件過濾
select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc

mysql> select id, db, user, host, command, time, state, info from information_schema.processlist where command != 'Sleep' order by time desc \g;
+--------+------+-----------------+-----------+---------+------+-----------------------------+---------------------------------------------+
| id     | db   | user            | host      | command | time | state                       |                        info                 |
+--------+------+-----------------+-----------+---------+------+-----------------------------+---------------------------------------------+
|      1 | NULL | event_scheduler | localhost | Daemon  |  515 | Waiting for next activation | NULL                                        |
| 239904 | NULL | root            | localhost | Query   | 1044 | executing                   | select * from T like `name` like '%陳哈哈%' |
+--------+------+-----------------+-----------+---------+------+-----------------------------+---------------------------------------------+
2 rows in set (0.00 sec)

這樣就過濾出來哪些是正在干活的，然后按照消耗時間倒敘展示，排在最前面的，極大可能就是有問題的鏈接了，然后查看 info 一列，就能看到具體執行的什么 SQL 語句了，針對分析。

一般來說，要 kill 掉這些線程(同時觀察 cpu 使用率是否下降)，等進行相應的調整(比如說加索引、改 sql、改內存參數)之后，再重新跑這些 SQL。

也有可能是每個 sql 消耗資源并不多，但是突然之間，有大量的 session 連進來導致 cpu 飆升，這種情況就需要跟應用一起來分析為何連接數會激增，再做出相應的調整，比如說限制連接數等。

面試題2：什么是存儲過程？有哪些優缺點

存儲過程（Procedure）是一條或多條預編譯的SQL語句，一組為了完成特定功能的SQL 語句集，它存儲在數據庫中，一次編譯后永久有效，用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。

優點

在數據庫中集中業務邏輯

我們可以使用存儲過程來實現可被多條SQL的業務邏輯，存儲過程有助于減少在許多應用程序中重復相同邏輯的工作。

使數據庫更安全

數據庫管理員可以為僅訪問特定存儲過程的應用程序授予適當的特權，而無需在基礎表上授予任何特權。

較快的執行速度

如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那么存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優化器對其進行分析優化，并且給出最終被存儲在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優化，速度相對要慢一些。

缺點

不可移植性

每種數據庫的存儲過程不盡相同，如果MySQL使用大量的存儲過程，當你們想切換成Oracle時，就會發現是多么的不切實際。

復雜存儲過程消耗資源多

如果存儲過程中邏輯比較復雜，包含多條SQL，則每個連接的內存使用量可能將大大增加，執行時間也會很長，要有所準備。

故障排除難

調試存儲過程很困難。不幸的是，MySQL沒有像其他企業數據庫產品（如Oracle和SQL Server）那樣提供任何調試存儲過程的功能。存儲過程可能會封裝很多業務細節，可能會導致開發人員難以理解業務，試想一下一條前輩留下來的幾百行的存儲過程，老板突然讓你改實現邏輯，你懵逼不？

維護成本高

開發和維護存儲過程可能非專業人員搞不定，新手很容易留坑或者浪費很多時間。

普通業務邏輯盡量不要使用存儲過程，定時性的ETL任務或報表統計函數可以根據團隊資源情況采用存儲過程處理。存儲過程可以快速解決問題，但是移植性、維護性、擴展性不好，它有時會約束軟件的架構，約速程序員的思維，在你的系統沒有性能問題時不建議用存儲過程。如果你要完成的功能只是一次或有限次的工作，如數據訂正、數據遷移等等，存儲過程也可以拿上用場。

如果你的系統很小，并且有50%的開發人員熟練掌握PL/SQL，人員結構穩定，那存儲過程可以減少很多代碼量，并且性能不錯。當系統變復雜了，開發人員多了，存儲過程的弊端就會呈現，這時你需要痛下決心了。

 面試題3：比如有個用戶表，身份證號字段唯一，那么基于這個字段建索引的話，從效率上講，你會有哪些考慮呢？

答案參考林曉斌的MySQL實戰45講

如果業務代碼已經保證了不會寫入重復的身份證號，那么這兩個選擇邏輯上都是正確的。如果從效率上講，主要關注點還是在SELECT和UPDATE操作上；

對于一條SELECT查詢來說：

假設，執行查詢的語句是 select id from T where id=5。這個查詢語句在索引樹上查找的過程，先是通過 B+ 樹從樹根開始，按層搜索到葉子節點，然后取出該葉子節點所在的數據頁（先判斷changebuffer內存中是否有該頁，沒有就先從磁盤中讀到內存），最后通過二分法在數據頁中定位id=5的行數據。

• 對于普通索引：查到第一條id=5后，然后繼續往后查找直到碰到第一個id!=5的記錄時，結束。

• 對于唯一索引：由于索引定義了唯一性，查找到第一個滿足條件的記錄后，直接結束。

這兩者性能差距會有多少呢？微乎其微。對于普通索引，因為本身就是以數據頁為單位讀進內存，數據頁大小默認16KB（大概1000行），要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計算。

對于一條UPDATE查詢來說：

當需要更新一個數據頁時，如果數據頁在內存中就直接更新，而如果這個數據頁還沒有在內存中的話，在不影響數據一致性的前提下，InnoDB 會將這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤中讀入這個數據頁了。在下次查詢需要訪問這個數據頁的時候，將數據頁讀入內存，然后執行 change buffer 中與這個頁有關的操作。通過這種方式就能保證這個數據邏輯的正確性。需要說明的是，雖然名字叫作 change buffer，實際上它是可以持久化的數據。也就是說，change buffer 在內存中有拷貝，也會被寫入到磁盤上。

將 change buffer 中的操作應用到原數據頁，得到最新結果的過程稱為merge。除了（SELECT）訪問這個數據頁會觸發 merge 外，系統有后臺線程會定期 merge。在數據庫正常關閉（shutdown）的過程中，也會執行 merge 操作。

顯然，如果能夠將更新操作先記錄在 change buffer，減少讀磁盤，語句的執行速度會得到明顯的提升。而且，數據讀入內存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內存，提高內存利用率。

那么，什么條件下可以使用 change buffer 呢？對于唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性約束。比如，要插入 id=5 這條記錄，就要先判斷現在表中是否已經存在 id=5 的記錄，而這必須要將數據頁讀入內存才能判斷。如果都已經讀入到內存了，那直接更新內存會更快，就沒必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，實際上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的內存，因此不能無限增大。change buffer 的大小，可以通過參數 innodb_change_buffer_max_size 來動態設置。這個參數設置為 50 的時候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

那么如果要在這張表(id,name)中插入一個新記錄 (5,“陳哈哈”) ，InnoDB 的處理流程是怎樣的呢？

第一種情況是，這個記錄要更新的目標頁在內存中。這時，InnoDB 的處理流程如下：

• 對于唯一索引來說，找到 3 和 5 之間的位置，判斷到沒有沖突，插入這個值，語句執行結束；

• 對于普通索引來說，找到 3 和 5 之間的位置，插入這個值，語句執行結束。這樣看來，普通索引和唯一索引對更新語句性能影響的差別，只是一個判斷，只會耗費微小的 CPU 時間。

第二種情況是，這個記錄要更新的目標頁不在內存中。這時，InnoDB 的處理流程如下：

• 對于唯一索引來說，需要將數據頁讀入內存，判斷到沒有沖突，插入這個值，語句執行結束；

• 對于普通索引來說，則是將更新記錄在 change buffer，語句執行就結束了。

將數據從磁盤讀入內存涉及隨機 IO的訪問，是數據庫里面成本最高的操作之一。change buffer 因為減少了隨機磁盤訪問，所以對更新性能的提升是會很明顯的。

之前我就碰到過一件事兒，有個 DBA 的同學跟我反饋說，他負責的某個業務的庫內存命中率突然從 99% 降低到了 75%，整個系統處于阻塞狀態，更新語句全部堵住。而探究其原因后，我發現這個業務有大量插入數據的操作，而他在前一天把其中的某個普通索引改成了唯一索引。

感謝各位的閱讀！關于“Java面試題之數據庫的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！