接上篇Mysql數(shù)據(jù)庫性能優(yōu)化二
對(duì)表進(jìn)行水平劃分
如果一個(gè)表的記錄數(shù)太多了,比如上千萬條,而且需要經(jīng)常檢索,那么我們就有必要化整為零了。如果我拆成100個(gè)表,那么每個(gè)表只有10萬條記錄。當(dāng)然這需要數(shù)據(jù)在邏輯上可以劃分。一個(gè)好的劃分依據(jù),有利于程序的簡單實(shí)現(xiàn),也可以充分利用水平分表的優(yōu)勢(shì)。比如系統(tǒng)界面上只提供按月查詢的功能,那么把表按月拆分成12個(gè),每個(gè)查詢只查詢一個(gè)表就夠了。如果非要按照地域來分,即使把表拆的再小,查詢還是要聯(lián)合所有表來查,還不如不拆了。所以一個(gè)好的拆分依據(jù)是 最重要的。關(guān)鍵字:UNION
例:
- 訂單表根據(jù)訂單產(chǎn)生時(shí)間來分表(一年一張)
- 學(xué)生情況表
- 查詢電話費(fèi),近三個(gè)月的數(shù)據(jù)放入一張表,一年內(nèi)的放入到另一張表
對(duì)表進(jìn)行垂直劃分
有些表記錄數(shù)并不多,可能也就2、3萬條,但是字段卻很長,表占用空間很大,檢索表時(shí)需要執(zhí)行大量I/O,嚴(yán)重降低了性能。這個(gè)時(shí)候需要把大的字段拆分到另一個(gè)表,并且該表與原表是一對(duì)一的關(guān)系。 (JOIN)
【試題內(nèi)容】、【答案信息】兩個(gè)表,最初是作為幾個(gè)字段添加到【試題信息】里的,可以看到試題內(nèi)容和答案這兩個(gè)字段很長,在表里有3萬記錄時(shí),表已經(jīng)占 了1G的空間,在列試題列表時(shí)非常慢。經(jīng)過分析,發(fā)現(xiàn)系統(tǒng)很多時(shí)候是根據(jù)【冊(cè)】、【單元】、類型、類別、難易程度等查詢條件,分頁顯示試題詳細(xì)內(nèi)容。而每 次檢索都是這幾個(gè)表做join,每次要掃描一遍1G的表。我們完全可以把內(nèi)容和答案拆分成另一個(gè)表,只有顯示詳細(xì)內(nèi)容的時(shí)候才讀這個(gè)大表,由此 就產(chǎn)生了【試題內(nèi)容】、【答案信息】兩個(gè)表。
選擇適當(dāng)?shù)淖侄晤愋?,特別是主鍵
選擇字段的一般原則是保小不保大,能用占用字節(jié)小的字段就不用大字段。比如主鍵, 建議使用自增類型,這樣省空間,空間就是效率!按4個(gè)字節(jié)和按32個(gè)字節(jié)定位一條記錄,誰快誰慢太明顯了。涉及到幾個(gè)表做join時(shí),效果就更明顯了。
建議使用一個(gè)不含業(yè)務(wù)邏輯的id做主角如s1001。例:
int 4 bigint 8 mediumint smallint 2 tinyint 1
md5 char(32)
id :整數(shù) tinyint samllint int bigint
student表
id stuno stuname adress
s1001 小民 深圳
文件、圖片等大文件用文件系統(tǒng)存儲(chǔ)
數(shù)據(jù)庫只存儲(chǔ)路徑。圖片和文件存放在文件系統(tǒng),甚至單獨(dú)放在一臺(tái)服務(wù)器(圖床 / 視頻服務(wù)器 ).
數(shù)據(jù)庫參數(shù)配置
最重要的參數(shù)就是內(nèi)存,我們主要用的innodb引擎,所以下面兩個(gè)參數(shù)調(diào)的很大
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G
對(duì)于myisam,需要調(diào)整key_buffer_size,當(dāng)然調(diào)整參數(shù)還是要看狀態(tài),用show status語句可以看到當(dāng)前狀態(tài),以決定改調(diào)整哪些參數(shù)
在my.ini修改端口3306,默認(rèn)存儲(chǔ)引擎和最大連接數(shù)
在my.ini中.
port=3306 [有兩個(gè)地方修改]
default-storage-engine=INNODB
max_connections=100
合理的硬件資源和操作系統(tǒng)
如果你的機(jī)器內(nèi)存超過4G,那么毋庸置疑應(yīng)當(dāng)采用64位操作系統(tǒng)和64位mysql 5.5.19 or mysql5.6
讀寫分離
如果數(shù)據(jù)庫壓力很大,一臺(tái)機(jī)器支撐不了,那么可以用mysql復(fù)制實(shí)現(xiàn)多臺(tái)機(jī)器同步,將數(shù)據(jù)庫的壓力分散。
Master
Slave1
Slave2
Slave3
主庫master用來寫入,slave1—slave3都用來做select,每個(gè)數(shù)據(jù)庫分擔(dān)的壓力小了很多。
要實(shí)現(xiàn)這種方式,需要程序特別設(shè)計(jì),寫都操作master,讀都操作slave,給程序開發(fā)帶來了額外負(fù)擔(dān)。當(dāng)然目前已經(jīng)有中間件來實(shí)現(xiàn)這個(gè)代理,對(duì)程 序來讀寫哪些數(shù)據(jù)庫是透明的。官方有個(gè)mysql-proxy,但是還是alpha版本的。新浪有個(gè)amobe for mysql,也可達(dá)到這個(gè)目的,結(jié)構(gòu)如下
定時(shí)完成數(shù)據(jù)庫的備份
項(xiàng)目實(shí)際需求,請(qǐng)完成定時(shí)備份某個(gè)數(shù)據(jù)庫,或者定時(shí)備份數(shù)據(jù)庫的某些表的操作
windows 下每隔1小時(shí),備份一次數(shù)據(jù)newsdb
windows 每天晚上2:00 備份 newsdb 下 某一張表
cmd> mysqldump –u root –p密碼 數(shù)據(jù)庫名 > 把數(shù)據(jù)庫放入到某個(gè)目錄
案例,備份 mydb 庫的所有表
進(jìn)入mysqldump所在的目錄
cmd> mysqldump –u root –phsp shop> d:/shop.log [把shop數(shù)據(jù)庫的所有表全部導(dǎo)出]
cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop數(shù)據(jù)庫的 temusers和emp導(dǎo)出]
如何恢復(fù)數(shù)據(jù)的表
進(jìn)入的mysql操作界面
mysql>source 備份文件的全路徑
定時(shí)備份:(把命令寫入到my.bat 問中)
windows 如何定時(shí)備份 (每天凌晨2:00)
使用windows自帶的計(jì)劃任務(wù),定時(shí)執(zhí)行批處理命令。
增量備份和還原
定義:mysql數(shù)據(jù)庫會(huì)以二進(jìn)制的形式,自動(dòng)把用戶對(duì)mysql數(shù)據(jù)庫的操作,記錄到文件,當(dāng)用戶希望恢復(fù)的時(shí)候,可以使用備份文件進(jìn)行恢復(fù)。
增量備份會(huì)記錄dml語句、創(chuàng)建表的語句,不會(huì)記錄select。記錄的東西包括:sql語句本身、操作時(shí)間,位置
進(jìn)行增量備份的步驟和恢復(fù)
注意:mysql5.0及之前的版本是不支持增量備份的
1、配置my.ini文件或者my.conf,啟用二進(jìn)制備份。
打開my.ini文件,查找log-bin,進(jìn)行配置:log-bin=G:\Database\mysqlbinlog\mylog
在G:\Database目錄下面新建目錄mysqlbinlog
2、重啟mysql服務(wù)
這個(gè)時(shí)候會(huì)在mysqlbinlog目錄下面看到以下兩個(gè)文件:
mylog.000001:日志備份文件。如果要查看這個(gè)日志文件里面的信息,我們可以使用mysqlbinlog程序查看,mysqlbinlog程序存放在mysql的bin目錄下面(“C:\Program Files\MySQL\MySQL Server 5.6\bin”)。
執(zhí)行sql語句
UPDATE emp set ename='zouqj' where empno=100003;
開始——運(yùn)行——cmd,mysqlbinlog 備份文件路徑
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog G:\Database\mysqlbinlog\mylog.000001
mylog.index:日志索引文件,里面記錄了所以的日志文件。(G:\Database\mysqlbinlog\mylog.000001)
3、假設(shè)現(xiàn)在問題來了,我這條update是誤操作,如何進(jìn)行恢復(fù)
在mysql日志中會(huì)記錄每一次操作的時(shí)間和位置,所以我們既可以根據(jù)時(shí)間來恢復(fù),也可以根據(jù)位置來恢復(fù)。
那么,我們現(xiàn)在馬上可以從上圖看出,這條語句產(chǎn)生的時(shí)間是"2016-04-17 12:01:36",位置是614
按時(shí)間來恢復(fù)
我們可以選擇在語句產(chǎn)生時(shí)間的前一秒
執(zhí)行cmd命令:mysqlbinlog --stop-datetime="2016-04-17 12:01:35" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p
這個(gè)時(shí)候我再執(zhí)行SQL語句查看
SELECT * from emp where empno=100003;
結(jié)果變成了
按位置來恢復(fù)
執(zhí)行cmd命令:mysqlbinlog --stop-position="614" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p
這個(gè)時(shí)候再執(zhí)行SQL來查看結(jié)果,又變回來了。
以上就是本文的全部內(nèi)容,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助,同時(shí)也希望多多支持腳本之家!
您可能感興趣的文章:- 優(yōu)化Mysql數(shù)據(jù)庫的8個(gè)方法
- mysql數(shù)據(jù)庫優(yōu)化總結(jié)(心得)
- MySQL優(yōu)化全攻略-相關(guān)數(shù)據(jù)庫命令
- MySQL數(shù)據(jù)庫優(yōu)化詳解
- mysql中優(yōu)化和修復(fù)數(shù)據(jù)庫工具mysqlcheck詳細(xì)介紹
- mysql數(shù)據(jù)庫查詢優(yōu)化 mysql效率
- Mysql數(shù)據(jù)庫性能優(yōu)化一
- MySQL數(shù)據(jù)庫21條最佳性能優(yōu)化經(jīng)驗(yàn)
- mysql數(shù)據(jù)庫常見的優(yōu)化操作總結(jié)(經(jīng)驗(yàn)分享)
- mysql數(shù)據(jù)庫sql優(yōu)化原則(經(jīng)驗(yàn)總結(jié))
- 詳解MySQL數(shù)據(jù)庫優(yōu)化的八種方式(經(jīng)典必看)
- MYSQL數(shù)據(jù)庫表結(jié)構(gòu)優(yōu)化方法詳解