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

當(dāng) cpu 飆升到 500%時(shí)，先用操作系統(tǒng)命令 top 命令觀察是不是 mysqld 占用導(dǎo)致的，如果不是，找出占用高的進(jìn)程，進(jìn)行相關(guān)處理。

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

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 其實(shí)是 Sleep 的，這種的其實(shí)是空閑狀態(tài)，沒(méi)有太多查看價(jià)值；我們要觀察的是有問(wèn)題的，所以可以進(jìn)行過(guò)濾：

-- 查詢非 Sleep 狀態(tài)的鏈接，按消耗時(shí)間倒序展示，自己加條件過(guò)濾
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)

這樣就過(guò)濾出來(lái)哪些是正在干活的，然后按照消耗時(shí)間倒敘展示，排在最前面的，極大可能就是有問(wèn)題的鏈接了，然后查看 info 一列，就能看到具體執(zhí)行的什么 SQL 語(yǔ)句了，針對(duì)分析。

一般來(lái)說(shuō)，要 kill 掉這些線程(同時(shí)觀察 cpu 使用率是否下降)，等進(jìn)行相應(yīng)的調(diào)整(比如說(shuō)加索引、改 sql、改內(nèi)存參數(shù))之后，再重新跑這些 SQL。

也有可能是每個(gè) sql 消耗資源并不多，但是突然之間，有大量的 session 連進(jìn)來(lái)導(dǎo)致 cpu 飆升，這種情況就需要跟應(yīng)用一起來(lái)分析為何連接數(shù)會(huì)激增，再做出相應(yīng)的調(diào)整，比如說(shuō)限制連接數(shù)等。

面試題2：什么是存儲(chǔ)過(guò)程？有哪些優(yōu)缺點(diǎn)

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

優(yōu)點(diǎn)

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

我們可以使用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)可被多條SQL的業(yè)務(wù)邏輯，存儲(chǔ)過(guò)程有助于減少在許多應(yīng)用程序中重復(fù)相同邏輯的工作。

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

數(shù)據(jù)庫(kù)管理員可以為僅訪問(wèn)特定存儲(chǔ)過(guò)程的應(yīng)用程序授予適當(dāng)?shù)奶貦?quán)，而無(wú)需在基礎(chǔ)表上授予任何特權(quán)。

較快的執(zhí)行速度

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

缺點(diǎn)

不可移植性

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

復(fù)雜存儲(chǔ)過(guò)程消耗資源多

如果存儲(chǔ)過(guò)程中邏輯比較復(fù)雜，包含多條SQL，則每個(gè)連接的內(nèi)存使用量可能將大大增加，執(zhí)行時(shí)間也會(huì)很長(zhǎng)，要有所準(zhǔn)備。

故障排除難

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

維護(hù)成本高

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

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

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

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

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

如果業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫(xiě)入重復(fù)的身份證號(hào)，那么這兩個(gè)選擇邏輯上都是正確的。如果從效率上講，主要關(guān)注點(diǎn)還是在SELECT和UPDATE操作上；

對(duì)于一條SELECT查詢來(lái)說(shuō)：

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

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

這兩者性能差距會(huì)有多少呢？微乎其微。對(duì)于普通索引，因?yàn)楸旧砭褪且詳?shù)據(jù)頁(yè)為單位讀進(jìn)內(nèi)存，數(shù)據(jù)頁(yè)大小默認(rèn)16KB（大概1000行），要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算。

對(duì)于一條UPDATE查詢來(lái)說(shuō)：

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

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

顯然，如果能夠?qū)⒏虏僮飨扔涗浽?change buffer，減少讀磁盤(pán)，語(yǔ)句的執(zhí)行速度會(huì)得到明顯的提升。而且，數(shù)據(jù)讀入內(nèi)存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內(nèi)存，提高內(nèi)存利用率。

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

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

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

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

第一種情況是，這個(gè)記錄要更新的目標(biāo)頁(yè)在內(nèi)存中。這時(shí)，InnoDB 的處理流程如下：

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

第二種情況是，這個(gè)記錄要更新的目標(biāo)頁(yè)不在內(nèi)存中。這時(shí)，InnoDB 的處理流程如下：

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

將數(shù)據(jù)從磁盤(pán)讀入內(nèi)存涉及隨機(jī) IO的訪問(wèn)，是數(shù)據(jù)庫(kù)里面成本最高的操作之一。change buffer 因?yàn)闇p少了隨機(jī)磁盤(pán)訪問(wèn)，所以對(duì)更新性能的提升是會(huì)很明顯的。

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

總結(jié)

本篇文章就到這里了，希望能夠給你帶來(lái)幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容！

最新評(píng)論