程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> Mysql數據庫性能優化三(分表、增量備份、還原),mysql性能優化

Mysql數據庫性能優化三(分表、增量備份、還原),mysql性能優化

編輯:更多數據庫知識

Mysql數據庫性能優化三(分表、增量備份、還原),mysql性能優化


接上篇Mysql數據庫性能優化二

對表進行水平劃分          

如果一個表的記錄數太多了,比如上千萬條,而且需要經常檢索,那麼我們就有必要化整為零了。如果我拆成100個表,那麼每個表只有10萬條記錄。當然這需要數據在邏輯上可以劃分。一個好的劃分依據,有利於程序的簡單實現,也可以充分利用水平分表的優勢。比如系統界面上只提供按月查詢的功能,那麼把表按月拆分成12個,每個查詢只查詢一個表就夠了。如果非要按照地域來分,即使把表拆的再小,查詢還是要聯合所有表來查,還不如不拆了。所以一個好的拆分依據是 最重要的。關鍵字:UNION

例:

  • 訂單表根據訂單產生時間來分表(一年一張)
  • 學生情況表
  • 查詢電話費,近三個月的數據放入一張表,一年內的放入到另一張表

對表進行垂直劃分 

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

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

選擇適當的字段類型,特別是主鍵     

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

建議使用一個不含業務邏輯的id做主角如s1001。例:

int 4 bigint 8 mediumint smallint 2 tinyint 1
md5 char(32)
id :整數 tinyint samllint int bigint
student表
id stuno  stuname  adress
 s1001 小民   深圳

文件、圖片等大文件用文件系統存儲

數據庫只存儲路徑。圖片和文件存放在文件系統,甚至單獨放在一台服務器(圖床 / 視頻服務器 ).

數據庫參數配置

最重要的參數就是內存,我們主要用的innodb引擎,所以下面兩個參數調的很大

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

對於myisam,需要調整key_buffer_size,當然調整參數還是要看狀態,用show status語句可以看到當前狀態,以決定改調整哪些參數

在my.ini修改端口3306,默認存儲引擎和最大連接數

在my.ini中.
port=3306 [有兩個地方修改]
default-storage-engine=INNODB 
max_connections=100

合理的硬件資源和操作系統

如果你的機器內存超過4G,那麼毋庸置疑應當采用64位操作系統和64位mysql 5.5.19 or mysql5.6

讀寫分離

    如果數據庫壓力很大,一台機器支撐不了,那麼可以用mysql復制實現多台機器同步,將數據庫的壓力分散。

    Master
  Slave1
  Slave2
  Slave3

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

定時完成數據庫的備份

項目實際需求,請完成定時備份某個數據庫,或者定時備份數據庫的某些表的操作

windows 下每隔1小時,備份一次數據newsdb

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

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

案例,備份 mydb 庫的所有表

進入mysqldump所在的目錄

cmd> mysqldump –u root –phsp shop> d:/shop.log   [把shop數據庫的所有表全部導出]

cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop數據庫的 temusers和emp導出]

如何恢復數據的表

進入的mysql操作界面

mysql>source  備份文件的全路徑

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

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

使用windows自帶的計劃任務,定時執行批處理命令。

增量備份和還原

定義:mysql數據庫會以二進制的形式,自動把用戶對mysql數據庫的操作,記錄到文件,當用戶希望恢復的時候,可以使用備份文件進行恢復。

增量備份會記錄dml語句、創建表的語句,不會記錄select。記錄的東西包括:sql語句本身、操作時間,位置

進行增量備份的步驟和恢復

注意:mysql5.0及之前的版本是不支持增量備份的

1、配置my.ini文件或者my.conf,啟用二進制備份。

打開my.ini文件,查找log-bin,進行配置:log-bin=G:\Database\mysqlbinlog\mylog

在G:\Database目錄下面新建目錄mysqlbinlog

2、重啟mysql服務

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

mylog.000001:日志備份文件。如果要查看這個日志文件裡面的信息,我們可以使用mysqlbinlog程序查看,mysqlbinlog程序存放在mysql的bin目錄下面(“C:\Program Files\MySQL\MySQL Server 5.6\bin”)。

執行sql語句

UPDATE emp set ename='zouqj' where empno=100003;

開始——運行——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、假設現在問題來了,我這條update是誤操作,如何進行恢復

在mysql日志中會記錄每一次操作的時間和位置,所以我們既可以根據時間來恢復,也可以根據位置來恢復。

那麼,我們現在馬上可以從上圖看出,這條語句產生的時間是"2016-04-17 12:01:36",位置是614

按時間來恢復

我們可以選擇在語句產生時間的前一秒

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

這個時候我再執行SQL語句查看

SELECT * from emp where empno=100003;

結果變成了

按位置來恢復

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

這個時候再執行SQL來查看結果,又變回來了。

以上就是本文的全部內容,希望本文的內容對大家的學習或者工作能帶來一定的幫助,同時也希望多多支持幫客之家!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved