MySQL資料庫面試題七（2020最新版）

二十三：SQL的生命周期？

1.應用伺服器與資料庫伺服器建立一個連接配接

2.資料庫程序拿到請求sql

3.解析并生成執行計劃，執行

4.讀取資料到記憶體并進行邏輯處理

5.通過步驟一的連接配接，發送結果到用戶端

6.關掉連接配接，釋放資源

二十四：其他

1. 大表資料查詢，怎麼優化

   1）優化shema、sql語句+索引；

   2）第二加緩存，memcached, redis；

   3）主從複制，讀寫分離；

   4）垂直拆分，根據你子產品的耦合度，将一個大的系統分為多個小的系統，也就是分布式系統；

   5）水準切分，針對資料量大的表，這一步最麻煩，最能考驗技術水準，要選擇一個合理的sharding key, 為了有好的查詢效率，表結構也要改動，做一定的備援，應用也要改，sql中盡量帶sharding key，将資料定位到限定的表上去查，而不是掃描全部的表；

2. 超大分頁怎麼處理？

   超大的分頁一般從兩個方向上來解決.

   1）資料庫層面,這也是我們主要集中關注的(雖然收效沒那麼大),類似于select * from table where age > 20 limit 1000000,10這種查詢其實也是有可以優化的餘地的. 這條語句需要load1000000資料然後基本上全部丢棄,隻取10條當然比較慢. 當時我們可以修改為select * from table where id in (select id from table where age > 20 limit 1000000,10).這樣雖然也load了一百萬的資料,但是由于索引覆寫,要查詢的所有字段都在索引中,是以速度會很快. 同時如果ID連續的好,我們還可以

效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的資料.

2）從需求的角度減少這種請求…主要是不做類似的需求(直接跳轉到幾百萬頁之後的具體某一頁.隻允許逐頁檢視或者按照給定的路線走,這樣可預測,可緩存)以及防止ID洩漏且連續被人惡意攻擊.

解決超大分頁,其實主要是靠緩存,可預測性的提前查到内容,緩存至redis等k-V資料庫中,直接傳回即可.

在阿裡巴巴《Java開發手冊》中,對超大分頁的解決辦法是類似于上面提到的第一種.

【推薦】利用延遲關聯或者子查詢優化超多分頁場景。

說明：MySQL并不是跳過offset行，而是取offset+N行，然後傳回放棄前offset行，傳回N行，那當offset特别大的時候，效率就非常的低下，要麼控制傳回的總頁數，要麼對超過特定門檻值的頁數進行SQL改寫。

正例：先快速定位需要擷取的id段，然後再關聯：

SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id
           

1. mysql 分頁

   LIMIT 子句可以被用于強制 SELECT 語句傳回指定的記錄數。LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個傳回記錄行的偏移量，第二個參數指定傳回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1)

mysql> SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15
1
           

為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last.
1
           

如果隻給定一個參數，它表示傳回最大的記錄行數目：

mysql> SELECT * FROM table LIMIT 5; //檢索前 5 個記錄行
1
           

換句話說，LIMIT n 等價于 LIMIT 0,n。

1. 慢查詢日志

   用于記錄執行時間超過某個臨界值的SQL日志，用于快速定位慢查詢，為我們的優化做參考。

   開啟慢查詢日志

   配置項：slow_query_log

   可以使用show variables like ‘slov_query_log’檢視是否開啟，如果狀态值為OFF，可以使用set GLOBAL slow_query_log = on來開啟，它會在datadir下産生一個xxx-slow.log的檔案。

   設定臨界時間

   配置項：long_query_time

   檢視：show VARIABLES like ‘long_query_time’，機關秒

   設定：set long_query_time=0.5

   實操時應該從長時間設定到短的時間，即将最慢的SQL優化掉

   檢視日志，一旦SQL超過了我們設定的臨界時間就會被記錄到xxx-slow.log中

2. 關心過業務系統裡面的sql耗時嗎？統計過慢查詢嗎？對慢查詢都怎麼優化過？

   在業務系統中，除了使用主鍵進行的查詢，其他的我都會在測試庫上測試其耗時，慢查詢的統計主要由運維在做，會定期将業務中的慢查詢回報給我們。

   慢查詢的優化首先要搞明白慢的原因是什麼？ 是查詢條件沒有命中索引？是load了不需要的資料列？還是資料量太大？

   是以優化也是針對這三個方向來的，

   1）首先分析語句，看看是否load了額外的資料，可能是查詢了多餘的行并且抛棄掉了，可能是加載了許多結果中并不需要的列，對語句進行分析以及重寫。

   2）分析語句的執行計劃，然後獲得其使用索引的情況，之後修改語句或者修改索引，使得語句可以盡可能的命中索引。

   3）如果對語句的優化已經無法進行，可以考慮表中的資料量是否太大，如果是的話可以進行橫向或者縱向的分表。

3. 為什麼要盡量設定一個主鍵？

   主鍵是資料庫確定資料行在整張表唯一性的保障，即使業務上本張表沒有主鍵，也建議添加一個自增長的ID列作為主鍵。設定了主鍵之後，在後續的删改查的時候可能更加快速以及確定操作資料範圍安全。

4. 主鍵使用自增ID還是UUID？

   推薦使用自增ID，不要使用UUID。

   因為在InnoDB存儲引擎中，主鍵索引是作為聚簇索引存在的，也就是說，主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的資料(按照順序)，如果主鍵索引是自增ID，那麼隻需要不斷向後排列即可，如果是UUID，由于到來的ID與原來的大小不确定，會造成非常多的資料插入，資料移動，然後導緻産生很多的記憶體碎片，進而造成插入性能的下降。

   總之，在資料量大一些的情況下，用自增主鍵性能會好一些。

   關于主鍵是聚簇索引，如果沒有主鍵，InnoDB會選擇一個唯一鍵來作為聚簇索引，如果沒有唯一鍵，會生成一個隐式的主鍵。

5. 字段為什麼要求定義為not null？

   null值會占用更多的位元組，且會在程式中造成很多與預期不符的情況。

6. 如果要存儲使用者的密碼散列，應該使用什麼字段進行存儲？

   密碼散列，鹽，使用者身份證号等固定長度的字元串應該使用char而不是varchar來存儲，這樣可以節省空間且提高檢索效率。

7. 優化查詢過程中的資料通路

   1）通路資料太多導緻查詢性能下降

   2）确定應用程式是否在檢索大量超過需要的資料，可能是太多行或列

   3）确認MySQL伺服器是否在分析大量不必要的資料行

   4）避免犯如下SQL語句錯誤

   5）查詢不需要的資料。解決辦法：使用limit解決

   6）多表關聯傳回全部列。解決辦法：指定列名

   7）總是傳回全部列。解決辦法：避免使用SELECT *

   8）重複查詢相同的資料。解決辦法：可以緩存資料，下次直接讀取緩存

   9）是否在掃描額外的記錄。解決辦法：

   10）使用explain進行分析，如果發現查詢需要掃描大量的資料，但隻傳回少數的行，可以通過如下技巧去優化：

   11）使用索引覆寫掃描，把所有的列都放到索引中，這樣存儲引擎不需要回表擷取對應行就可以傳回結果。

   12）改變資料庫和表的結構，修改資料表範式

   13）重寫SQL語句，讓優化器可以以更優的方式執行查詢。

8. 優化長難的查詢語句

   1）一個複雜查詢還是多個簡單查詢

   2）MySQL内部每秒能掃描記憶體中上百萬行資料，相比之下，響應資料給用戶端就要慢得多

   3）使用盡可能小的查詢是好的，但是有時将一個大的查詢分解為多個小的查詢是很有必要的。

   4）切分查詢

   5）将一個大的查詢分為多個小的相同的查詢

   6）一次性删除1000萬的資料要比一次删除1萬，暫停一會的方案更加損耗伺服器開銷。

   7）分解關聯查詢，讓緩存的效率更高。

   8）執行單個查詢可以減少鎖的競争。

   9）在應用層做關聯更容易對資料庫進行拆分。

   10）查詢效率會有大幅提升。

   11）較少備援記錄的查詢。

9. 優化特定類型的查詢語句

   1）count()會忽略所有的列，直接統計所有列數，不要使用count(列名)

   2）MyISAM中，沒有任何where條件的count()非常快。

   3）當有where條件時，MyISAM的count統計不一定比其它引擎快。

   4）可以使用explain查詢近似值，用近似值替代count()*

   5）增加彙總表

   6）使用緩存

10. 優化關聯查詢

    1）确定ON或者USING子句中是否有索引。

    2）確定GROUP BY和ORDER BY隻有一個表中的列，這樣MySQL才有可能使用索引。

    優化子查詢

    3）用關聯查詢替代

    4）優化GROUP BY和DISTINCT

    5）這兩種查詢據可以使用索引來優化，是最有效的優化方法

    6）關聯查詢中，使用辨別列分組的效率更高

    7）如果不需要ORDER BY，進行GROUP BY時加ORDER BY NULL，MySQL不會再進行檔案排序。

    8）WITH ROLLUP超級聚合，可以挪到應用程式處理

    優化LIMIT分頁

    9）LIMIT偏移量大的時候，查詢效率較低

    10）可以記錄上次查詢的最大ID，下次查詢時直接根據該ID來查詢

11. 優化UNION查詢

    1）UNION ALL的效率高于UNION

12. 優化WHERE子句

    解題方法

    對于此類考題，先說明如何定位低效SQL語句，然後根據SQL語句可能低效的原因做排查，先從索引着手，如果索引沒有問題，考慮以上幾個方面，資料通路的問題，長難查詢句的問題還是一些特定類型優化的問題，逐一回答。

    SQL語句優化的一些方法？

    1）對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

    2）應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描，如：

– 可以在num上設定預設值0，確定表中num列沒有null值，然後這樣查詢：

select id from t where num=

3）應盡量避免在 where 子句中使用!=或<>操作符，否則引擎将放棄使用索引而進行全表掃描。

4）應盡量避免在 where 子句中使用or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描，如：

– 可以這樣查詢：

5)in 和 not in 也要慎用，否則會導緻全表掃描，如：

– 對于連續的數值，能用 between 就不要用 in 了：

6)下面的查詢也将導緻全表掃描：

select id from t where name like ‘%李%’

若要提高效率，可以考慮全文檢索。

7)如果在 where 子句中使用參數，也會導緻全表掃描。因為SQL隻有在運作時才會解析局部變量，但優化程式不能将通路計劃的選擇推遲到運作時；它必須在編譯時進行選擇。然 而，如果在編譯時建立通路計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句将進行全表掃描：

– 可以改為強制查詢使用索引：

8)應盡量避免在 where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

– 應改為:

9)應盡量避免在where子句中對字段進行函數操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)=’abc’
           

– name以abc開頭的id應改為:

select id from t where name like ‘abc%’
           

10)不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統将可能無法正确使用索引。

二十五：資料庫優化

1.為什麼要優化

1）系統的吞吐量瓶頸往往出現在資料庫的通路速度上

2）随着應用程式的運作，資料庫的中的資料會越來越多，處理時間會相應變慢

3）資料是存放在磁盤上的，讀寫速度無法和記憶體相比

優化原則：減少系統瓶頸，減少資源占用，增加系統的反應速度。

1. 資料庫結構優化

   一個好的資料庫設計方案對于資料庫的性能往往會起到事半功倍的效果。

   需要考慮資料備援、查詢和更新的速度、字段的資料類型是否合理等多方面的内容。

   将字段很多的表分解成多個表

   對于字段較多的表，如果有些字段的使用頻率很低，可以将這些字段分離出來形成新表。

   因為當一個表的資料量很大時，會由于使用頻率低的字段的存在而變慢。

增加中間表

對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。

通過建立中間表，将需要通過聯合查詢的資料插入到中間表中，然後将原來的聯合查詢改為對中間表的查詢。

增加備援字段

設計資料表時應盡量遵循範式理論的規約，盡可能的減少備援字段，讓資料庫設計看起來精緻、優雅。但是，合理的加入備援字段可以提高查詢速度。

表的規範化程度越高，表和表之間的關系越多，需要連接配接查詢的情況也就越多，性能也就越差。

注意：

備援字段的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導緻資料不一緻的問題。

1. MySQL資料庫cpu飙升到500%的話他怎麼處理？

   當 cpu 飙升到 500%時，先用作業系統指令 top 指令觀察是不是 mysqld 占用導緻的，如果不是，找出占用高的程序，并進行相關處理。

   如果是 mysqld 造成的， show processlist，看看裡面跑的 session 情況，是不是有消耗資源的 sql 在運作。找出消耗高的 sql，看看執行計劃是否準确， index 是否缺失，或者實在是資料量太大造成。

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

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

2. 大表怎麼優化？某個表有近千萬資料，CRUD比較慢，如何優化？分庫分表了是怎麼做的？分表分庫了有什麼問題？有用到中間件麼？他們的原理知道麼？

   當MySQL單表記錄數過大時，資料庫的CRUD性能會明顯下降，一些常見的優化措施如下：

   1）限定資料的範圍： 務必禁止不帶任何限制資料範圍條件的查詢語句。比如：我們當使用者在查詢訂單曆史的時候，我們可以控制在一個月的範圍内。；

   2）讀/寫分離： 經典的資料庫拆分方案，主庫負責寫，從庫負責讀；

   3）緩存： 使用MySQL的緩存，另外對重量級、更新少的資料可以考慮使用應用級别的緩存；

   還有就是通過分庫分表的方式進行優化，主要有垂直分表和水準分表

   （1）.垂直分區：

   根據資料庫裡面資料表的相關性進行拆分。 例如，使用者表中既有使用者的登入資訊又有使用者的基本資訊，可以将使用者表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。

   簡單來說垂直拆分是指資料表列的拆分，把一張列比較多的表拆分為多張表。 如下圖所示，這樣來說大家應該就更容易了解了。

   

   垂直拆分的優點： 可以使得行資料變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分區可以簡化表的結構，易于維護。

   垂直拆分的缺點： 主鍵會出現備援，需要管理備援列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區會讓事務變得更加複雜；

   垂直分表

   把主鍵和一些列放在一個表，然後把主鍵和另外的列放在另一個表中

   

适用場景 1、如果一個表中某些列常用，另外一些列不常用 2、可以使資料行變小，一個資料頁能存儲更多資料，查詢時減少I/O次數 缺點

有些分表的政策基于應用層的邏輯算法，一旦邏輯算法改變，整個分表邏輯都會改變，擴充性較差 對于應用層來說，邏輯算法增加開發成本

管理備援列，查詢所有資料需要join操作

（2）水準分區：

保持資料表結構不變，通過某種政策存儲資料分片。這樣每一片資料分散到不同的表或者庫中，達到了分布式的目的。 水準拆分可以支撐非常大的資料量。

水準拆分是指資料表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的資料拆成多張表來存放。舉個例子：我們可以将使用者資訊表拆分成多個使用者資訊表，這樣就可以避免單一表資料量過大對性能造成影響。

水品拆分可以支援非常大的資料量。需要注意的一點是:分表僅僅是解決了單一表資料過大的問題，但由于表的資料還是在同一台機器上，其實對于提升MySQL并發能力沒有什麼意義，是以 水準拆分最好分庫 。

水準拆分能夠 支援非常大的資料量存儲，應用端改造也少，但 分片事務難以解決 ，跨界點Join性能較差，邏輯複雜。

《Java工程師修煉之道》的作者推薦 盡量不要對資料進行分片，因為拆分會帶來邏輯、部署、運維的各種複雜度 ，一般的資料表在優化得當的情況下支撐千萬以下的資料量是沒有太大問題的。如果實在要分片，盡量選擇用戶端分片架構，這樣可以減少一次和中間件的網絡I/O。

水準分表：

表很大，分割後可以降低在查詢時需要讀的資料和索引的頁數，同時也降低了索引的層數，提高查詢次數

适用場景 1、表中的資料本身就有獨立性，例如表中分表記錄各個地區的資料或者不同時期的資料，特别是有些資料常用，有些不常用。

2、需要把資料存放在多個媒體上。 水準切分的缺點 1、給應用增加複雜度，通常查詢時需要多個表名，查詢所有資料都需UNION操作

2、在許多資料庫應用中，這種複雜度會超過它帶來的優點，查詢時會增加讀一個索引層的磁盤次數 下面補充一下資料庫分片的兩種常見方案：

用戶端代理： 分片邏輯在應用端，封裝在jar包中，通過修改或者封裝JDBC層來實作。 當當網的 Sharding-JDBC

、阿裡的TDDL是兩種比較常用的實作。 中間件代理： 在應用和資料中間加了一個代理層。分片邏輯統一維護在中間件服務中。 我們現在談的

Mycat 、360的Atlas、網易的DDB等等都是這種架構的實作。

1. 分庫分表後面臨的問題

   1）事務支援 分庫分表後，就成了分布式事務了。如果依賴資料庫本身的分布式事務管理功能去執行事務，将付出高昂的性能代價； 如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成程式設計方面的負擔。

   2）跨庫join

   隻要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實作。在第一次查詢的結果集中找出關聯資料的id,根據這些id發起第二次請求得到關聯資料。 分庫分表方案産品

   3）跨節點的count,order by,group by以及聚合函數問題 這些是一類問題，因為它們都需要基于全部資料集合進行計算。多數的代理都不會自動處理合并工作。解決方案：與解決跨節點join問題的類似，分别在各個節點上得到結果後在應用程式端進行合并。和join不同的是每個結點的查詢可以并行執行，是以很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程式記憶體的消耗是一個問題。

   4）資料遷移，容量規劃，擴容等問題 來自淘寶綜合業務平台團隊，它利用對2的倍數取餘具有向前相容的特性（如對4取餘得1的數對2取餘也是1）來配置設定資料，避免了行級别的資料遷移，但是依然需要進行表級别的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。

   5）ID問題

   6）一旦資料庫被切分到多個實體結點上，我們将不能再依賴資料庫自身的主鍵生成機制。一方面，某個分區資料庫自生成的ID無法保證在全局上是唯一的；另一方面，應用程式在插入資料之前需要先獲得ID,以便進行SQL路由. 一些常見的主鍵生成政策。

   UUID 使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進行查詢時都存在性能問題。 Twitter的分布式自增ID算法Snowflake 在分布式系統中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實作也還是很簡單的，除去配置資訊，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒内序列12位。

   7）跨分片的排序分頁

   般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，情況就會變得比較複雜了。為了最終結果的準确性，我們需要在不同的分片節點中将資料進行排序并傳回，并将不同分片傳回的結果集進行彙總和再次排序，最後再傳回給使用者。如下圖所示：