mysql 優化框架是怎樣的

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

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 優化框架是怎樣的，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。