如何用mysql建立數據庫（MySQL數據庫基礎知識及優化）(2/2)
什么是臨時表，什么時候會使用到臨時表，什么時候刪除臨時表？ ＊
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表如下idusername0zhangsan1lisi2nullselect * 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，提升查詢性能及節約系統開銷
- 增加冗余，提高可用性
