mysql 數(shù)據(jù)庫是被廣泛應用的關系型數(shù)據(jù)庫，其體積小、支持多處理器、開源并免費的特性使其在 Internet 中小型網(wǎng)站中的使用率尤其高。在使用 mysql 的過程中不規(guī)范的 SQL 編寫、非最優(yōu)的策略選擇都可能導致系統(tǒng)性能甚至功能上的缺陷。

　　恰巧就在前幾天，本人所在公司的云事業(yè)部舉辦了一場關于 mysql 的技術交流會，其中一個 part 正是聚焦于開發(fā)過程中 mysql 數(shù)據(jù)庫設計及使用的常見問題，并提出相關優(yōu)化方案。根據(jù)會議內(nèi)容并查閱相關資料，本人對這個 part 進行了一次小結，結合自己的工作經(jīng)歷及理解形成此文以供分享，希望能有助于各位同行解決工作中的相關問題。

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

　　1. 庫表設計

　　2. 慢 SQL 問題

　　3. 誤操作、程序 bug 時怎么辦

　　一、庫表設計

　　1.1 引擎選擇

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

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

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

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

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

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

　　1.2 分表方法

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

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

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

　　2. 鎖爭用機率??；

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

　　4. 解決了數(shù)據(jù)庫同步壓力問題。

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

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

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

　　1.3 索引問題

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

　　1. 主鍵索此 ( PRIMARY )

　　2. 唯一索引 ( UNIQUE )

　　3. 普通索引 ( INDEX )

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

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

　　在設計表或索引時，常出現(xiàn)以下幾個問題：

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

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

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

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

　　二、慢 SQL 問題

　　2.1 導致慢 SQL 的原因

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

　　1. SQL編寫問題

　　2. 鎖

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

　　4. 服務器硬件

　　5. MYSQL BUG

　　2.2 由 SQL 編寫導致的慢 SQL 優(yōu)化

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

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

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

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

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

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

　　6. 根據(jù)聯(lián)合索引的第二個及以后的字段單獨查詢用不到索引；

　　7. 不要使用 select *；

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

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

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

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

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

　　1. 盡量規(guī)避大事務的 SQL，大事務的 SQL 會影響數(shù)據(jù)庫的并發(fā)性能及主從同步；

　　2. 分頁語句 limit 的問題；

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

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

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

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

　　7. 慎用 Oder by rand()。

　　三、分析診斷工具

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

　　1. mysqldumpslow

　　2. mysql profile

　　3. mysql explain

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

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

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

來源：ImportNew