快捷導(dǎo)航

MySQL 數(shù)據(jù)庫(kù)優(yōu)化的具體方法說(shuō)明

更新時(shí)間：2010年05月20日 01:37:53 作者：

以下的文章主要講述的是實(shí)現(xiàn)MySQL數(shù)據(jù)庫(kù)簡(jiǎn)單實(shí)用優(yōu)化的具體方法，以及在實(shí)際操作中有哪些具體操作步驟是值得我們大家注意的。

以下的文章主要描述的是MySQL數(shù)據(jù)庫(kù)簡(jiǎn)單實(shí)用優(yōu)化的具體方法的實(shí)現(xiàn)，中包括如何定期的表進(jìn)行分析與檢查，以及如何正確對(duì)表進(jìn)行定期的優(yōu)化，以下就是具體方案的描述，希望在你今后的學(xué)習(xí)中會(huì)有所幫助。

1、定期分析表和檢查表

分析表的語(yǔ)法如下：

復(fù)制代碼代碼如下:

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

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

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

復(fù)制代碼代碼如下:

analyze table table_name

檢查表的語(yǔ)法如下：

復(fù)制代碼代碼如下:

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

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

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

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

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

復(fù)制代碼代碼如下:

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

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

例如： optimize table table_name

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

常用的SQL優(yōu)化

我們?cè)陂_(kāi)發(fā)的時(shí)候常常用到的SQL語(yǔ)句，無(wú)非是INSERT、GROUPBY等等。對(duì)于這些SQL語(yǔ)句，我們?cè)趺催M(jìn)行優(yōu)化？

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

當(dāng)用load命令導(dǎo)入數(shù)據(jù)的時(shí)候，適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度。
對(duì)于MyISAM存儲(chǔ)引擎的表，可以通過(guò)如下方式快速的導(dǎo)入大量的數(shù)據(jù)

復(fù)制代碼代碼如下:

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

DISABLE KEYS 和 ENABLE KEYS 用來(lái)打開(kāi)或者關(guān)閉MyISAM表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個(gè)非空的MyISAM表時(shí)，通過(guò)設(shè)置這兩個(gè)命令，可以提高導(dǎo)入的效率。
對(duì)于導(dǎo)入大量的數(shù)據(jù)到一個(gè)空的MyISAM表時(shí)，默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的，索引不用進(jìn)行設(shè)置。

復(fù)制代碼代碼如下:

load data infile '/home/mysql/text_txt' into table text

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

1. 因?yàn)镮nnoDB類(lèi)型的表式按照主鍵的順序保存的，所以將導(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，打開(kāi)自動(dòng)提交，也可以提高導(dǎo)入效率。

優(yōu)化INSERT語(yǔ)句

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

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

例如：

復(fù)制代碼代碼如下:

 
insert 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. 如果從不同客戶(hù)插入很多行，能通過(guò)使用INSERT DELAYED 語(yǔ)句得到更高的速度。

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

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

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

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

下面是mysql 性能優(yōu)化的一些其它需要注意的地方，大家結(jié)合和，充分發(fā)揮mysql的性能。
mysql數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化
MySQL優(yōu)化全攻略-相關(guān)數(shù)據(jù)庫(kù)命令
mysql 數(shù)據(jù)庫(kù)中my.ini的優(yōu)化 2G內(nèi)存針對(duì)站多抗壓型的設(shè)置

您可能感興趣的文章: