mysql數(shù)據(jù)庫開發(fā)常見的問題及優(yōu)化

本篇內(nèi)容主要講解“mysql數(shù)據(jù)庫開發(fā)常見的問題及優(yōu)化”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“mysql數(shù)據(jù)庫開發(fā)常見的問題及優(yōu)化”吧!

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

1.1 引擎選擇

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

mysql 常用的存儲(chǔ)引擎包括 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 : 全表鎖，存儲(chǔ)在內(nèi)存當(dāng)中，速度快，但會(huì)占用和數(shù)據(jù)量成正比的內(nèi)存空間且數(shù)據(jù)在 mysql 重啟時(shí)會(huì)丟失。

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

1.2 分表方法

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

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

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

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

3. 實(shí)現(xiàn)核心與非核心的分級存儲(chǔ)，如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ù)庫的存儲(chǔ)空間，二是在插入和修改數(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)致不用索引，如字符串類型的不用引號(hào)，數(shù)字類型的用引號(hào)等，這有可能會(huì)用不到索引導(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)中減少排序，效率會(huì)更高。

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

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

2. 分頁語句 limit 的問題；

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

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

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

6. 在 Innodb上用 select count(*)，因?yàn)?Innodb 會(huì)存儲(chǔ)統(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ā)工作者會(huì)比較緊張。一個(gè)成熟的企業(yè)往往會(huì)有完善的數(shù)據(jù)管理規(guī)范和較豐富的數(shù)據(jù)恢復(fù)方案（初創(chuàng)公司除外），會(huì)進(jìn)行數(shù)據(jù)備份和數(shù)據(jù)容災(zāi)。當(dāng)你發(fā)現(xiàn)誤操作或程序 bug 導(dǎo)致線上數(shù)據(jù)被誤刪或誤改動(dòng)時(shí)，一定不能慌亂，應(yīng)及時(shí)與 DBA 聯(lián)系，第一時(shí)間進(jìn)行數(shù)據(jù)恢復(fù)（嚴(yán)重時(shí)直接停止服務(wù)），盡可能減少影響和損失。對于重要數(shù)據(jù)（如資金）的操作，在開發(fā)時(shí)一定要反復(fù)進(jìn)行測試，確保沒有問題后再上線。

到此，相信大家對“mysql數(shù)據(jù)庫開發(fā)常見的問題及優(yōu)化”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！