MySQL 深入學習總結

作者：yandeng，騰訊 PCG 應用開發工程師

1.數據庫基礎

1.1 MySQL 架構

和其它數據庫相比，MySQL 有點與眾不同，它的架構可以在多種不同場景中應用并發揮良好作用。主要體現在存儲引擎的架構上，插件式的存儲引擎架構將查詢處理和其它的系統任務以及數據的存儲提取相分離。這種架構可以根據業務的需求和實際需要選擇合適的存儲引擎，各層介紹：

1.1.1 連接層

最上層是一些客戶端和連接服務，包含本地 sock 通信和大多數基于客戶端/服務端工具實現的類似于 tcp/ip 的通信。主要完成一些類似于連接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基于 SSL 的安全鏈接。服務器也會為安全接入的每個客戶端驗證它所具有的操作權限。

1.1.2 服務層

1.1.3 引擎層

存儲引擎層，存儲引擎真正的負責了 MySQL 中數據的存儲和提取，服務器通過 API 與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。

1.1.4 存儲層

數據存儲層，主要是將數據存儲在運行于裸設備的文件系統之上，并完成與存儲引擎的交互。

1.2 數據引擎

不同的存儲引擎都有各自的特點，以適應不同的需求，如表所示。為了做出選擇，首先要考慮每一個存儲引擎提供了哪些不同的功能。

1.2.1 MyISAM

使用這個存儲引擎，每個 MyISAM 在磁盤上存儲成三個文件。

1. frm 文件：存儲表的定義數據
2. MYD 文件：存放表具體記錄的數據
3. MYI 文件：存儲索引

1.2.2 InnoDB

InnoDB 是默認的數據庫存儲引擎，他的主要特點有：

1. 可以通過自動增長列，方法是 auto_increment；
2. 支持事務。默認的事務隔離級別為可重復度，通過 MVCC（并發版本控制）來實現的；
3. 使用的鎖粒度為行級鎖，可以支持更高的并發；
4. 支持外鍵約束；外鍵約束其實降低了表的查詢速度，但是增加了表之間的耦合度；
5. 配合一些熱備工具可以支持在線熱備份；
6. 在 InnoDB 中存在著緩沖管理，通過緩沖池，將索引和數據全部緩存起來，加快查詢的速度；
7. 對于 InnoDB 類型的表，其數據的物理組織形式是聚簇表。所有的數據按照主鍵來組織。數據和索引放在一塊，都位于 B+數的葉子節點上。

1.2.3 Memory

將數據存在內存，為了提高數據的訪問速度，每一個表實際上和一個磁盤文件關聯。文件是 frm。

1. 支持的數據類型有限制，比如：不支持 TEXT 和 BLOB 類型，對于字符串類型的數據，只支持固定長度的行；VARCHAR 會被自動存儲為 CHAR 類型；
2. 支持的鎖粒度為表級鎖。所以，在訪問量比較大時，表級鎖會成為 MEMORY 存儲引擎的瓶頸；
3. 由于數據是存放在內存中，一旦服務器出現故障，數據都會丟失；
4. 查詢的時候，如果有用到臨時表，而且臨時表中有 BLOB，TEXT 類型的字段，那么這個臨時表就會轉化為 MyISAM 類型的表，性能會急劇降低；
5. 默認使用 hash 索引；
6. 如果一個內部表很大，會轉化為磁盤表。

1.3 表與字段設計

1.3.1 數據庫基本設計規范

1. 盡量控制單表數據量的大小，建議控制在 500 萬以。500 萬并不是 MySQL 數據庫的限制，過大會造成修改表結構、備份、恢復都會有很大的問題，可以用歷史數據歸檔（應用于日志數據），分庫分表（應用于業務數據）等手段來控制數據量大小；
2. 謹慎使用 MySQL 分區表。分區表在物理上表現為多個文件，在邏輯上表現為一個表 謹慎選擇分區鍵，跨分區查詢效率可能更低 建議采用物理分表的方式管理大數據；
3. 禁止在數據庫中存儲圖片，文件等大的二進制數據。通常文件很大，會短時間內造成數據量快速增長，數據庫進行數據庫讀取時，通常會進行大量的隨機 IO 操作，文件很大時，IO 操作很耗時 通常存儲于文件服務器，數據庫只存儲文件地址信息；
4. 禁止在線上做數據庫壓力測試。

1.3.2 數據庫字段設計規范

1. 優先選擇符合存儲需要的最小的數據類型。列的字段越大，建立索引時所需要的空間也就越大，這樣一頁中所能存儲的索引節點的數量也就越少也越少，在遍歷時所需要的 IO 次數也就越多， 索引的性能也就越差；
2. 避免使用 TEXT、BLOB 數據類型，最常見的 TEXT 類型可以存儲 64k 的數據；
3. 盡可能把所有列定義為 NOT NULL。

1.3.3 索引設計規范

1. 限制每張表上的索引數量，建議單張表索引不超過 5 個；
2. 禁止給表中的每一列都建立單獨的索引；
3. 每個 InnoDB 表必須有個主鍵；
4. 建立索引的目的是：希望通過索引進行數據查找，減少隨機 IO，增加查詢性能 ，索引能過濾出越少的數據，則從磁盤中讀入的數據也就越少。區分度最高的放在聯合索引的最左側（區分度 = 列中不同值的數量 / 列的總行數）。盡量把字段長度小的列放在聯合索引的最左側（因為字段長度越小，一頁能存儲的數據量越大，IO 性能也就越好）。使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）。

1.3.4 數據庫 SQL 開發規范

1. 充分利用表上已經存在的索引,避免使用雙 % 號的查詢條件。如 a like '%123%'，（如果無前置 %，只有后置 %，是可以用到列上的索引的）一個 SQL 只能利用到復合索引中的一列進行范圍查詢，如：有 a,b,c 列的聯合索引，在查詢條件中有 a 列的范圍查詢，則在 b,c 列上的索引將不會被用到，在定義聯合索引時，如果 a 列要用到范圍查找的話，就要把 a 列放到聯合索引的右側；使用 left join 或 not exists 來優化 not in 操作， 因為 not in 也通常會使用索引失效；
2. 禁止使用 SELECT * 必須使用 SELECT <字段列表> 查詢；
3. 避免使用子查詢，可以把子查詢優化為 JOIN 操作；
4. 避免使用 JOIN 關聯太多的表。

1.4 范式與反范式

1.4.1 第一范式

該范式是為了排除 重復組 的出現，因此要求數據庫的每個列的值域都由原子值組成；每個字段的值都只能是單一值。1971 年埃德加·科德提出了第一范式。即表中所有字段都是不可再分的。解決方案：想要消除重復組的話，只要把每筆記錄都轉化為單一記錄即可。

1.4.2 第二范式

表中必須存在業務主鍵，并且非主鍵依賴于全部業務主鍵。解決方案：拆分將依賴的字段單獨成表。

1.4.3 第三范式

表中的非主鍵列之間不能相互依賴，將不與 PK 形成依賴關系的字段直接提出單獨成表即可。

1.5 sql 索引

1. B 樹只適合隨機檢索，適合文件操作，B+樹同時支持隨機檢索和順序檢索；
2. B+樹的磁盤讀寫代價更低， B+樹的內部結點并沒有指向關鍵字具體信息的指針；
3. B+樹的查詢效率更加穩定。B 樹搜索有可能會在非葉子結點結束；
4. 只要遍歷葉子節點就可以實現整棵樹的遍歷，數據庫中基于范圍的查詢是非常頻繁，B 樹這樣的操作效率非常低。

1.6 join 連表

1.6.1 JOIN 按照功能大致分為如下三類：

1. INNER JOIN（內連接,或等值連接）：獲取兩個表中字段匹配關系的記錄。

2. LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。

3. RIGHT JOIN（右連接）：與 LEFT JOIN 相反，用于獲取右表所有記錄，即使左表沒有對應匹配的記錄。

1.6.2 join 的原理

MySQL 使用了嵌套循環（Nested-Loop Join）的實現方式。Nested-Loop Join 需要區分驅動表和被驅動表，先訪問驅動表，篩選出結果集，然后將這個結果集作為循環的基礎，訪問被驅動表過濾出需要的數據。Nested-Loop Join 分下面幾種類型：

1. SNLJ，簡單嵌套循環。這是最簡單的方案，性能也一般。實際上就是通過驅動表的結果集作為循環基礎數據，然后一條一條的通過該結果集中的數據作為過濾條件到下一個表中查詢數據，然后合并結果。如果還有第三個參與 Join，則再通過前兩個表的 Join 結果集作為循環基礎數據，再一次通過循環查詢條件到第三個表中查詢數據，如此往復。

1. INLJ，索引嵌套循環。索引嵌套聯系由于非驅動表上有索引，所以比較的時候不再需要一條條記錄進行比較，而可以通過索引來減少比較，從而加速查詢。這也就是平時我們在做關聯查詢的時候必須要求關聯字段有索引的一個主要原因。

相關圖片來源于網絡

1. BNLJ，塊嵌套循環。如果 join 字段沒索引，被驅動表需要進行掃描。這里 MySQL 并不會簡單粗暴的應用前面算法，而是加入了 buffer 緩沖區，降低了內循環的個數，也就是被驅動表的掃描次數。

這個 buffer 被稱為 join buffer，顧名思義，就是用來緩存 join 需要的字段。MySQL 默認 buffer 大小 256K，如果有 n 個 join 操作，會生成 n-1 個 join buffer。

1.6.3 join 的優化

1. 小結果集驅動大結果集。用數據量小的表去驅動數據量大的表，這樣可以減少內循環個數，也就是被驅動表的掃描次數。
2. 用來進行 join 的字段要加索引，會觸發 INLJ 算法，如果是主鍵的聚簇索引，性能最優。例子：第一個子查詢是 72075 條數據，join 的第二條子查詢是 50w 數據，主要的優化還是驅動表是小表，后面的是大表，on 的條件加上了唯一索引。

1. 如果無法使用索引，那么注意調整 join buffer 大小，適當調大些
2. 減少不必要的字段查詢（字段越少，join buffer 所緩存的數據就越多）

2.數據進階

2.1 sql 執行過程

如上圖所示，當向 MySQL 發送一個請求的時候，MySQL 到底做了什么：

1. 客戶端發送一條查詢給服務器。服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。否則進入下一階段；
2. 在解析一個查詢語句之前，如果查詢緩存是打開的，那么 MYSQL 會優先檢查這個查詢是否命中查詢緩存中的數據；
3. 這個檢查是通過一個對大小寫敏感的哈希查找的。查詢和緩存中的查詢即使只有一個不同，也不會匹配緩存結果；
4. 如果命中緩存，那么在但會結果前 MySQL 會檢查一次用戶權限，有權限則跳過其他步驟直接返回數據；
5. 服務器端進行 SQL 解析、預處理，再由優化器生成對應的執行計劃。MySQL 解析器將使用 MySQL 語法規則驗證和解析查詢。例如驗證是否使用錯誤的關鍵字、關鍵字順序、引號前后是否匹配等；預處理器則根據一些 MySQL 規則進一步解析樹是否合法，例如檢查數據表和數據列是否存在，解析名字和別名是否有歧義等；
6. MySQL 根據優化器生成的執行計劃，再調用存儲引擎的 API 來執行查詢。

MySQL 的查詢優化器使用很多策略來生成一個最優的執行計劃。優化策略可以簡單的分為兩種：

1. 靜態優化：靜態優化可以直接對解析樹進行分析，并完成優化。例如優化器可以通過簡單的代數變化將 WHERE 條件轉換成另外一種等價形式，靜態優化在第一次完成后就一直有效，即使使用不同的參數重復執行查詢也不會變化。可以認為是一種”編譯時優化“。

1. 動態優化：和查詢的上下文有關，也可能和其他因素有關，例如 WHERE 中取值、索引中條目對應的數據行數等。這需要在每次查詢的時候重新評估，可以讓那位 u 是'運行時優化'。

使用 show status like 'Last_query_cost’ 可以查詢上次執行的語句的成本，單位為數據頁。

2.2 sql 查詢計劃

使用 explain 進行執行計劃分析：

2.3 sql 索引優化

遵循索引原則適合大部分的常規數據庫查詢場景，但不是所有的索引都能符合預期，從索引原理本身來分析對索引的創建會更有幫助。

1. 小表的全表掃描往往會比索引更快 ；
2. 中大型表使用索引會有很大的查詢效率提升；超大型表，索引也無法解決慢查詢，過多和過大的索引會帶來更多的磁盤占用和降低 INSERT 效率。

2.3.1 前綴索引

當要索引的列字符很多時 索引則會很大且變慢( 可以只索引列開始的部分字符串 節約索引空間 從而提高索引效率 )

例如：一個數據表的 x_name 值都是類似 23213223.434323.4543.4543.34324 這種值，如果以整個字段值做索引，會使索引文件過大，但是如果設置前 7 位來做索引則不會出現重復索引值的情況了。

查詢效率會大大提升：

2.3.2 聯合索引順序

alter table table1 add key （distribute_type，plat_id）

使用選擇基數更高（不重復的數據）的字段作為最左索引：

2.3.3 聯合索引左前綴匹配

1. a=? and b=? and c=?；查詢效率最高，索引全覆蓋
2. a=? and b=?；索引覆蓋 a 和 b
3. a=? or b=?；索引覆蓋 a 和 b
4. b=? or a=?；無法覆蓋索引(>、<、between、like)
5. b=? and a=?；經過 mysql 的查詢分析器的優化，索引覆蓋 a 和 b
6. a=?；索引覆蓋 a
7. b=? and c=?；沒有 a 列，不走索引，索引失效
8. c=?；沒有 a 列，不走索引，索引失效

2.4 慢查詢分析

2.4.1 先對 sql 語句進行 explain，查看語句存在的問題

2.4.2 使用 show profile 查看執行耗時，分析具體耗時原因

show profile 的使用指引：

2.5 改表與 sql 日志

2.5.1 改表

改表會直接觸發表鎖，改表過程非常耗時，對于大表修改，無論是字段類型調整還是字段增刪，都需要謹慎操作，防止業務表操作被阻塞，大表修改往往有以下幾種方式。

1. 主備改表切換，先改冷庫表，再執行冷熱切換；
2. 直接操作表數據文件，拷貝文件替換；
3. 使用類似 percona-toolkit 工具操作表。

常用方法：

2.5.2 sql 日志

2.6 分庫與分表

2.6.1 數據庫瓶頸

不管是 IO 瓶頸，還是 CPU 瓶頸，最終都會導致數據庫的活躍連接數增加，進而逼近甚至達到數據庫可承載活躍連接數的閾值。在業務 Service 來看就是，可用數據庫連接少甚至無連接可用。接下來就可以想象了吧（并發量、吞吐量、崩潰）。

1. IO 瓶頸 第一種：磁盤讀 IO 瓶頸，熱點數據太多，數據庫緩存放不下，每次查詢時會產生大量的 IO，降低查詢速度 -> 分庫和垂直分表。

第二種：網絡 IO 瓶頸，請求的數據太多，網絡帶寬不夠 -> 分庫。

1. CPU 瓶頸 第一種：SQL 問題，如 SQL 中包含 join，group by，order by，非索引字段條件查詢等，增加 CPU 運算的操作 -> SQL 優化，建立合適的索引，在業務 Service 層進行業務計算。

第二種：單表數據量太大，查詢時掃描的行太多，SQL 效率低，CPU 率先出現瓶頸 -> 水平分表。

2.6.2 分庫分表

1. 水平分庫

概念：以字段為依據，按照一定策略（hash、range 等），將一個庫中的數據拆分到多個庫中。結果：每個庫的結構都一樣；每個庫的數據都不一樣，沒有交集；所有庫的并集是全量數據；場景：系統絕對并發量上來了，分表難以根本上解決問題，并且還沒有明顯的業務歸屬來垂直分庫。分析：庫多了，io 和 cpu 的壓力自然可以成倍緩解。

1. 水平分表相關圖片來源于網絡

概念：以字段為依據，按照一定策略（hash、range 等），將一個表中的數據拆分到多個表中。結果：每個表的結構都一樣；每個表的數據都不一樣，沒有交集；所有表的并集是全量數據。

場景：系統絕對并發量并沒有上來，只是單表的數據量太多，影響了 SQL 效率，加重了 CPU 負擔，以至于成為瓶頸。

分析：表的數據量少了，單次 SQL 執行效率高，自然減輕了 CPU 的負擔。

1. 垂直分庫
   相關圖片來源于網絡
   

概念：以表為依據，按照業務歸屬不同，將不同的表拆分到不同的庫中。

結果：每個庫的結構都不一樣；每個庫的數據也不一樣，沒有交集；所有庫的并集是全量數據。

場景：系統絕對并發量上來了，并且可以抽象出單獨的業務模塊。

分析：到這一步，基本上就可以服務化了。例如，隨著業務的發展一些公用的配置表、字典表等越來越多，這時可以將這些表拆到單獨的庫中，甚至可以服務化。再有，隨著業務的發展孵化出了一套業務模式，這時可以將相關的表拆到單獨的庫中，甚至可以服務化。

1. 垂直分表

概念：以字段為依據，按照字段的活躍性，將表中字段拆到不同的表（主表和擴展表）中。

結果：每個表的結構都不一樣；每個表的數據也不一樣，一般來說，每個表的字段至少有一列交集，一般是主鍵，用于關聯數據；所有表的并集是全量數據。

2.6.3 分庫分表工具

目前市面上的分庫分表中間件相對較多，其中基于代理方式的有 MySQL Proxy 和 Amoeba， 基于 Hibernate 框架的是 Hibernate Shards，基于 jdbc 的有當當 sharding-jdbc， 基于 mybatis 的類似 maven 插件式的有蘑菇街的蘑菇街 TSharding， 通過重寫 spring 的 ibatis template 類的 Cobar Client。

還有一些大公司的開源產品：

3.分布式數據庫

3.1 什么是分布式數據庫

分布式系統數據庫系統原理(第三版)中的描述：“我們把分布式數據庫定義為一群分布在計算機網絡上、邏輯上相互關聯的數據庫。分布式數據庫管理系統(分布式 DBMS)則是支持管理分布式數據庫的軟件系統，它使得分布對于用戶變得透明。有時，分布式數據庫系統(Distributed Database System,DDBS)用于表示分布式數據庫和分布式 DBMS 這兩者。

在以上表述中，“一群分布在網絡上、邏輯上相互關聯”是其要義。在物理上一群邏輯上相互關聯的數據庫可以分布式在一個或多個物理節點上。當然，主要還是應用在多個物理節點。這一方面是 X86 服務器性價比的提升有關，另一方面是因為互聯網的發展帶來了高并發和海量數據處理的需求，原來的單物理服務器節點不足以滿足這個需求。

3.2 分布式數據庫的理論基礎

1. CAP 理論首先，分布式數據庫的技術理論是基于單節點關系數據庫的基本特性的繼承，主要涉及事務的 ACID 特性、事務日志的容災恢復性、數據冗余的高可用性幾個要點。

其次，分布式數據的設計要遵循 CAP 定理，即：一個分布式系統不可能同時滿足 一致性( Consistency ) 、可用性 ( Availability ) 、分區容 忍 性 ( Partition tolerance ) 這三個基本需求，最 多只能同時滿足其中的兩項， 分區容錯性 是不能放棄的，因此架構師通常是在可用性和一致性之間權衡。這里的權衡不是簡單的完全拋棄，而是考慮業務情況作出的犧牲，或者用互聯網的一個術語“降級”來描述。

CAP 三個特性描述如下 ：一致性：確保分布式群集中的每個節點都返回相同的 、 最近 更新的數據 。一致性是指每個客戶端具有相同的數據視圖。有多種類型的一致性模型 ， CAP 中的一致性是指線性化或順序一致性，是強一致性。

可用性：每個非失敗節點在合理的時間內返回所有讀取和寫入請求的響應。為了可用，網絡分區兩側的每個節點必須能夠在合理的時間內做出響應。

分區容忍性：盡管存在網絡分區，系統仍可繼續運行并 保證 一致性。網絡分區已成事實。保證分區容忍度的分布式系統可以在分區修復后從分區進行適當的恢復。

2. BASE 理論

基于 CAP 定理的權衡，演進出了 BASE 理論 ，BASE 是 Basically Available(基本可用)、Soft state(軟狀態)和 Eventually consistent(最終一致性)三個短語的縮寫。BASE 理論的核心思想是：即使無法做到強一致性，但每個應用都可以根據自身業務特點，采用適當的方式來使系統達到最終一致性。

BA：Basically Available 基本可用，分布式系統在出現故障的時候，允許損失部分可用性，即保證核心可用；S：Soft state 軟狀態，允許系統存在中間狀態，而該中間狀態不會影響系統整體可用性；E：Consistency 最終一致性，系統中的所有數據副本經過一定時間后，最終能夠達到一致的狀態。

BASE 理論本質上是對 CAP 理論的延伸，是對 CAP 中 AP 方案的一個補充。

3.3 分布式數據庫的架構演變

三類數據庫架構特點：

1. Shard-everting：共享數據庫引擎和數據庫存儲，無數據存儲問題。一般是針對單個主機，完全透明共享 CPU/MEMORY/IO，并行處理能力是最差的，典型的代表 SQLServer；

2. Shared-storage：引擎集群部署，分攤接入壓力，無數據存儲問題；

3. Shard-noting：引擎集群部署，分攤接入壓力，存儲分布式部署，存在數據存儲問題。各個處理單元都有自己私有的 CPU/內存/硬盤等，不存在共享資源，類似于 MPP（大規模并行處理）模式，各處理單元之間通過協議通信，并行處理和擴展能力更好。典型代表 DB2 DPF 和 hadoop ，各節點相互獨立，各自處理自己的數據，處理后的結果可能向上層匯總或在節點間流轉。