mysql怎么進行優(yōu)化_mysql優(yōu)化什么方法
插入記錄時,影響插入速度的主要是索引、唯一性校驗、一次插入記錄條數(shù)等。根據(jù)這些情況,可以分別進行優(yōu)化。下面由學習啦小編為大家整理的mysql優(yōu)化的方法,希望大家喜歡!
mysql優(yōu)化的方法
一. 對于MyISAM引擎表常見的優(yōu)化方法如下:
1. 禁用索引。對于非空表插入記錄時,MySQL會根據(jù)表的索引對插入記錄建立索引。如果插入大量數(shù)據(jù),建立索引會降低插入記錄的速度。為了解決這種情況可以在插入記錄之前禁用索引,數(shù)據(jù)插入完畢后在開啟索引。禁用索引的語句為: ALTER TABLE tb_name DISABLE KEYS; 重新開啟索引的語句為: ALTER TABLE table_name ENABLE KEYS; 對于空表批量導入數(shù)據(jù),則不需要進行此操作,因為MyISAM引擎的表是在導入數(shù)據(jù)之后才建立索引的。
2. 禁用唯一性檢查:數(shù)據(jù)插入時,MySQL會對插入的記錄進行唯一性校驗。這種唯一性校驗也會降低插入記錄的速度。為了降低這種情況對查詢速度的影響,可以在插入記錄之前禁用唯一性檢查,等到記錄插入完畢之后再開啟。禁用唯一性檢查的語句為: SET UNIQUE_CHECKS=0; 開啟唯一性檢查的語句為: SET UNIQUE_CHECKS=1;
3. 使用批量插入。使用一條INSERT語句插入多條記錄。如 INSERT INTO table_name VALUES(....),(....),(....)
4. 使用LOAD DATA INFILE批量導入當需要批量導入數(shù)據(jù)時,使用LOAD DATA INFILE語句導入數(shù)據(jù)的速度比INSERT語句快。
二. 對于InnoDB引擎的表,常見的優(yōu)化方法如下:
1. 禁用唯一性檢查。同MyISAM引擎相同,通過 SET UNIQUE_CHECKS=0; 導入數(shù)據(jù)之后將該值置1。
2. 禁用外鍵檢查。插入數(shù)據(jù)之前執(zhí)行禁止對外鍵的查詢,數(shù)據(jù)插入完成之后再恢復對外鍵的檢查。禁用外鍵檢查語句為: SET FOREIGN_KEY_CHECKS=0; 恢復對外鍵的檢查語句為: SET FOREIGN_KEY_CHECKS=1;
3. 禁止自動提交。插入數(shù)據(jù)之前禁止事務(wù)的自動提交,數(shù)據(jù)導入完成之后,執(zhí)行恢復自動提交操作。禁止自動提交語句為: SET AUTOCOMMIT=0; 恢復自動提交只需將該值置1。
優(yōu)化Mysql數(shù)據(jù)庫性能的方法
對表進行水平劃分
如果一個表的記錄數(shù)太多了,比如上千萬條,而且需要經(jīng)常檢索,那么我們就有必要化整為零了。如果我拆成100個表,那么每個表只有10萬條記錄。當然這需要數(shù)據(jù)在邏輯上可以劃分。一個好的劃分依據(jù),有利于程序的簡單實現(xiàn),也可以充分利用水平分表的優(yōu)勢。比如系統(tǒng)界面上只提供按月查詢的功能,那么把表按月拆分成12個,每個查詢只查詢一個表就夠了。如果非要按照地域來分,即使把表拆的再小,查詢還是要聯(lián)合所有表來查,還不如不拆了。所以一個好的拆分依據(jù)是 最重要的。關(guān)鍵字:UNION
例:
訂單表根據(jù)訂單產(chǎn)生時間來分表(一年一張)
學生情況表
查詢電話費,近三個月的數(shù)據(jù)放入一張表,一年內(nèi)的放入到另一張表
對表進行垂直劃分
有些表記錄數(shù)并不多,可能也就2、3萬條,但是字段卻很長,表占用空間很大,檢索表時需要執(zhí)行大量I/O,嚴重降低了性能。這個時候需要把大的字段拆分到另一個表,并且該表與原表是一對一的關(guān)系。 (JOIN)
【試題內(nèi)容】、【答案信息】兩個表,最初是作為幾個字段添加到【試題信息】里的,可以看到試題內(nèi)容和答案這兩個字段很長,在表里有3萬記錄時,表已經(jīng)占 了1G的空間,在列試題列表時非常慢。經(jīng)過分析,發(fā)現(xiàn)系統(tǒng)很多時候是根據(jù)【冊】、【單元】、類型、類別、難易程度等查詢條件,分頁顯示試題詳細內(nèi)容。而每 次檢索都是這幾個表做join,每次要掃描一遍1G的表。我們完全可以把內(nèi)容和答案拆分成另一個表,只有顯示詳細內(nèi)容的時候才讀這個大表,由此 就產(chǎn)生了【試題內(nèi)容】、【答案信息】兩個表。
選擇適當?shù)淖侄晤愋?,特別是主鍵
選擇字段的一般原則是保小不保大,能用占用字節(jié)小的字段就不用大字段。比如主鍵, 建議使用自增類型,這樣省空間,空間就是效率!按4個字節(jié)和按32個字節(jié)定位一條記錄,誰快誰慢太明顯了。涉及到幾個表做join時,效果就更明顯了。
建議使用一個不含業(yè)務(wù)邏輯的id做主角如s1001。例:
int 4 bigint 8 mediumint smallint 2 tinyint 1md5 char(32)id :整數(shù) tinyint samllint int bigintstudent表id stuno stuname adress s1001 小民 深圳
文件、圖片等大文件用文件系統(tǒng)存儲
數(shù)據(jù)庫只存儲路徑。圖片和文件存放在文件系統(tǒng),甚至單獨放在一臺服務(wù)器(圖床 / 視頻服務(wù)器 ).
數(shù)據(jù)庫參數(shù)配置
最重要的參數(shù)就是內(nèi)存,我們主要用的innodb引擎,所以下面兩個參數(shù)調(diào)的很大
innodb_additional_mem_pool_size = 64Minnodb_buffer_pool_size =1G
對于myisam,需要調(diào)整key_buffer_size,當然調(diào)整參數(shù)還是要看狀態(tài),用show status語句可以看到當前狀態(tài),以決定改調(diào)整哪些參數(shù)
在my.ini修改端口3306,默認存儲引擎和最大連接數(shù)
在my.ini中.port=3306 [有兩個地方修改]default-storage-engine=INNODB max_connections=100
合理的硬件資源和操作系統(tǒng)
如果你的機器內(nèi)存超過4G,那么毋庸置疑應(yīng)當采用64位操作系統(tǒng)和64位mysql 5.5.19 or mysql5.6
讀寫分離
如果數(shù)據(jù)庫壓力很大,一臺機器支撐不了,那么可以用mysql復制實現(xiàn)多臺機器同步,將數(shù)據(jù)庫的壓力分散。
Master
Slave1
Slave2
Slave3
主庫master用來寫入,slave1—slave3都用來做select,每個數(shù)據(jù)庫分擔的壓力小了很多。
要實現(xiàn)這種方式,需要程序特別設(shè)計,寫都操作master,讀都操作slave,給程序開發(fā)帶來了額外負擔。當然目前已經(jīng)有中間件來實現(xiàn)這個代理,對程 序來讀寫哪些數(shù)據(jù)庫是透明的。官方有個mysql-proxy,但是還是alpha版本的。新浪有個amobe for mysql,也可達到這個目的
定時完成數(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 庫的所有表
進入mysqldump所在的目錄
cmd> mysqldump –u root –phsp shop> d:/shop.log [把shop數(shù)據(jù)庫的所有表全部導出]
cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop數(shù)據(jù)庫的 temusers和emp導出]
如何恢復數(shù)據(jù)的表
進入的mysql操作界面
mysql>source 備份文件的全路徑
定時備份:(把命令寫入到my.bat 問中)
windows 如何定時備份 (每天凌晨2:00)
使用windows自帶的計劃任務(wù),定時執(zhí)行批處理命令。
增量備份和還原
定義:mysql數(shù)據(jù)庫會以二進制的形式,自動把用戶對mysql數(shù)據(jù)庫的操作,記錄到文件,當用戶希望恢復的時候,可以使用備份文件進行恢復。
增量備份會記錄dml語句、創(chuàng)建表的語句,不會記錄select。記錄的東西包括:sql語句本身、操作時間,位置
進行增量備份的步驟和恢復
注意:mysql5.0及之前的版本是不支持增量備份的
1、配置my.ini文件或者my.conf,啟用二進制備份。
打開my.ini文件,查找log-bin,進行配置: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;開始——運行——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是誤操作,如何進行恢復
在mysql日志中會記錄每一次操作的時間和位置,所以我們既可以根據(jù)時間來恢復,也可以根據(jù)位置來恢復。
那么,我們現(xiàn)在馬上可以從上圖看出,這條語句產(chǎn)生的時間是"2016-04-17 12:01:36",位置是614
按時間來恢復
我們可以選擇在語句產(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é)果變成了按位置來恢復
執(zhí)行cmd命令:mysqlbinlog --stop-position="614" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p
這個時候再執(zhí)行SQL來查看結(jié)果,又變回來了。
如何MySQL主從同步原理
主從同步需求
要實現(xiàn) MySQL 的 Replication ,首先必須打開 Master 端的BinaryLog(mysql-bin.xxxxxx)功能,否則無法實現(xiàn)。因為整個復制過程實際上就是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。打開 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用“—log-bin” 參數(shù)選項,或者在 my.cnf 配置文件中的 mysqld 參數(shù)組([mysqld]標識后的參數(shù)部分)增加“log-bin” 參數(shù)項。
主從同步過程
MySQL 復制的基本過程如下:
1.Slave上面的IO線程連接上Master,并請求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;
2.Master接收到來自Slave的IO線程的請求后,通過負責復制的IO線程根據(jù)請求信息讀取指定日志指定位置之后的日志信息,返回給Slave端的 IO線程。返回信息中除了日志所包含的信息之外,還包括本次返回的信息在Master端的Binary Log文件的名稱以及在Binary Log中的位置;
3.Slave的IO線程接收到信息后,將接收到的日志內(nèi)容依次寫入到 Slave 端的RelayLog文件(mysql-relay-bin.xxxxxx)的最末端,并將讀取到的Master端的bin-log的文件名和位置記錄到master-info文件中,以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往后的日志內(nèi)容,請發(fā)給我”。
4.Slave的SQL線程檢測到Relay Log中新增加了內(nèi)容后,會馬上解析該Log文件中的內(nèi)容成為在Master 端真實執(zhí)行時候的那些可執(zhí)行的Query語句,并在自身執(zhí)行這些Query。這樣,實際上就是在Master端和Slave端執(zhí)行了同樣的Query,所以兩端的數(shù)據(jù)是完全一樣的。
實際上,在老版本中,MySQL 的復制實現(xiàn)在 Slave 端并不是由 SQL 線程和 IO線程這兩個線程共同協(xié)作而完成的,而是由單獨的一個線程來完成所有的工作。但是 MySQL的工程師們很快發(fā)現(xiàn),這樣做存在很大的風險和性能問題,主要如下:
1.首先,如果通過一個單一的線程來獨立實現(xiàn)這個工作的話,就使復制 Master 端的,BinaryLog日志,以及解析這些日志,然后再在自身執(zhí)行的這個過程成為一個串行的過程,性能自然會受到較大的限制,這種架構(gòu)下的Replication 的延遲自然就比較長了。
3.其次,Slave 端的這個復制線程從 Master 端獲取 Binary Log 過來之后,需要接著解析這些內(nèi)容,還原成Master 端所執(zhí)行的原始 Query,然后在自身執(zhí)行。在這個過程中,Master端很可能又已經(jīng)產(chǎn)生了大量的變化并生成了大量的Binary Log 信息。如果在這個階段 Master端的存儲系統(tǒng)出現(xiàn)了無法修復的故障,那么在這個階段所產(chǎn)生的所有變更都將永遠的丟失,無法再找回來。這種潛在風險在Slave端壓力比較大的時候尤其突出,因為如果 Slave壓力比較大,解析日志以及應(yīng)用這些日志所花費的時間自然就會更長一些,可能丟失的數(shù)據(jù)也就會更多。
所以,在后期的改造中,新版本的 MySQL 為了盡量減小這個風險,并提高復制的性能,將 Slave端的復制改為兩個線程來完成,也就是前面所提到的 SQL 線程和 IO線程。最早提出這個改進方案的是Yahoo!的一位工程師“JeremyZawodny”。通過這樣的改造,這樣既在很大程度上解決了性能問題,縮短了異步的延時時間,同時也減少了潛在的數(shù)據(jù)丟失量。
當然,即使是換成了現(xiàn)在這樣兩個線程來協(xié)作處理之后,同樣也還是存在 Slave數(shù)據(jù)延時以及數(shù)據(jù)丟失的可能性的,畢竟這個復制是異步的。只要數(shù)據(jù)的更改不是在一個事務(wù)中,這些問題都是存在的。