【51CTO.com原創稿件】在程序，語言，架構更新換代頻繁的今天，MySQL 恐怕是大家使用最多的存儲數據庫了。

[[280332]]

圖片來自 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<‘N’ 

如果分別在 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原創稿件，合作站點轉載請注明原文作者和出處為51CTO.com】