【51CTO.com原創稿件】在程序，語言，架構更新換代頻繁的今天，MySQL 恐怕是大家使用最多的存儲數據庫了。
圖片來自 Pexels
大量信息的存儲和查詢都會用到 MySQL，因此它的優化就對系統性能提升就尤為重要了。
由於 MySQL 的優化範圍較廣，從軟件到硬件，從配置到應用，無法一一道來。
今天就從開發者的角度介紹一下 MySQL 應用優化。包括數據類型，數據表查詢/修改，索引和查詢等幾個方面。
數據類型優化
字段是用來存放數據的單元，設計好字段是設計數據庫的第一步，同樣會影響到系統的性能。
設計字段有一個基本的原則，保小不保大，也就是能夠用字節少的字段就不用字節數大的字段，目的是為了節省空間，提高查詢效率。
更小的字段，佔用更小的磁盤空間，內存空間，更小的 IO 消耗。下面針對使用場景，說一些字段類型選取的經驗，供大家參考。
數值類型
手機號：通常我們在存儲手機號的時候，喜歡用 Varchar 類型。
如果是 11 位的手機號，假設我們用 utf8 的編碼，每位字節就需要 3 個字節，那麼就需要 11*33=33 個字節來存放;如果我們使用 bigint，只需要 8 個字節就可以存放。
IP 地址：同上，IP 地址也可以通過 int(4 字節)在存放，可以通過 INET_ATON() 函數把 IP 地址轉成數字。這裡需要注意溢出的問題，需要用無符號的 int。
年齡，枚舉類型：可以用 tinyint 來存放，它只佔用 1 個字節，無符號的 tinyint 可以表示 0-255 的範圍，基本夠用了。
字符類型
Char 和 Varchar 是我們常用的字符類型。char(N) 用來記錄固定長度的字符，如果長度不足 N 的，用空格補齊。
varchar(N) 用來保存可變長度的字符，它會額外增加 1-2 字節來保存字符串的長度。
Char 和 Varchar 佔用的字節數，根據數據庫的編碼格式不同而不同。Latin1 佔用 1 個字節，gbk 佔用 2 個字節，utf8 佔用 3 個字節。
用法方面，如果存儲的內容是可變長度的，例如：家庭住址，用戶描述就可以用 Varchar。
如果內容是固定長度的，例如：UUID(36 位)，或者是 MD5 加密串(32 位)，就可以使用 Char 存放。
時間類型
Datetime 和 Timestamp 都是可以精確到秒的時間類型，但是 Datetime 佔用 8 個字節，而 Timestamp 佔用 4 個字節。
所以在日常建表的時候可以有限選擇 Timestamp。不過他們有下面幾個小區別，需要注意的。
區別一：存儲數據方式不一樣。
Timestamp 是轉化成 utc 時間進行存儲，查詢時，轉化為客戶端時間返回的。
區別二：兩者存儲時間的範圍不一樣。
Timestamp 為'1970-01-01 00:00:01.000000' 到'2038-01-19 03:14:07.999999'。
Datetime為'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'。
數據表查詢/修改優化
說了如何高效地選擇存儲數據的類型以後，再來看看如何高效地讀取數據。MySQL 作為關係型數據庫，在處理複雜業務的時候多會選擇表與表之間的關聯。
這會導致我們在查詢數據的時候，會關聯其他的表，特別是一些多維度數據查詢的時候，這種關聯就尤為突出。
此時，為了提高查詢的效率，我們會對某些字段做冗餘處理，讓這些字段同時存在於多張表中。
但是，這又會帶來其他的問題，例如：如果針對冗餘字段進行修改的時候，就需要對多張表進行修改，並且需要讓這個修改保持在一個事物中。
如果處理不當，會導致數據的不一致性。這裡需要根據具體情況採取查詢策略，例如：需要跨多張表查詢公司銷售額信息。
由於，銷售信息需要連接多張表，並且對銷售量和金額做求和操作，直接查詢顯然是不妥當的。
可以生成後臺服務，定時從相關表中取出信息，計算出結果放入一張彙總表中。
將彙總表中需要查詢的條件字段加上索引信息，提高查詢的效率。這種做法，限於查詢數據實時性不強的情況。
在高速迭代開發過程中，業務變化快，數據庫會根據業務的變化進行迭代。所以，在開發新產品初期，表結構會面臨頻繁地修改。
MySQL 的 ALTERTABLE 操作性能對大表來說是個問題。MySQL 執行修改表結構操作的方法是，用新的結構創建一個空表，從舊錶中查出所有數據插入新表，然後刪除舊錶。
這一操作需要花費大量時間，如果內存不足而表數據很大，並且索引較多的情況，會造成長時間的鎖表。
有極端的情況，有些 ALTERTABLE 操作需要花費數個小時甚至數天才能完成。
這裡推薦兩種小技巧：
- 先把數據庫拷貝到一臺非生產服務器上，在上面做修改表操作，此時的修改不會影響生產庫。
修改完畢以後在做數據庫的切換，把非生產數據庫切換成生產庫。不過需要注意的時候，在做表結構修改的時候，生產庫會生成一些數據。這裡需要通過腳本根據時間區間導入這部分數據。
- “影子拷貝”，即生成一張表結構相同的不同名新數據表(更改數據結構以後的表)。
然後導入原表的數據到新表，導入成功以後停止數據庫，修改原表和新表的名字，最終將數據訪問指向新表。
在運行正常以後，將原表刪除。這裡有現成的工具可以協助完成上述操作，“online schema change”,”openark toolkit”
如果只是刪除或者更改某一列的默認值，那麼直接可以使用 Alert table modify column 和 Alert table alert column 來實現。
索引優化
說了字段和表再來聊聊索引。對於索引的優化網上有很多的說法，都是在實際工作中總結出來的，這裡沒有一定的標準。
針對我們使用比較多的 InnoDB 的存儲引擎(使用的 B-Tree 索引)，推薦幾個方法給大家。
索引獨立
“索引獨立”是指索引列不能是表達式的一部分，也不能是函數的參數。例如：假設 User 表中分別把 create_date 和 userId 設置為索引。
select *from user where date(create_date)=curdate()
selectuserId from user where userId+1=5
類似上面的語句就是將索引作為了函數中的參數和表達式的一部分，是不推薦這樣使用的。
前綴索引
有時候索引字段長度較大，例如：VarChar，Blob，Text。當搜索的時候，這會讓索引變得大且慢。
通常的做法是，可以索引開始的部分字符，這樣可以節約索引空間，提高索引效率。
既然索引全部字符行不通，那麼索引多少字符就是我們要討論的問題了。
這裡需要引入一個概念，索引的選擇性。索引的選擇性是指，不重複的索引值和數據表的記錄總數的比值。
索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓 MySQL 在查找時過濾掉更多的行。
例如：有一張 user 表，其中有一個字段是 FirstName，如何計算這個字段的選擇性，如下：
Select1.0*count(distinct FirstName)/count(*) from user
假設這個結果是 0.75 再用 left 函數對該字段取部分字符，例如取從左開始的 3，4，5 個字段。
分別查看其選擇性，目的是看當選擇多少字符的時候，選擇性最接近 0.75。
從左取3個字段的時候，
Select 1.0*count(distinct left(FirstName,3))/count(*) from user
結果為0.58
從左取4個字段的時候，
Select 1.0*count(distinct left(FirstName,4))/count(*) from user
結果為0.67
從左取5個字段的時候，
Select 1.0*count(distinct left(FirstName,5))/count(*) from user
結果為0.74
從上面嘗試發現，字段 FirstName 取左邊字符，從 3-5 的獲取可以看出，當從左邊取第 5 個字符的時候，選擇性 0.74 最接近 0.75。
因此，可以將 FirstName 的前面 5 個字符作為前綴索引，這樣建立索引的效果基本和 FirstName 全部字符建立索引的效果一致。而又不用將 FirstName 整個字段都當成索引。
於是可以用下面語句修改索引信息：
Alter tableuser add key(FirstName(5))
多列索引及其順序
多列索引，顧名思義就是將多列字段作為索引。假設在 user 表中通過搜索 LastName 和 FirstName 條件來查找數據。
可能出現以下語句：
Select *from user where LastName = ‘Green’
Select *from user where LastName = ‘Green’ and FirstName = ‘Jack’
Select *from user where LastName = ‘Green’ and (FirstName = ‘Jack’ or FirstName =‘Michael’
Select *from user where LastName = ‘Green’ and FirstName >=‘M’ and FirstName
如果分別在 LastName 和 FirstName 上面建立索引：
Select *from user where LastName = ‘Green’ and FirstName = ‘Jack’
當運行上面這段代碼的時候，系統會讓選擇性高的 SQL 的索引生效，另外一個索引是用不上的。因此我們就需要建立多列索引(合併索引)。
語句如下：
Alter table user add key(LastName, FirstName)
既然定義了多列索引，那麼其中的索引順序是否也需要考慮呢?在一個多列 B-Tree 索引中，索引列的順序意味著，索引首先按照最左列進行排序，其次是第二列。
索引可以按照升序或者降序進行掃描，以滿足精確符合列順序的 ORDERBY、GROUPBY 和 DISTINCT 等子句的查詢需求。
所以，多列索引的順序是需要考慮的。這裡給出的建議是，將選擇性最高的索引列放在前面。
接上面的例子，還是 LastName 和 FirstName 作為多列索引。看誰應該放前面。
通過按照選擇性規則，寫如下 SQL 語句：
先計算LastName的選擇性
Selectcount(disctinc LastName)/count(*) from user
結果為0.02
再計算FirstName的選擇性
Selectcount(disctinc FirstName)/count(*) from user
結果0.05
FirstName 的選擇性要高於 LastName 的選擇性。因此調整多列索引的順序如下：
Alter tableuser add key(FirstName ，LastName)
覆蓋索引
當使用 Select 的數據列只用從索引中取得，而不必從數據表中讀取，換句話說查詢列要被所使用的索引覆蓋。
例如：User 表中將 LastName 作為索引。如果寫以下查詢語句：
Select LastName from user
LastName 及作為索引，又在查詢內容中顯示出來，那麼 LastName 就是覆蓋索引。
覆蓋索引是高效查找行方法，通過索引就可以讀取數據，就不需要再到數據表中讀取數據了。
而且覆蓋索引會以 Usingindex 作為標示，可以通過 Explain 語句查看。
Explain 查看覆蓋索引標示
覆蓋索引主要應用在 Count 等一些聚合操作上，提升查詢的效率。例如上面提到的 Selectcount(LastName) from user 就可以把 LastName 設置為索引。
還有可以進行列查詢的回表優化，如下：
Select LastName, FirstName from user where LastName=‘Jack’
如果此時 LastName 設置為索引，可以將 LastName 和 FirstName 設置為多列索引(聯合索引)。
避免回錶行為的發生。這裡的回表是指二級索引搜索到以後，再找到聚合索引，然後在查找 PK 的過程。
這裡需要通過兩次搜索完成。簡單點說就是使用了覆蓋索引以後，一次就可以查到想要的記錄，不用在查第二次了。
回表示意圖
查詢優化
作為程序開發人員來說，使用得最多的就是 SQL 語句了，最多的操作就是查詢了。
我們一起來看看，哪些因素會影響查詢記錄，查詢基本原理是什麼，以及如何發現和優化 SQL 語句。
影響查詢效率的因素
一般來說，影響查詢的因素有三部分組成，如下：
- 響應時間，由兩部分組成，他們分別是，服務時間和排隊時間。服務時間是指數據庫處理查詢花費的時間。
排隊時間是指服務器因為等待某些資源花費的時間。例如：I/O 操作，等待其他事務釋放鎖的時間。
- 掃描記錄行數，在查詢過程中數據庫鎖掃描的行記錄。理想情況下掃描的行數和返回的行數是相同的。不過通常來說，掃描的行數都會大於返回記錄的行數。
- 返回記錄行數，返回實際要查詢的結果。
查詢基礎
查詢流程圖
說了影響查詢效率的因素以後，來看看查詢這件事情在 MySQL 中是如何運作的，可以幫助我理解，查詢優化工作是在哪裡進行的：
- 客戶端發送一條查詢給服務器。
- 服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。
- 解析器對 SQL 進行解析，它通過關鍵字將 SQL 語句進行解析，並生成一棵對應的“解析樹”。MySQL 解析器將使用 MySQL 語法規則驗證和解析查詢。
- 預處理器則根據一些 MySQL 規則進一步檢查解析樹是否合法，並且驗證權限。例如，檢查數據表和數據列是否存在，解析名字和別名看是否有歧義。
- MySQL 根據優化器生成的執行計劃，調用存儲引擎的 API 來執行查詢。
- 將結果返回給客戶端。
如何發現查詢慢的 SQL
說了影響查詢緩慢的因素以及查詢的基本流程以後，再來看看如何發現查詢慢的 SQL。這裡 MySQL 提供了日誌，其中可以查詢執行比較慢的 SQL。
①查看慢查詢日誌是否開啟
SHOWVARIABLESLIKE'%slow_query_log%';
②如果沒有開啟，通過命令開啟慢查詢日誌
SETGLOBAL slow_query_log=1;
③設置慢查詢日誌的時間，這裡的單位是秒，意思是隻要是執行時間超過 X 秒的查詢語句被記錄到這個日誌中。這裡的 X 就是你要設置的。(下面的例子設置的是 3 秒)
SETGLOBAL long_query_time=3;
④查看多少 SQL 語句是超過查詢閥值的(3 秒)
Explain 分析 SQL 查詢
通過上面的方法可以知道哪些 SQL 花費了較多的時間，那麼如何對這些 SQL 語句進行分析呢。畢竟，我們的目的是通過分析以後，優化 SQL 從而提高其性能。
將 Explain 關鍵字放在要執行的 SQL 語句前面，可以模擬優化器執行 SQL 語句，從而知道 MySQL 是如何處理你的 SQL 語句的。
Explain 執行 SQL 示意圖
上面每個字段的含義，在這裡不展開描述。
SQL 優化建議
如果發現慢查詢的 SQL，我們就需要針對其問題進行優化。這裡針對幾個常見的 SQL 給出一些優化建議。
類似 SQL 優化的文章和例子在網上種類繁多，千奇百怪。建議在優化之前，先查看慢查詢日誌和 Explain 的語句，再進行優化，做到有的放矢。
①Count 優化
從 user 表中搜索 id 大於 7 的所有用戶。如果是 InnoDB 存儲引擎會進行逐行掃描，如果表中記錄比較多，性能就是問題了。
Select count(*) from user where id>7
如果先將所有的行數 Count 出來，再減去 id<=7 的記錄，這樣速度就會快一些。
Select (select count(*) - (select count(*) from user where id <=7) from user)
如果有一個貨物表 items，其中有一個 color 字段來表示貨物的顏色，如果需要知道顏色是藍色或者紅色的貨物的數量，可以這麼寫：
Select count(color=‘blue’ or color=‘red’) from items
Select count(*) from items where color=‘blue’ and color=‘red’
不過顏色本身是除斥的字段，所以可以優化成下面的 SQL。
Select count(color=‘blue’ or null) as blue, count(color=‘red’ or null) as red from items
②GROUPBY 優化
MySQL 通過索引來優化 GROUPBY 查詢。在無法使用索引的時候，會使用兩種策略優化：臨時表和文件排序分組。
可以通過兩個參數 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 提升其性能。
這兩個參數只對 Select 語句有效。它們告訴優化器對 GROUPBY 查詢使用臨時表及排序。
SQL_SMALL_RESULT 告訴優化器結果集會很小，可以將結果集放在內存中的索引臨時表，以避免排序操作。
如果是 SQL_BIG_RESULT，則告訴優化器結果集可能會非常大，建議使用磁盤臨時表做排序操作。
例如：
SelectSQL_BUFFER_RESULTfield1, count(*) from table1 groupby field1
假設兩個表做關聯查詢，選擇查詢表中的標識列(主鍵)分組效率會高。
例如 actor 表和 film 表通過 actorId 做關聯，查詢如下：
Select actor.FirstName, actor.LastName,count(*) from film inner join actor using(actorId)
Group by actor.FirstName,actor.LastName
就可以修改為：
Select actor.FirstName, actor.LastName, count(*) from film inner join actor using(actorId)
Group by film.actorId
③Limit
Limit 對我們再熟悉也不過了，特別是在做分頁操作的時候，經常會用到它。但在偏移量非常的時候問題就來了。
例如，Limit 1000，20 就需要偏移 1000 條數據以後，再返回後面的 20 條記錄，前面的 1000 條數據是被拋棄掉的。
按照上例 SQL 代碼如下：
Select name from user order by id limit1000,20
這裡通過 id 索引到第 1001 條記錄，然後取 20 條記錄。這裡利用 id 的索引的優勢直接跳過了前面 1000 條記錄。
Select name from user where id>=1001order by id limit 20
總結
從開發者的角度瞭解 MySQL 的應用優化。從數據類型的選擇開始，針對數值類型，字符類型，時間類型進行了舉例說明。
接下來談到，作為數據表的查詢，修改的優化，我們應該注意哪些細節。然後，聊了索引獨立，前綴索引，多列索引，覆蓋索引的優化方法。
最後，針對使用最多的查詢優化進行了探討。從影響查詢的因素到查詢基礎，再到如何發現慢查詢，用幾個 SQL 優化的建議結束了我們的 MySQL 應用優化之旅。
寫完全文感覺 MySQL 博大精深，需要學習的東西很多，一文不能面面俱到，還需不斷學習。
簡介：十六年開發和架構經驗，曾擔任過惠普武漢交付中心技術專家，需求分析師，項目經理，後在創業公司擔任技術/產品經理。善於學習，樂於分享。目前專注於技術架構與研發管理。
閱讀更多 51CTO 的文章
