mysql數(shù)據(jù)庫優(yōu)化的步驟是怎樣的

這期內(nèi)容當中小編將會給大家?guī)碛嘘P(guān)mysql數(shù)據(jù)庫優(yōu)化的步驟是怎樣的，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

mysql數(shù)據(jù)庫優(yōu)化的步驟

1:磁盤尋道能力,以高速硬盤(7200轉(zhuǎn)/秒),理論上每秒尋道7200次.這是沒有辦法改變的,優(yōu)化的方法是----用多個硬盤,或者把數(shù)據(jù)分散存儲.
　　
　　2:硬盤的讀寫速度,這個速度非常的快,這個更容易解決--可以從多個硬盤上并行讀寫.
　　
　　3:cpu.cpu處理內(nèi)存中的數(shù)據(jù),當有相對內(nèi)存較小的表時,這是最常見的限制因素.
　　
　　4:內(nèi)存的限制.當cpu需要超出適合cpu緩存的數(shù)據(jù)時,緩存的帶寬就成了內(nèi)存的一個瓶頸---不過現(xiàn)在內(nèi)存大的驚人,一般不會出現(xiàn)這個問題.
　　
　　第二步: (本人使用的是學校網(wǎng)站的linux平臺(Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))
　　
　　1:調(diào)節(jié)服務(wù)器參數(shù)
　　
　　用shell>mysqld-help這個命令聲廠一張所有mysql選項和可配置變量的表.輸出以下信息:
　　
　　possible variables for option--set-variable(-o) are:
　　
　　back_log current value:5 //要求mysql能有的連接數(shù)量.back_log指出在mysql暫停接受連接的時間內(nèi)有多少個連接請求可以被存在堆棧中
　　
　　connect_timeout current value:5 //mysql服務(wù)器在用bad handshake(不好翻譯)應(yīng)答前等待一個連接的時間
　　
　　delayed_insert_timeout current value:200 //一個insert delayed在終止前等待insert的時間
　　
　　delayed_insert_limit current value:50 //insert delayed處理器將檢查是否有任何select語句未執(zhí)行,如果有,繼續(xù)前執(zhí)行這些語句
　　
　　delayed_queue_size current value:1000 //為insert delayed分配多大的隊
　　
　　flush_time current value:0 //如果被設(shè)置為非0,那么每個flush_time 時間,所有表都被關(guān)閉
　　
　　interactive_timeout current value:28800 //服務(wù)器在關(guān)上它之前在洋交互連接上等待的時間
　　
　　join_buffer_size current value:131072 //用與全部連接的緩沖區(qū)大小
　　
　　key_buffer_size current value:1048540 //用語索引塊的緩沖區(qū)的大小,增加它可以更好的處理索引
　　
　　lower_case_table_names current value:0 //
　　
　　long_query_time current value:10 //如果一個查詢所用時間大于此時間,slow_queried計數(shù)將增加
　　
　　max_allowed_packet current value:1048576 //一個包的大小
　　
　　max_connections current value:300 //允許同時連接的數(shù)量
　　
　　max_connect_errors current value:10 //如果有多于該數(shù)量的中斷連接,將阻止進一步的連接,可以用flush hosts來解決
　　
　　max_delayed_threads current value:15 //可以啟動的處理insert delayed的數(shù)量
　　
　　max_heap_table_size current value:16777216 //
　　
　　max_join_size current value:4294967295 //允許讀取的連接的數(shù)量
　　
　　max_sort_length current value:1024 //在排序blob或者text時使用的字節(jié)數(shù)量
　　
　　max_tmp_tables current value:32 //一個連接同時打開的臨時表的數(shù)量
　　
　　max_write_lock_count current value:4294967295 //指定一個值(通常很小)來啟動mysqld,使得在一定數(shù)量的write鎖定之后出現(xiàn)read鎖定
　　
　　net_buffer_length current value:16384 //通信緩沖區(qū)的大小--在查詢時被重置為該大小
　　
　　query_buffer_size current value:0 //查詢時緩沖區(qū)大小
　　
　　record_buffer current value:131072 //每個順序掃描的連接為其掃描的每張表分配的緩沖區(qū)的大小
　　
　　sort_buffer current value:2097116 //每個進行排序的連接分配的緩沖區(qū)的大小
　　
　　table_cache current value:64 //為所有連接打開的表的數(shù)量
　　
　　thread_concurrency current value:10 //
　　
　　tmp_table_size current value:1048576 //臨時表的大小
　　
　　thread_stack current value:131072 //每個線程的大小
　　
　　wait_timeout current value:28800 //服務(wù)器在關(guān)閉它3之前的一個連接上等待的時間
　　
　　根據(jù)自己的需要配置以上信息會對你幫助.
　　
　　第三:
　　
　　1:如果你在一個數(shù)據(jù)庫中創(chuàng)建大量的表,那么執(zhí)行打開,關(guān)閉，創(chuàng)建(表)的操作就會很慢. 2:mysql使用內(nèi)存
　　
　　a: 關(guān)鍵字緩存區(qū)(key_buffer_size)由所有線程共享
　　
　　b: 每個連接使用一些特定的線程空間.一個棧(默認為64k,變量thread_stack),一個連接緩沖區(qū)(變量net_buffer_length)和一個結(jié)果緩沖區(qū)(net_buffer_length).特定情況下,連接緩沖區(qū)和結(jié)果緩沖區(qū)被動態(tài)擴大到max_allowed_packet.
　　
　　c:所有線程共享一個基存儲器
　　
　　d:沒有內(nèi)存影射
　　
　　e:每個做順序掃描的請求分配一個讀緩沖區(qū)(record_buffer)
　　
　　f:所有聯(lián)結(jié)均有一遍完成并且大多數(shù)聯(lián)結(jié)甚至可以不用一個臨時表完成.最臨時的表是基于內(nèi)存的(heap)表
　　
　　g:排序請求分配一個排序緩沖區(qū)和2個臨時表
　　
　　h:所有語法分析和計算都在一個本地存儲器完成
　　
　　i:每個索引文件只被打開一次,并且數(shù)據(jù)文件為每個并發(fā)運行的線程打開一次
　　
　　j:對每個blob列的表，一個緩沖區(qū)動態(tài)的被擴大以便讀入blob值
　　
　　k:所有正在使用的表的表處理器被保存在一個緩沖器中并且作為一個fifo管理.
　　
　　l:一個mysqladmin flush-tables命令關(guān)閉所有不在使用的表并且在當前執(zhí)行的線程結(jié)束時標記所有在使用的表準備關(guān)閉
　　
　　3:mysql鎖定表
　　
　　mysql中所有鎖定不會成為死鎖. wirte鎖定: mysql的鎖定原理:a:如果表沒有鎖定,那么鎖定;b否則,把鎖定請求放入寫鎖定隊列中
　　
　　read鎖定: mysql的鎖定原理:a:如果表沒有鎖定,那么鎖定;b否則,把鎖定請求放入讀鎖定隊列中
　　
　　有時候會在一個表中進行很多的select,insert操作,可以在一個臨時表中插入行并且偶爾用臨時表的記錄更新真正的表
　　
　　a:用low_priority屬性給一個特定的insert,update或者delete較低的優(yōu)先級
　　
　　b:max_write_lock_count指定一個值(通常很小)來啟動mysqld,使得在一定數(shù)量的write鎖定之后出現(xiàn)read鎖定
　　
　　c:通過使用set sql_low_priority_updates=1可以從一個特定的線程指定所有的更改應(yīng)該由較低的優(yōu)先級完成
　　
　　d:用high_priority指定一個select
　　
　　e:如果使用insert....select....出現(xiàn)問題,使用myisam表------因為它支持因為它支持并發(fā)的select和insert
　　
　　4:最基本的優(yōu)化是使數(shù)據(jù)在硬盤上占據(jù)的空間最小.如果索引做在最小的列上,那么索引也最小.實現(xiàn)方法:
　　
　　a:使用盡可能小的數(shù)據(jù)類型
　　
　　b:如果可能，聲明表列為NOT NULL.
　　
　　c:如果有可能使用變成的數(shù)據(jù)類型,如varchar(但是速度會受一定的影響)
　　
　　d:每個表應(yīng)該有盡可能短的主索引 e:創(chuàng)建確實需要的索引
　　
　　f:如果一個索引在頭幾個字符上有唯一的前綴,那么僅僅索引這個前綴----mysql支持在一個字符列的一部分上的索引
　　
　　g:如果一個表經(jīng)常被掃描,那么試圖拆分它為更多的表
　　
　　第四步
　　
　　1:索引的使用,索引的重要性就不說了,功能也不說了,只說怎么做. 首先要明確所有的mysql索引(primary,unique,index)在b樹中有存儲.索引主要用語:
　　
　　a:快速找到where指定條件的記錄 b:執(zhí)行聯(lián)結(jié)時,從其他表檢索行 c:對特定的索引列找出max()和min()值
　　
　　d：如果排序或者分組在一個可用鍵的最前面加前綴，排序或分組一個表
　　
　　e：一個查詢可能被用來優(yōu)化檢索值，而不用訪問數(shù)據(jù)文件．如果某些表的列是數(shù)字型并且正好是某個列的前綴，為了更快，值可以從索引樹中取出
　　
　?。玻捍鎯蛘吒聰?shù)據(jù)的查詢速度 　grant的執(zhí)行會稍稍的減低效率．
　　
　　mysql的函數(shù)應(yīng)該被高度的優(yōu)化．可以用benchmark（loop_count,expression)來找出是否查詢有問題
　　
　　select的查詢速度：如果想要讓一個select．．．where．．．更快，我能想到的只有建立索引．可以在一個表上運行myisamchk－－analyze來更好的優(yōu)化查詢．可以用myisamchk－－sort－index－－sort－records＝１來設(shè)置用一個索引排序一個索引和數(shù)據(jù)．
　　
　?。常簃ysql優(yōu)化where子句
　　
　　3.１：刪除不必要的括號：
　　
　?。ǎ╝ 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:使用常數(shù)
　　
　?。╝b>5 AND b=c AND a=5
　　
　　3.3:刪除常數(shù)條件
　　
　　（b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=100 AND 2=3) >b=5 OR b=6
　　
　　3.4:索引使用的常數(shù)表達式僅計算一次
　　
　　3.5：在一個表中，沒有一個where的count(*)直接從表中檢索信息
　　
　　3.6:所有常數(shù)的表在查詢中在任何其他表之前讀出
　　
　　3.7:對外聯(lián)結(jié)表最好聯(lián)結(jié)組合是嘗試了所有可能性找到的
　　
　　3.8：如果有一個order　by字句和一個不同的group　by子句或者order　by或者group　by包含不是來自聯(lián)結(jié)的第一個表的列，那么創(chuàng)建一個臨時表
　　
　　3.9:如果使用了sql_small_result，那么msyql使用在內(nèi)存中的一個表
　　
　　3.10:每個表的索引給查詢并且使用跨越少于３０％的行的索引．
　　
　　3.11在每個記錄輸出前，跳過不匹配having子句的行
　　
　?。矗簝?yōu)化left　join
　　
　　在mysql中　a left join b按以下方式實現(xiàn)
　　
　　a：表b依賴于表a　
　　
　　b：表a依賴于所有用在left　join條件的表（除了b）
　　
　　c：所有l(wèi)eft　join條件被移到where子句中
　　
　　d：進行所有的聯(lián)結(jié)優(yōu)化，除了一個表總是在所有他依賴的表后讀?。绻幸粋€循環(huán)依賴，那么將發(fā)生錯誤
　　
　　e：進行所有的標準的where優(yōu)化 f：如果在a中有一行匹配where子句，但是在b中沒有任何匹配left　join條件，那么，在b中生成的所有設(shè)置為ＮＵＬＬ的一行
　　
　　g：如果使用left　join來找出某些表中不存在的行并且在where部分有column_name IS NULL測試(column_name為NOT NULL列)．那么，mysql在它已經(jīng)找到了匹配left　join條件的一行后，將停止在更多的行后尋找
　　
　　５：優(yōu)化limit
　　
　　a：如果用limit只選擇一行，當mysql需要掃描整個表時，它的作用相當于索引
　　
　　b：如果使用limit＃與order　by，mysql如果找到了第＃行，將結(jié)束排序，而不會排序正個表
　　
　　c：當結(jié)合limit＃和distinct時，mysql如果找到了第＃行，將停止
　　
　　d：只要mysql已經(jīng)發(fā)送了第一個＃行到客戶，mysql將放棄查詢
　　
　　e：limit 0一直會很快的返回一個空集合．
　　
　　f：臨時表的大小使用limit＃計算需要多少空間來解決查詢
　　
　　６：優(yōu)化insert
　　
　　插入一條記錄的是由以下構(gòu)成：
　　
　　a:連接（３）
　　
　　b:發(fā)送查詢給服務(wù)器（２）
　　
　　c:分析查詢（２）
　　
　　d:插入記錄（１*記錄大?。?br/>　　
　　e：插入索引（１*索引）
　　
　　f：關(guān)閉（１）
　　
　　以上數(shù)字可以看成和總時間成比例
　　
　　改善插入速度的一些方法：
　　
　　6.1：如果同時從一個連接插入許多行，使用多個值的insert，這比用多個語句要快
　　
　　6.2：如果從不同連接插入很多行，使用insert　delayed語句速度更快
　　
　　6.3: 用myisam，如果在表中沒有刪除的行，能在select：s正在運行的同時插入行
　　
　　6.4: 當從一個文本文件裝載一個表時，用load　data　infile．這個通常比insert快20 倍
　　
　　6.5:可以鎖定表然后插入－－主要的速度差別是在所有insert語句完成后，索引緩沖區(qū)僅被存入到硬盤一次．一般與有不同的insert語句那樣多次存入要快．如果能用一個單個語句插入所有的行，鎖定就不需要．鎖定也降低連接的整體時間．但是對某些線程最大等待時間將上升．例如：
　　
　　thread 1 does 1000 inserts
　　
　　thread 2,3 and 4 does 1 insert
　　
　　thread 5 does 1000 inserts
　　
　　如果不使用鎖定，２，３，４將在１和５之前完成．如果使用鎖定，２，３，４，將可能在１和５之后完成．但是整體時間應(yīng)該快４０％．因為insert，update，delete操作在mysql中是很快的，通過為多于大約５次連續(xù)不斷的插入或更新一行的東西加鎖，將獲得更好的整體性能．如果做很多一行的插入，可以做一個lock　tables，偶爾隨后做一個unlock　tables（大約每１０００行）以允許另外的線程存取表．這仍然將導(dǎo)致獲得好的性能．load　data　infile對裝載數(shù)據(jù)仍然是很快的．
　　
　　為了對load　data　infile和insert得到一些更快的速度，擴大關(guān)鍵字緩沖區(qū)．
　　
　　７優(yōu)化update的速度
　　
　　它的速度依賴于被更新數(shù)據(jù)的大小和被更新索引的數(shù)量
　　
　　使update更快的另一個方法是推遲修改，然后一行一行的做很多修改．如果鎖定表，做一行一行的很多修改比一次做一個快
　　
　?。竷?yōu)化delete速度
　　
　　刪除一個記錄的時間與索引數(shù)量成正比．為了更快的刪除記錄，可以增加索引緩存的大小 從一個表刪除所有行比刪除這個表的大部分要快的多
　　
　　第五步
　　
　?。保哼x擇一種表類型 1.1靜態(tài)myisam
　　
　　這種格式是最簡單且最安全的格式，它是磁盤格式中最快的．速度來自于數(shù)據(jù)能在磁盤上被找到的難易程度．當鎖定有一個索引和靜態(tài)格式的東西是，它很簡單，只是行長度乘以數(shù)量．而且在掃描一張表時，每次用磁盤讀取來讀入常數(shù)個記錄是很容易的．安全性來源于如果當寫入一個靜態(tài)myisam文件時導(dǎo)致計算機down掉，myisamchk很容易指出每行在哪里開始和結(jié)束，因此，它通常能收回所有記錄，除了部分被寫入的記錄．在mysql中所有索引總能被重建
　　
　　1.2動態(tài)myisam
　　
　　這種格式每一行必須有一個頭說明它有多長．當一個記錄在更改期間變長時，它可以在多于一個位置上結(jié)束．能使用optimize　tablename或myisamchk整理一張表．如果在同一個表中有像某些varchar或者blob列那樣存?。淖兊撵o態(tài)數(shù)據(jù)，將動態(tài)列移入另外一個表以避免碎片．
　　
　　1.2.1壓縮myisam，用可選的myisampack工具生成
　　
　　1.2.2內(nèi)存
　　
　　這種格式對小型／中型表很有用．對拷貝／創(chuàng)建一個常用的查找表到洋heap表有可能加快多個表聯(lián)結(jié)，用同樣數(shù)據(jù)可能要快好幾倍時間．
　　
　　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的聯(lián)結(jié)創(chuàng)建一個臨時表，因為用相同列（tablename1.a）查找．
　　
　?。茫遥牛粒裕拧。裕牛停校希遥粒遥佟。裕粒拢蹋拧est 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靜態(tài)表的特點
　　
　　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），除非是一個巨量的記錄被刪除并且優(yōu)化存儲大小
　　
　　1.3.7通常比動態(tài)表需要更多的存儲空間
　　
　　1.4動態(tài)表的特點
　　
　　1.4.1如果表包含任何varchar，blob，text列，使用該格式
　　
　　1.4.2所有字符串列是動態(tài)的
　　
　　1.4.3每個記錄前置一個位．
　　
　　1.4.4通常比定長表需要更多的磁盤空間
　　
　　1.4.5每個記錄僅僅使用所需要的空間，如果一個記錄變的很大，它按需要被分成很多段，這導(dǎo)致了記錄碎片
　　
　　1.4.6如果用超過行長度的信息更新行，行被分段．
　　
　　1.4.7在系統(tǒng)down掉以后不好重建表，因為一個記錄可以是多段
　　
　　1.4.8對動態(tài)尺寸記錄的期望行長度是３＋（number　of　columns＋７）／８＋(number of char columns)+packed size of numeric columns+length of strings +(number of NULL columns+7)/8
　　
　　對每個連接有６個字節(jié)的懲罰．無論何時更改引起記錄的變大，都有一個動態(tài)記錄被連接．每個新連接至少有２０個字節(jié)，因此下一個變大將可能在同一個連接中．如果不是，將有另外一個連接．可以用myisamchk　－惡毒檢查有多少連接．所有連接可以用myisamchk -r刪除．
　　
　　1.5壓縮表的特點
　　
　　1.5.1一張用myisampack實用程序制作的只讀表．
　　
　　1.5.2解壓縮代碼存在于所有mysql分發(fā)中，以便使沒有myisampack的連接也能讀取用myisampack壓縮的表
　　
　　1.5.3占據(jù)很小的磁盤空間
　　
　　1.5.4每個記錄被單獨壓縮．一個記錄的頭是一個定長的（１～～３個字節(jié)）這取決于表的最大記錄．每列以不同的方式被壓縮．一些常用的壓縮類型是：
　　
　　a:通常對每列有一張不同的哈夫曼表 　b:后綴空白壓縮 　c:前綴空白壓縮 d:用值０的數(shù)字使用１位存儲
　　
　　e:如果整數(shù)列的值有一個小范圍，列使用最小的可能類型來存儲．例如：如果所有的值在０到２５５之間，一個bigint可以作為一個tinyint存儲
　　
　　g:如果列僅有可能值的一個小集合，列類型被轉(zhuǎn)換到enum 　h:列可以使用上面的壓縮方法的組合
　　
　　1.5.5能處理定長或動態(tài)長度的記錄，去不能處理blob或者text列 1.5.6能用myisamchk解壓縮
　　
　　mysql能支持不同的索引類型，但一般的類型是isam，這是一個Ｂ樹索引并且能粗略的為索引文件計算大小為(key_length+4)*0.67，在所有的鍵上的總和．
　　
　　字符串索引是空白壓縮的。如果第一個索引是一個字符串，它可將壓縮前綴如果字符串列有很多尾部空白或是一個總部能甬道全長的varchar列，空白壓縮使索引文件更小．如果很多字符串有相同的前綴．
　　
　　1.6內(nèi)存表的特點
　　
　　mysql內(nèi)部的heap表使用每偶溢出去的１００％動態(tài)哈希并且沒有與刪除有關(guān)的問題．只能通過使用在堆表中的一個索引來用等式存取東西（通常用＇＝＇操作符）
　　
　　堆表的缺點是：
　　
　　1.6.1想要同時使用的所有堆表需要足夠的額外內(nèi)存
　　
　　1.6.2不能在索引的一個部分搜索
　　
　　1.6.3不能按順序搜索下一個條目（即，使用這個索引做一個order　by）
　　
　　1.6.4mysql不能算出在２個值之間大概有多少行．這被優(yōu)化器使用是用來決定使用哪個索引的，但是在另一個方面甚至不需要磁盤尋道

上述就是小編為大家分享的mysql數(shù)據(jù)庫優(yōu)化的步驟是怎樣的了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。