MySQL的使用及優化

前言

最近聽了公司里的同事做的技術分享，然後覺得對自己還是挺有幫助的。都是一些日常需要注意的地方，我們目前在開發過程中，其實用不到MySQL太深的內容的。只是能適用我們日常開發的知識就可以了。所以我將自己的理解和學習總結也寫出來，供大家一起分享。

大體分四部分：

• 資料庫優化概述

• 資料庫表設計

• 索引原理及優化

• 可擴展性設計

資料庫優化概述

優化金三角

做資料庫優化一般是由以下幾種方式：

成本和效果成反比。

伺服器硬體

增強伺服器的硬體方式不同的方式：例如增加磁碟配置（SSD，PCRE），增大內存，增加CPU配置等。增強伺服器硬體在一定的階段內確實可以達到不錯的效果，但是並不是長久之計，如果不注重下面的三種策略，一味的增加硬體配置，會適得其反。

系統及資料庫配置

隨著系統硬體的不斷更新迭代，資料庫的配置也是不斷變化的。例如以前的機械硬碟性能並不很好，所以資料庫的配置並沒有設置太高。當伺服器普遍的都是SSD後資料庫的系統配配置也是可以隨之變化的。另外隨著業務的變化以及數據量的增長，資料庫的配置也是隨著變化的。但是這部分的配置帶來的效果並不太明顯，和增加伺服器硬體類似。

資料庫表設計及規劃

資料庫的表在設計之初就應該考慮好了以後的規劃。不然當發現資料庫產生瓶頸了再去優化，成本會很高。所以也需要開發人員能通過對業務的深刻理解來對資料庫做好長遠的規劃。

SQL及索引優化

對SQL語句以及索引的優化可以說是成本最低的了，效果也是非常顯著的。

這四部分內容，總有人覺得SQL及索引優化是最重要的，但是本人覺得最重要的是資料庫表設計以及規劃，如果能根據業務將表設計好了，根本是不需要進行索引優化的。如果資料庫沒有規劃好，再好的DBA給你做SQL優化，效果也是杯水車薪的。

MySQL邏輯架構

上面這幅圖是MySQL的基本邏輯架構圖，主要分為四層。

連接層

通過MySQL的連接地址去訪問MySQL的資料庫，以及對訪問信息的校驗。

服務層

對SQL語句的校驗，以及對SQL的優化和優化策略選擇，最後發送到執行器去執行SQL。還包括MySQL的查詢緩存也在這一層。

引擎層

MySQL是插件式存儲引擎，最終將數據存到硬碟時不同的引擎有不同的組織方式。上面列出了一些引擎，常見InnoDB，MyISAM等，只要符合MySQL的介面規範，MySQL是支持自定義的引擎。

存儲系統層

這部分主要是數據存儲，將數據存到磁碟，磁碟的IO讀寫等過程。

資料庫表設計

引擎的選擇

請使用InnoDB存儲引擎，慎用MyISAM引擎。

上圖是InnoDB引擎和MyISAM引擎的一些區別對比。

ACID事務支持：由於我們介紹這次介紹MySQL的時候是以OLTP（on-line transaction processing：聯機事務處理）為主的，而非OLAP（On-Line Analytical Processing：聯機分析處理），所以事務處理是很重要的，這也就是為什麼強烈要求使用InnoDB引擎的一個原因。

鎖粒度：MyISAM支持的鎖粒度是表級鎖，表級鎖的意思是指當一張數據表被鎖住後，其他的對這張表的操作（DML）都要等著前一個鎖釋放了才可以執行。所以當並發量高時用戶體驗是很不好的。而InnoDB引擎的行級鎖，只是對錶的一部分數據進行加鎖，所以能很好的支持並發，降低了對同一張表的操作衝突。

外檢約束：雖然InnoDB支持外鍵，MyISAM不支持外鍵。但是也不建議在日常的使用過程中用外鍵，因為每次操作外鍵時都要去檢查一下外鍵關聯的數據。

全文索引：InnoDB引擎不支持全文索引，但是MyISAM支持。但是在資料庫中建立全文索引其實並不是什麼好的策略，還是建議如果需要建立全文索引的時候考慮使用搜索引擎工具如：ElasticSearch，Solr等。

崩潰安全：InnoDB支持崩潰安全，MyISAM是不支持的崩潰安全的。

什麼是崩潰安全呢？

舉個例子：當一台伺服器的上的資料庫突然掛了，或是伺服器崩潰了，甚至是突然斷電了。這個時候如果MySQL使用的是InnoDB引擎，那麼在資料庫恢復後或是重新通電後，會執行崩潰恢復，就是未執行完的事務會繼續執行，該回滾的回滾，該執行完的執行完，能確保數據的一致性。但是如果MySQL是使用的MyISAM引擎，那麼首先MyISAM不支持事務，所以會造成數據的不一致性，而且如果在對錶進行操作時斷電，導致沒有正確的關閉表，還會導致存儲文件的損壞，在恢復通電後對這張表的任何讀寫操作都不能執行了。而且就算手動恢複數據也是比較麻煩的。

表設計-規劃

在設計表時要遵循幾個基本原則：

1. 線上業務盡量避免使用外鍵、存儲過程、分區表、觸發器等。

2. 不在資料庫中存儲圖片、文件等大數據。

3. 盡量避免使用TEXTBLOB等類型的大欄位。

4. 拆分大欄位和訪問頻率低的欄位，分離冷熱數據。

5. 不同的業務使用不同的資料庫，禁止混合使用。

第一條基本原則，是為了防止隨著業務的發展以後如果數據量大到一定程度了需要分表時，拆分帶有這些特性的表時成本是非常大的。

第二條、第三條 、第四條都是說大欄位或大文件是不建議存儲到MySQL當中的，因為對這些數據的操作MySQL是有特殊的存儲方式的，性能很差。如果存儲了這些數據後，再有一些排序或者是聚合操作的話會直接在磁碟中建立臨時文件表，普通的欄位類型例如varchar類型的，在有聚合操作時是會在內存中進行臨時存儲的。

第五條原則是要求對業務有長遠的規劃，不同的業務首先要分表，其次要分庫。雖然MySQL的很強大，但是單節點的能力是有限的。所以企業級的資料庫都是分散式的，要為以後業務的增長數據的訪問量增長做好充分的規劃。

欄位類型選擇

VARCHAR(N)：只分配真正需要的空間

例如：使用VARCHAR(5)和VARCHAR(200)存儲"hello"的空間開銷是一樣的，使用更短的列有什麼優勢嗎？

雖然存儲開銷是一樣的，但是如果對這個欄位進行聚合操作（order by、group by等），這個時候是需要先將臨時數據存儲到內存中的，但是申請內存空間時是按照欄位的定義大小來申請的，也就是說VARCHAR(200)申請的內存空間是VARCHAR(5)的40倍。還有一種情況是，當一個表的數據量很大時，要做數據遷移或是大數據分析時，是需要抽取全表數據的，這個時候讀全表數據是無法靠申請內存空間來實現的，MySQL是會在磁碟中建立臨時文件表。並且是按照欄位定義的大小來佔用磁碟空間的，如果一個200G的硬碟，但是表中的數據是50G，在抽取全表數據時會有可能將磁碟佔滿的。

所以，更大的定義列會消耗更多的內存，在使用內存臨時表進行排序或操作時會根據定義的長度進行內存分配。

數值類型vs字元串類型vs時間類型vs枚舉類型

在給欄位選擇類型時，盡量遵循【小而簡單】的原則，但是可以根據可以讀性等因素適當調整。

例如：在存儲時間欄位時，有的人使用int類型（4個位元組），有的人使用datetime(8個位元組)，雖然說佔用的空間小了，但是可讀性也變差了。而且就即使是類型選擇的稍微不太合理，這部分也是可以通過對SQL的優化等操作來減小影響的。

還有就是例如存儲性別的時候，咱們使用tinyint，而不使用枚舉類型，因為如果以後又多了一種類型（），這種操作是需要進行改表的，成本比用tinyint類型大很多。

表欄位個數vs表記錄行數vs表物理文件大小

單個表的欄位數到了一定程度是建議拆表的，但是具體的峰值是根據實際的業務來看的，還有就是一個表的記錄行數也是不建議很多，當到達一定量時再進行聚合操作是性能很差的。當表的數據量很大時增加欄位也是需要消耗成本的，需要copy表中數據然後重新建表，這樣才能保證線上的數據在加欄位時是熱處理。表物理文件的大小也是根據實際需求來考慮是否拆分的，如果表中只是追加操作，而且查詢操作很不頻繁，呢么拆表就可以慢慢考慮。這部分內容不做過多的討論。

索引工作原理及優化

InnoDB表索引結構

上面的圖介紹的是InnoDB的索引結構，分為兩部分聚簇索引和輔助索引。

聚簇索引也是主鍵索引，InnoDB表都是有主鍵的，就算是沒有給表創建主鍵，MySQL也會默認的創建一個主鍵，聚簇索引每一個葉子節點代表的是主鍵的鍵值，最末端指向的是主鍵所在的那行數據記錄。

輔助索引也是非聚簇索引，輔助索引就是日常表中除了主鍵以外的其他索引，每個葉子節點都代表的是索引的欄位值，最末端指向的是索引值的主鍵。

在創建索引時需要注意，常用的有int類型，bigInt類型。首先這些類型是佔用位元組數少，並且是有序的。在建立輔助索引時能節省空間，因為每個輔助索引記錄後面都帶著一個主鍵索引，如果主鍵是uuid或是MD5值一類的，那麼在建立輔助索引後會佔用很大的磁碟空間，並且在按照主鍵去查詢的時候主鍵值是要載入到內存中的，所以綜合考慮還是int、bigInt更好一些。

例如下圖的例子。

主鍵之外將name欄位設置為索引。索引類型是varchar並且每個索引記錄後面都跟著一個主鍵值，這個索引其實是很耗性能的。

MyISAM表索引結構

相對於InnoDB來說，MyISAM引擎的主鍵也是指向主鍵所在的記錄的，但是輔助索引就不一樣了，輔助索引最終也是指向數據記錄的。MyISAM引擎的在數據存儲的物理位置上有一個物理位置的編號。然後無論是主鍵還是輔助索引都是指向這個編號的。

如下圖的例子所示：

索引優化

主鍵原則（InnoDB）

表必須有主鍵。

不使用更新頻繁的列。

忌用字元串列做主鍵。

不使用UUID/MD5等生成的隨機數做主鍵。

推薦用獨立於業務的AUTO_INCREMENT列或全局ID生成器做代理主鍵。

表必須有主鍵，即使沒有主鍵InnoDB也會自動生成一個，如果使用頻繁更新的列做主鍵，那主鍵的B+樹不是一個穩定的結構，很耗磁碟開銷，以及主鍵性能大大降低，上面已經說了字元串類型做主鍵會佔用大量磁碟空間。不適用隨機數做主鍵，是為了防止有磁碟空洞，產生不連續的空間。

最左前綴

目前的MySQL確實是有最左前綴的規則，即a_b_c索引，查詢b和c時不走聯合索引，但是隨著MySQL的不斷發展，現在又出現了一種叫做「索引下推」的概念，雖然不是代表著b和c使用時就能走索引了，但是看趨勢可能以後會出現這種優化。最左前綴內容就不做過多的介紹了。

覆蓋索引

首先介紹一下，回表的概念，InnoDB引擎的表是必須有主鍵的，但是當存在輔助索引時，輔助索引在索引記錄中存儲的是主鍵值。當通過二級索引去查詢非輔助索引包含的欄位時，是先根據輔助索引查詢到相應的主鍵值，然後再根據主鍵值去查詢到相應的記錄。這個查詢兩次的過程就是回表。如果一個聯合索引由a、b、c三個欄位組成，那麼「select b,c from test where a = 100」這個SQL就不需要產生回表的，因為只查詢聯合索引就能得到想要的結果了。

謹慎合理添加索引

改善查詢效率

避免排序

數據率重

減慢插入和更新的效率。

索引添加的目的就是為了改善查詢效率，添加索引時要避免出現using filesort，出現using filesort是指，當查詢操作中包含order by，無法利用索引完成排序操作時，MySQL優化器不得不選擇相應的排序演算法來實現，數據較少時從內存排序，否則從磁碟排序。

舉個例子：

還是以上面的tb_user_test表為例，"select b,c from tb_user_test where a=100 and b=200 order by c desc;"這個SQL語句在執行的時候如果tb_user_test沒有idx_a_b_c這個聯合索引那麼執行計劃是這樣的

注意Extra列的值，Using filesort 出現了，這說明MySQL將數據重新排序了。

如果將欄位a和b創建了聯合索引後的執行計劃是這樣的

還是會有Using filesort。

將欄位a和b還有c創建了聯合索引後的執行計劃是這樣的

這次沒有Using filesort了，創建索引時注意避免出現重排序問題。

數據慮重是指在使用distinct或者group by的時候也是可以使用索引進行優化查詢的。distinct或group by的列創建索引能提示查詢效率。

索引雖然能改善查詢效率，但是代價是犧牲了插入和更新的效率。

索引數據控制

單張表索引數量建議不超過5個。

單個索引中的欄位建議不超過5個。

字元串適度使用前綴索引。

索引不是越多越好，能不添加的索引盡量不要添加。

索引的控制只是一些建議，並不是強制要求。

索引禁忌

不在低區分度的列上建立索引，例如：「性別」。

盡量避免%前導查詢，如like "%ab"。

盡量避免負向查詢，如not in /like。

避免全表掃描以及頻繁的回表操作

區分度低的列創建了索引後查詢速度確實提升了，但是當數據量變大後會產生大量的隨機IO和回表查詢。like前綴是不走索引的，索引對負向查詢的支持也不好。

其他幾點需要注意的是，索引的建立要優先保證高頻查詢需求的效率，低頻需求儘可能使用到最左前綴索引。索引也要隨著業務的演進更變化，不是建完索引就完事了。

高效SQL開發

SQL優化--設計基本原則

SQL儘可能簡單，線上儘可能少使用大SQL，使用簡單小SQL。

儘可能少使用存儲過程/觸發器/函數，減少MySQL端的數學運算和邏輯判斷。（不易於擴展）

使用預編譯語句，降低SQL注入概率。

盡量少用select * ，只取需要的數據列。（可降低磁碟I/O，有機會只走複合索引，緩存使用降低。）

SQL優化--隱式轉換

基本原則：where條件比較，欄位類型和傳入值必須保證：數字對數字，字元對字元。

通過下面的例子就可以看出來。

欄位：`remark` varchar(50) NOT NULL COMMENT "備註，默認為空",

MySQL>SELECT id, gift_ code FROM gift Where deal_ id = 640 AND remark=115127; 1 row in set (0.14 sec)
MySQL>SELECT id, gift_ code FROM pool gift Where deal_ id = 640 AND remark="115127" ;1 row in set (0.005 sec)

當remark傳入int類型的值後，查詢時間0.14秒，傳入字元類型後只需要0.005秒。

SQL優化--函數計算

基本原則：不在索引列進行數學運算和函數運算。

索引欄位進行數學運算時，不走索引。可以放到後面對值進行運算。

例如：

通過運行時間就可以看出效果。

索引欄位慎用函數運算，MySQL的優化器對函數運算識別不出來時會直接走全表掃描。

例子如下：

SQL優化--分頁

傳統分頁

select * from table limt 10000,10;

LIMIT原理

limit 10000,10; 偏移量越大則越慢。查詢的時候要一步一步遍歷到第10010條記錄，然後取後10條記錄，前面的全部拋棄掉。

推薦分頁SQL

select * from table where id>=23424 limit 11;

#10+1(每頁10條)

select * from table where id>23434 limit 11;

分頁方式二

select * from table where id>=(select id from table limit 10000,1) limit 10;

分頁方式三

select * from table where Inner join (select id from table limit 10000,10) using (id);

分頁方式四

先取id：select id from table limit 10000,10;
select * from table where id in (123,456,...);

具體示例：

MySQL> select sql_no_ cache * from post limit 10,10;10 row in set (0.01 sec)
MySQL> select sql_ no_cache * from post limit 2000,10;10 row in set (0.13 sec)
MySQL> select sql_no_cache * from post limit 80000,10;10 rows in set (0.58 sec)
MySQL> select sql_no_ cache id from post limit 8000,10;10 rows in set (0.02 sec)
MySQL> select sql_no_ cache * from post WHERE id> = 323423 limit 10;10 rows in set (0.01 sec)
MySQL> select * from post WHERE id >= ( select sql_ no_ cache id from post limit8000,1 ) limit 10;10 rows in set (0.02 sec)

可擴展性設計

業務隔離

不同的業務使用不同的資料庫實例

垂直拆分

不同的業務表拆分到不同的資料庫中，可以根據不同的模塊，不同的功能將表拆分到不同個資料庫中。邏輯比較清晰，但是也要考慮到具體的情況，如果有關聯查詢時，兩個表放在里不同的庫中，這樣就拆分的不合理了，所以拆分的時候要對業務做深入的了解。

水平拆分

一個表中的數據拆分到不同表中或不同的庫中。但是拆分的時候要慎重的考慮好了，要以哪個鍵作為唯一標識進行拆分。一但確定下來最好不要隨意更改。

終極--數據拆分

水平拆分+垂直拆分

（如果對分散式事務要求不太高的可以使用WTable,底層也是做了拆分。聚合操作也比較麻煩，要對每個庫進行請求，然後再進行聚合操作。）

總結

這次的知識總結的比較粗糙，以後會對每一塊做深入研究。

作者：紀莫

原文：https://www.cnblogs.com/jimoer/p/10226952.html

限於本人水平，如果文章和代碼有表述不當之處，還請不吝賜教。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！