MySQL數據庫基礎知識及優化

紙鶴視界

右外連接：顯示左表中所有的數據及右表中符合條件的數據，右表中不符合條件的數據為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表

R表

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

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

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

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

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。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表如下

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線程：負責讀取中繼日志，解析出主服務器中已經執行的數據更改並在從服務器中重放

複制過程如下（圖片來源於網絡）：

Master在每個事務更新數據完成之前，將操作記錄寫入到binlog中。

Slave從庫連接Master主庫，並且Master有多少個Slave就會創建多少個binlog dump線程。當Master節點的binlog發生變化時，binlog dump會通知所有的Slave，並將相應的binlog發送給Slave。

I/O線程接收到binlog內容後，將其寫入到中繼日志（Relay log）中。

SQL線程讀取中繼日志，並在從服務器中重放。

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

主從複制的作用：

高可用和故障轉移

負載均衡

數據備份

升級測試

了解讀寫分離嗎？　＊＊＊

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

讀寫分離的優勢：

主服務器負責寫，從服務器負責讀，緩解了鎖的競爭

從服務器可以使用MyISAM，提升查詢性能及節約系統開銷

增加冗餘，提高可用性

文章轉載自：https://www.cnblogs.com/zydybaby/p/14310425.html