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

當 cpu 飆升到 500%時，先用操作系統(tǒng)命令 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 的，這種的其實是空閑狀態(tài)，沒有太多查看價值；我們要觀察的是有問題的，所以可以進行過濾：

-- 查詢非 Sleep 狀態(tài)的鏈接，按消耗時間倒序展示，自己加條件過濾
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 一列，就能看到具體執(zhí)行的什么 SQL 語句了，針對分析。

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

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

面試題2：什么是存儲過程？有哪些優(yōu)缺點

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

優(yōu)點

在數(shù)據(jù)庫中集中業(yè)務邏輯

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

使數(shù)據(jù)庫更安全

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

較快的執(zhí)行速度

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

缺點

不可移植性

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

復雜存儲過程消耗資源多

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

故障排除難

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

維護成本高

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

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

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

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

答案參考林曉斌的MySQL實戰(zhàn)45講

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

對于一條SELECT查詢來說：

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

• 對于普通索引：查到第一條id=5后，然后繼續(xù)往后查找直到碰到第一個id!=5的記錄時，結束。
• 對于唯一索引：由于索引定義了唯一性，查找到第一個滿足條件的記錄后，直接結束。

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

對于一條UPDATE查詢來說：

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

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

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

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

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

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

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

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

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

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

• 對于唯一索引來說，需要將數(shù)據(jù)頁讀入內存，判斷到?jīng)]有沖突，插入這個值，語句執(zhí)行結束；
• 對于普通索引來說，則是將更新記錄在 change buffer，語句執(zhí)行就結束了。

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

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

總結

本篇文章就到這里了，希望能夠給你帶來幫助，也希望您能夠多多關注腳本之家的更多內容！

最新評論