資料庫

1、MyISAM和InnoDB的區別
1、MyISAM不支援行級鎖，只支援表級鎖。InnoDB支援行級鎖和表級鎖
2、是否支援事務和奔潰後的安全恢復：MyiSAM是原子讀的操作，速度快。Innode支援事務，外部鍵等高階功能。InnoDB支援事務具有事務、回滾奔潰後安全恢復能力
3、是否支援外來鍵：MyISAM不支援外來鍵，Innode支援外來鍵

字元集及校對規則

字元集：指從二進位制編碼到某類字元符號的對映
校對規則：指的是字元的排序規則

索引

MySQL索引參照的資料結構主要有BTree索引和雜湊索引。對於雜湊索引來說，底層的資料結構就是雜湊表，因此在絕大多數數需求為單條記錄查詢的時候，可以選擇雜湊索引，查詢效能較快；其餘大部分場景，建議選擇BTree索引

B樹與B+樹常用於資料庫和作業系統的檔案系統中。NTFS、ReiserFS等檔案系統都在使用B+樹作為後設資料索引。B+樹的特點是能夠保持資料穩定有序，其插入與修改擁有較穩定的對數時間複雜度。

MySQL的BTree索引使用的是B樹中的B+Tree。但是對於主要的兩種儲存引擎的實現方式是不同的；

• MyISAM: B+Tree葉節點的data域存放的資料記錄的地址。在索引檢索的時候，首先按照B+Tree搜尋演演算法搜尋索引，如果指定的key存在，則取出data域的值，然後以data域的值為地址讀取相應的資料記錄。這被稱為「非聚簇索引」
• InnoDB：其資料檔案本身就是索引檔案。相比MyISAM，索引檔案和資料檔案是分離的。其表資料本身就是按照B+Tree組織的一個索引結構，樹的葉節點data域儲存了完整的資料記錄。這被稱為「聚簇索引」，而其餘索引都作為輔助索引，輔助索引的data域儲存相應記錄主鍵的值而不是地址，這也是和MyISAM不同的地方。在根據主索引搜尋時，直接找到key所在的節點即可取出資料；在根據輔助索引查詢時，則需要先取出主鍵的值，再走一遍主索引。因此，在設計表的時候，不需要使用過長的欄位作為主鍵，也不建議使用非單調的欄位作為主鍵，這樣會造成主索引頻繁分裂。

聚簇索引
InnoDB的表都擁有一個索引，稱之為聚簇索引，此索引中儲存著行記錄，一般來說，聚簇索引是根據主鍵生成的。為了能夠獲得高效能的查詢、插入和其他資料庫操作，理解InnoDB聚簇索引是根據主鍵生成的。

聚簇索引整體是一個b+樹，非葉子節點存放的是鍵值，葉子節點存放的是行資料，稱之為資料頁，這就決定了表中資料也是聚簇索引中的一部分，資料頁通過雙向連結串列來連結的。

輔助索引
除了聚簇索引之外的索引可以稱之為輔助索引，與聚簇索引的區別在於輔助索引的葉子節點中存放的是主鍵的鍵值。

MySQL可分為server層和引擎層

server層

聯結器
負責與使用者端建立連結、獲取許可權、維持和管理連線
長連線：連線成功後，如果使用者端持續有請求，則一直使用同一個連線
短連線：每次執行完很少的幾次查詢就斷開連線，下次查詢再連線一個

長連線佔用大記憶體，可用的方法：①定期斷開長連線   ②執行大操作後重新初始化

查詢快取
查詢：之前執行過的語句和其結果都會以key-value的形式，被直接存在記憶體中。
不實用，因為資料庫更新快，快取內也要頻繁刪除
更新：刪除查詢快取

分析器
語句解析。解析SQL語句的正確性和操作

優化器
在表內有多個索引的時候，決定要實用哪個索引；或者一個語句有多表關聯的時候，決定各個表的連線順序。
選擇方案

執行器
查詢：執行順序。①查詢許可權 ②呼叫儲存引擎 ③遍歷表選擇所有滿足條件的作為結果集返回使用者端
更新：

儲存引擎

資料的儲存和提取，架構模式是外掛式的，支援InnoDB、MyISAM、Memery等多個儲存引擎。

紀錄檔系統

如果每一次更新都寫入磁碟，然後磁碟也要找到對應的那條記錄，然後更新，整個IO成本，查詢成本都很高。
WAL技術：Write-ahead loggin：它的關鍵點就是先寫紀錄檔，再寫磁碟。

InnoDB 的 redo log 是固定大小的，比如可以設定為一組 4 個檔案，每個文
件的大小是 1GB，那麼這塊「粉板」總共就可以記錄 4GB 的操作。從頭開始寫，寫到末
尾就又回到開頭回圈寫，如下面這個圖所示。

重要的紀錄檔模組
為什麼會有兩份紀錄檔呢？
因為最開始 MySQL 裡並沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是
MyISAM 沒有 crash-safe 的能力，binlog 紀錄檔只能用於歸檔。而 InnoDB 是另一個公司
以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以
InnoDB 使用另外一套紀錄檔系統——也就是 redo log 來實現 crash-safe 能力。

這兩種紀錄檔有以下三點不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎
   都可以使用。
2. redo log 是物理紀錄檔，記錄的是「在某個資料頁上做了什麼修改」；binlog 是邏輯日
   志，記錄的是這個語句的原始邏輯，比如「給 ID=2 這一行的 c 欄位加 1 」。
3. redo log 是迴圈寫的，空間固定會用完；binlog 是可以追加寫入的。「追加寫」是指
   binlog 檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的紀錄檔。

redolog和binlog的二階段提交：

事務隔離

事務的特性：ACID
原子性：一個事務要麼內部的操作全部成功，要麼全部失敗
一致性：事務執行前後，需要從一個一致性狀態轉向另一個一致性狀態。
隔離性：一個事務對於另一個事物的執行沒有干擾，互相隔離。
永續性：只要事務進行了提交，及時還沒有提交到磁碟，也能夠進行恢復。

多事務執行的時候可能會出現：髒讀、不可重複讀、幻讀的問題

SQL 標準的事務隔離級別包括：讀未提交（readuncommitted）、讀提交（read committed）、可重複讀（repeatable read）和序列化（serializable ）。
讀未提交：一個事務未提交的更改可以被另一個事務讀取
讀已提交：事務只能讀取另一個事務已提交的結果。解決髒讀
可重複讀：事務執行期間讀取到的資料和執行開始讀到的資料是一樣的，解決不可重複讀
序列化：使用鎖機制，在我這個事務對這個資料沒處理完之前，別的事務都不能用。解決所有的問題

事務隔離的實現—可重複讀
每條記錄在更新的時候都會同時記錄一條回滾。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。

當前值是4，但是不同時刻啟動的事務會有不同的read_view。如上檢視A\B\C記錄的是1,2，4；
每條記錄在更新的時候都會同時記錄一條回滾操作。同一條記錄在系統中可以存在多個版本，就是資料庫的多版本並行控制（MVCC）

回滾紀錄檔什麼時候刪除？
再不需要的時候才刪除回滾紀錄檔，系統會判斷，當沒有事務再需要這個回滾紀錄檔的時候，回滾紀錄檔就會被刪除。

什麼時候不需要了呢？
當系統裡沒有比這個回滾紀錄檔更早的read-view的時候

不建議使用長事務？
長事務意味著系統裡面會存在很老的事務檢視。由於這些事務隨時可能存取資料庫裡面的
任何資料，所以這個事務提交之前，資料庫裡面它可能用到的回滾記錄都必須保留，這就
會導致大量佔用儲存空間

事務的啟動方式
1、顯示啟動，begin或者start transaction。配套語句是commit，回滾語句是rollback
2、set autocommit=0 這個命令會將執行緒的自動提交關閉，意味著如果你只執行一個select語句，這個事務就啟動，但是會持續到你主動執行commit或者rollback語句，或者斷開連線

因此，我會建議你總是使用 set autocommit=1, 通過顯式語句的方式來啟動事務

使用commit work and chain來解決顯示啟動需要多一句begin的互動，即在提交事務的時候啟動下一個事務。

事務的設定方式 ： transaction-isolation

如何避免長事務對業務的影響？
這個問題，我們可以用應用開發端和資料庫端來看。
首先從應用開發端來看：
1. 確認是否使用了set autocommit=0。這個確認工作可以在測試環境中開展，把
MySQL 的 general_log 開起來，然後隨便跑一個業務邏輯，通過 general_log 的紀錄檔
來確認。一般框架如果會設定這個值，也就會提供引數來控制行為，你的目標就是把它
改成 1。
2. **確認是否有不必要的唯讀事務。**有些框架會習慣不管什麼語句先用 begin/commit 框
起來。我見過有些是業務並沒有這個需要，但是也把好幾個 select 語句放到了事務中。
這種唯讀事務可以去掉。
3. 業務連線資料庫的時候，根據業務本身的預估，通過 SET MAX_EXECUTION_TIME 命
令，來控制每個語句執行的最長時間，避免單個語句意外執行太長時間。（為什麼會意
外？在後續的文章中會提到這類案例）
從資料庫端來看：
1. 監控 information_schema.Innodb_trx 表，設定長事務閾值，超過就報警 / 或者 kill；
2. Percona 的 pt-kill 這個工具不錯，推薦使用；
3. 在業務功能測試階段要求輸出所有的 general_log，分析紀錄檔行為提前發現問題；
4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 設定成
2（或更大的值）。如果真的出現大事務導致回滾段過大，這樣設定後清理起來更方
便。

深入淺出索引（上）

為什麼要應用索引？
索引的出現就是為了提高資料查詢的效率，就像書的目錄一樣。

索引的常見模型
**雜湊表：**採用key-value的資料儲存結構，只需要輸入待查詢的key的值，就可以找到其對應的value的值。雜湊的思路簡單，把值放到陣列裡，用一個雜湊函數將key值對映成陣列下標索引，然後再把value放到陣列的這個位置。
好處：增加新資料和查詢資料很快
缺點：因為儲存不是有序的，所以做區間索引會很慢

有序陣列：
優點：適用於靜態儲存引擎，比如要儲存的是2017年某個城市所有人口的資訊，不會進行修改。
缺點：缺點在中間插入的時候，就需要更改後面的所有資料。

二元搜尋樹
為了維持O（logN）的查詢複雜度，就需要這顆平衡二元樹
二元樹的效率是最高的，但是實際上大多數使用的資料庫儲存卻不使用二元樹，其原因是，索引不知在記憶體中，還在磁碟中。
為了讓一個查詢儘量少地讀磁碟，就必須讓查詢過程存取儘量少的資料塊。那麼，我們就
不應該使用二元樹，而是要使用「N 叉」樹。這裡，「N 叉」樹中的「N」取決於資料塊的大小。

以 InnoDB 的一個整數位段索引為例，這個 N 差不多是 1200。這棵樹高是 4 的時候，就
可以存 1200 的 3 次方個值，這已經 17 億了。考慮到樹根的資料塊總是在記憶體中的，一
個 10 億行的表上一個整數位段的索引，查詢一個值最多隻需要存取 3 次磁碟。其實，樹
的第二層也有很大概率在記憶體中，那麼存取磁碟的平均次數就更少了。

InnoDB索引模型
在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種儲存方式的表稱為索引組
織表。又因為前面我們提到的，InnoDB 使用了 B+ 樹索引模型，所以資料都是儲存在 B+
樹中的。

假設，有一個主鍵為ID的表，表中有欄位k，並且在k中有索索引

根據葉子節點的內容，索引型別分為主鍵索引和非主鍵索引；
主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為聚簇索引
（clustered index）。
非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引
（secondary index）。

基於主鍵索引和普通索引的查詢有什麼區別？
主鍵索引可以直接找到所在行的資料。而分主鍵索引需要先在輔助索引樹中找到主鍵索引，再查詢主鍵索引樹種查詢，這個過程稱為回表。

索引維護
B+樹為了維護索引的有序性，在插入新值的時候做有必要的維護。
如果直接在一個葉子節點後追加，就只需要在後面追加一格記錄。
但是如果在葉子節點中間插入值，就需要邏輯上挪動後面的資料，空出位置。更糟糕的是，如果後面資料頁已經滿了，根據B+樹的演演算法，就需要申請一個新的資料頁，然後挪動部分資料過去，這個過程稱為頁分裂。 分裂頁會影響效能，頁分裂操作也會影響資料頁的利用率。
有分裂也就有合併，當兩個頁由於刪除了資料，利用率很低之後，也會將資料頁做合併。

為什麼有些建議表語句中一定要有自增主鍵？
自增主鍵是指自增列上定義的主鍵，插入新記錄的時候可以不指定ID值，系統會獲取當前的ID最大值加1作為下一條記錄的ID值。
自增主鍵的每次插入都是追加操作，不涉及其他記錄的挪動，也不會出發葉子節點的分裂。

從儲存角度看，如果一個業務的欄位主鍵值很大，則會導致輔助索引葉子節點佔用大量的記憶體。顯然，主鍵長度越小，普通索引的葉子結點就越小，普通索引佔用的空間也就越小。

有沒有什麼場景適合用業務欄位直接做主鍵的呢？
1、只有一個索引 2、該索引必須是唯一的索引

重建索引？
索引可能因為刪除，或者頁分裂等原因，導致資料頁有空洞，重建索引的過程會建立一個新的索引，把資料按順序從插入，這樣頁面的利用率最高，也就是索引最緊湊，更省空間。
重建索引 k 的做法是合理的，可以達到省空間的目的。但是，重建主鍵的過程不合理。

深入淺出索引（下）

經過索引優化，避免回表的過程？
覆蓋索引：由於覆蓋索引可以減少樹的搜尋次數，顯著提升查詢效能，所以使用覆蓋索引是一個常用的效能優化手段。比如語句只需要知道ID的值，就可以使用覆蓋索引，這時候只需要查詢ID的值，而ID值就已經在K索引樹上了。
還可建立「聯合索引」，聯合索引的索引維護總是有代價的。因此，在建立冗餘索引來支援索引覆蓋就需要權衡考慮了。這正是業務DBA，或者成為業務資料架構師的工作。

最左字首原則：
B+樹這種索引結構，可以利用索引的「最左字首」，來定位記錄。
在建立聯合索引的時候，如何安排索引內的欄位順序?
索引項是按照索引定義裡面出現的欄位順序排序的
不只是索引的全部定義，只要滿足最左字首，就可以利用索引來加速檢索。這
個最左字首可以是聯合索引的最左 N 個欄位，也可以是字串索引的最左 M 個字元。

在建立聯合索引的時候，如何安排索引內的欄位順序？
第一原則是，如果通過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。

索引下推
我們還是以市民表的聯合索引（name, age）為例。如果現在有一個需求：檢索出表中「名字第一個字是張，而且年齡是 10 歲的所有男孩」。那麼，SQL 語句是這麼寫的：

可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

全域性鎖和表鎖：給表加個欄位怎麼有怎麼多障礙？
根據加鎖的範圍，可以分為全域性鎖、表鎖和行鎖

全域性鎖就是對整個資料庫範例加鎖。MySQL提供了一個枷全域性讀鎖的方法命令是Flush table with read lock(FTWRL)。當你需要讓整個庫處於唯讀狀態的時候，可以使用這個命令，之後其他執行緒的一下語句就會被阻塞：資料更新（增刪改），資料定義語句（建表、修改表結構）和更新類事務的提交語句。

全域性鎖的典型使用場景是，做全庫邏輯備份。也就是把整個庫每個表都select出來存成文字。

**single-transaction方法用於庫備份，但是它適用於所有的表使用事務引擎的庫。**如果有的表使用了不支援事務的引擎，那麼備份就只能通過 FTWRL 方法。這往往是 DBA 要求業務開發人員
使用 InnoDB 替代 MyISAM 的原因之一。
既然要全庫唯讀，為什麼不使用 set global readonly=true 的方式呢？
一是，在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫
還是備庫。因此，修改 global 變數的方式影響面更大，我不建議你使用。
二是，在例外處理機制上有差異。如果執行 FTWRL 命令之後由於使用者端發生異常斷
開，那麼 MySQL 會自動釋放這個全域性鎖，整個庫回到可以正常更新的狀態。而將整個
庫設定為 readonly 之後，如果使用者端發生異常，則資料庫就會一直保持 readonly 狀
態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。

表級鎖
MySQL有兩種表級所：一種是表鎖，一種是後設資料鎖（meta data lock,MDL）
表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動
釋放鎖，也可以在使用者端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別
的執行緒的讀寫外，也限定了本執行緒接下來的操作物件。
另一類表級的鎖是 MDL（metadata lock)。 MDL 不需要顯式使用，在存取一個表的時
候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個查詢正
在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查
詢執行緒拿到的結果跟表結構對不上，肯定是不行的。
當對一個表做增刪改查操作的時候，加 MDL 讀鎖（多個執行緒可以對錶進行增刪改查）；當要對表做結構變更操作的時候，加 MDL 寫鎖。

如何安全地給小表加欄位？
首先我們要解決長事務，事務不提交，就會一直佔著 MDL 鎖。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。
**定時，**拿不到表就放棄。在 alter table語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。

行鎖功過：怎麼減少行鎖對效能的影響？

MyISAM 引擎就不支援行鎖。不支援行鎖意味著並行控制只能使用表鎖，對於這種引

擎的表，同一張表上任何時刻只能有一個更新在執行，這就會影響到業務並行度。
在InnoDB事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段行鎖協定。

這就導致，如果你的事務中需要鎖多個行，要把最可能造成鎖衝突，最可能影響並行度的鎖儘量往後放。

死鎖和死鎖檢測
當並行系統中，不同執行緒出現迴圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖。

解決死鎖的兩種策略：
一，進入等待，直到超時，這個超時引數可以通過引數innodb_lock_wait_timeout 來設定。
二、**發起死鎖檢測，**發現死鎖後，主動回滾死鎖鏈條中的某個事務，讓其他事務得以繼續執行。將引數 innodb_deadlock_detect 設定為 on，表示開啟這個邏輯。

怎麼解決由這種熱點行更新導致的效能問題呢？
一種頭痛醫頭的方法，就是如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢
測關掉。
**另一個思路是控制並行度。**並行控制要做在資料庫伺服器。可是使用中介軟體、修改MySQL實現。基本思路就是，對於相同行的更新，在進入引擎之前排隊。這樣InnoDB內部就不會有大量的死鎖檢測工作了。
有一個簡單的辦法是將一行改成邏輯上的多行來減少鎖衝突。

事務到底是隔離還是不隔離的？

解說「當前讀」、「提交讀」和「一致性讀（可重複讀）」

begin/start stransation命令並不是一個事務的起點，在執行到它們的第一個操作InnoDB表的語句，事務才真正啟動。如果要馬上啟動一個事務，可以使用starttransaction with consistent snashot這個命令。

在MySQL裡，有兩個「檢視」的概念：
一個是 view。它是一個用查詢語句定義的虛擬表，在呼叫的時候執行查詢語句並生成結
果。建立檢視的語法是 create view … ，而它的查詢方法與表一樣。
另一個是 InnoDB 在實現 MVCC 時用到的一致性讀檢視，即 consistent read view，
用於支援 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重複讀）隔
離級別的實現。

「快照」在 MVCC 裡是怎麼工作的？
在可重複讀隔離級別下，事務在啟動的時候就「拍了個快照」。注意，這個快照是基於整
庫的。
InnoDB 裡面每個事務有一個唯一的事務 ID，叫作 transaction id。它是在事務開始的時候向 InnoDB 的事務系統申請的，是按申請順序嚴格遞增的。
而每行資料也都是有多個版本的。每次事務更新資料的時候，都會生成一個新的資料版
本，並且把 transaction id 賦值給這個資料版本的事務 ID，記為 row trx_id。同時，舊的
資料版本要保留，並且在新的資料版本中，能夠有資訊可以直接拿到它。
也就是說，資料表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row
trx_id。

undo log 在哪呢？
InnoDB 為每個事務構造了一個陣列，用來儲存這個事務啟動瞬間，當前正在「活躍」的所有事務 ID。「活躍」指的就是，啟動了但還沒提交
陣列裡面事務 ID 的最小值記為低水位，當前系統裡面已經建立過的事務 ID 的最大值加 1
記為高水位。
這個檢視陣列和高水位，就組成了當前事務的一致性檢視（read-view）。

1. 如果落在綠色部分，表示這個版本是已提交的事務或者是當前事務自己生成的，這個數
   據是可見的；
2. 如果落在紅色部分，表示這個版本是由將來啟動的事務生成的，是肯定不可見的；
3. 如果落在黃色部分，那就包括兩種情況
   a. 若 row trx_id 在陣列中，表示這個版本是由還沒提交的事務生成的，不可見；
   b. 若 row trx_id 不在陣列中，表示這個版本是已經提交了的事務生成的，可見。
   InnoDB 利用了「所有資料都有多個版本」的這個特性，實現了「秒級建立快照」的能力。

更新邏輯
更新資料都是先讀後寫的，而這個讀，只能讀當前的值，稱為「當前讀」（current read）
這裡我們提到了一個概念，叫作當前讀。其實，除了 update 語句外，select 語句如果加鎖，也是當前讀。

事務的可重複讀的能力是怎麼實現的？
可重複讀的核心就是一致性讀；而事務更新資料的時候，只能用當前讀。如果當前記錄的行鎖被其他可重複讀的事務佔用的話，就需要進入鎖等待。

讀提交和可重複讀的邏輯類似，它們最主要的區別是：
在可重複讀隔離級別下，只需要在事務開始的時候建立一致性檢視，之後事務裡的其他查詢都共用這一個一致性檢視；
在讀隔離級別下，每個語句執行前都會重新計算一個新的檢視。

InnoDB 的行資料有多個版本，每個資料版本有自己的 row trx_id，每個事務或者語句有
自己的一致性檢視。普通查詢語句是一致性讀，一致性讀會根據 row trx_id 和一致性檢視確定資料版本的可見性
對於可重複讀，查詢只承認在事務啟動前就已經提交完成的資料；
對於讀提交，查詢只承認在語句啟動前就已經提交完成的資料；

而當前讀，總是讀取已經提交完成的最新版本。