Mysql高階最佳化（一）

以下內容為本人MySql高階最佳化整理，轉載請附上原文連結

Mysql 執行流程

1> 客戶端 : 並非MySQL所獨有，諸如 : 連線處理、授權認證、安全等功能均在這一層處理
2> 核心服務 : 包括查詢解析、分析、最佳化、快取、內建函式(比如 : 時間、數學、加密等函式)，所有的跨儲存引擎的功能也在這一層實現 : 儲存過程、觸發器、檢視等
3> 儲存引擎 : 負責 MySQL 中的資料儲存和提取，和 Linux 下的檔案系統類似，每種儲存引擎都
有其優勢和劣勢，中間的服務層透過 API 與儲存引擎通訊，這些 API介面 遮蔽不同儲存引擎間的
差異

組成部分：

1. 連線池元件
2. 管理服務和工具元件
3. Sql介面元件
4. 查詢分析器元件
5. 最佳化器元件
6. 緩衝元件
7. 外掛式儲存引擎(與其他資料庫的主要區別)
8. 物理檔案

MySQL資料庫區別與其他資料庫最重要的一個特點就是期外掛式的表儲存引擎，mysql外掛式的表儲存引擎 結構提供了一系列標準的管理和服務支援，這些標準與儲存引擎本身無關，可能是每個資料庫系統本身都 必須的，例如SQL分析器和最佳化器等，而儲存引擎是底層物理結構的實現，每個儲存引擎開發者可以按照自 己的意願來進行開發，需要特別注意的是，儲存引擎是基於表的，而不是資料庫

mysql的體系結構可以分為兩層，mysql server層和儲存引擎層，在mysql server層中又包括連線層與sql層。

1. 連線層
客戶端或者應用程式透過介面(如：ODBC，JDBC)來連線MySQL，最先連線處理的就是連線層，連線層 包括通訊協議，執行緒處理，使用者名稱密碼認證三個部分，通訊協議負責檢測客戶端版本是否與服務端 相容，執行緒處理是指每一個連線請求都會分配一個對應獨立的執行緒，使用者名稱密碼認證建立的賬號和 密碼，以及host主機授權是否可以連線到mysql伺服器

2. sql層
sql層包含許可權判斷，查詢快取，解析器，預處理，查詢最佳化器，快取和執行計劃； 1) 許可權判斷可以透過稽核使用者有沒有訪問某個庫，某個表，或者表裡某行的許可權。 2) 查詢快取透過query cache進行操作，如果資料在query cache中，則直接返回結果給客戶端 3) 查詢解析器針對sql語句進行解析，判斷語法是否正確，並生成解析器 4) 前處理器解決解析器無法解析的語義 5) 最佳化器對sql語句進行改寫和相應的最佳化,例如：對連線表重排序，對外連線轉內連線，代數等價 法則，計算和減少常量表示式，自查詢最佳化，早期終結，相等傳遞等，並生成最優的執行計劃，然 後就可以呼叫程式的API介面，透過儲存引擎層訪問資料；

3. 儲存引擎層
MySQL資料庫的核心就在與儲存引擎； mysql資料庫是開源的，所以使用者可以根據mysql預定義的儲存引擎介面編寫自己的儲存引擎，如果對現有 儲存引擎的效能或功能不滿意，可以透過修改原始碼來得到想要的特性。 儲存引擎可以分為mysql官網儲存引擎和第三方儲存引擎，innodb儲存引擎早期就是第三方儲存引擎，後來 被oracle收購，同時也是mysql資料量OLTP線上事務處理應用最廣泛的儲存引擎。
3.1 Innodb儲存引擎 Innodb儲存引擎支援事務，其設計目標主要面向線上事務處理的應用，其特點是行鎖設計，支援外 鍵，並支援類似於Oracle的非鎖定讀，即預設讀取操作不會產生鎖，從mysql資料庫5.5版本開始， Innodb儲存引擎是預設的儲存引擎。

Innodb儲存引擎將資料放在一個邏輯表空間中，這個表空間就行黑盒一樣由Innodb儲存引擎自身進 行管理，從mysql4.1版本開始，他可以將每個Innodb儲存引擎的表單獨存放到一個獨立的ibd檔案 中，此外，Innodb儲存引擎支援用裸裝置來建立其表空間。 Innodb儲存引擎透過使用多版本併發控制(MVCC)來獲得高併發性，並且實現了sql標準的4中隔離級 別，預設為RR級別，同時，使用一種被稱為next-key locking的策略來避免幻讀現象的產生，除此 之外，Innodb儲存引擎還提供了插入快取，二次寫入，自適應hash索引，預讀等高效能和高可用的 功能。對於表中的資料儲存，Innodb儲存引擎採用聚集的方式，因此每張表的儲存都是按主鍵的順 序進行存放，如果沒有顯示在表定義時指定主鍵，Innodb儲存引擎會為每一行生成一個6位元組的 ROWID，並以此作為主鍵。

3.2 myisam儲存引擎 myisam儲存引擎不支援事務。支援全文索引，主要面向一些OLAP資料應用，mysql5.5版本之前 myisam儲存引擎是預設的儲存引擎，資料庫系統與檔案系統很大的一個不同之處在於對事務的支 持，然而myisam儲存引擎是不支援事務的，這個也不是很難理解，試想一下使用者是否在所有的應用 中都需要事務呢？在資料倉儲中，如果沒有ETL這些操作，只是簡單的報表查詢是否還需要事務的支 持呢？此外，Myisam儲存引擎的另一個與眾不同的地方是它的緩衝池只快取索引檔案，而不是緩衝 資料檔案，這一點和大多數的資料庫都非常不同。 Myisam儲存引擎表由MYD和MYI組成，MYD用來存放資料檔案，MYI用來存放索引檔案，可以透過使用 myisampack工具進一步壓縮資料檔案，因為myisampack工具使用赫夫曼編碼靜態演算法來壓縮資料， 因此使用myisampack工具壓縮後的表是隻讀的，也可以透過myisampack來解壓資料檔案 在mysql5.0版本之前，myisam預設支援表大小為4GB，如果需要支援大於4GB的myisam表時，則需要 指定MAX_WORS和AVG_ROW_LENGTH屬性，從mysql5.0版本開始，myisam預設支援256GB的表資料，這足 夠滿足一般的需求

MySQL 整個查詢執行過程，總的來說分為 5 個步驟 :

1. 客戶端向 MySQL 伺服器傳送一條查詢請求 2. 伺服器首先檢查查詢快取，如果命中快取，則立刻返回儲存在快取中的結果，否則進入下一階段 3. 伺服器進行 SQL解析、預處理、再由最佳化器生成對應的執行計劃 4. MySQL 根據執行計劃，呼叫儲存引擎的 API來執行查詢 5. 將結果返回給客戶端，同時快取查詢結果
客戶端/服務端通訊協議 MySQL客戶端/服務端通訊協議 是 “半雙工” 的，在任一時刻，要麼是伺服器向客戶端傳送資料，要麼是客 戶端向伺服器傳送資料，這兩個動作不能同時發生。一旦一端開始傳送訊息，另一端要接收完整個訊息才 能響應它，所以無法也無須將一個訊息切成小塊獨立傳送，也沒有辦法進行流量控制。客戶端用一個單獨 的資料包將查詢請求傳送給伺服器，所以當查詢語句很長的時候，需要設定 max_allowed_packet引數，如 果查詢實在是太大，服務端會拒絕接收更多資料並丟擲異常。與之相反的是，伺服器響應給使用者的資料通 常會很多，由多個資料包組成。但是當伺服器響應客戶端請求時，客戶端必須完整的接收整個返回結果， 而不能簡單的只取前面幾條結果，然後讓伺服器停止傳送。因而在實際開發中，儘量保持查詢簡單且只返 回必需的資料，減小通訊間資料包的大小和數量是一個非常好的習慣，這也是查詢中儘量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一 查詢快取 在解析一個查詢語句前，如果查詢快取是開啟的，那麼 MySQL 會檢查這個查詢語句是否命中查詢快取中的 資料。如果當前查詢恰好命中查詢快取，在檢查一次使用者許可權後直接返回快取中的結果。這種情況下，查 詢不會被解析，也不會生成執行計劃，更不會執行。MySQL將快取存放在一個引用表 (不要理解成table，可 以認為是類似於 HashMap 的資料結構)，透過一個雜湊值索引，這個雜湊值透過查詢本身、當前要查詢的 資料庫、客戶端協議版本號等一些可能影響結果的資訊計算得來。所以兩個查詢在任何字元上的不同 (例如 : 空格、註釋)，都會導致快取不會命中

如果查詢中包含任何使用者自定義函式、儲存函式、使用者變數、臨時表、MySQL庫中的系統表，其查詢結果 都不會被快取。比如函式 NOW() 或者 CURRENT_DATE() 會因為不同的查詢時間，返回不同的查詢結果，再 比如包含 CURRENT_USER 或者 CONNECION_ID() 的查詢語句會因為不同的使用者而返回不同的結果，將這樣 的查詢結果快取起來沒有任何的意義 MySQL 查詢快取系統會跟蹤查詢中涉及的每個表，如果這些表 (資料或結構) 發生變化，那麼和這張表相關 的所有快取資料都將失效。正因為如此，在任何的寫操作時，MySQL必須將對應表的所有快取都設定為失 效。如果查詢快取非常大或者碎片很多，這個操作就可能帶來很大的系統消耗，甚至導致系統僵死一會 兒，而且查詢快取對系統的額外消耗也不僅僅在寫操作，讀操作也不例外 : 1. 任何的查詢語句在開始之前都必須經過檢查，即使這條 SQL語句 永遠不會命中快取 2. 如果查詢結果可以被快取，那麼執行完成後，會將結果存入快取，也會帶來額外的系統消耗 基於此，並不是什麼情況下查詢快取都會提高系統效能，快取和失效都會帶來額外消耗，特別是寫密集型 應用，只有當快取帶來的資源節約大於其本身消耗的資源時，才會給系統帶來效能提升。可以嘗試開啟查 詢快取，並在資料庫設計上做一些最佳化 : 1. 用多個小表代替一個大表，注意不要過度設計 2. 批次插入代替迴圈單條插入 3. 合理控制快取空間大小，一般來說其大小設定為幾十兆比較合適 4. 可以透過 SQL_CACHE 和 SQL_NO_CACHE 來控制某個查詢語句是否需要進行快取 注 : SQL_NO_CACHE 是禁止快取查詢結果，但並不意味著 cache 不作為結果返回給 query，之前的 快取結果之後也可以查詢到

可以在 SELECT 語句中指定查詢快取的選項，對於那些肯定要實時的從表中獲取資料的查詢，或者對於那些 一天只執行一次的查詢，都可以指定不進行查詢快取，使用 SQL_NO_CACHE 選項。對於那些變化不頻繁的 表，查詢操作很固定，可以將該查詢操作快取起來，這樣每次執行的時候不實際訪問表和執行查詢，只是 從快取獲得結果，可以有效地改善查詢的效能，使用 SQL_CACHE 選項

檢視開啟快取情況

對於查詢快取的一些操作 FLUSH QUERY CACHE : 清理查詢快取記憶體碎片 RESET QUERY CACHE : 從查詢快取中移出所有查詢 FLUSH TABLES : 關閉所有開啟的表，同時該操作將會清空查詢快取中的內容 查詢最佳化 經過前面的步驟生成的語法樹被認為是合法的了，並且由最佳化器將其轉化成查詢計劃。多數情況下，一條 查詢可以有很多種執行方式，最後都返回相應的結果。最佳化器的作用就是找到這其中最好的執行計劃。 MySQL使用基於成本的最佳化器，它嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的 一個。在 MySQL 可以透過查詢當前會話的 last_query_cost 的值來得到其計算當前查詢的成本 mysql> SELECT * FROM p_product_fee WHERE total_price BETWEEN 580000 AND 680000; mysql> SHOW STATUS LIKE ‘last_query_cost’; # 顯示要做多少頁的隨機查詢才能得到最後一查 詢結果， 這個結果是根據一些列的統計資訊計算得來的，這些統計資訊包括 : 每張表或者索引的 頁面個數、索引的基數、索引和資料行的長度、索引的分佈情況等等 有非常多的原因會導致 MySQL 選擇錯誤的執行計劃，比如統計資訊不準確、不會考慮不受其控制的操作成 本(使用者自定義函式、儲存過程)、MySQL認為的最優跟我們想的不一樣 (我們希望執行時間儘可能短，但 MySQL 值選擇它認為成本小的，但成本小並不意味著執行時間短) 等等

MySQL的查詢最佳化器是一個非常複雜的部件，它使用了非常多的最佳化策略來生成一個最優的執行計劃 :
1. 重新定義表的關聯順序 (多張表關聯查詢時，並不一定按照 SQL 中指定的順序進行，但有一些技巧可 以指定關聯順序) 2. 最佳化 MIN() 和 MAX()函式 (找某列的最小值，如果該列有索引，只需要查詢 B+Tree索引 最左端，反之 則可以找到最大值) 3. 提前終止查詢 (比如 : 使用 Limit 時，查詢到滿足數量的結果集後會立即終止查詢) 4. 最佳化排序 (在老版本 MySQL 會使用兩次傳輸排序，即先讀取行指標和需要排序的欄位在記憶體中對其排 序，然後再根據排序結果去讀取資料行，而新版本採用的是單次傳輸排序，也就是一次讀取所有的數 據行，然後根據給定的列排序。對於I/O密集型應用，效率會高很多) 查詢執行引擎 在完成解析和最佳化階段以後，MySQL會生成對應的執行計劃，查詢執行引擎根據執行計劃給出的指令逐步 執行得出結果。整個執行過程的大部分操作均是透過呼叫儲存引擎實現的介面來完成，這些介面被稱為 handler API。查詢過程中的每一張表由一個 handler 例項表示。實際上，MySQL在查詢最佳化階段就為每一 張表建立了一個 handler例項，最佳化器可以根據這些例項的介面來獲取表的相關資訊，包括表的所有列名、 索引統計資訊等。儲存引擎介面提供了非常豐富的功能，但其底層僅有幾十個介面，這些介面像搭積木一 樣完成了一次查詢的大部分操作 返回結果給客戶端 查詢執行的最後一個階段就是將結果返回給客戶端。即使查詢不到資料，MySQL 仍然會返回這個查詢的相 關資訊，比如該查詢影響到的行數以及執行時間等。如果查詢快取被開啟且這個查詢可以被快取，MySQL 也會將結果存放到快取中。結果集返回客戶端是一個增量且逐步返回的過程。有可能 MySQL 在生成第一條 結果時，就開始向客戶端逐步返回結果集。這樣服務端就無須儲存太多結果而消耗過多記憶體，也可以讓客 戶端第一時間獲得返回結果。需要注意的是，結果集中的每一行都會以一個滿足客戶端/伺服器通訊協議的 資料包傳送，再透過 TCP協議 進行傳輸，在傳輸過程中，可能對 MySQL 的資料包進行快取然後批次傳送

Mysql 物理檔案

1. 資料庫的資料儲存檔案
2. 慢查詢日誌
3. 錯誤日誌與二進位制檔案
4. 二進位制檔案基本操作
5. 使用二進位制檔案恢復資料

1. 資料庫的資料儲存檔案 MySQL資料庫會在data目錄下面簡歷一個以資料庫為名的資料夾，用來儲存資料庫中的表檔案資料。不同 的資料庫引擎，每個表的副檔名也不一樣 ，例如： MyISAM用“.MYD”作為副檔名，Innodb用“.ibd”， Archive 用“.arc”，CSV 用“.csv “.FRM”檔案 無論是那種儲存引擎，建立表之後就一定會生成一個以表明命名的’.frm’檔案。frm檔案主要存放與表相關的 資料資訊，主要包括表結構的定義信 息。當資料庫崩潰時，使用者可以透過frm檔案來恢復資料表結構。 “.MYD”檔案
“.MYD”檔案是MyISAM儲存引擎專用，存放MyISAM表的資料。每一個MyISAM表都會有一個“.MYD”檔案與 之對應，同樣存放於所屬資料庫的資料夾 下， 和“.frm”檔案在一起。 “.MYI”檔案 “.MYI”檔案也是專屬於MyISAM儲存引擎的，主要存放MyISAM表的索引相關資訊。對於MyISAM儲存來說， 可以被cache 的內容主要就是來源 於“.MYI”檔案中。 每一個MyISAM表對應一個“.MYI”檔案，存放於位置 和“.frm”以及“.MYD”一樣。 “.ibd”檔案與”.ibdata”檔案 這兩種檔案都是存放Innodb資料的檔案，之所以有兩種檔案來存放Innodb的資料（包括索引），是因為 Innodb的資料儲存方式能夠透過配置來決 定是使用共享 表空間存放儲存資料，還是獨享表空間存放儲存數 據。獨享表空間儲存方式使用“.ibd”檔案來存放資料，且每個表一個“.ibd”檔案 ，檔案存放在和MyISAM資料 相 同的位置。如果選用共享儲存表空間來存放資料，則會使用ibdata檔案來存放，所有表共同使用一個 （或者多個， 可自行配置）ibdata檔案。 ibdata檔案可以透過innodb_data_home_dir（資料存放目錄）和innodb_data_file_path(配置每個檔案的名稱) 兩個引數配置組成 innodb_data_file_path中可以一次配置多個ibdata檔案

innodb_data_file_path中可以一次配置多個ibdata檔案 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方 式

共享表空間以及獨佔表空間都是針對資料的儲存方式而言的。 共享表空間: 某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中。 獨佔表空間: 每一個表都將會生成以獨立的檔案方式來進行儲存，每一個表都有一個.frm表描述檔案，還有 一個.ibd檔案。其中這個檔案包括了 單獨一個表的資料 內容以及索引內容。

兩者對比 （1.）共享表空間： 優點： 可以放表空間分成多個檔案存放到各個磁碟上。資料和檔案放在一起方便管理。 缺點： 所有的資料和索引存放到一個檔案中，多個表及索引在表空間中混合儲存，這樣對於一個表做了大 量刪除操作後表空間中將會有大量的空 隙，特別是對於統計分 析，日值系統這類應用最不適合用共享表空 間。 （2.）獨立表空間： 優點: 1. 每個表都有自已獨立的表空間。 2. 每個表的資料和索引都會存在自已的表空間中。 3. 可以實現單表在不同的資料庫中移動。 4. 空間可以回收 a) Drop table操作自動回收表空間，如果對於統計分析或是日值表，刪除大量資料後可以透過:altertable TableName engine=innodb;回 縮不用的空間。 b) 對於使用獨立表空間的表，不管怎麼刪除，表空間的碎片不會太嚴重的影響效能，而且還有機會處理。 缺點：單表增加過大，如超過100 個G。 相比較之下，使用獨佔表空間的效率以及效能會更高一點 共享表空間和獨立表空間之間的轉換

Mysql 慢查詢日誌

MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具 體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為 10，意思是執行10S以上的語句。預設情況下，Mysql資料庫並不啟動慢查詢日誌，需要我們手動來設定這 個引數，當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一 定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案，也支援將日誌記錄寫入資料庫表。

慢查詢相關引數

MySQL 慢查詢的相關引數解釋： slow_query_log ：是否開啟慢查詢日誌，1表示開啟，0表示關閉。 log-slow-queries：舊版（5.6以下版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該引數，系統則會 預設給一個預設的檔案host_name-slow.log slow-query-log-file：新版（5.6及以上版本）MySQL資料庫慢查詢日誌儲存路徑。可以不設定該引數，系統 則會預設給一個預設的檔案host_name-slow.log long_query_time ：慢查詢閾值，當查詢時間多於設定的閾值時，記錄日誌。 log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢日誌中（可選項）。 log_output：日誌儲存方式。log_output=’FILE’表示將日誌存入檔案，預設值是’FILE’。 log_output=’TABLE’表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。MySQL資料 庫支援同時兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output=’FILE,TABLE’。日誌記錄到系 統的專用日誌表中，要比記錄到檔案耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲 得更高的系統效能，那麼建議優先記錄到檔案。

###慢查詢日誌配置
預設情況下slow_query_log的值為OFF，表示慢查詢日誌是禁用的，可以透過設定slow_query_log的值來開 啟，如下所示：

使用set global slow_query_log=1開啟了慢查詢日誌只對當前資料庫生效，如果MySQL重啟後則會失效。如 果要永久生效，就必須修改配置檔案my.cnf（其它系統變數也是如此）。

修改my.cnf檔案，增加或修改引數slow_query_log 和slow_query_log_file後，然後重啟MySQL伺服器，如下 所示

那麼開啟了慢查詢日誌後，什麼樣的SQL才會記錄到慢查詢日誌裡面呢？ 這個是由引數long_query_time控 制，預設情況下long_query_time的值為10秒，可以使用命令修改，也可以在my.cnf引數裡面修改。關於運 行時間正好等於long_query_time的情況，並不會被記錄下來。也就是說，在mysql原始碼裡是判斷大於long_query_time，而非大於等於。從MySQL 5.1開始，long_query_time開始以微秒記錄SQL語句執行時間，
之前僅用秒為單位記錄。如果記錄到表裡面，只會記錄整數部分，不會記錄微秒部分。

如上所示，修改了變數long_query_time，但是查詢變數long_query_time的值還是10，難道沒有修改到呢？ 注意：使用命令 set global long_query_time=4修改後，需要重新連線或新開一個會話才能看到修改 值。你用show variables like ‘long_query_time’檢視是當前會話的變數值，你也可以不用重新連線會話，而是 用show global variables like ‘long_query_time’; 如下所示：

在MySQL裡面執行下面SQL語句，然後我們去檢查對應的慢查詢日誌，就會發現類似下面這樣的資訊。

log_output 引數是指定日誌的儲存方式。log_output=’FILE’表示將日誌存入檔案，預設值是’FILE’。 log_output=’TABLE’表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。MySQL資料 庫支援同時兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output=’FILE,TABLE’。日誌記錄到系 統的專用日誌表中，要比記錄到檔案耗費更多的系統資源，因此對於需要啟用慢查詢日誌，又需要能夠獲 得更高的系統效能，那麼建議優先記錄到檔案。

日誌分析工具

在生產環境中，如果要手工分析日誌，查詢、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具 mysqldumpslow 檢視mysqldumpslow的幫助資訊：

本作品採用《CC 協議》，轉載必須註明作者和本文連結