共計 4517 個字符，預計需要花費 12 分鐘才能閱讀完成。

本篇文章給大家分享的是有關 mysql 優化框架是怎樣的，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

MySQL 優化框架

1. SQL 語句優化
2. 索引優化
3. 數據庫結構優化
4. InnoDB 表優化
5. MyISAM 表優化
6. Memory 表優化
7. 理解查詢執行計劃
8. 緩沖和緩存
9. 鎖優化
10. MySQL 服務器優化
11. 性能評估
12. MySQL 優化內幕

MySQL 優化需要在三個不同層次上協調進行：MySQL 級別、OS 級別和硬件級別。MySQL 級別的優化包括表優化、查詢優化和 MySQL 服務器配置優化等，而 MySQL 的各種數據結構又最終作用于 OS 直至硬件設備，因此還需要了解每種結構對 OS 級別的資源的需要并最終導致的 CPU 和 I / O 操作等，并在此基礎上將 CPU 及 I / O 操作需要盡量降低以提升其效率。

數據庫層面的優化著眼點：
1、是否正確設定了表結構的相關屬性，尤其是每個字段的字段類型是否為最佳。同時，是否為特定類型的工作組織使用了合適的表及表字段也將影響系統性能，比如，數據頻繁更新的場景應該使用較多的表而每張表有著較少字段的結構，而復雜數據查詢或分析的場景應該使用較少的表而每張表較多字段的結構等。
2、是否為高效進行查詢創建了合適的索引。
3、是否為每張表選用了合適的存儲引擎，并有效利用了選用的存儲引擎本身的優勢和特性。
4、是否基于存儲引擎為表選用了合適的行格式(row format)。例如，壓縮表在讀寫操作中會降低 I / O 操作需求并占用較少的磁盤空間，InnoDB 支持讀寫應用場景中使用壓縮表，但 MyISAM 僅能在讀環境中使用壓縮表。
5、是否使用了合適的鎖策略，如在并發操作場景中使用共享鎖，而對較高優先級的需求使用獨占鎖等。同時，還應該考慮存儲引擎所支持的鎖類型。
6、是否為 InnoDB 的緩沖池、MyISAM 的鍵緩存以及 MySQL 查詢緩存設定了合適大小的內存空間，以便能夠存儲頻繁訪問的數據且又不會引起頁面換出。

操作系統和硬件級別的優化著眼點：
1、是否為實際的工作負載選定了合適的 CPU，如對于 CPU 密集型的應用場景要使用更快速度的 CPU 甚至更多數量的 CPU，為有著更多查詢的場景使用更多的 CPU 等。基于多核以及超線程 (hyperthreading) 技術，現代的 CPU 架構越來越復雜、性能也越來越強了，但 MySQL 對多 CPU 架構的并行計算能力的利用仍然是有著不太盡如人意之處，尤其是較老的版本如 MySQL 5.1 之前的版本甚至無法發揮多 CPU 的優勢。不過，通常需要實現的 CPU 性能提升目標有兩類：低遲延和高吞吐量。低延遲需要更快速度的 CPU，因為單個查詢只能使用一顆；而需要同時運行許多查詢的場景，多 CPU 更能提供更好的吞吐能力，然而其能否奏效還依賴于實際工作場景，因為 MySQL 尚不能高效的運行于多 CPU，并且其對 CPU 數量的支持也有著限制。一般來說，較新的版本可以支持 16 至 24 顆 CPU 甚至更多。
2、是否有著合適大小的物理內存，并通過合理的配置平衡內存和磁盤資源，降低甚至避免磁盤 I /O。現代的程序設計為提高性能通常都會基于局部性原理使用到緩存技術，這對于頻繁操作數據的數據庫系統來說尤其如此——有著良好設計的數據庫緩存通常比針對通用任務的操作系統的緩存效率更高。緩存可以有效地延遲寫入、優化寫入，但并能消除寫入，并綜合考慮存儲空間的可擴展性等，為業務選擇合理的外部存儲設備也是非常重要的工作。
3、是否選擇了合適的網絡設備并正確地配置了網絡對整體系統系統也有著重大影響。延遲和帶寬是網絡連接的限制性因素，而常見的網絡問題如丟包等，即是很小的丟包率也會贊成性能的顯著下降。而更重要的還有按需調整系統中關網絡方面的設置，以高效處理大量的連接和小查詢。
4、是否基于操作系統選擇了適用的文件系統。實際測試表明大部分文件系統的性能都非常接近，因此，為了性能而苦選文件系統并不劃算。但考慮到文件系統的修復能力，應該使用日志文件系統如 ext3、ext4、XFS 等。同時，關閉文件系統的某些特性如訪問時間和預讀行為，并選擇合理的磁盤調度器通常都會給性能提升帶來幫助。
5、MySQL 為響應每個用戶連接使用一個單獨的線程，再加內部使用的線程、特殊目的線程以及其它任何由存儲引擎創建的線程等，MySQL 需要對這些大量線程進行有效管理。Linux 系統上的 NPTL 線程庫更為輕量級也更有效率。MySQL 5.5 引入了線程池插件，但其效用尚不明朗。

使用 InnoDB 存儲引擎最佳實踐：
1、基于 MySQL 查詢語句中最常用的字段或字段組合創建主鍵，如果沒有合適的主鍵也最好使用 AUTO_INCRMENT 類型的某字段為主鍵。
2、根據需要考慮使用多表查詢，將這些表通過外鍵建立約束關系。
3、關閉 autocommit。
4、使用事務 (START TRANSACTION 和 COMMIT 語句) 組合相關的修改操作或一個整體的工作單元，當然也不應該創建過大的執行單元。
5、停止使用 LOCK TABLES 語句，InnoDB 可以高效地處理來自多個會話的并發讀寫請求。如果需要在一系列的行上獲取獨占訪問權限，可以使用 SELECT … FOR UPDATE 鎖定僅需要更新的行。
6、啟用 innodb_file_per_table 選項，將各表的數據和索引分別進行存放。
7、評估數據和訪問模式是否能從 InnoDB 的表壓縮功能中受益(在創建表時使用 ROW_FORMAT=COMPRESSED 選項)，如果可以，則應該啟用壓縮功能。

EXPLAIN 語句解析：
id:SELECT 語句的標識符，一般為數字，表示對應的 SELECT 語句在原始語句中的位置。沒有子查詢或聯合的整個查詢只有一個 SELECT 語句，因此其 id 通常為 1。在聯合或子查詢語句中，內層的 SELECT 語句通常按它們在原始語句中的次序進行編號。但 UNION 操作通常最后會有一個 id 為 NULL 的行，因為 UNION 的結果通常保存至臨時表中，而 MySQL 需要到此臨時表中取得結果。

select_type:
即 SELECT 類型，有如下值列表：
SIMPLE：簡單查詢，即沒有使用聯合或子查詢；
PRIMARY：UNION 的最外圍的查詢或者最先進行的查詢；
UNION：相對于 PRIMARY，為聯合查詢的第二個及以后的查詢；
DEPENDENT UNION：與 UNION 相同，但其位于聯合子查詢中(即 UNION 查詢本身是子查詢)；
UNION RESULT：UNION 的執行結果；
SUBQUERY：非從屬子查詢，優化器通常認為其只需要運行一次；
DEPENDENT SUBQUERY：從屬子查詢，優化器認為需要為外圍的查詢的每一行運行一次，如用于 IN 操作符中的子查詢；
DERIVED：用于 FROM 子句的子查詢，即派生表查詢；

table:
輸出信息所關系到的表的表名，也有可能會顯示為如下格式：
：id 為 M 和 N 的查詢執行聯合查詢后的結果；
：id 為 N 的查詢執行的結果集；

type:
MySQL 官方手冊中解釋 type 的作用為“type of join(聯結的類型)”，但其更確切的意思應該是“記錄 (record) 訪問類型”，因為其主要目的在于展示 MySQL 在表中找到所需行的方式。通常有如下所示的記錄訪問類型：
system: 表中僅有一行，是 const 類型的一種特殊情況；
const：表中至多有一個匹配的行，該行僅在查詢開始時讀取一次，因此，該行此字段中的值可以被優化器看作是個常量 (constant)；當基于 PRIMARY KEY 或 UNIQUE NOT NULL 字段查詢，且與某常量進行等值比較時其類型就為 const，其執行速度非常快；
eq_ref：類似于 const，表中至多有一個匹配的行，但比較的數值不是某常量，而是來自于其它表；ed_ref 出現在 PRIMARY KEY 或 UNIQUE NOT NULL 類型的索引完全用于聯結操作中進行等值(=) 比較時；這是除了 system 和 const 之外最好的訪問類型；
ref：查詢時的索引類型不是 PRIMARY KEY 或 UNIQUE NOT NULL 導致匹配到的行可能不惟一，或者僅能用到索引的左前綴而非全部時的訪問類型；ref 可被用于基于索引的字段進行 = 或 = 操作；
fulltext：用于 FULLTEXT 索引中用純文本匹配的方法來檢索記錄。
ref_or_null：類似于 ref，但可以額外搜索 NULL 值；
index_merge：使用“索引合并優化”的記錄訪問類型，相應地，其 key 字段 (EXPLAIN 的輸出結果) 中會出現用到的多個索引，key_len 字段中會出現被使用索引的最長長度列表；將多個“范圍掃描 (range scan)”獲取到的行進行合并成一個結果集的操作即索引合并(index merge)。
unique_subquery：用于 IN 比較操作符中的子查詢中進行的“鍵值惟一”的訪問類型場景中，如 value IN (SELECT primary_key FROM single_table WHERE some_expr)；
index_subquery：類似于 unique_subquery，但子查詢中鍵值不惟一；
range：帶有范圍限制的索引掃描，而非全索引掃描，它開始于索引里的某一點，返回匹配那個值的范圍的行；相應地，其 key 字段(EXPLAIN 的輸出結果) 中會輸出所用到的索引，key_len 字段中會包含用到的索引的最長部分的長度；range 通常用于將索引與常量進行 =、、、=、、=、IS NULL、=、BETWEEN 或 IN()類的比較操作中；
index：同全表掃描 (ALL)，只不過是按照索引的次序進行而不行的次序；其優點是避免了排序，但是要承擔按索引次序讀取整個表的開銷，這意味著若是按隨機次序訪問行，代價將非常大；
ALL：“全表掃描”的方式查找所需要的行，如果第一張表的查詢類型(EXPLAIN 的輸出結果) 為 const，其性能可能不算太壞，而第一張表的查詢類型為其它結果時，其性能通常會非常差；

Extra:
Using where：MySQL 服務器將在存儲引擎收到數據后進行“后過濾(post-filter)”以限定發送給下張表或客戶端的行；如果 WHERE 條件中使用了索引列，其讀取索引時就由存儲引擎檢查，因此，并非所有帶有 WHERE 子句的查詢都會顯示“Using where”；
Using index：表示所需要的數據從索引就能夠全部獲取到，從而不再需要從表中查詢獲取所需要數據，這意味著 MySQL 將使用覆蓋索引；但如果同時還出現了 Using where，則表示索引將被用于查找特定的鍵值；
Using index for group-by：類似于 Using index，它表示 MySQL 可僅通過索引中的數據完成 GROUP BY 或 DISTINCT 類的查詢；
Using filesort：表示 MySQL 會對結果使用一個外部索引排序，而不是從表里按索引次序來讀取行。

以上就是 mysql 優化框架是怎樣的，丸趣 TV 小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注丸趣 TV 行業資訊頻道。