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>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不能算出在２個值之間大概有多少行．這被最佳化器使用是用來決定使用哪個索引的，但是在另一個方面甚至不需要磁碟尋道
