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

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

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

庫表設(shè)計

慢 SQL 問題

誤操作、程序 bug 時怎么辦

一、庫表設(shè)計

1.1 引擎選擇

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

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

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

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

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

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

1.2 分表方法

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

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

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

鎖爭用機率?。?/p>

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

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

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

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

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

1.3 索引問題

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

主鍵索此 ( PRIMARY )

唯一索引 ( UNIQUE )

普通索引 ( INDEX )

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

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

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

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

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

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

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

二、慢 SQL 問題

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

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

SQL編寫問題

鎖

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

服務(wù)器硬件

MYSQL BUG

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

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

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

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

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

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

like % 在前面用不到索引；

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

不要使用 select *；

排序請盡量使用升序 ;

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

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

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

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

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

分頁語句 limit 的問題；

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

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

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

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

慎用 Oder by rand()。

三、分析診斷工具

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

mysqldumpslow

mysql profile

mysql explain

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

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

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