插入記錄時(shí)，影響插入速度的主要是索引、唯一性校驗(yàn)、一次插入記錄條數(shù)等。根據(jù)這些情況，可以分別進(jìn)行優(yōu)化。下面由學(xué)習(xí)啦小編為大家整理的mysql優(yōu)化的方法，希望大家喜歡!

　　mysql優(yōu)化的方法

　　一. 對(duì)于MyISAM引擎表常見的優(yōu)化方法如下：

　　1. 禁用索引。對(duì)于非空表插入記錄時(shí)，MySQL會(huì)根據(jù)表的索引對(duì)插入記錄建立索引。如果插入大量數(shù)據(jù)，建立索引會(huì)降低插入記錄的速度。為了解決這種情況可以在插入記錄之前禁用索引，數(shù)據(jù)插入完畢后在開啟索引。禁用索引的語句為： ALTER TABLE tb_name DISABLE KEYS; 重新開啟索引的語句為： ALTER TABLE table_name ENABLE KEYS; 對(duì)于空表批量導(dǎo)入數(shù)據(jù)，則不需要進(jìn)行此操作，因?yàn)镸yISAM引擎的表是在導(dǎo)入數(shù)據(jù)之后才建立索引的。

　　2. 禁用唯一性檢查：數(shù)據(jù)插入時(shí)，MySQL會(huì)對(duì)插入的記錄進(jìn)行唯一性校驗(yàn)。這種唯一性校驗(yàn)也會(huì)降低插入記錄的速度。為了降低這種情況對(duì)查詢速度的影響，可以在插入記錄之前禁用唯一性檢查，等到記錄插入完畢之后再開啟。禁用唯一性檢查的語句為： SET UNIQUE_CHECKS=0; 開啟唯一性檢查的語句為： SET UNIQUE_CHECKS=1;

　　3. 使用批量插入。使用一條INSERT語句插入多條記錄。如 INSERT INTO table_name VALUES(....),(....),(....)

　　4. 使用LOAD DATA INFILE批量導(dǎo)入當(dāng)需要批量導(dǎo)入數(shù)據(jù)時(shí)，使用LOAD DATA INFILE語句導(dǎo)入數(shù)據(jù)的速度比INSERT語句快。

　　二. 對(duì)于InnoDB引擎的表，常見的優(yōu)化方法如下：

　　1. 禁用唯一性檢查。同MyISAM引擎相同，通過 SET UNIQUE_CHECKS=0; 導(dǎo)入數(shù)據(jù)之后將該值置1。

　　2. 禁用外鍵檢查。插入數(shù)據(jù)之前執(zhí)行禁止對(duì)外鍵的查詢，數(shù)據(jù)插入完成之后再恢復(fù)對(duì)外鍵的檢查。禁用外鍵檢查語句為： SET FOREIGN_KEY_CHECKS=0; 恢復(fù)對(duì)外鍵的檢查語句為： SET FOREIGN_KEY_CHECKS=1;

　　3. 禁止自動(dòng)提交。插入數(shù)據(jù)之前禁止事務(wù)的自動(dòng)提交，數(shù)據(jù)導(dǎo)入完成之后，執(zhí)行恢復(fù)自動(dòng)提交操作。禁止自動(dòng)提交語句為： SET AUTOCOMMIT=0; 恢復(fù)自動(dòng)提交只需將該值置1。

　　優(yōu)化Mysql數(shù)據(jù)庫性能的方法

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

　　如果一個(gè)表的記錄數(shù)太多了，比如上千萬條，而且需要經(jīng)常檢索，那么我們就有必要化整為零了。如果我拆成100個(gè)表，那么每個(gè)表只有10萬條記錄。當(dāng)然這需要數(shù)據(jù)在邏輯上可以劃分。一個(gè)好的劃分依據(jù)，有利于程序的簡(jiǎn)單實(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 1md5 char(32)id ：整數(shù) tinyint samllint int bigintstudent表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 = 64Minnodb_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è)目的

　　定時(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é)果，又變回來了。

　　如何MySQL主從同步原理

　　主從同步需求

　　要實(shí)現(xiàn) MySQL 的 Replication ，首先必須打開 Master 端的BinaryLog(mysql-bin.xxxxxx)功能，否則無法實(shí)現(xiàn)。因?yàn)檎麄€(gè)復(fù)制過程實(shí)際上就是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。打開 MySQL 的 Binary Log 可以通過在啟動(dòng) MySQL Server 的過程中使用“—log-bin” 參數(shù)選項(xiàng)，或者在 my.cnf 配置文件中的 mysqld 參數(shù)組([mysqld]標(biāo)識(shí)后的參數(shù)部分)增加“log-bin” 參數(shù)項(xiàng)。

　　主從同步過程

　　MySQL 復(fù)制的基本過程如下：

　　1.Slave上面的IO線程連接上Master，并請(qǐng)求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;

　　2.Master接收到來自Slave的IO線程的請(qǐng)求后，通過負(fù)責(zé)復(fù)制的IO線程根據(jù)請(qǐng)求信息讀取指定日志指定位置之后的日志信息，返回給Slave端的 IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在Master端的Binary Log文件的名稱以及在Binary Log中的位置;

　　3.Slave的IO線程接收到信息后，將接收到的日志內(nèi)容依次寫入到 Slave 端的RelayLog文件(mysql-relay-bin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的告訴Master“我需要從某個(gè)bin-log的哪個(gè)位置開始往后的日志內(nèi)容，請(qǐng)發(fā)給我”。

　　4.Slave的SQL線程檢測(cè)到Relay Log中新增加了內(nèi)容后，會(huì)馬上解析該Log文件中的內(nèi)容成為在Master 端真實(shí)執(zhí)行時(shí)候的那些可執(zhí)行的Query語句，并在自身執(zhí)行這些Query。這樣，實(shí)際上就是在Master端和Slave端執(zhí)行了同樣的Query，所以兩端的數(shù)據(jù)是完全一樣的。

　　實(shí)際上，在老版本中，MySQL 的復(fù)制實(shí)現(xiàn)在 Slave 端并不是由 SQL 線程和 IO線程這兩個(gè)線程共同協(xié)作而完成的，而是由單獨(dú)的一個(gè)線程來完成所有的工作。但是 MySQL的工程師們很快發(fā)現(xiàn)，這樣做存在很大的風(fēng)險(xiǎn)和性能問題，主要如下：

　　1.首先，如果通過一個(gè)單一的線程來獨(dú)立實(shí)現(xiàn)這個(gè)工作的話，就使復(fù)制 Master 端的，BinaryLog日志，以及解析這些日志，然后再在自身執(zhí)行的這個(gè)過程成為一個(gè)串行的過程，性能自然會(huì)受到較大的限制，這種架構(gòu)下的Replication 的延遲自然就比較長了。

　　3.其次，Slave 端的這個(gè)復(fù)制線程從 Master 端獲取 Binary Log 過來之后，需要接著解析這些內(nèi)容，還原成Master 端所執(zhí)行的原始 Query，然后在自身執(zhí)行。在這個(gè)過程中，Master端很可能又已經(jīng)產(chǎn)生了大量的變化并生成了大量的Binary Log 信息。如果在這個(gè)階段 Master端的存儲(chǔ)系統(tǒng)出現(xiàn)了無法修復(fù)的故障，那么在這個(gè)階段所產(chǎn)生的所有變更都將永遠(yuǎn)的丟失，無法再找回來。這種潛在風(fēng)險(xiǎn)在Slave端壓力比較大的時(shí)候尤其突出，因?yàn)槿绻?Slave壓力比較大，解析日志以及應(yīng)用這些日志所花費(fèi)的時(shí)間自然就會(huì)更長一些，可能丟失的數(shù)據(jù)也就會(huì)更多。

　　所以，在后期的改造中，新版本的 MySQL 為了盡量減小這個(gè)風(fēng)險(xiǎn)，并提高復(fù)制的性能，將 Slave端的復(fù)制改為兩個(gè)線程來完成，也就是前面所提到的 SQL 線程和 IO線程。最早提出這個(gè)改進(jìn)方案的是Yahoo!的一位工程師“JeremyZawodny”。通過這樣的改造，這樣既在很大程度上解決了性能問題，縮短了異步的延時(shí)時(shí)間，同時(shí)也減少了潛在的數(shù)據(jù)丟失量。

　　當(dāng)然，即使是換成了現(xiàn)在這樣兩個(gè)線程來協(xié)作處理之后，同樣也還是存在 Slave數(shù)據(jù)延時(shí)以及數(shù)據(jù)丟失的可能性的，畢竟這個(gè)復(fù)制是異步的。只要數(shù)據(jù)的更改不是在一個(gè)事務(wù)中，這些問題都是存在的。