最佳化mysql資料庫的方法

最佳化mysql資料庫的方法

在一個資料庫中建立大量的表,那麼執行開啟,關閉，建立(表)的操作就會很慢. 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:使用常數
　　
　　（ab>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速度
　　
　　刪除一個記錄的時間與索引數量成正比．為了更快的刪除記錄，可以增加索引緩衝的大小 從一個表刪除所有行比刪除這個表的大部分要快的多