MySQL邏輯架構

MySQL的存儲引擎架構將查詢處理與數據的存儲/提取相分離，和其他數據庫相比，MySQL有點與眾不同，主要體現在存儲引擎的架構上，這種架構可以根據業務的需求和實際需求選擇合適的存儲引擎。

下面是MySQL的邏輯架構分層：

• 連接層：最上層是一些客戶端和連接服務，包含本地sock通信和大多數基于客戶端/服務端工具實現的類似于tcp/ip的通信。主要完成一些類似于連接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基于SSL的安全鏈接。服務器也會為安全接入的每個客戶端驗證它所具有的操作權限。
• 服務層：MySQL的核心服務功能層，該層是MySQL的核心，包括查詢緩存，解析器，解析樹，預處理器，查詢優化器。主要進行查詢解析、分析、查詢緩存、內置函數、存儲過程、觸發器、視圖等，select操作會先檢查是否命中查詢緩存，命中則直接返回緩存數據，否則解析查詢并創建對應的解析樹。
• 引擎層：存儲引擎層，存儲引擎真正的負責了MySQL中數據的存儲和提取，服務器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。
• 存儲層：數據存儲層，主要是將數據存儲在運行于裸設備的文件系統之上，并完成與存儲引擎的交互。

對比InnoDB與MyISAM

存儲結構

MyISAM：每個MyISAM在磁盤上存儲成三個文件。分別為：表定義文件、數據文件、索引文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm文件存儲表定義。數據文件的擴展名為.MYD (MYData)。索引文件的擴展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一個數據文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統文件的大小，一般為2GB。

存儲空間

MyISAM： MyISAM支持支持三種不同的存儲格式：靜態表(默認，但是注意數據末尾不能有空格，會被去掉)、動態表、壓縮表。當表在創建之后并導入數據之后，不會再進行修改操作，可以使用壓縮表，極大的減少磁盤的空間占用。

InnoDB： 需要更多的內存和存儲，它會在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。

可移植性、備份及恢復

MyISAM：數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作。

InnoDB：免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十G的時候就相對痛苦了。

事務支持

MyISAM：強調的是性能，每次查詢具有原子性,其執行數度比InnoDB類型更快，但是不提供事務支持。

InnoDB：提供事務支持事務，外部鍵等高級數據庫功能。 具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

AUTO_INCREMENT

MyISAM：可以和其他字段一起建立聯合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序后遞增。

InnoDB：InnoDB中必須包含只有該字段的索引。引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列。

表鎖差異

MyISAM： 只支持表級鎖，用戶在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發的情況下，可以在表的尾部插入新的數據。

InnoDB： 支持事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多用戶并發操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

全文索引

MyISAM：支持 FULLTEXT類型的全文索引

InnoDB：不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

表主鍵

MyISAM：允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。

InnoDB：如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節的主鍵(用戶不可見)，數據是主索引的一部分，附加索引保存的是主索引的值。

表的具體行數

MyISAM： 保存有表的總行數，如果select count() from table;會直接取出出該值。

InnoDB： 沒有保存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre條件后，myisam和innodb處理的方式都一樣。

CRUD操作

MyISAM：如果執行大量的SELECT，MyISAM是更好的選擇。

InnoDB：如果你的數據執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表。

外鍵

MyISAM：不支持

InnoDB：支持

sql優化簡介

什么情況下進行sql優化

性能低、執行時間太長、等待時間太長、連接查詢、索引失效。

sql語句執行過程

1. 編寫過程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

2. 解析過程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

sql優化就是優化索引

• 索引相當于書的目錄。
• 索引的數據結構是B+樹。

索引

索引的優勢

• 提高查詢效率（降低IO使用率）
• 降低CPU使用率

比如查詢order by age desc，因為B+索引樹本身就是排好序的，所以再查詢如果觸發索引，就不用再重新查詢了。

索引的弊端

• 索引本身很大，可以存放在內存或硬盤上，通常存儲在硬盤上。
• 索引不是所有情況都使用，比如①少量數據②頻繁變化的字段③很少使用的字段
• 索引會降低增刪改的效率

索引的分類

• 單值索引
• 唯一索引
• 聯合索引
• 主鍵索引

備注：唯一索引和主鍵索引唯一的區別：主鍵索引不能為null

創建索引

alter table user add INDEX `user_index_username_password` (`username`,`password`)

MySQL索引原理 -> B+樹

MySQL索引的底層數據結構是 B+樹

B+Tree是在B-Tree基礎上的一種優化，使其更適合實現外存儲索引結構，InnoDB存儲引擎就是用B+Tree實現其索引結構。

B-Tree結構圖中每個節點中不僅包含數據的key值，還有data值。而每一個頁的存儲空間是有限的，如果data數據較大時將會導致每個節點（即一個頁）能存儲的key的數量很小，當存儲的數據量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁盤I/O次數，進而影響查詢效率。在B+Tree中，所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B+Tree的高度。

B+Tree相對于B-Tree有幾點不同

• 非葉子節點只存儲鍵值信息。
• 所有葉子節點之間都有一個鏈指針。
• 數據記錄都存放在葉子節點中。

由于B+Tree的非葉子節點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結構如下圖所示：

通常在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即數據節點）之間是一種鏈式環結構。因此可以對B+Tree進行兩種查找運算：一種是對于主鍵的范圍查找和分頁查找，另一種是從根節點開始，進行隨機查找。

可能上面例子中只有22條數據記錄，看不出B+Tree的優點，下面做一個推算：

InnoDB存儲引擎中頁的大小為16KB，一般表的主鍵類型為INT（占用4個字節）或BIGINT（占用8個字節），指針類型也一般為4或8個字節，也就是說一個頁（B+Tree中的一個節點）中大概存儲16KB/(8B+8B)=1K個鍵值。也就是說一個深度為3的B+Tree索引可以維護103 * 10^3 * 10^3 = 10億 條記錄。

實際情況中每個節點可能不能填充滿，因此在數據庫中，B+Tree的高度一般都在24層。MySQL的InnoDB存儲引擎在設計時是將根節點常駐內存的，也就是說查找某一鍵值的行記錄時最多只需要13次磁盤I/O操作。

數據庫中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree示例圖在數據庫中的實現即為聚集索引，聚集索引的B+Tree中的葉子節點存放的是整張表的行記錄數據。輔助索引與聚集索引的區別在于輔助索引的葉子節點并不包含行記錄的全部數據，而是存儲相應行數據的聚集索引鍵，即主鍵。當通過輔助索引來查詢數據時，InnoDB存儲引擎會遍歷輔助索引找到主鍵，然后再通過主鍵在聚集索引中找到完整的行記錄數據。

聚簇索引與非聚簇索引

mysql中普遍使用B+Tree做索引，但在實現上又根據聚簇索引和非聚簇索引而不同。

聚簇索引

所謂聚簇索引，就是指主索引文件和數據文件為同一份文件，聚簇索引主要用在Innodb存儲引擎中。在該索引實現方式中B+Tree的葉子節點上的data就是數據本身，key為主鍵，如果是一般索引的話，data便會指向對應的主索引，如下圖所示：

在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指針，就形成了帶有順序訪問指針的B+Tree。做這個優化的目的是為了提高區間訪問的性能，例如上圖中如果要查詢key為從18到49的所有數據記錄，當找到18后，只需順著節點和指針順序遍歷就可以一次性訪問到所有數據節點，極大提到了區間查詢效率。

非聚簇索引

非聚簇索引就是指B+Tree的葉子節點上的data，并不是數據本身，而是數據存放的地址。主索引和輔助索引沒啥區別，只是主索引中的key一定得是唯一的。主要用在MyISAM存儲引擎中，如下圖：

非聚簇索引比聚簇索引多了一次讀取數據的IO操作，所以查找性能上會差。

如何觸發聯合索引

對user表建立聯合索引username、password

觸發聯合索引

使用聯合索引的全部索引鍵可觸發聯合索引

使用聯合索引的全部索引鍵，但是用or連接的，不可觸發聯合索引

單獨使用聯合索引的左邊第一個字段時，可觸發聯合索引

單獨使用聯合索引的其它字段時，不可觸發聯合索引

分析sql的執行計劃—explain

explain可以模擬sql優化執行sql語句。

explan使用簡介

用戶表

部門表

未觸發索引

觸發索引

結果分析

explain中第一行出現的表是驅動表。

• 指定了聯接條件時，滿足查詢條件的記錄行數少的表為[驅動表]
• 未指定聯接條件時，行數少的表為[驅動表]
• 對驅動表直接進行排序就會觸發索引，對非驅動表進行排序不會觸發索引。

explain查詢結果簡介

1. id：SELECT識別符。這是SELECT的查詢序列號。

2. select_type：SELECT類型：

   • SIMPLE： 簡單SELECT(不使用UNION或子查詢)
   • PRIMARY： 最外面的SELECT
   • UNION：UNION中的第二個或后面的SELECT語句
   • DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
   • UNION RESULT：UNION的結果
   • SUBQUERY：子查詢中的第一個SELECT
   • DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢
   • DERIVED：導出表的SELECT(FROM子句的子查詢)

3. table：表名

4. type：聯接類型

   • system：表僅有一行(=系統表)。這是const聯接類型的一個特例。
   • const：表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩余部分認為是常數。const用于用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時。
   • eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。
   • ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY(換句話說，如果聯接不能基于關鍵字選擇單個行的話)，則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。ref可以用于使用=或<=>操作符的帶索引的列。
   • ref_or_null：該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。
   • index_merge：該聯接類型表示使用了索引合并優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。
   • unique_subquery：該類型替換了下面形式的IN子查詢的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。
   • index_subquery：該聯接類型類似于unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
   • range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range
     index：該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。
   • all：對于每個來自于先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數值或列值被檢索出。

5. possible_keys：possible_keys列指出MySQL能使用哪個索引在該表中找到行。注意，該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。

6. key：key列顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7. key_len：key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。注意通過key_len值我們可以確定MySQL將實際使用一個多部關鍵字的幾個部分。

8. ref：ref列顯示使用哪個列或常數與key一起從表中選擇行。

9. rows：rows列顯示MySQL認為它執行查詢時必須檢查的行數。

10. Extra：該列包含MySQL解決查詢的詳細信息。

    • Distinct：MySQL發現第1個匹配行后，停止為當前的行組合搜索更多的行。
    • Not exists：MySQL能夠對查詢進行LEFT JOIN優化，發現1個匹配LEFT JOIN標準的行后，不再為前面的的行組合在該表內檢查更多的行。
    • range checked for each record (index map: #)：MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。
    • Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然后關鍵字被排序，并按排序順序檢索行。
    • Using index：從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。當查詢只使用作為單一索引一部分的列時，可以使用該策略。
    • Using temporary：為了解決查詢，MySQL需要創建一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。
    • Using where：WHERE子句用于限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯接類型為ALL或index，查詢可能會有一些錯誤。
    • Using sort_union(…), Using union(…), Using intersect(…)：這些函數說明如何為index_merge聯接類型合并索引掃描。
    • Using index for group-by：類似于訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。并且，按最有效的方式使用索引，以便對于每個組，只讀取少量索引條目。

通過相乘EXPLAIN輸出的rows列的所有值，你能得到一個關于一個聯接如何的提示。這應該粗略地告訴你MySQL必須檢查多少行以執行查詢。當你使用max_join_size變量限制查詢時，也用這個乘積來確定執行哪個多表SELECT語句。