:磁盤尋道能力以高速硬盤(轉/秒)理論上每秒尋道次這是沒有辦法改變的優化的方法是用多個硬盤或者把數據分散存儲
:硬盤的讀寫速度這個速度非常的快這個更容易解決可以從多個硬盤上並行讀寫
:cpucpu處理內存中的數據當有相對內存較小的表時這是最常見的限制因素
:內存的限制當cpu需要超出適合cpu緩存的數據時緩存的帶寬就成了內存的一個瓶頸不過現在內存大的驚人一般不會出現這個問題
第二步: (本人使用的是學校網站的linux平台(Linux A mdk ))
:調節服務器參數
用shell>mysqldhelp這個命令聲廠一張所有mysql選項和可配置變量的表輸出以下信息:
possible variables for optionsetvariable(o) are:
back_log current value: //要求mysql能有的連接數量back_log指出在mysql暫停接受連接的時間內有多少個連接請求可以被存在堆棧中
connect_timeout current value: //mysql服務器在用bad handshake(不好翻譯)應答前等待一個連接的時間
delayed_insert_timeout current value: //一個insert delayed在終止前等待insert的時間
delayed_insert_limit current value: //insert delayed處理器將檢查是否有任何select語句未執行如果有繼續前執行這些語句
delayed_queue_size current value: //為insert delayed分配多大的隊
flush_time current value: //如果被設置為非那麼每個flush_time 時間所有表都被關閉
interactive_timeout current value: //服務器在關上它之前在洋交互連接上等待的時間
join_buffer_size current value: //用與全部連接的緩沖區大小
key_buffer_size current value: //用語索引塊的緩沖區的大小增加它可以更好的處理索引
lower_case_table_names current value: //
long_query_time current value: //如果一個查詢所用時間大於此時間slow_queried計數將增加
max_allowed_packet current value: //一個包的大小
max_connections current value: //允許同時連接的數量
max_connect_errors current value: //如果有多於該數量的中斷連接將阻止進一步的連接可以用flush hosts來解決
max_delayed_threads current value: //可以啟動的處理insert delayed的數量
max_heap_table_size current value: //
max_join_size current value: //允許讀取的連接的數量
max_sort_length current value: //在排序blob或者text時使用的字節數量
max_tmp_tables current value: //一個連接同時打開的臨時表的數量
max_write_lock_count current value: //指定一個值(通常很小)來啟動mysqld使得在一定數量的write鎖定之後出現read鎖定
net_buffer_length current value: //通信緩沖區的大小在查詢時被重置為該大小
query_buffer_size current value: //查詢時緩沖區大小
record_buffer current value: //每個順序掃描的連接為其掃描的每張表分配的緩沖區的大小
sort_buffer current value: //每個進行排序的連接分配的緩沖區的大小
table_cache current value: //為所有連接打開的表的數量
thread_concurrency current value: //
tmp_table_size current value: //臨時表的大小
thread_stack current value: //每個線程的大小
wait_timeout current value: //服務器在關閉它之前的一個連接上等待的時間
根據自己的需要配置以上信息會對你幫助
第三:
:如果你在一個數據庫中創建大量的表那麼執行打開關閉創建(表)的操作就會很慢 :mysql使用內存
a: 關鍵字緩存區(key_buffer_size)由所有線程共享
b: 每個連接使用一些特定的線程空間一個棧(默認為k變量thread_stack)一個連接緩沖區(變量net_buffer_length)和一個結果緩沖區(net_buffer_length)特定情況下連接緩沖區和結果緩沖區被動態擴大到max_allowed_packet
c:所有線程共享一個基存儲器
d:沒有內存影射
e:每個做順序掃描的請求分配一個讀緩沖區(record_buffer)
f:所有聯結均有一遍完成並且大多數聯結甚至可以不用一個臨時表完成最臨時的表是基於內存的(heap)表
g:排序請求分配一個排序緩沖區和個臨時表
h:所有語法分析和計算都在一個本地存儲器完成
i:每個索引文件只被打開一次並且數據文件為每個並發運行的線程打開一次
j:對每個blob列的表一個緩沖區動態的被擴大以便讀入blob值
k:所有正在使用的表的表處理器被保存在一個緩沖器中並且作為一個fifo管理
l:一個mysqladmin flushtables命令關閉所有不在使用的表並且在當前執行的線程結束時標記所有在使用的表准備關閉
:mysql鎖定表
mysql中所有鎖定不會成為死鎖 wirte鎖定: mysql的鎖定原理:a:如果表沒有鎖定那麼鎖定;b否則把鎖定請求放入寫鎖定隊列中
read鎖定: mysql的鎖定原理:a:如果表沒有鎖定那麼鎖定;b否則把鎖定請求放入讀鎖定隊列中
有時候會在一個表中進行很多的selectinsert操作可以在一個臨時表中插入行並且偶爾用臨時表的記錄更新真正的表
a:用low_priority屬性給一個特定的insertupdate或者delete較低的優先級
b:max_write_lock_count指定一個值(通常很小)來啟動mysqld使得在一定數量的write鎖定之後出現read鎖定
c:通過使用set sql_low_priority_updates=可以從一個特定的線程指定所有的更改應該由較低的優先級完成
d:用high_priority指定一個select
e:如果使用insertselect出現問題使用myisam表因為它支持因為它支持並發的select和insert
:最基本的優化是使數據在硬盤上占據的空間最小如果索引做在最小的列上那麼索引也最小實現方法:
a:使用盡可能小的數據類型
b:如果可能聲明表列為NOT NULL
c:如果有可能使用變成的數據類型如varchar(但是速度會受一定的影響)
d:每個表應該有盡可能短的主索引 e:創建確實需要的索引
f:如果一個索引在頭幾個字符上有唯一的前綴那麼僅僅索引這個前綴mysql支持在一個字符列的一部分上的索引
g:如果一個表經常被掃描那麼試圖拆分它為更多的表
第四步
:索引的使用索引的重要性就不說了功能也不說了只說怎麼做 首先要明確所有的mysql索引(primaryuniqueindex)在b樹中有存儲索引主要用語:
a:快速找到where指定條件的記錄 b:執行聯結時從其他表檢索行 c:對特定的索引列找出max()和min()值
d如果排序或者分組在一個可用鍵的最前面加前綴排序或分組一個表
e一個查詢可能被用來優化檢索值而不用訪問數據文件．如果某些表的列是數字型並且正好是某個列的前綴為了更快值可以從索引樹中取出
２存儲或者更新數據的查詢速度 grant的執行會稍稍的減低效率．
mysql的函數應該被高度的優化．可以用benchmark（loop_countexpression)來找出是否查詢有問題
select的查詢速度如果想要讓一個select．．．where．．．更快我能想到的只有建立索引．可以在一個表上運行myisamchk－－analyze來更好的優化查詢．可以用myisamchk－－sort－index－－sort－records＝１來設置用一個索引排序一個索引和數據．
３mysql優化where子句
１刪除不必要的括號
（（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)
:使用常數
（ab> AND b=c AND a=
:刪除常數條件
（b>= AND b=) OR (b= AND =) OR (b= AND =) >b= OR b=
:索引使用的常數表達式僅計算一次
在一個表中沒有一個where的count(*)直接從表中檢索信息
:所有常數的表在查詢中在任何其他表之前讀出
:對外聯結表最好聯結組合是嘗試了所有可能性找到的
如果有一個order by字句和一個不同的group by子句或者order by或者group by包含不是來自聯結的第一個表的列那麼創建一個臨時表
:如果使用了sql_small_result那麼msyql使用在內存中的一個表
:每個表的索引給查詢並且使用跨越少於３０％的行的索引．
在每個記錄輸出前跳過不匹配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 bymysql如果找到了第＃行將結束排序而不會排序正個表
c當結合limit＃和distinct時mysql如果找到了第＃行將停止
d只要mysql已經發送了第一個＃行到客戶mysql將放棄查詢
elimit 一直會很快的返回一個空集合．
f臨時表的大小使用limit＃計算需要多少空間來解決查詢
６優化insert
插入一條記錄的是由以下構成
a:連接（３）
b:發送查詢給服務器（２）
c:分析查詢（２）
d:插入記錄（１*記錄大小）
e插入索引（１*索引）
f關閉（１）
以上數字可以看成和總時間成比例
改善插入速度的一些方法
如果同時從一個連接插入許多行使用多個值的insert這比用多個語句要快
如果從不同連接插入很多行使用insert delayed語句速度更快
: 用myisam如果在表中沒有刪除的行能在selects正在運行的同時插入行
: 當從一個文本文件裝載一個表時用load data infile．這個通常比insert快 倍
:可以鎖定表然後插入－－主要的速度差別是在所有insert語句完成後索引緩沖區僅被存入到硬盤一次．一般與有不同的insert語句那樣多次存入要快．如果能用一個單個語句插入所有的行鎖定就不需要．鎖定也降低連接的整體時間．但是對某些線程最大等待時間將上升．例如
thread does inserts
thread and does insert
thread does inserts
如果不使用鎖定２３４將在１和５之前完成．如果使用鎖定２３４將可能在１和５之後完成．但是整體時間應該快４０％．因為insertupdatedelete操作在mysql中是很快的通過為多於大約５次連續不斷的插入或更新一行的東西加鎖將獲得更好的整體性能．如果做很多一行的插入可以做一個lock tables偶爾隨後做一個unlock tables（大約每１０００行）以允許另外的線程存取表．這仍然將導致獲得好的性能．load data infile對裝載數據仍然是很快的．
為了對load data infile和insert得到一些更快的速度擴大關鍵字緩沖區．
７優化update的速度
它的速度依賴於被更新數據的大小和被更新索引的數量
使update更快的另一個方法是推遲修改然後一行一行的做很多修改．如果鎖定表做一行一行的很多修改比一次做一個快
８優化delete速度
刪除一個記錄的時間與索引數量成正比．為了更快的刪除記錄可以增加索引緩存的大小 從一個表刪除所有行比刪除這個表的大部分要快的多
第五步
１選擇一種表類型 靜態myisam
這種格式是最簡單且最安全的格式它是磁盤格式中最快的．速度來自於數據能在磁盤上被找到的難易程度．當鎖定有一個索引和靜態格式的東西是它很簡單只是行長度乘以數量．而且在掃描一張表時每次用磁盤讀取來讀入常數個記錄是很容易的．安全性來源於如果當寫入一個靜態myisam文件時導致計算機down掉myisamchk很容易指出每行在哪裡開始和結束因此它通常能收回所有記錄除了部分被寫入的記錄．在mysql中所有索引總能被重建
動態myisam
這種格式每一行必須有一個頭說明它有多長．當一個記錄在更改期間變長時它可以在多於一個位置上結束．能使用optimize tablename或myisamchk整理一張表．如果在同一個表中有像某些varchar或者blob列那樣存取／改變的靜態數據將動態列移入另外一個表以避免碎片．
壓縮myisam用可選的myisampack工具生成
內存
這種格式對小型／中型表很有用．對拷貝／創建一個常用的查找表到洋heap表有可能加快多個表聯結用同樣數據可能要快好幾倍時間．
select tablenameatablenamea from tablenametablanemtablename where
tablanemea=tablenamea and tablenamea=tablenamea and tablenamec!=;
為了加速它可以用tablename和tablename的聯結創建一個臨時表因為用相同列（tablenamea）查找．
ＣＲＥＡＴＥ ＴＥＭＰＯＲＡＲＹ ＴＡＢＬＥ test TYPE=HEAP
SELECT
tablenamea as atablenamea as a
FROM
tablenamtablename
WHERE
tablenamea=tablenamea and c=;
SELECT tablenameatesta from tablenametest where tablenamea=testa;
SELECT tablenameatestafrom tablenametest where tablenamea=testa and ;
靜態表的特點
默認格式．用在表不包含varcharblobtext列的時候
所有的charnumeric和decimal列填充到列寬度
非常快
容易緩沖
容易在down後重建因為記錄位於固定的位置
不必被重新組織（用myisamchk）除非是一個巨量的記錄被刪除並且優化存儲大小
通常比動態表需要更多的存儲空間
動態表的特點
如果表包含任何varcharblobtext列使用該格式
所有字符串列是動態的
每個記錄前置一個位．
通常比定長表需要更多的磁盤空間
每個記錄僅僅使用所需要的空間如果一個記錄變的很大它按需要被分成很多段這導致了記錄碎片
如果用超過行長度的信息更新行行被分段．
在系統down掉以後不好重建表因為一個記錄可以是多段
對動態尺寸記錄的期望行長度是３＋（number of columns＋７）／８＋(number of char columns)+packed size of numeric columns+length of strings +(number of NULL columns+)/
對每個連接有６個字節的懲罰．無論何時更改引起記錄的變大都有一個動態記錄被連接．每個新連接至少有２０個字節因此下一個變大將可能在同一個連接中．如果不是將有另外一個連接．可以用myisamchk －惡毒檢查有多少連接．所有連接可以用myisamchk r刪除．
壓縮表的特點
一張用myisampack實用程序制作的只讀表．
解壓縮代碼存在於所有mysql分發中以便使沒有myisampack的連接也能讀取用myisampack壓縮的表
占據很小的磁盤空間
每個記錄被單獨壓縮．一個記錄的頭是一個定長的（１～～３個字節）這取決於表的最大記錄．每列以不同的方式被壓縮．一些常用的壓縮類型是
a:通常對每列有一張不同的哈夫曼表 b:後綴空白壓縮 c:前綴空白壓縮 d:用值０的數字使用１位存儲
e:如果整數列的值有一個小范圍列使用最小的可能類型來存儲．例如如果所有的值在０到２５５之間一個bigint可以作為一個tinyint存儲
g:如果列僅有可能值的一個小集合列類型被轉換到enum h:列可以使用上面的壓縮方法的組合
能處理定長或動態長度的記錄去不能處理blob或者text列 能用myisamchk解壓縮
mysql能支持不同的索引類型但一般的類型是isam這是一個Ｂ樹索引並且能粗略的為索引文件計算大小為(key_length+)*在所有的鍵上的總和．
字符串索引是空白壓縮的如果第一個索引是一個字符串它可將壓縮前綴如果字符串列有很多尾部空白或是一個總部能甬道全長的varchar列空白壓縮使索引文件更小．如果很多字符串有相同的前綴．
內存表的特點
mysql內部的heap表使用每偶溢出去的１００％動態哈希並且沒有與刪除有關的問題．只能通過使用在堆表中的一個索引來用等式存取東西（通常用＇＝＇操作符）
堆表的缺點是
想要同時使用的所有堆表需要足夠的額外內存
不能在索引的一個部分搜索
不能按順序搜索下一個條目（即使用這個索引做一個order by）
mysql不能算出在２個值之間大概有多少行．這被優化器使用是用來決定使用哪個索引的但是在另一個方面甚至不需要磁盤尋道
From:http://tw.wingwit.com/Article/program/MySQL/201311/29322.html
