MySQL最佳化小建議

標籤：配置資訊   資料庫設計   null   實現   class   tor   open   監控   訪問量   

背景

“那啥，你過來一下！”

“怎麼了？我代碼都單元測試了的，沒出問題啊！”我一臉懵逼跑到營運大佬旁邊。

“你看看！你看看！多少條警示，趕快最佳化一下！”
營運大佬簡訊列表裡面50多條MySQL CPU 100%警示簡訊。再看看項目名稱不就是我前幾天剛發布的項目嗎！？

我心底一沉，趕快賠上笑臉。“這個一定最佳化，馬上最佳化！那個，能不能看下資料庫監控日誌...”

營運大佬又數落了我幾句，然後調開了資料庫監控日誌。

那傢伙...每秒300多的串連數，幾乎快要封頂的全表掃描數，還有大紅色CPU警報。。。

“那個，能不能看看nginx訪問日誌...我看下訪問量...”我弱弱地說到。

營運大佬不情願的跑了下下面的語句：

grep -c come access.log

come這個介面是其中一個請求量比較大的介面，結果是600多萬。那個時候才中午，周末高峰期估計一天得有上千萬吧，

我撇了撇嘴，心裡想著這麼高的請求量，當初那麼摳門只給我一台低配資料庫還好意思說，不過嘴上肯定是：“好好好，請求量不是很大，看來是資料庫問題，我立刻去最佳化一下！”

“給它弄一個讀寫分離不就行了嗎！？”這時另外一個營運大佬湊了過來，隨意地揮了揮手。。。

你問我DBA去哪兒了？DBA當時有點忙，只說讓我自己檢查一下。。。

最佳化思路

我這個項目由於上線之前比較趕，所以前期並沒有管資料庫設計方面的一些問題，如今隨著遊戲接入，請求量劇增才暴露出來。（其實是前期加班加煩了懶得搞）

這個問題，並不需要增加資料庫硬體設定和增加讀寫分離這種高端手段就能解決，我自個兒挖了多少坑，心裡還是有點碧樹的。

詳細的MySQL最佳化步驟如下：

• 檢查資料表結構，改善不完善設計
• 跑一遍主要業務，收集常用的資料庫查詢SQL
• 分析查詢SQL，適當拆分，添加索引等最佳化查詢
• 最佳化SQL的同時，最佳化代碼邏輯
• 添加本機快取和redis緩衝

這個項目是原生PHP寫的，以上這些只能自己做了。

檢查資料表結構

因為比較菜，回去看設計的表結構，真是慘不忍睹。

儘可能不要使用NULL值

因為建表的時候，如果不對建立的值設定預設值，MySQL都會設定預設為NULL。那麼為啥用NULL不好呢？

• NULL使得索引維護更加複雜，強烈建議對索引列設定NOT NULL
• NOT IN、!=等負向條件查詢在有NULL值的情況下返回永遠為空白結果，查詢容易出錯
• NULL列需要一個額外位元組作為判斷是否為NULL的標誌位
• 使用NULL時和該列其他的值可能不是同種類型，導致問題。（在不同的語言中表現不一樣）
• MySQL難以最佳化對可為NULL的列的查詢

所以對於那些以前偷懶的欄位，手動設定一個預設值吧，Null 字元串呀，0呀補上。

雖然這種方法對於MySQL的效能來說沒有提升多少，但是這是一個好習慣，而且以小見大，不要忽略這些細節。

添加索引

對於經常查詢的欄位，請加上索引，有索引和沒有索引的查詢速度相差十倍甚至更多。

• 一般來說，每張表都需要有一個主鍵id欄位
• 常用於查詢的欄位應該設定索引
• varchar類型的欄位，在建立索引的時候，最好指定長度
• 查詢有多個條件時，優先使用具有索引的條件
• 像LIKE條件這樣的模糊搜尋對於欄位索引是無效的，需要另外建立關鍵詞索引來解決
• 請盡量不要在資料庫層面約束表和表之間的關係，這些表之間的依賴應該在代碼層面去解決

當表和表之間有約束時，雖然增刪查的SQL語句變簡單了，但是帶來的負面效果是插入等操作資料庫都會去檢查約束（雖然可以手動設定忽略約束），這樣相當於把一些商務邏輯寫到了資料庫層，不便於維護。

最佳化表欄位結構

資料庫中那些可以用整形表示的資料就不要使用字串類型，到底是用varchar還是char要看欄位的可能值。

這種最佳化往往在資料庫中有大量資料以後是不可行的，最好在資料庫設計之前就設計好。

• 對於那些可能值很有限的列，使用tinyint代替VARCHAR，
  • 比如記錄行動裝置平台，只有兩個值：android，ios，那麼就可以使用0表示android，1表示ios，這種列一定要寫好注釋
  • 為什麼不用ENUM呢？ENUM擴充困難，比如後來移動平台又增加了一個ipad，那豈不是懵逼了，而tinyint加個2就行，而且ENUM在代碼裡面處理起來特別奇怪，是當成整形呢還是字串，各個語言不一樣。
  • 這種方式，一定要在資料庫注釋或者代碼裡面寫明各個值的含義
• 對於那些定長字串，可以使用char，比如郵編，總是5位
• 對於那些長度未知的字串，使用varchar
• 不要濫用bigint，比如記錄文章數目的表id欄位，用int就行了，21億篇文章上限夠了
• 適當打破資料庫範式添加冗餘欄位，避免查詢時的表串連

查詢的時候，肯定int類型比varchar快，因為整數的比較直接調用底層運算器就可以實現，而字串比較要逐個字元比較。

定長資料比變長資料查詢快，因為比較定長資料與資料之間的位移是固定的，很容易計算下一個資料的位移。而變長資料則還需要多一步去查詢下一個資料的位移量。不過。定長資料可能會浪費更多的儲存空間。

大表拆分

對於那些資料量可能近期會超過500W或者增長很快的表，一定要提前做好垂直分表或者水平分表，當資料量超過百萬以後，查詢速度會明顯下降。

分庫分表盡量在資料庫設計初期敲定方案，否則後期會極大增加代碼複雜性而且不易更改。

垂直分表是按照日期等外部變數進行分表，水平分表是按照表中的某些欄位關係，使用hash映射等分表。

分庫分表的前提條件是在執行查詢語句之前，已經知道需要查詢的資料可能會落在哪一個分庫和哪一個分表中。

最佳化查詢語句

這個才是很多系統資料庫瓶頸的始作俑者。

• 請盡量使用簡單的查詢，避免使用錶鏈接
• 請盡量避免全表掃描，會造成全表掃描的語句包括但不限於：
  • where子句條件恒真或為空白
  • 使用LIKE
  • 使用不等操作符（<>、!=）
  • 查詢含有is null的列
  • 在非索引列上使用or
• 多條件查詢時，請把簡單查詢條件或者索引列查詢置於前面
• 請盡量指定需要查詢的列，不要偷懶使用select *
  • 如果不指定，一方面會返回多餘的資料，佔用寬頻等
  • 另一方面MySQL執行查詢的時候，沒有欄位時會先去查詢表結構有哪些欄位
• 大寫的查詢關鍵字比小寫快一點點
• 使用子查詢會建立暫存資料表，會比連結（JOIN）和聯合（UNION）稍慢
• 在索引欄位上查詢盡量不要使用資料庫函數，不便於緩衝查詢結果
• 當只要一行資料時，請使用LIMIT 1，如果資料過多，請適當設定LIMIT，分頁查詢
• 千萬不要 ORDER BY RAND()，效能極低

上面是我總結的一些小tips，這些規則是死的，但是業務情境是活的，在實際使用的過程中，比如資料統計，可以適當犧牲效能換取便利。

添加緩衝

使用redis等緩衝，還有本地檔案快取等，可以極大地減少資料庫查詢次數。緩衝這個東西，一定要分析自己系統的資料特點，適當選擇。

• 對於一些常用的資料，比如配置資訊等，可以放在緩衝中
• 可以在本機快取資料庫的表結構
• 緩衝的資料一定要注意及時更新，還有設定有效期間
• 增加緩衝務必會增加系統複雜性，一定要注意權衡

最佳化執行個體

下面舉幾個簡單的最佳化查詢例子。首先就是跑一下主要業務，把主要的查詢語句列印到一個檔案裡面，然後分析這些語句。

補充一下，在查詢語句前使用關鍵字explain可以查看查詢執行的具體情況。

看下面的這個查詢語句

select * from link where player_id=‘15298635‘ AND gameid=‘10389‘ AND appid=‘200‘ AND action=‘open‘ AND creator=‘android_sdk‘ AND transport=‘{"name":"uusama","age":20}‘

上面這條語句毛病挺多的

• select * 沒有指定查詢列，這個表有20個欄位，其實我用到的就幾個
• 查詢列沒有索引，造成全表掃描
• 查詢條件過於冗餘，可以適當拆分
• 只需要一條查詢結果，但是沒有限定查詢結果大小

顯然查詢條件很多，而且很多列都是不定長的varchar類型，如果要建立索引，是不是要建立聯合索引呢？

顯然沒有必要，索引的欄位越多，MySQL維護的時候越複雜，對效能也會有損耗，像這樣的SQL查詢語句，我們在主要欄位上建立索引即可。比如在player_id欄位、gameid欄位、appid欄位上建立索引就夠了。

這樣的查詢語句要結合具體的業務情境來進行分析，比如在我當前的系統中，我是期望上面的語句能夠查詢相同的參數下是否有記錄。其實沒必要使用這麼多條件的查詢。

我只需要使用下面的這條更簡單的查詢語句代替即可。

select id,player_idfrom link where player_id=‘15298635‘

查詢到的記錄條數在100條以下，大部分就只用幾十條記錄，我完全可以在代碼裡面在把查詢結果遍曆一遍判斷即可。這樣不知道有多快呢！

再看下面的這個例子：

select * from browser where device_id=‘52‘ AND created>=‘1513735322‘ order by id desc

我只是想查一下這個表裡面某個時間以後的資料。問題大了！

created欄位是timestamp類型，這樣用是不對的，而且沒有限定行數，這條語句會把資料庫所有的device_id=‘52‘的資料搞出來。

還好device_id欄位設定了索引，要不然必然會導致全表掃描。

修改後的查詢如下：

select *from browserwhere device_id=‘52‘ AND created>=‘2018-03-27 00:00:00‘ order by id desc

我的系統總沒有使用複雜的像表串連和聯合這樣的查詢，這類查詢一定要謹慎使用，能夠拆分的話盡量拆分。

記住下面的速度優先順序，兩兩之間相差2個以上數量級

CPU運行速度 > 記憶體訪問速度 > 磁碟io訪問速度 > 網路請求速度

MySQL最佳化小建議