mysql數據庫開發常見的問題及優化

本篇內容主要講解“mysql數據庫開發常見的問題及優化”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“mysql數據庫開發常見的問題及優化”吧!

一、庫表設計

1.1 引擎選擇

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

mysql 常用的存儲引擎包括 MYISAM、Innodb 和 Memory，其中各自的特點如下：

1. MYISAM : 全表鎖，擁有較高的執行速度，一個寫請求請阻塞另外相同表格的所有讀寫請求，并發性能差，占用空間相對較小，mysql 5.5 及以下僅 MYISAM 支持全文索引，不支持事務。

2. Innodb：行級鎖（SQL 都走索引查詢），并發能力相對強，占用空間是 MYISAM 的 2.5 倍，不支持全文索引（5.6 開始支持），支持事務。

3. Memory : 全表鎖，存儲在內存當中，速度快，但會占用和數據量成正比的內存空間且數據在 mysql 重啟時會丟失。

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

1.2 分表方法

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

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

1. 表與表之間資源爭用問題；

2. 鎖爭用機率小；

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

4. 解決了數據庫同步壓力問題。

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

1. 單表過大造成的性能問題；

2. 單表過大造成的單服務器空間問題。

1.3 索引問題

索引是對數據庫表中一個或多個列的值進行排序的結構，建立索引有助于更快地獲取信息。 mysql 有四種不同的索引類型：

1. 主鍵索此 ( PRIMARY )

2. 唯一索引 ( UNIQUE )

3. 普通索引 ( INDEX )

4. 全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）

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

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

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

2. 索引濫用。濫用索引將導致寫請求變慢，拖慢整體數據庫的響應速度（5.5 以下的 mysql 只能用到一個索引)。

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

4. 非最優列選擇。低選擇性的字段不適合建單列索引，如 status 類型的字段。

二、慢 SQL 問題

2.1 導致慢 SQL 的原因

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

1. SQL編寫問題

2. 鎖

3. 業務實例相互干繞對 IO/CPU 資源爭用

4. 服務器硬件

5. MYSQL BUG

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

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

1. 字段類型轉換導致不用索引，如字符串類型的不用引號，數字類型的用引號等，這有可能會用不到索引導致全表掃描；

2. mysql 不支持函數轉換，所以字段前面不能加函數，否則這將用不到索引；

3. 不要在字段前面加減運算；

4. 字符串比較長的可以考慮索引一部份減少索引文件大小，提高寫入效率；

5. like % 在前面用不到索引；

6. 根據聯合索引的第二個及以后的字段單獨查詢用不到索引；

7. 不要使用 select *；

8. 排序請盡量使用升序 ;

9. or 的查詢盡量用 union 代替 （Innodb）；

10. 復合索引高選擇性的字段排在前面；

11. order by / group by 字段包括在索引當中減少排序，效率會更高。

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

1. 盡量規避大事務的 SQL，大事務的 SQL 會影響數據庫的并發性能及主從同步；

2. 分頁語句 limit 的問題；

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

4. 不讓 mysql 干多余的事情，如計算；

5. 輸寫 SQL 帶字段，以防止后面表變更帶來的問題，性能也是比較優的 ( 涉及到數據字典解析，請自行查詢資料)；

6. 在 Innodb上用 select count(*)，因為 Innodb 會存儲統計信息；

7. 慎用 Oder by rand()。

三、分析診斷工具

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

1. mysqldumpslow

2. mysql profile

3. mysql explain

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

四、誤操作、程序 bug 時怎么辦

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

到此，相信大家對“mysql數據庫開發常見的問題及優化”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！