總結MySQL優化最最基礎的操作

#                                                        

優化想法

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

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

盡可能不要使用NULL值

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

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

#新增快取

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

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

檢查資料表結構

推薦學習：《mysql影片教學》

以上是總結MySQL優化最最基礎的操作的詳細內容。更多資訊請關注PHP中文網其他相關文章！