【51CTO.com原創(chuàng)稿件】在程序，語言，架構(gòu)更新?lián)Q代頻繁的今天，MySQL 恐怕是大家使用最多的存儲數(shù)據(jù)庫了。

[[280332]]

圖片來自 Pexels

大量信息的存儲和查詢都會用到 MySQL，因此它的優(yōu)化就對系統(tǒng)性能提升就尤為重要了。

由于 MySQL 的優(yōu)化范圍較廣，從軟件到硬件，從配置到應(yīng)用，無法一一道來。

今天就從開發(fā)者的角度介紹一下 MySQL 應(yīng)用優(yōu)化。包括數(shù)據(jù)類型，數(shù)據(jù)表查詢/修改，索引和查詢等幾個方面。

數(shù)據(jù)類型優(yōu)化

字段是用來存放數(shù)據(jù)的單元，設(shè)計(jì)好字段是設(shè)計(jì)數(shù)據(jù)庫的第一步，同樣會影響到系統(tǒng)的性能。

設(shè)計(jì)字段有一個基本的原則，保小不保大，也就是能夠用字節(jié)少的字段就不用字節(jié)數(shù)大的字段，目的是為了節(jié)省空間，提高查詢效率。

更小的字段，占用更小的磁盤空間，內(nèi)存空間，更小的 IO 消耗。下面針對使用場景，說一些字段類型選取的經(jīng)驗(yàn)，供大家參考。

數(shù)值類型

手機(jī)號：通常我們在存儲手機(jī)號的時候，喜歡用 Varchar 類型。

如果是 11 位的手機(jī)號，假設(shè)我們用 utf8 的編碼，每位字節(jié)就需要 3 個字節(jié)，那么就需要 11*33=33 個字節(jié)來存放;如果我們使用 bigint，只需要 8 個字節(jié)就可以存放。

IP 地址：同上，IP 地址也可以通過 int(4 字節(jié))在存放，可以通過 INET_ATON() 函數(shù)把 IP 地址轉(zhuǎn)成數(shù)字。這里需要注意溢出的問題，需要用無符號的 int。

年齡，枚舉類型：可以用 tinyint 來存放，它只占用 1 個字節(jié)，無符號的 tinyint 可以表示 0-255 的范圍，基本夠用了。

字符類型

Char 和 Varchar 是我們常用的字符類型。char(N) 用來記錄固定長度的字符，如果長度不足 N 的，用空格補(bǔ)齊。

varchar(N) 用來保存可變長度的字符，它會額外增加 1-2 字節(jié)來保存字符串的長度。

Char 和 Varchar 占用的字節(jié)數(shù)，根據(jù)數(shù)據(jù)庫的編碼格式不同而不同。Latin1 占用 1 個字節(jié)，gbk 占用 2 個字節(jié)，utf8 占用 3 個字節(jié)。

用法方面，如果存儲的內(nèi)容是可變長度的，例如：家庭住址，用戶描述就可以用 Varchar。

如果內(nèi)容是固定長度的，例如：UUID(36 位)，或者是 MD5 加密串(32 位)，就可以使用 Char 存放。

時間類型

Datetime 和 Timestamp 都是可以精確到秒的時間類型，但是 Datetime 占用 8 個字節(jié)，而 Timestamp 占用 4 個字節(jié)。

所以在日常建表的時候可以有限選擇 Timestamp。不過他們有下面幾個小區(qū)別，需要注意的。

區(qū)別一：存儲數(shù)據(jù)方式不一樣。

Timestamp 是轉(zhuǎn)化成 utc 時間進(jìn)行存儲，查詢時，轉(zhuǎn)化為客戶端時間返回的。

區(qū)別二：兩者存儲時間的范圍不一樣。

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'。

數(shù)據(jù)表查詢/修改優(yōu)化

說了如何高效地選擇存儲數(shù)據(jù)的類型以后，再來看看如何高效地讀取數(shù)據(jù)。MySQL 作為關(guān)系型數(shù)據(jù)庫，在處理復(fù)雜業(yè)務(wù)的時候多會選擇表與表之間的關(guān)聯(lián)。

這會導(dǎo)致我們在查詢數(shù)據(jù)的時候，會關(guān)聯(lián)其他的表，特別是一些多維度數(shù)據(jù)查詢的時候，這種關(guān)聯(lián)就尤為突出。

此時，為了提高查詢的效率，我們會對某些字段做冗余處理，讓這些字段同時存在于多張表中。

但是，這又會帶來其他的問題，例如：如果針對冗余字段進(jìn)行修改的時候，就需要對多張表進(jìn)行修改，并且需要讓這個修改保持在一個事物中。

如果處理不當(dāng)，會導(dǎo)致數(shù)據(jù)的不一致性。這里需要根據(jù)具體情況采取查詢策略，例如：需要跨多張表查詢公司銷售額信息。

由于，銷售信息需要連接多張表，并且對銷售量和金額做求和操作，直接查詢顯然是不妥當(dāng)?shù)摹?/p>

可以生成后臺服務(wù)，定時從相關(guān)表中取出信息，計(jì)算出結(jié)果放入一張匯總表中。

將匯總表中需要查詢的條件字段加上索引信息，提高查詢的效率。這種做法，限于查詢數(shù)據(jù)實(shí)時性不強(qiáng)的情況。

在高速迭代開發(fā)過程中，業(yè)務(wù)變化快，數(shù)據(jù)庫會根據(jù)業(yè)務(wù)的變化進(jìn)行迭代。所以，在開發(fā)新產(chǎn)品初期，表結(jié)構(gòu)會面臨頻繁地修改。

MySQL 的 ALTERTABLE 操作性能對大表來說是個問題。MySQL 執(zhí)行修改表結(jié)構(gòu)操作的方法是，用新的結(jié)構(gòu)創(chuàng)建一個空表，從舊表中查出所有數(shù)據(jù)插入新表，然后刪除舊表。

這一操作需要花費(fèi)大量時間，如果內(nèi)存不足而表數(shù)據(jù)很大，并且索引較多的情況，會造成長時間的鎖表。

有極端的情況，有些 ALTERTABLE 操作需要花費(fèi)數(shù)個小時甚至數(shù)天才能完成。

這里推薦兩種小技巧：

• 先把數(shù)據(jù)庫拷貝到一臺非生產(chǎn)服務(wù)器上，在上面做修改表操作，此時的修改不會影響生產(chǎn)庫。

修改完畢以后在做數(shù)據(jù)庫的切換，把非生產(chǎn)數(shù)據(jù)庫切換成生產(chǎn)庫。不過需要注意的時候，在做表結(jié)構(gòu)修改的時候，生產(chǎn)庫會生成一些數(shù)據(jù)。這里需要通過腳本根據(jù)時間區(qū)間導(dǎo)入這部分?jǐn)?shù)據(jù)。

• “影子拷貝”，即生成一張表結(jié)構(gòu)相同的不同名新數(shù)據(jù)表(更改數(shù)據(jù)結(jié)構(gòu)以后的表)。

然后導(dǎo)入原表的數(shù)據(jù)到新表，導(dǎo)入成功以后停止數(shù)據(jù)庫，修改原表和新表的名字，最終將數(shù)據(jù)訪問指向新表。

在運(yùn)行正常以后，將原表刪除。這里有現(xiàn)成的工具可以協(xié)助完成上述操作，“online schema change”,”openark toolkit”

如果只是刪除或者更改某一列的默認(rèn)值，那么直接可以使用 Alert table modify column 和 Alert table alert column 來實(shí)現(xiàn)。

索引優(yōu)化

說了字段和表再來聊聊索引。對于索引的優(yōu)化網(wǎng)上有很多的說法，都是在實(shí)際工作中總結(jié)出來的，這里沒有一定的標(biāo)準(zhǔn)。

針對我們使用比較多的 InnoDB 的存儲引擎(使用的 B-Tree 索引)，推薦幾個方法給大家。

索引獨(dú)立

“索引獨(dú)立”是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。例如：假設(shè) User 表中分別把 create_date 和 userId 設(shè)置為索引。

select *from user where date(create_date)=curdate() 
selectuserId from user where userId+1=5 

類似上面的語句就是將索引作為了函數(shù)中的參數(shù)和表達(dá)式的一部分，是不推薦這樣使用的。

前綴索引

有時候索引字段長度較大，例如：VarChar，Blob，Text。當(dāng)搜索的時候，這會讓索引變得大且慢。

通常的做法是，可以索引開始的部分字符，這樣可以節(jié)約索引空間，提高索引效率。

既然索引全部字符行不通，那么索引多少字符就是我們要討論的問題了。

這里需要引入一個概念，索引的選擇性。索引的選擇性是指，不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值。

索引的選擇性越高則查詢效率越高，因?yàn)檫x擇性高的索引可以讓 MySQL 在查找時過濾掉更多的行。

例如：有一張 user 表，其中有一個字段是 FirstName，如何計(jì)算這個字段的選擇性，如下：

Select1.0*count(distinct FirstName)/count(*) from user 

假設(shè)這個結(jié)果是 0.75 再用 left 函數(shù)對該字段取部分字符，例如取從左開始的 3，4，5 個字段。

分別查看其選擇性，目的是看當(dāng)選擇多少字符的時候，選擇性最接近 0.75。

從左取3個字段的時候， 
Select 1.0*count(distinct left(FirstName,3))/count(*) from user 
結(jié)果為0.58 
 
從左取4個字段的時候， 
Select 1.0*count(distinct left(FirstName,4))/count(*) from user 
結(jié)果為0.67 
 
從左取5個字段的時候， 
Select 1.0*count(distinct left(FirstName,5))/count(*) from user 
結(jié)果為0.74 

從上面嘗試發(fā)現(xiàn)，字段 FirstName 取左邊字符，從 3-5 的獲取可以看出，當(dāng)從左邊取第 5 個字符的時候，選擇性 0.74 最接近 0.75。

因此，可以將 FirstName 的前面 5 個字符作為前綴索引，這樣建立索引的效果基本和 FirstName 全部字符建立索引的效果一致。而又不用將 FirstName 整個字段都當(dāng)成索引。

于是可以用下面語句修改索引信息：

Alter tableuser add key(FirstName(5)) 

多列索引及其順序

多列索引，顧名思義就是將多列字段作為索引。假設(shè)在 user 表中通過搜索 LastName 和 FirstName 條件來查找數(shù)據(jù)。

可能出現(xiàn)以下語句：

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’ 

當(dāng)運(yùn)行上面這段代碼的時候，系統(tǒng)會讓選擇性高的 SQL 的索引生效，另外一個索引是用不上的。因此我們就需要建立多列索引(合并索引)。

語句如下：

Alter table user add key(LastName, FirstName) 

既然定義了多列索引，那么其中的索引順序是否也需要考慮呢?在一個多列 B-Tree 索引中，索引列的順序意味著，索引首先按照最左列進(jìn)行排序，其次是第二列。

索引可以按照升序或者降序進(jìn)行掃描，以滿足精確符合列順序的 ORDERBY、GROUPBY 和 DISTINCT 等子句的查詢需求。

所以，多列索引的順序是需要考慮的。這里給出的建議是，將選擇性最高的索引列放在前面。

接上面的例子，還是 LastName 和 FirstName 作為多列索引。看誰應(yīng)該放前面。

通過按照選擇性規(guī)則，寫如下 SQL 語句：

先計(jì)算LastName的選擇性 
Selectcount(disctinc LastName)/count(*) from user 
結(jié)果為0.02 

再計(jì)算FirstName的選擇性 
Selectcount(disctinc FirstName)/count(*) from user 
結(jié)果0.05 

FirstName 的選擇性要高于 LastName 的選擇性。因此調(diào)整多列索引的順序如下：

Alter tableuser add key(FirstName ，LastName) 

覆蓋索引

當(dāng)使用 Select 的數(shù)據(jù)列只用從索引中取得，而不必從數(shù)據(jù)表中讀取，換句話說查詢列要被所使用的索引覆蓋。

例如：User 表中將 LastName 作為索引。如果寫以下查詢語句：

Select LastName from user 

LastName 及作為索引，又在查詢內(nèi)容中顯示出來，那么 LastName 就是覆蓋索引。

覆蓋索引是高效查找行方法，通過索引就可以讀取數(shù)據(jù)，就不需要再到數(shù)據(jù)表中讀取數(shù)據(jù)了。

而且覆蓋索引會以 Usingindex 作為標(biāo)示，可以通過 Explain 語句查看。

Explain 查看覆蓋索引標(biāo)示

覆蓋索引主要應(yīng)用在 Count 等一些聚合操作上，提升查詢的效率。例如上面提到的 Selectcount(LastName) from user 就可以把 LastName 設(shè)置為索引。

還有可以進(jìn)行列查詢的回表優(yōu)化，如下：

Select LastName, FirstName from user where LastName=‘Jack’ 

如果此時 LastName 設(shè)置為索引，可以將 LastName 和 FirstName 設(shè)置為多列索引(聯(lián)合索引)。

避免回表行為的發(fā)生。這里的回表是指二級索引搜索到以后，再找到聚合索引，然后在查找 PK 的過程。

這里需要通過兩次搜索完成。簡單點(diǎn)說就是使用了覆蓋索引以后，一次就可以查到想要的記錄，不用在查第二次了。

回表示意圖

查詢優(yōu)化

作為程序開發(fā)人員來說，使用得最多的就是 SQL 語句了，最多的操作就是查詢了。

我們一起來看看，哪些因素會影響查詢記錄，查詢基本原理是什么，以及如何發(fā)現(xiàn)和優(yōu)化 SQL 語句。

影響查詢效率的因素

一般來說，影響查詢的因素有三部分組成，如下：

• 響應(yīng)時間，由兩部分組成，他們分別是，服務(wù)時間和排隊(duì)時間。服務(wù)時間是指數(shù)據(jù)庫處理查詢花費(fèi)的時間。

排隊(duì)時間是指服務(wù)器因?yàn)榈却承┵Y源花費(fèi)的時間。例如：I/O 操作，等待其他事務(wù)釋放鎖的時間。

• 掃描記錄行數(shù)，在查詢過程中數(shù)據(jù)庫鎖掃描的行記錄。理想情況下掃描的行數(shù)和返回的行數(shù)是相同的。不過通常來說，掃描的行數(shù)都會大于返回記錄的行數(shù)。
• 返回記錄行數(shù)，返回實(shí)際要查詢的結(jié)果。

查詢基礎(chǔ)

查詢流程圖

說了影響查詢效率的因素以后，來看看查詢這件事情在 MySQL 中是如何運(yùn)作的，可以幫助我理解，查詢優(yōu)化工作是在哪里進(jìn)行的：

• 客戶端發(fā)送一條查詢給服務(wù)器。
• 服務(wù)器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結(jié)果。
• 解析器對 SQL 進(jìn)行解析，它通過關(guān)鍵字將 SQL 語句進(jìn)行解析，并生成一棵對應(yīng)的“解析樹”。MySQL 解析器將使用 MySQL 語法規(guī)則驗(yàn)證和解析查詢。
• 預(yù)處理器則根據(jù)一些 MySQL 規(guī)則進(jìn)一步檢查解析樹是否合法，并且驗(yàn)證權(quán)限。例如，檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，解析名字和別名看是否有歧義。
• MySQL 根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲引擎的 API 來執(zhí)行查詢。
• 將結(jié)果返回給客戶端。

如何發(fā)現(xiàn)查詢慢的 SQL

說了影響查詢緩慢的因素以及查詢的基本流程以后，再來看看如何發(fā)現(xiàn)查詢慢的 SQL。這里 MySQL 提供了日志，其中可以查詢執(zhí)行比較慢的 SQL。

①查看慢查詢?nèi)罩臼欠耖_啟

SHOWVARIABLESLIKE'%slow_query_log%';

②如果沒有開啟，通過命令開啟慢查詢?nèi)罩?/strong>

SETGLOBAL slow_query_log=1; 

SETGLOBAL long_query_time=3; 

Select count(*) from user where id>7 

Select (select count(*) - (select count(*) from user where id <=7) from user) 

Select count(color=‘blue’ or color=‘red’) from items 
Select count(*) from items where color=‘blue’ and color=‘red’ 

Select count(color=‘blue’ or null) as blue, count(color=‘red’ or null) as red from items 

SelectSQL_BUFFER_RESULTfield1, count(*) from table1 groupby field1 

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 

Select name from user order by id limit1000,20 

Select name from user where id>=1001order by id limit 20