怎么優(yōu)化Mysql數(shù)據(jù)庫

這篇文章將為大家詳細(xì)講解有關(guān)怎么優(yōu)化MySQL數(shù)據(jù)庫，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

從策劃到設(shè)計制作，每一步都追求做到細(xì)膩，制作可持續(xù)發(fā)展的企業(yè)網(wǎng)站。為客戶提供成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站策劃、網(wǎng)頁設(shè)計、域名注冊、網(wǎng)站空間、網(wǎng)絡(luò)營銷、VI設(shè)計、 網(wǎng)站改版、漏洞修補(bǔ)等服務(wù)。為客戶提供更好的一站式互聯(lián)網(wǎng)解決方案,以客戶的口碑塑造優(yōu)易品牌,攜手廣大客戶,共同發(fā)展進(jìn)步。

學(xué)無止境，數(shù)據(jù)庫優(yōu)化分為方方面面，在這里，我進(jìn)行了比較全的總結(jié)，分享給正在工作或者學(xué)習(xí)的同行們。

數(shù)據(jù)庫的優(yōu)化分為以下七大方面：

1、表的設(shè)計要符合三范式（適當(dāng)?shù)姆慈妒揭部梢裕?/p>

2、添加適當(dāng)?shù)乃饕?，索引對查詢速度影響很大，必須添加索引（主鍵索引，唯一索引，普通索引，全文索引）；

3、添加適當(dāng)存儲過程，觸發(fā)器，事務(wù)等；

4、讀寫分離（主從數(shù)據(jù)庫）；

5、對sql語句的一些優(yōu)化，（查詢執(zhí)行速度比較慢的sql語句）；

6、分表分區(qū)（分表：把一張大表分成多張表。分區(qū)：把一張表里面的分配到不同的區(qū)域存儲）；

7、對mysql服務(wù)器硬件的升級操作。

接下來我將具體講解優(yōu)化的方式。

一、三范式

第一范式：

原子性：表里面的字段不能再分割，只要是關(guān)系型數(shù)據(jù)庫，就天然的自動滿足第一范式

關(guān)系型數(shù)據(jù)庫（有行和列的概念）：mysql、sql server、oracle、db2、infomix、sybase、postgresql，在設(shè)計時，先有庫->表->字段->具體記錄（內(nèi)容）：在存儲數(shù)據(jù)時，要設(shè)計字段。

非關(guān)系型數(shù)據(jù)庫（泛指NoSql數(shù)據(jù)庫）：memcache、redis、momgodb等。

第二范式：

一個表中沒有完全相同的記錄，通過一個主鍵即能解決

第三范式：

表中不能存儲冗余數(shù)據(jù)

反三范式設(shè)計：

相冊表

ID	相冊名稱	相冊瀏覽量
1	生活照	100
2	工作照	100

照片表

ID	照片名稱	相冊ID	瀏覽量
1	我的小狗	1	49
2	我的小貓	1	51
3	我的同事	2	100

如果要算一個相冊的瀏覽量，我們可以在相冊表中添加相冊瀏覽量字段，瀏覽照片的時候同時更新相冊瀏覽量。

二、開啟慢查詢

Mysql慢查詢默認(rèn)是關(guān)閉的，默認(rèn)記錄超過10秒的sql語句。

1.查看慢查詢記錄時間：

show variables like ‘long_query_time’;

2.修改慢查詢時間：

set long_query_time=2;

3.通過如下的一個函數(shù)來進(jìn)行測試：

benchmark(count,expr)   函數(shù)可以測試執(zhí)行count次expr操作需要的時間

三、建立索引

1、主鍵索引的特點：

(1)一個表中最多只有一個主鍵索引

(2)一個主鍵索引可以指向多個列

(3)主鍵索引的列，不能有重復(fù)的值，也不能有null

(4)主鍵索引的效率高。

2、唯一索引的特點：

(1)一個表中可以有多個唯一索引

(2)一個唯一索引可以指向多個列，

(3)如果在唯一索引上，沒有指定not null，則該列可以為空，同時可以有多個null,

(4)唯一索引的效率較高。

3、普通索引：

使用普通索引主要是提高查詢效率

4、全文索引

mysql自帶的全文索引mysql5.5不支持中文，支持英文，同時要求表的存儲引擎是myisam。如果希望支持中文，有兩個方案，

(1)使用aphinx中文版coreseek (來替代全文索引)

(2)插件mysqlcft。

添加索引主要的問題：

(1)較頻繁的作為查詢條件字段應(yīng)該創(chuàng)建索引，唯一性太差的字段不適合單獨創(chuàng)建索引，即使頻繁作為查詢條件，更新非常頻繁的字段不適合創(chuàng)建索引

(2)不會出現(xiàn)在WHERE子句中字段不該創(chuàng)建索，索引是由代價的，雖然是查詢速度提高了，但是，會影響增該刪的效率。而且索引文件會占用空間。

四、分表、分區(qū)

垂直分表（內(nèi)容主表+附加表）：

內(nèi)容主表：存儲各種數(shù)據(jù)的一些公共信息，比如數(shù)據(jù)的名稱，添加時間等，

可以使用多個附加表，附加表存儲一些數(shù)據(jù)的獨特的信息。

主要原因：是內(nèi)容主表里面的數(shù)據(jù)訪問比較頻繁。

特點：表結(jié)構(gòu)不同

水平分表：

將表數(shù)據(jù)存在不同的表中。

特點：表結(jié)構(gòu)相同

分區(qū)：

就是把一個表存儲到磁盤不同區(qū)域，仍然是一張表。

基本的概念：

(1)Range（范圍）–這種模式允許將數(shù)據(jù)劃分不同范圍。例如可以將一個表通過年份劃分成若干個分區(qū)。

(2)List（預(yù)定義列表）–這種模式允許系統(tǒng)通過預(yù)定義的列表的值來對數(shù)據(jù)進(jìn)行分割。

(3)Hash（哈希）–這中模式允許通過對表的一個或多個列的Hash Key進(jìn)行計算，最后通過這個Hash碼不同數(shù)值對應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。例如可以建立一個對表主鍵進(jìn)行分區(qū)的表。

(4)Key（鍵值）-上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。

分區(qū)表的限制：

(1)只能對數(shù)據(jù)表的整型列進(jìn)行分區(qū)，或者數(shù)據(jù)列可以通過分區(qū)函數(shù)轉(zhuǎn)化成整型列。

(2)最大分區(qū)數(shù)目不能超過1024。

(3)如果含有唯一索引或者主鍵，則分區(qū)列必須包含在所有的唯一索引或者主鍵在內(nèi)。

(4)按日期進(jìn)行分區(qū)很非常適合，因為很多日期函數(shù)可以用。但是對于字符串來說合適的分區(qū)函數(shù)不太多。

五、并發(fā)處理的鎖機(jī)制

鎖機(jī)制：在執(zhí)行時，只有一個用戶獲得鎖，其他用戶處于阻塞狀態(tài)，需要等待解鎖。

mysql 的鎖有以下幾種形式：

表級鎖：開銷小，加鎖快，發(fā)生鎖沖突的概率最高，并發(fā)度最低。myisam引擎屬于這種類型。

行級鎖：開銷大，加鎖慢，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。innodb屬于這種類型。

表鎖的演示：

1.對myisam表的讀操作（加讀鎖），不會阻塞其他進(jìn)程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當(dāng)讀鎖釋放后，才會執(zhí)行其他進(jìn)程的操作。

2.表添加讀鎖后，其他進(jìn)程對該表只能查詢操作，修改時會被阻塞。

3.當(dāng)前進(jìn)程，能夠執(zhí)行查詢操作，不能執(zhí)行修改操作。不能對沒有鎖定的表進(jìn)行操作。

4.鎖表的語法：

lock table 表名 read|write

5.也可以鎖定多個表

6.對myisam表的寫操作（加寫鎖），會阻塞其他進(jìn)程對鎖定表的任何操作，不能讀寫，

7.表加寫鎖后，則只有當(dāng)前進(jìn)程對鎖定的表，可以執(zhí)行任何操作。其他進(jìn)程的操作會被阻塞。

行鎖的演示：

1.innodb存儲引擎是通過給索引上的索引項加鎖來實現(xiàn)的，這就意味著：只有通過索引條件檢索數(shù)據(jù)，innodb才會使用行級鎖，否則，innodb使用表鎖。

2.開啟行鎖后，當(dāng)前進(jìn)程在針對某條記錄執(zhí)行操作時，其他進(jìn)程不能操作和當(dāng)前進(jìn)程相同id的記錄。

php里面有文件鎖，在實際的項目中多數(shù)使用文件鎖，因為表鎖，會阻塞，當(dāng)對一些表添加寫鎖后，其他進(jìn)程就不能操作了。這樣會阻塞整個網(wǎng)站，會拖慢網(wǎng)站的速度。

關(guān)于怎么優(yōu)化Mysql數(shù)據(jù)庫就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

文章名稱：怎么優(yōu)化Mysql數(shù)據(jù)庫
網(wǎng)址分享：http://muchs.cn/article6/ihchig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、網(wǎng)站制作、網(wǎng)站建設(shè)、微信小程序、動態(tài)網(wǎng)站、自適應(yīng)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)