mysql最佳化，mysql效能最佳化

mysql最佳化，mysql效能最佳化

1、查看mysql伺服器配置資訊show variables;還可以使用類似SHOW VARIABLES LIKE 'Key%';2、查看mysql伺服器啟動並執行各種狀態值show status;是當前會話，你退出就失效了。show global status;全域會話，重啟資料庫或關閉資料庫就失效了。1）、用show status語句查詢資料庫效能，如：show status like 'value';其中，value參數有connections：串連mysql伺服器的次數；uptime：mysql伺服器的上線時間；slow_queries：慢查詢的次數；com_select:查詢操作的次數；com_insert:插入操作的次數；com_update:更新操作的次數；com_delete:刪除操作的次數；註：msyql中存在查詢InnoDB類型的表的一些參數。如：InnoDB_rows_read參數表示select語句查詢的記錄數；InnoDB_rows_inserted表示insert語句插入的記錄數；InnoDB_rows_updated參數表示update語句更新的記錄數；InnoDB_rows_deleted參數表示delete語句刪除的記錄數。3、最佳化查詢語句可以用explain或desc來分析查詢語句格式：explain select語句;mysql> explain select * from pet71 \G*************************** 1. row **           id: 1  select_type: SIMPLE        table: pet71         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1        Extra: NULL1 row in set (0.00 sec)其中id：表示select語句的編號；select_type：表示select語句的類型。其中simple表示簡單查詢，其中不包括串連查詢和子查詢；primary表示主查詢，或者是最外層的查詢語句；union表示串連查詢的第二個或後面的查詢語句；table：表示查詢的表；type：表示表的連線類型。其中，system表示表中只有一條記錄；const表示表中有多條記錄，但是只從表中查詢一條記錄；all表對錶進行了完整的掃描；eq_ref表示多表串連時，後面的表使用了unique或者primary key；ref表示多表查詢時，後面的表使用了普通索引；unique_subquery表示子查詢中使用了unique或primary key；index_subquery表示子查詢中使用了普通索引；range表示查詢語句中給出了查詢範圍；index表示對錶中的索引進行了完整的掃描；possible_keys:表示查詢中可能使用的索引；key：表示查詢使用到的索引；key_len:表示索引欄位的長度；ref：表示使用哪個列或常數與索引一起來查詢記錄；rows：表示查詢的行數；extra：表示查詢過程的附件資訊1）、最佳化索引查詢a、在查詢語句中使用like關鍵字進行查詢時，如果匹配字串的第一個字元為"%"時，索引不會被使用。如果"%"不在第一個位置，索引就會被使用。b、多列索引只有查詢條件中使用這些欄位中第一個欄位，索引才會被使用。c、查詢語句中只有or關鍵字時，如果or前後的兩個條件的列都是索引時，查詢中將使用索引。如果or前後有一個條件的列不是索引，那麼查詢中將不使用索引。2）、最佳化子查詢子查詢內層查詢語句的查詢結果建立了一個暫存資料表。所以速度會受到影響。可以用串連查詢替代子查詢。4、最佳化資料庫結構1）、有些表欄位很多，可以分成多個表，資料量大的欄位如備忘欄欄位可以單獨存放在一個表中。需要顯示該欄位時可以使用聯表查詢。2）、增加中間表：建立一個表存放聯表查詢後的資料，然後可以直接查詢該中間表3）、增加冗餘欄位5、最佳化插入記錄的速度插入記錄時，索引、唯一性校正都會影響到插入記錄的速度。一次插入多條記錄和多次插入記錄耗費的時間是不同的。1）、禁用索引禁用索引alter table 表名 disable keys;插入後重新開啟索引alter table 表名 enable keys;2）、禁用唯一性檢查禁用唯一性檢查set unique_ckecks=0;重新開啟唯一性檢查set unique_ckecks=1;3)、最佳化insert語句insert語句如下：insert into food values(null,a,b),(null,b,c);或insert into food values(null,a,b)insert into food values(null,b,c);第一種減少了與資料庫的串連比第二種插入快註：load data infile語句匯入資料的速度比insert語句速度快。6、分析表、檢查表、最佳化表分析表作用是分析關鍵字的分部。檢查表作用是檢查表是否存在錯誤，還可以檢查視圖是否存在錯誤。最佳化表主要作用是消除刪除或者更新造成的空間浪費。1）、分析表analyze table 表名1[,表名2...];分析表的過程中，資料庫系統會對錶加一個唯讀鎖。在分析期間只能讀取表中的記錄，不能更新和插入記錄。分析語句能分析InnoDB和MyISAM類型的表。如：mysql> analyze table pet60;+-------------+---------+----------+----------+| Table       | Op      | Msg_type | Msg_text |+-------------+---------+----------+----------+| test1.pet60 | analyze | status   | OK       |+-------------+---------+----------+----------+1 row in set (0.53 sec)OP：表示執行的操作。analyze表示進行分析操作。check表示進行檢查尋找。optimize表示進行最佳化操作；Msg_type:表示資訊類型，其顯示的值通常是狀態、警告、錯誤和資訊這四者之一；Msg_text:顯示資訊。2）、檢查表該語句能分析InnoDB和MyISAM類型的表是否存在錯誤。該語句執行過程中也會給表加上唯讀鎖。文法：check table 表名1[,表名2...][option];其中optioin參數有5個參數，分別是quick、fast、changed、medium和extended。這5個參數的執行效率依次降低。option選項只對MyISAM類型的表有效，對InnoDB類型的表無效。3）、最佳化表該語句對InnoDB和MyISAM類型的表都有效。但是optimize table語句只能最佳化表中的varchar、blob或text類型的欄位。如：optilmize table 表名1[,表名2...];通過該語句可以消除刪除和更新造成的磁碟片段，從而減少空間浪費。該語句執行過程中也會給表加上唯讀鎖。註：如果一個表用了text或blob這樣的資料類型，那麼更新、刪除等操作後之前分配的磁碟空間不會自動收回會造成磁碟空間的浪費。使用最佳化語句就可以將這些磁碟磁碟重組出來，以便以後再利用。7、最佳化mysql伺服器分為伺服器硬體最佳化和mysql服務參數最佳化1）、最佳化伺服器硬體a、可在記憶體中為mysql設定更多緩衝區。電腦4G記憶體可以選擇my-innodb-heavy-4G.ini作為mysql資料庫的設定檔。但是這個設定檔主要支援InnoDB儲存引擎的表。電腦2G記憶體可以選擇my-huge.ini作為設定檔。b、可以用多塊磁碟儲存資料，可以從多個磁碟並行讀取資料，可以提高讀取速度。通過鏡像機制可以將不同電腦上的mysql伺服器進行同步，這些伺服器中資料是一樣的，降低單個伺服器壓力。2）、最佳化mysql的參數記憶體中為mysql保留部分緩衝區。緩衝區大小都是在mysql的設定檔中進行設定的。mysql中比較重要的配置參數都在my.cnf或my.ini檔案的[mysqld]組中。參數如：  key_buffer_size:表示索引緩衝的大小。值越大，使用索引進行查詢的速度越快。  table_cache:表示同時開啟的表的個數。這個值越大，能夠同時開啟的表的個數越多。  query_cache_size:表示查詢快取區的大小。預設為0，只適用於修改操作少且經常執行相同的查詢的操作。  query_cache_type:表示查詢緩衝區的開啟狀態。取值為0時表示關閉，取值為1時表示開啟，取值2時表示按要求使用查詢快取區。當取1時，在查詢語句中加上sql_no_cache關鍵字，該查詢語句將不使用查詢查詢快取區。可以用flush query cache語句清理查詢快取區中的片段。 當取值為2時， select語句中使用了sql_cache 關鍵字，查詢快取區才會使用。例如， select sql_cache * from score;  max_connectioins:表示資料庫最大串連數。  sort_buffer_size:表示排序緩衝區大小。值越大進行排序的速度越快。  read_buffer_size:表示為每個線程保留的緩衝區大小。當線程需要從表連續讀取記錄時需要用到這個緩衝區。set session read_buffer_size=n;可以臨時設定該參數的值。  read_rnd_buffer_size:表示為每個線程保留的緩衝區大小。與 read_buffer_size相似。但主要用於儲存特定順序讀取出來的記錄。也可以用set session read_rnd_buffer_size=n來臨時設定該參數的值。  innodb_buffer_pool_size:表示InnoDB類型的表和索引的最大緩衝。值越大查詢速度越快。  innodb_flush_log_at_trx_commit:表示何時將緩衝區的資料寫入記錄檔，並且將記錄檔寫入磁碟中。該參數有3個值，分別為0、1、2。該參數預設值為1；為0時表示每隔1秒將資料寫入記錄檔並將記錄檔寫入磁碟；為1時表示每次提交事務時將資料寫入記錄檔並將記錄檔寫入磁碟；為2時表示每次提交事務時將資料寫入記錄檔，每隔1秒將記錄檔寫入磁碟。