明輝手游網(wǎng)中心:是一個(gè)免費(fèi)提供流行視頻軟件教程、在線學(xué)習(xí)分享的學(xué)習(xí)平臺(tái)!

Mysql數(shù)據(jù)庫優(yōu)化的方法總結(jié)(必看)

[摘要]本篇文章給大家?guī)淼膬?nèi)容是關(guān)于Mysql數(shù)據(jù)庫優(yōu)化的方法總結(jié),有一定的參考價(jià)值,有需要的朋友可以參考一下,希望對(duì)你有所幫助。學(xué)無止境,數(shù)據(jù)庫優(yōu)化分為方方面面,在這里,我進(jìn)行了比較全的總結(jié),分享給正在...
本篇文章給大家?guī)淼膬?nèi)容是關(guān)于Mysql數(shù)據(jù)庫優(yōu)化的方法總結(jié),有一定的參考價(jià)值,有需要的朋友可以參考一下,希望對(duì)你有所幫助。

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

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

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

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

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

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

5、對(duì)sql語句的一些優(yōu)化,(查詢執(zhí)行速度比較慢的sql語句);

6、分表分區(qū)(分表:把一張大表分成多張表。分區(qū):把一張表里面的分配到不同的區(qū)域存儲(chǔ));

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

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

一、三范式

第一范式:

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

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

非關(guān)系型數(shù)據(jù)庫(泛指nosql數(shù)據(jù)庫):memcache、redis、momgodb等。

第二范式:

一個(gè)表中沒有完全相同的記錄,通過一個(gè)主鍵即能解決

第三范式:

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

反三范式設(shè)計(jì):

相冊(cè)表
ID相冊(cè)名稱相冊(cè)瀏覽量
1生活100
2工作照100
照片表
ID照片名稱相冊(cè)ID瀏覽量
1我的小狗149
2我的小貓151
3我的同事2100

如果要算一個(gè)相冊(cè)的瀏覽量,我們可以在相冊(cè)表中添加相冊(cè)瀏覽量字段,瀏覽照片的時(shí)候同時(shí)更新相冊(cè)瀏覽量。

二、開啟慢查詢

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

1.查看慢查詢記錄時(shí)間:

show variables like ‘long_query_time’;

2.修改慢查詢時(shí)間:

set long_query_time=2;

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

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

三、建立索引

1、主鍵索引的特點(diǎn):

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

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

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

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

2、唯一索引的特點(diǎn):

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

(2)一個(gè)唯一索引可以指向多個(gè)列,

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

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

3、普通索引:

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

4、全文索引

mysql自帶的全文索引mysql5.5不支持中文,支持英文,同時(shí)要求表的存儲(chǔ)引擎是myisam。如果希望支持中文,有兩個(gè)方案,

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

(2)插件mysqlcft。

添加索引主要的問題:

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

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

四、分表、分區(qū)

垂直分表(內(nèi)容主表+附加表):

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

可以使用多個(gè)附加表,附加表存儲(chǔ)一些數(shù)據(jù)的獨(dú)特的信息。

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

特點(diǎn):表結(jié)構(gòu)不同

水平分表:

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

特點(diǎn):表結(jié)構(gòu)相同

分區(qū):

就是把一個(gè)表存儲(chǔ)到磁盤不同區(qū)域,仍然是一張表。

基本的概念:

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

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

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

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

分區(qū)表的限制:

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

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

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

(4)按日期進(jìn)行分區(qū)很非常適合,因?yàn)楹芏嗳掌诤瘮?shù)可以用。但是對(duì)于字符串來說合適的分區(qū)函數(shù)不太多。

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

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

mysql 的鎖有以下幾種形式:

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

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

表鎖的演示:

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

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

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

4.鎖表的語法:

lock table 表名 read write

5.也可以鎖定多個(gè)表

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

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

行鎖的演示:

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

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

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

相關(guān)推薦:

以上就是Mysql數(shù)據(jù)庫優(yōu)化的方法總結(jié)(必看)的詳細(xì)內(nèi)容,更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章!


學(xué)習(xí)教程快速掌握從入門到精通的SQL知識(shí)。