Java面試題沖刺第四天–數據庫

Posted on 2021-07-13 by WalkonNet

面試題1：你對數據庫優化有哪些瞭解呀？

正經回答：

在高並發環境下，數據庫是最敏感的地方，nginx負載均衡、Server集群、MQ消息隊列、Redis緩存集群、數據庫主從集群所作的一切都是為瞭減輕數據庫訪問壓力。但是！前提是要有健壯的數據庫和底層代碼，這樣才能使前期準備不再是花架子。

在這裡插入圖片描述

性價比如上圖，我們針對數據庫的優化優先級大致如下：

高：從SQL優化、索引優化入手，優化慢SQL、利用好索引，是重中之重；
中：SQL優化之後，是對數據表結構設計、橫縱分表分庫，對數據量級的處理；
低：通過修改數據庫系統配置，最大化裡用服務器內存等資源；
低：通過以上方式還不行，那就是服務器資源瓶頸瞭，加機器。

優化成本：硬件 > 系統配置 > 數據庫表結構 > SQL及索引。優化效果：硬件 < 系統配置 < 數據庫表結構 < SQL及索引。

深入追問：

追問1：那你對SQL優化方面有哪些技巧呢？

簡單說對於SQL優化，就三點:

最大化利用索引；
盡可能避免全表掃描；
減少無效數據的查詢；

首先要清楚SELECT語句 – 執行順序：

FROM <表名> # 選取表，將多個表數據通過笛卡爾積變成一個表。 ON <篩選條件> # 對笛卡爾積的虛表進行篩選 JOIN <join, left join, right join…> <join表> # 指定join，用於添加數據到on之後的虛表中，例如left join會將左表的剩餘數據添加到虛表中 WHERE <where條件> # 對上述虛表進行篩選 GROUP BY <分組條件> # 分組 <SUM()等聚合函數> # 用於having子句進行判斷，在書寫上這類聚合函數是寫在having判斷裡面的 HAVING <分組篩選> # 對分組後的結果進行聚合篩選 SELECT <返回數據列表> # 返回的單列必須在group by子句中，聚合函數除外 DISTINCT #數據除重 ORDER BY <排序條件> # 排序 LIMIT <行數限制>

SQL優化策略:

聲明：以下SQL優化策略適用於數據量較大的場景下，如果數據量較小，沒必要以此為準，以免畫蛇添足。

一、避免不走索引的場景

1.盡量避免在字段開頭模糊查詢，會導致數據庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE username LIKE '%陳%'

優化方式：盡量在字段後面使用模糊查詢。如下：（原因涉及B+Tree索引最左前綴原則，可以參考《MySQL最左匹配原則，道兒上兄弟都得知道的原則》）

SELECT * FROM t WHERE username LIKE '陳%'

如果需求是要在前面使用模糊查詢，

使用MySQL內置函數INSTR(str,substr) 來匹配，作用類似於java中的indexOf()，查詢字符串出現的角標位.

使用FullText全文索引，用match against 檢索

數據量較大的情況，建議引用ElasticSearch、solr，億級數據量檢索速度秒級

當表數據量較少（幾千條兒那種），別整花裡胡哨的，直接用like ‘%xx%’。

2.盡量避免使用 or，會導致數據庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

優化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

盡量避免進行null值的判斷，會導致數據庫引擎放棄索引進行全表掃描。如下：

SELECT * FROM t WHERE score IS NULL

優化方式：可以給字段添加默認值0，對0值進行判斷。如下：

SELECT * FROM t WHERE score = 0

4.盡量避免在where條件中等號的左側進行表達式、函數操作，會導致數據庫引擎放棄索引進行全表掃描。

可以將表達式、函數操作移動到等號右側。如下：

-- 全表掃描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

當數據量大時，避免使用where 1=1的條件。通常為瞭方便拼裝查詢條件，我們會默認使用該條件，數據庫引擎會放棄索引進行全表掃描。如下：

SELECT username, age, sex FROM T WHERE 1=1

優化方式：用代碼拼裝sql時進行判斷，沒 where 條件就去掉 where，有where條件就加 and。

6.查詢條件不要用 <> 或者 !=

使用索引列作為條件進行查詢時，需要避免使用<>或者!=等判斷條件。如確實業務需要，使用到不等於符號，需要在重新評估索引建立，避免在此字段上建立索引，改由查詢條件中其他索引字段代替。

7.where條件僅包含復合索引非前置列

如下：復合（聯合）索引包含key_part1，key_part2，key_part3三列，但SQL語句沒有包含索引前置列”key_part1″，按照MySQL聯合索引的最左匹配原則，不會走聯合索引。。

select col1 from table where key_part2=1 and key_part3=2

8.隱式類型轉換造成不使用索引

如下SQL語句由於索引對列類型為varchar，但給定的值為數值，涉及隱式類型轉換，造成不能正確走索引。

select col1 from table where col_varchar=123;

9.order by 條件要與where中條件一致，否則order by不會利用索引進行排序

-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;
對於上面的語句，數據庫的處理順序是：

第一步：根據where條件和統計信息生成執行計劃，得到數據。
第二步：將得到的數據排序。當執行處理數據（order by）時，數據庫會先查看第一步的執行計劃，看order by 的字段是否在執行計劃中利用瞭索引。如果是，則可以利用索引順序而直接取得已經排好序的數據。如果不是，則重新進行排序操作。
第三步：返回排序後的數據。

當order by 中的字段出現在where條件中時，才會利用索引而不再二次排序，更準確的說，order by 中的字段在執行計劃中利用瞭索引時，不用排序操作。

這個結論不僅對order by有效，對其他需要排序的操作也有效。比如group by 、union 、distinct等。

在這裡插入圖片描述

二、SELECT語句的一些其他優化

1.避免出現select *

首先，select * 操作在任何類型數據庫中都不是一個好的SQL編寫習慣。

使用select * 取出全部列，會讓優化器無法完成索引覆蓋掃描這類優化，會影響優化器對執行計劃的選擇，也會增加網絡帶寬消耗，更會帶來額外的I/O,內存和CPU消耗。

建議提出業務實際需要的列數，將指定列名以取代select *。

2.避免出現不確定結果的函數

特定針對主從復制這類業務場景。由於原理上從庫復制的是主庫執行的語句，使用如now()、rand()、sysdate()、current_user()等不確定結果的函數很容易導致主庫與從庫相應的數據不一致。另外不確定值的函數,產生的SQL語句無法利用query cache。

3.多表關聯查詢時，小表在前，大表在後

在MySQL中，執行 from 後的表關聯查詢是從左往右執行的（Oracle相反），第一張表會涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描後面的大表，或許隻掃描大表的前100行就符合返回條件並return瞭。

例如：表1有50條數據，表2有30億條數據；如果全表掃描表2，你品，那就先去吃個飯再說吧是吧。

4.使用表的別名

當在SQL語句中連接多個表時，請使用表的別名並把別名前綴於每個列名上。這樣就可以減少解析的時間並減少哪些友列名歧義引起的語法錯誤。

5.用where字句替換HAVING字句

避免使用HAVING字句，因為HAVING隻會在檢索出所有記錄之後才對結果集進行過濾，而where則是在聚合前刷選記錄，如果能通過where字句限制記錄的數目，那就能減少這方面的開銷。HAVING中的條件一般用於聚合函數的過濾，除此之外，應該將條件寫在where字句中。

where和having的區別：where後面不能使用組函數

6.調整Where字句中的連接順序

MySQL采用從左往右，自上而下的順序解析where子句。根據這個原理，應將過濾數據多的條件往前放，最快速度縮小結果集。對瞭，聽說5.7版的語法解析器已經實現瞭where後條件的自動調節工作。查詢條件很多的場景，建議不要做這種嘗試。

追問2：嗯，那你說一下為什麼不建議用SELECT * 呢？

在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫出。

增加查詢分析器解析成本。

增減字段容易與 resultMap 配置不一致。

無用字段增加網絡消耗，尤其是 text 類型的字段。

1. 不需要的列會增加數據傳輸時間和網絡開銷

用“SELECT * ”數據庫需要解析更多的對象、字段、權限、屬性等相關內容，在 SQL 語句復雜，硬解析較多的情況下，會對數據庫造成沉重的負擔。

增大網絡開銷；* 有時會誤帶上如log、IconMD5之類的無用且大文本字段，數據傳輸size會幾何增漲。如果DB和應用程序不在同一臺機器，這種開銷非常明顯。

即使 mysql 服務器和客戶端是在同一臺機器上，使用的協議還是 tcp，通信也是需要額外的時間。

2. 對於無用的大字段，如 varchar、blob、text，會增加 io 操作

準確來說，長度超過 728 字節的時候，會先把超出的數據序列化到另外一個地方，因此讀取這條記錄會增加一次 io 操作。（MySQL InnoDB）

3. 失去MySQL優化器“覆蓋索引”策略優化的可能性

SELECT * 杜絕瞭覆蓋索引的可能性，而基於MySQL優化器的“覆蓋索引”策略又是速度極快，效率極高，業界極為推薦的查詢優化方式。

面試題2：你對分庫分表是怎麼看的呀？

正經回答：

分庫：由單個數據庫實例拆分成多個數據庫實例，將數據分佈到多個數據庫實例中。
分表：由單張表拆分成多張表，將數據劃分到多張表內。

要知道，對於大型互聯網項目，數據量級可能不是我們能想到的，每日新增數據量過千萬是常有的事兒，想靠單臺MySQL服務器是不現實的。你項羽在牛B，也頂不住四個隊友掛機啊！！項羽：？？？

隨著業務數據量和網站QPS日益增高，對數據庫壓力也越來越大，單機版數據庫很快會到達存儲和並發瓶頸，就需要做數據庫性能方面的優化，分庫分表采取的是分而治之的策略，分庫目的是減輕單臺MySQL實例存儲壓力及可擴展性，而分表是解決單張表數據過大以後查詢的瓶頸問題，坦白說，這些問題也是所有關系型數據庫的“硬傷”。

常用策略包括：垂直分表、水平分表、垂直分庫、水平分庫。

在這裡插入圖片描述

1、垂直分表

垂直分表，或者叫豎著切表，是不是感受到該策略是以字段為依據的！主要按照字段的活躍性、字段長度，將表中字段拆分到不同的表（主表和擴展表）中。

特點：

每個表的結構都不一樣；
每個表的數據也不一樣，
有一個關聯字段，一般是主鍵或外鍵，用於關聯兄弟表數據；
所有兄弟表的並集是該表的全量數據；

場景：

1.有幾個字段屬於熱點字段，更新頻率很高，要把這些字段單獨切到一張表裡，不然innodb行鎖很惡心的，鎖死你呀~~如用戶表裡的餘額字段？不，我的餘額就很穩定，一直是0。。

2.有大字段，如text，存儲壓力很大，畢竟innodb數據和索引是同一個文件；同時，我又喜歡用SELECT *，你懂得，這磁盤IO消耗的，跟玩兒似的，誰都扛不住的。

3.有明顯的業務區分，或表結構設計時字段冗餘；有些小夥伴看到第一點時，就發現陳哈哈是個菜雞，用戶表怎麼會有餘額字段？明顯有問題啊！趕緊先到評論區噴陳哈哈一波~~然後笑嘻嘻的發現原來是個小尾巴，真不要臉是吧。。是的，因此不同業務我們要把具體字段拆開，這樣才有利於業務後續擴展哦。

2、水平分表

水平分表，也叫“橫著切”。。以行數據為依據進行切分，一般按照某列的自容進行切分。

如手機號表，我們可以通過前兩位或前三位進行切分，如131、132、133 → phone_131、phone_132、phone_133，手機號有11位（100億），量大是很正常的事兒，這年頭誰傢老頭老太太每個手機呢是吧。這樣切就把一張大表切成瞭好幾十張小表，數據量不就下來瞭。有同學就問瞭那我怎麼知道我這手機號查哪個表呢？一看你就沒認真看前兩行標紅的點，為啥標紅嘞？比如我查13100001111，那我截取前三位，動態拼接到查詢的表名上，就行瞭。

特點：

每個表的結構都一樣；
每個表的數據都不一樣，沒有交集；
所有表的並集是該表的全量數據；

場景：單表的數據量過大或增長速度很快，已經影響或即將會影響SQL查詢效率，加重瞭CPU負擔，提前到達瓶頸。記得水平分表越早越好，別問我為什麼。。

分庫

需要你註意的是，傳統的分庫和我們熟悉的集群、主從復制可不是一個事兒；多節點集群是將一個庫復制成N個庫，從而通過讀寫分離實現多個MySQL服務的負載均衡，實際是圍繞一個庫來搞的，這個庫稱為Master主庫。而分庫就不同瞭，分庫是將這個主庫一分為N，比如一分為二，然後針對這兩個主庫，再配置2N個從庫節點。

3、垂直分庫

縱向切庫，太經典的切分方式，基於表進行切分，通常是把新的業務模塊或集成公共模塊拆分出去，比如我們最熟悉的單點登錄、鑒權模塊。熟悉的味道，記得有一次我把一些沒用的表切到一個性能很好的服務器中，這服務器我專門用來學習，後來也不知被哪個狗腿子告密瞭~ 我**你個**，有種站出來，你個**東西

在這裡插入圖片描述

特點：

每個庫的表都不一樣；
表不一樣，數據就更不一樣瞭~ 沒有任何交集；
每個庫相對獨立，模塊化

場景：可以抽象出單獨的業務模塊時，可以抽象出公共區時（如字典、公共時間、公共配置等），或者想有一臺屬於自己的服務器時？

4、水平分庫

以行數據為依據，將一個庫中的數據拆分到多個庫中。大型分表體驗一下？坦白說這種策略並不實用，因為會對後臺開發很不友好，有很多坑，不建議采用，理解即可。

特點：

每個庫的結構都一樣；
每個庫的數據都不一樣，沒有交集；
所有庫的並集是全量數據；

場景：系統絕對並發量上來瞭，CPU內存壓力大。分表難以根本上解決量的問題，並且還沒有明顯的業務歸屬來垂直分庫，主庫磁盤接近飽和。

其實，在實際工作中，我們在選擇分庫分表策略前，想到的應該是從緩存、讀寫分離、SQL優化等方面，因為這些能夠更直接、代價更小的解決問題。要記住動表就是動根本，你永遠不知道這張表後面會連帶多少歷史遺留問題，如果是個很大型的項目，遇到些問題你就跟經理提議要分庫分表，小心被呼死~

深入追問：

追問1：毫無意義，我真的不想問他MySQL問題瞭

面試題3：MySQL刪除數據的方式都有哪些？

正經回答：

咱們常用的三種刪除方式：通過 delete、truncate、drop 關鍵字進行刪除；這三種都可以用來刪除數據，但用於的場景不同。

深入追問：

追問1：說一下 delete、truncate、drop的區別吧

一、從執行速度上來說

drop > truncate >> DELETE

二、從原理上講

DELETE

DELETE from TABLE_NAME where xxx

1.DELETE屬於數據庫DML操作語言，隻刪除數據不刪除表的結構，會走事務，執行時會觸發trigger；

2.在 InnoDB 中，DELETE其實並不會真的把數據刪除，mysql 實際上隻是給刪除的數據打瞭個標記為已刪除，因此 delete 刪除表中的數據時，表文件在磁盤上所占空間不會變小，存儲空間不會被釋放，隻是把刪除的數據行設置為不可見。雖然未釋放磁盤空間，但是下次插入數據的時候，仍然可以重用這部分空間（重用 → 覆蓋）。

3.DELETE執行時，會先將所刪除數據緩存到rollback segement中，事務commit之後生效;

4.delete from table_name刪除表的全部數據,對於MyISAM 會立刻釋放磁盤空間，InnoDB 不會釋放磁盤空間;

5.對於delete from table_name where xxx 帶條件的刪除, 不管是InnoDB還是MyISAM都不會釋放磁盤空間;

6.delete操作以後使用 optimize table table_name 會立刻釋放磁盤空間。不管是InnoDB還是MyISAM 。所以要想達到釋放磁盤空間的目的，delete以後執行optimize table 操作。

7.delete 操作是一行一行執行刪除的，並且同時將該行的的刪除操作日志記錄在redo和undo表空間中以便進行回滾（rollback）和重做操作，生成的大量日志也會占用磁盤空間。

truncate

Truncate table TABLE_NAME

1.truncate：屬於數據庫DDL定義語言，不走事務，原數據不放到 rollback segment 中，操作不觸發 trigger。

執行後立即生效，無法找回

2.truncate table table_name 立刻釋放磁盤空間，不管是 InnoDB和MyISAM 。truncate table其實有點類似於drop table 然後creat,隻不過這個create table 的過程做瞭優化，比如表結構文件之前已經有瞭等等。所以速度上應該是接近drop table的速度;

3.truncate能夠快速清空一個表。並且重置auto_increment的值。

但對於不同的類型存儲引擎需要註意的地方是： 對於MyISAM，truncate會重置auto_increment（自增序列）的值為1。而delete後表仍然保持auto_increment。

對於InnoDB，truncate會重置auto_increment的值為1。delete後表仍然保持auto_increment。但是在做delete整個表之後重啟MySQL的話，則重啟後的auto_increment會被置為1。

也就是說，InnoDB的表本身是無法持久保存auto_increment。delete表之後auto_increment仍然保存在內存，但是重啟後就丟失瞭，隻能從1開始。實質上重啟後的auto_increment會從 SELECT 1+MAX(ai_col) FROM t 開始。

4.小心使用 truncate，尤其沒有備份的時候，如果誤刪除線上的表，記得及時聯系中國民航，訂票電話：400-806-9553

drop

Drop table Tablename

1.drop：屬於數據庫DDL定義語言，同Truncate；

執行後立即生效，無法找回

2.drop table table_name 立刻釋放磁盤空間，不管是 InnoDB 和 MyISAM; drop 語句將刪除表的結構被依賴的約束(constrain)、觸發器(trigger)、索引(index); 依賴於該表的存儲過程/函數將保留,但是變為 invalid 狀態。

3.小心使用 drop ，要刪表跑路的兄弟，請在訂票成功後在執行操作！訂票電話：400-806-9553

可以這麼理解，一本書，delete是把目錄撕瞭，truncate是把書的內容撕下來燒瞭，drop是把書燒瞭

總結

本篇文章就到這裡瞭，希望能給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容！

Java面試題沖刺第四天–數據庫

目錄

面試題1：你對數據庫優化有哪些瞭解呀？

正經回答：

深入追問：

追問1：那你對SQL優化方面有哪些技巧呢？

二、SELECT語句的一些其他優化

追問2：嗯，那你說一下為什麼不建議用SELECT * 呢？

面試題2：你對分庫分表是怎麼看的呀？

正經回答：

1、垂直分表

2、水平分表

3、垂直分庫

4、水平分庫

深入追問：

追問1：毫無意義，我真的不想問他MySQL問題瞭

面試題3：MySQL刪除數據的方式都有哪些？

正經回答：

深入追問：

追問1：說一下 delete、truncate、drop的區別吧

總結

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

面試題1：你對數據庫優化有哪些瞭解呀？

正經回答：

深入追問：

追問1：那你對SQL優化方面有哪些技巧呢？

二、SELECT語句的一些其他優化

追問2：嗯，那你說一下為什麼不建議用SELECT * 呢？

面試題2：你對分庫分表是怎麼看的呀？

正經回答：

1、垂直分表

2、水平分表

3、垂直分庫

4、水平分庫

深入追問：

追問1：毫無意義，我真的不想問他MySQL問題瞭

面試題3：MySQL刪除數據的方式都有哪些？

正經回答：

深入追問：

追問1：說一下 delete、truncate、drop的區別吧

總結

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

發佈留言取消回覆