MySQL數(shù)據(jù)庫(kù)是一種小型關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，MySQL數(shù)據(jù)庫(kù)的優(yōu)化是MySQL數(shù)據(jù)庫(kù)操作過(guò)程中非常重要的工作，MySQL數(shù)據(jù)庫(kù)的優(yōu)化能夠?qū)崿F(xiàn)MySQL數(shù)據(jù)庫(kù)操作的簡(jiǎn)便。

第一步：

1:磁盤尋道能力,以高速硬盤(7200轉(zhuǎn)/秒),理論上每秒尋道7200次.這是沒(méi)有辦法改變的,優(yōu)化的方法是----用多個(gè)硬盤,或者把數(shù)據(jù)分散存儲(chǔ).

2:硬盤的讀寫速度,這個(gè)速度非常的快,這個(gè)更容易解決--可以從多個(gè)硬盤上并行讀寫.

3:cpu.cpu處理內(nèi)存中的數(shù)據(jù),當(dāng)有相對(duì)內(nèi)存較小的表時(shí),這是最常見(jiàn)的限制因素.

4:內(nèi)存的限制.當(dāng)cpu需要超出適合cpu緩存的數(shù)據(jù)時(shí),緩存的帶寬就成了內(nèi)存的一個(gè)瓶頸---不過(guò)現(xiàn)在內(nèi)存大的驚人,一般不會(huì)出現(xiàn)這個(gè)問(wèn)題.

第二步: (本人使用的是學(xué)校網(wǎng)站的linux平臺(tái)(Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))

1:調(diào)節(jié)服務(wù)器參數(shù)

用shell>mysqld-help這個(gè)命令聲廠一張所有mysql選項(xiàng)和可配置變量的表.輸出以下信息:

possible variables for option--set-variable(-o) are:

back_log current value:5 //要求mysql能有的連接數(shù)量.back_log指出在mysql暫停接受連接的時(shí)間內(nèi)有多少個(gè)連接請(qǐng)求可以被存在堆棧中

connect_timeout current value:5 //mysql服務(wù)器在用bad handshake(不好翻譯)應(yīng)答前等待一個(gè)連接的時(shí)間

delayed_insert_timeout current value:200 //一個(gè)insert delayed在終止前等待insert的時(shí)間

delayed_insert_limit current value:50 //insert delayed處理器將檢查是否有任何select語(yǔ)句未執(zhí)行,如果有,繼續(xù)前執(zhí)行這些語(yǔ)句

delayed_queue_size current value:1000 //為insert delayed分配多大的隊(duì)

flush_time current value:0 //如果被設(shè)置為非0,那么每個(gè)flush_time 時(shí)間,所有表都被關(guān)閉

interactive_timeout current value:28800 //服務(wù)器在關(guān)上它之前在洋交互連接上等待的時(shí)間

join_buffer_size current value:131072 //用與全部連接的緩沖區(qū)大小

key_buffer_size current value:1048540 //用語(yǔ)索引塊的緩沖區(qū)的大小,增加它可以更好的處理索引

lower_case_table_names current value:0 //

long_query_time current value:10 //如果一個(gè)查詢所用時(shí)間大于此時(shí)間,slow_queried計(jì)數(shù)將增加

max_allowed_packet current value:1048576 //一個(gè)包的大小

max_connections current value:300 //允許同時(shí)連接的數(shù)量

max_connect_errors current value:10 //如果有多于該數(shù)量的中斷連接,將阻止進(jìn)一步的連接,可以用flush hosts來(lái)解決

max_delayed_threads current value:15 //可以啟動(dòng)的處理insert delayed的數(shù)量

max_heap_table_size current value:16777216 //

max_join_size current value:4294967295 //允許讀取的連接的數(shù)量

max_sort_length current value:1024 //在排序blob或者text時(shí)使用的字節(jié)數(shù)量

max_tmp_tables current value:32 //一個(gè)連接同時(shí)打開(kāi)的臨時(shí)表的數(shù)量

max_write_lock_count current value:4294967295 //指定一個(gè)值(通常很小)來(lái)啟動(dòng)mysqld,使得在一定數(shù)量的write鎖定之后出現(xiàn)read鎖定

net_buffer_length current value:16384 //通信緩沖區(qū)的大小--在查詢時(shí)被重置為該大小

query_buffer_size current value:0 //查詢時(shí)緩沖區(qū)大小

record_buffer current value:131072 //每個(gè)順序掃描的連接為其掃描的每張表分配的緩沖區(qū)的大小

sort_buffer current value:2097116 //每個(gè)進(jìn)行排序的連接分配的緩沖區(qū)的大小

table_cache current value:64 //為所有連接打開(kāi)的表的數(shù)量

thread_concurrency current value:10 //

tmp_table_size current value:1048576 //臨時(shí)表的大小

thread_stack current value:131072 //每個(gè)線程的大小

wait_timeout current value:28800 //服務(wù)器在關(guān)閉它3之前的一個(gè)連接上等待的時(shí)間

根據(jù)自己的需要配置以上信息會(huì)對(duì)你幫助.

第三步：

1:如果你在一個(gè)數(shù)據(jù)庫(kù)中創(chuàng)建大量的表,那么執(zhí)行打開(kāi),關(guān)閉，創(chuàng)建(表)的操作就會(huì)很慢. 2:mysql使用內(nèi)存

a: 關(guān)鍵字緩存區(qū)(key_buffer_size)由所有線程共享

b: 每個(gè)連接使用一些特定的線程空間.一個(gè)棧(默認(rèn)為64k,變量thread_stack),一個(gè)連接緩沖區(qū)(變量net_buffer_length)和一個(gè)結(jié)果緩沖區(qū)(net_buffer_length).特定情況下,連接緩沖區(qū)和結(jié)果緩沖區(qū)被動(dòng)態(tài)擴(kuò)大到max_allowed_packet.

c:所有線程共享一個(gè)基存儲(chǔ)器

d:沒(méi)有內(nèi)存影射

e:每個(gè)做順序掃描的請(qǐng)求分配一個(gè)讀緩沖區(qū)(record_buffer)

f:所有聯(lián)結(jié)均有一遍完成并且大多數(shù)聯(lián)結(jié)甚至可以不用一個(gè)臨時(shí)表完成.最臨時(shí)的表是基于內(nèi)存的(heap)表

g:排序請(qǐng)求分配一個(gè)排序緩沖區(qū)和2個(gè)臨時(shí)表

h:所有語(yǔ)法分析和計(jì)算都在一個(gè)本地存儲(chǔ)器完成

i:每個(gè)索引文件只被打開(kāi)一次,并且數(shù)據(jù)文件為每個(gè)并發(fā)運(yùn)行的線程打開(kāi)一次

j:對(duì)每個(gè)blob列的表，一個(gè)緩沖區(qū)動(dòng)態(tài)的被擴(kuò)大以便讀入blob值

k:所有正在使用的表的表處理器被保存在一個(gè)緩沖器中并且作為一個(gè)fifo管理.

l:一個(gè)mysqladmin flush-tables命令關(guān)閉所有不在使用的表并且在當(dāng)前執(zhí)行的線程結(jié)束時(shí)標(biāo)記所有在使用的表準(zhǔn)備關(guān)閉

3:mysql鎖定表

mysql中所有鎖定不會(huì)成為死鎖. wirte鎖定: mysql的鎖定原理:a:如果表沒(méi)有鎖定,那么鎖定;b否則,把鎖定請(qǐng)求放入寫鎖定隊(duì)列中

read鎖定: mysql的鎖定原理:a:如果表沒(méi)有鎖定,那么鎖定;b否則,把鎖定請(qǐng)求放入讀鎖定隊(duì)列中

有時(shí)候會(huì)在一個(gè)表中進(jìn)行很多的select,insert操作,可以在一個(gè)臨時(shí)表中插入行并且偶爾用臨時(shí)表的記錄更新真正的表

a:用low_priority屬性給一個(gè)特定的insert,update或者delete較低的優(yōu)先級(jí)

b:max_write_lock_count指定一個(gè)值(通常很小)來(lái)啟動(dòng)mysqld,使得在一定數(shù)量的write鎖定之后出現(xiàn)read鎖定

c:通過(guò)使用set sql_low_priority_updates=1可以從一個(gè)特定的線程指定所有的更改應(yīng)該由較低的優(yōu)先級(jí)完成

d:用high_priority指定一個(gè)select

e:如果使用insert....select....出現(xiàn)問(wèn)題,使用myisam表------因?yàn)樗С忠驗(yàn)樗С植l(fā)的select和insert

4:最基本的優(yōu)化是使數(shù)據(jù)在硬盤上占據(jù)的空間最小.如果索引做在最小的列上,那么索引也最小.實(shí)現(xiàn)方法:

a:使用盡可能小的數(shù)據(jù)類型

b:如果可能，聲明表列為NOT NULL.

c:如果有可能使用變成的數(shù)據(jù)類型,如varchar(但是速度會(huì)受一定的影響)

d:每個(gè)表應(yīng)該有盡可能短的主索引 e:創(chuàng)建確實(shí)需要的索引

f:如果一個(gè)索引在頭幾個(gè)字符上有唯一的前綴,那么僅僅索引這個(gè)前綴----mysql支持在一個(gè)字符列的一部分上的索引

g:如果一個(gè)表經(jīng)常被掃描,那么試圖拆分它為更多的表

第四步

1:索引的使用,索引的重要性就不說(shuō)了,功能也不說(shuō)了,只說(shuō)怎么做. 首先要明確所有的mysql索引(primary,unique,index)在b樹(shù)中有存儲(chǔ).索引主要用語(yǔ):

a:快速找到where指定條件的記錄 b:執(zhí)行聯(lián)結(jié)時(shí),從其他表檢索行 c:對(duì)特定的索引列找出max()和min()值

d：如果排序或者分組在一個(gè)可用鍵的最前面加前綴，排序或分組一個(gè)表

e：一個(gè)查詢可能被用來(lái)優(yōu)化檢索值，而不用訪問(wèn)數(shù)據(jù)文件.如果某些表的列是數(shù)字型并且正好是某個(gè)列的前綴，為了更快，值可以從索引樹(shù)中取出

2：存儲(chǔ)或者更新數(shù)據(jù)的查詢速度 　grant的執(zhí)行會(huì)稍稍的減低效率.

mysql的函數(shù)應(yīng)該被高度的優(yōu)化.可以用benchmark(loop_count,expression)來(lái)找出是否查詢有問(wèn)題

select的查詢速度：如果想要讓一個(gè)select...where...更快，我能想到的只有建立索引.可以在一個(gè)表上運(yùn)行myisamchk--analyze來(lái)更好的優(yōu)化查詢.可以用myisamchk--sort-index--sort-records=1來(lái)設(shè)置用一個(gè)索引排序一個(gè)索引和數(shù)據(jù).

3：mysql優(yōu)化where子句

3.1：刪除不必要的括號(hào)：

((a AND b) AND c OR (((a AND b) AND (a AND d))))>(a AND b AND c) OR (a AND b AND c AND d)

3.2:使用常數(shù)

(ab>5 AND b=c AND a=5

3.3:刪除常數(shù)條件

(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=100 AND 2=3) >b=5 OR b=6

3.4:索引使用的常數(shù)表達(dá)式僅計(jì)算一次

3.5：在一個(gè)表中，沒(méi)有一個(gè)where的count(*)直接從表中檢索信息

3.6:所有常數(shù)的表在查詢中在任何其他表之前讀出

3.7:對(duì)外聯(lián)結(jié)表最好聯(lián)結(jié)組合是嘗試了所有可能性找到的

3.8：如果有一個(gè)order　by字句和一個(gè)不同的group　by子句或者order　by或者group　by包含不是來(lái)自聯(lián)結(jié)的第一個(gè)表的列，那么創(chuàng)建一個(gè)臨時(shí)表

3.9:如果使用了sql_small_result，那么msyql使用在內(nèi)存中的一個(gè)表

3.10:每個(gè)表的索引給查詢并且使用跨越少于30%的行的索引.

3.11在每個(gè)記錄輸出前，跳過(guò)不匹配having子句的行

4：優(yōu)化left　join

在mysql中　a left join b按以下方式實(shí)現(xiàn)

a：表b依賴于表a

b：表a依賴于所有用在left　join條件的表(除了b)

c：所有l(wèi)eft　join條件被移到where子句中

d：進(jìn)行所有的聯(lián)結(jié)優(yōu)化，除了一個(gè)表總是在所有他依賴的表后讀取.如果有一個(gè)循環(huán)依賴，那么將發(fā)生錯(cuò)誤

e：進(jìn)行所有的標(biāo)準(zhǔn)的where優(yōu)化 f：如果在a中有一行匹配where子句，但是在b中沒(méi)有任何匹配left　join條件，那么，在b中生成的所有設(shè)置為NULL的一行

g：如果使用left　join來(lái)找出某些表中不存在的行并且在where部分有column_name IS NULL測(cè)試(column_name為NOT NULL列).那么，mysql在它已經(jīng)找到了匹配left　join條件的一行后，將停止在更多的行后尋找

5：優(yōu)化limit

a：如果用limit只選擇一行，當(dāng)mysql需要掃描整個(gè)表時(shí)，它的作用相當(dāng)于索引

b：如果使用limit#與order　by，mysql如果找到了第#行，將結(jié)束排序，而不會(huì)排序正個(gè)表

c：當(dāng)結(jié)合limit#和distinct時(shí)，mysql如果找到了第#行，將停止

d：只要mysql已經(jīng)發(fā)送了第一個(gè)#行到客戶，mysql將放棄查詢

e：limit 0一直會(huì)很快的返回一個(gè)空集合.

f：臨時(shí)表的大小使用limit#計(jì)算需要多少空間來(lái)解決查詢

6：優(yōu)化insert

插入一條記錄的是由以下構(gòu)成：

a:連接(3)

b:發(fā)送查詢給服務(wù)器(2)

c:分析查詢(2)

d:插入記錄(1*記錄大小)

e：插入索引(1*索引)

f：關(guān)閉(1)

以上數(shù)字可以看成和總時(shí)間成比例

改善插入速度的一些方法：

6.1：如果同時(shí)從一個(gè)連接插入許多行，使用多個(gè)值的insert，這比用多個(gè)語(yǔ)句要快

6.2：如果從不同連接插入很多行，使用insert　delayed語(yǔ)句速度更快

6.3: 用myisam，如果在表中沒(méi)有刪除的行，能在select：s正在運(yùn)行的同時(shí)插入行

6.4: 當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，用load　data　infile.這個(gè)通常比insert快20 倍

6.5:可以鎖定表然后插入--主要的速度差別是在所有insert語(yǔ)句完成后，索引緩沖區(qū)僅被存入到硬盤一次.一般與有不同的insert語(yǔ)句那樣多次存入要快.如果能用一個(gè)單個(gè)語(yǔ)句插入所有的行，鎖定就不需要.鎖定也降低連接的整體時(shí)間.但是對(duì)某些線程最大等待時(shí)間將上升.例如：

thread 1 does 1000 inserts

thread 2,3 and 4 does 1 insert

thread 5 does 1000 inserts

如果不使用鎖定，2，3，4將在1和5之前完成.如果使用鎖定，2，3，4，將可能在1和5之后完成.但是整體時(shí)間應(yīng)該快40%.因?yàn)閕nsert，update，delete操作在mysql中是很快的，通過(guò)為多于大約5次連續(xù)不斷的插入或更新一行的東西加鎖，將獲得更好的整體性能.如果做很多一行的插入，可以做一個(gè)lock　tables，偶爾隨后做一個(gè)unlock　tables(大約每1000行)以允許另外的線程存取表.這仍然將導(dǎo)致獲得好的性能.load　data　infile對(duì)裝載數(shù)據(jù)仍然是很快的.

為了對(duì)load　data　infile和insert得到一些更快的速度，擴(kuò)大關(guān)鍵字緩沖區(qū).

7優(yōu)化update的速度

它的速度依賴于被更新數(shù)據(jù)的大小和被更新索引的數(shù)量

使update更快的另一個(gè)方法是推遲修改，然后一行一行的做很多修改.如果鎖定表，做一行一行的很多修改比一次做一個(gè)快

8優(yōu)化delete速度

刪除一個(gè)記錄的時(shí)間與索引數(shù)量成正比.為了更快的刪除記錄，可以增加索引緩存的大小 從一個(gè)表刪除所有行比刪除這個(gè)表的大部分要快的多

第五步

1：選擇一種表類型 1.1靜態(tài)myisam

這種格式是最簡(jiǎn)單且最安全的格式，它是磁盤格式中最快的.速度來(lái)自于數(shù)據(jù)能在磁盤上被找到的難易程度.當(dāng)鎖定有一個(gè)索引和靜態(tài)格式的東西是，它很簡(jiǎn)單，只是行長(zhǎng)度乘以數(shù)量.而且在掃描一張表時(shí)，每次用磁盤讀取來(lái)讀入常數(shù)個(gè)記錄是很容易的.安全性來(lái)源于如果當(dāng)寫入一個(gè)靜態(tài)myisam文件時(shí)導(dǎo)致計(jì)算機(jī)down掉，myisamchk很容易指出每行在哪里開(kāi)始和結(jié)束，因此，它通常能收回所有記錄，除了部分被寫入的記錄.在mysql中所有索引總能被重建

1.2動(dòng)態(tài)myisam

這種格式每一行必須有一個(gè)頭說(shuō)明它有多長(zhǎng).當(dāng)一個(gè)記錄在更改期間變長(zhǎng)時(shí)，它可以在多于一個(gè)位置上結(jié)束.能使用optimize　tablename或myisamchk整理一張表.如果在同一個(gè)表中有像某些varchar或者blob列那樣存取/改變的靜態(tài)數(shù)據(jù)，將動(dòng)態(tài)列移入另外一個(gè)表以避免碎片.

1.2.1壓縮myisam，用可選的myisampack工具生成

1.2.2內(nèi)存

這種格式對(duì)小型/中型表很有用.對(duì)拷貝/創(chuàng)建一個(gè)常用的查找表到洋heap表有可能加快多個(gè)表聯(lián)結(jié)，用同樣數(shù)據(jù)可能要快好幾倍時(shí)間.

select tablename.a,tablename2.a from tablename,tablanem2,tablename3 where

tablaneme.a=tablename2.a and tablename2.a=tablename3.a and tablename2.c!=0;

為了加速它，可以用tablename2和tablename3的聯(lián)結(jié)創(chuàng)建一個(gè)臨時(shí)表，因?yàn)橛孟嗤?tablename1.a)查找.

CREATE　TEMPORARY　TABLE　test TYPE=HEAP

SELECT

tablename2.a as a2,tablename3.a as a3

FROM

tablenam2,tablename3

WHERE

tablename2.a=tablename3.a and c=0;

SELECT tablename.a,test.a3 from tablename,test where tablename.a=test.a1;

SELECT tablename.a,test,a3,from tablename,test where tablename.a=test.a1 and ....;

1.3靜態(tài)表的特點(diǎn)

1.3.1默認(rèn)格式.用在表不包含varchar，blob，text列的時(shí)候

1.3.2所有的char，numeric和decimal列填充到列寬度

1.3.3非常快

1.3.4容易緩沖

1.3.5容易在down后重建，因?yàn)橛涗浳挥诠潭ǖ奈恢?/p>

1.3.6不必被重新組織(用myisamchk)，除非是一個(gè)巨量的記錄被刪除并且優(yōu)化存儲(chǔ)大小

1.3.7通常比動(dòng)態(tài)表需要更多的存儲(chǔ)空間

1.4動(dòng)態(tài)表的特點(diǎn)

1.4.1如果表包含任何varchar，blob，text列，使用該格式

1.4.2所有字符串列是動(dòng)態(tài)的

1.4.3每個(gè)記錄前置一個(gè)位.

1.4.4通常比定長(zhǎng)表需要更多的磁盤空間

1.4.5每個(gè)記錄僅僅使用所需要的空間，如果一個(gè)記錄變的很大，它按需要被分成很多段，這導(dǎo)致了記錄碎片

1.4.6如果用超過(guò)行長(zhǎng)度的信息更新行，行被分段.

1.4.7在系統(tǒng)down掉以后不好重建表，因?yàn)橐粋€(gè)記錄可以是多段

1.4.8對(duì)動(dòng)態(tài)尺寸記錄的期望行長(zhǎng)度是3+(number　of　columns+7)/8+(number of char columns)+packed size of numeric columns+length of strings +(number of NULL columns+7)/8

對(duì)每個(gè)連接有6個(gè)字節(jié)的懲罰.無(wú)論何時(shí)更改引起記錄的變大，都有一個(gè)動(dòng)態(tài)記錄被連接.每個(gè)新連接至少有20個(gè)字節(jié)，因此下一個(gè)變大將可能在同一個(gè)連接中.如果不是，將有另外一個(gè)連接.可以用myisamchk　-惡毒檢查有多少連接.所有連接可以用myisamchk -r刪除.

1.5壓縮表的特點(diǎn)

1.5.1一張用myisampack實(shí)用程序制作的只讀表.

1.5.2解壓縮代碼存在于所有mysql分發(fā)中，以便使沒(méi)有myisampack的連接也能讀取用myisampack壓縮的表

1.5.3占據(jù)很小的磁盤空間

1.5.4每個(gè)記錄被單獨(dú)壓縮.一個(gè)記錄的頭是一個(gè)定長(zhǎng)的(1～～3個(gè)字節(jié))這取決于表的最大記錄.每列以不同的方式被壓縮.一些常用的壓縮類型是：

a:通常對(duì)每列有一張不同的哈夫曼表 　b:后綴空白壓縮 　c:前綴空白壓縮 d:用值0的數(shù)字使用1位存儲(chǔ)

e:如果整數(shù)列的值有一個(gè)小范圍，列使用最小的可能類型來(lái)存儲(chǔ).例如：如果所有的值在0到255之間，一個(gè)bigint可以作為一個(gè)tinyint存儲(chǔ)

g:如果列僅有可能值的一個(gè)小集合，列類型被轉(zhuǎn)換到enum 　h:列可以使用上面的壓縮方法的組合

1.5.5能處理定長(zhǎng)或動(dòng)態(tài)長(zhǎng)度的記錄，去不能處理blob或者text列 1.5.6能用myisamchk解壓縮

mysql能支持不同的索引類型，但一般的類型是isam，這是一個(gè)B樹(shù)索引并且能粗略的為索引文件計(jì)算大小為(key_length+4)*0.67，在所有的鍵上的總和.

字符串索引是空白壓縮的。如果第一個(gè)索引是一個(gè)字符串，它可將壓縮前綴如果字符串列有很多尾部空白或是一個(gè)總部能甬道全長(zhǎng)的varchar列，空白壓縮使索引文件更小.如果很多字符串有相同的前綴.

1.6內(nèi)存表的特點(diǎn)

mysql內(nèi)部的heap表使用每偶溢出去的100%動(dòng)態(tài)哈希并且沒(méi)有與刪除有關(guān)的問(wèn)題.只能通過(guò)使用在堆表中的一個(gè)索引來(lái)用等式存取東西(通常用'='操作符)

堆表的缺點(diǎn)是：

1.6.1想要同時(shí)使用的所有堆表需要足夠的額外內(nèi)存

1.6.2不能在索引的一個(gè)部分搜索

1.6.3不能按順序搜索下一個(gè)條目(即，使用這個(gè)索引做一個(gè)order　by)

1.6.4mysql不能算出在2個(gè)值之間大概有多少行.這被優(yōu)化器使用是用來(lái)決定使用哪個(gè)索引的，但是在另一個(gè)方面甚至不需要磁盤尋道

通過(guò)上文中介紹的這五步就能夠?qū)崿F(xiàn)MySQL數(shù)據(jù)庫(kù)的優(yōu)化工作，希望大家都能夠從上文中涉及到的內(nèi)容中有所收獲。

【編輯推薦】

1. 一位資深DBA的十年職業(yè)之路
2. 教你五招，快速提升MySQL可擴(kuò)展性
3. MySQL數(shù)據(jù)庫(kù)新特性之存儲(chǔ)過(guò)程入門教程
4. 幾個(gè)不錯(cuò)的免費(fèi)MySQL管理工具
5. MySQL性能優(yōu)化的21個(gè)最佳實(shí)踐