Mysql數(shù)據(jù)庫性能優(yōu)化三（分表、增量備份、還原）

接上篇Mysql數(shù)據(jù)庫性能優(yōu)化二

對表進(jìn)行水平劃分          

如果一個表的記錄數(shù)太多了，比如上千萬條，而且需要經(jīng)常檢索，那么我們就有必要化整為零了。如果我拆成100個表，那么每個表只有10萬條記錄。當(dāng)然這需要數(shù)據(jù)在邏輯上可以劃分。一個好的劃分依據(jù)，有利于程序的簡單實現(xiàn)，也可以充分利用水平分表的優(yōu)勢。比如系統(tǒng)界面上只提供按月查詢的功能，那么把表按月拆分成12個，每個查詢只查詢一個表就夠了。如果非要按照地域來分，即使把表拆的再小，查詢還是要聯(lián)合所有表來查，還不如不拆了。所以一個好的拆分依據(jù)是 最重要的。關(guān)鍵字：UNION

例：

• 訂單表根據(jù)訂單產(chǎn)生時間來分表（一年一張）
• 學(xué)生情況表
• 查詢電話費(fèi)，近三個月的數(shù)據(jù)放入一張表，一年內(nèi)的放入到另一張表

對表進(jìn)行垂直劃分 

有些表記錄數(shù)并不多，可能也就2、3萬條，但是字段卻很長，表占用空間很大，檢索表時需要執(zhí)行大量I/O，嚴(yán)重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關(guān)系。 (JOIN)       

【試題內(nèi)容】、【答案信息】兩個表，最初是作為幾個字段添加到【試題信息】里的，可以看到試題內(nèi)容和答案這兩個字段很長，在表里有3萬記錄時，表已經(jīng)占 了1G的空間，在列試題列表時非常慢。經(jīng)過分析，發(fā)現(xiàn)系統(tǒng)很多時候是根據(jù)【冊】、【單元】、類型、類別、難易程度等查詢條件，分頁顯示試題詳細(xì)內(nèi)容。而每 次檢索都是這幾個表做join，每次要掃描一遍1G的表。我們完全可以把內(nèi)容和答案拆分成另一個表，只有顯示詳細(xì)內(nèi)容的時候才讀這個大表，由此 就產(chǎn)生了【試題內(nèi)容】、【答案信息】兩個表。

選擇適當(dāng)?shù)淖侄晤愋停貏e是主鍵     

選擇字段的一般原則是保小不保大，能用占用字節(jié)小的字段就不用大字段。比如主鍵， 建議使用自增類型，這樣省空間,空間就是效率!按4個字節(jié)和按32個字節(jié)定位一條記錄，誰快誰慢太明顯了。涉及到幾個表做join時，效果就更明顯了。

建議使用一個不含業(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)存儲

數(shù)據(jù)庫只存儲路徑。圖片和文件存放在文件系統(tǒng)，甚至單獨(dú)放在一臺服務(wù)器(圖床 / 視頻服務(wù)器 ).

數(shù)據(jù)庫參數(shù)配置

最重要的參數(shù)就是內(nèi)存，我們主要用的innodb引擎，所以下面兩個參數(shù)調(diào)的很大

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

對于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)存儲引擎和最大連接數(shù)

在my.ini中.
port=3306 [有兩個地方修改]
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ù)庫壓力很大，一臺機(jī)器支撐不了，那么可以用mysql復(fù)制實現(xiàn)多臺機(jī)器同步，將數(shù)據(jù)庫的壓力分散。

    Master
　　Slave1
　　Slave2
　　Slave3

主庫master用來寫入，slave1—slave3都用來做select，每個數(shù)據(jù)庫分擔(dān)的壓力小了很多。
要實現(xiàn)這種方式，需要程序特別設(shè)計，寫都操作master，讀都操作slave，給程序開發(fā)帶來了額外負(fù)擔(dān)。當(dāng)然目前已經(jīng)有中間件來實現(xiàn)這個代理，對程 序來讀寫哪些數(shù)據(jù)庫是透明的。官方有個mysql-proxy，但是還是alpha版本的。新浪有個amobe for mysql，也可達(dá)到這個目的，結(jié)構(gòu)如下

定時完成數(shù)據(jù)庫的備份

項目實際需求，請完成定時備份某個數(shù)據(jù)庫，或者定時備份數(shù)據(jù)庫的某些表的操作

windows 下每隔1小時，備份一次數(shù)據(jù)newsdb

windows 每天晚上2:00   備份 newsdb 下 某一張表

cmd> mysqldump –u root –p密碼  數(shù)據(jù)庫名 > 把數(shù)據(jù)庫放入到某個目錄

案例,備份 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  備份文件的全路徑

定時備份:(把命令寫入到my.bat 問中)

windows 如何定時備份 (每天凌晨2:00)

使用windows自帶的計劃任務(wù)，定時執(zhí)行批處理命令。

增量備份和還原

定義：mysql數(shù)據(jù)庫會以二進(jìn)制的形式，自動把用戶對mysql數(shù)據(jù)庫的操作，記錄到文件，當(dāng)用戶希望恢復(fù)的時候，可以使用備份文件進(jìn)行恢復(fù)。

增量備份會記錄dml語句、創(chuàng)建表的語句，不會記錄select。記錄的東西包括：sql語句本身、操作時間，位置

進(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ù)

這個時候會在mysqlbinlog目錄下面看到以下兩個文件：

mylog.000001：日志備份文件。如果要查看這個日志文件里面的信息，我們可以使用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日志中會記錄每一次操作的時間和位置，所以我們既可以根據(jù)時間來恢復(fù)，也可以根據(jù)位置來恢復(fù)。

那么，我們現(xiàn)在馬上可以從上圖看出，這條語句產(chǎn)生的時間是"2016-04-17 12:01:36"，位置是614

按時間來恢復(fù)

我們可以選擇在語句產(chǎn)生時間的前一秒

執(zhí)行cmd命令：mysqlbinlog --stop-datetime="2016-04-17 12:01:35" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p

這個時候我再執(zhí)行SQL語句查看

SELECT * from emp where empno=100003;

結(jié)果變成了

按位置來恢復(fù)

執(zhí)行cmd命令：mysqlbinlog --stop-position="614" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p

這個時候再執(zhí)行SQL來查看結(jié)果，又變回來了。

以上就是本文的全部內(nèi)容，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，同時也希望多多支持腳本之家！