Mysql數(shù)據(jù)庫優(yōu)化的方法總結(jié)(必看)
發(fā)表時(shí)間:2023-07-16 來源:明輝站整理相關(guān)軟件相關(guān)文章人氣:
[摘要]本篇文章給大家?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 | 我的小狗 | 1 | 49 |
2 | 我的小貓 | 1 | 51 |
3 | 我的同事 | 2 | 100 |
如果要算一個(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í)。