網易游戲面試題目整理及答案（3）

• 數據庫部分
• • MySQL 事務
  • MySQL鎖機制
  • MySQL調優
  • MySQL分區、分表、分庫
  • 主從復制
  • 其他問題

數據庫部分

MySQL 事務

事務的隔離級別有哪些？MySQL的默認隔離級別是什么？什么是幻讀，臟讀，不可重復讀呢？MySQL事務的四大特性以及實現原理。MVCC熟悉嗎，它的底層原理？
答：MySQL 事務主要用于處理操作量大，復雜度高的數據。事務是由一組SQL語句組成的邏輯處理單元，具有4個屬性，通常簡稱為事務的ACID屬性。

• A (Atomicity) 原子性：整個事務中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣
• C (Consistency) 一致性：在事務開始之前和事務結束以后，數據庫的完整性約束沒有被破壞
• I (Isolation)隔離性：一個事務的執行不能其它事務干擾。即一個事務內部的操作及使用的數據對其它并發事務是隔離的，并發執行的各個事務之間不能互相干擾
• D (Durability) 持久性：在事務完成以后，該事務所對數據庫所作的更改便持久的保存在數據庫之中，并不會被回滾

并發事務處理帶來的問題

• 更新丟失（Lost Update)：事務A和事務B選擇同一行，然后基于最初選定的值更新該行時，由于兩個事務都不知道彼此的存在，就會發生丟失更新問題
• 臟讀(Dirty Reads)：事務A讀取了事務B更新的數據，然后B回滾操作，那么A讀取到的數據是臟數據
• 不可重復讀（Non-Repeatable Reads)：事務 A 多次讀取同一數據，事務B在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A- 多次讀取同一數據時，結果不一致。
• 幻讀（Phantom Reads)：幻讀與不可重復讀類似。它發生在一個事務A讀取了幾行數據，接著另一個并發事務B插入了一些數據時。在隨后的查詢中，事務A就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。

幻讀和不可重復讀的區別：

• 不可重復讀的重點是修改：在同一事務中，同樣的條件，第一次讀的數據和第二次讀的數據不一樣。（因為中間有其他事務提交了修改）
• 幻讀的重點在于新增或者刪除：在同一事務中，同樣的條件,，第一次和第二次讀出來的記錄數不一樣。（因為中間有其他事務提交了插入/刪除）

并發事務處理帶來的問題的解決辦法：

• “更新丟失”通常是應該完全避免的。但防止更新丟失，并不能單靠數據庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
• “臟讀” 、 “不可重復讀”和“幻讀” ，其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決：
  ①一種是加鎖：在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
  ②另一種是數據多版本并發控制（MultiVersion Concurrency Control，簡稱 MVCC 或 MCC），也稱為多版本數據庫：不用加任何鎖， 通過一定機制生成一個數據請求時間點的一致性數據快照 （Snapshot)， 并用這個快照來提供一定級別 （語句級或事務級） 的一致性讀取。從用戶的角度來看，好象是數據庫可以提供同一數據的多個版本。

事務隔離級別
答：數據庫事務的隔離級別有4種，由低到高分別為

• READ-UNCOMMITTED(讀未提交)： 最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重復讀。
• READ-COMMITTED(讀已提交)： 允許讀取并發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生。
• REPEATABLE-READ(可重復讀)： 對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發生。
• SERIALIZABLE(可串行化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀。
  查看當前數據庫的事務隔離級別：

show variables like 'tx_isolation'

數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價就越大，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重復讀”和“幻讀”并不敏感，可能更關心數據并發訪問的能力。
Read uncommitted：讀未提交，就是一個事務可以讀取另一個未提交事務的數據。
Read committed：讀提交，顧名思義，就是一個事務要等另一個事務提交后才能讀取數據。解決臟讀問題。
Repeatable read：重復讀，就是在開始讀取數據（事務開啟）時，不再允許修改操作。MySQL的默認事務隔離級別。解決可能的不可重復讀問題。不可重復讀對應的是修改，即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作，而不是UPDATE操作。
Serializable 序列化：Serializable 是最高的事務隔離級別，在該級別下，事務串行化順序執行，可以避免臟讀、不可重復讀與幻讀。簡單來說，Serializable會在讀取的每一行數據上都加鎖，所以可能導致大量的超時和鎖爭用問題。這種事務隔離級別效率低下，比較耗數據庫性能，一般不使用。

需要說明的是，事務隔離級別和數據訪問的并發性是對立的，事務隔離級別越高并發性就越差。所以要根據具體的應用來確定合適的事務隔離級別，這個地方沒有萬能的原則。

MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀）。我們可以通過SELECT @@tx_isolation;命令來查看，MySQL 8.0 該命令改為SELECT @@transaction_isolation;
這里需要注意的是：與 SQL 標準不同的地方在于InnoDB 存儲引擎在 REPEATABLE-READ（可重讀）事務隔離級別下使用的是Next-Key Lock 算法，因此可以避免幻讀的產生，這與其他數據庫系統(如 SQL Server)是不同的。所以說InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀）已經可以完全保證事務的隔離性要求，即達到了 SQL標準的 SERIALIZABLE(可串行化)隔離級別，而且保留了比較好的并發性能。

因為隔離級別越低，事務請求的鎖越少，所以大部分數據庫系統的隔離級別都是READ-COMMITTED(讀已提交):，但是你要知道的是InnoDB 存儲引擎默認使用 REPEATABLE-READ（可重讀）并不會有任何性能損失。

MVCC 多版本并發控制
答：MySQL的大多數事務型存儲引擎實現都不是簡單的行級鎖。基于提升并發性考慮，一般都同時實現了多版本并發控制（MVCC），包括Oracle、PostgreSQL。只是實現機制各不相同。可以認為 **MVCC 是行級鎖的一個變種，但它在很多情況下避免了加鎖操作，因此開銷更低。**雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只是鎖定必要的行。**MVCC 的實現是通過保存數據在某個時間點的快照來實現的。**也就是說不管需要執行多長時間，每個事物看到的數據都是一致的。典型的MVCC實現方式，分為樂觀（optimistic）并發控制和悲觀（pressimistic）并發控制。下邊通過 InnoDB的簡化版行為來說明 MVCC 是如何工作的。
InnoDB 的 MVCC，是通過在每行記錄后面保存兩個隱藏的列來實現。這兩個列，一個保存了行的創建時間，一個保存行的過期時間（刪除時間）。當然存儲的并不是真實的時間，而是系統版本號（system version number）。每開始一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。

REPEATABLE READ（可重讀）隔離級別下MVCC如何工作：

• SELECT
  InnoDB會根據以下兩個條件檢查每行記錄：
  1.InnoDB只查找版本早于當前事務版本的數據行，這樣可以確保事務讀取的行，要么是在開始事務之前已經存在要么是事務自身插入或者修改過的
  2.行的刪除版本號要么未定義，要么大于當前事務版本號，這樣可以確保事務讀取到的行在事務開始之前未被刪除
  只有符合上述兩個條件的才會被查詢出來
• INSERT：InnoDB為新插入的每一行保存當前系統版本號作為行版本號
• DELETE：InnoDB為刪除的每一行保存當前系統版本號作為行刪除標識
• UPDATE：InnoDB為插入的一行新紀錄保存當前系統版本號作為行版本號，同時保存當前系統版本號到原來的行作為刪除標識
  保存這兩個額外系統版本號，使大多數操作都不用加鎖。使數據操作簡單，性能很好，并且也能保證只會讀取到符合要求的行。不足之處是每行記錄都需要額外的存儲空間，需要做更多的行檢查工作和一些額外的維護工作。

MVCC 只在 COMMITTED READ（讀提交）和REPEATABLE READ（可重復讀）兩種隔離級別下工作。

事務日志
InnoDB 使用日志來減少提交事務時的開銷。因為日志中已經記錄了事務，就無須在每個事務提交時把緩沖池的臟塊刷新(flush)到磁盤中。事務修改的數據和索引通常會映射到表空間的隨機位置，所以刷新這些變更到磁盤需要很多隨機 IO。
InnoDB 假設使用常規磁盤，隨機IO比順序IO昂貴得多，因為一個IO請求需要時間把磁頭移到正確的位置，然后等待磁盤上讀出需要的部分，再轉到開始位置。
InnoDB 用日志把隨機IO變成順序IO。一旦日志安全寫到磁盤，事務就持久化了，即使斷電了，InnoDB可以重放日志并且恢復已經提交的事務。
InnoDB 使用一個后臺線程智能地刷新這些變更到數據文件。這個線程可以批量組合寫入，使得數據寫入更順序，以提高效率。

事務日志可以幫助提高事務效率：

• 使用事務日志，存儲引擎在修改表的數據時只需要修改其內存拷貝，再把該修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據本身持久到磁盤。
• 事務日志采用的是追加的方式，因此寫日志的操作是磁盤上一小塊區域內的順序I/O，而不像隨機I/O需要在磁盤的多個地方移動磁頭，所以采用事務日志的方式相對來說要快得多。
• 事務日志持久以后，內存中被修改的數據在后臺可以慢慢刷回到磁盤。
• 如果數據的修改已經記錄到事務日志并持久化，但數據本身沒有寫回到磁盤，此時系統崩潰，存儲引擎在重啟時能夠自動恢復這一部分修改的數據。

目前來說，大多數存儲引擎都是這樣實現的，我們通常稱之為預寫式日志（Write-Ahead Logging），修改數據需要寫兩次磁盤。

事務的實現
答：事務的實現是基于數據庫的存儲引擎。不同的存儲引擎對事務的支持程度不一樣。MySQL 中支持事務的存儲引擎有 InnoDB 和 NDB。事務的實現就是如何實現ACID特性。事務的隔離性是通過鎖實現，而事務的原子性、一致性和持久性則是通過事務日志實現 。

補充問題：事務是如何通過日志來實現的？
答：事務日志包括：重做日志redo和回滾日志undo
1）redo log（重做日志） 實現持久化和原子性
在innoDB的存儲引擎中，事務日志通過重做(redo)日志和innoDB存儲引擎的日志緩沖(InnoDB Log Buffer)實現。事務開啟時，事務中的操作，都會先寫入存儲引擎的日志緩沖中，在事務提交之前，這些緩沖的日志都需要提前刷新到磁盤上持久化，這就是DBA們口中常說的“日志先行”(Write-Ahead Logging)。當事務提交之后，在Buffer Pool中映射的數據文件才會慢慢刷新到磁盤。此時如果數據庫崩潰或者宕機，那么當系統重啟進行恢復時，就可以根據redo log中記錄的日志，把數據庫恢復到崩潰前的一個狀態。未完成的事務，可以繼續提交，也可以選擇回滾，這基于恢復的策略而定。
在系統啟動的時候，就已經為redo log分配了一塊連續的存儲空間，以順序追加的方式記錄Redo Log，通過順序IO來改善性能。所有的事務共享redo log的存儲空間，它們的Redo Log按語句的執行順序，依次交替的記錄在一起。
2）undo log（回滾日志） 實現一致性
undo log 主要為事務的回滾服務。在事務執行的過程中，除了記錄redo log，還會記錄一定量的undo log。undo log記錄了數據在每個操作前的狀態，如果事務執行過程中需要回滾，就可以根據undo log進行回滾操作。單個事務的回滾，只會回滾當前事務做的操作，并不會影響到其他的事務做的操作。
Undo記錄的是已部分完成并且寫入硬盤的未完成的事務，默認情況下回滾日志是記錄下表空間中的（共享表空間或者獨享表空間）

二種日志均可以視為一種恢復操作，redo_log是恢復提交事務修改的頁操作，而undo_log是回滾行記錄到特定版本。二者記錄的內容也不同，redo_log是物理日志，記錄頁的物理修改操作，而undo_log是邏輯日志，根據每行記錄進行記錄。

補充問題：MySQL 有多少種日志嗎？

• 錯誤日志：記錄出錯信息，也記錄一些警告信息或者正確的信息。
• 查詢日志：記錄所有對數據庫請求的信息，不論這些請求是否得到了正確的執行。
• 慢查詢日志：設置一個閾值，將運行時間超過該值的所有SQL語句都記錄到慢查詢的日志文件中。
• 二進制日志：記錄對數據庫執行更改的所有操作。
• 中繼日志：中繼日志也是二進制日志，用來給slave 庫恢復
• 事務日志：重做日志redo和回滾日志undo

MySQL對分布式事務的支持
答：分布式事務的實現方式有很多，既可以采用 InnoDB 提供的原生的事務支持，也可以采用消息隊列來實現分布式事務的最終一致性。MySQL 從 5.0.3 InnoDB 存儲引擎開始支持XA協議的分布式事務。**一個分布式事務會涉及多個行動，這些行動本身是事務性的。所有行動都必須一起成功完成，或者一起被回滾。**在MySQL中，使用分布式事務涉及一個或多個資源管理器和一個事務管理器。

如圖，MySQL 的分布式事務模型分為三塊：應用程序（AP）、資源管理器（RM）、事務管理器（TM）:

• 應用程序：定義了事務的邊界，指定需要做哪些事務；
• 資源管理器：提供了訪問事務的方法，通常一個數據庫就是一個資源管理器；
• 事務管理器：協調參與了全局事務中的各個事務。

分布式事務采用兩段式提交（two-phase commit）的方式：

• 第一階段所有的事務節點開始準備，告訴事務管理器ready。
• 第二階段事務管理器告訴每個節點是commit還是rollback。如果有一個節點失敗，就需要全局的節點全部rollback，以此保障事務的原子性。

MySQL鎖機制

數據庫的樂觀鎖和悲觀鎖？MySQL 中有哪幾種鎖，列舉一下？MySQL中InnoDB引擎的行鎖是怎么實現的？MySQL 間隙鎖有沒有了解，死鎖有沒有了解，寫一段會造成死鎖的 sql 語句，死鎖發生了如何解決，MySQL 有沒有提供什么機制去解決死鎖?
答：鎖是計算機協調多個進程或線程并發訪問某一資源的機制。在數據庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。數據庫鎖定機制簡單來說，就是數據庫為了保證數據的一致性，而使各種共享資源在被并發訪問變得有序所設計的一種規則。

1. 鎖的分類：
1）從對數據操作的類型分類：

• 讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行，不會互相影響
• 寫鎖（排他鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖
  2）從對數據操作的粒度分類：
  為了盡可能提高數據庫的并發度，每次鎖定的數據范圍越小越好，理論上每次只鎖定當前操作的數據的方案會得到最大的并發度，但是管理鎖是很耗資源的事情（涉及獲取，檢查，釋放鎖等動作），因此數據庫系統需要在高并發響應和系統性能兩方面進行平衡，這樣就產生了“鎖粒度（Lock granularity）”的概念。
• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低（MyISAM 和 MEMORY 存儲引擎采用的是表級鎖）；
• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高（InnoDB 存儲引擎既支持行級鎖也支持表級鎖，但默認情況下是采用行級鎖）；
• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。
  適用：從鎖的角度來說，表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如Web應用；而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理（OLTP）系統。
  

2. MyISAM表鎖
MyISAM 的表鎖有兩種模式：

• 表共享讀鎖 （Table Read Lock）：不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
• 表獨占寫鎖 （Table Write Lock）：會阻塞其他用戶對同一表的讀和寫操作；
  MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。當一個線程獲得對一個表的寫鎖后， 只有持有鎖的線程可以對表進行更新操作。其他線程的讀、 寫操作都會等待，直到鎖被釋放為止。默認情況下，寫鎖比讀鎖具有更高的優先級：當一個鎖釋放時，這個鎖會優先給寫鎖隊列中等候的獲取鎖請求，然后再給讀鎖隊列中等候的獲取鎖請求。

3. InnoDB行鎖
InnoDB 實現了以下兩種類型的行鎖：

• 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
• 排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。
  為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖：
• 意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的 IS 鎖。
• 意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的 IX 鎖。
  索引失效會導致行鎖變表鎖。比如 vchar 查詢不寫單引號的情況。
  1）加鎖機制
  樂觀鎖與悲觀鎖是兩種并發控制的思想，可用于解決丟失更新問題
  樂觀鎖會“樂觀地”假定大概率不會發生并發更新沖突，訪問、處理數據過程中不加鎖，只在更新數據時再根據版本號或時間戳判斷是否有沖突，有則處理，無則提交事務。用數據版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式
  悲觀鎖會“悲觀地”假定大概率會發生并發更新沖突，訪問、處理數據前就加排他鎖，在整個數據處理過程中鎖定數據，事務提交或回滾后才釋放鎖。另外與樂觀鎖相對應的，悲觀鎖是由數據庫自己實現了的，要用的時候，我們直接調用數據庫的相關語句就可以了。
  2）鎖模式(InnoDB有三種行鎖的算法)
• 記錄鎖(Record Locks)：單個行記錄上的鎖。對索引項加鎖，鎖定符合條件的行。其他事務不能修改和刪除加鎖項；

SELECT * FROM table WHERE id = 1 FOR UPDATE;

它會在 id=1 的記錄上加上記錄鎖，以阻止其他事務插入，更新，刪除 id=1 這一行
在通過 主鍵索引 與 唯一索引 對數據行進行 UPDATE 操作時，也會對該行數據加記錄鎖：

-- id 列為主鍵列或唯一索引列 UPDATE SET age = 50 WHERE id = 1;

• 間隙鎖（Gap Locks）：當我們使用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖。對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙”。
  InnoDB 也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖。
  對索引項之間的“間隙”加鎖，鎖定記錄的范圍（對第一條記錄前的間隙或最后一條將記錄后的間隙加鎖），不包含索引項本身。其他事務不能在鎖范圍內插入數據，這樣就防止了別的事務新增幻影行。
  間隙鎖基于非唯一索引，它鎖定一段范圍內的索引記錄。間隙鎖基于下面將會提到的Next-Key Locking 算法，請務必牢記：使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條數據。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）區間內的記錄行都會被鎖住，所有id 為 2、3、4、5、6、7、8、9 的數據行的插入會被阻塞，但是 1 和 10 兩條記錄行并不會被鎖住。
GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況

• 臨鍵鎖(Next-key Locks)：臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區間。(臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務的隔離級別降級為RC，臨鍵鎖則也會失效。)
  Next-Key 可以理解為一種特殊的間隙鎖，也可以理解為一種特殊的算法。通過臨建鎖可以解決幻讀的問題。每個數據行上的非唯一索引列上都會存在一把臨鍵鎖，當某個事務持有該數據行的臨鍵鎖時，會鎖住一段左開右閉區間的數據。需要強調的一點是，InnoDB 中行級鎖是基于索引實現的，臨鍵鎖只與非唯一索引列有關，在唯一索引列（包括主鍵列）上不存在臨鍵鎖。
  對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。

補充問題：select for update有什么含義，會鎖表還是鎖行還是其他？
for update 僅適用于InnoDB，且必須在事務塊(BEGIN/COMMIT)中才能生效。在進行事務操作時，通過“for update”語句，MySQL會對查詢結果集中每行數據都添加排他鎖，其他線程對該記錄的更新與刪除操作都會阻塞。排他鎖包含行鎖、表鎖。
InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！假設有個表單 products ，里面有id跟name二個欄位，id是主鍵。

• 明確指定主鍵，并且有該記錄，則row lock

SELECT * FROM products WHERE id='3' FOR UPDATE; SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

• 明確指定主鍵，若沒有該記錄，則無lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

• 無主鍵， table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

• 主鍵不明確， table lock

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

• 主鍵不明確，table lock

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE僅適用于InnoDB，且必須在交易區塊(BEGIN/COMMIT)中才能生效。注2: 要測試鎖定的狀況，可以利用MySQL的Command Mode ，開二個視窗來做測試。

補充問題：MySQL 遇到過死鎖問題嗎，你是如何解決的？
4.死鎖
死鎖產生：

• 死鎖是指兩個或多個事務在同一資源上相互占用，并請求鎖定對方占用的資源，從而導致惡性循環
• 當事務試圖以不同的順序鎖定資源時，就可能產生死鎖。多個事務同時鎖定同一個資源時也可能會產生死鎖
• 鎖的行為和順序和存儲引擎相關。以同樣的順序執行語句，有些存儲引擎會產生死鎖有些不會——死鎖有雙重原因：真正的數據沖突；存儲引擎的實現方式。

**檢測死鎖：**數據庫系統實現了各種死鎖檢測和死鎖超時的機制。InnoDB存儲引擎能檢測到死鎖的循環依賴并立即返回一個錯誤。

**死鎖恢復：**死鎖發生以后，只有部分或完全回滾其中一個事務，才能打破死鎖，InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務進行回滾。所以事務型應用程序在設計時必須考慮如何處理死鎖，多數情況下只需要重新執行因死鎖回滾的事務即可。

**外部鎖的死鎖檢測：**發生死鎖后，InnoDB 一般都能自動檢測到，并使一個事務釋放鎖并回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 并不能完全自動檢測到死鎖， 這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout 來解決

**死鎖影響性能：**死鎖會影響性能而不是會產生嚴重錯誤，因為InnoDB會自動檢測死鎖狀況并回滾其中一個受影響的事務。在高并發系統上，當許多線程等待同一個鎖時，死鎖檢測可能導致速度變慢。有時當發生死鎖時，禁用死鎖檢測（使用innodb_deadlock_detect配置選項）可能會更有效，這時可以依賴innodb_lock_wait_timeout設置進行事務回滾。

MyISAM避免死鎖：
在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，所以 MyISAM 表不會出現死鎖。
InnoDB避免死鎖：

• 為了在單個InnoDB表上執行多個并發寫入操作時避免死鎖，可以在事務開始時通過為預期要修改的每個元祖（行）使用SELECT ... FOR UPDATE語句來獲取必要的鎖，即使這些行的更改語句是在之后才執行的。
• 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖、更新時再申請排他鎖，因為這時候當用戶再申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖
• 如果事務需要修改或鎖定多個表，則應在每個事務中以相同的順序使用加鎖語句。在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會
• 通過SELECT ... LOCK IN SHARE MODE獲取行的讀鎖后，如果當前事務再需要對該記錄進行更新操作，則很有可能造成死鎖。
• 改變事務隔離級別
  如果出現死鎖，可以用 show engine innodb status;命令來確定最后一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發死鎖的 SQL 語句，事務已經獲得的鎖，正在等待什么鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。

MySQL調優

日常工作中你是怎么優化SQL的？SQL優化的一般步驟是什么，怎么看執行計劃（explain），如何理解其中各個字段的含義？如何寫sql能夠有效的使用到復合索引？一條sql執行過長的時間，你如何優化，從哪些方面入手？什么是最左前綴原則？什么是最左匹配原則？
1. 影響MySQL的性能因素：

• 業務需求對MySQL的影響（合適合度）
• 存儲定位對MySQL的影響
  - 系統各種配置及規則數據
  - 活躍用戶的基本信息數據
  - 活躍用戶的個性化定制信息數據
  - 準實時的統計信息數據
  - 其他一些訪問頻繁但變更較少的數據
  - 二進制多媒體數據
  - 流水隊列數據‘
  - 超大文本數據
  - 不適合放進MySQL的數據
  - 需要放進緩存的數據
• Schema設計對系統的性能影響
  - 盡量減少對數據庫訪問的請求
  - 盡量減少無用數據的查詢請求
• 硬件環境對系統性能的影響
  2.性能分析
  1）MySQL Query Optimizer
• MySQL 中有專門負責優化 SELECT 語句的優化器模塊，主要功能：通過計算分析系統中收集到的統計信息，為客戶端請求的 Query 提供他認為最優的執行計劃（他認為最優的數據檢索方式，但不見得是 DBA 認為是最優的，這部分最耗費時間）
• 當客戶端向 MySQL 請求一條 Query，命令解析器模塊完成請求分類，區別出是 SELECT 并轉發給 MySQL Query Optimize r時，MySQL Query Optimizer 首先會對整條 Query 進行優化，處理掉一些常量表達式的預算，直接換算成常量值。并對 Query 中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、結構調整等。然后分析 Query 中的 Hint 信息（如果有），看顯示 Hint 信息是否可以完全確定該 Query 的執行計劃。如果沒有 Hint 或 Hint 信息還不足以完全確定執行計劃，則會讀取所涉及對象的統計信息，根據 Query 進行寫相應的計算分析，然后再得出最后的執行計劃。
  2）MySQL常見瓶頸
• CPU：CPU在飽和的時候一般發生在數據裝入內存或從磁盤上讀取數據時候
• IO：磁盤I/O瓶頸發生在裝入數據遠大于內存容量的時候
• 服務器硬件的性能瓶頸：top，free，iostat 和 vmstat來查看系統的性能狀態
  3）性能下降SQL慢 執行時間長 等待時間長 原因分析
• 查詢語句寫的爛
• 索引失效（單值、復合）
• 關聯查詢太多join（設計缺陷或不得已的需求）
• 服務器調優及各個參數設置（緩沖、線程數等）
  4）MySQL常見性能分析手段
  在優化MySQL時，通常需要對數據庫進行分析，常見的分析手段有慢查詢日志，EXPLAIN 分析查詢，profiling分析以及show命令查詢系統狀態及系統變量，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能。
  性能瓶頸定位
  我們可以通過 show 命令查看 MySQL 狀態及變量，找到系統的瓶頸：

Mysql> show status ——顯示狀態信息（擴展show status like ‘XXX’） Mysql> show variables ——顯示系統變量（擴展show variables like ‘XXX’） Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態 Mysql> show processlist ——查看當前SQL執行，包括執行狀態、是否鎖表等 Shell> mysqladmin variables -u username -p password——顯示系統變量 Shell> mysqladmin extended-status -u username -p password——顯示狀態信息

Explain(執行計劃)
是什么：使用 Explain 關鍵字可以模擬優化器執行SQL查詢語句，從而知道 MySQL 是如何處理你的 SQL 語句的。分析你的查詢語句或是表結構的性能瓶頸。
能干什么：

• 表的讀取順序
• 數據讀取操作的操作類型
• 哪些索引可以使用
• 哪些索引被實際使用
• 表之間的引用
• 每張表有多少行被優化器查詢
  怎么玩：
• Explain + SQL語句
• 執行計劃包含的信息（如果有分區表的話還會有partitions）
  
  各字段解釋
• id（select 查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序）
  • id相同，執行順序從上往下
  • id全不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行
  • id部分相同，執行順序是先按照數字大的先執行，然后數字相同的按照從上往下的順序執行
• select_type（查詢類型，用于區別普通查詢、聯合查詢、子查詢等復雜查詢）
  • SIMPLE ：簡單的select查詢，查詢中不包含子查詢或UNION
  • PRIMARY：查詢中若包含任何復雜的子部分，最外層查詢被標記為PRIMARY
  • SUBQUERY：在select或where列表中包含了子查詢
  • DERIVED：在from列表中包含的子查詢被標記為DERIVED，MySQL會遞歸執行這些子查詢，把結果放在臨時表里
  • UNION：若第二個select出現在UNION之后，則被標記為UNION，若UNION包含在from子句的子查詢中，外層select將被標記為 DERIVED
  • UNION RESULT：從UNION表獲取結果的select
• table（顯示這一行的數據是關于哪張表的）
• type（顯示查詢使用了那種類型，從最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）
  tip: 一般來說，得保證查詢至少達到range級別，最好到達ref
  • system：表只有一行記錄（等于系統表），是 const 類型的特例，平時不會出現
  • const：表示通過索引一次就找到了，const 用于比較 primary key 或 unique 索引，因為只要匹配一行數據，所以很快，如將主鍵置于 where 列表中，mysql 就能將該查詢轉換為一個常量
  • eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描
  • ref：非唯一性索引掃描，范圍匹配某個單獨值得所有行。本質上也是一種索引訪問，他返回所有匹配某個單獨值的行，然而，它可能也會找到多個符合條件的行，多以他應該屬于查找和掃描的混合體
  • range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是在你的where語句中出現了between、<、>、in等的查詢，這種范圍掃描索引比全表掃描要好，因為它只需開始于索引的某一點，而結束于另一點，不用掃描全部索引
  • index：Full Index Scan，index于ALL區別為index類型只遍歷索引樹。通常比ALL快，因為索引文件通常比數據文件小。（也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）
  • ALL：Full Table Scan，將遍歷全表找到匹配的行
• possible_keys（顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的字段若存在索引，則該索引將被列出，但不一定被查詢實際使用）
• key
  • 實際使用的索引，如果為NULL，則沒有使用索引
  • 查詢中若使用了覆蓋索引，則該索引和查詢的 select 字段重疊，僅出現在key列表中
    
• key_len
  表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
  • key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的
  • ref（顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引列上的值）
• rows（根據表統計信息及索引選用情況，大致估算找到所需的記錄所需要讀取的行數）
• Extra（包含不適合在其他列中顯示但十分重要的額外信息）
• using filesort: 說明mysql會對數據使用一個外部的索引排序，不是按照表內的索引順序進行讀取。**mysql中無法利用索引完成的排序操作稱為“文件排序”。**常見于order by和group by語句中
• Using temporary：使用了臨時表保存中間結果，mysql在對查詢結果排序時使用臨時表。常見于排序order by和分組查詢group by。
• using index：表示相應的select操作中使用了覆蓋索引，避免訪問了表的數據行，效率不錯，如果同時出現using where，表明索引被用來執行索引鍵值的查找；否則索引被用來讀取數據而非執行查找操作
• using where：使用了where過濾
• using join buffer：使用了連接緩存
• impossible where：where子句的值總是false，不能用來獲取任何元祖
• select tables optimized away：在沒有group by子句的情況下，基于索引優化操作或對于MyISAM存儲引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化
• distinct：優化distinct操作，在找到第一匹配的元祖后即停止找同樣值的動作
  示例：
  
  1 第一行（執行順序4）：id列為1，表示是union里的第一個select，select_type列的primary表示該查詢為外層查詢，table列被標記為，表示查詢結果來自一個衍生表，其中derived3中3代表該查詢衍生自第三個select查詢，即id為3的select。【select d1.name…】
  2 第二行（執行順序2）：id為3，是整個查詢中第三個select的一部分。因查詢包含在from中，所以為derived。【select id,name from t1 where other_column=’’】
  3 第三行（執行順序3）：select列表中的子查詢select_type為subquery，為整個查詢中的第二個select。【select id from t3】
  4 第四行（執行順序1）：select_type為union，說明第四個select是union里的第二個select，最先執行【select name,id from t2】
  5 第五行（執行順序5）：代表從union的臨時表中讀取行的階段，table列的<union1,4>表示用第一個和第四個select的結果進行union操作。【兩個結果union操作】

慢查詢日志
MySQL 的慢查詢日志是 MySQL 提供的一種日志記錄，它用來記錄在 MySQL 中響應時間超過閾值的語句，具體指運行時間超過 long_query_time 值的 SQL，則會被記錄到慢查詢日志中。

• long_query_time 的默認值為10，意思是運行10秒以上的語句
• 默認情況下，MySQL數據庫沒有開啟慢查詢日志，需要手動設置參數開啟
  查看開啟狀態

mysql> show variables like '%slow_query_log%';

開啟慢查詢日志

• 臨時配置：

mysql> set global slow_query_log='ON'; mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log'; mysql> set global long_query_time=2;

也可set文件位置，系統會默認給一個缺省文件host_name-slow.log
使用set操作開啟慢查詢日志只對當前數據庫生效，如果MySQL重啟則會失效。

• 永久配置
  修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入兩個配置參數

[mysqld] slow_query_log = ON slow_query_log_file = /var/lib/mysql/hostname-slow.log long_query_time = 3

注：log-slow-queries 參數為慢查詢日志存放的位置，一般這個目錄要有 MySQL 的運行帳號的可寫權限，一般都將這個目錄設置為 MySQL 的數據存放目錄；long_query_time=2 中的 2 表示查詢超過兩秒才記錄；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 參數，表示記錄下沒有使用索引的查詢。
可以用 select sleep(4) 驗證是否成功開啟。
在生產環境中，如果手工分析日志，查找、分析SQL，還是比較費勁的，所以MySQL提供了日志分析工具mysqldumpslow。通過 mysqldumpslow --help 查看操作幫助信息：

• 得到返回記錄集最多的10個SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log

• 得到訪問次數最多的10個SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log

• 得到按照時間排序的前10條里面含有左連接的查詢語句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log

• 也可以和管道配合使用

mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more

也可使用 pt-query-digest 分析 RDS MySQL 慢查詢日志

Show Profile 分析查詢
通過慢日志查詢可以知道哪些 SQL 語句執行效率低下，通過 explain 我們可以得知 SQL 語句的具體執行情況，索引使用等，還可以結合Show Profile命令查看執行狀態。

• Show Profile 是 MySQL 提供可以用來分析當前會話中語句執行的資源消耗情況。可以用于SQL的調優的測量
• 默認情況下，參數處于關閉狀態，并保存最近15次的運行結果
• 分析步驟
  mysql> show profiles; ±---------±-----------±--------------------------------+ | Query_ID | Duration | Query | ±---------±-----------±--------------------------------+ | 1 | 0.00385450 | show variables like “profiling” | | 2 | 0.00170050 | show variables like “profiling” | | 3 | 0.00038025 | select * from t_base_user | ±---------±-----------±--------------------------------+
• converting HEAP to MyISAM 查詢結果太大，內存都不夠用了往磁盤上搬了。
• create tmp table 創建臨時表，這個要注意
• Copying to tmp table on disk 把內存臨時表復制到磁盤
• locked
• 診斷SQL，show profile cpu,block io for query id(上一步前面的問題SQL數字號碼)
• 日常開發需要注意的結論

1.是否支持，看看當前的mysql版本是否支持 mysql>Show variables like 'profiling'; --默認是關閉，使用前需要開啟 2.開啟功能，默認是關閉，使用前需要開啟 mysql>set profiling=1; 3.運行SQL 4.查看結果

補充問題：查詢中哪些情況不會使用索引？
5）性能優化
索引優化
1.全值匹配我最愛
2.最佳左前綴法則，比如建立了一個聯合索引(a,b,c)，那么其實我們可利用的索引就有(a), (a,b), (a,b,c)
3.不在索引列上做任何操作（計算、函數、(自動or手動)類型轉換），會導致索引失效而轉向全表掃描
4.存儲引擎不能使用索引中范圍條件右邊的列
5.盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致))，減少select
6.is null ,is not null 也無法使用索引
7.like “xxxx%” 是可以用到索引的，like “%xxxx” 則不行(like “%xxx%” 同理)。like以通配符開頭(’%abc…’)索引失效會變成全表掃描的操作，
8.字符串不加單引號索引失效
9.少用or，用它來連接時會索引失效
10.<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 則不行，會導致全表掃描
一般性建議

• 對于單鍵索引，盡量選擇針對當前query過濾性更好的索引
• 在選擇組合索引的時候，當前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好。
• 在選擇組合索引的時候，盡量選擇可以能夠包含當前query中的where字句中更多字段的索引
• 盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的
• 少用Hint強制索引

查詢優化
永遠小標驅動大表（小的數據集驅動大的數據集）

slect * from A where id in (select id from B)`等價于 #等價于 select id from B select * from A where A.id=B.id

當 B 表的數據集必須小于 A 表的數據集時，用 in 優于 exists

select * from A where exists (select 1 from B where B.id=A.id) #等價于 select * from A select * from B where B.id = A.id`

當 A 表的數據集小于B表的數據集時，用 exists優于用 in
注意：A表與B表的ID字段應建立索引。

order by關鍵字優化

• order by子句，盡量使用 Index 方式排序，避免使用 FileSort 方式排序
• MySQL 支持兩種方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 掃描索引本身完成排序，FileSort 效率較低；
• ORDER BY 滿足兩種情況，會使用Index方式排序；①ORDER BY語句使用索引最左前列 ②使用where子句與ORDER BY子句條件列組合滿足索引最左前列
• 盡可能在索引列上完成排序操作，遵照索引建的最佳最前綴
• 如果不在索引列上，filesort 有兩種算法，mysql就要啟動雙路排序和單路排序
  • 雙路排序：MySQL 4.1之前是使用雙路排序,字面意思就是兩次掃描磁盤，最終得到數據
  • 單路排序：從磁盤讀取查詢需要的所有列，按照order by 列在 buffer對它們進行排序，然后掃描排序后的列表進行輸出，效率高于雙路排序
• 優化策略
  • 增大sort_buffer_size參數的設置
  • 增大max_lencth_for_sort_data參數的設置

GROUP BY關鍵字優化

• group by實質是先排序后進行分組，遵照索引建的最佳左前綴
• 當無法使用索引列，增大 max_length_for_sort_data 參數的設置，增大sort_buffer_size參數的設置
• where高于having，能寫在where限定的條件就不要去having限定了

數據類型優化
MySQL 支持的數據類型非常多，選擇正確的數據類型對于獲取高性能至關重要。不管存儲哪種類型的數據，下面幾個簡單的原則都有助于做出更好的選擇。

• 更小的通常更好：一般情況下，應該盡量使用可以正確存儲數據的最小數據類型。
  簡單就好：簡單的數據類型通常需要更少的CPU周期。例如，整數比字符操作代價更低，因為字符集和校對規則（排序規則）使字符比較比整型比較復雜。
• 盡量避免NULL：通常情況下最好指定列為NOT NULL

MySQL分區、分表、分庫

MySQL分區
一般情況下我們創建的表對應一組存儲文件，使用MyISAM存儲引擎時是一個.MYI和.MYD文件，使用Innodb存儲引擎時是一個.ibd和.frm（表結構）文件。
當數據量較大時（一般千萬條記錄級別以上），MySQL的性能就會開始下降，這時我們就需要將數據分散到多組存儲文件，保證其單個文件的執行效率
能干嘛

• 邏輯數據分割
• 提高單一的寫和讀應用速度
• 提高分區范圍讀查詢的速度
• 分割數據能夠有多個不同的物理文件路徑
• 高效的保存歷史數據

怎么玩
首先查看當前數據庫是否支持分區

• MySQL5.6以及之前版本：

SHOW VARIABLES LIKE '%partition%';

• MySQL5.6：

show plugins;

分區類型及操作

• RANGE分區：基于屬于一個給定連續區間的列值，把多行分配給分區。mysql將會根據指定的拆分策略，,把數據放在不同的表文件上。相當于在文件上,被拆成了小塊.但是,對外給客戶的感覺還是一張表，透明的。

按照 range 來分，就是每個庫一段連續的數據，這個一般是按比如時間范圍來的，比如交易表啊，銷售表啊等，可以根據年月來存放數據。可能會產生熱點問題，大量的流量都打在最新的數據上了。
range 來分，好處在于說，擴容的時候很簡單。

• LIST分區：類似于按RANGE分區，每個分區必須明確定義。它們的主要區別在于，LIST分區中每個分區的定義和選擇是基于某列的值從屬于一個值列表集中的一個值，而RANGE分區是從屬于一個連續區間值的集合。

• HASH分區：基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。
  hash 分發，好處在于說，可以平均分配每個庫的數據量和請求壓力；壞處在于說擴容起來比較麻煩，會有一個數據遷移的過程，之前的數據需要重新計算 hash 值重新分配到不同的庫或表

• KEY分區：類似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。

看上去分區表很帥氣，為什么大部分互聯網還是更多的選擇自己分庫分表來水平擴展咧？

• 分區表，分區鍵設計不太靈活，如果不走分區鍵，很容易出現全表鎖
• 一旦數據并發量上來，如果在分區表實施關聯，就是一個災難
• 自己分庫分表，自己掌控業務場景與訪問模式，可控。分區表，研發寫了一個sql，都不確定mysql是怎么玩的，不太可控

補充問題：隨著業務的發展，業務越來越復雜，應用的模塊越來越多，總的數據量很大，高并發讀寫操作均超過單個數據庫服務器的處理能力怎么辦？
答：這個時候就出現了數據分片，數據分片指按照某個維度將存放在單一數據庫中的數據分散地存放至多個數據庫或表中。數據分片的有效手段就是對關系型數據庫進行分庫和分表。
區別于分區的是，分區一般都是放在單機里的，用的比較多的是時間范圍分區，方便歸檔。只不過分庫分表需要代碼實現，分區則是mysql內部實現。分庫分表和分區并不沖突，可以結合使用。
補充問題：說說分庫與分表的設計。
1）MySQL分表
分表有兩種分割方式，一種垂直拆分，另一種水平拆分。

• 垂直拆分
  垂直分表，通常是按照業務功能的使用頻次，把主要的、熱門的字段放在一起做為主要表。然后把不常用的，按照各自的業務屬性進行聚集，拆分到不同的次要表中；主要表和次要表的關系一般都是一對一的。
• 水平拆分(數據分片)
  單表的容量不超過500W，否則建議水平拆分。是把一個表復制成同樣表結構的不同表，然后把數據按照一定的規則劃分，分別存儲到這些表中，從而保證單表的容量不會太大，提升性能；當然這些結構一樣的表，可以放在一個或多個數據庫中。
  水平分割的幾種方法：
  1.使用MD5哈希，做法是對UID進行md5加密，然后取前幾位（我們這里取前兩位），然后就可以將不同的UID哈希到不同的用戶表（user_xx）中了。
  2.還可根據時間放入不同的表，比如：article_201601，article_201602。
  3.按熱度拆分，高點擊率的詞條生成各自的一張表，低熱度的詞條都放在一張大表里，待低熱度的詞條達到一定的貼數后，再把低熱度的表單獨拆分成一張表。
  4.根據ID的值放入對應的表，第一個表user_0000，第二個100萬的用戶數據放在第二 個表user_0001中，隨用戶增加，直接添加用戶表就行了。
  
  2）MySQL分庫
  問題：為什么要分庫?
  答：數據庫集群環境后都是多臺 slave，基本滿足了讀取操作; 但是寫入或者說大數據、頻繁的寫入操作對master性能影響就比較大，這個時候，單庫并不能解決大規模并發寫入的問題，所以就會考慮分庫。
  問題：分庫是什么？
  答：一個庫里表太多了，導致了海量數據，系統性能下降，把原本存儲于一個庫的表拆分存儲到多個庫上， 通常是將表按照功能模塊、關系密切程度劃分出來，部署到不同庫上。

優點：

• 減少增量數據寫入時的鎖對查詢的影響
• 由于單表數量下降，常見的查詢操作由于減少了需要掃描的記錄，使得單表單次查詢所需的檢索行數變少，減少了磁盤IO，時延變短
  但是它無法解決單表數據量太大的問題

分庫分表后的難題：分布式事務的問題，數據的完整性和一致性問題。
數據操作維度問題：用戶、交易、訂單各個不同的維度，用戶查詢維度、產品數據分析維度的不同對比分析角度。**跨庫聯合查詢的問題，**可能需要兩次查詢 跨節點的count、order by、group by以及聚合函數問題，可能需要分別在各個節點上得到結果后在應用程序端進行合并。額外的數據管理負擔，如：訪問數據表的導航定位。 額外的數據運算壓力，如：需要在多個節點執行，然后再合并，計算程序編碼開發難度提升，沒有太好的框架解決，更多依賴業務看如何分，如何合，是個難題。

主從復制

復制的基本原理

• slave 會從 master 讀取 binlog 來進行數據同步
• 三個步驟：
  
  1）master將改變記錄到二進制日志（binary log）。這些記錄過程叫做二進制日志事件，binary log events；
  2）salve 將 master 的 binary log events 拷貝到它的中繼日志（relay log）;
  3）slave 重做中繼日志中的事件，將改變應用到自己的數據庫中。MySQL 復制是異步且是串行化的。

復制的基本原則

• 每個 slave只有一個 master
• 每個 salve只能有一個唯一的服務器 ID
• 每個master可以有多個salve

復制的最大問題：延時

其他問題

說一說三個范式

• 第一范式（1NF）：數據庫表中的字段都是單一屬性的，不可再分。這個單一屬性由基本類型構成，包括整型、實數、字符型、邏輯型、日期型等。
• 第二范式（2NF）：數據庫表中不存在非關鍵字段對任一候選關鍵字段的部分函數依賴（部分函數依賴指的是存在組合關鍵字中的某些字段決定非關鍵字段的情況），也即所有非關鍵字段都完全依賴于任意一組候選關鍵字。
• 第三范式（3NF）：在第二范式的基礎上，數據表中如果不存在非關鍵字段對任一候選關鍵字段的傳遞函數依賴則符合第三范式。所謂傳遞函數依賴，指的是如 果存在"A → B → C"的決定關系，則C傳遞函數依賴于A。因此，滿足第三范式的數據庫表應該不存在如下依賴關系：關鍵字段 → 非關鍵字段 x → 非關鍵字段y

百萬級別或以上的數據如何刪除
關于索引：由于索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數據的增加,修改,刪除,都會產生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執行效率。所以，在我們刪除數據庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數據的速度和創建的索引數量是成正比的。所以我們想要刪除百萬數據的時候：
1）可以先刪除索引（此時大概耗時三分多鐘）
2）然后刪除其中無用數據（此過程需要不到兩分鐘）
3）刪除完成后重新創建索引(此時數據較少了)創建索引也非常快，約十分鐘左右。
與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。

總結

以上是生活随笔為你收集整理的【面试】网易游戏面试题目整理及答案（3）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。