mysql資料庫優化(轉)

下決心開始學oracle了,用mysql已經有不短的時間了,今天寫下這些算是對自己的一個mysql之旅的一個交代吧.以下僅僅是本人在使用mysql過程中的一點個人的體會,也許存在許多纰漏和錯誤,還請指正!!

首先,為了使一個系統更快,最重要的部分就是基礎設計,不過有些東西是現有情況下無法逾越的,比如說系統常見的瓶頸.

我所能想到的:

1:磁盤尋道能力,以高速硬碟(7200轉/秒),理論上每秒尋道7200次.這是沒有辦法改變的,優化的方法是----用多個硬碟,或者把資料分散存儲.

2:硬碟的讀寫速度,這個速度非常的快(限于本人的知識所限,隻知道在每秒幾十甚至上百MB).這個更容易解決--可以從多個硬碟上并行讀寫.

3:cpu.cpu處理記憶體中的資料,當有相對記憶體較小的表時,這是最常見的限制因素.

4:記憶體的限制.當cpu需要超出适合cpu緩存的資料時,緩存的帶寬就成了記憶體的一個瓶頸---不過現在記憶體大的驚人,一般不會出現這個問題.

第二步:

(本人使用的是學校網站的linux平台(Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))

1:調節伺服器參數

用shell>mysqld-help這個指令聲廠一張所有mysql選項和可配置變量的表.輸出以下資訊:

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

back_log current value:5 //要求mysql能有的連接配接數量.back_log指出在mysql暫停接受連接配接的時間内有多少個連接配接請求可以被存在堆棧中

connect_timeout current value:5 //mysql伺服器在用bad handshake(不好翻譯)應答前等待一個連接配接的時間

delayed_insert_timeout current value:200 //一個insert delayed在終止前等待insert的時間

delayed_insert_limit current value:50 //insert delayed處理器将檢查是否有任何select語句未執行,如果有,繼續前執行這些語句

delayed_queue_size current value:1000 //為insert delayed配置設定多大的隊

flush_time current value:0 //如果被設定為非0,那麼每個flush_time 時間,所有表都被關閉

interactive_timeout current value:28800 //伺服器在關上它之前在洋互動連接配接上等待的時間

join_buffer_size current value:131072 //用與全部連接配接的緩沖區大小

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

lower_case_table_names current value:0 //

long_query_time current value:10 //如果一個查詢所用時間大于此時間,slow_queried計數将增加

max_allowed_packet current value:1048576 //一個包的大小

max_connections current value:300 //允許同時連接配接的數量

max_connect_errors current value:10 //如果有多于該數量的中斷連接配接,将阻止進一步的連接配接,可以用flush hosts來解決

max_delayed_threads current value:15 //可以啟動的處理insert delayed的數量

max_heap_table_size current value:16777216 //

max_join_size current value:4294967295 //允許讀取的連接配接的數量

max_sort_length current value:1024 //在排序blob或者text時使用的位元組數量

max_tmp_tables current value:32 //一個連接配接同時打開的臨時表的數量

max_write_lock_count current value:4294967295 //指定一個值(通常很小)來啟動mysqld,使得在一定數量的write鎖定之後出現read鎖定

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

query_buffer_size current value:0 //查詢時緩沖區大小

record_buffer current value:131072 //每個順序掃描的連接配接為其掃描的每張表配置設定的緩沖區的大小

sort_buffer current value:2097116 //每個進行排序的連接配接配置設定的緩沖區的大小

table_cache current value:64 //為所有連接配接打開的表的數量

thread_concurrency current value:10 //

tmp_table_size current value:1048576 //臨時表的大小

thread_stack current value:131072 //每個線程的大小

wait_timeout current value:28800 //伺服器在關閉它3之前的一個連接配接上等待的時間

根據自己的需要配置以上資訊會對你幫助.

第三:

1:如果你在一個資料庫中建立大量的表,那麼執行打開,關閉，建立(表)的操作就會很慢.

2:mysql使用記憶體

a: 關鍵字緩存區(key_buffer_size)由所有線程共享

b: 每個連接配接使用一些特定的線程空間.一個棧(預設為64k,變量thread_stack),一個連接配接緩沖區(變量net_buffer_length)和一個結果緩沖區(net_buffer_length).特定情況下,連接配接緩沖區和結果緩沖區被動态擴大到max_allowed_packet.

c:所有線程共享一個基存儲器

d:沒有記憶體影射

e:每個做順序掃描的請求配置設定一個讀緩沖區(record_buffer)

f:所有聯結均有一遍完成并且大多數聯結甚至可以不用一個臨時表完成.最臨時的表是基于記憶體的(heap)表

g:排序請求配置設定一個排序緩沖區和2個臨時表

h:所有文法分析和計算都在一個本地存儲器完成

i:每個索引檔案隻被打開一次,并且資料檔案為每個并發運作的線程打開一次

j:對每個blob列的表，一個緩沖區動态的被擴大以便讀入blob值

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

l:一個mysqladmin flush-tables指令關閉所有不在使用的表并且在目前執行的線程結束時标記所有在使用的表準備關閉

3:mysql鎖定表

mysql中所有鎖定不會成為死鎖.

wirte鎖定:

mysql的鎖定原理:a:如果表沒有鎖定,那麼鎖定;b否則,把鎖定請求放入寫鎖定隊列中

read鎖定:

mysql的鎖定原理:a:如果表沒有鎖定,那麼鎖定;b否則,把鎖定請求放入讀鎖定隊列中

有時候會在一個表中進行很多的select,insert操作,可以在一個臨時表中插入行并且偶爾用臨時表的記錄更新真正的表

a:用low_priority屬性給一個特定的insert,update或者delete較低的優先級

b:max_write_lock_count指定一個值(通常很小)來啟動mysqld,使得在一定數量的write鎖定之後出現read鎖定

c:通過使用set sql_low_priority_updates=1可以從一個特定的線程指定所有的更改應該由較低的優先級完成

d:用high_priority指定一個select

e:如果使用insert....select....出現問題,使用myisam表------因為它支援因為它支援并發的select和insert

4:最基本的優化是使資料在硬碟上占據的空間最小.如果索引做在最小的列上,那麼索引也最小.實作方法:

a:使用盡可能小的資料類型

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

c:如果有可能使用變成的資料類型,如varchar(但是速度會受一定的影響)

d:每個表應該有盡可能短的主索引

e:建立确實需要的索引

f:如果一個索引在頭幾個字元上有唯一的字首,那麼僅僅索引這個字首----mysql支援在一個字元列的一部分上的索引

g:如果一個表經常被掃描,那麼試圖拆分它為更多的表

第四步

1:索引的使用,索引的重要性就不說了,功能也不說了,隻說怎麼做.

首先要明确所有的mysql索引(primary,unique,index)在b樹中有存儲.索引主要用語:

a:快速找到where指定條件的記錄

b:執行聯結時,從其他表檢索行

c:對特定的索引列找出max()和min()值

d：如果排序或者分組在一個可用鍵的最前面加字首，排序或分組一個表

e：一個查詢可能被用來優化檢索值，而不用通路資料檔案．如果某些表的列是數字型并且正好是某個列的字首，為了更快，值可以從索引樹中取出

２：存儲或者更新資料的查詢速度

　grant的執行會稍稍的減低效率．

　mysql的函數應該被高度的優化．可以用benchmark（loop_count,expression)來找出是否查詢有問題

　select 的查詢速度：如果想要讓一個select．．．where．．．更快，我能想到的隻有建立索引．可以在一個表上運作myisamchk－－analyze 來更好的優化查詢．可以用myisamchk－－sort－index－－sort－records＝１來設定用一個索引排序一個索引和資料．

３：mysql優化where子句

3.１：删除不必要的括号：

　（（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:使用常數

　（a<b AND b=c) AND a=100 > b>5 AND b=c AND a=5

3.3:删除常數條件

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

3.4:索引使用的常數表達式僅計算一次

3.5：在一個表中，沒有一個where的count(*)直接從表中檢索資訊

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

3.7:對外聯結表最好聯結組合是嘗試了所有可能性找到的

3.8：如果有一個order　by字句和一個不同的group　by子句或者order　by或者group　by包含不是來自聯結的第一個表的列，那麼建立一個臨時表

3.9:如果使用了sql_small_result，那麼msyql使用在記憶體中的一個表

3.10:每個表的索引給查詢并且使用跨越少于３０％的行的索引．

3.11在每個記錄輸出前，跳過不比對having子句的行

４：優化left　join

在mysql中　a left join b按以下方式實作

a：表b依賴于表a　

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

c：所有left　join條件被移到where子句中

d：進行所有的聯結優化，除了一個表總是在所有他依賴的表後讀取．如果有一個循環依賴，那麼将發生錯誤

e：進行所有的标準的where優化

f：如果在a中有一行比對where子句，但是在b中沒有任何比對left　join條件，那麼，在b中生成的所有設定為ＮＵＬＬ的一行

g：如果使用left　join來找出某些表中不存在的行并且在where部分有column_name IS NULL測試(column_name為NOT NULL列)．那麼，mysql在它已經找到了比對left　join條件的一行後，将停止在更多的行後尋找

５：優化limit

a：如果用limit隻選擇一行，當mysql需要掃描整個表時，它的作用相當于索引

b：如果使用limit＃與order　by，mysql如果找到了第＃行，将結束排序，而不會排序正個表

c：當結合limit＃和distinct時，mysql如果找到了第＃行，将停止

d：隻要mysql已經發送了第一個＃行到客戶，mysql将放棄查詢

e：limit 0一直會很快的傳回一個空集合．

f：臨時表的大小使用limit＃計算需要多少空間來解決查詢

６：優化insert

插入一條記錄的是由以下構成：

a:連接配接（３）

b:發送查詢給伺服器（２）

c:分析查詢（２）

d:插入記錄（１*記錄大小）

e：插入索引（１*索引）

f：關閉（１）

以上數字可以看成和總時間成比例

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

6.1：如果同時從一個連接配接插入許多行，使用多個值的insert，這比用多個語句要快

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

6.3: 用myisam，如果在表中沒有删除的行，能在select：s正在運作的同時插入行

6.4: 當從一個文本檔案裝載一個表時，用load　data　infile．這個通常比insert快20

倍

6.5: 可以鎖定表然後插入－－主要的速度差别是在所有insert語句完成後，索引緩沖區僅被存入到硬碟一次．一般與有不同的insert語句那樣多次存入要快．如果能用一個單個語句插入所有的行，鎖定就不需要．鎖定也降低連接配接的整體時間．但是對某些線程最大等待時間将上升．例如：

thread 1 does 1000 inserts

thread 2,3 and 4 does 1 insert

thread 5 does 1000 inserts

如果不使用鎖定，２，３，４将在１和５之前完成．如果使用鎖定，２，３，４，将可能在１和５之後完成．但是整體時間應該快４０％．因為insert， update，delete操作在mysql中是很快的，通過為多于大約５次連續不斷的插入或更新一行的東西加鎖，将獲得更好的整體性能．如果做很多一行的插入，可以做一個lock　tables，偶爾随後做一個unlock　tables（大約每１０００行）以允許另外的線程存取表．這仍然将導緻獲得好的性能．load　data　infile對裝載資料仍然是很快的．

為了對load　data　infile和insert得到一些更快的速度，擴大關鍵字緩沖區．

７優化update的速度

它的速度依賴于被更新資料的大小和被更新索引的數量

使update更快的另一個方法是推遲修改，然後一行一行的做很多修改．如果鎖定表，做一行一行的很多修改比一次做一個快

８優化delete速度

删除一個記錄的時間與索引數量成正比．為了更快的删除記錄，可以增加索引緩存的大小

從一個表删除所有行比删除這個表的大部分要快的多

第五步

１：選擇一種表類型

1.1靜态myisam

這種格式是最簡單且最安全的格式，它是磁盤格式中最快的．速度來自于資料能在磁盤上被找到的難易程度．當鎖定有一個索引和靜态格式的東西是，它很簡單，隻是行長度乘以數量．而且在掃描一張表時，每次用磁盤讀取來讀入常數個記錄是很容易的．安全性來源于如果當寫入一個靜态myisam檔案時導緻計算機down 掉，myisamchk很容易指出每行在哪裡開始和結束，是以，它通常能收回所有記錄，除了部分被寫入的記錄．在mysql中所有索引總能被重建

1.2動态myisam

這種格式每一行必須有一個頭說明它有多長．當一個記錄在更改期間變長時，它可以在多于一個位置上結束．能使用optimize　tablename或 myisamchk整理一張表．如果在同一個表中有像某些varchar或者blob列那樣存取／改變的靜态資料，将動态列移入另外一個表以避免碎片．

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

1.2.2記憶體

這種格式對小型／中型表很有用．對拷貝／建立一個常用的查找表到洋heap表有可能加快多個表聯結，用同樣資料可能要快好幾倍時間．

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的聯結建立一個臨時表，因為用相同列（tablename1.a）查找．

ＣＲＥＡＴＥ　ＴＥＭＰＯＲＡＲＹ　ＴＡＢＬＥ　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靜态表的特點

1.3.1預設格式．用在表不包含varchar，blob，text列的時候

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

1.3.3非常快

1.3.4容易緩沖

1.3.5容易在down後重建，因為記錄位于固定的位置

1.3.6不必被重新組織（用myisamchk），除非是一個巨量的記錄被删除并且優化存儲大小

1.3.7通常比動态表需要更多的存儲空間

1.4動态表的特點

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

1.4.2所有字元串列是動态的

1.4.3每個記錄前置一個位．

1.4.4通常比定長表需要更多的磁盤空間

1.4.5每個記錄僅僅使用所需要的空間，如果一個記錄變的很大，它按需要被分成很多段，這導緻了記錄碎片

1.4.6如果用超過行長度的資訊更新行，行被分段．

1.4.7在系統down掉以後不好重建表，因為一個記錄可以是多段

1.4.8對動态尺寸記錄的期望行長度是３＋（number　of　columns＋７）／８＋(number

of char columns)+packed size of numeric columns+length of strings +(number of

NULL columns+7)/8

對每個連接配接有６個位元組的懲罰．無論何時更改引起記錄的變大，都有一個動态記錄被連接配接．每個新連接配接至少有２０個位元組，是以下一個變大将可能在同一個連接配接中．如果不是，将有另外一個連接配接．可以用myisamchk　－惡毒檢查有多少連接配接．所有連接配接可以用myisamchk -r删除．

1.5壓縮表的特點

1.5.1一張用myisampack實用程式制作的隻讀表．

1.5.2解壓縮代碼存在于所有mysql分發中，以便使沒有myisampack的連接配接也能讀取用myisampack壓縮的表

1.5.3占據很小的磁盤空間

1.5.4每個記錄被單獨壓縮．一個記錄的頭是一個定長的（１～～３個位元組）這取決于表的最大記錄．每列以不同的方式被壓縮．一些常用的壓縮類型是：

　a:通常對每列有一張不同的哈夫曼表

　b:字尾空白壓縮

　c:字首空白壓縮

d:用值０的數字使用１位存儲

e:如果整數列的值有一個小範圍，列使用最小的可能類型來存儲．例如：如果所有的值在０到２５５之間，一個bigint可以作為一個tinyint存儲

　g:如果列僅有可能值的一個小集合，列類型被轉換到enum

　h:列可以使用上面的壓縮方法的組合

1.5.5能處理定長或動态長度的記錄，去不能處理blob或者text列

1.5.6能用myisamchk解壓縮

mysql能支援不同的索引類型，但一般的類型是isam，這是一個Ｂ樹索引并且能粗略的為索引檔案計算大小為(key_length+4)*0.67，在所有的鍵上的總和．

字元串索引是空白壓縮的。如果第一個索引是一個字元串，它可将壓縮字首如果字元串列有很多尾部空白或是一個總部能甬道全長的varchar列，空白壓縮使索引檔案更小．如果很多字元串有相同的字首．

1.6記憶體表的特點

mysql内部的heap表使用每偶溢出去的１００％動态哈希并且沒有與删除有關的問題．隻能通過使用在堆表中的一個索引來用等式存取東西（通常用＇＝＇操作符）

堆表的缺點是：

1.6.1想要同時使用的所有堆表需要足夠的額外記憶體

1.6.2不能在索引的一個部分搜尋

1.6.3不能按順序搜尋下一個條目（即，使用這個索引做一個order　by）

1.6.4mysql不能算出在２個值之間大概有多少行．這被優化器使用是用來決定使用哪個索引的，但是在另一個方面甚至不需要磁盤尋道