作者： Grey
原文位址：MySQL學習筆記
注：本文中的SQL語句如果用到了特定方言，都是基于MySQL資料庫。
如果是查詢語句，會走如上流程圖，如果是更新語句，同樣要走如上邏輯，同時更新語句會把這個表的查詢緩存結果都清空。接下來，分析器會通過詞法和文法解析知道這是一條更新語句。優化器決定要使用 ID 這個索引。然後，執行器負責具體執行，找到這一行，然後更新。與查詢流程不一樣的是，更新流程還涉及兩個重要的日志子產品：redo log（重做日志）和 binlog（歸檔日志）
Redo Log是引擎層日志
使用了WAL技術，先寫日志，再寫磁盤，當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log（粉闆）裡面，并更新記憶體，這個時候更新就算完成了。同時，InnoDB 引擎會在适當的時候，将這個操作記錄更新到磁盤裡面，而這個更新往往是在系統比較空閑的時候做
redo log是固定大小的，可以配置一組4個檔案，每個檔案大小1GB，那麼可以記錄4G記錄。
有了 redo log，InnoDB 就可以保證即使資料庫發生異常重新開機，之前送出的記錄都不會丢失，這個能力稱為 crash-safe。
binlog是Server層日志
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實作的，所有引擎都可以使用。
redo log 是實體日志，記錄的是“在某個資料頁上做了什麼修改”；binlog 是邏輯日志，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。redo log 是循環寫的，空間固定會用完；
binlog 是可以追加寫入的。“追加寫”是指 binlog 檔案寫到一定大小後會切換到下一個，并不會覆寫以前的日志。
是以，即便有了redo log，binlog還不能去掉，一個原因是，redolog隻有InnoDB有，别的引擎沒有。另一個原因是，redolog是循環寫的，不持久儲存，binlog的“歸檔”這個功能，redolog是不具備的。
MySQL在執行這段更新操作的時候：
圖檔引用自：MySQL實戰45講
操作過程如上圖: 圖中淺色框表示是在 InnoDB 内部執行的，深色框表示是在執行器中執行的。最後三步将 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段送出"。
為什麼要使用兩階段送出？用前面的 update 語句來做例子。假設目前 ID=2 的行，字段 c 的值是 0，再假設執行 update 語句過程中在寫完第一個日志後，第二個日志還沒有寫完期間發生了 crash，會出現什麼情況呢？
先寫 redo log 後寫 binlog。假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 程序異常重新開機。由于我們前面說過的，redo log 寫完之後，系統即使崩潰，仍然能夠把資料恢複回來，是以恢複後這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了，這時候 binlog 裡面就沒有記錄這個語句。是以，之後備份日志的時候，存起來的 binlog 裡面就沒有這條語句。然後你會發現，如果需要用這個 binlog 來恢複臨時庫的話，由于這個語句的 binlog 丢失，這個臨時庫就會少了這一次更新，恢複出來的這一行 c 的值就是 0，與原庫的值不同。
先寫 binlog 後寫 redo log。如果在 binlog 寫完之後 crash，由于 redo log 還沒寫，崩潰恢複以後這個事務無效，是以這一行 c 的值是 0。但是 binlog 裡面已經記錄了“把 c 從 0 改成 1”這個日志。是以，在之後用 binlog 來恢複的時候就多了一個事務出來，恢複出來的這一行 c 的值就是 1，與原庫的值不同。
可以看到，如果不使用“兩階段送出”，那麼資料庫的狀态就有可能和用它的日志恢複出來的庫的狀态不一緻。
redo log 用于保證 crash-safe 能力。<code>innodb_flush_log_at_trx_commit</code> 這個參數設定成 1 的時候，表示每次事務的 redo log 都直接持久化到磁盤。這個參數我建議你設定成 1，這樣可以保證 MySQL 異常重新開機之後資料不丢失。
<code>sync_binlog</code> 這個參數設定成 1 的時候，表示每次事務的 binlog 都持久化到磁盤。這個參數我也建議你設定成 1，這樣可以保證 MySQL 異常重新開機之後 binlog 不丢失。
DDL 的英文全稱是 Data Definition Language，中文是資料定義語言。它定義了資料庫的結構和資料表的結構。在 DDL 中，我們常用的功能是增删改，分别對應的指令是 <code>CREATE</code>、<code>DROP</code> 和 <code>ALTER</code>。
需要注意的是：在執行 DDL 的時候，不需要 COMMIT，就可以完成執行任務。
排序規則是<code>utf8_general_ci</code>，代表對大小寫不敏感。
如果設定為<code>utf8_bin</code>，代表對大小寫敏感。
<code>DISTINCT</code>其實是對後面所有列名的組合進行去重
其實是對<code>(attack_range,name)</code>這個組合去重。
另外在查詢過程中，我們可以限制傳回結果的數量，使用 LIMIT 關鍵字。
在不同的 DBMS 中使用的關鍵字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 關鍵字，而且需要放到 SELECT 語句的最後面。如果是 SQL Server 和 Access，需要使用 TOP 關鍵字，比如：
如果是 DB2，使用FETCH FIRST 5 ROWS ONLY這樣的關鍵字：
如果是 Oracle，你需要基于 ROWNUM 來統計行數：
需要說明的是，這條語句是先取出來前 5 條資料行，然後再按照 hp_max 從高到低的順序進行排序, 如果這樣寫：
就表示先執行查詢結果，再來過濾結果中的前五條。
WHERE 子句中同時出現 AND 和 OR 操作符的時候，你需要考慮到執行的先後順序，也就是兩個操作符執行的優先級。一般來說 () 優先級最高，其次優先級是 AND，然後是 OR。
MySQL 在 Linux 的環境下，資料庫名、表名、變量名是嚴格區分大小寫的，而字段名是忽略大小寫的。
而 MySQL 在 Windows 的環境下全部不區分大小寫。
SQL編寫的一個規範：
資料庫名、表名、表别名、字段名、字段别名等都小寫
SQL保留字、函數名、綁定變量等都大寫
資料表的字段名推薦采用下劃線命名
SQL語句必須以分号結尾
<code>COUNT(字段)</code>會忽略字段值值為<code>NULL</code>的資料行，而<code>COUNT( * )</code>和<code>COUNT(1)</code>隻是統計資料行數，不管某個字段是否為<code>NULL</code>。
<code>COUNT(*)</code>會因為記錄數量增多而變的查詢很慢，此時可以通過把<code>COUNT(*)</code>的值存在一個單獨的表裡面，并且把插入記錄/删除記錄和更新數量放在同一個事務中，即可準确快速得到<code>COUNT(*)</code>的值。
<code>AVG</code>、<code>MAX</code>、<code>MIN</code>等聚集函數會自動忽略值為<code>NULL</code>的資料行。
對于<code>count(主鍵 id)</code> 來說，InnoDB 引擎會周遊整張表，把每一行的 id 值都取出來，傳回給 server 層。server 層拿到 id 後，判斷是不可能為空的，就按行累加。
對于 <code>count(1)</code> 來說，InnoDB 引擎周遊整張表，但不取值。server 層對于傳回的每一行，放一個數字“1”進去，判斷是不可能為空的，按行累加。單看這兩個用法的差别的話，你能對比出來，<code>count(1)</code> 執行得要比 <code>count(主鍵 id)</code> 快。因為從引擎傳回 id 會涉及到解析資料行，以及拷貝字段值的操作。
對于 <code>count(字段)</code> 來說：如果這個“字段”是定義為 not null 的話，一行行地從記錄裡面讀出這個字段，判斷不能為 null，按行累加；
如果這個“字段”定義允許為 null，那麼執行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。
<code>count(*)</code> 是例外，并不會把全部字段取出來，而是專門做了優化，不取值。<code>count(*)</code> 肯定不是 null，按行累加。
是以結論是：按照效率排序的話，<code>count(字段)</code><<code>count(主鍵 id)</code><<code>count(1)</code>≈<code>count(*)</code>，是以我建議你，盡量使用 count(*)。
可以依據子查詢是否執行多次，進而将子查詢劃分為關聯子查詢和非關聯子查詢。子查詢從資料表中查詢了資料結果，如果這個資料結果隻執行一次，然後這個資料結果作為主查詢的條件進行執行，那麼這樣的子查詢叫做非關聯子查詢。同樣，如果子查詢需要執行多次，即采用循環的方式，先從外部查詢開始，每次都傳入子查詢進行查詢，然後再将結果回報給外部，這種嵌套的執行方式就稱為關聯子查詢
一個非關聯子查詢的例子：
一個關聯子查詢的例子：
實際上在查詢過程中，在我們對 cc 列建立索引的情況下，我們還需要判斷表 A 和表 B 的大小。在這裡例子當中，表 A 指的是 player 表，表 B 指的是 player_score 表。如果表 A 比表 B 大，那麼 IN 子查詢的效率要比 EXIST 子查詢效率高，因為這時 B 表中如果對 cc 列進行了索引，那麼 IN 子查詢的效率就會比較高。同樣，如果表 A 比表 B 小，那麼使用 EXISTS 子查詢效率會更高，因為我們可以使用到 A 表中對 cc 列的索引，而不用從 B 中進行 cc 列的查詢。
當 A 小于 B 時，用 EXISTS。因為 EXISTS 的實作，相當于外表循環，實作的邏輯類似于：
當 B 小于 A 時用 IN，因為實作的邏輯類似于：
哪個表小就用哪個表來驅動，A 表小就用 EXISTS，B 表小就用 IN。
其他一些子查詢的關鍵字：EXISTS、IN、ANY、ALL 和 SOME
一個簡單的函數例子：Leetcode 177. Nth Highest Salary
Employee表資料如下：
函數調用：
結果：
調用
另一個例子
更複雜的一個例子
"張飛"這條記錄，如果資料庫未開啟自動送出，則不會入庫，如果開啟了自動送出，則第二個”張飛“輸入會復原不插入，但是第一條”張飛“資料依然會插入。
MySQL 中 completion_type 這個參數有 3 種可能：
completion=0，這是預設情況。也就是說當我們執行 COMMIT 的時候會送出事務，在執行下一個事務時，還需要我們使用 START TRANSACTION 或者 BEGIN 來開啟。
completion=1，這種情況下，當我們送出事務後，相當于執行了 COMMIT AND CHAIN，也就是開啟一個鍊式事務，即當我們送出事務之後會開啟一個相同隔離級别的事務）。
completion=2，這種情況下 COMMIT=COMMIT AND RELEASE，也就是當我們送出後，會自動與伺服器斷開連接配接。
ACID，是指資料庫管理系統（DBMS）在寫入或更新資料的過程中，為保證事務（transaction）是正确可靠的，所必須具備的四個特性：原子性（atomicity，或稱不可分割性）、一緻性（consistency）、隔離性（isolation，又稱獨立性）、持久性（durability）。
當資料庫上有多個事務同時執行的時候，就可能出現髒讀（dirty read）、不可重複讀（non-repeatable read）、幻讀（phantom read）的問題，為了解決這些問題，就有了隔離級别的概念。
髒讀（Dirty Read）
讀到了其他事務還沒有送出的資料。
不可重複讀（Nnrepeatable Read）
對某資料進行讀取，發現兩次讀取的結果不同，也就是說沒有讀到相同的内容。這是因為有其他事務對這個資料同時進行了修改或删除。
幻讀（Phantom Read）
事務 A 根據條件查詢得到了 N 條資料，但此時事務 B 更改或者增加了 M 條符合事務 A 查詢條件的資料，這樣當事務 A 再次進行查詢的時候發現會有 N+M 條資料，産生了幻讀。
SQL-92 标準還定義了 4 種隔離級别來解決這些異常情況。
這些隔離級别能解決的異常情況如下表所示：
髒讀
不可重複讀
幻讀
讀未送出（READ UNCOMMITTED）
允許
讀已送出（READ COMMITTED）
禁止
可重複讀（REPEATABLE READ）
可串行化（SERIALIZABLE
讀未送出，也就是允許讀到未送出的資料，這種情況下查詢是不會使用鎖的，可能會産生髒讀、不可重複讀、幻讀等情況。
讀已送出，就是隻能讀到已經送出的内容，可以避免髒讀的産生，屬于 RDBMS 中常見的預設隔離級别（比如說 Oracle 和 SQL Server），但如果想要避免不可重複讀或者幻讀，就需要我們在 SQL 查詢的時候編寫帶加鎖的 SQL 語句
可重複讀，保證一個事務在相同查詢條件下兩次查詢得到的資料結果是一緻的，可以避免不可重複讀和髒讀，但無法避免幻讀。MySQL 預設的隔離級别就是可重複讀。
可串行化，将事務進行串行化，也就是在一個隊列中按照順序執行，可串行化是最進階别的隔離等級，可以解決事務讀取中所有可能出現的異常情況，但是它犧牲了系統的并發性。
在實作上，資料庫裡面會建立一個視圖，通路的時候以視圖的邏輯結果為準。
在“可重複讀”隔離級别下，這個視圖是在事務啟動時建立的，整個事務存在期間都用這個視圖。
在“讀送出”隔離級别下，這個視圖是在每個 SQL 語句開始執行的時候建立的。
“讀未送出”隔離級别下直接傳回記錄上的最新值，沒有視圖概念；
“串行化”隔離級别下直接用加鎖的方式來避免并行通路。
我們可以看到在不同的隔離級别下，資料庫行為是有所不同的。Oracle 資料庫的預設隔離級别其實就是“讀送出”，是以對于一些從 Oracle 遷移到 MySQL 的應用，為保證資料庫隔離級别的一緻，你一定要記得将 MySQL 的隔離級别設定為“讀送出”。
什麼時候需要“可重複讀”的場景呢？我們來看一個資料校對邏輯的案例。假設你在管理一個個人銀行賬戶表。一個表存了賬戶餘額，一個表存了賬單明細。到了月底你要做資料校對，也就是判斷上個月的餘額和目前餘額的差額，是否與本月的賬單明細一緻。你一定希望在校對過程中，即使有使用者發生了一筆新的交易，也不影響你的校對結果。這時候使用“可重複讀”隔離級别就很友善。事務啟動時的視圖可以認為是靜态的，不受其他事務更新的影響。
檢視隔離級别
配置隔離級别
為什麼建議盡量不要使用長事務？
長事務意味着系統裡面會存在很老的事務視圖。由于這些事務随時可能通路資料庫裡面的任何資料，是以這個事務送出之前，資料庫裡面它可能用到的復原記錄都必須保留，這就會導緻大量占用存儲空間。在 MySQL 5.5 及以前的版本，復原日志是跟資料字典一起放在 ibdata 檔案裡的，即使長事務最終送出，復原段被清理，檔案也不會變小。
除了對復原段的影響，長事務還占用鎖資源，也可能拖垮整個庫。
在 information_schema 庫的 innodb_trx 這個表中查詢長事務，比如下面這個語句，用于查找持續時間超過 60s 的事務。
MySQL 的事務啟動方式有以下幾種：
顯式啟動事務語句， begin 或 start transaction。配套的送出語句是 commit，復原語句是 rollback。
set autocommit=0，這個指令會将這個線程的自動送出關掉。意味着如果你隻執行一個 select 語句，這個事務就啟動了，而且并不會自動送出。這個事務持續存在直到你主動執行 commit 或 rollback 語句，或者斷開連接配接。
有些用戶端連接配接架構會預設連接配接成功後先執行一個 set autocommit=0 的指令。這就導緻接下來的查詢都在事務中，如果是長連接配接，就導緻了意外的長事務。是以，我會建議你總是使用 set autocommit=1, 通過顯式語句的方式來啟動事務。但是有的開發同學會糾結“多一次互動”的問題。對于一個需要頻繁使用事務的業務，第二種方式每個事務在開始時都不需要主動執行一次 “begin”，減少了語句的互動次數。如果你也有這個顧慮，建議使用 commit work and chain 文法。在 autocommit 為 1 的情況下，用 begin 顯式啟動的事務，如果執行 commit 則送出事務。如果執行 commit work and chain，則是送出事務并自動啟動下一個事務，這樣也省去了再次執行 begin 語句的開銷。同時帶來的好處是從程式開發的角度明确地知道每個語句是否處于事務中。
在 MySQL 中，實際上每條記錄在更新的時候都會同時記錄一條復原操作。記錄上的最新值，通過復原操作，都可以得到前一個狀态的值。假設一個值從 1 被按順序改成了 2、3、4，在復原日志裡面就會有類似下面的記錄。
目前值是 4，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的 read-view。如圖中看到的，在視圖 A、B、C 裡面，這一個記錄的值分别是 1、2、4，同一條記錄在系統中可以存在多個版本，就是資料庫的多版本并發控制（MVCC）。對于 read-view A，要得到 1，就必須将目前值依次執行圖中所有的復原操作得到。同時你會發現，即使現在有另外一個事務正在将 4 改成 5，這個事務跟 read-view A、B、C 對應的事務是不會沖突的。
多版本并發控制技術，是通過資料行的多個版本管理來實作資料庫的并發控制，簡單來說它的思想就是儲存資料的曆史版本。這樣我們就可以通過比較版本号決定資料是否顯示出來，讀取資料的時候不需要加鎖也可以保證事務的隔離效果。
通過 MVCC 我們可以解決以下幾個問題：
讀寫之間阻塞的問題，通過 MVCC 可以讓讀寫互相不阻塞，即讀不阻塞寫，寫不阻塞讀，這樣就可以提升事務并發處理能力。
降低了死鎖的機率。這是因為 MVCC 采用了樂觀鎖的方式，讀取資料時并不需要加鎖，對于寫操作，也隻鎖定必要的行。
解決一緻性讀的問題。一緻性讀也被稱為快照讀，當我們查詢資料庫在某個時間點的快照時，隻能看到這個時間點之前事務送出更新的結果，而不能看到這個時間點之後事務送出的更新結果。
使用了如下内容來實作：
事務版本号
行記錄的隐藏列
Undo Log
InnoDB 中，MVCC 是通過 Undo Log + Read View 進行資料讀取，Undo Log 儲存了曆史快照，而 Read View 規則幫我們判斷目前版本的資料是否可見。需要說明的是，在隔離級别為讀已送出（Read Commit）時，一個事務中的每一次 SELECT 查詢都會擷取一次 Read View。
出現幻讀的原因是在讀已送出的情況下，InnoDB 隻采用記錄鎖（Record Locking）。這裡要介紹下 InnoDB 三種行鎖的方式：記錄鎖：針對單個行記錄添加鎖。間隙鎖（Gap Locking）：可以幫我們鎖住一個範圍（索引之間的空隙），但不包括記錄本身。采用間隙鎖的方式可以防止幻讀情況的産生。Next-Key 鎖：幫我們鎖住一個範圍，同時鎖定記錄本身，相當于間隙鎖 + 記錄鎖，可以解決幻讀的問題。在隔離級别為可重複讀時，InnoDB 會采用 Next-Key 鎖的機制，幫我們解決幻讀問題。
在一天一備的模式裡，最壞情況下需要應用一天的 binlog。比如，你每天 0 點做一次全量備份，而要恢複出一個到昨天晚上 23 點的備份。一周一備最壞情況就要應用一周的 binlog 了。
導圖引用自20丨當我們思考資料庫調優的時候，都有哪些次元可以選擇？
1NF 指的是資料庫表中的任何屬性都是原子性的，不可再分。
2NF 指的資料表裡的非主屬性都要和這個資料表的候選鍵有完全依賴關系。所謂完全依賴不同于部分依賴，也就是不能僅依賴候選鍵的一部分屬性，而必須依賴全部屬性。
一個沒有滿足 2NF 的例子，
一張球員比賽表 player_game，裡面包含球員編号、姓名、年齡、比賽編号、比賽時間和比賽場地等屬性， 這裡候選鍵和主鍵都為:<code>（球員編号，比賽編号），</code>
我們可以通過候選鍵來決定如下的關系：
上面這個關系說明球員編号和比賽編号的組合決定了球員的姓名、年齡、比賽時間、比賽地點和該比賽的得分資料。
但是這個資料表不滿足第二範式，因為資料表中的字段之間還存在着如下的對應關系：
也就是說候選鍵中的某個字段決定了非主屬性。
插入異常：如果我們想要添加一場新的比賽，但是這時還沒有确定參加的球員都有誰，那麼就沒法插入。
删除異常：如果我要删除某個球員編号，如果沒有單獨儲存比賽表的話，就會同時把比賽資訊删除掉。
更新異常：如果我們調整了某個比賽的時間，那麼資料表中所有這個比賽的時間都需要進行調整，否則就會出現一場比賽時間不同的情況。
3NF 在滿足 2NF 的同時，對任何非主屬性都不傳遞依賴于候選鍵。也就是說不能存在非主屬性 A 依賴于非主屬性 B，非主屬性 B 依賴于候選鍵的情況。比如：
你能看到球員編号決定了球隊名稱，同時球隊名稱決定了球隊主教練，非主屬性球隊主教練就會傳遞依賴于球員編号，是以不符合 3NF 的要求。
Hash索引
适用于隻有等值查詢的場景，因為不是有序的，是以做範圍查詢的速度是很慢的。
有序數組
有序數組在等值查詢和範圍查詢場景中的性能就都非常優秀, 有序數組索引隻适用于靜态存儲引擎，因為動态插入資料，會引起插入位置後續的資料移動，成本比較高。
二叉搜尋樹
查詢複雜度是：O(log(N)) ，需要保持這棵樹是平衡二叉樹。為了做這個保證，更新的時間複雜度也是 O(log(N))。樹可以有二叉，也可以有多叉。多叉樹就是每個節點有多個兒子，兒子之間的大小保證從左到右遞增。二叉樹是搜尋效率最高的，但是實際上大多數的資料庫存儲卻并不使用二叉樹。其原因是，索引不止存在記憶體中，還要寫到磁盤上。你可以想象一下一棵 100 萬節點的平衡二叉樹，樹高 20。一次查詢可能需要通路 20 個資料塊。在機械硬碟時代，從磁盤随機讀一個資料塊需要 10 ms 左右的尋址時間。也就是說，對于一個 100 萬行的表，如果使用二叉樹來存儲，單獨通路一個行可能需要 20 個 10 ms 的時間，這個查詢可真夠慢的。為了讓一個查詢盡量少地讀磁盤，就必須讓查詢過程通路盡量少的資料塊。那麼，我們就不應該使用二叉樹，而是要使用“N 叉”樹。這裡，“N 叉”樹中的“N”取決于資料塊的大小。
B樹和B+樹
先看單次查詢。為了盡可能快的命中資料，我們希望盡可能的将更多的索引資料存儲在記憶體中。b樹有一個特點，每一層都會存儲真正的資料，這會擠壓索引可用的記憶體空間，進而在整體上增加io次數。另外，如果隻看等值查詢的話，樹型索引是不如hash索引的。 其次，關系資料庫中還會大量使用範圍查詢、有序查詢等，比如某時間範圍内的使用者交易資料。範圍查詢，這種查詢的特點是會大量使用排序，比較，傳回結果也往往是多條。 如果使用b樹的話，需要使用中序周遊，因為資料節點不在同一層上，會頻繁引起io，進而導緻整體速度下降。而在b+樹中，所有的資料節點都在葉子節點，相近的葉子節點之間也存在着連結，是以會節約io時間。這樣，b+樹整體上就比b樹要快。 其實，b+樹主要應用于關系型資料庫中。也有使用b樹做索引的資料庫，比如mangodb。 MySQL 的 InnoDB 存儲引擎還有個“自适應 Hash 索引”的功能，就是當某個索引值使用非常頻繁的時候，它會在 B+ 樹索引的基礎上再建立一個 Hash 索引，這樣讓 B+ 樹也具備了 Hash 索引的優點
功能上分：普通索引，唯一索引，主鍵索引，全文索引
主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為聚簇索引（clustered index）。非主鍵索引的葉子節點内容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。
什麼是回表，什麼是覆寫索引？(ID是主鍵索引，k是普通索引)
如果語句是 select * from T where ID=500，即主鍵查詢方式，則隻需要搜尋 ID 這棵 B+ 樹；
如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜尋 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜尋一次。這個過程稱為回表。
如果執行的語句是 select ID from T where k between 3 and 5，這時隻需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，是以可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡面，索引 k 已經“覆寫了”我們的查詢需求，我們稱為覆寫索引。
身份證号是市民的唯一辨別。也就是說，如果有根據身份證号查詢市民資訊的需求，我們隻要在身份證号字段上建立索引就夠了。而再建立一個（身份證号、姓名）的聯合索引，是不是浪費空間？如果現在有一個高頻請求，要根據市民的身份證号查詢他的姓名，這個聯合索引就有意義了。它可以在這個高頻請求上用到覆寫索引，不再需要回表查整行記錄，減少語句的執行時間。
實體結構上：聚集索引(順序)和非聚集索引(非順序)，可以類比連結清單和數組的差別。
字段上分：單一索引和聯合索引（最左比對原則）
聚集索引的葉子節點存儲的就是我們的資料記錄，非聚集索引的葉子節點存儲的是資料位置。非聚集索引不會影響資料表的實體存儲順序。
一個表隻能有一個聚集索引，因為隻能有一種排序存儲的方式，但可以有多個非聚集索引，也就是多個索引目錄提供資料檢索。
使用聚集索引的時候，資料的查詢效率高，但如果對資料進行插入，删除，更新等操作，效率會比非聚集索引低。
什麼時候建立索引，什麼時候不應該建立索引？
建立索引
字段唯一
WHERE頻繁查詢
經常GROUP BY或者ORDER BY的列
DISTINCT字段
不适合的情況
頻繁更新的字段
重複資料比較多的字段
WHERE用不到的字段
InnoDB 表 T，如果你要重建索引 k，你的兩個 SQL 語句可以這麼寫：
如果你要重建主鍵索引，也可以這麼寫：
其中，重建索引 k 的做法是合理的，可以達到省空間的目的。但是，重建主鍵的過程不合理。不論是删除主鍵還是建立主鍵，都會将整個表重建。是以連着執行這兩個語句的話，第一個語句就白做了。這兩個語句，你可以用這個語句代替 ： alter table T engine=InnoDB。
分析一下哪些場景下應該使用自增主鍵，而哪些場景下不應該？
自增主鍵每次插入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。
而有業務邏輯的字段做主鍵，則往往不容易保證有序插入，這樣寫資料成本相對較高。
除了考慮性能外，還可以從存儲空間的角度來看。
假設你的表中确實有一個唯一字段，比如字元串類型的身份證号，那應該用身份證号做主鍵，還是用自增字段做主鍵呢？
由于每個非主鍵索引的葉子節點上都是主鍵的值。如果用身份證号做主鍵，那麼每個二級索引的葉子節點占用約 20 個位元組，而如果用整型做主鍵，則隻要 4 個位元組，如果是長整型（bigint）則是 8 個位元組。顯然，主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小。
是以，從性能和存儲空間方面考量，自增主鍵往往是更合理的選擇。
有沒有什麼場景适合用業務字段直接做主鍵的呢？
比如典型的 KV 場景。由于沒有其他索引，是以也就不用考慮其他索引的葉子節點大小的問題。這時候我們就要優先考慮上一段提到的“盡量使用主鍵查詢”原則，直接将這個索引設定為主鍵，可以避免每次查詢需要搜尋兩棵樹。
最左字首原則。
基于以上原則，在建立聯合索引的時候，如何安排索引内的字段順序？
因為可以支援最左字首，是以當已經有了 (a,b) 這個聯合索引後，一般就不需要單獨在 a 上建立索引了。是以，第一原則是，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮采用的。那麼，如果既有聯合查詢，又有基于 a、b 各自的查詢呢？查詢條件裡面隻有 b 的語句，是無法使用 (a,b) 這個聯合索引的，這時候你不得不維護另外一個索引，也就是說你需要同時維護 (a,b)、(b) 這兩個索引。這時候，我們要考慮的原則就是空間了。比如上面這個市民表的情況，name 字段是比 age 字段大的 ，那我就建議你建立一個（name,age) 的聯合索引和一個 (age) 的單字段索引。
索引下推原則
以市民表的聯合索引（name, age）為例。如果現在有一個需求：檢索出表中“名字第一個字是張，而且年齡是 10 歲的所有男孩”。那麼，SQL 語句是這麼寫的：
在 MySQL 5.6 之前，隻能從 ID3 開始一個個回表。到主鍵索引上找出資料行，再對比字段值。
而 MySQL 5.6 引入的索引下推優化（index condition pushdown)， 可以在索引周遊過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。
在圖 3 和 4 這兩個圖裡面，每一個虛線箭頭表示回表一次。圖 3 中，在 (name,age) 索引裡面我特意去掉了 age 的值，這個過程 InnoDB 并不會去看 age 的值，隻是按順序把“name 第一個字是’張’”的記錄一條條取出來回表。是以，需要回表 4 次。圖 4 跟圖 3 的差別是，InnoDB 在 (name,age) 索引内部就判斷了 age 是否等于 10，對于不等于 10 的記錄，直接判斷并跳過。在我們的這個例子中，隻需要對 ID4、ID5 這兩條記錄回表取資料判斷，就隻需要回表 2 次。
什麼時候索引失效
索引字段使用了表達式
使用函數
在 WHERE 子句中，如果在 OR 前的條件列進行了索引，而在 OR 後的條件列沒有進行索引，那麼索引會失效。
當我們使用 LIKE 進行模糊查詢的時候，前面不能是 %
最左原則
示例題：
假設原表如上，索引('c','a')和索引('c','b')是否都需要？
假設表資料如下：
按(<code>a</code>,<code>b</code>)排序
按(<code>c</code>)排序
按(<code>c</code>,<code>a</code>)排序
按(<code>c</code>,<code>b</code>)排序
按c排序和按ca排序是一緻的，是以(<code>c</code>,<code>a</code>)索引可以省略。
涉及change buffer
參考如下：https://time.geekbang.org/column/article/70848
使用字首索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。比如：email字段中可以使用前面若幹個字元作為字首索引。
實際上，我們在建立索引時關注的是區分度，區分度越高越好。因為區分度越高，意味着重複的鍵值越少。是以，我們可以通過統計索引上有多少個不同的值來判斷要使用多長的字首。首先，你可以使用下面這個語句，算出這個列上有多少個不同的值：
然後，依次選取不同長度的字首來看這個值，比如我們要看一下 4~7 個位元組的字首索引，可以用這個語句：
當然，使用字首索引很可能會損失區分度，是以你需要預先設定一個可以接受的損失比例，比如 5%。然後，在傳回的 L4~L7 中，找出不小于 L * 95% 的值，假設這裡 L6、L7 都滿足，你就可以選擇字首長度為 6。
使用字首索引就用不上覆寫索引對查詢性能的優化了，這也是你在選擇是否使用字首索引時需要考慮的一個因素。比如：
如果使用email完整字元串作為索引，就不需要回表，如果使用字首索引，需要回表确認id然後再比對email
其他方式：
hash方式
倒序方式
對索引字段做函數操作，可能會破壞索引值的有序性，是以優化器就決定放棄走樹搜尋功能。
例1,注：其中的t_modified是datetime類型，在t_modified中加上了索引，但是如下SQL
還是會走全表掃描。
正确方式應該為：
例2,注：其中的tradeid設定了索引，且類型為varchar(32)
其中tradeid設定了索引，但是如上語句還是走了全表掃描，因為tradeid是varchar類型，但是輸入參數卻是整數（字元串和數字做比較的話，是将字元串轉換成數字），MySQL會做類型轉換，并放棄走索引。
例3，注：其中trade_detail表是utf8字元集，而trade_detail是utf8mb4
在tradelog中找到id等于2的字段後，再去trade_detail中比對的行時候，走了全表掃描，因為MySQL做了如下執行：
即：将trade_detail字段中的traideid做了編碼轉換。
如果要使用索引，可以參考如下做法：
在這個例子中，MySQL會執行如下操作
這裡的 CONVERT 函數是加在輸入參數上的，這樣就可以用上被驅動表的 traideid 索引。
是以，例3中的SQL語句，可以有如下兩種優化方式：
将兩個表的編碼集統一，utf8->utf8mb4
将語句改成：
資料庫管理存儲空間的基本機關是頁（Page），一頁中可以存儲多行記錄, InnoDB中頁大小檢視
我們在配置設定空間的時候會按照頁為機關來進行配置設定，同一棵樹上同一層的頁與頁之間采用雙向連結清單，而在頁裡面，記錄之間采用的單向連結清單的方式。
區（Extent）是比頁大一級的存儲結構，InnoDB 中，頁大小預設是 16KB, 一個區會配置設定 64 個連續的頁。是以一個區的大小是:
<code>64*16KB=1MB。</code>
段（Segment）由一個或多個區組成，不過在段中不要求區與區之間是相鄰的。段是資料庫中的配置設定機關，不同類型的資料庫對象以不同的段形式存在。當我們建立資料表、索引的時候，就會相應建立對應的段，比如建立一張表時會建立一個表段，建立一個索引時會建立一個索引段。
表空間（Tablespace）是一個邏輯容器，表空間存儲的對象是段，在一個表空間中可以有一個或多個段，但是一個段隻能屬于一個表空間。資料庫由一個或多個表空間組成，表空間從管理上可以劃分為系統表空間、使用者表空間、撤銷表空間、臨時表空間等。在 InnoDB 中存在兩種表空間的類型：
共享表空間和獨立表空間。
如果是共享表空間就意味着多張表共用一個表空間。
如果是獨立表空間，就意味着每張表有一個獨立的表空間，也就是資料和索引資訊都會儲存在自己的表空間中。獨立的表空間可以在不同的資料庫之間進行遷移。
在 WHERE 條件語句中，找到所有等值謂詞中的條件列，将它們作為索引片中的開始列；
将 GROUP BY 和 ORDER BY 中的列加入到索引中；
将 SELECT 字段中剩餘的列加入到索引片中。
按鎖粒度劃分
行鎖
表鎖（表鎖，MySQL 5.5引入了中繼資料鎖）
全局鎖
不同的資料庫和存儲引擎支援的鎖粒度不同，InnoDB 和 Oracle 支援行鎖和表鎖。而 MyISAM 隻支援表鎖，MySQL 中的 BDB 存儲引擎支援頁鎖和表鎖。SQL Server 可以同時支援行鎖、頁鎖和表鎖。
全局鎖的典型使用場景是，做全庫的邏輯備份。官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用參數–single-transaction 的時候，導資料之前就會啟動一個事務，來確定拿到一緻性視圖。而由于 MVCC 的支援，這個過程中資料是可以正常更新的。single-transaction 方法隻适用于所有的表使用事務引擎的庫。如果有的表使用了不支援事務的引擎，那麼備份就隻能通過 FTWRL 方法。這往往是 DBA 要求業務開發人員使用 InnoDB 替代 MyISAM 的原因之一。
為什麼不使用 set global readonly=true 的方式呢？主要有兩個原因：
一是，在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。是以，修改 global 變量的方式影響面更大，不建議使用。
二是，在異常處理機制上有差異。如果執行 FTWRL 指令之後由于用戶端發生異常斷開，那麼 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀态。而将整個庫設定為 readonly 之後，如果用戶端發生異常，則資料庫就會一直保持 readonly 狀态，這樣會導緻整個庫長時間處于不可寫狀态，風險較高。
事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後并不會馬上釋放，而會等到整個事務送出後再釋放。
那麼如何安全地給小表加字段？
首先我們要解決長事務，事務不送出，就會一直占着 MDL 鎖。在 MySQL 的 information_schema 庫的 innodb_trx 表中，你可以查到目前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。但考慮一下這個場景。如果你要變更的表是一個熱點表，雖然資料量不大，但是上面的請求很頻繁，而你不得不加個字段，你該怎麼做呢？這時候 kill 可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在 alter table 語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。之後開發人員或者 DBA 再通過重試指令重複這個過程。MariaDB 已經合并了 AliSQL 的這個功能，是以這兩個開源分支目前都支援 DDL NOWAIT/WAIT n 這個文法。
我們還可以從資料庫管理的角度對鎖進行劃分。共享鎖和排它鎖
加共享鎖：
解鎖：
加排他鎖
解鎖
意向鎖（Intent Lock），簡單來說就是給更大一級别的空間示意裡面是否已經上過鎖。
如果事務想要獲得資料表中某些記錄的共享鎖，就需要在資料表上添加意向共享鎖。同理，事務想要獲得資料表中某些記錄的排他鎖，就需要在資料表上添加意向排他鎖。這時，意向鎖會告訴其他事務已經有人鎖定了表中的某些記錄，不能對整個表進行全表掃描。
從程式員角度劃分
樂觀鎖：通過版本号或者時間戳來控制
悲觀鎖：對資料被其他事務的修改持保守态度，會通過資料庫自身的鎖機制來實作，進而保證資料操作的排它性。
如果事務涉及多個表，操作比較複雜，那麼可以盡量一次鎖定所有的資源，而不是逐漸來擷取，這樣可以減少死鎖發生的機率；
如果事務需要更新資料表中的大部分資料，資料表又比較大，這時可以采用鎖更新的方式，比如将行級鎖更新為表級鎖，進而減少死鎖産生的機率；
不同僚務并發讀寫多張資料表，可以約定通路表的順序，采用相同的順序降低死鎖發生的機率。
當然在資料庫中，也有一些情況是不會發生死鎖的，比如采用樂觀鎖的方式。另外在 MySQL MyISAM 存儲引擎中也不會出現死鎖，這是因為 MyISAM 總是一次性獲得全部的鎖，這樣的話要麼全部滿足可以執行，要麼就需要全部等待。
使用 MySQL InnoDB 存儲引擎時，為什麼對某行資料添加排它鎖之前，會在資料表上添加意向排他鎖呢？
因為要告訴其他人這個資料頁或資料表已經有人上過排它鎖了，這樣當其他人想要擷取資料表排它鎖的時候，隻需要了解是否有人已經擷取了這個資料表的意向排他鎖即可。而不需要進行全表的掃描，節省時間，提高效率！
在 InnoDB 事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協定。知道了這個設定，對我們使用事務有什麼幫助呢？那就是，如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發度的鎖盡量往後放。我給你舉個例子。假設你負責實作一個電影票線上交易業務，顧客 A 要在影院 B 購買電影票。我們簡化一點，這個業務需要涉及到以下操作：
從顧客 A 賬戶餘額中扣除電影票價；
給影院 B 的賬戶餘額增加這張電影票價；
記錄一條交易日志。
也就是說，要完成這個交易，我們需要 update 兩條記錄，并 insert 一條記錄。當然，為了保證交易的原子性，我們要把這三個操作放在一個事務中。那麼，你會怎樣安排這三個語句在事務中的順序呢？試想如果同時有另外一個顧客 C 要在影院 B 買票，那麼這兩個事務沖突的部分就是語句 2 了。因為它們要更新同一個影院賬戶的餘額，需要修改同一行資料。根據兩階段鎖協定，不論你怎樣安排語句順序，所有的操作需要的行鎖都是在事務送出的時候才釋放的。是以，如果你把語句 2 安排在最後，比如按照 3、1、2 這樣的順序，那麼影院賬戶餘額這一行的鎖時間就最少。這就最大程度地減少了事務之間的鎖等待，提升了并發度。好了，現在由于你的正确設計，影院餘額這一行的行鎖在一個事務中不會停留很長時間。但是，這并沒有完全解決你的困擾。如果這個影院做活動，可以低價預售一年内所有的電影票，而且這個活動隻做一天。于是在活動時間開始的時候，你的 MySQL 就挂了。你登上伺服器一看，CPU 消耗接近 100%，但整個資料庫每秒就執行不到 100 個事務。這是什麼原因呢？這裡，我就要說到死鎖和死鎖檢測了。
當并發系統中不同線程出現循環資源依賴，涉及的線程都在等待别的線程釋放資源時，就會導緻這幾個線程都進入無限等待的狀态，稱為死鎖。
這裡我用資料庫中的行鎖舉個例子。
這時候，事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。
事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀态。
當出現死鎖以後，有兩種政策：
一種政策是，直接進入等待，直到逾時。這個逾時時間可以通過參數 innodb_lock_wait_timeout 來設定。
另一種政策是，發起死鎖檢測，發現死鎖後，主動復原死鎖鍊條中的某一個事務，讓其他事務得以繼續執行。将參數 innodb_deadlock_detect 設定為 on，表示開啟這個邏輯。在 InnoDB 中，innodb_lock_wait_timeout 的預設值是 50s，意味着如果采用第一個政策，當出現死鎖以後，第一個被鎖住的線程要過 50s 才會逾時退出，然後其他線程才有可能繼續執行。
對于線上服務來說，這個等待時間往往是無法接受的。但是，我們又不可能直接把這個時間設定成一個很小的值，比如 1s。這樣當出現死鎖的時候，确實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？是以，逾時時間設定太短的話，會出現很多誤傷。
是以，正常情況下我們還是要采用第二種政策，即：主動死鎖檢測，而且 innodb_deadlock_detect 的預設值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現并進行處理的，但是它也是有額外負擔的。你可以想象一下這個過程：每當一個事務被鎖的時候，就要看看它所依賴的線程有沒有被别人鎖住，如此循環，最後判斷是否出現了循環等待，也就是死鎖。那如果是我們上面說到的所有事務都要更新同一行的場景呢？每個新來的被堵住的線程，都要判斷會不會由于自己的加入導緻了死鎖，這是一個時間複雜度是 O(n) 的操作。假設有 1000 個并發線程要同時更新同一行，那麼死鎖檢測操作就是 100 萬這個量級的。雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的 CPU 資源。是以，你就會看到 CPU 使用率很高，但是每秒卻執行不了幾個事務。根據上面的分析，我們來讨論一下，怎麼解決由這種熱點行更新導緻的性能問題呢？問題的症結在于，死鎖檢測要耗費大量的 CPU 資源。
一種方案是：
如果你能確定這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉。但是這種操作本身帶有一定的風險，因為業務設計的時候一般不會把死鎖當做一個嚴重錯誤，畢竟出現死鎖了，就復原，然後通過業務重試一般就沒問題了，這是業務無損的。而關掉死鎖檢測意味着可能會出現大量的逾時，這是業務有損的。另一個思路是控制并發度。根據上面的分析，你會發現如果并發能夠控制住，比如同一行同時最多隻有 10 個線程在更新，那麼死鎖檢測的成本很低，就不會出現這個問題。一個直接的想法就是，在用戶端做并發控制。但是，你會很快發現這個方法不太可行，因為用戶端很多。我見過一個應用，有 600 個用戶端，這樣即使每個用戶端控制到隻有 5 個并發線程，彙總到資料庫服務端以後，峰值并發數也可能要達到 3000。是以，這個并發控制要做在資料庫服務端。如果你有中間件，可以考慮在中間件實作；如果你的團隊有能修改 MySQL 源碼的人，也可以做在 MySQL 裡面。基本思路就是，對于相同行的更新，在進入引擎之前排隊。這樣在 InnoDB 内部就不會有大量的死鎖檢測工作了。
第一種方案是：
你可以考慮通過将一行改成邏輯上的多行來減少鎖沖突。還是以影院賬戶為例，可以考慮放在多條記錄上，比如 10 個記錄，影院的賬戶總額等于這 10 個記錄的值的總和。這樣每次要給影院賬戶加金額的時候，随機選其中一條記錄來加。這樣每次沖突機率變成原來的 1/10，可以減少鎖等待個數，也就減少了死鎖檢測的 CPU 消耗。這個方案看上去是無損的，但其實這類方案需要根據業務邏輯做詳細設計。如果賬戶餘額可能會減少，比如退票邏輯，那麼這時候就需要考慮當一部分行記錄變成 0 的時候，代碼要有特殊處理。
MySQL 5.6 版本引入的一個新的排序算法，即：優先隊列排序算法。用來替換臨時檔案的算法。詳見：17 | 如何正确地顯示随機消息？
如下SQL 查詢語句：
也用到了 limit，為什麼沒用優先隊列排序算法呢？
原因是，這條 SQL 語句是 limit 1000，如果使用優先隊列算法的話，需要維護的堆的大小就是 1000 行的 (name,rowid)，超過了我設定的 sort_buffer_size 大小，是以隻能使用歸并排序算法。
方法1
方法2
如果你要删除一個表裡面的前 10000 行資料，有以下三種方法可以做到：
第一種，直接執行 delete from T limit 10000;
事務相對較長，則占用鎖的時間較長，會導緻其他用戶端等待資源時間較長。
第二種，在一個連接配接中循環執行 20 次 delete from T limit 500;
串行化執行，将相對長的事務分成多次相對短的事務，則每次事務占用鎖的時間相對較短，其他用戶端在等待相應資源的時間也較短。這樣的操作，同時也意味着将資源分片使用（每次執行使用不同片段的資源），可以提高并發性。
第三種，在 20 個連接配接中同時執行 delete from T limit 500。
人為自己制造鎖競争，加劇并發量。
第一種是基于規則的優化器（RBO，Rule-Based Optimizer），規則就是人們以往的經驗，或者是采用已經被證明是有效的方式。通過在優化器裡面嵌入規則，來判斷 SQL 查詢符合哪種規則，就按照相應的規則來制定執行計劃，同時采用啟發式規則去掉明顯不好的存取路徑。
第二種是基于代價的優化器（CBO，Cost-Based Optimizer），這裡會根據代價評估模型，計算每條可能的執行計劃的代價，也就是 COST，從中選擇代價最小的作為執行計劃。相比于 RBO 來說，CBO 對資料更敏感，因為它會利用資料表中的統計資訊來做判斷，針對不同的資料表，查詢得到的執行計劃可能是不同的，是以制定出來的執行計劃也更符合資料表的實際情況。
但我們需要記住，SQL 是面向集合的語言，并沒有指定執行的方式，是以在優化器中會存在各種組合的可能。我們需要通過優化器來制定資料表的掃描方式、連接配接方式以及連接配接順序，進而得到最佳的 SQL 執行計劃。
你能看出來，RBO 的方式更像是一個計程車老司機，憑借自己的經驗來選擇從 A 到 B 的路徑。而 CBO 更像是手機導航，通過資料驅動，來選擇最佳的執行路徑。
5.7.10 版本之後，MySQL 會引入兩張資料表，裡面規定了各種步驟預估的代價（Cost Value） ，我們可以從mysql.server_cost和mysql.engine_cost這兩張表中獲得這些步驟的代價
mysqldumpslow
EXPLAIN 檢視執行計劃
SHOW PROFILE 檢視開銷
提到主從同步的原理，我們就需要了解在資料庫中的一個重要日志檔案，那就是 Binlog 二進制日志，它記錄了對資料庫進行更新的事件。實際上主從同步的原理就是基于 Binlog 進行資料同步的。
在主從複制過程中，會基于 3 個線程來操作，一個主庫線程，兩個從庫線程。二進制日志轉儲線程（Binlog dump thread）是一個主庫線程。當從庫線程連接配接的時候，主庫可以将二進制日志發送給從庫，當主庫讀取事件的時候，會在 Binlog 上加鎖，讀取完成之後，再将鎖釋放掉。從庫 I/O 線程會連接配接到主庫，向主庫發送請求更新 Binlog。這時從庫的 I/O 線程就可以讀取到主庫的二進制日志轉儲線程發送的 Binlog 更新部分，并且拷貝到本地形成中繼日志（Relay log）。從庫 SQL 線程會讀取從庫中的中繼日志，并且執行日志中的事件，進而将從庫中的資料與主庫保持同步。
異步複制
半同步複制
MySQL5.5 版本之後開始支援半同步複制的方式。原理是在用戶端送出 COMMIT 之後不直接将結果傳回給用戶端，而是等待至少有一個從庫接收到了 Binlog，并且寫入到中繼日志中，再傳回給用戶端。這樣做的好處就是提高了資料的一緻性，當然相比于異步複制來說，至少多增加了一個網絡連接配接的延遲，降低了主庫寫的效率。在 MySQL5.7 版本中還增加了一個rpl_semi_sync_master_wait_for_slave_count參數，我們可以對應答的從庫數量進行設定，預設為 1，也就是說隻要有 1 個從庫進行了響應，就可以傳回給用戶端。如果将這個參數調大，可以提升資料一緻性的強度，但也會增加主庫等待從庫響應的時間。
組複制（MySQL 5.7.17 以後 ，基于Paxos）
方式1：
innodb_force_recovery
方式2：
Linux下MySQL資料庫的備份與恢複
為什麼我的MySQL會“抖”一下
參數 innodb_file_per_table
這個參數設定為 OFF 表示的是，表的資料放在系統共享表空間，也就是跟資料字典放在一起；
這個參數設定為 ON 表示的是，每個 InnoDB 表資料存儲在一個以 .ibd 為字尾的檔案中。
建議配置為ON。
删除資料和插入資料都會造成資料空洞（空洞被辨別為可複用，但是空間不變）
你可以使用 alter table A engine=InnoDB 指令來重建表。在 MySQL 5.5 版本之前，如果在這個過程中，有新的資料要寫入到表 A 的話，就會造成資料丢失。是以，在整個 DDL 過程中，表 A 中不能有更新。也就是說，這個 DDL 不是 Online 的。
而在 MySQL 5.6 版本開始引入的 Online DDL，對這個操作流程做了優化。
建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；
用資料頁中表 A 的記錄生成 B+ 樹，存儲到臨時檔案中；生成臨時檔案的過程中，将所有對 A 的操作記錄在一個日志檔案（row log）中；
臨時檔案生成後，将日志檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案；用臨時檔案替換表 A 的資料檔案。
更多
13 | 為什麼表資料删掉一半，表檔案大小不變？
工具：Github-gh-ost
通常情況下，我們建議把 innodb_thread_concurrency 設定為 64~128 之間的值。這時，你一定會有疑問，并發線程上限數設定為 128 夠幹啥，線上的并發連接配接數動不動就上千了。産生這個疑問的原因，是搞混了并發連接配接和并發查詢。并發連接配接和并發查詢，并不是同一個概念。你在 show processlist 的結果裡，看到的幾千個連接配接，指的就是并發連接配接。而“目前正在執行”的語句，才是我們所說的并發查詢。并發連接配接數達到幾千個影響并不大，就是多占一些記憶體而已。我們應該關注的是并發查詢，因為并發查詢太高才是 CPU 殺手。這也是為什麼我們需要設定 innodb_thread_concurrency 參數的原因。
如果把 innodb_thread_concurrency 設定為 128 的話，那麼出現同一行熱點更新的問題時，是不是很快就把 128 消耗完了，這樣整個系統是不是就挂了呢？實際上，線上程進入鎖等待以後，并發線程的計數會減一，也就是說等行鎖（也包括間隙鎖）的線程是不算在 128 裡面的。MySQL 這樣設計是非常有意義的。因為，進入鎖等待的線程已經不吃 CPU 了；更重要的是，必須這麼設計，才能避免整個系統鎖死。為什麼呢？假設處于鎖等待的線程也占并發線程的計數，你可以設想一下這個場景：線程 1 執行 begin; update t set c=c+1 where id=1, 啟動了事務 trx1， 然後保持這個狀态。這時候，線程處于空閑狀态，不算在并發線程裡面。線程 2 到線程 129 都執行 update t set c=c+1 where id=1; 由于等行鎖，進入等待狀态。這樣就有 128 個線程處于等待狀态；如果處于鎖等待狀态的線程計數不減一，InnoDB 就會認為線程數用滿了，會阻止其他語句進入引擎執行，這樣線程 1 不能送出事務。而另外的 128 個線程又處于鎖等待狀态，整個系統就堵住了。
MySQL實戰45講
SQL必知必會
MySQL 面試題閱讀指南