mysql性能優(yōu)化指南

本文的主要目標(biāo)是介紹如何優(yōu)化mysql數(shù)據(jù)庫,通過定期分析表和檢查表、定期優(yōu)化表以及一些常用sql語句的優(yōu)化手段,可以幫助你學(xué)會(huì)在mysql開發(fā)中編寫更為高效的sql。

1、定期分析表和檢查表

分析表的語法如下:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]…

? ?

以上語句用于分析和存儲(chǔ)表的關(guān)鍵字分布,分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計(jì)信息,使得SQL能夠生成正確的執(zhí)行計(jì)劃。如果用戶感覺實(shí)際執(zhí)行計(jì)劃并不是預(yù)期的執(zhí)行計(jì)劃,執(zhí)行一次分析表可能會(huì)解決問題。在分析期間,使用一個(gè)讀取鎖定對表進(jìn)行鎖定。這對于MyISAM,DBD和InnoDB表有作用。

例如分析一個(gè)數(shù)據(jù)表

analyze table table_name

? ?

檢查表的語法如下:

CHECK TABLE tb1_name[,tbl_name]…[option]…option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

? ?

檢查表的作用是檢查一個(gè)或多個(gè)表是否有錯(cuò)誤,CHECK TABLE 對MyISAM 和 InnoDB表有作用,對于MyISAM表,關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)被更新

CHECK TABLE 也可以檢查視圖是否有錯(cuò)誤,比如在視圖定義中被引用的表不存在。

2. 定期優(yōu)化表

優(yōu)化表的語法如下:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]…

? ?

如果刪除了表的一大部分,或者如果已經(jīng)對含有可變長度行的表(含有 VARCHAR、BLOB或TEXT列的表)進(jìn)行更多更改,則應(yīng)使用OPTIMIZE TABLE命令來進(jìn)行表優(yōu)化。這個(gè)命令可以將表中的空間碎片進(jìn)行合并,并且可以消除由于刪除或者更新造成的空間浪費(fèi),但OPTIMIZE TABLE 命令只對MyISAM、 BDB 和InnoDB表起作用。

例如: optimize table table_name

注意: analyze、check、optimize執(zhí)行期間將對表進(jìn)行鎖定,因此一定注意要在MySQL數(shù)據(jù)庫不繁忙的時(shí)候執(zhí)行相關(guān)的操作。

常用的SQL優(yōu)化

我們在開發(fā)的時(shí)候常常用到的SQL語句,無非是INSERT、GROUPBY等等。對于這些SQL語句,我們怎么進(jìn)行優(yōu)化?

1. 大批量插入數(shù)據(jù)

當(dāng)用load命令導(dǎo)入數(shù)據(jù)的時(shí)候,適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度。

對于MyISAM存儲(chǔ)引擎的表,可以通過如下方式快速的導(dǎo)入大量的數(shù)據(jù)

代碼如下:

ALTER?TABLE?tb1_name?DISABLE?KEYS;  loading?the?data  ALTER?TABLE?tb1_name?ENABLE?KEYS;

? ??DISABLE KEYS 和 ENABLE KEYS 用來打開或者關(guān)閉MyISAM表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個(gè)非空的MyISAM表時(shí),通過設(shè)置這兩個(gè)命令,可以提高導(dǎo)入的效率。

對于導(dǎo)入大量的數(shù)據(jù)到一個(gè)空的MyISAM表時(shí),默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的,索引不用進(jìn)行設(shè)置。

load data infile ‘/home/mysql/text_txt’ into table text

? ?

對于InnoDB類型的表,這種方式不能提高導(dǎo)入數(shù)據(jù)的效率,但也有幾種針對InnoDB類型的表進(jìn)行優(yōu)化的方式。

1. 因?yàn)镮nnoDB類型的表式按照主鍵的順序保存的,所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排序,可以有效提高導(dǎo)入數(shù)據(jù)的效率。

2. 在導(dǎo)入數(shù)據(jù)前執(zhí)行 SET UNIQUE_CHECKS=0,關(guān)閉唯一性校驗(yàn),在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1,恢復(fù)唯一性校驗(yàn),可以提高導(dǎo)入的效率。

3. 如果應(yīng)用使用自動(dòng)提交的方式,建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0,關(guān)閉自動(dòng)提交,導(dǎo)入結(jié)束后執(zhí)行SET AUTOCOMMIT=1,打開自動(dòng)提交,也可以提高導(dǎo)入效率。

優(yōu)化INSERT語句

當(dāng)進(jìn)行數(shù)據(jù)INSERT的時(shí)候,可以考慮采用以下幾種方式進(jìn)行優(yōu)化

1. 如果同時(shí)從一個(gè)客戶插入很多行,盡量使用多個(gè)值表的INSERT語句,這種方式將大大縮短客戶端與MySQL數(shù)據(jù)庫的鏈接、關(guān)閉等消耗,使得效率比分開執(zhí)行的單個(gè)INSERT語句快.

例如:

i

nsert?into?test?values(1,2)  insert?into?test?values(3,4)  insert?into?test?values(5,6)

? ??將上面三句改為:insert into test values(1,2),(3,4),(5,6)……

2. 如果從不同客戶插入很多行,能通過使用INSERT DELAYED 語句得到更高的速度。

DELAYED 的含義是讓INSERT 語句馬上執(zhí)行,其實(shí)數(shù)據(jù)都被放在內(nèi)存的隊(duì)列中,并沒有真正寫入磁盤,這比每條語句分別插入要快得多;LOW_PRIORITY剛好相反,在所有其他用戶對表的讀寫完后才進(jìn)行插入。

3. 將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放

4. 如果進(jìn)行批量插入,可以增加bulk_insert_buffer_size變量值的方法來提高速度,但是,這只能對于MyISAM表使用。

5. 當(dāng)從一個(gè)文本文件中裝載一個(gè)表時(shí),使用LOAD DATA INFILE。 這通常比使用很多insert語句快20倍左右。

以上是MySQL 性能優(yōu)化的一些其它需要注意的地方,大家結(jié)合和,充分發(fā)揮mysql的性能。

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊10 分享