MySQL資料庫開發常見問題及幾點優化！

來源：www.cnblogs.com/cyfonly/p/5616536.html

MySQL資料庫是被廣泛應用的關係型資料庫，其體積小、支援多處理器、開源並免費的特性使其在Internet中小型網站中的使用率尤其高。在使用 MySQL的過程中不規範的SQL編寫、非最優的策略選擇都可能導致系統效能甚至功能上的缺陷。

恰巧就在前幾天，本人所在公司的雲事業部舉辦了一場關於MySQL的技術交流會，其中一個部分正是聚焦於開發過程中MySQL資料庫設計及使用的常見問題，並提出相關優化方案。根據會議內容並查閱相關資料，本人對這個部分進行了一次小結，結合自己的工作經歷及理解形成此文以供分享，希望能有助於各位同行解決工作中的相關問題。

本文將就以下三個問題進行展開：

• 庫表設計

• 慢 SQL 問題

• 誤操作、程式 bug 時怎麼辦

一、庫表設計

1.1、引擎選擇

在 MySQL5.1 中，引入了新的外掛式儲存引擎體系結構，允許將儲存引擎載入到正在運新的 MySQL伺服器中。使用 MySQL外掛式儲存引擎體系結構，允許資料庫專業人員或者設計庫表的軟體開發人員為特定的應用需求選擇專門的儲存引擎，完全不需要管理任何特殊的應用編碼要求，也無需考慮所有的底層實施細節。因此，儘管不同的儲存引擎具有不同的能力，應用程式是與之分離的。此外，使用者可以在伺服器、資料庫和表格三個層級中儲存引擎，提供了極大的靈活性。

MySQL常用的儲存引擎包括 MyISAM、InnoDB 和 Memory，其中各自的特點如下：

1. MyISAM: 全表鎖，擁有較高的執行速度，一個寫請求請阻塞另外相同表格的所有讀寫請求，併發效能差，佔用空間相對較小，MySQL5.5 及以下僅 MyISAM支援全文索引，不支援事務。

2. InnoDB ：行級鎖（SQL 都走索引查詢），併發能力相對強，佔用空間是 MyISAM的 2.5 倍，不支援全文索引（5.6 開始支援），支援事務。

3. Memory : 全表鎖，儲存在記憶體當中，速度快，但會佔用和資料量成正比的記憶體空間且資料在 MySQL重啟時會丟失。

基於以上特性，建議絕大部份都設定為InnoDB引擎，特殊的業務再考慮選用 MyISAM或 Memory ，如全文索引支援或極高的執行效率等。

1.2、分表方法

在資料庫表使用過程中，為了減小資料庫伺服器的負擔、縮短查詢時間，常常會考慮做分表設計。分表分兩種，一種是縱向分表（將本來可以在同一個表的內容，人為劃分儲存在為多個不同結構的表）和橫向分表（把大的表結構，橫向切割為同樣結構的不同表）。

其中，縱向分表常見的方式有根據活躍度分表、根據重要性分表等。其主要解決問題如下：

• 表與表之間資源爭用問題；

• 鎖爭用機率小；

• 實現核心與非核心的分級儲存，如UDB登陸庫拆分成一級二級三級庫；

• 解決了資料庫同步壓力問題。

橫向分表是指根據某些特定的規則來劃分大資料量表，如根據時間分表。其主要解決問題如下：

• 單表過大造成的效能問題；

• 單表過大造成的單伺服器空間問題。

1.3、索引問題

索引是對資料庫表中一個或多個列的值進行排序的結構，建立索引有助於更快地獲取資訊。 mysql 有四種不同的索引型別：

• 主鍵索此 ( PRIMARY )

• 唯一索引 ( UNIQUE )

• 普通索引 ( INDEX )

• 全文索引（FULLTEXT , MyISAM 及 MySQL5.6 以上的 InnoDB ）

建立索引的目的是加快對錶中記錄的查詢或排序，索引也並非越多越好，因為建立索引是要付出代價的：一是增加了資料庫的儲存空間，二是在插入和修改資料時要花費較多的時間維護索引。

在設計表或索引時，常出現以下幾個問題：

• 少建索引或不建索引。這個問題最突出，建議建表時 DBA 可以一起協助把關。

• 索引濫用。濫用索引將導致寫請求變慢，拖慢整體資料庫的響應速度（5.5 以下的 MySQL只能用到一個索引)。

• 從不考慮聯合索引。實際上聯合索引的效率往往要比單列索引的效率更高。

• 非最優列選擇。低選擇性的欄位不適合建單列索引，如 status 型別的欄位。

二、慢 SQL 問題

2.1、導致慢 SQL 的原因

在遇到慢 SQL 情況時，不能簡單的把原因歸結為 SQL 編寫問題(雖然這是最常見的因素)，實際上導致慢 SQL 有很多因素，甚至包括硬體和 MySQL本身的Bug。根據出現的概率從大到小，羅列如下：

• SQL編寫問題

• 鎖

• 業務例項相互幹繞對 IO/CPU 資源爭用

• 伺服器硬體

• MySQL Bug

2.2、由 SQL 編寫導致的慢 SQL 優化

針對SQL編寫導致的慢 SQL，優化起來還是相對比較方便的。正如上一節提到的正確的使用索引能加快查詢速度，那麼我們在編寫 SQL 時就需要注意與索引相關的規則：

• 欄位型別轉換導致不用索引，如字串型別的不用引號，數字型別的用引號等，這有可能會用不到索引導致全表掃描；

• MySQL不支援函式轉換，所以欄位前面不能加函式，否則這將用不到索引；

• 不要在欄位前面加減運算；

• 字串比較長的可以考慮索引一部份減少索引檔案大小，提高寫入效率；

• like % 在前面用不到索引；

• 根據聯合索引的第二個及以後的欄位單獨查詢用不到索引；

• 不要使用 select *；

• 排序請儘量使用升序 ;

• or 的查詢儘量用 union 代替 （Innodb）；

• 複合索引高選擇性的欄位排在前面；

• order by / group by 欄位包括在索引當中減少排序，效率會更高。

除了上述索引使用規則外，SQL 編寫時還需要特別注意一下幾點：

• 儘量規避大事務的 SQL，大事務的 SQL 會影響資料庫的併發效能及主從同步；

• 分頁語句 limit 的問題；

• 刪除表所有記錄請用 truncate，不要用 delete；

• 不讓 mysql 幹多餘的事情，如計算；

• 輸寫 SQL 帶欄位，以防止後面表變更帶來的問題，效能也是比較優的 ( 涉及到資料字典解析，請自行查詢資料)；

• 在 Innodb上用 select count(*)，因為 Innodb 會儲存統計資訊；

• 慎用 Oder by rand()。

三、分析診斷工具

在日常開發工作中，我們可以做一些工作達到預防慢 SQL 問題，比如在上線前預先用診斷工具對 SQL 進行分析。常用的工具有：

• mysql dumpslow

• mysql profile

• mysql explain

具體使用及分析方法在此就不贅述，網上有豐富的資源可以參考。

四、誤操作、程式 bug 時怎麼辦

提出這個問題顯然主要是針對剛開始工作的年輕同行們……實際上誤操作和程式 bug 導致資料誤刪或者混亂的問題並非少見，但是剛入行的開發工作者會比較緊張。一個成熟的企業往往會有完善的資料管理規範和較豐富的資料恢復方案（初創公司除外），會進行資料備份和資料容災。當你發現誤操作或程式 bug 導致線上資料被誤刪或誤改動時，一定不能慌亂，應及時與 DBA 聯絡，第一時間進行資料恢復（嚴重時直接停止服務），儘可能減少影響和損失。對於重要資料（如資金）的操作，在開發時一定要反覆進行測試，確保沒有問題後再上線。

PS：如果覺得我的分享不錯，歡迎大家隨手點贊、轉發。

（完）

Java團長

專注於Java乾貨分享

掃描上方二維碼獲取更多Java乾貨