電子商務(wù)網(wǎng)站建設(shè)Mysql開發(fā)與優(yōu)化

　　mysql，一般熟悉互聯(lián)網(wǎng)的人應(yīng)該都清楚是數(shù)據(jù)庫(kù)，電子商務(wù)網(wǎng)站建設(shè)其中有一項(xiàng)重要的工程就是mysql開發(fā)與優(yōu)化，下面我們就來(lái)講講電子商務(wù)網(wǎng)站建設(shè)Mysql開發(fā)與優(yōu)化。

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

　　1.1 引擎選擇

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

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

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

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

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

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

　　表與表之間資源爭(zhēng)用問(wèn)題；

　　鎖爭(zhēng)用機(jī)率??；

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

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

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

　　單表過(guò)大造成的性能問(wèn)題；

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

　　1.3 索引問(wèn)題

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

　　主鍵索此 ( PRIMARY )

　　唯一索引 ( UNIQUE )

　　普通索引 ( INDEX )

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

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

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

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

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

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

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

　　二、慢 SQL 問(wèn)題

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

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

　　SQL編寫問(wèn)題

　　鎖

　　業(yè)務(wù)實(shí)例相互干繞對(duì) IO/CPU 資源爭(zhēng)用

　　服務(wù)器硬件

　　MYSQL BUG

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

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

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

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

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

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

　　like % 在前面用不到索引；

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

　　不要使用 select *；

　　排序請(qǐng)盡量使用升序 ;

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

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

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

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

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

　　分頁(yè)語(yǔ)句 limit 的問(wèn)題；

　　刪除表所有記錄請(qǐng)用 truncate，不要用 delete；

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

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

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

　　慎用 Oder by rand()。

　　三、分析診斷工具

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

　　mysqldumpslow

　　mysql profile

　　mysql explain

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

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

　　提出這個(gè)問(wèn)題顯然主要是針對(duì)剛開始工作的年輕同行們……實(shí)際上誤操作和程序 bug 導(dǎo)致數(shù)據(jù)誤刪或者混亂的問(wèn)題并非少見，但是剛?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ù)），盡可能減少影響和損失。對(duì)于重要數(shù)據(jù)（如資金）的操作，在開發(fā)時(shí)一定要反復(fù)進(jìn)行測(cè)試，確保沒(méi)有問(wèn)題后再上線。

　　MySQL的優(yōu)化不僅對(duì)網(wǎng)站有很好的幫助也對(duì)網(wǎng)站排名會(huì)起到一定的作用，上述簡(jiǎn)單介紹了一下Mysql開發(fā)與優(yōu)化，這是個(gè)技術(shù)活，想做好沒(méi)那么容易。