MySQL數據庫怎么進行優化

這篇文章主要講解了“MySQL數據庫怎么進行優化”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySQL數據庫怎么進行優化”吧！

　　MySQL數據庫怎么優化

　　第一步：

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

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

　　3:cpu.cpu處理內存中的數據,當有相對內存較小的表時,這是最常見的限制因素.

　　4:內存的限制.當cpu需要超出適合cpu緩存的數據時,緩存的帶寬就成了內存的一個瓶頸---不過現在內存大的驚人,一般不會出現這個問題.

　　第二步:(本人使用的是學校網站的linux平臺(LinuxADVX.Mandrakesoft.com2.4.3-19mdk))

　　1:調節服務器參數

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

　　possiblevariablesforoption--set-variable(-o)are:

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

　　connect_timeoutcurrentvalue:5//mysql服務器在用badhandshake(不好翻譯)應答前等待一個連接的時間

　　delayed_insert_timeoutcurrentvalue:200//一個insertdelayed在終止前等待insert的時間

　　delayed_insert_limitcurrentvalue:50//insertdelayed處理器將檢查是否有任何select語句未執行,如果有,繼續前執行這些語句

　　delayed_queue_sizecurrentvalue:1000//為insertdelayed分配多大的隊

　　flush_timecurrentvalue:0//如果被設置為非0,那么每個flush_time時間,所有表都被關閉

　　interactive_timeoutcurrentvalue:28800//服務器在關上它之前在洋交互連接上等待的時間

　　join_buffer_sizecurrentvalue:131072//用與全部連接的緩沖區大小

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

　　lower_case_table_namescurrentvalue:0//

　　long_query_timecurrentvalue:10//如果一個查詢所用時間大于此時間,slow_queried計數將增加

　　max_allowed_packetcurrentvalue:1048576//一個包的大小

　　max_connectionscurrentvalue:300//允許同時連接的數量

　　max_connect_errorscurrentvalue:10//如果有多于該數量的中斷連接,將阻止進一步的連接,可以用flushhosts來解決

　　max_delayed_threadscurrentvalue:15//可以啟動的處理insertdelayed的數量

　　max_heap_table_sizecurrentvalue:16777216//

　　max_join_sizecurrentvalue:4294967295//允許讀取的連接的數量

　　max_sort_lengthcurrentvalue:1024//在排序blob或者text時使用的字節數量

　　max_tmp_tablescurrentvalue:32//一個連接同時打開的臨時表的數量

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

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

　　query_buffer_sizecurrentvalue:0//查詢時緩沖區大小

　　record_buffercurrentvalue:131072//每個順序掃描的連接為其掃描的每張表分配的緩沖區的大小

　　sort_buffercurrentvalue:2097116//每個進行排序的連接分配的緩沖區的大小

　　table_cachecurrentvalue:64//為所有連接打開的表的數量

　　thread_concurrencycurrentvalue:10//

　　tmp_table_sizecurrentvalue:1048576//臨時表的大小

　　thread_stackcurrentvalue:131072//每個線程的大小

　　wait_timeoutcurrentvalue: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:一個mysqladminflush-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:通過使用setsql_low_priority_updates=1可以從一個特定的線程指定所有的更改應該由較低的優先級完成

　　d:用high_priority指定一個select

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

　　4:最基本的優化是使數據在硬盤上占據的空間最小.如果索引做在最小的列上,那么索引也最小.實現方法:

　　a:使用盡可能小的數據類型

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

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

　　d:每個表應該有盡可能短的主索引e:創建確實需要的索引

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

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

　　第四步

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

　　a:快速找到where指定條件的記錄b:執行聯結時,從其他表檢索行c:對特定的索引列找出max()和min()值

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

　　e：一個查詢可能被用來優化檢索值，而不用訪問數據文件.如果某些表的列是數字型并且正好是某個列的前綴，為了更快，值可以從索引樹中取出

　　2：存儲或者更新數據的查詢速度　grant的執行會稍稍的減低效率.

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

　　select的查詢速度：如果想要讓一個select...where...更快，我能想到的只有建立索引.可以在一個表上運行myisamchk--analyze來更好的優化查詢.可以用myisamchk--sort-index--sort-records=1來設置用一個索引排序一個索引和數據.

　　3：mysql優化where子句

　　3.1：刪除不必要的括號：

　　((aANDb)ANDcOR(((aANDb)AND(aANDd))))>(aANDbANDc)OR(aANDbANDcANDd)

　　3.2:使用常數

　　(ab>5ANDb=cANDa=5

　　3.3:刪除常數條件

　　(b>=5ANDb=5)OR(b=6AND5=5)OR(b=100AND2=3)>b=5ORb=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:每個表的索引給查詢并且使用跨越少于30%的行的索引.

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

　　4：優化left　join

　　在mysql中　aleftjoinb按以下方式實現

　　a：表b依賴于表a

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

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

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

　　e：進行所有的標準的where優化f：如果在a中有一行匹配where子句，但是在b中沒有任何匹配left　join條件，那么，在b中生成的所有設置為NULL的一行

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

　　5：優化limit

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

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

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

　　d：只要mysql已經發送了第一個#行到客戶，mysql將放棄查詢

　　e：limit0一直會很快的返回一個空集合.

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

　　6：優化insert

　　MySQL數據庫怎么優化

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

　　a:連接(3)

　　b:發送查詢給服務器(2)

　　c:分析查詢(2)

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

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

　　f：關閉(1)

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

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

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

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

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

　　6.4:當從一個文該篇文章件裝載一個表時，用load　data　infile.這個通常比insert快20倍

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

　　thread1does1000inserts

　　thread2,3and4does1insert

　　thread5does1000inserts

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

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

　　7優化update的速度

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

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

　　8優化delete速度

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

　　第五步

　　1：選擇一種表類型1.1靜態myisam

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

　　1.2動態myisam

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

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

　　1.2.2內存

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

　　selecttablename.a,tablename2.afromtablename,tablanem2,tablename3where

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

　　為了加速它，可以用tablename2和tablename3的聯結創建一個臨時表，因為用相同列(tablename1.a)查找.

　　CREATE　TEMPORARY　TABLE　testTYPE=HEAP

　　SELECT

　　tablename2.aasa2,tablename3.aasa3

　　FROM

　　tablenam2,tablename3

　　WHERE

　　tablename2.a=tablename3.aandc=0;

　　SELECTtablename.a,test.a3fromtablename,testwheretablename.a=test.a1;

　　SELECTtablename.a,test,a3,fromtablename,testwheretablename.a=test.a1and....;

　　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對動態尺寸記錄的期望行長度是3+(number　of　columns+7)/8+(numberofcharcolumns)+packedsizeofnumericcolumns+lengthofstrings+(numberofNULLcolumns+7)/8

　　對每個連接有6個字節的懲罰.無論何時更改引起記錄的變大，都有一個動態記錄被連接.每個新連接至少有20個字節，因此下一個變大將可能在同一個連接中.如果不是，將有另外一個連接.可以用myisamchk　-惡毒檢查有多少連接.所有連接可以用myisamchk-r刪除.

　　1.5壓縮表的特點

　　1.5.1一張用myisampack實用程序制作的只讀表.

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

　　1.5.3占據很小的磁盤空間

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

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

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

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

　　1.5.5能處理定長或動態長度的記錄，去不能處理blob或者text列1.5.6能用myisamchk解壓縮

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

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

　　1.6內存表的特點

　　mysql內部的heap表使用每偶溢出去的100%動態哈希并且沒有與刪除有關的問題.只能通過使用在堆表中的一個索引來用等式存取東西(通常用'='操作符)

　　堆表的缺點是：

　　1.6.1想要同時使用的所有堆表需要足夠的額外內存

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

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

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

感謝各位的閱讀，以上就是“MySQL數據庫怎么進行優化”的內容了，經過本文的學習后，相信大家對MySQL數據庫怎么進行優化這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！