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

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

本文將就以下三個(gè)問題進(jìn)行展開：

1、庫表設(shè)計(jì)

2、慢 SQL 問題

3、誤操作、程序 bug 時(shí)怎么辦

一、庫表設(shè)計(jì)

1.1 引擎選擇

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

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

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

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

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

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

1.2 分表方法

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

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

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

2、鎖爭用機(jī)率小；

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

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

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

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

2、單表過大造成的單服務(wù)器空間問題。

1.3 索引問題

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

1、主鍵索此 ( PRIMARY )

2、唯一索引 ( UNIQUE )

3、普通索引 ( INDEX )

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

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

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

1、少建索引或不建索引。這個(gè)問題最突出，建議建表時(shí) DBA 可以一起協(xié)助把關(guān)。

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

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

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

二、慢 SQL 問題

2.1 導(dǎo)致慢 SQL 的原因

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

1、SQL編寫問題

2、鎖

3、業(yè)務(wù)實(shí)例相互干繞對 IO/CPU 資源爭用

4、服務(wù)器硬件

5、MYSQL BUG

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

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

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

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

3、不要在字段前面加減運(yùn)算；

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

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

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

7、不要使用 select *；

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

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

10、復(fù)合索引高選擇性的字段排在前面；

11、order by / group by 字段包括在索引當(dāng)中減少排序，效率會更高。

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

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

2、分頁語句 limit 的問題；

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

4、不讓 mysql 干多余的事情，如計(jì)算；

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

6、在 Innodb上用 select count(*)，因?yàn)?Innodb 會存儲統(tǒng)計(jì)信息；

7、慎用 Oder by rand()。

三、分析診斷工具

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

1、mysqldumpslow

2、mysql profile

3、mysql explain

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

四、誤操作、程序 bug 時(shí)怎么辦

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