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

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

例：

訂單表根據(jù)訂單產(chǎn)生時(shí)間來(lái)分表（一年一張）

學(xué)生情況表

查詢電話費(fèi)宵呛，近三個(gè)月的數(shù)據(jù)放入一張表单匣，一年內(nèi)的放入到另一張表

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

有些表記錄數(shù)并不多，可能也就2宝穗、3萬(wàn)條户秤，但是字段卻很長(zhǎng)，表占用空間很大逮矛，檢索表時(shí)需要執(zhí)行大量I/O鸡号，嚴(yán)重降低了性能。這個(gè)時(shí)候需要把大的字段拆分到另一個(gè)表须鼎，并且該表與原表是一對(duì)一的關(guān)系鲸伴。 (JOIN)

【試題內(nèi)容】、【答案信息】?jī)蓚€(gè)表莉兰，最初是作為幾個(gè)字段添加到【試題信息】里的挑围，可以看到試題內(nèi)容和答案這兩個(gè)字段很長(zhǎng)，在表里有3萬(wàn)記錄時(shí)糖荒，表已經(jīng)占 了1G的空間，在列試題列表時(shí)非常慢模捂。經(jīng)過(guò)分析捶朵，發(fā)現(xiàn)系統(tǒng)很多時(shí)候是根據(jù)【冊(cè)】、【單元】狂男、類型综看、類別、難易程度等查詢條件岖食，分頁(yè)顯示試題詳細(xì)內(nèi)容红碑。而每 次檢索都是這幾個(gè)表做join，每次要掃描一遍1G的表。我們完全可以把內(nèi)容和答案拆分成另一個(gè)表析珊，只有顯示詳細(xì)內(nèi)容的時(shí)候才讀這個(gè)大表羡鸥，由此 就產(chǎn)生了【試題內(nèi)容】、【答案信息】?jī)蓚€(gè)表忠寻。

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

選擇字段的一般原則是保小不保大，能用占用字節(jié)小的字段就不用大字段奕剃。比如主鍵衷旅， 建議使用自增類型，這樣省空間,空間就是效率!按4個(gè)字節(jié)和按32個(gè)字節(jié)定位一條記錄纵朋，誰(shuí)快誰(shuí)慢太明顯了柿顶。涉及到幾個(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 1? s1001? ? 小民? ? ? ? ? ? 深圳

文件、圖片等大文件用文件系統(tǒng)存儲(chǔ)

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

數(shù)據(jù)庫(kù)參數(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語(yǔ)句可以看到當(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)存超過(guò)4G凉驻，那么毋庸置疑應(yīng)當(dāng)采用64位操作系統(tǒng)和64位mysql 5.5.19 or mysql5.6

讀寫分離

如果數(shù)據(jù)庫(kù)壓力很大腻要，一臺(tái)機(jī)器支撐不了，那么可以用mysql復(fù)制實(shí)現(xiàn)多臺(tái)機(jī)器同步涝登，將數(shù)據(jù)庫(kù)的壓力分散雄家。

Master

Slave1

Slave2

Slave3

主庫(kù)master用來(lái)寫入，slave1—slave3都用來(lái)做select胀滚，每個(gè)數(shù)據(jù)庫(kù)分擔(dān)的壓力小了很多趟济。

要實(shí)現(xiàn)這種方式，需要程序特別設(shè)計(jì)咽笼，寫都操作master顷编，讀都操作slave，給程序開發(fā)帶來(lái)了額外負(fù)擔(dān)剑刑。當(dāng)然目前已經(jīng)有中間件來(lái)實(shí)現(xiàn)這個(gè)代理媳纬，對(duì)程 序來(lái)讀寫哪些數(shù)據(jù)庫(kù)是透明的。官方有個(gè)mysql-proxy，但是還是alpha版本的钮惠。新浪有個(gè)amobe for mysql茅糜，也可達(dá)到這個(gè)目的，結(jié)構(gòu)如下

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

項(xiàng)目實(shí)際需求萌腿，請(qǐng)完成定時(shí)備份某個(gè)數(shù)據(jù)庫(kù)限匣，或者定時(shí)備份數(shù)據(jù)庫(kù)的某些表的操作

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

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

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

案例,備份 mydb 庫(kù)的所有表

進(jìn)入mysqldump所在的目錄

cmd> mysqldump –u root –phsp shop> d:/shop.log [把shop數(shù)據(jù)庫(kù)的所有表全部導(dǎo)出]

cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop數(shù)據(jù)庫(kù)的 temusers和emp導(dǎo)出]

如何恢復(fù)數(shù)據(jù)的表

進(jìn)入的mysql操作界面

mysql>source 備份文件的全路徑

定時(shí)備份:(把命令寫入到my.bat 問(wèn)中)

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

使用windows自帶的計(jì)劃任務(wù)毁菱，定時(shí)執(zhí)行批處理命令米死。

增量備份和還原

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

增量備份會(huì)記錄dml語(yǔ)句物喷、創(chuàng)建表的語(yǔ)句，不會(huì)記錄select遮斥。記錄的東西包括：sql語(yǔ)句本身峦失、操作時(shí)間，位置

進(jìn)行增量備份的步驟和恢復(fù)

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

1术吗、配置my.ini文件或者my.conf尉辑，啟用二進(jìn)制備份。

打開my.ini文件较屿，查找log-bin隧魄，進(jìn)行配置：log-bin=G:Databasemysqlbinlogmylog

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

2、重啟mysql服務(wù)

這個(gè)時(shí)候會(huì)在mysqlbinlog目錄下面看到以下兩個(gè)文件：

mylog.000001：日志備份文件隘蝎。如果要查看這個(gè)日志文件里面的信息购啄，我們可以使用mysqlbinlog程序查看，mysqlbinlog程序存放在mysql的bin目錄下面（“C:Program FilesMySQLMySQL Server 5.6bin”）嘱么。

執(zhí)行sql語(yǔ)句

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

開始——運(yùn)行——cmd狮含，mysqlbinlog 備份文件路徑

C:Program FilesMySQLMySQL Server 5.6bin>mysqlbinlog G:Databasemysqlbinlogmylog.000001

mylog.index：日志索引文件，里面記錄了所以的日志文件拱撵。（G:Databasemysqlbinlogmylog.000001）

3辉川、假設(shè)現(xiàn)在問(wèn)題來(lái)了，我這條update是誤操作拴测，如何進(jìn)行恢復(fù)

在mysql日志中會(huì)記錄每一次操作的時(shí)間和位置，所以我們既可以根據(jù)時(shí)間來(lái)恢復(fù)府蛇，也可以根據(jù)位置來(lái)恢復(fù)集索。

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

按時(shí)間來(lái)恢復(fù)

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

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

這個(gè)時(shí)候我再執(zhí)行SQL語(yǔ)句查看

SELECT * from emp where empno=100003;

結(jié)果變成了

按位置來(lái)恢復(fù)

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

這個(gè)時(shí)候再執(zhí)行SQL來(lái)查看結(jié)果妆距，又變回來(lái)了。