MySQL資料庫基礎知識及優化

MySQL資料庫基礎知識及優化必會的知識點，你掌握了多少？

推薦閱讀：

• 這些必會的計算機網路知識點你都掌握了嗎

• 關於資料庫事務和鎖的必會知識點，你掌握了多少？

• 關於資料庫索引，必須掌握的知識點

目錄

• SQL語句基礎知識及優化
  • SQL語句主要分為哪幾類　＊
  • SQL約束有哪些？　＊＊
  • 什麼是子查詢？　＊＊
  • 瞭解MySQL的幾種連線查詢嗎？　＊＊＊
  • mysql中in和exists的區別？　＊＊
  • varchar和char的區別？　＊＊＊
  • MySQL中int(10)和char(10)和varchar(10)的區別？　＊＊＊
  • drop、delete和truncate的區別？　＊＊
  • UNION和UNION ALL的區別？　＊＊
  • 什麼是臨時表，什麼時候會使用到臨時表，什麼時候刪除臨時表？　＊
  • 大表資料查詢如何進行優化？　＊＊＊
  • 瞭解慢日誌查詢嗎？統計過慢查詢嗎？對慢查詢如何優化？　＊＊＊
  • 為什麼要設定主鍵？　＊＊
  • 主鍵一般用自增ID還是UUID？　＊＊
  • 欄位為什麼要設定成not null?　＊＊
  • 如何優化查詢過程中的資料訪問？　＊＊＊
  • 如何優化長難的查詢語句？　＊＊
  • 如何優化LIMIT分頁？　＊＊
  • 如何優化UNION查詢　＊＊
  • 如何優化WHERE子句　＊＊＊
  • SQL語句執行的很慢原因是什麼？　＊＊＊
  • SQL語句的執行順序?　＊
• 資料庫優化
  • 大表如何優化？　＊＊＊
  • 什麼是垂直分表、垂直分庫、水平分表、水平分庫？　＊＊＊
  • 分庫分表後，ID鍵如何處理？　＊＊＊
  • MySQL的複製原理及流程？如何實現主從複製？　＊＊＊
  • 瞭解讀寫分離嗎？　＊＊＊

SQL語句基礎知識及優化

SQL語句主要分為哪幾類　＊

• 資料據定義語言DDL（Data Definition Language）：主要有CREATE，DROP，ALTER等對邏輯結構有操作的，包括表結構、檢視和索引。
• 資料庫查詢語言DQL（Data Query Language）：主要以SELECT為主
• 資料操縱語言DML（Data Manipulation Language）：主要包括INSERT，UPDATE，DELETE
• 資料控制功能DCL（Data Control Language）：主要是許可權控制能操作，包括GRANT，REVOKE，COMMIT，ROLLBACK等。

SQL約束有哪些？　＊＊

• 主鍵約束：主鍵為在表中存在一列或者多列的組合，能唯一標識表中的每一行。一個表只有一個主鍵，並且主鍵約束的列不能為空。
• 外來鍵約束：外來鍵約束是指用於在兩個表之間建立關係，需要指定引用主表的哪一列。只有主表的主鍵可以被從表用作外來鍵，被約束的從表的列可以不是主鍵，所以建立外來鍵約束需要先定義主表的主鍵，然後定義從表的外來鍵。
• 唯一約束：確保表中的一列資料沒有相同的值，一個表可以定義多個唯一約束。
• 預設約束：在插入新資料時，如果該行沒有指定資料，系統將預設值賦給該行，如果沒有設定沒預設值，則為NULL。
• Check約束：Check會通過邏輯表示式來判斷資料的有效性，用來限制輸入一列或者多列的值的範圍。在列更新資料時，輸入的內容必須滿足Check約束的條件。

什麼是子查詢？　＊＊

子查詢：把一個查詢的結果在另一個查詢中使用

子查詢可以分為以下幾類：

• 標量子查詢：指子查詢返回的是一個值，可以使用 =,>,<,>=,<=,<>等操作符對子查詢標量結果進行比較，一般子查詢會放在比較式的右側。

  SELECT * FROM user WHERE age = (SELECT max(age) from user)  //查詢年紀最大的人
  

• 列子查詢：指子查詢的結果是n行一列，一般應用於對錶的某個欄位進行查詢返回。可以使用IN、ANY、SOME和ALL等操作符，不能直接使用

  SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
  

• 行子查詢：指子查詢返回的結果一行n列

  SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
  

• 表子查詢：指子查詢是n行n列的一個資料表

  SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在學生表中找到班級在1班的學生
  

瞭解MySQL的幾種連線查詢嗎？　＊＊＊

MySQl的連線查詢主要可以分為外連線，內連線，交叉連線

• 外連線

  外連線主要分為左外連線(LEFT JOIN)、右外連線(RIGHT JOIN)、全外連線。

  左外連線：顯示左表中所有的資料及右表中符合條件的資料，右表中不符合條件的資料為null。

右外連線：顯示左表中所有的資料及右表中符合條件的資料，右表中不符合條件的資料為null。

MySQL中不支援全外連線。

• 內連線：只顯示符合條件的資料

• 交叉連線：使用笛卡爾積的一種連線。

  笛卡爾積，百度百科的解釋：兩個集合X和Y的笛卡爾積表示為X × Y，第一個物件是X的成員而第二個物件是Y的所有可能有序對的其中一個成員 。例如：A={a,b}，B={0,1,2}，A × B = {(a,0)，(a,1)，(a,2)，(b,0)，(b,1)，(b,2)}

舉例如下：有兩張表分為L表和R表。

L表

A	B
a1	b1
a2	b2
a3	b3

R表

B	C
b1	c1
b2	c2
b4	c3

• 左外連線 ：select L.`*`,R.`*` from L left join R on L.b=R.b

  A	B	B	C
  a1	b1	b1	c1
  a2	b2	b2	c2
  a3	b3	null	null

• 右外連線：select L.`*`,R.`*` from L right join R on L.b=R.b

  B	C	A	B
  b1	c1	a1	b1
  b2	c2	a2	b2
  b4	c3	null	null

• 內連線：select L.`*`,R.`*` from L inner join R on L.b=R.b

  A	B	B	C
  a1	b1	b1	c1
  a2	b2	b2	c2

• 交叉連線：select L.`*`,R.`*` from L,R

  A	B	B	C
  a1	b1	b1	c1
  a1	b1	b2	c2
  a1	b1	b4	c3
  a2	b2	b1	c1
  a2	b2	b2	c2
  a2	b2	b4	c3
  a3	b3	b1	c1
  a3	b3	b2	c2
  a3	b3	b4	c3

mysql中in和exists的區別？　＊＊

in和exists一般用於子查詢。

• 使用exists時會先進行外表查詢，將查詢到的每行資料帶入到內表查詢中看是否滿足條件；使用in一般會先進行內表查詢獲取結果集，然後對外表查詢匹配結果集，返回資料。
• in在內表查詢或者外表查詢過程中都會用到索引。
• exists僅在內表查詢時會用到索引
• 一般來說，當子查詢的結果集比較大，外表較小使用exist效率更高；當子查詢尋得結果集較小，外表較大時，使用in效率更高。
• 對於not in和not exists，not exists效率比not in的效率高，與子查詢的結果集無關，因為not in對於內外表都進行了全表掃描，沒有使用到索引。not exists的子查詢中可以用到表上的索引。

varchar和char的區別？　＊＊＊

• varchar表示變長，char表示長度固定。當所插入的字元超過他們的長度時，在嚴格模式下，會拒絕插入並提示錯誤資訊，在一般模式下，會擷取後插入。如char(5)，無論插入的字元長度是多少，長度都是5，插入字元長度小於5，則用空格補充。對於varchar(5)，如果插入的字元長度小於5，則儲存的字元長度就是插入字元的長度，不會填充。
• 儲存容量不同，對於char來說，最多能存放的字元個數為255。對於varchar，最多能存放的字元個數是65532。
• 儲存速度不同，char長度固定，儲存速度會比varchar快一些，但在空間上會佔用額外的空間，屬於一種空間換時間的策略。而varchar空間利用率會高些，但儲存速度慢，屬於一種時間換空間的策略。

MySQL中int(10)和char(10)和varchar(10)的區別？　＊＊＊

int(10)中的10表示的是顯示資料的長度，而char(10)和varchar(10)表示的是儲存資料的大小。

drop、delete和truncate的區別？　＊＊

	drop	delete	truncate
速度	快	逐行刪除，慢	較快
型別	DDL	DML	DDL
回滾	不可回滾	可回滾	不可回滾
刪除內容	刪除整個表，資料行、索引都會被刪除	表結構還在，刪除表的一部分或全部資料	表結構還在，刪除表的全部資料

一般來講，刪除整個表，使用drop，刪除表的部分資料使用delete，保留表結構刪除表的全部資料使用truncate。

UNION和UNION ALL的區別？　＊＊

union和union all的作用都是將兩個結果集合併到一起。

• union會對結果去重並排序，union all直接直接返回合併後的結果，不去重也不進行排序。
• union all的效能比union效能好。

什麼是臨時表，什麼時候會使用到臨時表，什麼時候刪除臨時表？　＊

MySQL在執行SQL語句的時候會臨時建立一些儲存中間結果集的表，這種表被稱為臨時表，臨時表只對當前連線可見，在連線關閉後，臨時表會被刪除並釋放空間。

臨時表主要分為記憶體臨時表和磁碟臨時表兩種。記憶體臨時表使用的是MEMORY儲存引擎，磁碟臨時表使用的是MyISAM儲存引擎。

一般在以下幾種情況中會使用到臨時表：

• FROM中的子查詢
• DISTINCT查詢並加上ORDER BY
• ORDER BY和GROUP BY的子句不一樣時會產生臨時表
• 使用UNION查詢會產生臨時表

大表資料查詢如何進行優化？　＊＊＊

• 索引優化
• SQL語句優化
• 水平拆分
• 垂直拆分
• 建立中間表
• 使用快取技術
• 固定長度的表訪問起來更快
• 越小的列訪問越快

瞭解慢日誌查詢嗎？統計過慢查詢嗎？對慢查詢如何優化？　＊＊＊

慢查詢一般用於記錄執行時間超過某個臨界值的SQL語句的日誌。

相關引數：

• slow_query_log：是否開啟慢日誌查詢，1表示開啟，0表示關閉。
• slow_query_log_file：MySQL資料庫慢查詢日誌儲存路徑。
• long_query_time：慢查詢閾值，當SQL語句查詢時間大於閾值，會被記錄在日誌上。
• log_queries_not_using_indexes：未使用索引的查詢會被記錄到慢查詢日誌中。
• log_output：日誌儲存方式。“FILE”表示將日誌存入檔案。“TABLE”表示將日誌存入資料庫。

如何對慢查詢進行優化？

• 分析語句的執行計劃，檢視SQL語句的索引是否命中
• 優化資料庫的結構，將欄位很多的表分解成多個表，或者考慮建立中間表。
• 優化LIMIT分頁。

為什麼要設定主鍵？　＊＊

主鍵是唯一區分表中每一行的唯一標識，如果沒有主鍵，更新或者刪除表中特定的行會很困難，因為不能唯一準確地標識某一行。

主鍵一般用自增ID還是UUID？　＊＊

使用自增ID的好處：

• 欄位長度較uuid會小很多。
• 資料庫自動編號，按順序存放，利於檢索
• 無需擔心主鍵重複問題

使用自增ID的缺點：

• 因為是自增，在某些業務場景下，容易被其他人查到業務量。
• 發生資料遷移時，或者表合併時會非常麻煩
• 在高併發的場景下，競爭自增鎖會降低資料庫的吞吐能力

UUID：通用唯一標識碼，UUID是基於當前時間、計數器和硬體標識等資料計算生成的。

使用UUID的優點：

• 唯一標識，不會考慮重複問題，在資料拆分、合併時也能達到全域性的唯一性。
• 可以在應用層生成，提高資料庫的吞吐能力。
• 無需擔心業務量洩露的問題。

使用UUID的缺點：

• 因為UUID是隨機生成的，所以會發生隨機IO，影響插入速度，並且會造成硬碟的使用率較低。
• UUID佔用空間較大，建立的索引越多，造成的影響越大。
• UUID之間比較大小較自增ID慢不少，影響查詢速度。

最後說下結論，一般情況MySQL推薦使用自增ID。因為在MySQL的InnoDB儲存引擎中，主鍵索引是一種聚簇索引，主鍵索引的B+樹的葉子節點按照順序儲存了主鍵值及資料，如果主鍵索引是自增ID，只需要按順序往後排列即可，如果是UUID，ID是隨機生成的，在資料插入時會造成大量的資料移動，產生大量的記憶體碎片，造成插入效能的下降。

欄位為什麼要設定成not null?　＊＊

首先說一點，NULL和空值是不一樣的，空值是不佔用空間的，而NULL是佔用空間的，所以欄位設為NOT NULL後仍然可以插入空值。

欄位設定成not null主要有以下幾點原因：

• NULL值會影響一些函式的統計，如count，遇到NULL值，這條記錄不會統計在內。

• B樹不儲存NULL，所以索引用不到NULL，會造成第一點中說的統計不到的問題。

• NOT IN子查詢在有NULL值的情況下返回的結果都是空值。

  例如user表如下

  id	username
  0	zhangsan
  1	lisi
  2	null

  select * from `user` where username NOT IN (select username from `user` where id != 0)，這條查詢語句應該查到zhangsan這條資料，但是結果顯示為null。

• MySQL在進行比較的時候，NULL會參與欄位的比較，因為NULL是一種比較特殊的資料型別，資料庫在處理時需要進行特數處理，增加了資料庫處理記錄的複雜性。

如何優化查詢過程中的資料訪問？　＊＊＊

從減少資料訪問方面考慮：

• 正確使用索引，儘量做到索引覆蓋
• 優化SQL執行計劃

從返回更少的資料方面考慮：

• 資料分頁處理
• 只返回需要的欄位

從減少伺服器CPU開銷方面考慮：

• 合理使用排序
• 減少比較的操作
• 複雜運算在客戶端處理

從增加資源方面考慮：

• 客戶端多程式並行訪問
• 資料庫並行處理

如何優化長難的查詢語句？　＊＊

• 將一個大的查詢分解為多個小的查詢
• 分解關聯查詢，使快取的效率更高

如何優化LIMIT分頁？　＊＊

• 在LIMIT偏移量較大的時候，查詢效率會變低，可以記錄每次取出的最大ID，下次查詢時可以利用ID進行查詢

• 建立複合索引

如何優化UNION查詢　＊＊

如果不需要對結果集進行去重或者排序建議使用UNION ALL，會好一些。

如何優化WHERE子句　＊＊＊

• 不要在where子句中使用!=和<>進行不等於判斷，這樣會導致放棄索引進行全表掃描。
• 不要在where子句中使用null或空值判斷，儘量設定欄位為not null。
• 儘量使用union all代替or
• 在where和order by涉及的列建立索引
• 儘量減少使用in或者not in，會進行全表掃描
• 在where子句中使用引數會導致全表掃描
• 避免在where子句中對欄位及進行表示式或者函式操作會導致儲存引擎放棄索引進而全表掃描

SQL語句執行的很慢原因是什麼？　＊＊＊

• 如果SQL語句只是偶爾執行很慢，可能是執行的時候遇到了鎖，也可能是redo log日誌寫滿了，要將redo log中的資料同步到磁碟中去。
• 如果SQL語句一直都很慢，可能是欄位上沒有索引或者欄位有索引但是沒用上索引。

SQL語句的執行順序?　＊

SELECT DISTINCT 
	select_list 
FROM 
	left_table 
LEFT JOIN 
	right_table ON join_condition 
WHERE 
	where_condition 
GROUP BY 
	group_by_list 
HAVING 
	having_condition 
ORDER BY 
	order_by_condition

執行順序如下：

• FROM：對SQL語句執行查詢時，首先對關鍵字兩邊的表以笛卡爾積的形式執行連線，併產生一個虛表V1。虛表就是檢視，資料會來自多張表的執行結果。

• ON：對FROM連線的結果進行ON過濾,並建立虛表V2

• JOIN：將ON過濾後的左表新增進來，並建立新的虛擬表V3

• WHERE：對虛擬表V3進行WHERE篩選，建立虛擬表V4

• GROUP BY：對V4中的記錄進行分組操作，建立虛擬表V5

• HAVING：對V5進行過濾，建立虛擬表V6

• SELECT：將V6中的結果按照SELECT進行篩選，建立虛擬表V7

• DISTINCT：對V7表中的結果進行去重操作，建立虛擬表V8，如果使用了GROUP BY子句則無需使用DISTINCT，因為分組的時候是將列中唯一的值分成一組，並且每組只返回一行記錄，所以所有的記錄都h是不同的。

• ORDER BY：對V8表中的結果進行排序。

資料庫優化

大表如何優化？　＊＊＊

• 限定資料的範圍：避免不帶任何限制資料範圍條件的查詢語句。
• 讀寫分離：主庫負責寫，從庫負責讀。
• 垂直分表：將一個表按照欄位分成多個表，每個表儲存其中一部分欄位。
• 水平分表：在同一個資料庫內，把一個表的資料按照一定規則拆分到多個表中。
• 對單表進行優化：對錶中的欄位、索引、查詢SQL進行優化。
• 新增快取

什麼是垂直分表、垂直分庫、水平分表、水平分庫？　＊＊＊

垂直分表：將一個表按照欄位分成多個表，每個表儲存其中一部分欄位。一般會將常用的欄位放到一個表中，將不常用的欄位放到另一個表中。

垂直分表的優勢：

• 避免IO競爭減少鎖表的概率。因為大的欄位效率更低，第一資料量大，需要的讀取時間長。第二，大欄位佔用的空間更大，單頁記憶體儲的行數變少，會使得IO操作增多。

• 可以更好地提升熱門資料的查詢效率。

垂直分庫：按照業務對錶進行分類，部署到不同的資料庫上面，不同的資料庫可以放到不同的伺服器上面。

垂直分庫的優勢：

• 降低業務中的耦合，方便對不同的業務進行分級管理。
• 可以提升IO、資料庫連線數、解決單機硬體資源的瓶頸問題。

垂直拆分（分庫、分表）的缺點：

• 主鍵出現冗餘，需要管理冗餘列
• 事務的處理變得複雜
• 仍然存在單表資料量過大的問題

水平分表：在同一個資料庫內，把同一個表的資料按照一定規則拆分到多個表中。

水平分表的優勢：

• 解決了單表資料量過大的問題
• 避免IO競爭並減少鎖表的概率

水平分庫：把同一個表的資料按照一定規則拆分到不同的資料庫中，不同的資料庫可以放到不同的伺服器上。

水平分庫的優勢：

• 解決了單庫大資料量的瓶頸問題
• IO衝突減少，鎖的競爭減少，某個資料庫出現問題不影響其他資料庫（可用性），提高了系統的穩定性和可用性

水平拆分（分表、分庫）的缺點：

• 分片事務一致性難以解決
• 跨節點JOIN效能差，邏輯會變得複雜
• 資料擴充套件難度大，不易維護

在系統設計時應根據業務耦合來確定垂直分庫和垂直分表的方案，在資料訪問壓力不是特別大時應考慮快取、讀寫分離等方法，若資料量很大，或持續增長可考慮水平分庫分表，水平拆分所涉及的邏輯比較複雜，常見的方案有客戶端架構和惡代理架構。

分庫分表後，ID鍵如何處理？　＊＊＊

分庫分表後不能每個表的ID都是從1開始，所以需要一個全域性ID，設定全域性ID主要有以下幾種方法：

• UUID：優點：本地生成ID，不需要遠端呼叫；全域性唯一不重複。缺點：佔用空間大，不適合作為索引。

• 資料庫自增ID：在分庫分表表後使用資料庫自增ID，需要一個專門用於生成主鍵的庫，每次服務接收到請求，先向這個庫中插入一條沒有意義的資料，獲取一個資料庫自增的ID，利用這個ID去分庫分表中寫資料。優點：簡單易實現。缺點：在高併發下存在瓶頸。系統結構如下圖（圖片來源於網路）

• Redis生成ID：優點：不依賴資料庫，效能比較好。缺點：引入新的元件會使得系統複雜度增加

• Twitter的snowflake演算法：是一個64位的long型的ID，其中有1bit是不用的，41bit作為毫秒數，10bit作為工作機器ID，12bit作為序列號。

  1bit：第一個bit預設為0，因為二進位制中第一個bit為1的話為負數，但是ID不能為負數.

  41bit：表示的是時間戳，單位是毫秒。

  10bit：記錄工作機器ID，其中5個bit表示機房ID，5個bit表示機器ID。

  12bit：用來記錄同一毫秒內產生的不同ID。

• 美團的Leaf分散式ID生成系統，美團點評分散式ID生成系統

MySQL的複製原理及流程？如何實現主從複製？　＊＊＊

MySQL複製：為保證主伺服器和從伺服器的資料一致性，在向主伺服器插入資料後，從伺服器會自動將主伺服器中修改的資料同步過來。

主從複製的原理：

主從複製主要有三個執行緒：binlog執行緒，I/O執行緒，SQL執行緒。

• binlog執行緒：負責將主伺服器上的資料更改寫入到二進位制日誌（Binary log）中。
• I/O執行緒：負責從主伺服器上讀取二進位制日誌（Binary log），並寫入從伺服器的中繼日誌（Relay log）中。
• SQL執行緒：負責讀取中繼日誌，解析出主伺服器中已經執行的資料更改並在從伺服器中重放

複製過程如下（圖片來源於網路）：

1. Master在每個事務更新資料完成之前，將操作記錄寫入到binlog中。
2. Slave從庫連線Master主庫，並且Master有多少個Slave就會建立多少個binlog dump執行緒。當Master節點的binlog發生變化時，binlog dump會通知所有的Slave，並將相應的binlog傳送給Slave。
3. I/O執行緒接收到binlog內容後，將其寫入到中繼日誌（Relay log）中。
4. SQL執行緒讀取中繼日誌，並在從伺服器中重放。

這裡補充一個通俗易懂的圖。

主從複製的作用：

• 高可用和故障轉移
• 負載均衡
• 資料備份
• 升級測試

瞭解讀寫分離嗎？　＊＊＊

讀寫分離主要依賴於主從複製，主從複製為讀寫分離服務。

讀寫分離的優勢：

• 主伺服器負責寫，從伺服器負責讀，緩解了鎖的競爭
• 從伺服器可以使用MyISAM，提升查詢效能及節約系統開銷
• 增加冗餘，提高可用性