面試題1：你對數(shù)據(jù)庫優(yōu)化有哪些了解呀？

正經(jīng)回答：

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

性價比如上圖，我們針對數(shù)據(jù)庫的優(yōu)化優(yōu)先級大致如下：

• 高：從SQL優(yōu)化、索引優(yōu)化入手，優(yōu)化慢SQL、利用好索引，是重中之重；
• 中：SQL優(yōu)化之后，是對數(shù)據(jù)表結(jié)構(gòu)設(shè)計、橫縱分表分庫，對數(shù)據(jù)量級的處理；
• 低：通過修改數(shù)據(jù)庫系統(tǒng)配置，最大化里用服務(wù)器內(nèi)存等資源；
• 低：通過以上方式還不行，那就是服務(wù)器資源瓶頸了，加機器。

優(yōu)化成本：硬件 > 系統(tǒng)配置 > 數(shù)據(jù)庫表結(jié)構(gòu) > SQL及索引。優(yōu)化效果：硬件 < 系統(tǒng)配置 < 數(shù)據(jù)庫表結(jié)構(gòu) < SQL及索引。

深入追問：

追問1：那你對SQL優(yōu)化方面有哪些技巧呢？

簡單說對于SQL優(yōu)化，就三點:

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

首先要清楚SELECT語句 - 執(zhí)行順序：

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

SQL優(yōu)化策略:

聲明：以下SQL優(yōu)化策略適用于數(shù)據(jù)量較大的場景下，如果數(shù)據(jù)量較小，沒必要以此為準，以免畫蛇添足。

一、避免不走索引的場景

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

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

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

SELECT * FROM t WHERE username LIKE '陳%'

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

使用MySQL內(nèi)置函數(shù)INSTR(str,substr) 來匹配，作用類似于java中的indexOf()，查詢字符串出現(xiàn)的角標位.

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

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

當表數(shù)據(jù)量較少（幾千條兒那種），別整花里胡哨的，直接用like ‘%xx%'。

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

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

優(yōu)化方式：可以用union代替or。如下：

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

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

SELECT * FROM t WHERE score IS NULL

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

SELECT * FROM t WHERE score = 0

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

可以將表達式、函數(shù)操作移動到等號右側(cè)。如下：

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

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

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

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

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

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

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

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

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

8.隱式類型轉(zhuǎn)換造成不使用索引

如下SQL語句由于索引對列類型為varchar，但給定的值為數(shù)值，涉及隱式類型轉(zhuǎn)換，造成不能正確走索引。

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;
對于上面的語句，數(shù)據(jù)庫的處理順序是：

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

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

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

二、SELECT語句的一些其他優(yōu)化

1.避免出現(xiàn)select *

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

使用select * 取出全部列，會讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化，會影響優(yōu)化器對執(zhí)行計劃的選擇，也會增加網(wǎng)絡(luò)帶寬消耗，更會帶來額外的I/O,內(nèi)存和CPU消耗。

建議提出業(yè)務(wù)實際需要的列數(shù)，將指定列名以取代select *。

2.避免出現(xiàn)不確定結(jié)果的函數(shù)

特定針對主從復制這類業(yè)務(wù)場景。由于原理上從庫復制的是主庫執(zhí)行的語句，使用如now()、rand()、sysdate()、current_user()等不確定結(jié)果的函數(shù)很容易導致主庫與從庫相應(yīng)的數(shù)據(jù)不一致。另外不確定值的函數(shù),產(chǎn)生的SQL語句無法利用query cache。

3.多表關(guān)聯(lián)查詢時，小表在前，大表在后

在MySQL中，執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的（Oracle相反），第一張表會涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表，或許只掃描大表的前100行就符合返回條件并return了。

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

4.使用表的別名

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

5.用where字句替換HAVING字句

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

• where和having的區(qū)別：where后面不能使用組函數(shù)

6.調(diào)整Where字句中的連接順序

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

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

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

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

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

無用字段增加網(wǎng)絡(luò) 消耗，尤其是 text 類型的字段。

1. 不需要的列會增加數(shù)據(jù)傳輸時間和網(wǎng)絡(luò)開銷

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

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

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

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

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

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

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

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

正經(jīng)回答：

• 分庫：由單個數(shù)據(jù)庫實例拆分成多個數(shù)據(jù)庫實例，將數(shù)據(jù)分布到多個數(shù)據(jù)庫實例中。
• 分表：由單張表拆分成多張表，將數(shù)據(jù)劃分到多張表內(nèi)。

要知道，對于大型互聯(lián)網(wǎng)項目，數(shù)據(jù)量級可能不是我們能想到的，每日新增數(shù)據(jù)量過千萬是常有的事兒，想靠單臺MySQL服務(wù)器是不現(xiàn)實的。你項羽在牛B，也頂不住四個隊友掛機?。?！項羽：？？？

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

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

1、垂直分表

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

特點：

• 每個表的結(jié)構(gòu)都不一樣；
• 每個表的數(shù)據(jù)也不一樣，
• 有一個關(guān)聯(lián)字段，一般是主鍵或外鍵，用于關(guān)聯(lián)兄弟表數(shù)據(jù)；
• 所有兄弟表的并集是該表的全量數(shù)據(jù)；

場景：

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

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

3.有明顯的業(yè)務(wù)區(qū)分，或表結(jié)構(gòu)設(shè)計時字段冗余；有些小伙伴看到第一點時，就發(fā)現(xiàn)陳哈哈是個菜雞，用戶表怎么會有余額字段？明顯有問題??！趕緊先到評論區(qū)噴陳哈哈一波~~然后笑嘻嘻的發(fā)現(xiàn)原來是個小尾巴，真不要臉是吧。。是的，因此不同業(yè)務(wù)我們要把具體字段拆開，這樣才有利于業(yè)務(wù)后續(xù)擴展哦。

2、水平分表

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

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

特點：

• 每個表的結(jié)構(gòu)都一樣；
• 每個表的數(shù)據(jù)都不一樣，沒有交集；
• 所有表的并集是該表的全量數(shù)據(jù)；

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

分庫

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

3、垂直分庫

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

特點：

• 每個庫的表都不一樣；
• 表不一樣，數(shù)據(jù)就更不一樣了~ 沒有任何交集；
• 每個庫相對獨立，模塊化

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

4、水平分庫

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

特點：

• 每個庫的結(jié)構(gòu)都一樣；
• 每個庫的數(shù)據(jù)都不一樣，沒有交集；
• 所有庫的并集是全量數(shù)據(jù)；

場景：系統(tǒng)絕對并發(fā)量上來了，CPU內(nèi)存壓力大。分表難以根本上解決量的問題，并且還沒有明顯的業(yè)務(wù)歸屬來垂直分庫，主庫磁盤接近飽和。

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

深入追問：

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

面試題3：MySQL刪除數(shù)據(jù)的方式都有哪些？

正經(jīng)回答：

咱們常用的三種刪除方式：通過 delete、truncate、drop 關(guān)鍵字進行刪除；這三種都可以用來刪除數(shù)據(jù)，但用于的場景不同。

深入追問：

追問1：說一下 delete、truncate、drop的區(qū)別吧

一、從執(zhí)行速度上來說

drop > truncate >> DELETE

二、從原理上講

DELETE

DELETE from TABLE_NAME where xxx

1.DELETE屬于數(shù)據(jù)庫DML操作語言，只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)，會走事務(wù)，執(zhí)行時會觸發(fā)trigger；

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

3.DELETE執(zhí)行時，會先將所刪除數(shù)據(jù)緩存到rollback segement中，事務(wù)commit之后生效;

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

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

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

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

• truncate

Truncate table TABLE_NAME

1.truncate：屬于數(shù)據(jù)庫DDL定義語言，不走事務(wù)，原數(shù)據(jù)不放到 rollback segment 中，操作不觸發(fā) trigger。

執(zhí)行后立即生效，無法找回

執(zhí)行后立即生效，無法找回

執(zhí)行后立即生效，無法找回

2.truncate table table_name 立刻釋放磁盤空間 ，不管是 InnoDB和MyISAM 。truncate table其實有點類似于drop table 然后creat,只不過這個create table 的過程做了優(yōu)化，比如表結(jié)構(gòu)文件之前已經(jīng)有了等等。所以速度上應(yīng)該是接近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仍然保存在內(nèi)存，但是重啟后就丟失了，只能從1開始。實質(zhì)上重啟后的auto_increment會從 SELECT 1+MAX(ai_col) FROM t 開始。

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

• drop

Drop table Tablename

1.drop：屬于數(shù)據(jù)庫DDL定義語言，同Truncate；

執(zhí)行后立即生效，無法找回

執(zhí)行后立即生效，無法找回

執(zhí)行后立即生效，無法找回

2.drop table table_name 立刻釋放磁盤空間 ，不管是 InnoDB 和 MyISAM; drop 語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)、觸發(fā)器(trigger)、索引(index); 依賴于該表的存儲過程/函數(shù)將保留,但是變?yōu)?invalid 狀態(tài)。

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

可以這么理解，一本書，delete是把目錄撕了，truncate是把書的內(nèi)容撕下來燒了，drop是把書燒了

總結(jié)

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

最新評論