Mysql優化總結(一)

• 2019 年 10 月 3 日
• 筆記

一，前言

​ 數據庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫。

​ MySQL 是一個關係型數據庫管理系統，由瑞典 MySQL AB 公司開發，目前屬於 Oracle 公司。MySQL 是一種關聯數據庫管理系統，關聯數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

• MySQL是開源的，所以不需要支付任何費用。
• MySQL使用標準的SQL數據語言形式。
• MySQL可以處理擁有千萬條記錄的大型數據庫。
• MySQL支持多種語言，如C，PHP，Java等。

　　本篇博客是總結MySQL存儲機制及SQL優化方面的內容。

二，MySQL邏輯架構

​

​ 分析：

• 第一層是大多數基於網絡的客戶端/服務器的工具或者服務都有類似的架構。如連接處理，授權認證等。
• 第二層架構中，大多數的MYSQL的核心服務功能都在這一層，包括查詢解析、分析、優化、緩存以及所有的內置函數（日期時間等），所有跨存儲引擎的功能都在這一層實現：存儲過程、觸發器、視圖等層。
• 第三層包含了存儲引擎。存儲引擎負責Mysql中的數據的存儲和提取。
  Mysql支持各種不同的存儲引擎，每個存儲引擎都有它的優勢和劣勢。服務器通過API和存儲引擎進行通信。這些API接口屏蔽了不同存儲引擎之間的差異，使的這些差異對上層的查詢過程透明。存儲引擎不會解析SQL（InnoDB是個例外，它會解析外鍵定義），不同的存儲引擎之間也不能互相通信，而只是簡單的響應上層服務器的請求。

​ 緩存：當客戶端請求到來時，先去查詢緩存中是否存在數據，沒有再去數據庫中查詢。

分析器：對sql語句的分析，比如SELECT username FROM users; ，分析器分析出先執行FROM users，然後再執行SELECT　username。

​ 優化器：上面只是舉了一個簡單的例子，而優化器作用在於選擇那種執行方式，是執行SQL效率最快的。因為分析器對一條SQL語句會分析出多種不同的結果，而優化器就會對以上的分析結果選擇它認為最優的結果來執行。但是，需要注意的是，優化器認為最優的執行方案，並不一定是我們認為的最優執行方案。因此再SQL語句量較大的情況下，就需要程序員手動對語句進行優化。

​ 下面再來說說MySQL的存儲引擎。

三，存儲引擎

​ 以博主的MySQL作為案例，執行select version();語句可以查看數據庫版本。

​

​ 接着再來看看MySQL都支持哪些存儲引擎。

​

​ 在倒數第二個，可以看出MySQL默認的存儲引擎是InnoDB，再細看Transactions中只有InnoDB是支持事務的，其他存儲引擎都是不支持的。這是在MySQL5.5之後支持的引擎，所以說在5.5之前MySQL是不支持事務的。

​ 最長用的兩種存儲引擎為InnoDB和Myisam，請看兩者之間的區別（MySQL5.6）。

​ 那麼如何選擇兩種存儲引擎，可以從以下幾個方面考慮。

​ 事務：

​ 如果應用需要事務支持，那麼InnoDB是較號的選擇。如果不需要事務，並且主要是select和insert操作，則MyISAM是個不錯的選擇，比如日誌型系統。

​ 備份：

​ 備份的需求也會影響存儲引擎的選擇。如果需要在線熱備份，則選擇InnoDB就是基本的要求。

​ 數據恢復：

​ 數據量比較大的時候，系統崩潰後如何快速恢復是一個需要考慮的額問題。相對而言，MyISAM崩潰後發生損壞的概率比InnoDB高的多，而且恢復也慢，因此即使不需要事務，通常也應該選擇InnoDB引擎。

​ 對於不同存儲引擎的選擇，對數據庫的性能都會帶來較大的影響，除了MySQL默認支持的存儲引擎之外，還有很多第三方存儲引擎。

​ 通常情況下，導致SQL性能下降還有以下幾方面：

• 查詢語句編寫不合理
• 索引失效
• 關聯查詢數據太多
• 服務器的參數設置問題

下面總結一些關於SQL優化方面的內容。

四，數據類型優化

4.1，數據類型的選擇原則。

​ 更小的通常更好：

​ 一般情況選擇可以正確存儲數據的最小數據類型。因為更小的數據類型通常更快，因為佔用磁盤、內存和CPU緩存會越小。

​ 簡單就好：

​ 簡單數據類型的操作通常需要更少的CPU消耗。

​ 盡量避免NULL：

​ 一般情況下最好執行列為NOT NULL，除非特殊需要。因為如果查詢中包含為NULL的列，從SQL優化角度考慮，對創建索引，索引的統計等都會帶來較大的困難，且優化起來也是較為複雜的，因此說盡量避免NULL值。

4.2，具體類型的選擇

​ 1，整數類型：選擇合適的位元組大小作為數據的類型。

• tinyint：8位位元組
• smallint：16位位元組
• mediumint：24位位元組
• int：32位位元組
• bigint：64位位元組
• unsigned屬性：添加unsigned屬性表示該字段不允許負數，正數的上限大致可以提高一倍。
  比如tinyint unsigned可以存儲0-255的範圍。而tinyint是-128~127的範圍。有符號和無符號佔用空間大小相同，具有相同的性能。

int(11)是指定整數類型的寬度，它不會限制值得合法範圍，對於存儲和計算而言，int(1)和int(20)沒什麼區別

​ 2，實數類型

• float：32位
• double：64位
• decimal：decimel需要額外的空間和計算開銷，所以應該盡量只對小數進行精準計算時才使用decimel，例如存儲金額。

​ 3，字符串類型

• varchar：是可變長的字符串，它比定長更節省空間。varchar需要使用1~2個額外位元組記錄字符串的長度。varchar節省了存儲空間，所以對性能也有幫助，
  但是由於長度可變，在update時可能使行變得比原來更長，這就導致需要進行額外的工作。至於如何進行空間增長取決於不同的存儲引擎。當字符串列的最大長度比平均長度要大很多，並且列的更新很少時比較適合使用varchar。
• char：定長字符串，mysql根據定義的字符串長度分配足夠的空間。
  char非常適合存儲很短的字符串，或者值得長度都很接近的字段。例如char非常適合存放密碼的md5值，因為這是一個定長的值。對於經常變更的字段，使用char也更為合適，因為定長的char類型不容易產生碎片。對於非常短的列，存儲空間也更有優勢，比如char(1)只會佔用一個位元組，而varchar(1)會用到兩個位元組，因為還有一個位元組用來記錄varchar的長度。
• blob和text：兩者都是用於存儲很大的數據而設計的字符串數據類型，
  分別採用二進制和字符的方式存儲。

varchar(5)和varchar(200)存儲’hello’的空間開銷是一樣的。但是varchar(5)對性能提升有很大的優勢。更長的列會消耗更多的內存，因為mysql通常會分配固定大小的內存塊來保存內部值。尤其是使用內存臨時表進行排序等操作時會特別糟糕。所以最好的策略是只分配真正需要的空間

​ 4，時間和日期類型

• datetime：能保存大範圍的值，從1001年到9999年，精度為秒。它把日期和時間封裝到YYYYMMDDHHMMSS的整數中，使用8個位元組的存儲空間 。
• timestamp：保存了從1970年1月1日以來的毫秒數，timestamp只使用了4個位元組的存儲空間，因此它的範圍比datetime小的多；但是只能表示從1970年到2038年。另外timestamp也依賴於時區。

除了特殊行為之外，通常應該盡量使用timestamp，
因為它比datetime空間效率更高。

五，總結

​ 下面將總結索引方面的優化，包括B-Tree和B+Tree兩種數據結構的原理，由於放在一起篇幅較長，所以就分成兩個博客，這些內容會在下一篇博客中進行詳細總結。

​ 以上內容均是自主學習總結，如有不適之處，歡迎留言指正。

感謝閱讀！